CREATE TABLE items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    item_code VARCHAR(80) NOT NULL,
    name VARCHAR(180) NOT NULL,
    item_category VARCHAR(80) NULL,
    item_type ENUM('fabric', 'trim', 'accessory', 'packaging', 'chemical', 'finished_good', 'other') NOT NULL DEFAULT 'other',
    uom_id BIGINT UNSIGNED NOT NULL,
    tracking_method ENUM('none', 'batch', 'serial', 'roll') NOT NULL DEFAULT 'none',
    reorder_level DECIMAL(18,6) NOT NULL DEFAULT 0,
    minimum_stock DECIMAL(18,6) NOT NULL DEFAULT 0,
    maximum_stock DECIMAL(18,6) NULL,
    standard_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    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_item_code (company_id, item_code),
    CONSTRAINT fk_item_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_item_uom FOREIGN KEY (uom_id) REFERENCES uoms (id)
) ENGINE=InnoDB;

CREATE TABLE item_batches (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT UNSIGNED NOT NULL,
    batch_number VARCHAR(100) NOT NULL,
    supplier_id BIGINT UNSIGNED NULL,
    manufacture_date DATE NULL,
    expiry_date DATE NULL,
    received_date DATE NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    status ENUM('active', 'quarantine', 'expired', 'closed') NOT NULL DEFAULT 'active',
    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_item_batch (item_id, batch_number),
    CONSTRAINT fk_item_batch_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_item_batch_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers (id)
) ENGINE=InnoDB;

CREATE TABLE item_serials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    serial_number VARCHAR(150) NOT NULL UNIQUE,
    warehouse_id BIGINT UNSIGNED NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    status ENUM('available', 'reserved', 'issued', 'damaged', 'returned') NOT NULL DEFAULT 'available',
    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_item_serial_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_item_serial_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_item_serial_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_item_serial_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id)
) ENGINE=InnoDB;

CREATE TABLE fabric_rolls (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    roll_number VARCHAR(100) NOT NULL UNIQUE,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    color_id BIGINT UNSIGNED NULL,
    width_value DECIMAL(12,4) NULL,
    width_uom VARCHAR(20) NULL,
    gsm DECIMAL(12,4) NULL,
    original_quantity DECIMAL(18,6) NOT NULL,
    available_quantity DECIMAL(18,6) NOT NULL,
    reserved_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    quality_grade VARCHAR(30) NULL,
    status ENUM('available', 'reserved', 'issued', 'finished', 'hold', 'rejected') NOT NULL DEFAULT 'available',
    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_fabric_roll_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_fabric_roll_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_fabric_roll_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_fabric_roll_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_fabric_roll_color FOREIGN KEY (color_id) REFERENCES colors (id)
) ENGINE=InnoDB;

CREATE TABLE stock_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    batch_key BIGINT UNSIGNED GENERATED ALWAYS AS (IFNULL(item_batch_id, 0)) STORED,
    location_key BIGINT UNSIGNED GENERATED ALWAYS AS (IFNULL(warehouse_location_id, 0)) STORED,
    quantity_on_hand DECIMAL(18,6) NOT NULL DEFAULT 0,
    reserved_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    available_quantity DECIMAL(18,6) NOT NULL DEFAULT 0,
    average_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    stock_value DECIMAL(18,4) NOT NULL DEFAULT 0,
    last_transaction_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stock_balance (company_id, item_id, warehouse_id, batch_key, location_key),
    CONSTRAINT fk_stock_balance_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_stock_balance_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_stock_balance_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_stock_balance_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_balance_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id)
) ENGINE=InnoDB;

