CREATE TABLE CustomerTbl(
custID	varchar(8) PRIMARY KEY,
custName	varchar(25),
custSurname	varchar(30),
Tel			varchar(10),
LineID		varchar(25),
FB			varchar(30),
emailAddr	varchar(30),
birthDate	DATE,
gender		varchar(1) CHECK(gender IN ('M', 'F'))
);

CREATE TABLE OrderTbl(
orderNo		int PRIMARY KEY,
orderDate	DATE,
custID		varchar(8),
FOREIGN KEY(custID) REFERENCES CustomerTbl(custID),
totalPrice	money,
tax			int,
totalPriceWithTax	money,
totalDiscount		money,
paymenyStatus	varchar(10),
paymentType		varchar(10));

CREATE TABLE CategoryTbl(
Catecode	varchar(2) PRIMARY KEY,
Category	varchar(30)
);

CREATE TABLE DiscountTbl(
discountCode	varchar(10) PRIMARY KEY,
discountPercentage	int
);

CREATE TABLE BrandTbl(
brandCode	varchar(3) PRIMARY KEY,
brandName	varchar(30)
);

CREATE TABLE ProductTbl(
productCode	varchar(6) PRIMARY KEY,
adescription	varchar(50),
category	varchar(2),
FOREIGN KEY(category) REFERENCES CategoryTbl(Catecode),
brand		varchar(3),
FOREIGN KEY (brand)	REFERENCES BrandTbl(brandcode),
model		varchar(50),
quantity	int,
unitPrice	money);

CREATE TABLE ItemListTbl(
itemNo	int	PRIMARY KEY,
orderNo	int,
FOREIGN KEY(orderNO) REFERENCES OrderTbl(orderNo),
product	varchar(6),
FOREIGN KEY(product) REFERENCES ProductTbl (productCode),
quantity int,
totalPricePerItem money,
discount varchar(10),
FOREIGN KEY (discount) REFERENCES DiscountTbl(discountCode)
);




INSERT INTO CategoryTbl
VALUES('01', 'Sport Gadget'), ('02', 'Running Shoes'), ('03', 'Sport Wear');

INSERT INTO BrandTbl
VALUES ('001', 'ALTRA'),
('002', 'NEWTON'), ('003', 'COMPRESSPORT'),
('004', 'GARMIN'), ('005', 'SUUNTO'),
('006', 'TOMTOM'), ('007', 'BACKBEAT'),
('008', 'SOUL'), ('009', 'SPENCO');

INSERT INTO DiscountTbl
VALUES ('1212', 10), ('MemberCard', 5), ('CLEARANCE', 40), ('BLACKFRI', 20);

INSERT INTO ProductTbl
VALUES ('000001', 'Suunto Spartan Trainer Wrist HR GOLD', '01' ,'005' ,'Trainer Wrist HR GOLD' ,20 ,11900),
('000002', 'Suunto Spartan Trainer Wrist HR', '01' ,'005' ,'Trainer Wrist HR' ,15 ,9900),
('000003', 'Garmin-Fenix-5S-Rose-Goldtone', '01' ,'004' ,'Fenix-5S' ,10 ,27900),
('000004', 'Garmin-Forerunner-935-Yellow', '01' ,'004' ,'Forerunner-935' ,30 ,20990),
('000005', 'TomTom-Spark-Cardio_Black', '01' ,'006' ,'Spark-Cardio' ,10 ,9180),
('000006', 'ALTRA-Women-One-V3-Light-BLUE', '02' ,'001' ,'Women-One-V3-Light' ,50 ,4900),
('000007', 'ALTRA-Women-Paradigm-3.0-Dark-Blue', '02' ,'001' ,'Women-Paradigm-3.0' ,40 ,6200),
('000008', 'Newton-Women-Motion-Vl-Teal-Hero', '02' ,'002' ,'Women-Motion-Vl-Teal-Hero' ,30 ,6000),
('000009', 'Spenco-Total-Support-Max', '01' ,'009' ,'Total-Support-Max' ,25 ,900),
('000010', 'SOUL-Flex-Sport-Earphones-BlueBlack', '01' ,'008' ,'Flex-Sport-Earphones' ,20 ,2400),
('000011', 'backbeat-fit-green', '01' ,'007' ,'fit-green' ,30 ,4590),
('000012', 'Compressport-Training-Tshirt-Man-Blue', '03' ,'003' ,'Training-Tshirt-Man' ,30 ,2250);

INSERT INTO CustomerTbl
VALUES ('ABC12341', 'นฤภัทร', 'สำราญใจ', '0868667014', 'Naru', 'NaRuPat', 'narupat.s@gmail.com', '1997-3-16', 'M'),
('ABC12342', 'สมศรี', 'สุขสำราญใจ', '0898171010', 'SriHappy', 'SomSriHappyness', 'SomSri.Hap@gmail.com', '1988-12-10', 'F'),
('ABC12343', 'สมานศักดิ์', 'ภักดีตลอดกาล', '0918021011', 'Samansuk', 'SamanLoyalty', 'Saman@gmail.com', '1988-12-10', 'M'),
('ABC12344', 'ชาย', 'ขยันยิ่ง', '0878179034', 'Manny', 'ManManny', 'chai.c@gmail.com', '1998-4-28', 'M');

INSERT INTO OrderTbl
VALUES (100, '2561-08-09', 'ABC12341', 19150.00, 1340, 20490.50, 0, 'ชำระแล้ว', 'เงินสด'),
(101, '2561-08-09', 'ABC12341', 54030.00, 3403.89, 57433, 5403, 'ชำระแล้ว', 'เครดิต');

INSERT INTO ItemListTbl (itemNo, orderNo, product, quantity, totalPricePerItem)
VALUES(1, 100, '000002', 1, 9900),
(2, 100, '000006', 1, 4900),
(3, 100, '000009', 1, 900);

INSERT INTO ItemListTbl
VALUES (4, 101, '000011', 1, 4590, '1212'),
(5, 101, '000010', 1, 2400, '1212'),
(6, 101, '000012', 1, 2250, '1212'),
(7, 101, '000001', 1, 23800, '1212'),
(8, 101, '000004', 1, 20990, '1212');