-- ============================================================
-- Assets Management System 1.0
-- Phase 3: Suppliers, Purchase Orders, Goods Receipt, Inspection
-- Run AFTER database/migrations/002_phase2_assets.sql + 002_phase2_seed.sql
--
-- Design note: the master spec (section 17) lists a separate
-- "procurement proposal" concept above the purchase order. This
-- build folds procurement method/reference directly onto the
-- purchase order instead of a separate procurements table, to
-- avoid an empty pass-through entity — a dedicated tender/proposal
-- module can be layered in later without breaking this schema.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- suppliers
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS suppliers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    name VARCHAR(200) NOT NULL,
    name_np VARCHAR(200) NULL,
    contact_person VARCHAR(150) NULL,
    phone VARCHAR(30) NULL,
    email VARCHAR(150) NULL,
    address VARCHAR(255) NULL,
    pan_number VARCHAR(50) NULL,
    registration_no VARCHAR(80) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_supplier_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- purchase_orders / purchase_order_items (spec section 17)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS purchase_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    po_number VARCHAR(40) NOT NULL,
    supplier_id INT UNSIGNED NOT NULL,
    fiscal_year_id INT UNSIGNED NULL,
    procurement_method ENUM('Sealed Quotation','Public Tender','Direct Purchase','Consumer Committee','Framework Agreement','Other') NOT NULL DEFAULT 'Direct Purchase',
    reference_number VARCHAR(100) NULL COMMENT 'quotation/tender/contract reference',
    po_date DATE NOT NULL,
    expected_delivery_date DATE NULL,
    status ENUM('Draft','Issued','Partially Received','Received','Closed','Cancelled') NOT NULL DEFAULT 'Draft',
    remarks TEXT NULL,
    created_by INT UNSIGNED NULL,
    approved_by INT UNSIGNED NULL,
    approved_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_po_number (organization_id, po_number),
    CONSTRAINT fk_po_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_po_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    CONSTRAINT fk_po_fy FOREIGN KEY (fiscal_year_id) REFERENCES fiscal_years(id),
    INDEX idx_po_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS purchase_order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    po_id INT UNSIGNED NOT NULL,
    category_id INT UNSIGNED NULL,
    item_name VARCHAR(200) NOT NULL,
    specification TEXT NULL,
    quantity DECIMAL(12,2) NOT NULL,
    unit VARCHAR(30) NOT NULL DEFAULT 'Piece',
    unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    total_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    received_quantity DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'running total received across all GRNs against this line',
    CONSTRAINT fk_poi_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
    CONSTRAINT fk_poi_cat FOREIGN KEY (category_id) REFERENCES asset_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- goods_receipts / goods_receipt_items (spec section 18)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS goods_receipts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    grn_number VARCHAR(40) NOT NULL,
    po_id INT UNSIGNED NULL COMMENT 'nullable — direct/donated receipts may have no PO',
    supplier_id INT UNSIGNED NULL,
    receipt_date DATE NOT NULL,
    delivery_note_number VARCHAR(100) NULL,
    invoice_number VARCHAR(100) NULL,
    status ENUM('Draft','Pending Inspection','Inspected','Completed') NOT NULL DEFAULT 'Pending Inspection',
    remarks TEXT NULL,
    received_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_grn_number (organization_id, grn_number),
    CONSTRAINT fk_grn_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_grn_po FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
    CONSTRAINT fk_grn_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    INDEX idx_grn_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS goods_receipt_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    grn_id INT UNSIGNED NOT NULL,
    po_item_id INT UNSIGNED NULL,
    item_name VARCHAR(200) NOT NULL,
    specification TEXT NULL,
    ordered_quantity DECIMAL(12,2) NULL COMMENT 'null when receipt has no PO line to compare against',
    received_quantity DECIMAL(12,2) NOT NULL,
    unit VARCHAR(30) NOT NULL DEFAULT 'Piece',
    unit_cost DECIMAL(18,2) NOT NULL DEFAULT 0,
    condition_on_receipt VARCHAR(50) NOT NULL DEFAULT 'Good',
    remarks VARCHAR(255) NULL,
    CONSTRAINT fk_gri_grn FOREIGN KEY (grn_id) REFERENCES goods_receipts(id),
    CONSTRAINT fk_gri_poi FOREIGN KEY (po_item_id) REFERENCES purchase_order_items(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- inspections / inspection_items (spec section 19)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS inspections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NOT NULL,
    inspection_number VARCHAR(40) NOT NULL,
    grn_id INT UNSIGNED NOT NULL,
    inspection_date DATE NOT NULL,
    committee_members TEXT NULL COMMENT 'free text: names + designations, one per line',
    overall_result ENUM('Accepted','Accepted with Remarks','Partially Accepted','Rejected') NOT NULL,
    remarks TEXT NULL,
    recommendation TEXT NULL,
    inspected_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_inspection_number (organization_id, inspection_number),
    CONSTRAINT fk_insp_org FOREIGN KEY (organization_id) REFERENCES organizations(id),
    CONSTRAINT fk_insp_grn FOREIGN KEY (grn_id) REFERENCES goods_receipts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inspection_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    inspection_id INT UNSIGNED NOT NULL,
    grn_item_id INT UNSIGNED NOT NULL,
    ordered_quantity DECIMAL(12,2) NULL,
    received_quantity DECIMAL(12,2) NOT NULL,
    accepted_quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
    rejected_quantity DECIMAL(12,2) NOT NULL DEFAULT 0,
    condition_status VARCHAR(50) NOT NULL DEFAULT 'Good',
    compliance ENUM('Yes','No','Partial') NOT NULL DEFAULT 'Yes',
    defects VARCHAR(255) NULL,
    remarks VARCHAR(255) NULL,
    asset_id INT UNSIGNED NULL COMMENT 'set once this accepted line has been registered as an asset',
    CONSTRAINT fk_ii_inspection FOREIGN KEY (inspection_id) REFERENCES inspections(id),
    CONSTRAINT fk_ii_grnitem FOREIGN KEY (grn_item_id) REFERENCES goods_receipt_items(id),
    CONSTRAINT fk_ii_asset FOREIGN KEY (asset_id) REFERENCES assets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;