CREATE DATABASE coffeemaster;

USE coffeemaster;


DROP TABLE tai_khoan;
CREATE TABLE tai_khoan(
	username NVARCHAR(100) NOT NULL,
	password NVARCHAR(100),
	name NVARCHAR(50),
	birthday DATETIME,
	phone NVARCHAR(15),
	andress NVARCHAR(500),
	status INT,
	roles INT,
	CONSTRAINT PK_TK PRIMARY KEY(username)
);
INSERT INTO tai_khoan VALUES('admin', '123', N'Thiên Đẹp Zaii', '07-11-2000', '0941661235', 'Hưng Yên', 1, 1);
INSERT INTO tai_khoan VALUES('staff', '123', N'Thiên Đẹp Zaii', '07-11-2000', '0941661235', 'Hưng Yên', 1, 2);


DROP TABLE mat_hang;
CREATE TABLE mat_hang(
	id_mh NVARCHAR(10) NOT NULL,
	name NVARCHAR(100),
	sizes NVARCHAR(500),
	options NVARCHAR(500),
	status INT,
	CONSTRAINT PK_MH PRIMARY KEY (id_mh)
);

drop table san_pham;
CREATE TABLE san_pham(
	id_sp NVARCHAR(10) NOT NULL,
	name_sp NVARCHAR(100),
	price_sp INT,
	status_sp INT,
	id_mh NVARCHAR(10) NOT NULL,
	CONSTRAINT PK_SP PRIMARY KEY (id_sp),
	CONSTRAINT FK_SP_MH FOREIGN KEY(id_mh) REFERENCES mat_hang(id_mh)
);

drop table tang;
CREATE TABLE tang(
	id_tang NVARCHAR(10) NOT NULL,
	name_tang NVARCHAR(100),
	CONSTRAINT PK_T PRIMARY KEY(id_tang)
);
INSERT INTO tang VALUES('t1', N'Tầng 1'), ('t2', N'Tầng 2'), ('t3', N'Tầng 3');


drop table ban;
CREATE TABLE ban(
	id_ban NVARCHAR(10) NOT NULL,
	name_ban NVARCHAR(100),
	id_tang NVARCHAR(10) NOT NULL,
	CONSTRAINT PK_B PRIMARY KEY(id_ban),
	CONSTRAINT FK_B_T FOREIGN KEY(id_tang) REFERENCES tang(id_tang)
);
INSERT INTO ban VALUES('b1', 'Bàn 1', 't1'), ('b2', 'Bàn 2', 't1'), ('b3', 'Bàn 3', 't1'), ('b4', 'Bàn 4', 't1'), ('b5', 'Bàn 5', 't1'), ('b6', 'Bàn 6', 't1'), ('b7', 'Bàn 7', 't1'), ('b8', 'Bàn 8', 't1'), ('b9', 'Bàn 9', 't1'), ('b10', 'Bàn 10', 't1'), ('b11', 'Bàn 11', 't1'), ('b12', 'Bàn 12', 't1'), ('b13', 'Bàn 13', 't1'), ('b14', 'Bàn 14', 't1'), ('b15', 'Bàn 15', 't1'), ('b16', 'Bàn 16', 't1');

drop table hoa_don;
CREATE TABLE hoa_don(
	id_hd INT NOT NULL IDENTITY(1 , 1),
	total_money INT,
	time DATETIME,
	id_ban NVARCHAR(10) NOT NULL,
	CONSTRAINT PK_HD PRIMARY KEY(id_hd),
	CONSTRAINT FK_HD_B FOREIGN KEY(id_ban) REFERENCES ban(id_ban)
);

drop table ct_hoa_don;
CREATE TABLE ct_hoa_don(
	id_cthd INT NOT NULL IDENTITY(1 , 1), 
	id_hd  INT NOT NULL,
	id_sp NVARCHAR(10) NOT NULL,
	sizes NVARCHAR(10),
	amount INT,
	total_money INT,
	note NVARCHAR(500),
	CONSTRAINT PK_CTHD PRIMARY KEY(id_cthd),
	CONSTRAINT FK_CTHD_HD FOREIGN KEY(id_hd) REFERENCES hoa_don(id_hd),
	CONSTRAINT FK_CTHD_SP FOREIGN KEY(id_sp) REFERENCES san_pham(id_sp)
);

SELECT * FROM tai_khoan;
SELECT * FROM mat_hang;
SELECT * FROM san_pham;

SELECT * FROM hoa_don;
SELECT * FROM ct_hoa_don;

DELETE FROM tai_khoan;
DELETE FROM san_pham;
DELETE FROM hoa_don;
DELETE FROM ct_hoa_don;



SELECT * FROM hoa_don JOIN ban ON hoa_don.id_ban = ban.id_ban ORDER BY (time) DESC;
SELECT * FROM ct_hoa_don JOIN san_pham ON ct_hoa_don.id_sp = san_pham.id_sp;