SET NAMES 'utf8';

CREATE TABLE `CUSTOMER` (
  `PINDEX`  INTEGER  NOT NULL,
  `CNR`  INTEGER  DEFAULT 0  NOT NULL  UNIQUE,
  `LASTNAME`  VARCHAR(50)  DEFAULT '',
  `FIRSTNAME`  VARCHAR(50)  DEFAULT '',
  `STREET`  VARCHAR(50)  DEFAULT '',
  `BIRTHDAY`  DATE  DEFAULT '2018-01-01',
  `CATINDEX`  INTEGER  NOT NULL,

  CONSTRAINT `PK_CUSTOMER`  PRIMARY KEY (`PINDEX`)
);

CREATE TABLE `CATEGORY` (
  `PINDEX`  INTEGER  NOT NULL,
  `BEZ`  VARCHAR(30)  DEFAULT '',

  CONSTRAINT `PK_CATEGORY`  PRIMARY KEY (`PINDEX`)
);



ALTER TABLE `CUSTOMER` ADD CONSTRAINT 
   `FK_CATEGORY_CUSTOMER` FOREIGN KEY (`CATINDEX`) REFERENCES `CATEGORY`(`PINDEX`);






  create table sequence(
     id INT not null
   );
   
   insert into sequence values(40);






Insert into category(pindex,  bez)
Values(0, 'Nicht vorhanden');

Insert into category(pindex,  bez)
Values(1, 'Gelegenheitskäufer');

Insert into category(pindex,  bez)
Values(2, 'Guter Kunde');

Insert into category(pindex,  bez)
Values(3, 'Sehr guter Kunde');



Insert into category(pindex,  bez)
Values(4, 'Super Kunden');


SET NAMES 'utf8';
Insert into customer(pindex,  cnr, lastname, firstname, street,  birthday,catindex)
Values(11, 12345, 'Meier', 'Hans', 'Friedrichstrasse 55', '2018-03-14',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(12, 54321,'Schulze', 'Susanne', 'Langgasse 4a', '2011-03-07',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(13, 234, 'Brandter', 'Torsten', 'Marktstrasse 5', '2018-03-07',2);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(14,42, 'Dent', 'Arthur', 'Magarethea Weg 12', '1996-08-07',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(15, 142, 'Braun', 'Werner', 'Nasa 1969', '1969-07-20',1);  

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(16, 145, 'Brandt', 'Willi', 'Kastanieallee 12a', '1971-02-10',3);

Insert into customer(pindex,  cnr, lastname, firstname, street,  birthday,catindex)
Values(17, 345, 'Schmitt', 'Dorethea', 'Martkstrasse 3', '2001-12-24',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(18, 11, 'Schwarzenau', 'Paul', 'Kreuzerweg 12a', '2001-04-17',4);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(19, 55, 'Gisin', 'Nicola', 'Springerallee 1', '1999-12-24',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(20, 123, 'Genius', 'Chantal', 'Hallenser Strasse 3', '1991-02-10',1);

Insert into customer(pindex,  cnr, lastname, firstname, street,  birthday,catindex)
Values(21, 77, 'Schoen', 'Jaqueline', 'Einkaufsallee 123', '1991-02-11',4);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(22, 14, 'Schwarz', 'Hans-Georg', 'Lange Strasse 3', '1985-06-27',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(23, 35, 'Maurer', 'Fritz', 'Handwerkerweg 5', '1979-02-29',2);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(24, 113, 'Bosch', 'Hieronymus', 'Kunstweg 42', '1998-05-11',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(27, 1451, 'Brandt', 'Andrea', 'Langer Weg 345a', '2010-05-10',2);

Insert into customer(pindex,  cnr, lastname, firstname, street,  birthday,catindex)
Values(25, 57, 'Brandtner', 'Andrea', 'Rosenweg 2', '2001-02-03',1);

Insert into customer(pindex, cnr, lastname, firstname, street,  birthday,catindex)
Values(37, 1452, 'Brandt', 'Georg', 'Rosenweg 1', '2006-01-01',3);


SET NAMES 'utf8';

CREATE TABLE UIPATTERN (
  UIKEY  VARCHAR(30)  DEFAULT '' unique,
  UITYP  VARCHAR(20)  DEFAULT '',
  CAPTION  VARCHAR(20)  DEFAULT '',
  MINVAL  INTEGER  DEFAULT 0,
  MAXVAL  INTEGER  DEFAULT 0,
  PATTERN  VARCHAR(400)  DEFAULT '',

  CONSTRAINT PK_PATTERN   PRIMARY KEY (UIKEY)
);

SET NAMES 'utf8';
Insert into uipattern(uikey,uityp, caption)
Values('pindex', '-','Pindex');

Insert into uipattern(uikey,uityp,caption, minval, maxval)
Values('cnr', 'number', 'Kundenummer',1,100002 );

Insert into uipattern(uikey,uityp,caption,pattern)
Values('lastname', 'text', 'Nachname','^[A-ZÜÄÖ][a-züäöß]{1,29}$' );

Insert into uipattern(uikey,uityp,caption,pattern)
Values('firstname', 'text', 'Vorname','^[A-ZÜÄÖ][a-züäöß]{1,29}$' );

Insert into uipattern(uikey,uityp,caption,pattern)
Values('street', 'text', 'Straße','^[A-ZÜÄÖ][a-züäöß]{1,29}([ ][A-ZÜÄÖ][a-züäöß]{1,29}){0,2}[ ][1-9][0-9]{0,3}([a-z]){0,1}$' );

Insert into uipattern(uikey,uityp,caption)
Values('birthday', 'date', 'Geburtstag');