-- SQL schema for OOH Management

IF OBJECT_ID('formats', 'U') IS NOT NULL
    DROP TABLE formats;
CREATE TABLE formats (
    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_y 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),
    supplier_cost BIGINT,
    duration NVARCHAR(50),
    duration_buy NVARCHAR(50),
    discount DECIMAL(5,2),
    plan_cost BIGINT,
    buy_type NVARCHAR(50),
    availability DATE,
    current_own NVARCHAR(255),
    image_comp NVARCHAR(255),
    evaluate_score DECIMAL(4,2),
    buy_cost BIGINT,
    profit_lost BIGINT
);


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'
);

USE [OOH]
GO

/****** Object:  Table [dbo].[suppliers]    Script Date: 7/29/2025 11:36:46 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[suppliers](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [OOH]
GO


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');

/****** Object:  Table [dbo].[formats]    Script Date: 7/29/2025 11:37:45 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[formats](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](255) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
UNIQUE NONCLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

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');


CREATE TABLE unilever_brand (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO unilever_brand (name) VALUES
('OMO'), ('Lifebuoy'), ('Sunsilk'), ('P/S'), ('Comfort'), ('Sunlight'),
('Surf'), ('Dove'), ('Knorr'), ('Clear'), ('Lux'), ('Close-up'), ('Lipton');

CREATE TABLE competitor_brand (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO competitor_brand (name) VALUES
('GSK'), ('P&G'), ('Loreal'), ('Nestle'), ('Vinamilk'), ('Masan'), ('Nutifood');

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');



CREATE TABLE planning (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    description NVARCHAR(1000),
    created_by INT NOT NULL,
    created_at DATETIME DEFAULT GETDATE(),
    start_date DATE,
    end_date DATE,
    target_market NVARCHAR(50),
    target_brand INT,
    budget DECIMAL(18,2),
    status NVARCHAR(20) DEFAULT 'draft',
    notes NVARCHAR(1000),
    FOREIGN KEY (target_brand) REFERENCES unilever_brand(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);