-- ERP1 Full Schema
CREATE DATABASE IF NOT EXISTS erp1 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE erp1;

SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE TABLE IF NOT EXISTS currencies (
    code CHAR(3) PRIMARY KEY,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    decimals TINYINT NOT NULL DEFAULT 2,
    is_base TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    cr_number VARCHAR(50) NULL,
    vat_number VARCHAR(50) NULL,
    address_en VARCHAR(255) NULL,
    address_ar VARCHAR(255) NULL,
    logo_path VARCHAR(255) NULL,
    base_currency_code CHAR(3) NOT NULL,
    timezone VARCHAR(50) NOT NULL DEFAULT 'Asia/Riyadh',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_companies_currency FOREIGN KEY (base_currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS branches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    address_en VARCHAR(255) NULL,
    address_ar VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    is_head TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_branches_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS exchange_rates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    currency_code CHAR(3) NOT NULL,
    rate DECIMAL(18,6) NOT NULL,
    effective_date DATE NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_rate (company_id, currency_code, effective_date),
    CONSTRAINT fk_rates_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_rates_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fiscal_years (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_fy_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fiscal_periods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    fiscal_year_id BIGINT UNSIGNED NOT NULL,
    period_no INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    is_closed TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_period (fiscal_year_id, period_no),
    CONSTRAINT fk_fp_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS taxes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    rate DECIMAL(5,2) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_taxes_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_sequences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    module VARCHAR(50) NOT NULL,
    prefix VARCHAR(20) NOT NULL,
    next_number BIGINT UNSIGNED NOT NULL DEFAULT 1,
    padding TINYINT NOT NULL DEFAULT 5,
    reset_period VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_doc_seq (company_id, branch_id, module),
    CONSTRAINT fk_docseq_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_docseq_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(50) NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_users_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_role (company_id, name),
    CONSTRAINT fk_roles_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    perm_key VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_rp_perm FOREIGN KEY (permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_ur_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_ur_role FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(128) PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    last_activity DATETIME NOT NULL,
    data MEDIUMTEXT NULL,
    CONSTRAINT fk_sessions_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_login_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    description VARCHAR(500) NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(150) NULL,
    address_en VARCHAR(255) NULL,
    address_ar VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_contacts_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    cr_number VARCHAR(50) NULL,
    vat_number VARCHAR(50) NULL,
    credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0,
    payment_terms_days INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_customers_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_customers_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    cr_number VARCHAR(50) NULL,
    vat_number VARCHAR(50) NULL,
    bank_name VARCHAR(150) NULL,
    iban VARCHAR(50) NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_vendors_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_vendors_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS entity_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(100) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ec_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_ec_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS product_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_prodcat_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_brands (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_prodbrand_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS units_of_measure (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    symbol VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_uom_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    sku VARCHAR(100) NOT NULL,
    barcode VARCHAR(100) NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    brand_id BIGINT UNSIGNED NULL,
    uom_id BIGINT UNSIGNED NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'product',
    purchase_price DECIMAL(18,2) NOT NULL DEFAULT 0,
    cost_price DECIMAL(18,2) NOT NULL DEFAULT 0,
    selling_price DECIMAL(18,2) NOT NULL DEFAULT 0,
    vat_rate DECIMAL(5,2) NOT NULL DEFAULT 15.00,
    is_stock TINYINT NOT NULL DEFAULT 1,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_product_sku (company_id, sku),
    CONSTRAINT fk_products_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES product_categories(id),
    CONSTRAINT fk_products_brand FOREIGN KEY (brand_id) REFERENCES product_brands(id),
    CONSTRAINT fk_products_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warehouses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    address_en VARCHAR(255) NULL,
    address_ar VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_wh_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_wh_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_moves (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    move_date DATETIME NOT NULL,
    qty DECIMAL(18,4) NOT NULL,
    unit_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    direction VARCHAR(10) NOT NULL,
    reference_type VARCHAR(50) NULL,
    reference_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_sm_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_sm_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_sm_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_sm_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stock_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_on_hand DECIMAL(18,4) NOT NULL DEFAULT 0,
    avg_cost DECIMAL(18,4) NOT NULL DEFAULT 0,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_stock (company_id, warehouse_id, product_id),
    CONSTRAINT fk_sb_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_sb_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    CONSTRAINT fk_sb_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfqs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    rfq_no VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    rfq_date DATE NOT NULL,
    required_date DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_rfq (company_id, rfq_no),
    CONSTRAINT fk_rfq_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_rfq_branch FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfq_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rfq_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    CONSTRAINT fk_rfqitem_rfq FOREIGN KEY (rfq_id) REFERENCES rfqs(id),
    CONSTRAINT fk_rfqitem_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_rfqitem_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfq_vendors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rfq_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_rfqvend_rfq FOREIGN KEY (rfq_id) REFERENCES rfqs(id),
    CONSTRAINT fk_rfqvend_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfq_vendor_quotes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rfq_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    quote_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'submitted',
    total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_rfqquote_rfq FOREIGN KEY (rfq_id) REFERENCES rfqs(id),
    CONSTRAINT fk_rfqquote_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rfq_vendor_quote_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rfq_vendor_quote_id BIGINT UNSIGNED NOT NULL,
    rfq_item_id BIGINT UNSIGNED NOT NULL,
    qty DECIMAL(18,4) NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_rfqquoteitem_quote FOREIGN KEY (rfq_vendor_quote_id) REFERENCES rfq_vendor_quotes(id),
    CONSTRAINT fk_rfqquoteitem_rfqitem FOREIGN KEY (rfq_item_id) REFERENCES rfq_items(id),
    CONSTRAINT fk_rfqquoteitem_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS purchase_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    po_no VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    po_date DATE NOT NULL,
    expected_date DATE NULL,
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    source_doc_type VARCHAR(20) NULL,
    source_rfq_id BIGINT UNSIGNED NULL,
    source_rfq_no VARCHAR(50) NULL,
    po_type VARCHAR(30) NULL,
    price_control_type VARCHAR(30) NULL,
    delivery_address_id VARCHAR(100) NULL,
    invoice_matching_type VARCHAR(30) NULL,
    acceptance_required TINYINT NOT NULL DEFAULT 0,
    receipt_required TINYINT NOT NULL DEFAULT 1,
    service_acceptance_required TINYINT NOT NULL DEFAULT 0,
    inspection_required TINYINT NOT NULL DEFAULT 0,
    tolerance_qty_pct DECIMAL(6,3) NULL,
    tolerance_price_pct DECIMAL(6,3) NULL,
    over_delivery_pct DECIMAL(6,3) NULL,
    invoice_currency_code CHAR(3) NULL,
    tax_code VARCHAR(50) NULL,
    vat_rate DECIMAL(6,3) NULL,
    withholding_tax_pct DECIMAL(6,3) NULL,
    invoice_matching_rule VARCHAR(50) NULL,
    retention_pct DECIMAL(6,3) NULL,
    milestone_billing TINYINT NOT NULL DEFAULT 0,
    advance_payment_pct DECIMAL(6,3) NULL,
    agreed_payment_terms VARCHAR(100) NULL,
    agreed_delivery_date DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_po (company_id, po_no),
    CONSTRAINT fk_po_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_po_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_po_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    CONSTRAINT fk_po_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
    CONSTRAINT fk_po_source_rfq FOREIGN KEY (source_rfq_id) REFERENCES rfqs(id),
    CONSTRAINT fk_po_invoice_currency FOREIGN KEY (invoice_currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    received_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
    uom_id BIGINT UNSIGNED NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    source_rfq_item_id BIGINT UNSIGNED NULL,
    final_unit_price DECIMAL(18,4) NULL,
    final_qty DECIMAL(18,4) NULL,
    final_currency_code CHAR(3) NULL,
    agreed_payment_terms VARCHAR(100) NULL,
    agreed_delivery_date DATE NULL,
    CONSTRAINT fk_poi_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id),
    CONSTRAINT fk_poi_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_poi_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id),
    CONSTRAINT fk_poi_tax FOREIGN KEY (tax_id) REFERENCES taxes(id),
    CONSTRAINT fk_poi_source_rfq_item FOREIGN KEY (source_rfq_item_id) REFERENCES rfq_items(id),
    CONSTRAINT fk_poi_final_currency FOREIGN KEY (final_currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS grns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    grn_no VARCHAR(50) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'received',
    grn_date DATE NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_grn (purchase_order_id, grn_no),
    CONSTRAINT fk_grn_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id),
    CONSTRAINT fk_grn_wh FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS grn_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    grn_id BIGINT UNSIGNED NOT NULL,
    purchase_order_item_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_received DECIMAL(18,4) NOT NULL,
    unit_cost DECIMAL(18,4) NOT NULL,
    lot_no VARCHAR(50) NULL,
    expiry_date DATE NULL,
    CONSTRAINT fk_grni_grn FOREIGN KEY (grn_id) REFERENCES grns(id),
    CONSTRAINT fk_grni_poi FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items(id),
    CONSTRAINT fk_grni_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendor_bills (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    bill_no VARCHAR(50) NOT NULL,
    bill_date DATE NOT NULL,
    due_date DATE NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_bill (company_id, bill_no),
    CONSTRAINT fk_bill_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_bill_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_bill_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    CONSTRAINT fk_bill_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vendor_bill_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vendor_bill_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_vbi_bill FOREIGN KEY (vendor_bill_id) REFERENCES vendor_bills(id),
    CONSTRAINT fk_vbi_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_vbi_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id),
    CONSTRAINT fk_vbi_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS debit_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    vendor_id BIGINT UNSIGNED NOT NULL,
    debit_no VARCHAR(50) NOT NULL,
    debit_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_debit (company_id, debit_no),
    CONSTRAINT fk_debit_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_debit_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
    CONSTRAINT fk_debit_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS debit_note_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    debit_note_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_dni_dn FOREIGN KEY (debit_note_id) REFERENCES debit_notes(id),
    CONSTRAINT fk_dni_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_dni_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    vendor_id BIGINT UNSIGNED NULL,
    expense_no VARCHAR(50) NOT NULL,
    expense_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    amount DECIMAL(18,2) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    account_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_expense (company_id, expense_no),
    CONSTRAINT fk_exp_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_exp_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_exp_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sales_quotations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    quote_no VARCHAR(50) NOT NULL,
    quote_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_squote (company_id, quote_no),
    CONSTRAINT fk_squote_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_squote_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_squote_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_squote_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_quotation_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sales_quotation_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_sqi_sq FOREIGN KEY (sales_quotation_id) REFERENCES sales_quotations(id),
    CONSTRAINT fk_sqi_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_sqi_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id),
    CONSTRAINT fk_sqi_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_sorder (company_id, order_no),
    CONSTRAINT fk_sorder_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_sorder_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_sorder_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_sorder_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sales_order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    delivered_qty DECIMAL(18,4) NOT NULL DEFAULT 0,
    uom_id BIGINT UNSIGNED NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_soi_so FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id),
    CONSTRAINT fk_soi_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_soi_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id),
    CONSTRAINT fk_soi_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    delivery_no VARCHAR(50) NOT NULL,
    delivery_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_delivery (company_id, delivery_no),
    CONSTRAINT fk_dn_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_dn_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_dn_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS delivery_note_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    delivery_note_id BIGINT UNSIGNED NOT NULL,
    sales_order_item_id BIGINT UNSIGNED NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    qty_delivered DECIMAL(18,4) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    CONSTRAINT fk_dni_dn FOREIGN KEY (delivery_note_id) REFERENCES delivery_notes(id),
    CONSTRAINT fk_dni_soi FOREIGN KEY (sales_order_item_id) REFERENCES sales_order_items(id),
    CONSTRAINT fk_dni_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_dni_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    invoice_no VARCHAR(50) NOT NULL,
    invoice_date DATE NOT NULL,
    invoice_time TIME NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    zatca_uuid VARCHAR(50) NULL,
    zatca_hash VARCHAR(255) NULL,
    zatca_xml LONGTEXT NULL,
    zatca_qr_base64 TEXT NULL,
    zatca_status VARCHAR(20) NULL,
    invoice_type_code VARCHAR(10) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_invoice (company_id, invoice_no),
    CONSTRAINT fk_inv_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_inv_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_inv_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    uom_id BIGINT UNSIGNED NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_invi_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id),
    CONSTRAINT fk_invi_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_invi_uom FOREIGN KEY (uom_id) REFERENCES units_of_measure(id),
    CONSTRAINT fk_invi_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    credit_no VARCHAR(50) NOT NULL,
    credit_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    subtotal DECIMAL(18,2) NOT NULL DEFAULT 0,
    tax_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    grand_total DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_credit (company_id, credit_no),
    CONSTRAINT fk_credit_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_credit_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
    CONSTRAINT fk_credit_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_note_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    credit_note_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    qty DECIMAL(18,4) NOT NULL,
    unit_price DECIMAL(18,4) NOT NULL,
    tax_id BIGINT UNSIGNED NULL,
    line_total DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_cni_cn FOREIGN KEY (credit_note_id) REFERENCES credit_notes(id),
    CONSTRAINT fk_cni_product FOREIGN KEY (product_id) REFERENCES products(id),
    CONSTRAINT fk_cni_tax FOREIGN KEY (tax_id) REFERENCES taxes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS bank_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    bank_name VARCHAR(150) NULL,
    account_no VARCHAR(100) NULL,
    iban VARCHAR(50) NULL,
    currency_code CHAR(3) NOT NULL,
    gl_account_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_bank_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_bank_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    party_type VARCHAR(20) NOT NULL,
    party_id BIGINT UNSIGNED NOT NULL,
    payment_no VARCHAR(50) NOT NULL,
    payment_date DATE NOT NULL,
    direction VARCHAR(10) NOT NULL,
    method VARCHAR(20) NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
    status VARCHAR(20) NOT NULL DEFAULT 'posted',
    reference VARCHAR(100) NULL,
    bank_account_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_payment (company_id, payment_no),
    CONSTRAINT fk_pay_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_pay_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_pay_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
    CONSTRAINT fk_pay_bank FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_allocations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payment_id BIGINT UNSIGNED NOT NULL,
    document_type VARCHAR(50) NOT NULL,
    document_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_pa_payment FOREIGN KEY (payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_reconciliations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bank_account_id BIGINT UNSIGNED NOT NULL,
    statement_date DATE NOT NULL,
    opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    closing_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_br_bank FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_reconciliation_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bank_reconciliation_id BIGINT UNSIGNED NOT NULL,
    payment_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    amount DECIMAL(18,2) NOT NULL,
    CONSTRAINT fk_bri_br FOREIGN KEY (bank_reconciliation_id) REFERENCES bank_reconciliations(id),
    CONSTRAINT fk_bri_payment FOREIGN KEY (payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    type VARCHAR(20) NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    is_posting TINYINT NOT NULL DEFAULT 1,
    currency_code CHAR(3) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_account (company_id, code),
    CONSTRAINT fk_acc_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_acc_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
    CONSTRAINT fk_acc_parent FOREIGN KEY (parent_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cost_centers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_cc (company_id, code),
    CONSTRAINT fk_cc_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    entry_no VARCHAR(50) NOT NULL,
    entry_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    source_module VARCHAR(50) NULL,
    source_id BIGINT UNSIGNED NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'posted',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_je (company_id, entry_no),
    CONSTRAINT fk_je_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_je_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_je_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS journal_lines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    journal_entry_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    cost_center_id BIGINT UNSIGNED NULL,
    description VARCHAR(255) NULL,
    debit DECIMAL(18,2) NOT NULL DEFAULT 0,
    credit DECIMAL(18,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_jl_je FOREIGN KEY (journal_entry_id) REFERENCES journal_entries(id),
    CONSTRAINT fk_jl_acc FOREIGN KEY (account_id) REFERENCES accounts(id),
    CONSTRAINT fk_jl_cc FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS departments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_dept_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS designations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_desig_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NULL,
    contact_id BIGINT UNSIGNED NULL,
    name_en VARCHAR(200) NOT NULL,
    name_ar VARCHAR(200) NOT NULL,
    national_id VARCHAR(50) NULL,
    iqama_no VARCHAR(50) NULL,
    department_id BIGINT UNSIGNED NULL,
    designation_id BIGINT UNSIGNED NULL,
    hire_date DATE NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_emp_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_emp_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_emp_contact FOREIGN KEY (contact_id) REFERENCES contacts(id),
    CONSTRAINT fk_emp_dept FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_emp_desig FOREIGN KEY (designation_id) REFERENCES designations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_salary_components (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    component_type VARCHAR(20) NOT NULL,
    name VARCHAR(100) NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    is_taxable TINYINT NOT NULL DEFAULT 1,
    CONSTRAINT fk_esc_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    run_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pr_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payroll_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    payroll_run_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    gross_amount DECIMAL(18,2) NOT NULL,
    deduction_amount DECIMAL(18,2) NOT NULL,
    net_amount DECIMAL(18,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pi_pr FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id),
    CONSTRAINT fk_pi_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leave_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lt_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leave_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    leave_type_id BIGINT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lr_emp FOREIGN KEY (employee_id) REFERENCES employees(id),
    CONSTRAINT fk_lr_type FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_travel (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    purpose VARCHAR(255) NULL,
    country VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    ticket_no VARCHAR(100) NULL,
    ticket_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_travel_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id BIGINT UNSIGNED NOT NULL,
    attachment_id BIGINT UNSIGNED NULL,
    doc_type VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ed_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    vehicle_no VARCHAR(50) NOT NULL,
    plate_no VARCHAR(50) NOT NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(100) NULL,
    year INT NULL,
    chassis_no VARCHAR(100) NULL,
    engine_no VARCHAR(100) NULL,
    assigned_employee_id BIGINT UNSIGNED NULL,
    department_id BIGINT UNSIGNED NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_vehicle (company_id, vehicle_no),
    CONSTRAINT fk_vehicle_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_vehicle_emp FOREIGN KEY (assigned_employee_id) REFERENCES employees(id),
    CONSTRAINT fk_vehicle_dept FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_maintenance (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    maintenance_type VARCHAR(100) NOT NULL,
    description VARCHAR(255) NULL,
    maintenance_date DATE NOT NULL,
    cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    odometer INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_vm_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_warranties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    warranty_type VARCHAR(100) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    details VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_vw_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    expense_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    amount DECIMAL(18,2) NOT NULL,
    account_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ve_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_reminders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    reminder_type VARCHAR(100) NOT NULL,
    reminder_date DATE NOT NULL,
    notes VARCHAR(255) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_vr_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NULL,
    is_shared TINYINT NOT NULL DEFAULT 0,
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    due_date DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_notes_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT NULL,
    priority VARCHAR(20) NOT NULL DEFAULT 'normal',
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    due_date DATE NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_tasks_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_tasks_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS note_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    note_id BIGINT UNSIGNED NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_nl_note FOREIGN KEY (note_id) REFERENCES notes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS task_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT UNSIGNED NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_tl_task FOREIGN KEY (task_id) REFERENCES tasks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    uploaded_by BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_att_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_att_user FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachment_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attachment_id BIGINT UNSIGNED NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    CONSTRAINT fk_al_attachment FOREIGN KEY (attachment_id) REFERENCES attachments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS zatca_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    environment VARCHAR(20) NOT NULL DEFAULT 'sandbox',
    seller_name VARCHAR(200) NOT NULL,
    vat_number VARCHAR(50) NOT NULL,
    address VARCHAR(255) NULL,
    certificate_path VARCHAR(255) NULL,
    private_key_path VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    CONSTRAINT fk_zs_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS zatca_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id BIGINT UNSIGNED NOT NULL,
    request_payload LONGTEXT NULL,
    response_payload LONGTEXT NULL,
    status VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_zl_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Deferred FK constraints
ALTER TABLE expenses
    ADD CONSTRAINT fk_exp_account FOREIGN KEY (account_id) REFERENCES accounts(id);

ALTER TABLE bank_accounts
    ADD CONSTRAINT fk_bank_gl FOREIGN KEY (gl_account_id) REFERENCES accounts(id);

ALTER TABLE vehicle_expenses
    ADD CONSTRAINT fk_ve_account FOREIGN KEY (account_id) REFERENCES accounts(id);

ALTER TABLE employee_documents
    ADD CONSTRAINT fk_ed_attachment FOREIGN KEY (attachment_id) REFERENCES attachments(id);

-- ERP1 Enterprise Expansion
-- Enterprise Expansion (2026-02-08)
USE erp1;

-- Companies
ALTER TABLE companies
  ADD COLUMN company_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_company_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN company_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE companies
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE companies
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE companies
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE companies
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_account_id BIGINT UNSIGNED NULL;

ALTER TABLE companies
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN secondary_currency_code CHAR(3) NULL,
  ADD COLUMN fiscal_year_start DATE NULL,
  ADD COLUMN fiscal_year_end DATE NULL,
  ADD COLUMN accounting_standard VARCHAR(50) NULL,
  ADD COLUMN chart_of_accounts_ref VARCHAR(100) NULL,
  ADD COLUMN cost_center_structure VARCHAR(100) NULL,
  ADD COLUMN profit_center_structure VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE companies
  ADD COLUMN gosi_no VARCHAR(100) NULL,
  ADD COLUMN gosi_office VARCHAR(100) NULL,
  ADD COLUMN labor_office_no VARCHAR(100) NULL,
  ADD COLUMN saudization_level VARCHAR(50) NULL,
  ADD COLUMN establishment_id VARCHAR(100) NULL,
  ADD COLUMN employee_capacity INT NULL;

ALTER TABLE companies
  ADD COLUMN warehouse_locations TEXT NULL,
  ADD COLUMN default_warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE companies
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN is_active TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE companies
  ADD COLUMN print_header_accent VARCHAR(20) NULL,
  ADD COLUMN print_header_logo_size INT NULL,
  ADD COLUMN print_header_show_logo TINYINT NOT NULL DEFAULT 1;

ALTER TABLE companies
  ADD CONSTRAINT fk_companies_parent FOREIGN KEY (parent_company_id) REFERENCES companies(id),
  ADD CONSTRAINT fk_companies_secondary_currency FOREIGN KEY (secondary_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_companies_default_wh FOREIGN KEY (default_warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_companies_default_bank FOREIGN KEY (default_bank_account_id) REFERENCES bank_accounts(id),
  ADD CONSTRAINT fk_companies_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code);
-- Customers
ALTER TABLE customers
  ADD COLUMN customer_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_customer_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN customer_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE customers
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE customers
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE customers
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN customer_segment VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer_name VARCHAR(150) NULL,
  ADD COLUMN accounting_standard VARCHAR(50) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_exposure DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN gosi_no VARCHAR(100) NULL,
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE customers
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE customers
  ADD CONSTRAINT fk_customers_parent FOREIGN KEY (parent_customer_id) REFERENCES customers(id),
  ADD CONSTRAINT fk_customers_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code);

-- Vendors
ALTER TABLE vendors
  ADD COLUMN vendor_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN vendor_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE vendors
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE vendors
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE vendors
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN default_bank_account_id BIGINT UNSIGNED NULL;

ALTER TABLE vendors
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN vendor_group VARCHAR(100) NULL,
  ADD COLUMN vendor_segment VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_exposure DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE vendors
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD CONSTRAINT fk_vendors_parent FOREIGN KEY (parent_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_vendors_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_vendors_default_bank FOREIGN KEY (default_bank_account_id) REFERENCES bank_accounts(id);
-- Contacts (Enterprise)
ALTER TABLE contacts
  ADD COLUMN uuid CHAR(36) NULL,
  ADD COLUMN contact_type VARCHAR(50) NULL,
  ADD COLUMN contact_role VARCHAR(100) NULL,
  ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN priority_level VARCHAR(20) NULL,
  ADD COLUMN confidentiality_level VARCHAR(20) NULL,
  ADD COLUMN first_name VARCHAR(100) NULL,
  ADD COLUMN middle_name VARCHAR(100) NULL,
  ADD COLUMN last_name VARCHAR(100) NULL,
  ADD COLUMN full_name VARCHAR(200) NULL,
  ADD COLUMN name_ar_full VARCHAR(200) NULL,
  ADD COLUMN gender VARCHAR(20) NULL,
  ADD COLUMN date_of_birth DATE NULL,
  ADD COLUMN nationality VARCHAR(100) NULL,
  ADD COLUMN preferred_language VARCHAR(50) NULL,
  ADD COLUMN title VARCHAR(20) NULL,
  ADD COLUMN national_id_number VARCHAR(100) NULL,
  ADD COLUMN iqama_number VARCHAR(100) NULL,
  ADD COLUMN passport_number VARCHAR(100) NULL,
  ADD COLUMN passport_issuing_country VARCHAR(100) NULL,
  ADD COLUMN passport_expiry_date DATE NULL,
  ADD COLUMN absher_registered TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN government_id_type VARCHAR(50) NULL,
  ADD COLUMN government_id_number VARCHAR(100) NULL,
  ADD COLUMN id_issuing_authority VARCHAR(100) NULL,
  ADD COLUMN id_expiry_date DATE NULL,
  ADD COLUMN kyc_verified TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN aml_checked TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN sanction_screened TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN pep_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN department VARCHAR(150) NULL,
  ADD COLUMN job_title VARCHAR(150) NULL,
  ADD COLUMN employment_type VARCHAR(50) NULL,
  ADD COLUMN start_date DATE NULL,
  ADD COLUMN end_date DATE NULL,
  ADD COLUMN is_primary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN reporting_manager_contact_id BIGINT UNSIGNED NULL,
  ADD COLUMN preferred_contact_method VARCHAR(50) NULL,
  ADD COLUMN preferred_contact_time VARCHAR(50) NULL,
  ADD COLUMN do_not_call TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN do_not_email TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN whatsapp_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN marketing_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN sms_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN authorized_signatory TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_limit_amount DECIMAL(18,2) NULL,
  ADD COLUMN approval_currency_code CHAR(3) NULL,
  ADD COLUMN payment_approval_authority TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN contract_signing_authority TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN bank_authorization_ref VARCHAR(150) NULL,
  ADD COLUMN user_id BIGINT UNSIGNED NULL,
  ADD COLUMN system_role VARCHAR(100) NULL,
  ADD COLUMN mfa_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN last_login_date DATETIME NULL,
  ADD COLUMN account_locked TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN relationship_type VARCHAR(50) NULL,
  ADD COLUMN account_owner_user_id BIGINT UNSIGNED NULL,
  ADD COLUMN assigned_sales_rep_id BIGINT UNSIGNED NULL,
  ADD COLUMN assigned_account_manager_id BIGINT UNSIGNED NULL,
  ADD COLUMN interaction_frequency VARCHAR(50) NULL,
  ADD COLUMN last_interaction_date DATE NULL,
  ADD COLUMN notes TEXT NULL,
  ADD COLUMN internal_comments TEXT NULL,
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN source_system VARCHAR(50) NULL,
  ADD COLUMN data_sync_status VARCHAR(50) NULL,
  ADD COLUMN record_status VARCHAR(20) NULL,
  ADD COLUMN active_flag TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN blocked_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN legal_hold_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blacklisted_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN data_retention_expiry_date DATE NULL;

ALTER TABLE contacts
  ADD CONSTRAINT fk_contacts_reporting_manager FOREIGN KEY (reporting_manager_contact_id) REFERENCES contacts(id),
  ADD CONSTRAINT fk_contacts_user FOREIGN KEY (user_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_account_owner FOREIGN KEY (account_owner_user_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_sales_rep FOREIGN KEY (assigned_sales_rep_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_account_manager FOREIGN KEY (assigned_account_manager_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_approval_currency FOREIGN KEY (approval_currency_code) REFERENCES currencies(code);

CREATE TABLE IF NOT EXISTS contact_phones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  phone_type VARCHAR(20) NOT NULL,
  country_code VARCHAR(10) NULL,
  phone_number VARCHAR(30) NOT NULL,
  extension VARCHAR(10) NULL,
  is_primary TINYINT NOT NULL DEFAULT 0,
  is_verified TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_phones_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_emails (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(150) NOT NULL,
  email_type VARCHAR(20) NULL,
  is_primary TINYINT NOT NULL DEFAULT 0,
  is_verified TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_emails_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_addresses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  address_type VARCHAR(50) NULL,
  is_ksa_address TINYINT NOT NULL DEFAULT 1,
  building_no VARCHAR(20) NULL,
  street_name VARCHAR(150) NULL,
  district VARCHAR(150) NULL,
  city VARCHAR(150) NULL,
  postal_code VARCHAR(20) NULL,
  additional_no VARCHAR(20) NULL,
  short_address VARCHAR(50) NULL,
  address_line1 VARCHAR(150) NULL,
  address_line2 VARCHAR(150) NULL,
  state_province VARCHAR(150) NULL,
  country VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_addresses_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer price agreements (contract rates)
CREATE TABLE IF NOT EXISTS customer_price_agreements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  currency_code CHAR(3) NOT NULL,
  unit_price DECIMAL(18,4) NOT NULL,
  start_date DATE NULL,
  end_date DATE NULL,
  contract_ref VARCHAR(100) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_cpa (company_id, customer_id, product_id, currency_code, start_date),
  CONSTRAINT fk_cpa_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_cpa_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_cpa_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_cpa_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- RFQ / Procurement expansion
ALTER TABLE rfqs
  ADD COLUMN rfq_type VARCHAR(30) NULL,
  ADD COLUMN rfq_version VARCHAR(20) NULL,
  ADD COLUMN reference_rfq_no VARCHAR(50) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN requesting_department VARCHAR(100) NULL,
  ADD COLUMN requester_id BIGINT UNSIGNED NULL,
  ADD COLUMN buyer_id BIGINT UNSIGNED NULL,
  ADD COLUMN approval_workflow_id VARCHAR(50) NULL,
  ADD COLUMN primary_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN vendor_contact_id BIGINT UNSIGNED NULL,
  ADD COLUMN sourcing_type VARCHAR(30) NULL,
  ADD COLUMN bid_submission_method VARCHAR(30) NULL,
  ADD COLUMN bid_currency_code CHAR(3) NULL,
  ADD COLUMN bid_exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
  ADD COLUMN rfq_issue_date DATE NULL,
  ADD COLUMN bid_submission_deadline DATE NULL,
  ADD COLUMN rfq_validity_date DATE NULL,
  ADD COLUMN expected_award_date DATE NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_location VARCHAR(150) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN price_basis VARCHAR(50) NULL,
  ADD COLUMN contract_reference VARCHAR(100) NULL,
  ADD COLUMN nda_reference VARCHAR(100) NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN technical_specs_ref VARCHAR(150) NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN po_type VARCHAR(30) NULL,
  ADD COLUMN price_control_type VARCHAR(30) NULL,
  ADD COLUMN delivery_address_id VARCHAR(100) NULL,
  ADD COLUMN invoice_matching_type VARCHAR(30) NULL,
  ADD COLUMN acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN receipt_required TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN service_acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN inspection_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN tolerance_qty_pct DECIMAL(6,3) NULL,
  ADD COLUMN tolerance_price_pct DECIMAL(6,3) NULL,
  ADD COLUMN over_delivery_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_currency_code CHAR(3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN withholding_tax_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_matching_rule VARCHAR(50) NULL,
  ADD COLUMN retention_pct DECIMAL(6,3) NULL,
  ADD COLUMN milestone_billing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN advance_payment_pct DECIMAL(6,3) NULL,
  ADD COLUMN budget_check TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN budget_reference VARCHAR(100) NULL,
  ADD COLUMN approval_matrix VARCHAR(100) NULL,
  ADD COLUMN risk_level VARCHAR(30) NULL,
  ADD COLUMN awarded_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN award_date DATE NULL,
  ADD COLUMN awarded_by BIGINT UNSIGNED NULL,
  ADD COLUMN award_notes TEXT NULL,
  ADD COLUMN rfq_closed_after_po TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN po_balance_remaining DECIMAL(18,2) NULL,
  ADD COLUMN invoiced_amount DECIMAL(18,2) NULL,
  ADD COLUMN grn_quantity DECIMAL(18,4) NULL,
  ADD COLUMN invoice_hold_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE rfqs
  ADD CONSTRAINT fk_rfq_requester FOREIGN KEY (requester_id) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_buyer FOREIGN KEY (buyer_id) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_primary_vendor FOREIGN KEY (primary_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_rfq_vendor_contact FOREIGN KEY (vendor_contact_id) REFERENCES contacts(id),
  ADD CONSTRAINT fk_rfq_awarded_vendor FOREIGN KEY (awarded_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_rfq_awarded_by FOREIGN KEY (awarded_by) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_bid_currency FOREIGN KEY (bid_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_rfq_invoice_currency FOREIGN KEY (invoice_currency_code) REFERENCES currencies(code);

ALTER TABLE rfq_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_code VARCHAR(100) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN manufacturer VARCHAR(100) NULL,
  ADD COLUMN estimated_unit_price DECIMAL(18,4) NULL,
  ADD COLUMN line_total DECIMAL(18,2) NULL,
  ADD COLUMN price_breaks TEXT NULL,
  ADD COLUMN discount_pct DECIMAL(6,3) NULL,
  ADD COLUMN discount_amount DECIMAL(18,2) NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN lead_time_days INT NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN vat_applicable TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_inclusive TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN gl_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN asset_no VARCHAR(100) NULL,
  ADD COLUMN country_of_origin VARCHAR(100) NULL,
  ADD COLUMN hs_code VARCHAR(50) NULL,
  ADD COLUMN warranty_period VARCHAR(50) NULL,
  ADD COLUMN compliance_cert_required TINYINT NOT NULL DEFAULT 0;

ALTER TABLE rfq_items
  ADD CONSTRAINT fk_rfqitem_gl FOREIGN KEY (gl_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_rfqitem_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE rfq_vendor_quotes
  ADD COLUMN quotation_currency_code CHAR(3) NULL,
  ADD COLUMN quotation_validity DATE NULL,
  ADD COLUMN delivery_lead_time_days INT NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN deviation_notes TEXT NULL,
  ADD COLUMN attachment_path VARCHAR(255) NULL,
  ADD COLUMN price_score DECIMAL(6,3) NULL,
  ADD COLUMN delivery_score DECIMAL(6,3) NULL,
  ADD COLUMN payment_term_score DECIMAL(6,3) NULL,
  ADD COLUMN total_score DECIMAL(6,3) NULL,
  ADD COLUMN rank_no INT NULL,
  ADD COLUMN technical_compliance TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN qa_approval TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN engineering_approval TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN award_status VARCHAR(20) NULL,
  ADD COLUMN award_date DATE NULL,
  ADD COLUMN award_notes TEXT NULL;

ALTER TABLE rfq_vendor_quotes
  ADD CONSTRAINT fk_rfqquote_currency FOREIGN KEY (quotation_currency_code) REFERENCES currencies(code);

ALTER TABLE rfq_vendor_quote_items
  ADD COLUMN quoted_qty DECIMAL(18,4) NULL,
  ADD COLUMN quoted_unit_price DECIMAL(18,4) NULL,
  ADD COLUMN quoted_total DECIMAL(18,2) NULL;

-- Sales expansion
ALTER TABLE sales_quotations
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN sales_org VARCHAR(100) NULL,
  ADD COLUMN distribution_channel VARCHAR(100) NULL,
  ADD COLUMN division VARCHAR(100) NULL,
  ADD COLUMN branch_location VARCHAR(100) NULL,
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN customer_vat_number VARCHAR(50) NULL,
  ADD COLUMN customer_cr_number VARCHAR(50) NULL,
  ADD COLUMN customer_national_address_id VARCHAR(100) NULL,
  ADD COLUMN quotation_validity_date DATE NULL,
  ADD COLUMN order_date DATE NULL,
  ADD COLUMN requested_delivery_date DATE NULL,
  ADD COLUMN confirmed_delivery_date DATE NULL,
  ADD COLUMN billing_date DATE NULL,
  ADD COLUMN price_list VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN open_quantity DECIMAL(18,4) NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL;

ALTER TABLE sales_quotations
  ADD CONSTRAINT fk_sq_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_sq_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE sales_quotation_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN delivered_qty DECIMAL(18,4) NULL,
  ADD COLUMN invoiced_qty DECIMAL(18,4) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN backorder_qty DECIMAL(18,4) NULL;

ALTER TABLE sales_quotation_items
  ADD CONSTRAINT fk_sqi_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_sqi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_sqi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE sales_orders
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN sales_org VARCHAR(100) NULL,
  ADD COLUMN distribution_channel VARCHAR(100) NULL,
  ADD COLUMN division VARCHAR(100) NULL,
  ADD COLUMN branch_location VARCHAR(100) NULL,
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN customer_vat_number VARCHAR(50) NULL,
  ADD COLUMN customer_cr_number VARCHAR(50) NULL,
  ADD COLUMN customer_national_address_id VARCHAR(100) NULL,
  ADD COLUMN quotation_validity_date DATE NULL,
  ADD COLUMN requested_delivery_date DATE NULL,
  ADD COLUMN confirmed_delivery_date DATE NULL,
  ADD COLUMN billing_date DATE NULL,
  ADD COLUMN price_list VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN open_quantity DECIMAL(18,4) NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL;

ALTER TABLE sales_orders
  ADD CONSTRAINT fk_so_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_so_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE sales_order_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN invoiced_qty DECIMAL(18,4) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN backorder_qty DECIMAL(18,4) NULL;

ALTER TABLE sales_order_items
  ADD CONSTRAINT fk_soi_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_soi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_soi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE delivery_notes
  ADD COLUMN delivery_status VARCHAR(20) NULL,
  ADD COLUMN pick_status VARCHAR(20) NULL,
  ADD COLUMN pack_status VARCHAR(20) NULL,
  ADD COLUMN goods_issue_date DATE NULL,
  ADD COLUMN carrier VARCHAR(100) NULL,
  ADD COLUMN tracking_number VARCHAR(100) NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL;

ALTER TABLE delivery_notes
  ADD CONSTRAINT fk_dn_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE delivery_note_items
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL;

ALTER TABLE delivery_note_items
  ADD CONSTRAINT fk_dni_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE invoices
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN due_date DATE NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL,
  ADD COLUMN invoice_type VARCHAR(30) NULL;

ALTER TABLE invoices
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN sales_org VARCHAR(100) NULL,
  ADD COLUMN distribution_channel VARCHAR(100) NULL,
  ADD COLUMN division VARCHAR(100) NULL,
  ADD COLUMN branch_location VARCHAR(100) NULL;

ALTER TABLE invoices
  ADD CONSTRAINT fk_inv_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_inv_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE invoice_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE invoice_items
  ADD CONSTRAINT fk_invi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_invi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

CREATE TABLE IF NOT EXISTS sales_pricing_conditions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(30) NOT NULL,
  document_id BIGINT UNSIGNED NOT NULL,
  line_id BIGINT UNSIGNED NULL,
  condition_type VARCHAR(50) NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_spc_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- HR expansion
ALTER TABLE employees
  ADD COLUMN employee_number VARCHAR(50) NULL,
  ADD COLUMN employee_status VARCHAR(20) NULL,
  ADD COLUMN employment_type VARCHAR(30) NULL,
  ADD COLUMN employee_category VARCHAR(30) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN position_title VARCHAR(150) NULL,
  ADD COLUMN grade_level VARCHAR(50) NULL,
  ADD COLUMN reporting_manager_id BIGINT UNSIGNED NULL,
  ADD COLUMN confirmation_date DATE NULL,
  ADD COLUMN contract_start_date DATE NULL,
  ADD COLUMN contract_end_date DATE NULL,
  ADD COLUMN first_name VARCHAR(100) NULL,
  ADD COLUMN middle_name VARCHAR(100) NULL,
  ADD COLUMN last_name VARCHAR(100) NULL,
  ADD COLUMN full_name VARCHAR(200) NULL,
  ADD COLUMN gender VARCHAR(20) NULL,
  ADD COLUMN date_of_birth DATE NULL,
  ADD COLUMN nationality VARCHAR(100) NULL,
  ADD COLUMN marital_status VARCHAR(50) NULL,
  ADD COLUMN religion VARCHAR(50) NULL,
  ADD COLUMN blood_group VARCHAR(20) NULL,
  ADD COLUMN iqama_expiry_date DATE NULL,
  ADD COLUMN passport_number VARCHAR(50) NULL,
  ADD COLUMN passport_expiry_date DATE NULL,
  ADD COLUMN passport_issuing_country VARCHAR(100) NULL,
  ADD COLUMN visa_type VARCHAR(50) NULL,
  ADD COLUMN visa_expiry_date DATE NULL,
  ADD COLUMN personal_mobile VARCHAR(50) NULL,
  ADD COLUMN work_mobile VARCHAR(50) NULL,
  ADD COLUMN personal_email VARCHAR(150) NULL,
  ADD COLUMN work_email VARCHAR(150) NULL,
  ADD COLUMN emergency_contact_name VARCHAR(150) NULL,
  ADD COLUMN emergency_contact_relation VARCHAR(50) NULL,
  ADD COLUMN emergency_contact_phone VARCHAR(50) NULL,
  ADD COLUMN national_address TEXT NULL,
  ADD COLUMN address_line1 VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(100) NULL,
  ADD COLUMN state_province VARCHAR(100) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN country VARCHAR(100) NULL,
  ADD COLUMN data_access_level VARCHAR(20) NULL,
  ADD COLUMN record_status VARCHAR(20) NULL,
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL;

ALTER TABLE employees
  ADD CONSTRAINT fk_emp_reporting_manager FOREIGN KEY (reporting_manager_id) REFERENCES employees(id);

CREATE TABLE IF NOT EXISTS employee_salary_structures (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  salary_type VARCHAR(20) NOT NULL DEFAULT 'monthly',
  basic_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  housing_allowance DECIMAL(18,2) NOT NULL DEFAULT 0,
  transport_allowance DECIMAL(18,2) NOT NULL DEFAULT 0,
  other_allowances DECIMAL(18,2) NOT NULL DEFAULT 0,
  gross_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  gosi_employee_pct DECIMAL(6,3) NULL,
  gosi_employer_pct DECIMAL(6,3) NULL,
  tax_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  loan_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  advance_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  other_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  net_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  effective_from DATE NULL,
  effective_to DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ess_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE payroll_runs
  ADD COLUMN period_month INT NULL,
  ADD COLUMN period_year INT NULL,
  ADD COLUMN posting_reference VARCHAR(100) NULL,
  ADD COLUMN payment_method VARCHAR(50) NULL,
  ADD COLUMN bank_file_reference VARCHAR(100) NULL;

ALTER TABLE payroll_items
  ADD COLUMN basic_salary DECIMAL(18,2) NULL,
  ADD COLUMN allowances DECIMAL(18,2) NULL,
  ADD COLUMN overtime_amount DECIMAL(18,2) NULL,
  ADD COLUMN bonus_amount DECIMAL(18,2) NULL,
  ADD COLUMN deductions DECIMAL(18,2) NULL,
  ADD COLUMN net_pay DECIMAL(18,2) NULL,
  ADD COLUMN payroll_currency CHAR(3) NULL,
  ADD COLUMN exchange_rate DECIMAL(18,6) NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN gl_posting_reference VARCHAR(100) NULL,
  ADD COLUMN payment_method VARCHAR(50) NULL;

ALTER TABLE payroll_items
  ADD CONSTRAINT fk_pi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id),
  ADD CONSTRAINT fk_pi_currency FOREIGN KEY (payroll_currency) REFERENCES currencies(code);

ALTER TABLE leave_types
  ADD COLUMN leave_category VARCHAR(50) NULL,
  ADD COLUMN max_allowed_days INT NULL,
  ADD COLUMN carry_forward_allowed TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN paid_flag TINYINT NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS leave_balances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  leave_type_id BIGINT UNSIGNED NOT NULL,
  opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
  accrued_days DECIMAL(18,2) NOT NULL DEFAULT 0,
  used_days DECIMAL(18,2) NOT NULL DEFAULT 0,
  remaining_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NULL,
  CONSTRAINT fk_lb_emp FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_lb_type FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE leave_requests
  ADD COLUMN total_days DECIMAL(18,2) NULL,
  ADD COLUMN reason VARCHAR(255) NULL,
  ADD COLUMN approver_id BIGINT UNSIGNED NULL,
  ADD COLUMN approved_date DATE NULL;

ALTER TABLE leave_requests
  ADD CONSTRAINT fk_lr_approver FOREIGN KEY (approver_id) REFERENCES users(id);

ALTER TABLE employee_travel
  ADD COLUMN travel_type VARCHAR(20) NULL,
  ADD COLUMN from_location VARCHAR(150) NULL,
  ADD COLUMN to_location VARCHAR(150) NULL,
  ADD COLUMN status VARCHAR(20) NULL;

CREATE TABLE IF NOT EXISTS employee_travel_expenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  travel_id BIGINT UNSIGNED NOT NULL,
  expense_type VARCHAR(50) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency_code CHAR(3) NULL,
  exchange_rate DECIMAL(18,6) NULL,
  receipt_attachment_id BIGINT UNSIGNED NULL,
  approved_amount DECIMAL(18,2) NULL,
  CONSTRAINT fk_ete_travel FOREIGN KEY (travel_id) REFERENCES employee_travel(id),
  CONSTRAINT fk_ete_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_travel_advances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  travel_id BIGINT UNSIGNED NOT NULL,
  advance_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  advance_currency CHAR(3) NULL,
  settlement_status VARCHAR(20) NULL,
  CONSTRAINT fk_eta_travel FOREIGN KEY (travel_id) REFERENCES employee_travel(id),
  CONSTRAINT fk_eta_currency FOREIGN KEY (advance_currency) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE employee_documents
  ADD COLUMN document_number VARCHAR(100) NULL,
  ADD COLUMN issue_date DATE NULL,
  ADD COLUMN expiry_date DATE NULL,
  ADD COLUMN confidentiality_level VARCHAR(50) NULL,
  ADD COLUMN verified_flag TINYINT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS hr_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  request_type VARCHAR(50) NOT NULL,
  request_date DATE NOT NULL,
  effective_date DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  approval_workflow_id VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_hr_req_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Fleet expansion
ALTER TABLE vehicles
  ADD COLUMN vehicle_code VARCHAR(50) NULL,
  ADD COLUMN plate_type VARCHAR(30) NULL,
  ADD COLUMN registration_country VARCHAR(100) NULL,
  ADD COLUMN vin VARCHAR(100) NULL,
  ADD COLUMN vehicle_type VARCHAR(50) NULL,
  ADD COLUMN make VARCHAR(100) NULL,
  ADD COLUMN body_type VARCHAR(50) NULL,
  ADD COLUMN color VARCHAR(50) NULL,
  ADD COLUMN fuel_type VARCHAR(30) NULL,
  ADD COLUMN transmission_type VARCHAR(30) NULL,
  ADD COLUMN engine_capacity VARCHAR(50) NULL,
  ADD COLUMN seating_capacity INT NULL,
  ADD COLUMN load_capacity DECIMAL(18,2) NULL,
  ADD COLUMN ownership_type VARCHAR(30) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN assignment_start_date DATE NULL,
  ADD COLUMN assignment_end_date DATE NULL,
  ADD COLUMN purchase_date DATE NULL,
  ADD COLUMN in_service_date DATE NULL,
  ADD COLUMN odometer_reading INT NULL,
  ADD COLUMN odometer_unit VARCHAR(10) NULL;

CREATE TABLE IF NOT EXISTS vehicle_maintenance_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  maintenance_type VARCHAR(50) NOT NULL,
  frequency_type VARCHAR(20) NOT NULL,
  interval_value INT NOT NULL,
  last_service_date DATE NULL,
  last_service_odometer INT NULL,
  next_due_date DATE NULL,
  next_due_odometer INT NULL,
  CONSTRAINT fk_vmp_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE vehicle_maintenance
  ADD COLUMN maintenance_category VARCHAR(50) NULL,
  ADD COLUMN workshop_type VARCHAR(30) NULL,
  ADD COLUMN vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN start_date DATE NULL,
  ADD COLUMN end_date DATE NULL,
  ADD COLUMN downtime_hours INT NULL,
  ADD COLUMN status VARCHAR(20) NULL,
  ADD COLUMN labor_cost DECIMAL(18,2) NULL,
  ADD COLUMN parts_cost DECIMAL(18,2) NULL,
  ADD COLUMN external_service_cost DECIMAL(18,2) NULL,
  ADD COLUMN total_cost DECIMAL(18,2) NULL,
  ADD COLUMN currency_code CHAR(3) NULL;

ALTER TABLE vehicle_maintenance
  ADD CONSTRAINT fk_vm_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_vm_currency FOREIGN KEY (currency_code) REFERENCES currencies(code);

ALTER TABLE vehicle_warranties
  ADD COLUMN coverage_description VARCHAR(255) NULL,
  ADD COLUMN coverage_limit_km INT NULL,
  ADD COLUMN remaining_coverage_km INT NULL,
  ADD COLUMN warranty_provider VARCHAR(150) NULL,
  ADD COLUMN claim_procedure_ref VARCHAR(150) NULL;

CREATE TABLE IF NOT EXISTS vehicle_warranty_claims (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  warranty_id BIGINT UNSIGNED NOT NULL,
  maintenance_id BIGINT UNSIGNED NULL,
  claim_date DATE NULL,
  claimed_amount DECIMAL(18,2) NULL,
  approved_amount DECIMAL(18,2) NULL,
  claim_status VARCHAR(20) NULL,
  CONSTRAINT fk_vwc_warranty FOREIGN KEY (warranty_id) REFERENCES vehicle_warranties(id),
  CONSTRAINT fk_vwc_maintenance FOREIGN KEY (maintenance_id) REFERENCES vehicle_maintenance(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE vehicle_expenses
  ADD COLUMN expense_type VARCHAR(50) NULL,
  ADD COLUMN expense_category VARCHAR(50) NULL,
  ADD COLUMN vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN payment_method VARCHAR(30) NULL,
  ADD COLUMN currency_code CHAR(3) NULL,
  ADD COLUMN quantity DECIMAL(18,2) NULL,
  ADD COLUMN unit_cost DECIMAL(18,4) NULL,
  ADD COLUMN tax_amount DECIMAL(18,2) NULL,
  ADD COLUMN vat_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN invoice_reference VARCHAR(100) NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN posting_status VARCHAR(20) NULL;

ALTER TABLE vehicle_expenses
  ADD CONSTRAINT fk_ve_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_ve_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_ve_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE vehicle_reminders
  ADD COLUMN reference_type VARCHAR(50) NULL,
  ADD COLUMN reference_id BIGINT UNSIGNED NULL,
  ADD COLUMN trigger_condition VARCHAR(30) NULL,
  ADD COLUMN trigger_value VARCHAR(50) NULL,
  ADD COLUMN reminder_frequency VARCHAR(30) NULL,
  ADD COLUMN notification_channel VARCHAR(30) NULL,
  ADD COLUMN assigned_to_user_id BIGINT UNSIGNED NULL,
  ADD COLUMN last_triggered_date DATE NULL,
  ADD COLUMN next_trigger_date DATE NULL;

ALTER TABLE vehicle_reminders
  ADD CONSTRAINT fk_vr_assigned_user FOREIGN KEY (assigned_to_user_id) REFERENCES users(id);

CREATE TABLE IF NOT EXISTS vehicle_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(50) NOT NULL,
  document_number VARCHAR(100) NULL,
  issue_date DATE NULL,
  expiry_date DATE NULL,
  issuing_authority VARCHAR(150) NULL,
  attachment_id BIGINT UNSIGNED NULL,
  confidentiality_level VARCHAR(50) NULL,
  verified_flag TINYINT NOT NULL DEFAULT 0,
  CONSTRAINT fk_vd_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  CONSTRAINT fk_vd_attachment FOREIGN KEY (attachment_id) REFERENCES attachments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_incidents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  driver_id BIGINT UNSIGNED NULL,
  incident_date DATE NULL,
  location VARCHAR(150) NULL,
  incident_type VARCHAR(50) NULL,
  description TEXT NULL,
  insurance_claim_reference VARCHAR(100) NULL,
  repair_cost DECIMAL(18,2) NULL,
  status VARCHAR(20) NULL,
  CONSTRAINT fk_vi_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  CONSTRAINT fk_vi_driver FOREIGN KEY (driver_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Attachments expansion
ALTER TABLE attachments
  ADD COLUMN file_display_name VARCHAR(255) NULL,
  ADD COLUMN file_description TEXT NULL,
  ADD COLUMN file_category VARCHAR(50) NULL,
  ADD COLUMN document_type VARCHAR(50) NULL,
  ADD COLUMN reference_module VARCHAR(50) NULL,
  ADD COLUMN reference_record_id BIGINT UNSIGNED NULL,
  ADD COLUMN file_extension VARCHAR(20) NULL,
  ADD COLUMN file_hash CHAR(64) NULL,
  ADD COLUMN version_no INT NOT NULL DEFAULT 1,
  ADD COLUMN is_latest TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN upload_source VARCHAR(30) NULL,
  ADD COLUMN storage_type VARCHAR(30) NULL,
  ADD COLUMN storage_provider VARCHAR(50) NULL,
  ADD COLUMN storage_key VARCHAR(255) NULL,
  ADD COLUMN encryption_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compression_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN access_level VARCHAR(30) NULL,
  ADD COLUMN allowed_roles TEXT NULL,
  ADD COLUMN allowed_users TEXT NULL,
  ADD COLUMN download_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN print_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN watermark_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN confidentiality_level VARCHAR(30) NULL,
  ADD COLUMN contains_pii TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN data_classification VARCHAR(50) NULL,
  ADD COLUMN legal_hold_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN retention_policy_id BIGINT UNSIGNED NULL,
  ADD COLUMN retention_expiry_date DATE NULL,
  ADD COLUMN document_status VARCHAR(20) NULL,
  ADD COLUMN verified_by BIGINT UNSIGNED NULL,
  ADD COLUMN verified_date DATE NULL,
  ADD COLUMN approval_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN expiry_date DATE NULL,
  ADD COLUMN auto_archive_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN parent_document_id BIGINT UNSIGNED NULL,
  ADD COLUMN change_reason VARCHAR(150) NULL,
  ADD COLUMN superseded_date DATE NULL,
  ADD COLUMN superseded_by BIGINT UNSIGNED NULL,
  ADD COLUMN version_notes TEXT NULL,
  ADD COLUMN tags TEXT NULL,
  ADD COLUMN ocr_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN ocr_text LONGTEXT NULL,
  ADD COLUMN language VARCHAR(30) NULL,
  ADD COLUMN search_index_status VARCHAR(20) NULL,
  ADD COLUMN last_accessed_by BIGINT UNSIGNED NULL,
  ADD COLUMN last_accessed_at DATETIME NULL,
  ADD COLUMN download_count INT NOT NULL DEFAULT 0,
  ADD COLUMN access_log_id BIGINT UNSIGNED NULL,
  ADD COLUMN ip_address VARCHAR(50) NULL,
  ADD COLUMN reminder_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN reminder_type VARCHAR(30) NULL,
  ADD COLUMN reminder_schedule VARCHAR(100) NULL,
  ADD COLUMN notified_roles TEXT NULL,
  ADD COLUMN notified_users TEXT NULL,
  ADD COLUMN source_system VARCHAR(50) NULL,
  ADD COLUMN external_reference_id VARCHAR(100) NULL,
  ADD COLUMN api_sync_status VARCHAR(20) NULL;

ALTER TABLE attachments
  ADD CONSTRAINT fk_att_verified_by FOREIGN KEY (verified_by) REFERENCES users(id),
  ADD CONSTRAINT fk_att_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
  ADD CONSTRAINT fk_att_parent FOREIGN KEY (parent_document_id) REFERENCES attachments(id),
  ADD CONSTRAINT fk_att_superseded_by FOREIGN KEY (superseded_by) REFERENCES users(id);
-- ERP1 Lookup Values
CREATE TABLE IF NOT EXISTS lookup_values (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NULL,
    list_key VARCHAR(50) NOT NULL,
    code VARCHAR(50) NOT NULL,
    name_en VARCHAR(150) NOT NULL,
    name_ar VARCHAR(150) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_lookup (company_id, list_key, code),
    CONSTRAINT fk_lookup_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS party_addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    party_type VARCHAR(20) NOT NULL,
    party_id BIGINT UNSIGNED NOT NULL,
    address_label VARCHAR(100) NULL,
    address_type VARCHAR(50) NULL,
    address_line1 VARCHAR(150) NULL,
    address_line2 VARCHAR(150) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    country VARCHAR(100) NULL,
    is_default TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    CONSTRAINT fk_party_addr_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE customers
    ADD COLUMN default_version_no VARCHAR(20) NULL,
    ADD COLUMN default_reference_doc_no VARCHAR(50) NULL,
    ADD COLUMN default_shipping_method VARCHAR(50) NULL,
    ADD COLUMN default_delivery_priority VARCHAR(50) NULL,
    ADD COLUMN default_delivery_terms VARCHAR(50) NULL;

ALTER TABLE vendors
    ADD COLUMN default_po_type VARCHAR(30) NULL,
    ADD COLUMN default_price_control_type VARCHAR(30) NULL,
    ADD COLUMN default_invoice_matching_type VARCHAR(30) NULL,
    ADD COLUMN default_invoice_matching_rule VARCHAR(50) NULL,
    ADD COLUMN default_approval_workflow_id VARCHAR(50) NULL,
    ADD COLUMN default_nda_reference VARCHAR(100) NULL,
    ADD COLUMN default_delivery_address_id VARCHAR(100) NULL,
    ADD COLUMN tags TEXT NULL;

CREATE TABLE IF NOT EXISTS user_saved_filters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    module VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    scope VARCHAR(20) NOT NULL DEFAULT 'private',
    approved_flag TINYINT NOT NULL DEFAULT 0,
    query_json TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_usf_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE vendors
    ADD COLUMN kanban_rank BIGINT NULL;

-- Money Manager
CREATE TABLE IF NOT EXISTS money_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    membership_no VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    id_type VARCHAR(30) NOT NULL,
    id_number VARCHAR(100) NOT NULL,
    id_issue_date DATE NULL,
    id_expiry_date DATE NOT NULL,
    date_of_birth DATE NULL,
    nationality VARCHAR(100) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    phone_alt VARCHAR(50) NULL,
    address_line1 VARCHAR(150) NULL,
    address_line2 VARCHAR(150) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    country VARCHAR(100) NULL,
    kyc_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    kyc_verified_at DATETIME NULL,
    kyc_verified_by BIGINT UNSIGNED NULL,
    pep_flag TINYINT NOT NULL DEFAULT 0,
    aml_checked TINYINT NOT NULL DEFAULT 0,
    sanction_screened TINYINT NOT NULL DEFAULT 0,
    risk_level VARCHAR(20) NULL,
    risk_tags TEXT NULL,
    compliance_notes TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    onboarding_date DATE NULL,
    relationship_manager VARCHAR(150) NULL,
    segment VARCHAR(50) NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_money_customer_membership (company_id, membership_no),
    UNIQUE KEY uq_money_customer_idnum (company_id, id_number),
    CONSTRAINT fk_money_customer_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_customer_kyc_by FOREIGN KEY (kyc_verified_by) REFERENCES users(id),
    CONSTRAINT fk_money_customer_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_money_customer_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    owner_user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    account_no VARCHAR(100) NULL,
    iban VARCHAR(50) NULL,
    account_type VARCHAR(30) NOT NULL,
    account_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    opened_on DATE NULL,
    branch_name VARCHAR(100) NULL,
    currency_code CHAR(3) NOT NULL,
    opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    current_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0,
    visibility ENUM('private', 'shared', 'public') NOT NULL DEFAULT 'private',
    public_access ENUM('view', 'operate') NOT NULL DEFAULT 'view',
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_money_account_no (company_id, account_no),
    UNIQUE KEY uq_money_iban (company_id, iban),
    CONSTRAINT fk_money_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_customer FOREIGN KEY (customer_id) REFERENCES money_customers(id),
    CONSTRAINT fk_money_owner FOREIGN KEY (owner_user_id) REFERENCES users(id),
    CONSTRAINT fk_money_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_account_permissions (
    account_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    can_view TINYINT NOT NULL DEFAULT 1,
    can_operate TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (account_id, user_id),
    CONSTRAINT fk_mperm_account FOREIGN KEY (account_id) REFERENCES money_accounts(id),
    CONSTRAINT fk_mperm_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('debit', 'credit', 'transfer_in', 'transfer_out') NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    reference VARCHAR(100) NULL,
    related_account_id BIGINT UNSIGNED NULL,
    transfer_id VARCHAR(64) NULL,
    admin_note TEXT NULL,
    admin_tags VARCHAR(255) NULL,
    admin_status VARCHAR(30) NULL DEFAULT 'approved',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_mtxn_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_mtxn_account FOREIGN KEY (account_id) REFERENCES money_accounts(id),
    CONSTRAINT fk_mtxn_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_mtxn_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_settings (
    company_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    account_no_prefix VARCHAR(30) NOT NULL DEFAULT 'ACC',
    iban_prefix VARCHAR(30) NOT NULL DEFAULT 'IBAN',
    money_logo_path VARCHAR(255) NULL,
    print_header_title VARCHAR(150) NULL,
    print_header_line1 VARCHAR(150) NULL,
    print_header_line2 VARCHAR(150) NULL,
    print_header_line3 VARCHAR(150) NULL,
    print_header_footer VARCHAR(150) NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_money_settings_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_settings_user FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
