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