USE master;
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'QL_HighlandsCoffee')
BEGIN
    ALTER DATABASE QL_HighlandsCoffee SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE QL_HighlandsCoffee;
END
GO

CREATE DATABASE QL_HighlandsCoffee;
GO

USE QL_HighlandsCoffee;
GO

CREATE TABLE CHINHANH (
    MaCN CHAR(5) PRIMARY KEY,
    TenCN NVARCHAR(50) NOT NULL,
    DiaChi NVARCHAR(100) NOT NULL,
    SDT VARCHAR(15) UNIQUE CHECK (LEN(SDT) >= 10),
    NgayMo DATE DEFAULT GETDATE()
);
GO

CREATE TABLE SANPHAM (
    MaSP CHAR(5) PRIMARY KEY,
    TenSP NVARCHAR(50) NOT NULL,
    DonGia DECIMAL(10, 2) CHECK (DonGia > 0),
    LoaiSP NVARCHAR(30) NULL
);
GO

CREATE TABLE NHANVIEN (
    MaNV CHAR(5) PRIMARY KEY,
    HoTen NVARCHAR(50) NOT NULL,
    GioiTinh BIT DEFAULT 1 CHECK (GioiTinh IN (0,1)),
    NgaySinh DATE CHECK (NgaySinh < GETDATE()),
    MaCN CHAR(5) FOREIGN KEY REFERENCES CHINHANH(MaCN),
    Luong DECIMAL(10, 2) CHECK (Luong >= 5000000)
);
GO

CREATE TABLE HOADON (
    MaHD CHAR(10) PRIMARY KEY,
    NgayBan DATETIME DEFAULT GETDATE(),
    MaNV CHAR(5) FOREIGN KEY REFERENCES NHANVIEN(MaNV),
    MaCN CHAR(5) FOREIGN KEY REFERENCES CHINHANH(MaCN)
);
GO

ALTER TABLE NHANVIEN ADD Email VARCHAR(50) UNIQUE;
GO

ALTER TABLE CHINHANH ADD TrangThai NVARCHAR(20) DEFAULT N'Hoạt động';
GO

ALTER TABLE SANPHAM ADD MoTa NVARCHAR(200);
GO

ALTER TABLE SANPHAM DROP COLUMN LoaiSP;
GO