CREATE TABLE stock_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    item_serial_id BIGINT UNSIGNED NULL,
    fabric_roll_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    transaction_number VARCHAR(70) NOT NULL UNIQUE,
    transaction_type ENUM('opening', 'receipt', 'issue', 'adjustment_in', 'adjustment_out', 'transfer_in', 'transfer_out', 'return_in', 'return_out') NOT NULL,
    transaction_date DATETIME NOT NULL,
    reference_type VARCHAR(80) NOT NULL,
    reference_id BIGINT UNSIGNED NOT NULL,
    reference_number VARCHAR(100) NOT NULL,
    quantity_in DECIMAL(18,6) NOT NULL DEFAULT 0,
    quantity_out DECIMAL(18,6) NOT NULL DEFAULT 0,
    balance_after DECIMAL(18,6) NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    total_value DECIMAL(18,4) NOT NULL DEFAULT 0,
    remarks VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_stock_transaction_item (company_id, item_id, transaction_date),
    KEY idx_stock_transaction_reference (reference_type, reference_id),
    CONSTRAINT fk_stock_transaction_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_stock_transaction_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_stock_transaction_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_stock_transaction_serial FOREIGN KEY (item_serial_id) REFERENCES item_serials (id),
    CONSTRAINT fk_stock_transaction_roll FOREIGN KEY (fabric_roll_id) REFERENCES fabric_rolls (id),
    CONSTRAINT fk_stock_transaction_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_transaction_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_stock_transaction_user FOREIGN KEY (created_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE stock_adjustments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    adjustment_number VARCHAR(60) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    adjustment_date DATE NOT NULL,
    adjustment_type ENUM('increase', 'decrease') NOT NULL,
    quantity DECIMAL(18,6) NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    reason VARCHAR(500) NOT NULL,
    status ENUM('draft', 'posted', 'cancelled') NOT NULL DEFAULT 'posted',
    created_by BIGINT UNSIGNED NOT NULL,
    approved_by BIGINT UNSIGNED 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_stock_adjustment_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_stock_adjustment_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_stock_adjustment_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_stock_adjustment_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_adjustment_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_stock_adjustment_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_stock_adjustment_approver FOREIGN KEY (approved_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE stock_transfers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    transfer_number VARCHAR(60) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    from_warehouse_id BIGINT UNSIGNED NOT NULL,
    from_location_id BIGINT UNSIGNED NULL,
    to_warehouse_id BIGINT UNSIGNED NOT NULL,
    to_location_id BIGINT UNSIGNED NULL,
    transfer_date DATE NOT NULL,
    quantity DECIMAL(18,6) NOT NULL,
    status ENUM('draft', 'in_transit', 'received', 'cancelled') NOT NULL DEFAULT 'received',
    reference VARCHAR(120) NULL,
    remarks VARCHAR(500) NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    received_by BIGINT UNSIGNED 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_stock_transfer_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_stock_transfer_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_stock_transfer_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_stock_transfer_from_warehouse FOREIGN KEY (from_warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_transfer_from_location FOREIGN KEY (from_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_stock_transfer_to_warehouse FOREIGN KEY (to_warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_stock_transfer_to_location FOREIGN KEY (to_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_stock_transfer_creator FOREIGN KEY (created_by) REFERENCES users (id),
    CONSTRAINT fk_stock_transfer_receiver FOREIGN KEY (received_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE issue_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    issue_number VARCHAR(60) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    item_serial_id BIGINT UNSIGNED NULL,
    fabric_roll_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    issue_date DATE NOT NULL,
    quantity DECIMAL(18,6) NOT NULL,
    issue_to_type VARCHAR(80) NOT NULL,
    issue_to_id BIGINT UNSIGNED NULL,
    reference VARCHAR(120) NULL,
    remarks VARCHAR(500) NULL,
    status ENUM('draft', 'posted', 'cancelled') NOT NULL DEFAULT 'posted',
    issued_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_issue_note_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_issue_note_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_issue_note_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_issue_note_serial FOREIGN KEY (item_serial_id) REFERENCES item_serials (id),
    CONSTRAINT fk_issue_note_roll FOREIGN KEY (fabric_roll_id) REFERENCES fabric_rolls (id),
    CONSTRAINT fk_issue_note_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_issue_note_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_issue_note_user FOREIGN KEY (issued_by) REFERENCES users (id)
) ENGINE=InnoDB;

CREATE TABLE receive_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    receive_number VARCHAR(60) NOT NULL UNIQUE,
    item_id BIGINT UNSIGNED NOT NULL,
    item_batch_id BIGINT UNSIGNED NULL,
    item_serial_id BIGINT UNSIGNED NULL,
    fabric_roll_id BIGINT UNSIGNED NULL,
    warehouse_id BIGINT UNSIGNED NOT NULL,
    warehouse_location_id BIGINT UNSIGNED NULL,
    receive_date DATE NOT NULL,
    quantity DECIMAL(18,6) NOT NULL,
    unit_cost DECIMAL(18,6) NOT NULL DEFAULT 0,
    source_type VARCHAR(80) NOT NULL,
    source_id BIGINT UNSIGNED NULL,
    reference VARCHAR(120) NULL,
    remarks VARCHAR(500) NULL,
    status ENUM('draft', 'posted', 'cancelled') NOT NULL DEFAULT 'posted',
    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_receive_note_company FOREIGN KEY (company_id) REFERENCES companies (id),
    CONSTRAINT fk_receive_note_item FOREIGN KEY (item_id) REFERENCES items (id),
    CONSTRAINT fk_receive_note_batch FOREIGN KEY (item_batch_id) REFERENCES item_batches (id),
    CONSTRAINT fk_receive_note_serial FOREIGN KEY (item_serial_id) REFERENCES item_serials (id),
    CONSTRAINT fk_receive_note_roll FOREIGN KEY (fabric_roll_id) REFERENCES fabric_rolls (id),
    CONSTRAINT fk_receive_note_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses (id),
    CONSTRAINT fk_receive_note_location FOREIGN KEY (warehouse_location_id) REFERENCES warehouse_locations (id),
    CONSTRAINT fk_receive_note_user FOREIGN KEY (received_by) REFERENCES users (id)
) ENGINE=InnoDB;