-- V006 - sourcing, quotations, purchase orders, inventory lots and quality inspections
CREATE TABLE IF NOT EXISTS counterparty_products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 origin_country_code CHAR(2) NULL,
 specification TEXT NULL,
 monthly_capacity DECIMAL(20,3) NULL,
 minimum_order_quantity DECIMAL(20,3) NULL,
 unit VARCHAR(30) NULL,
 indicative_price DECIMAL(20,4) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 availability_status ENUM('available','to_produce','allocated','unavailable') NOT NULL DEFAULT 'available',
 lead_time_days INT UNSIGNED NULL,
 valid_until DATE NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 UNIQUE KEY uq_counterparty_product(counterparty_id,product_id,origin_country_code),
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
 INDEX(product_id,availability_status), INDEX(counterparty_id,active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sourcing_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 request_code VARCHAR(60) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 target_price DECIMAL(20,4) NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 required_delivery_date DATE NULL,
 response_deadline DATETIME NULL,
 specifications TEXT NULL,
 status ENUM('draft','open','under_review','awarded','closed','cancelled') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(response_deadline,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sourcing_request_suppliers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sourcing_request_id BIGINT UNSIGNED NOT NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 invitation_status ENUM('pending','sent','viewed','responded','declined') NOT NULL DEFAULT 'pending',
 invited_at DATETIME NULL,
 responded_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_rfq_supplier(sourcing_request_id,counterparty_id),
 FOREIGN KEY(sourcing_request_id) REFERENCES sourcing_requests(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS supplier_quotations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sourcing_request_id BIGINT UNSIGNED NOT NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 quotation_number VARCHAR(80) NULL,
 quoted_quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 origin_country_code CHAR(2) NULL,
 loading_location VARCHAR(190) NULL,
 availability_date DATE NULL,
 payment_terms TEXT NULL,
 validity_date DATE NULL,
 quality_specification TEXT NULL,
 internal_score DECIMAL(5,2) NULL,
 status ENUM('received','under_review','shortlisted','selected','rejected','expired','withdrawn') NOT NULL DEFAULT 'received',
 reviewed_by BIGINT UNSIGNED NULL,
 reviewed_at DATETIME NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(sourcing_request_id) REFERENCES sourcing_requests(id) ON DELETE CASCADE,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE CASCADE,
 FOREIGN KEY(reviewed_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(sourcing_request_id,status), INDEX(counterparty_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 quotation_id BIGINT UNSIGNED NULL,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 po_number VARCHAR(80) NOT NULL UNIQUE,
 product_id BIGINT UNSIGNED NOT NULL,
 quantity DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 unit_price DECIMAL(20,4) NOT NULL,
 total_amount DECIMAL(20,2) NOT NULL,
 currency CHAR(3) NOT NULL DEFAULT 'USD',
 incoterm VARCHAR(20) NULL,
 delivery_location VARCHAR(190) NULL,
 expected_delivery_date DATE NULL,
 payment_terms TEXT NULL,
 status ENUM('draft','pending_approval','approved','sent','confirmed','partially_fulfilled','fulfilled','cancelled') NOT NULL DEFAULT 'draft',
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(quotation_id) REFERENCES supplier_quotations(id) ON DELETE SET NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(operation_id,status), INDEX(counterparty_id,status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory_lots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 counterparty_id BIGINT UNSIGNED NOT NULL,
 product_id BIGINT UNSIGNED NOT NULL,
 operation_id BIGINT UNSIGNED NULL,
 purchase_order_id BIGINT UNSIGNED NULL,
 lot_code VARCHAR(100) NOT NULL UNIQUE,
 origin_country_code CHAR(2) NULL,
 production_date DATE NULL,
 expiry_date DATE NULL,
 warehouse_location VARCHAR(190) NULL,
 quantity_total DECIMAL(20,3) NOT NULL,
 quantity_available DECIMAL(20,3) NOT NULL,
 unit VARCHAR(30) NOT NULL,
 quality_grade VARCHAR(100) NULL,
 certificate_reference VARCHAR(190) NULL,
 status ENUM('expected','available','reserved','partially_allocated','allocated','in_transit','delivered','blocked','expired') NOT NULL DEFAULT 'expected',
 notes TEXT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(counterparty_id) REFERENCES counterparties(id) ON DELETE RESTRICT,
 FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE RESTRICT,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE SET NULL,
 FOREIGN KEY(purchase_order_id) REFERENCES purchase_orders(id) ON DELETE SET NULL,
 INDEX(product_id,status), INDEX(counterparty_id,status), INDEX(operation_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS quality_inspections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 operation_id BIGINT UNSIGNED NOT NULL,
 inventory_lot_id BIGINT UNSIGNED NULL,
 shipment_id BIGINT UNSIGNED NULL,
 inspection_company_id BIGINT UNSIGNED NULL,
 inspection_type ENUM('pre_shipment','loading','quantity','quality','health','phytosanitary','customs','other') NOT NULL DEFAULT 'quality',
 scheduled_date DATE NULL,
 completed_date DATE NULL,
 location VARCHAR(190) NULL,
 result ENUM('pending','passed','passed_with_reservations','failed','cancelled') NOT NULL DEFAULT 'pending',
 certificate_number VARCHAR(120) NULL,
 findings TEXT NULL,
 corrective_actions TEXT NULL,
 document_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 FOREIGN KEY(operation_id) REFERENCES operations(id) ON DELETE CASCADE,
 FOREIGN KEY(inventory_lot_id) REFERENCES inventory_lots(id) ON DELETE SET NULL,
 FOREIGN KEY(shipment_id) REFERENCES shipments(id) ON DELETE SET NULL,
 FOREIGN KEY(inspection_company_id) REFERENCES counterparties(id) ON DELETE SET NULL,
 FOREIGN KEY(document_id) REFERENCES operation_documents(id) ON DELETE SET NULL,
 INDEX(operation_id,result), INDEX(scheduled_date,result)
) ENGINE=InnoDB;