-- ============================================================
-- FashCognitive - Procurement Module Phase 2
-- Missing Tables: Adjustments, Returns, RFQ, Quotations,
-- ASN, Supplier Invoices, PO Revisions, Approvals, QC
-- ============================================================

-- -----------------------------------------------------------
-- 17. STOCK ADJUSTMENTS (formal adjustment workflow)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_adjustments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `adjustment_no` varchar(50) NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `adjustment_type` enum('shortage','surplus','damage','expiry','reclassification','opening') NOT NULL DEFAULT 'shortage',
  `reason` text,
  `requested_by` int unsigned DEFAULT NULL,
  `approved_by` int unsigned DEFAULT NULL,
  `status` enum('draft','pending_approval','approved','rejected','posted') NOT NULL DEFAULT 'draft',
  `posted_at` timestamp NULL DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sa_no` (`adjustment_no`, `company_id`),
  KEY `idx_sa_warehouse` (`warehouse_id`),
  KEY `idx_sa_status` (`status`),
  KEY `idx_sa_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `stock_adjustment_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `adjustment_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `system_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `physical_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `adjustment_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sai_adj` (`adjustment_id`),
  KEY `idx_sai_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 18. MATERIAL RETURNS (production → warehouse)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `material_returns` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `return_no` varchar(50) NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `indent_id` int unsigned DEFAULT NULL,
  `issue_reference` varchar(100) DEFAULT NULL,
  `returned_by` int unsigned DEFAULT NULL,
  `return_date` date NOT NULL,
  `status` enum('draft','confirmed','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mr_no` (`return_no`, `company_id`),
  KEY `idx_mr_warehouse` (`warehouse_id`),
  KEY `idx_mr_status` (`status`),
  KEY `idx_mr_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `material_return_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `return_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `issued_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `used_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `returned_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `condition` enum('good','damaged','expired') NOT NULL DEFAULT 'good',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mri_return` (`return_id`),
  KEY `idx_mri_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 19. MATERIAL ISSUES (warehouse → production)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `material_issues` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `issue_no` varchar(50) NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `factory_id` int unsigned DEFAULT NULL,
  `indent_id` int unsigned DEFAULT NULL,
  `production_order` varchar(100) DEFAULT NULL,
  `style_no` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `issued_to` int unsigned DEFAULT NULL,
  `issue_date` date NOT NULL,
  `status` enum('draft','issued','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mi_no` (`issue_no`, `company_id`),
  KEY `idx_mi_warehouse` (`warehouse_id`),
  KEY `idx_mi_status` (`status`),
  KEY `idx_mi_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `material_issue_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `issue_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `requested_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `issued_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `unit_cost` decimal(14,4) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mii_issue` (`issue_id`),
  KEY `idx_mii_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 20. PURCHASE RFQ (Request for Quotation)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `purchase_rfq` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `rfq_no` varchar(50) NOT NULL,
  `pr_id` int unsigned DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `required_date` date DEFAULT NULL,
  `closing_date` date DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `status` enum('draft','sent','partial_responses','closed','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_rfq_no` (`rfq_no`, `company_id`),
  KEY `idx_rfq_pr` (`pr_id`),
  KEY `idx_rfq_status` (`status`),
  KEY `idx_rfq_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_rfq_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `rfq_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `required_qty` decimal(14,4) NOT NULL,
  `specifications` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rfi_rfq` (`rfq_id`),
  KEY `idx_rfi_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `purchase_rfq_suppliers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `rfq_id` int unsigned NOT NULL,
  `supplier_id` int unsigned NOT NULL,
  `status` enum('invited','responded','declined') NOT NULL DEFAULT 'invited',
  `responded_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_rfs_rfq` (`rfq_id`),
  KEY `idx_rfs_supplier` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 21. SUPPLIER QUOTATIONS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_quotations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `rfq_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned NOT NULL,
  `quotation_no` varchar(100) DEFAULT NULL,
  `quotation_date` date NOT NULL,
  `valid_until` date DEFAULT NULL,
  `payment_terms` varchar(255) DEFAULT NULL,
  `delivery_days` int unsigned DEFAULT NULL,
  `moq` varchar(255) DEFAULT NULL,
  `currency_id` int unsigned DEFAULT NULL,
  `total_amount` decimal(16,4) DEFAULT 0,
  `notes` text,
  `status` enum('draft','submitted','shortlisted','accepted','rejected') NOT NULL DEFAULT 'draft',
  `ai_score` decimal(5,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sq_rfq` (`rfq_id`),
  KEY `idx_sq_supplier` (`supplier_id`),
  KEY `idx_sq_status` (`status`),
  KEY `idx_sq_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `supplier_quotation_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `quotation_id` int unsigned NOT NULL,
  `rfq_item_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `qty` decimal(14,4) NOT NULL,
  `unit_price` decimal(14,4) NOT NULL,
  `total_price` decimal(16,4) DEFAULT NULL,
  `lead_time_days` int unsigned DEFAULT NULL,
  `moq` int unsigned DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sqi_quotation` (`quotation_id`),
  KEY `idx_sqi_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 22. ADVANCE SHIPPING NOTICES (ASN)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `asn` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `asn_no` varchar(50) NOT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `ship_date` date DEFAULT NULL,
  `eta_date` date DEFAULT NULL,
  `actual_arrival` date DEFAULT NULL,
  `vehicle_no` varchar(100) DEFAULT NULL,
  `challan_no` varchar(100) DEFAULT NULL,
  `invoice_no` varchar(100) DEFAULT NULL,
  `total_boxes` int unsigned DEFAULT NULL,
  `total_rolls` int unsigned DEFAULT NULL,
  `total_qty` decimal(14,4) DEFAULT 0,
  `status` enum('draft','in_transit','arrived','under_inspection','received','rejected') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_asn_no` (`asn_no`, `company_id`),
  KEY `idx_asn_po` (`po_id`),
  KEY `idx_asn_supplier` (`supplier_id`),
  KEY `idx_asn_status` (`status`),
  KEY `idx_asn_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `asn_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `asn_id` int unsigned NOT NULL,
  `po_item_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned NOT NULL,
  `shipped_qty` decimal(14,4) NOT NULL,
  `received_qty` decimal(14,4) DEFAULT NULL,
  `boxes` int unsigned DEFAULT NULL,
  `rolls` int unsigned DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ai_asn` (`asn_id`),
  KEY `idx_ai_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 23. SUPPLIER INVOICES
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_invoices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `invoice_no` varchar(100) NOT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `grn_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned NOT NULL,
  `invoice_date` date NOT NULL,
  `invoice_amount` decimal(16,4) NOT NULL,
  `tax_amount` decimal(16,4) DEFAULT 0,
  `total_amount` decimal(16,4) DEFAULT 0,
  `currency_id` int unsigned DEFAULT NULL,
  `exchange_rate` decimal(14,6) DEFAULT 1.000000,
  `payment_status` enum('pending','partial','paid','overdue') NOT NULL DEFAULT 'pending',
  `paid_amount` decimal(16,4) DEFAULT 0,
  `paid_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `matching_status` enum('pending','matched','mismatch','exception') NOT NULL DEFAULT 'pending',
  `notes` text,
  `status` enum('draft','submitted','verified','approved','paid','cancelled') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_si_po` (`po_id`),
  KEY `idx_si_supplier` (`supplier_id`),
  KEY `idx_si_status` (`status`),
  KEY `idx_si_company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 24. PO REVISIONS (amendment history)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `po_revisions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `revision_no` int unsigned NOT NULL DEFAULT 1,
  `revision_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `revision_by` int unsigned DEFAULT NULL,
  `reason` text,
  `changes_json` longtext,
  `status` enum('draft','superseded') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_por_po` (`po_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 25. MATERIAL INDENT APPROVALS (audit trail)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `material_indent_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `indent_id` int unsigned NOT NULL,
  `approver_id` int unsigned NOT NULL,
  `action` enum('submitted','approved','rejected','revision_requested') NOT NULL,
  `remarks` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_mia_indent` (`indent_id`),
  KEY `idx_mia_approver` (`approver_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 26. SUPPLIER PORTAL USERS
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `supplier_portal_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `role` enum('admin','viewer') NOT NULL DEFAULT 'admin',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `last_login` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_spu_email` (`email`),
  KEY `idx_spu_supplier` (`supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Add expected_delivery to indent_allocations if missing
-- -----------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'indent_allocations' AND COLUMN_NAME = 'expected_delivery');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `indent_allocations` ADD COLUMN `expected_delivery` date DEFAULT NULL AFTER `status`',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
