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