-- ============================================================
-- FashCognitive Intelligent Procurement — Missing Tables
-- ============================================================

-- 1. Warehouse Reservations (reserve stock before procurement)
CREATE TABLE IF NOT EXISTS `warehouse_reservations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `reservation_no` varchar(50) NOT NULL,
  `indent_id` int unsigned DEFAULT NULL,
  `indent_item_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `required_qty` decimal(14,4) NOT NULL,
  `reserved_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `fulfilled_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `status` enum('active','partially_fulfilled','fulfilled','cancelled') NOT NULL DEFAULT 'active',
  `reserved_by` int unsigned 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 `reservation_no` (`reservation_no`),
  KEY `indent` (`indent_id`),
  KEY `warehouse` (`warehouse_id`,`item_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Quotation Comparisons (AI scoring records)
CREATE TABLE IF NOT EXISTS `quotation_comparisons` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `rfq_id` int unsigned NOT NULL,
  `comparison_date` date NOT NULL,
  `total_quotations` int unsigned NOT NULL DEFAULT 0,
  `recommended_quotation_id` int unsigned DEFAULT NULL,
  `recommendation_reason` text,
  `comparison_data` json DEFAULT NULL,
  `created_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `rfq` (`rfq_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Vendor Scorecards (performance tracking)
CREATE TABLE IF NOT EXISTS `vendor_scorecards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `supplier_id` int unsigned NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `total_orders` int unsigned NOT NULL DEFAULT 0,
  `on_time_deliveries` int unsigned NOT NULL DEFAULT 0,
  `quality_pass_rate` decimal(5,2) NOT NULL DEFAULT 0,
  `avg_lead_time_days` decimal(6,1) NOT NULL DEFAULT 0,
  `price_competitiveness` decimal(5,2) NOT NULL DEFAULT 0,
  `response_rate` decimal(5,2) NOT NULL DEFAULT 0,
  `overall_score` decimal(5,2) NOT NULL DEFAULT 0,
  `rating` enum('A','B','C','D','F') 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`),
  KEY `supplier` (`supplier_id`),
  KEY `period` (`period_start`,`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Vendor Price History (track price trends)
CREATE TABLE IF NOT EXISTS `vendor_price_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `supplier_id` int unsigned NOT NULL,
  `item_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `unit_price` decimal(14,4) NOT NULL,
  `currency_code` varchar(10) DEFAULT 'INR',
  `quotation_id` int unsigned DEFAULT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `recorded_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `supplier_item` (`supplier_id`,`item_id`),
  KEY `date` (`recorded_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Procurement Approvals (multi-level)
CREATE TABLE IF NOT EXISTS `procurement_approvals` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `entity_type` varchar(50) NOT NULL COMMENT 'indent, pr, po, invoice',
  `entity_id` int unsigned NOT NULL,
  `approval_level` int unsigned NOT NULL DEFAULT 1,
  `approver_id` int unsigned DEFAULT NULL,
  `approver_name` varchar(255) DEFAULT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `remarks` text,
  `decided_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `entity` (`entity_type`,`entity_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Procurement Audit Logs
CREATE TABLE IF NOT EXISTS `procurement_audit_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `entity_type` varchar(50) NOT NULL,
  `entity_id` int unsigned NOT NULL,
  `action` varchar(50) NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `entity` (`entity_type`,`entity_id`),
  KEY `created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Purchase Returns
CREATE TABLE IF NOT EXISTS `purchase_returns` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `return_no` varchar(50) NOT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `grn_id` int unsigned DEFAULT NULL,
  `supplier_id` int unsigned NOT NULL,
  `warehouse_id` int unsigned NOT NULL,
  `return_date` date NOT NULL,
  `reason` enum('quality','damage','excess','wrong_item','other') NOT NULL DEFAULT 'quality',
  `total_qty` decimal(14,4) NOT NULL DEFAULT 0,
  `total_value` decimal(16,4) NOT NULL DEFAULT 0,
  `status` enum('draft','approved','shipped','received_by_supplier','cancelled') NOT NULL DEFAULT 'draft',
  `notes` text,
  `created_by` int unsigned 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`),
  UNIQUE KEY `return_no` (`return_no`),
  KEY `po` (`po_id`),
  KEY `supplier` (`supplier_id`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Purchase Return Items
CREATE TABLE IF NOT EXISTS `purchase_return_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `return_id` int unsigned NOT NULL,
  `po_item_id` int unsigned DEFAULT NULL,
  `grn_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,
  `return_qty` decimal(14,4) NOT NULL,
  `unit_rate` decimal(14,4) NOT NULL DEFAULT 0,
  `reason` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `return` (`return_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
