-- ============================================================
-- SAP MM-Style RFQ Enhancement Migration
-- ============================================================

-- 1. Enhance purchase_rfq with SAP-style fields
ALTER TABLE purchase_rfq
  ADD COLUMN document_type ENUM('standard','blanket','framework','urgent') DEFAULT 'standard' AFTER pr_id,
  ADD COLUMN currency_code VARCHAR(10) DEFAULT 'INR' AFTER document_type,
  ADD COLUMN incoterm_id INT UNSIGNED NULL AFTER currency_code,
  ADD COLUMN incoterm_name VARCHAR(50) NULL AFTER incoterm_id,
  ADD COLUMN priority ENUM('low','medium','high','urgent') DEFAULT 'medium' AFTER incoterm_name,
  ADD COLUMN approval_status ENUM('none','pending','approved','rejected') DEFAULT 'none' AFTER priority,
  ADD COLUMN approved_by INT UNSIGNED NULL AFTER approval_status,
  ADD COLUMN approved_at TIMESTAMP NULL AFTER approved_by,
  ADD COLUMN sent_at TIMESTAMP NULL AFTER approved_at,
  ADD COLUMN total_amount DECIMAL(16,4) DEFAULT 0 AFTER sent_at,
  ADD COLUMN currency_symbol VARCHAR(5) DEFAULT '₹' AFTER total_amount;

-- 2. RFQ Attachments table
CREATE TABLE IF NOT EXISTS rfq_attachments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rfq_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('specification','tech_pack','bom','quotation','drawing','image','test_report','other') DEFAULT 'other',
  uploaded_by INT UNSIGNED NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rfa_rfq (rfq_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. RFQ Approvals table (multi-level)
CREATE TABLE IF NOT EXISTS rfq_approvals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rfq_id INT UNSIGNED NOT NULL,
  approval_level INT UNSIGNED NOT NULL,
  role_name VARCHAR(100) NOT NULL,
  user_id INT UNSIGNED NULL,
  status ENUM('pending','approved','rejected','skipped') DEFAULT 'pending',
  comments TEXT NULL,
  decided_at TIMESTAMP NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rfa_rfq (rfq_id),
  KEY idx_rfa_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. RFQ Audit Logs (for timeline)
CREATE TABLE IF NOT EXISTS rfq_audit_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rfq_id INT UNSIGNED NOT NULL,
  action VARCHAR(50) NOT NULL,
  description TEXT NULL,
  user_id INT UNSIGNED NULL,
  user_name VARCHAR(255) NULL,
  old_value TEXT NULL,
  new_value TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rfal_rfq (rfq_id),
  KEY idx_rfal_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Update RFQ status to include approval states
ALTER TABLE purchase_rfq
  MODIFY COLUMN status ENUM('draft','pending_approval','approved','sent','partial_responses','closed','cancelled') NOT NULL DEFAULT 'draft';
