--====== SQL Schema for OOH Project ======--
-- Map API: AIzaSyDxIW1Vz81M2Lpn2Cg6jACA4rm4k3-LNZg
-- Users
IF OBJECT_ID('users', 'U') IS NOT NULL
    DROP TABLE users;
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    username NVARCHAR(100) UNIQUE,
    password NVARCHAR(255),
    role NVARCHAR(20) DEFAULT 'other'
);

-- Suppliers
CREATE TABLE [dbo].[suppliers](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
);
INSERT INTO suppliers (name) VALUES
('3 Mien'),
('An Giang'),
('Bac Nam'),
('Bizman'),
('Binh Minh'),
('Chicilon'),
('Chieu Duong'),
('DatViet'),
('Golden'),
('Golden Land (Dat Vang)'),
('Goldmoon'),
('Goldsun'),
('Green Light'),
('Hai Van'),
('Huy Nam'),
('IntelAds'),
('Le Nguyen'),
('Maxicus'),
('Nam Long'),
('Ngoc Ha'),
('NTCom'),
('Pacific'),
('Prowtech'),
('Sao Dong Nam'),
('Shojiki'),
('Suc Song Moi'),
('SunLogo'),
('Target Media'),
('Thien An'),
('Youth Ad');

-- Formats
CREATE TABLE [dbo].[formats](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NOT NULL,
);
INSERT INTO formats (name) VALUES
('Billboard'),
('Sky LED'),
('Big LED'),
('LCD - Office'),
('LCD - Mall'),
('LCD - Supermarket'),
('LCD - Street'),
('LCD - Airport'),
('LCD - F&B'),
('Digital Poster - Office'),
('Digital Poster - Mall'),
('Digital Poster - Supermarket'),
('Digital Poster - Street'),
('Digital Poster - Airport'),
('Digital Poster - F&B'),
('Standee Screen - Mall'),
('Standee Screen - Super Market'),
('Bus Ad'),
('Bus Shelter'),
('Taxi Ad (Outside)'),
('Taxi Ad (Inside)'),
('Motorbike Ad'),
('Lightbox - Street'),
('Lightbox - Mall'),
('Lightbox - Airport'),
('Lightbox - Market'),
('Lightbox - Industrial'),
('Market - Banner'),
('Market - Pano'),
('Market - POSM'),
('Train Sticker'),
('Subway Station'),
('Electric Column');

