-- Table: categories
CREATE TABLE IF NOT EXISTS categories (
    category_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100) NOT NULL UNIQUE --Laptop, Phone, ...,
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: manufacturers
CREATE TABLE IF NOT EXISTS manufacturers (
    manufacturer_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100) NOT NULL UNIQUE, --Apple, Legion, ...
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: products
CREATE TABLE IF NOT EXISTS products (
    product_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100) UNIQUE NOT NULL, --Iphone 15, Iphone 16, ...
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: stock
CREATE TABLE IF NOT EXISTS stock (
    stock_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    product_id CHAR(36) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
    manufacturer_id CHAR(36) NOT NULL,
    category_id CHAR(36),
    is_deleted BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (product_id) REFERENCES products (product_id) ON UPDATE CASCADE,
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturers (manufacturer_id) ON UPDATE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories (category_id) ON UPDATE CASCADE
);

-- Table: product_prices
CREATE TABLE IF NOT EXISTS product_prices (
    price_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    stock_id CHAR(36) NOT NULL,
    price_type ENUM('import', 'export') NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    effective_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_deleted BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Table: partners
CREATE TABLE IF NOT EXISTS partners (
    partner_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100) NOT NULL,
    partner_type ENUM('supplier', 'customer') NOT NULL,
    address TEXT,
    phone VARCHAR(15) NOT NULL,
    email VARCHAR(100),
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (name, partner_type)
);

-- Table: employees
CREATE TABLE IF NOT EXISTS employees (
    employee_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15),
    is_deleted BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: transaction_headers
CREATE TABLE IF NOT EXISTS transaction_headers (
    header_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    action ENUM('import', 'export') NOT NULL,
    partner_id CHAR(36),
    employee_id CHAR(36),
    total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (partner_id) REFERENCES partners (partner_id) ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY (employee_id) REFERENCES employees (employee_id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- Table: transaction_items
CREATE TABLE IF NOT EXISTS transaction_items (
    item_id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    header_id CHAR(36) NOT NULL,
    stock_id CHAR(36),
    quantity INT NOT NULL CHECK (quantity > 0),
    price_per_unit_snapshot DECIMAL(10, 2) NOT NULL CHECK (price_per_unit_snapshot > 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (header_id) REFERENCES transaction_headers (header_id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (stock_id) REFERENCES stock (stock_id) ON DELETE SET NULL ON UPDATE CASCADE
);