-- =========================
-- COUNTRY / LOCATION CORE
-- =========================

CREATE TABLE country (
    country_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    phone_code VARCHAR(5),
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE territory (
    territory_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country_id INT NOT NULL,
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

CREATE TABLE city (
    city_id INT AUTO_INCREMENT PRIMARY KEY,
    territory_id INT NOT NULL,
    country_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territory(territory_id),
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

CREATE TABLE address (
    id INT AUTO_INCREMENT PRIMARY KEY,
    address_1 VARCHAR(100) NOT NULL,
    address_2 VARCHAR(255),
    city_id INT NOT NULL,
    territory_id INT NOT NULL,
    country_id INT NOT NULL,
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES city(city_id),
    FOREIGN KEY (territory_id) REFERENCES territory(territory_id),
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

CREATE TABLE phones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_code_id INT NOT NULL,
    ph_number VARCHAR(50) NOT NULL,
    ph_ext VARCHAR(20),
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (country_code_id) REFERENCES country(country_id)
);

-- =========================
-- COMPANY / USERS
-- =========================

CREATE TABLE tax_agency (
    id INT AUTO_INCREMENT PRIMARY KEY,
    country_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    link VARCHAR(255),
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

CREATE TABLE company (
    company_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    website VARCHAR(255),
    country_id INT NOT NULL,
    billing_address_id INT,
    shipping_address_id INT,
    tax_agency_id INT,
    tax_number VARCHAR(255),
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES country(country_id),
    FOREIGN KEY (billing_address_id) REFERENCES address(id),
    FOREIGN KEY (shipping_address_id) REFERENCES address(id),
    FOREIGN KEY (tax_agency_id) REFERENCES tax_agency(id)
);

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    initials VARCHAR(5) UNIQUE,
    name VARCHAR(100),
    description TEXT,
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    role_id INT NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(200) UNIQUE,
    password TEXT NOT NULL,
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES company(company_id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE users_event_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    event_creator_id INT,
    event_note VARCHAR(255),
    internal_note VARCHAR(255),
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- =========================
-- PRODUCT SYSTEM
-- =========================

CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category_id INT,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES category(id)
);

CREATE TABLE product_image (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    image LONGBLOB,
    is_default BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES product(id)
);

CREATE TABLE presentation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    units_per_presentation INT NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_presentation (
    product_id INT NOT NULL,
    presentation_id INT NOT NULL,
    PRIMARY KEY (product_id, presentation_id),
    FOREIGN KEY (product_id) REFERENCES product(id),
    FOREIGN KEY (presentation_id) REFERENCES presentation(id)
);

CREATE TABLE packaging (
    id INT AUTO_INCREMENT PRIMARY KEY,
    presentation_id INT NOT NULL,
    name VARCHAR(255),
    quantity INT NOT NULL,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (presentation_id) REFERENCES presentation(id)
);

-- =========================
-- ATTRIBUTE SYSTEM
-- =========================

CREATE TABLE attribute (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    unit VARCHAR(50),
    data_type ENUM('text','numeric') DEFAULT 'text',
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_attribute_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    attribute_id INT NOT NULL,
    value_text VARCHAR(255),
    value_numeric DECIMAL(10,2),
    FOREIGN KEY (product_id) REFERENCES product(id),
    FOREIGN KEY (attribute_id) REFERENCES attribute(id)
);

CREATE TABLE packaging_attribute_value (
    id INT AUTO_INCREMENT PRIMARY KEY,
    packaging_id INT NOT NULL,
    attribute_id INT NOT NULL,
    value_text VARCHAR(255),
    value_numeric DECIMAL(10,2),
    FOREIGN KEY (packaging_id) REFERENCES packaging(id),
    FOREIGN KEY (attribute_id) REFERENCES attribute(id)
);

-- =========================
-- STATIONS
-- =========================

CREATE TABLE station (
    station_id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    name VARCHAR(100),
    status BOOLEAN DEFAULT 1,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE product_station (
    product_id INT NOT NULL,
    station_id INT NOT NULL,
    price DECIMAL(10,2),
    max_qty INT,
    status BOOLEAN DEFAULT 1,
    PRIMARY KEY (product_id, station_id),
    FOREIGN KEY (product_id) REFERENCES product(id),
    FOREIGN KEY (station_id) REFERENCES station(station_id)
);

CREATE TABLE station_user (
    station_id INT NOT NULL,
    user_id INT NOT NULL,
    status BOOLEAN DEFAULT 1,
    PRIMARY KEY (station_id, user_id),
    FOREIGN KEY (station_id) REFERENCES station(station_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- =========================
-- ORDER SYSTEM
-- =========================

CREATE TABLE priority (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    color VARCHAR(50),
    status BOOLEAN DEFAULT 1
);

CREATE TABLE order_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description TEXT,
    status BOOLEAN DEFAULT 1
);

CREATE TABLE orders (
    ord_id INT AUTO_INCREMENT PRIMARY KEY,
    ord_number INT UNIQUE NOT NULL,
    company_id INT NOT NULL,
    station_id INT NOT NULL,
    priority_id INT,
    order_status_id INT,
    filled BOOLEAN DEFAULT 0,
    note TEXT,
    reference_number VARCHAR(255),
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES company(company_id),
    FOREIGN KEY (station_id) REFERENCES station(station_id),
    FOREIGN KEY (priority_id) REFERENCES priority(id),
    FOREIGN KEY (order_status_id) REFERENCES order_status(id)
);

CREATE TABLE order_products (
    ord_prod_id INT AUTO_INCREMENT PRIMARY KEY,
    ord_id INT NOT NULL,
    product_id INT NOT NULL,
    price DECIMAL(10,2),
    qty INT NOT NULL,
    fulfilled_qty INT DEFAULT 0,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ord_id) REFERENCES orders(ord_id),
    FOREIGN KEY (product_id) REFERENCES product(id)
);

CREATE TABLE order_event_log (
    ord_log_id INT AUTO_INCREMENT PRIMARY KEY,
    ord_id INT,
    ord_prod_id INT,
    order_status_id INT,
    user_id INT,
    event_note VARCHAR(255),
    internal_note VARCHAR(255),
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ord_id) REFERENCES orders(ord_id),
    FOREIGN KEY (ord_prod_id) REFERENCES order_products(ord_prod_id),
    FOREIGN KEY (order_status_id) REFERENCES order_status(id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- =========================
-- INVOICE SYSTEM
-- =========================

CREATE TABLE invoices (
    inv_id INT AUTO_INCREMENT PRIMARY KEY,
    ord_id INT NOT NULL,
    inv_number VARCHAR(100) UNIQUE,
    is_partial BOOLEAN DEFAULT 0,
    creation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ord_id) REFERENCES orders(ord_id)
);

CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inv_id INT NOT NULL,
    ord_prod_id INT NOT NULL,
    qty INT NOT NULL,
    FOREIGN KEY (inv_id) REFERENCES invoices(inv_id),
    FOREIGN KEY (ord_prod_id) REFERENCES order_products(ord_prod_id)
);

-- =========================
-- LOGISTICS
-- =========================

CREATE TABLE logistic_provider (
    lp_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    phone_id INT,
    website_link VARCHAR(255),
    tracking_link VARCHAR(255),
    country_id INT,
    status BOOLEAN DEFAULT 1,
    FOREIGN KEY (phone_id) REFERENCES phones(id),
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

-- =========================
-- PROJECT SETTINGS (Landing Page URL)
-- =========================

CREATE TABLE project (
    id INT NOT NULL PRIMARY KEY,
    landing_page VARCHAR(1024) NULL,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO project (id, landing_page) VALUES (1, 'http://weborder.com');