CREATE TABLE purchase_requisitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    requisition_number VARCHAR(50) NOT NULL,
    title VARCHAR(180) NOT NULL,
    mrp_header_id BIGINT UNSIGNED NULL,
    department_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    required_date DATE NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
    status ENUM('draft', 'submitted', 'approved', 'rejected', 'ordered', 'closed', 'cancelled') NOT NULL DEFAULT 'draft',
    estimated_total DECIMAL(18,4) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    requested_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_requisition_number (company_id, requisition_number),
    CONSTRAINT fk_pr_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_pr_mrp FOREIGN KEY (mrp_header_id) REFERENCES mrp_headers (id),
    CONSTRAINT fk_pr_department FOREIGN KEY (department_id) REFERENCES departments (id),
    CONSTRAINT fk_pr_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_pr_requester FOREIGN KEY (requested_by) REFERENCES users (id),
    CONSTRAINT fk_pr_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE purchase_requisition_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_requisition_id BIGINT UNSIGNED NOT NULL,
    mrp_detail_id BIGINT UNSIGNED NULL,
    material_code VARCHAR(80) NULL,
    material_name VARCHAR(180) NOT NULL,
    specification VARCHAR(500) NULL,
    uom_id BIGINT UNSIGNED NULL,
    requested_quantity DECIMAL(18,6) NOT NULL,
    estimated_unit_price DECIMAL(18,6) NOT NULL DEFAULT 0,
    estimated_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    preferred_supplier_id BIGINT UNSIGNED NULL,
    ordered_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('open', 'quoted', 'ordered', 'closed', 'cancelled') NOT NULL DEFAULT 'open',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_pr_item_header FOREIGN KEY (purchase_requisition_id) REFERENCES purchase_requisitions (id),
    CONSTRAINT fk_pr_item_mrp_detail FOREIGN KEY (mrp_detail_id) REFERENCES mrp_details (id),
    CONSTRAINT fk_pr_item_uom FOREIGN KEY (uom_id) REFERENCES uoms (id),
    CONSTRAINT fk_pr_item_supplier FOREIGN KEY (preferred_supplier_id) REFERENCES suppliers (id)
) ENGINE=InnoDB;

CREATE TABLE supplier_quotations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    purchase_requisition_id BIGINT UNSIGNED NOT NULL,
    purchase_requisition_item_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    quotation_number VARCHAR(80) NOT NULL,
    quotation_date DATE NOT NULL,
    valid_until DATE NULL,
    quoted_quantity DECIMAL(18,6) NOT NULL,
    unit_price DECIMAL(18,6) NOT NULL,
    total_amount DECIMAL(18,4) NOT NULL,
    currency_id BIGINT UNSIGNED NOT NULL,
    delivery_days INT UNSIGNED NOT NULL DEFAULT 0,
    payment_terms VARCHAR(255) NULL,
    status ENUM('received', 'selected', 'rejected', 'expired') NOT NULL DEFAULT 'received',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    KEY idx_supplier_quotation_pr (purchase_requisition_id, supplier_id),
    CONSTRAINT fk_quotation_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_quotation_pr FOREIGN KEY (purchase_requisition_id) REFERENCES purchase_requisitions (id),
    CONSTRAINT fk_quotation_pr_item FOREIGN KEY (purchase_requisition_item_id) REFERENCES purchase_requisition_items (id),
    CONSTRAINT fk_quotation_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_quotation_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_quotation_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE purchase_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    purchase_requisition_id BIGINT UNSIGNED NULL,
    purchase_order_number VARCHAR(50) NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    currency_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NULL,
    order_date DATE NOT NULL,
    expected_delivery_date DATE NULL,
    status ENUM('draft', 'approved', 'sent', 'partially_received', 'received', 'closed', 'cancelled') NOT NULL DEFAULT 'draft',
    subtotal DECIMAL(18,4) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    tax_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    shipping_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    total_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    payment_terms VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uq_company_purchase_order_number (company_id, purchase_order_number),
    CONSTRAINT fk_po_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_po_pr FOREIGN KEY (purchase_requisition_id) REFERENCES purchase_requisitions (id),
    CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_po_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_po_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_po_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_po_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE purchase_order_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    purchase_requisition_item_id BIGINT UNSIGNED NULL,
    material_code VARCHAR(80) NULL,
    material_name VARCHAR(180) NOT NULL,
    specification VARCHAR(500) NULL,
    uom_id BIGINT UNSIGNED NULL,
    ordered_quantity DECIMAL(18,6) NOT NULL,
    unit_price DECIMAL(18,6) NOT NULL,
    discount_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    tax_percent DECIMAL(9,4) NOT NULL DEFAULT 0,
    line_total DECIMAL(18,4) NOT NULL,
    received_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    returned_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('open', 'partially_received', 'received', 'closed', 'cancelled') NOT NULL DEFAULT 'open',
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_po_item_header FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
    CONSTRAINT fk_po_item_pr_item FOREIGN KEY (purchase_requisition_item_id) REFERENCES purchase_requisition_items (id),
    CONSTRAINT fk_po_item_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE goods_receipts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    receipt_number VARCHAR(60) NOT NULL UNIQUE,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    purchase_order_item_id BIGINT UNSIGNED NOT NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    receipt_date DATE NOT NULL,
    received_quantity DECIMAL(18,6) NOT NULL,
    accepted_quantity DECIMAL(18,6) NOT NULL,
    rejected_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    batch_number VARCHAR(100) NULL,
    challan_number VARCHAR(100) NULL,
    status ENUM('received', 'inspected', 'posted', 'cancelled') NOT NULL DEFAULT 'received',
    received_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_gr_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_gr_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
    CONSTRAINT fk_gr_po_item FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items (id),
    CONSTRAINT fk_gr_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_gr_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_gr_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_gr_receiver FOREIGN KEY (received_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE purchase_returns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    return_number VARCHAR(60) NOT NULL UNIQUE,
    goods_receipt_id BIGINT UNSIGNED NOT NULL,
    purchase_order_id BIGINT UNSIGNED NOT NULL,
    purchase_order_item_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    return_date DATE NOT NULL,
    return_quantity DECIMAL(18,6) NOT NULL,
    return_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    reason VARCHAR(500) NOT NULL,
    status ENUM('draft', 'approved', 'dispatched', 'credited', 'cancelled') NOT NULL DEFAULT 'draft',
    created_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED NULL,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT fk_purchase_return_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_purchase_return_receipt FOREIGN KEY (goods_receipt_id) REFERENCES goods_receipts (id),
    CONSTRAINT fk_purchase_return_po FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (id),
    CONSTRAINT fk_purchase_return_po_item FOREIGN KEY (purchase_order_item_id) REFERENCES purchase_order_items (id),
    CONSTRAINT fk_purchase_return_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_purchase_return_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_purchase_return_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE supplier_ledgers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    transaction_date DATE NOT NULL,
    transaction_type ENUM('purchase_order', 'goods_receipt', 'purchase_return', 'payment', 'adjustment') NOT NULL,
    reference_type VARCHAR(80) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    reference_number VARCHAR(80) NOT NULL,
    description VARCHAR(255) NULL,
    debit_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    credit_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    balance_amount DECIMAL(18,4) NOT NULL DEFAULT 0,
    currency_id BIGINT UNSIGNED NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_supplier_ledger (company_id, supplier_id, transaction_date),
    CONSTRAINT fk_supplier_ledger_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_supplier_ledger_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id),
    CONSTRAINT fk_supplier_ledger_currency FOREIGN KEY (currency_id) REFERENCES currencies (id),
    CONSTRAINT fk_supplier_ledger_creator FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;