-- ============================================================
-- SAP-Style Purchase Order Enhancement Migration
-- ============================================================

-- 1. Enhance procurement_orders with SAP-style header fields
ALTER TABLE procurement_orders
  ADD COLUMN document_type ENUM('standard','service','stock_transfer','blanket') DEFAULT 'standard' AFTER po_no,
  ADD COLUMN purchasing_org VARCHAR(100) NULL AFTER document_type,
  ADD COLUMN purchasing_group VARCHAR(100) NULL AFTER purchasing_org,
  ADD COLUMN incoterms VARCHAR(50) NULL AFTER purchasing_group,
  ADD COLUMN delivery_terms TEXT NULL AFTER incoterms,
  ADD COLUMN currency_code VARCHAR(10) DEFAULT 'INR' AFTER delivery_terms,
  ADD COLUMN freight_amount DECIMAL(16,4) DEFAULT 0 AFTER currency_code,
  ADD COLUMN insurance_amount DECIMAL(16,4) DEFAULT 0 AFTER freight_amount,
  ADD COLUMN discount_amount DECIMAL(16,4) DEFAULT 0 AFTER insurance_amount,
  ADD COLUMN subtotal DECIMAL(16,4) DEFAULT 0 AFTER discount_amount,
  ADD COLUMN tax_rate DECIMAL(8,4) DEFAULT 18.0000 AFTER subtotal,
  ADD COLUMN grand_total DECIMAL(16,4) DEFAULT 0 AFTER tax_rate,
  ADD COLUMN approval_level INT DEFAULT 0 AFTER grand_total,
  ADD COLUMN sent_to_supplier_at TIMESTAMP NULL AFTER approval_level,
  ADD COLUMN payment_status ENUM('unpaid','partial','paid','overdue') DEFAULT 'unpaid' AFTER sent_to_supplier_at,
  ADD COLUMN payment_completed_at TIMESTAMP NULL AFTER payment_status,
  ADD COLUMN closed_at TIMESTAMP NULL AFTER payment_completed_at,
  ADD COLUMN revised_at TIMESTAMP NULL AFTER closed_at,
  ADD COLUMN revision_no INT DEFAULT 0 AFTER revised_at;

-- 2. Enhance procurement_order_items with line numbers + detail fields
ALTER TABLE procurement_order_items
  ADD COLUMN line_no INT NOT NULL DEFAULT 10 AFTER po_id,
  ADD COLUMN vendor_material_code VARCHAR(100) NULL AFTER item_id,
  ADD COLUMN material_description VARCHAR(255) NULL AFTER vendor_material_code,
  ADD COLUMN tax_code VARCHAR(20) NULL AFTER notes,
  ADD COLUMN discount_pct DECIMAL(8,4) DEFAULT 0 AFTER tax_code,
  ADD COLUMN discount_amount DECIMAL(16,4) DEFAULT 0 AFTER discount_pct,
  ADD COLUMN freight_per_unit DECIMAL(14,4) DEFAULT 0 AFTER discount_amount,
  ADD COLUMN qc_required TINYINT(1) DEFAULT 1 AFTER freight_per_unit,
  ADD COLUMN gr_required TINYINT(1) DEFAULT 1 AFTER qc_required,
  ADD COLUMN invoice_verification_required TINYINT(1) DEFAULT 1 AFTER gr_required,
  ADD COLUMN batch_required TINYINT(1) DEFAULT 0 AFTER invoice_verification_required,
  ADD COLUMN delivery_date DATE NULL AFTER expected_date,
  ADD COLUMN plant_id INT UNSIGNED NULL AFTER delivery_date;

-- 3. Delivery Schedules table (staggered deliveries per PO item)
CREATE TABLE IF NOT EXISTS po_delivery_schedules (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_id INT UNSIGNED NOT NULL,
  po_item_id INT UNSIGNED NOT NULL,
  delivery_no INT NOT NULL DEFAULT 1,
  delivery_date DATE NOT NULL,
  quantity DECIMAL(14,4) NOT NULL,
  received_qty DECIMAL(14,4) DEFAULT 0,
  status ENUM('pending','partially_received','fully_received','cancelled') DEFAULT 'pending',
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_po_id (po_id),
  KEY idx_po_item_id (po_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Approval Workflow table
CREATE TABLE IF NOT EXISTS po_approvals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_id INT UNSIGNED NOT NULL,
  approval_level INT NOT NULL,
  role_name VARCHAR(100) NOT NULL,
  user_id INT UNSIGNED NULL,
  status ENUM('pending','approved','rejected','skipped') DEFAULT 'pending',
  comments TEXT NULL,
  approved_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_po_id (po_id),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Attachments table
CREATE TABLE IF NOT EXISTS po_attachments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_id INT UNSIGNED NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  file_type VARCHAR(100) NULL,
  file_size INT UNSIGNED NULL,
  category ENUM('quotation','tech_pack','bom','specification','agreement','other') DEFAULT 'other',
  uploaded_by INT UNSIGNED NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_po_id (po_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. AI Insights cache table
CREATE TABLE IF NOT EXISTS po_ai_insights (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_id INT UNSIGNED NOT NULL,
  supplier_score DECIMAL(5,2) NULL,
  previous_price DECIMAL(14,4) NULL,
  price_change_pct DECIMAL(8,4) NULL,
  lead_time_days INT NULL,
  on_time_delivery_pct DECIMAL(5,2) NULL,
  quality_acceptance_pct DECIMAL(5,2) NULL,
  alternative_supplier_id INT UNSIGNED NULL,
  alternative_price DECIMAL(14,4) NULL,
  estimated_savings DECIMAL(16,4) NULL,
  recommendation TEXT NULL,
  duplicate_po_alert TINYINT(1) DEFAULT 0,
  excess_stock_alert TINYINT(1) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_po_id (po_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Update PO statuses to full SAP-style workflow
ALTER TABLE procurement_orders
  MODIFY COLUMN status ENUM(
    'draft','pending_approval','approved','sent_to_supplier',
    'partially_received','fully_received',
    'invoice_received','payment_completed','closed',
    'cancelled'
  ) DEFAULT 'draft';

-- 8. Update PR statuses
ALTER TABLE purchase_requisitions
  MODIFY COLUMN status ENUM(
    'draft','pending_approval','approved','partially_ordered','fully_ordered',
    'rejected','cancelled'
  ) DEFAULT 'draft';
