CREATE DATABASE IF NOT EXISTS pos_lite_2026
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE pos_lite_2026;
USE pos_lite_2026;

-- ============================================================
-- 1. ROLES
-- ============================================================

CREATE TABLE roles (
    role_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- ============================================================
-- 2. USERS
-- ============================================================

CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    photo_id VARCHAR(255) NULL,
    last_login DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_role (role_id),
    INDEX idx_users_branch (branch_id),
    INDEX idx_users_status (status),

    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id)
        REFERENCES roles(role_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;


-- ============================================================
-- 3. BRANCHES
-- ============================================================

CREATE TABLE branches (
    branch_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    branch_code VARCHAR(30) NOT NULL UNIQUE,
    branch_name VARCHAR(150) NOT NULL,
    address VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- Add branch FK after branches exists
ALTER TABLE users
ADD CONSTRAINT fk_users_branch
    FOREIGN KEY (branch_id)
    REFERENCES branches(branch_id)
    ON UPDATE CASCADE
    ON DELETE SET NULL;


-- ============================================================
-- 4. CATEGORIES
-- ============================================================

CREATE TABLE categories (
    category_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_category_name (category_name),
    INDEX idx_categories_status (status)
) ENGINE=InnoDB;


-- ============================================================
-- 5. PRODUCTS
-- ============================================================

CREATE TABLE products (
    product_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    product_code VARCHAR(50) NOT NULL UNIQUE,
    barcode VARCHAR(100) NULL UNIQUE,
    part_number VARCHAR(100) NULL,

    product_name VARCHAR(200) NOT NULL,

    category_id BIGINT UNSIGNED NULL,

    purchase_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    sale_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    current_stock DECIMAL(15,3) NOT NULL DEFAULT 0.000,
    minimum_stock DECIMAL(15,3) NOT NULL DEFAULT 0.000,

    unit VARCHAR(30) NOT NULL DEFAULT 'PCS',
    location VARCHAR(100) NULL,

    status TINYINT(1) NOT NULL DEFAULT 1,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_products_name (product_name),
    INDEX idx_products_part_number (part_number),
    INDEX idx_products_category (category_id),
    INDEX idx_products_status (status),

    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories(category_id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB;


-- ============================================================
-- 6. CUSTOMERS
-- ============================================================

CREATE TABLE customers (
    customer_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    customer_code VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(150) NOT NULL,

    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    address VARCHAR(255) NULL,

    opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    credit_limit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    status TINYINT(1) NOT NULL DEFAULT 1,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_customers_name (customer_name),
    INDEX idx_customers_phone (phone),
    INDEX idx_customers_status (status)
) ENGINE=InnoDB;


-- ============================================================
-- 7. SUPPLIERS
-- ============================================================

CREATE TABLE suppliers (
    supplier_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    supplier_code VARCHAR(50) NOT NULL UNIQUE,
    supplier_name VARCHAR(150) NOT NULL,

    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    address VARCHAR(255) NULL,

    opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    credit_limit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    status TINYINT(1) NOT NULL DEFAULT 1,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_suppliers_name (supplier_name),
    INDEX idx_suppliers_phone (phone),
    INDEX idx_suppliers_status (status)
) ENGINE=InnoDB;


-- ============================================================
-- 8. PURCHASES
-- ============================================================

CREATE TABLE purchases (
    purchase_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    purchase_no VARCHAR(50) NOT NULL UNIQUE,

    branch_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NULL,

    supplier_name VARCHAR(150) NULL,

    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    pending_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    payment_status ENUM(
        'PAID',
        'PARTIAL',
        'PENDING'
    ) NOT NULL DEFAULT 'PENDING',

    status ENUM(
        'DRAFT',
        'POSTED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'POSTED',

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_purchases_branch (branch_id),
    INDEX idx_purchases_supplier (supplier_id),
    INDEX idx_purchases_date (created_at),
    INDEX idx_purchases_status (status),

    CONSTRAINT fk_purchases_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_purchases_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id),

    CONSTRAINT fk_purchases_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 9. PURCHASE ITEMS
-- ============================================================

CREATE TABLE purchase_items (
    purchase_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    purchase_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,

    quantity DECIMAL(15,3) NOT NULL,
    purchase_price DECIMAL(15,2) NOT NULL,

    amount DECIMAL(15,2)
        GENERATED ALWAYS AS
        (quantity * purchase_price) STORED,

    INDEX idx_purchase_items_purchase (purchase_id),
    INDEX idx_purchase_items_product (product_id),

    CONSTRAINT fk_purchase_items_purchase
        FOREIGN KEY (purchase_id)
        REFERENCES purchases(purchase_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_purchase_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
) ENGINE=InnoDB;


-- ============================================================
-- 10. SALES
-- ============================================================

CREATE TABLE sales (
    sale_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    sale_no VARCHAR(50) NOT NULL UNIQUE,

    branch_id BIGINT UNSIGNED NOT NULL,

    customer_id BIGINT UNSIGNED NULL,
    customer_name VARCHAR(150) NULL,

    total_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    paid_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    pending_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    payment_status ENUM(
        'PAID',
        'PARTIAL',
        'PENDING'
    ) NOT NULL DEFAULT 'PAID',

    status ENUM(
        'DRAFT',
        'POSTED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'POSTED',

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_sales_branch (branch_id),
    INDEX idx_sales_customer (customer_id),
    INDEX idx_sales_date (created_at),
    INDEX idx_sales_status (status),
    INDEX idx_sales_payment_status (payment_status),

    CONSTRAINT fk_sales_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_sales_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL,

    CONSTRAINT fk_sales_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 11. SALE ITEMS
-- ============================================================

CREATE TABLE sale_items (
    sale_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    sale_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,

    quantity DECIMAL(15,3) NOT NULL,

    sale_price DECIMAL(15,2) NOT NULL,

    amount DECIMAL(15,2)
        GENERATED ALWAYS AS
        (quantity * sale_price) STORED,

    historical_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    INDEX idx_sale_items_sale (sale_id),
    INDEX idx_sale_items_product (product_id),

    CONSTRAINT fk_sale_items_sale
        FOREIGN KEY (sale_id)
        REFERENCES sales(sale_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_sale_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
) ENGINE=InnoDB;


-- ============================================================
-- 12. SALE RETURNS
-- ============================================================

CREATE TABLE sale_returns (
    return_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    return_no VARCHAR(50) NOT NULL UNIQUE,

    sale_id BIGINT UNSIGNED NOT NULL,
    sale_no VARCHAR(50) NOT NULL,

    branch_id BIGINT UNSIGNED NOT NULL,

    customer_id BIGINT UNSIGNED NULL,
    customer_name VARCHAR(150) NULL,

    total_return_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    refund_method ENUM(
        'CUSTOMER_BALANCE',
        'CASH'
    ) NOT NULL DEFAULT 'CUSTOMER_BALANCE',

    status ENUM(
        'POSTED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'POSTED',

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_returns_sale (sale_id),
    INDEX idx_returns_branch (branch_id),
    INDEX idx_returns_customer (customer_id),
    INDEX idx_returns_date (created_at),

    CONSTRAINT fk_returns_sale
        FOREIGN KEY (sale_id)
        REFERENCES sales(sale_id),

    CONSTRAINT fk_returns_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_returns_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE SET NULL,

    CONSTRAINT fk_returns_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 13. SALE RETURN ITEMS
-- ============================================================

CREATE TABLE sale_return_items (
    return_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    return_id BIGINT UNSIGNED NOT NULL,

    sale_id BIGINT UNSIGNED NOT NULL,

    sale_item_id BIGINT UNSIGNED NOT NULL,

    product_id BIGINT UNSIGNED NOT NULL,

    quantity DECIMAL(15,3) NOT NULL,

    sale_price DECIMAL(15,2) NOT NULL,

    amount DECIMAL(15,2)
        GENERATED ALWAYS AS
        (quantity * sale_price) STORED,

    INDEX idx_return_items_return (return_id),
    INDEX idx_return_items_sale (sale_id),
    INDEX idx_return_items_product (product_id),

    CONSTRAINT fk_return_items_return
        FOREIGN KEY (return_id)
        REFERENCES sale_returns(return_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_return_items_sale
        FOREIGN KEY (sale_id)
        REFERENCES sales(sale_id),

    CONSTRAINT fk_return_items_sale_item
        FOREIGN KEY (sale_item_id)
        REFERENCES sale_items(sale_item_id),

    CONSTRAINT fk_return_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
) ENGINE=InnoDB;


-- ============================================================
-- 14. STOCK TRANSACTIONS
-- ============================================================

CREATE TABLE stock_transactions (
    transaction_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    product_id BIGINT UNSIGNED NOT NULL,

    branch_id BIGINT UNSIGNED NOT NULL,

    transaction_type ENUM(
        'OPENING',
        'PURCHASE',
        'SALE',
        'SALE_RETURN',
        'TRANSFER_IN',
        'TRANSFER_OUT',
        'ADJUSTMENT_IN',
        'ADJUSTMENT_OUT'
    ) NOT NULL,

    quantity DECIMAL(15,3) NOT NULL,

    reference_id BIGINT UNSIGNED NULL,

    unit_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_stock_product (product_id),
    INDEX idx_stock_branch (branch_id),
    INDEX idx_stock_date (transaction_date),
    INDEX idx_stock_type (transaction_type),
    INDEX idx_stock_reference (reference_id),

    CONSTRAINT fk_stock_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id),

    CONSTRAINT fk_stock_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_stock_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 15. CUSTOMER LEDGER
-- ============================================================

CREATE TABLE customer_ledger (
    ledger_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    customer_id BIGINT UNSIGNED NOT NULL,

    branch_id BIGINT UNSIGNED NULL,

    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    transaction_type ENUM(
        'OPENING',
        'SALE',
        'PAYMENT',
        'SALE_RETURN',
        'ADJUSTMENT'
    ) NOT NULL,

    reference_id BIGINT UNSIGNED NULL,

    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    description VARCHAR(255) NULL,

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_customer_ledger_customer (customer_id),
    INDEX idx_customer_ledger_branch (branch_id),
    INDEX idx_customer_ledger_date (transaction_date),
    INDEX idx_customer_ledger_reference (reference_id),

    CONSTRAINT fk_customer_ledger_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id),

    CONSTRAINT fk_customer_ledger_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_customer_ledger_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 16. SUPPLIER LEDGER
-- ============================================================

CREATE TABLE supplier_ledger (
    ledger_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    supplier_id BIGINT UNSIGNED NOT NULL,

    branch_id BIGINT UNSIGNED NULL,

    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    transaction_type ENUM(
        'OPENING',
        'PURCHASE',
        'PAYMENT',
        'PURCHASE_RETURN',
        'ADJUSTMENT'
    ) NOT NULL,

    reference_id BIGINT UNSIGNED NULL,

    debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    description VARCHAR(255) NULL,

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_supplier_ledger_supplier (supplier_id),
    INDEX idx_supplier_ledger_branch (branch_id),
    INDEX idx_supplier_ledger_date (transaction_date),

    CONSTRAINT fk_supplier_ledger_supplier
        FOREIGN KEY (supplier_id)
        REFERENCES suppliers(supplier_id),

    CONSTRAINT fk_supplier_ledger_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_supplier_ledger_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 17. EXPENSES
-- ============================================================

CREATE TABLE expenses (
    expense_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    branch_id BIGINT UNSIGNED NOT NULL,

    expense_category VARCHAR(100) NOT NULL,

    amount DECIMAL(15,2) NOT NULL,

    description VARCHAR(255) NULL,

    expense_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_expenses_branch (branch_id),
    INDEX idx_expenses_category (expense_category),
    INDEX idx_expenses_date (expense_date),

    CONSTRAINT fk_expenses_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_expenses_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 18. STOCK TRANSFERS
-- ============================================================

CREATE TABLE stock_transfers (
    transfer_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    transfer_no VARCHAR(50) NOT NULL UNIQUE,

    from_branch_id BIGINT UNSIGNED NOT NULL,
    to_branch_id BIGINT UNSIGNED NOT NULL,

    status ENUM(
        'PENDING',
        'COMPLETED',
        'CANCELLED'
    ) NOT NULL DEFAULT 'PENDING',

    created_by BIGINT UNSIGNED NOT NULL,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    completed_at DATETIME NULL,

    INDEX idx_transfer_from (from_branch_id),
    INDEX idx_transfer_to (to_branch_id),
    INDEX idx_transfer_status (status),
    INDEX idx_transfer_date (created_at),

    CONSTRAINT fk_transfer_from_branch
        FOREIGN KEY (from_branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_transfer_to_branch
        FOREIGN KEY (to_branch_id)
        REFERENCES branches(branch_id),

    CONSTRAINT fk_transfer_user
        FOREIGN KEY (created_by)
        REFERENCES users(user_id)
) ENGINE=InnoDB;


-- ============================================================
-- 19. STOCK TRANSFER ITEMS
-- ============================================================

CREATE TABLE stock_transfer_items (
    transfer_item_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    transfer_id BIGINT UNSIGNED NOT NULL,

    product_id BIGINT UNSIGNED NOT NULL,

    quantity DECIMAL(15,3) NOT NULL,

    unit_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,

    INDEX idx_transfer_items_transfer (transfer_id),
    INDEX idx_transfer_items_product (product_id),

    CONSTRAINT fk_transfer_items_transfer
        FOREIGN KEY (transfer_id)
        REFERENCES stock_transfers(transfer_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_transfer_items_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
) ENGINE=InnoDB;


-- ============================================================
-- 20. SETTINGS
-- ============================================================

CREATE TABLE settings (
    setting_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) NOT NULL UNIQUE,

    setting_value TEXT NULL,

    description VARCHAR(255) NULL,

    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE branch_stock (
    branch_stock_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    branch_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,

    current_stock DECIMAL(15,3) NOT NULL DEFAULT 0.000,

    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_branch_product (
        branch_id,
        product_id
    ),

    INDEX idx_branch_stock_branch (
        branch_id
    ),

    INDEX idx_branch_stock_product (
        product_id
    ),

    CONSTRAINT fk_branch_stock_branch
        FOREIGN KEY (branch_id)
        REFERENCES branches(branch_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_branch_stock_product
        FOREIGN KEY (product_id)
        REFERENCES products(product_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;