-- STOCKFLOW - Hệ thống quản lý kho
-- MySQL 8.x

DROP DATABASE IF EXISTS stockflow_db;
CREATE DATABASE stockflow_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE stockflow_db;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP VIEW IF EXISTS vw_stock_card;
DROP VIEW IF EXISTS vw_inventory_summary;

DROP TABLE IF EXISTS debt_ledger_entries;
DROP TABLE IF EXISTS debt_opening_balances;
DROP TABLE IF EXISTS vat_invoice_details;
DROP TABLE IF EXISTS vat_invoices;
DROP TABLE IF EXISTS inventory_transactions;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS adjustment_details;
DROP TABLE IF EXISTS adjustment_receipts;
DROP TABLE IF EXISTS stock_check_details;
DROP TABLE IF EXISTS stock_checks;
DROP TABLE IF EXISTS transfer_details;
DROP TABLE IF EXISTS transfer_receipts;
DROP TABLE IF EXISTS export_details;
DROP TABLE IF EXISTS export_receipts;
DROP TABLE IF EXISTS import_details;
DROP TABLE IF EXISTS import_receipts;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS warehouses;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS audit_logs;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(120) UNIQUE,
    phone VARCHAR(30),
    role ENUM('ADMIN', 'WAREHOUSE_STAFF') NOT NULL DEFAULT 'WAREHOUSE_STAFF',
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  audit_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(40) NOT NULL,
  resource VARCHAR(80) NOT NULL,
  resource_id VARCHAR(80) NULL,
  method VARCHAR(10) NOT NULL,
  path VARCHAR(255) NOT NULL,
  status_code SMALLINT NOT NULL,
  ip_address VARCHAR(64) NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_logs_created_at (created_at),
  INDEX idx_audit_logs_user_id (user_id),
  INDEX idx_audit_logs_resource (resource),
  FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE warehouses (
    warehouse_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_name VARCHAR(120) NOT NULL,
    address VARCHAR(255),
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_code VARCHAR(30) NOT NULL UNIQUE,
    category_name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE units (
    unit_id INT AUTO_INCREMENT PRIMARY KEY,
    unit_code VARCHAR(20) NOT NULL UNIQUE,
    unit_name VARCHAR(40) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_code VARCHAR(40) NOT NULL UNIQUE,
    product_name VARCHAR(160) NOT NULL,
    category_id INT NOT NULL,
    unit_id INT NOT NULL,
    minimum_stock DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    import_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    export_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    description VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(category_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_products_unit FOREIGN KEY (unit_id) REFERENCES units(unit_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_products_prices CHECK (minimum_stock >= 0 AND import_price >= 0 AND export_price >= 0)
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    supplier_id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_code VARCHAR(40) NOT NULL UNIQUE,
    supplier_name VARCHAR(160) NOT NULL,
    tax_code VARCHAR(30),
    contact_name VARCHAR(120),
    phone VARCHAR(30),
    email VARCHAR(120),
    address VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_code VARCHAR(40) NOT NULL UNIQUE,
    customer_name VARCHAR(160) NOT NULL,
    tax_code VARCHAR(30),
    contact_name VARCHAR(120),
    phone VARCHAR(30),
    email VARCHAR(120),
    address VARCHAR(255),
    status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE import_receipts (
    import_id INT AUTO_INCREMENT PRIMARY KEY,
    import_code VARCHAR(40) NOT NULL UNIQUE,
    import_type ENUM('PURCHASE', 'FINISHED_GOODS', 'CUSTOMER_RETURN', 'OTHER') NOT NULL DEFAULT 'PURCHASE',
    warehouse_id INT NOT NULL,
    supplier_id INT,
    customer_id INT,
    source_export_id INT,
    created_by INT NOT NULL,
    delivery_person VARCHAR(120),
    source_name VARCHAR(160),
    import_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_import_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_import_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE import_details (
    import_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    import_id INT NOT NULL,
    product_id INT NOT NULL,
    document_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    import_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(16,2) GENERATED ALWAYS AS (actual_quantity * unit_price) STORED,
    note VARCHAR(255),
    CONSTRAINT fk_import_detail_receipt FOREIGN KEY (import_id) REFERENCES import_receipts(import_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_import_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_import_detail_qty CHECK (document_quantity > 0 AND actual_quantity > 0 AND unit_price >= 0)
) ENGINE=InnoDB;

CREATE TABLE export_receipts (
    export_id INT AUTO_INCREMENT PRIMARY KEY,
    export_code VARCHAR(40) NOT NULL UNIQUE,
    export_type ENUM('SALE', 'SUPPLIER_RETURN', 'INTERNAL_USE', 'DISPOSAL', 'OTHER') NOT NULL DEFAULT 'SALE',
    warehouse_id INT NOT NULL,
    customer_id INT,
    supplier_id INT,
    source_import_id INT,
    created_by INT NOT NULL,
    receiver_name VARCHAR(120),
    receiver_department VARCHAR(120),
    export_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_export_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_export_source_import FOREIGN KEY (source_import_id) REFERENCES import_receipts(import_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_export_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE export_details (
    export_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    export_id INT NOT NULL,
    product_id INT NOT NULL,
    requested_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    export_price DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    line_total DECIMAL(16,2) GENERATED ALWAYS AS (actual_quantity * unit_price) STORED,
    note VARCHAR(255),
    CONSTRAINT fk_export_detail_receipt FOREIGN KEY (export_id) REFERENCES export_receipts(export_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_export_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_export_detail_qty CHECK (requested_quantity > 0 AND actual_quantity > 0 AND unit_price >= 0)
) ENGINE=InnoDB;

ALTER TABLE import_receipts
    ADD CONSTRAINT fk_import_source_export FOREIGN KEY (source_export_id) REFERENCES export_receipts(export_id)
        ON UPDATE CASCADE ON DELETE SET NULL;

CREATE TABLE transfer_receipts (
    transfer_id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_code VARCHAR(40) NOT NULL UNIQUE,
    from_warehouse_id INT NOT NULL,
    to_warehouse_id INT NOT NULL,
    created_by INT NOT NULL,
    carrier_name VARCHAR(120),
    transfer_date DATE NOT NULL,
    reason VARCHAR(255),
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_transfer_from_warehouse FOREIGN KEY (from_warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_transfer_to_warehouse FOREIGN KEY (to_warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_transfer_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_transfer_different_warehouses CHECK (from_warehouse_id <> to_warehouse_id)
) ENGINE=InnoDB;

CREATE TABLE transfer_details (
    transfer_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_id INT NOT NULL,
    product_id INT NOT NULL,
    requested_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    note VARCHAR(255),
    CONSTRAINT fk_transfer_detail_receipt FOREIGN KEY (transfer_id) REFERENCES transfer_receipts(transfer_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_transfer_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_transfer_detail_qty CHECK (requested_quantity > 0 AND actual_quantity > 0)
) ENGINE=InnoDB;

CREATE TABLE stock_checks (
    check_id INT AUTO_INCREMENT PRIMARY KEY,
    check_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    created_by INT NOT NULL,
    approved_by INT,
    check_date DATE NOT NULL,
    note VARCHAR(255),
    status ENUM('DRAFT', 'APPROVED', 'ADJUSTED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_check_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_check_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_check_approved_by FOREIGN KEY (approved_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE stock_check_details (
    check_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    check_id INT NOT NULL,
    product_id INT NOT NULL,
    system_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    system_damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    system_obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    good_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255),
    UNIQUE KEY uq_stock_check_product (check_id, product_id),
    CONSTRAINT fk_check_detail_check FOREIGN KEY (check_id) REFERENCES stock_checks(check_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_check_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_stock_check_values CHECK (
        system_quantity >= 0 AND actual_quantity >= 0 AND
        system_damaged_quantity >= 0 AND system_obsolete_quantity >= 0 AND
        good_quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND
        system_damaged_quantity + system_obsolete_quantity <= system_quantity AND
        good_quantity + damaged_quantity + obsolete_quantity = actual_quantity
    )
) ENGINE=InnoDB;

CREATE TABLE adjustment_receipts (
    adjustment_id INT AUTO_INCREMENT PRIMARY KEY,
    adjustment_code VARCHAR(40) NOT NULL UNIQUE,
    warehouse_id INT NOT NULL,
    check_id INT,
    created_by INT NOT NULL,
    approved_by INT,
    adjustment_date DATE NOT NULL,
    reason VARCHAR(255) NOT NULL,
    note VARCHAR(255),
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_adjustment_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_adjustment_check FOREIGN KEY (check_id) REFERENCES stock_checks(check_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_adjustment_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_adjustment_approved_by FOREIGN KEY (approved_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE adjustment_details (
    adjustment_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    adjustment_id INT NOT NULL,
    product_id INT NOT NULL,
    system_quantity DECIMAL(14,2) NOT NULL,
    actual_quantity DECIMAL(14,2) NOT NULL,
    quantity_change DECIMAL(14,2) NOT NULL,
    damaged_change DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_change DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    note VARCHAR(255),
    UNIQUE KEY uq_adjustment_product (adjustment_id, product_id),
    CONSTRAINT fk_adjustment_detail_receipt FOREIGN KEY (adjustment_id) REFERENCES adjustment_receipts(adjustment_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_adjustment_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_adjustment_values CHECK (
        system_quantity >= 0 AND actual_quantity >= 0 AND
        quantity_change = actual_quantity - system_quantity
    )
) ENGINE=InnoDB;

CREATE TABLE inventory (
    inventory_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    damaged_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    obsolete_quantity DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_inventory_warehouse_product (warehouse_id, product_id),
    CONSTRAINT fk_inventory_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_inventory_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_inventory_values CHECK (
        quantity >= 0 AND damaged_quantity >= 0 AND obsolete_quantity >= 0 AND
        damaged_quantity + obsolete_quantity <= quantity
    )
) ENGINE=InnoDB;

CREATE TABLE inventory_transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    warehouse_id INT NOT NULL,
    product_id INT NOT NULL,
    created_by INT,
    transaction_type ENUM('IMPORT', 'EXPORT', 'TRANSFER_OUT', 'TRANSFER_IN', 'ADJUSTMENT', 'DAMAGE_ADJUSTMENT', 'OBSOLETE_ADJUSTMENT') NOT NULL,
    reference_type VARCHAR(40) NOT NULL,
    reference_id INT NOT NULL,
    quantity_before DECIMAL(14,2) NOT NULL,
    quantity_change DECIMAL(14,2) NOT NULL,
    quantity_after DECIMAL(14,2) NOT NULL,
    transaction_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    note VARCHAR(255),
    CONSTRAINT fk_transaction_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT fk_transaction_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT chk_transaction_quantities CHECK (quantity_before >= 0 AND quantity_after >= 0)
) ENGINE=InnoDB;

CREATE TABLE vat_invoices (
    invoice_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(60) NOT NULL,
    invoice_type ENUM('INPUT', 'OUTPUT') NOT NULL,
    invoice_date DATE NOT NULL,
    supplier_id INT,
    customer_id INT,
    import_id INT,
    export_id INT,
    seller_name VARCHAR(160),
    seller_tax_code VARCHAR(30),
    buyer_name VARCHAR(160),
    buyer_tax_code VARCHAR(30),
    subtotal DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    status ENUM('DRAFT', 'ISSUED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_vat_invoice_type_number (invoice_type, invoice_number),
    CONSTRAINT fk_vat_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vat_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_vat_import FOREIGN KEY (import_id) REFERENCES import_receipts(import_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_vat_export FOREIGN KEY (export_id) REFERENCES export_receipts(export_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_vat_user FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_vat_amounts CHECK (subtotal >= 0 AND vat_rate >= 0 AND vat_rate <= 100 AND vat_amount >= 0 AND total_amount >= 0),
    CONSTRAINT chk_vat_reference CHECK (
        (invoice_type = 'INPUT' AND import_id IS NOT NULL AND export_id IS NULL) OR
        (invoice_type = 'OUTPUT' AND export_id IS NOT NULL AND import_id IS NULL)
    )
) ENGINE=InnoDB;

CREATE TABLE vat_invoice_details (
    invoice_detail_id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity DECIMAL(14,2) NOT NULL,
    unit_price DECIMAL(14,2) NOT NULL,
    subtotal DECIMAL(16,2) NOT NULL,
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    vat_amount DECIMAL(16,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(16,2) NOT NULL,
    CONSTRAINT fk_vat_detail_invoice FOREIGN KEY (invoice_id) REFERENCES vat_invoices(invoice_id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_vat_detail_product FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT chk_vat_detail_values CHECK (quantity > 0 AND unit_price >= 0 AND subtotal >= 0 AND vat_rate >= 0 AND vat_rate <= 100 AND vat_amount >= 0 AND total_amount >= 0)
) ENGINE=InnoDB;

-- Debt reporting foundation.  Monetary values stay in DECIMAL columns so
-- opening balances and manual ledger postings can be aggregated exactly in
-- SQL without JavaScript floating-point arithmetic.
CREATE TABLE debt_opening_balances (
    opening_balance_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fiscal_year SMALLINT UNSIGNED NOT NULL,
    effective_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    budget_chapter VARCHAR(20) NULL,
    budget_type VARCHAR(20) NULL,
    budget_section VARCHAR(20) NULL,
    budget_item VARCHAR(20) NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    reference_code VARCHAR(100) NULL,
    note VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    UNIQUE KEY uq_debt_opening_year_partner_category (fiscal_year, partner_key, category),
    KEY idx_debt_opening_partner_date (partner_type, customer_id, supplier_id, effective_date),
    KEY idx_debt_opening_status_date (status, effective_date),
    KEY idx_debt_opening_category (partner_type, category),
    CONSTRAINT fk_debt_opening_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_opening_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_opening_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_opening_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_opening_year CHECK (fiscal_year BETWEEN 1900 AND 9999),
    CONSTRAINT chk_debt_opening_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_opening_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB;

CREATE TABLE debt_ledger_entries (
    debt_entry_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_date DATE NOT NULL,
    partner_type ENUM('CUSTOMER', 'SUPPLIER') NOT NULL,
    customer_id INT NULL,
    supplier_id INT NULL,
    category VARCHAR(32) NOT NULL,
    debit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    credit_amount DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    entry_source ENUM('MANUAL', 'INVOICE', 'PAYMENT', 'ADJUSTMENT') NOT NULL DEFAULT 'MANUAL',
    reference_type VARCHAR(40) NULL,
    reference_id BIGINT UNSIGNED NULL,
    reference_code VARCHAR(100) NULL,
    description VARCHAR(500) NULL,
    status ENUM('DRAFT', 'CONFIRMED', 'CANCELLED') NOT NULL DEFAULT 'DRAFT',
    created_by INT NOT NULL,
    updated_by INT NULL,
    confirmed_by INT NULL,
    confirmed_at DATETIME NULL,
    cancelled_by INT NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    partner_key VARCHAR(80) GENERATED ALWAYS AS (
        CASE
            WHEN partner_type = 'CUSTOMER' THEN CONCAT('CUSTOMER:', customer_id)
            ELSE CONCAT('SUPPLIER:', supplier_id)
        END
    ) STORED,
    KEY idx_debt_ledger_partner_date (partner_type, customer_id, supplier_id, entry_date),
    KEY idx_debt_ledger_status_date (status, entry_date),
    KEY idx_debt_ledger_category (partner_type, category),
    KEY idx_debt_ledger_reference (reference_type, reference_id),
    CONSTRAINT fk_debt_ledger_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_created_by FOREIGN KEY (created_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_updated_by FOREIGN KEY (updated_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE SET NULL,
    CONSTRAINT fk_debt_ledger_confirmed_by FOREIGN KEY (confirmed_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT fk_debt_ledger_cancelled_by FOREIGN KEY (cancelled_by) REFERENCES users(user_id)
        ON UPDATE RESTRICT ON DELETE RESTRICT,
    CONSTRAINT chk_debt_ledger_partner CHECK (
        (partner_type = 'CUSTOMER' AND customer_id IS NOT NULL AND supplier_id IS NULL AND category IN ('TRADE_RECEIVABLE', 'RESERVE_CAPITAL', 'OTHER_RECEIVABLE', 'SHORTFALL')) OR
        (partner_type = 'SUPPLIER' AND supplier_id IS NOT NULL AND customer_id IS NULL AND category IN ('TRADE_PAYABLE', 'INTERNAL', 'EXTERNAL', 'OTHER_PAYABLE'))
    ),
    CONSTRAINT chk_debt_ledger_amounts CHECK (
        debit_amount >= 0 AND credit_amount >= 0 AND
        ((debit_amount > 0 AND credit_amount = 0) OR (credit_amount > 0 AND debit_amount = 0))
    ),
    CONSTRAINT chk_debt_ledger_reference CHECK (reference_id IS NULL OR reference_type IS NOT NULL),
    CONSTRAINT chk_debt_ledger_status_audit CHECK (
        (status <> 'CONFIRMED' OR (confirmed_by IS NOT NULL AND confirmed_at IS NOT NULL)) AND
        (status <> 'CANCELLED' OR (cancelled_by IS NOT NULL AND cancelled_at IS NOT NULL))
    )
) ENGINE=InnoDB;

CREATE INDEX idx_products_name ON products(product_name);
CREATE INDEX idx_suppliers_name ON suppliers(supplier_name);
CREATE INDEX idx_customers_name ON customers(customer_name);
CREATE INDEX idx_import_date ON import_receipts(import_date);
CREATE INDEX idx_export_date ON export_receipts(export_date);
CREATE INDEX idx_transfer_date ON transfer_receipts(transfer_date);
CREATE INDEX idx_check_date ON stock_checks(check_date);
CREATE INDEX idx_adjustment_date ON adjustment_receipts(adjustment_date);
CREATE INDEX idx_transaction_date ON inventory_transactions(transaction_date);
CREATE INDEX idx_transaction_reference ON inventory_transactions(reference_type, reference_id);
CREATE INDEX idx_vat_invoice_date ON vat_invoices(invoice_date);
CREATE INDEX idx_debt_opening_effective_date ON debt_opening_balances(effective_date);
CREATE INDEX idx_debt_ledger_entry_date ON debt_ledger_entries(entry_date);

CREATE OR REPLACE VIEW vw_inventory_summary AS
SELECT i.inventory_id, w.warehouse_id, w.warehouse_code, w.warehouse_name, p.product_id, p.product_code, p.product_name,
       c.category_name, u.unit_name, u.symbol AS unit_symbol, i.quantity, i.damaged_quantity, i.obsolete_quantity,
       i.quantity - i.damaged_quantity - i.obsolete_quantity AS available_quantity, p.minimum_stock,
       CASE WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity <= 0 THEN 'OUT_OF_STOCK'
            WHEN i.quantity - i.damaged_quantity - i.obsolete_quantity <= p.minimum_stock THEN 'LOW_STOCK'
            ELSE 'IN_STOCK' END AS stock_status
FROM inventory i JOIN warehouses w ON w.warehouse_id=i.warehouse_id JOIN products p ON p.product_id=i.product_id
JOIN categories c ON c.category_id=p.category_id JOIN units u ON u.unit_id=p.unit_id;

CREATE OR REPLACE VIEW vw_stock_card AS
SELECT t.transaction_id, t.warehouse_id, w.warehouse_name, t.product_id, p.product_code, p.product_name,
       t.transaction_date, t.transaction_type, t.reference_type, t.reference_id,
       CASE WHEN t.quantity_change > 0 THEN t.quantity_change ELSE 0 END AS quantity_in,
       CASE WHEN t.quantity_change < 0 THEN ABS(t.quantity_change) ELSE 0 END AS quantity_out,
       t.quantity_before, t.quantity_after, t.note
FROM inventory_transactions t JOIN warehouses w ON w.warehouse_id=t.warehouse_id JOIN products p ON p.product_id=t.product_id;

SET FOREIGN_KEY_CHECKS = 1;