-- Duration
CREATE TABLE duration (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO duration (name) VALUES
('day'), ('week'), ('month'), ('quarter'), ('year');

-- Duration Buy (only for multiple slots selling formats such as LED, LCD, Digital Poster)
CREATE TABLE duration_buy (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO duration (name) VALUES
('1 hour/day'), ('2 hour/day'), ('3 hour/day'), ('4 hour/day'), ('5 hour/day'), ('full timeslot booking');

-- Market
CREATE TABLE market (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO market (name) VALUES
('Nationwide'), ('North'), ('Central'), ('South'), ('Mekong Delta'), ('Highland'), ('Ho Chi Minh'), ('Ha Noi'), ('Da Nang'), ('Hai Phong'), ('Can Tho'), ('Nha Trang'), ('Binh Duong'), ('Dong Nai'), ('Long An'), ('Ba Ria - Vung Tau'), ('Quang Ninh'), ('Khanh Hoa'), ('Thanh Hoa'), ('Nghe An'), ('Bac Ninh'), ('Hai Duong'), ('Bac Giang'), ('Thua Thien - Hue');

-- Unilever BU
CREATE TABLE business_unit (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);

-- Unilever Brand
CREATE TABLE unilever (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO unilever (name) VALUES
('OMO'), ('Lifebuoy'), ('Sunsilk'), ('P/S'), ('Comfort'), ('Sunlight'),
('Surf'), ('Dove'), ('Knorr'), ('Clear'), ('Lux'), ('Close-up'), ('Lipton');

-- Competitors
CREATE TABLE competitors (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);
INSERT INTO competitors (name) VALUES
('GSK'), ('P&G'), ('Loreal'), ('Nestle'), ('Vinamilk'), ('Masan'), ('Nutifood');

-- Market Overview/ Sites
IF OBJECT_ID('sites', 'U') IS NOT NULL
    DROP TABLE sites;
CREATE TABLE sites (
    id INT IDENTITY(1,1) PRIMARY KEY,
    supplier NVARCHAR(255),
    type NVARCHAR(100),
    format NVARCHAR(100),
    size_x_m DECIMAL(6,2),
    size_y_m DECIMAL(6,2),
    size_x_p INT,
    size_y_p INT,
    side_num INT,
    image_base NVARCHAR(255),
    pdooh NVARCHAR(10),
    market NVARCHAR(100),
    district NVARCHAR(100),
    ward NVARCHAR(100),
    street NVARCHAR(255),
    street_no NVARCHAR(50),
    format_coordination NVARCHAR(255),
    availability DATE,
    current_own NVARCHAR(255),
    evaluate_score DECIMAL(4,2),
    weekly_imp BIGINT NULL  -- Weekly impression numbers
);

-- Competitive Activity
CREATE TABLE comp_activity (
    id INT IDENTITY(1,1) PRIMARY KEY,
    competitor_id INT NOT NULL, -- Foreign key to competitors.id
    site INT NOT NULL,          -- Foreign key to sites.id
    image_comp NVARCHAR(255),
    airing_time DATETIME,
    estimate_end DATETIME NULL,
    FOREIGN KEY (competitor_id) REFERENCES competitors(id),
    FOREIGN KEY (site) REFERENCES sites(id)
);
--instruction to connect competitors, sites with comp_activity
$sql = "
    SELECT ca.*, c.name AS competitor_name, s.*
    FROM comp_activity ca
    JOIN competitors c ON ca.competitor_id = c.id
    JOIN sites s ON ca.site = s.id
";

-- Plans
CREATE TABLE plans (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    description NVARCHAR(1000),
    budget DECIMAL(18,2),
    created_by INT NOT NULL,
    created_at DATETIME DEFAULT GETDATE(),
    start_date DATE,
    end_date DATE,
    target_market NVARCHAR(50),
    category INT NOT NULL,
    target_brand INT NOT NULL,
    status NVARCHAR(20) DEFAULT 'draft',
    notes NVARCHAR(1000),
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (target_brand) REFERENCES unilever(id),
    FOREIGN KEY (category) REFERENCES business_unit(id)
);
--instruction to connect users, unilever with plan
$sql = "
    SELECT p.*, u.username, b.name AS brand_name
    FROM plan p
    JOIN users u ON p.created_by = u.id
    JOIN unilever b ON p.target_brand = b.id
";

-- Planning Items
CREATE TABLE planning_items (
    id INT IDENTITY(1,1) PRIMARY KEY,
    plan_name INT NOT NULL,         -- Foreign key to plan(id)
    site INT NOT NULL,              -- Foreign key to sites(id)
    duration INT NOT NULL,          -- Foreign key to duration(id)
    duration_buy INT NOT NULL,      -- Foreign key to duration_buy(id)
    supplier_cost BIGINT,
    discount DECIMAL(5,2),
    plant_cost BIGINT,
    buy_type NVARCHAR(50),
    image_uni NVARCHAR(255),        -- Similar to image_base/image_comp
    total_imp BIGINT NULL,          -- Total impression numbers
    FOREIGN KEY (plan_name) REFERENCES plans(id),
    FOREIGN KEY (site) REFERENCES sites(id),
    FOREIGN KEY (duration) REFERENCES duration(id),
    FOREIGN KEY (duration_buy) REFERENCES duration_buy(id)
);
--instruction to connect plans, sites, duration, and duration_buy with planning_items
$sql = "
    SELECT pi.*, p.name AS plan_name, s.*, d.name AS duration_name, db.name AS duration_buy_name
    FROM planning_items pi
    JOIN plans p ON pi.plan_name = p.id
    JOIN sites s ON pi.site = s.id
    JOIN duration d ON pi.duration = d.id
    JOIN duration_buy db ON pi.duration_buy = db.id
";



