-- ============================================================
-- CAF v2 Migration: caf_list → caf_master + caf_trail + caf_link_token
-- Run once. Keep caf_list as caf_list_backup for reference.
-- ============================================================

-- ---- 1. Create caf_master ----
CREATE TABLE IF NOT EXISTS caf_master (
  id               INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bp_id            INT          NOT NULL,
  cycle_no         TINYINT      NOT NULL DEFAULT 1,
  current_status   VARCHAR(60)  NOT NULL DEFAULT 'DRAFT',

  cust_code        VARCHAR(30)  NOT NULL DEFAULT '',
  cust_name        VARCHAR(150) NOT NULL DEFAULT '',
  cust_type        VARCHAR(50)  NOT NULL DEFAULT '',
  credit_limit     DECIMAL(15,2) DEFAULT 0,
  credit_days      INT           DEFAULT 0,
  sales_person     VARCHAR(100) DEFAULT '',
  cs_person        VARCHAR(100) DEFAULT '',
  email_address    VARCHAR(150) DEFAULT '',
  gst_number       VARCHAR(30)  DEFAULT '',
  tan_number       VARCHAR(30)  DEFAULT '',
  invoice_type     VARCHAR(30)  DEFAULT '',

  credit_assessment_reviewer VARCHAR(100) DEFAULT '',
  approver                   VARCHAR(100) DEFAULT '',
  caf_approved_by            VARCHAR(100) DEFAULT '',

  valid_from       DATE,
  valid_until      DATE,

  company_id       INT,
  location_id      INT,
  created_by       VARCHAR(60)  NOT NULL DEFAULT '',
  created_date     DATETIME     NOT NULL DEFAULT NOW(),
  closed_date      DATETIME,

  UNIQUE KEY uk_bp_cycle   (bp_id, cycle_no),
  KEY        idx_bp_status (bp_id, current_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---- 2. Create caf_trail ----
CREATE TABLE IF NOT EXISTS caf_trail (
  id          INT         NOT NULL AUTO_INCREMENT PRIMARY KEY,
  caf_id      INT         NOT NULL,
  from_status VARCHAR(60),
  to_status   VARCHAR(60) NOT NULL,
  action_by   VARCHAR(60) NOT NULL DEFAULT '',
  action_date DATETIME    NOT NULL DEFAULT NOW(),
  remarks     TEXT,
  ip          VARCHAR(45),
  ua          TEXT,
  ekey        VARCHAR(200),

  KEY idx_caf_trail (caf_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---- 3. Create caf_link_token ----
CREATE TABLE IF NOT EXISTS caf_link_token (
  id         INT          NOT NULL AUTO_INCREMENT PRIMARY KEY,
  caf_id     INT          NOT NULL,
  trail_id   INT,
  token      VARCHAR(100) NOT NULL,
  for_role   VARCHAR(30)  NOT NULL,       -- VERIFIER | ASSESSOR | APPROVER
  sent_to    VARCHAR(60)  NOT NULL DEFAULT '',
  expires_at DATETIME     NOT NULL,
  used_at    DATETIME,
  is_active  TINYINT      NOT NULL DEFAULT 1,

  UNIQUE KEY uk_token   (token),
  KEY        idx_caf_id (caf_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---- 4. Add caf_id to mt_bp_caf ----
ALTER TABLE mt_bp_caf
  ADD COLUMN IF NOT EXISTS caf_id INT AFTER bp_id;


-- ---- 5. Migrate caf_list → caf_master (one row per bp_id, latest state wins) ----
INSERT IGNORE INTO caf_master (
  bp_id, cycle_no, current_status,
  cust_code, cust_name, cust_type,
  credit_limit, credit_days,
  sales_person, cs_person, email_address,
  gst_number, tan_number, invoice_type,
  credit_assessment_reviewer, approver, caf_approved_by,
  company_id, location_id,
  created_by, created_date
)
SELECT
  cl.bp_id,
  1 AS cycle_no,
  latest.caf_status AS current_status,
  cl.cust_code, cl.cust_name, cl.cust_type,
  cl.credit_limit, cl.credit_days,
  cl.sales_person, COALESCE(cl.cs_person,''), cl.email_address,
  COALESCE(cl.gst_number,''), COALESCE(cl.tan_number,''), COALESCE(cl.invoice_type,''),
  COALESCE(cl.credit_assessment_reviewer,''), COALESCE(cl.approver,''),
  COALESCE(cl.caf_approved_by,''),
  cl.company_id, cl.location_id,
  cl.uid, cl.created_date
FROM caf_list cl
JOIN (
  SELECT bp_id, caf_status
  FROM caf_list cl2
  WHERE cl2.id = (SELECT MAX(id) FROM caf_list WHERE bp_id = cl2.bp_id)
) latest ON latest.bp_id = cl.bp_id
WHERE cl.id = (SELECT MIN(id) FROM caf_list WHERE bp_id = cl.bp_id);


-- Set valid_from / valid_until for already-APPROVED cycles
UPDATE caf_master m
JOIN (
  SELECT bp_id, created_date
  FROM caf_list cl
  WHERE cl.id = (SELECT MAX(id) FROM caf_list WHERE bp_id = cl.bp_id AND caf_status = 'APPROVED')
) appr ON appr.bp_id = m.bp_id
SET
  m.valid_from  = DATE(appr.created_date),
  m.valid_until = DATE(DATE_ADD(appr.created_date, INTERVAL 12 MONTH)),
  m.closed_date = appr.created_date
WHERE m.current_status = 'APPROVED';

-- Set closed_date for REJECTED cycles
UPDATE caf_master m
JOIN (
  SELECT bp_id, created_date
  FROM caf_list cl
  WHERE cl.id = (SELECT MAX(id) FROM caf_list WHERE bp_id = cl.bp_id AND caf_status = 'REJECTED')
) rej ON rej.bp_id = m.bp_id
SET m.closed_date = rej.created_date
WHERE m.current_status = 'REJECTED';


-- ---- 6. Migrate caf_list rows → caf_trail ----
INSERT INTO caf_trail (caf_id, from_status, to_status, action_by, action_date, remarks, ip, ua, ekey)
SELECT
  cm.id AS caf_id,
  (
    SELECT cl2.caf_status
    FROM caf_list cl2
    WHERE cl2.bp_id = cl.bp_id AND cl2.id < cl.id
    ORDER BY cl2.id DESC LIMIT 1
  ) AS from_status,
  cl.caf_status AS to_status,
  cl.uid        AS action_by,
  cl.created_date,
  cl.remarks,
  cl.ip, cl.ua, cl.ekey
FROM caf_list cl
JOIN caf_master cm ON cm.bp_id = cl.bp_id AND cm.cycle_no = 1
ORDER BY cl.bp_id, cl.id;


-- ---- 7. Link existing attachments to cycle 1 ----
UPDATE mt_bp_caf att
JOIN caf_master cm ON cm.bp_id = att.bp_id AND cm.cycle_no = 1
SET att.caf_id = cm.id
WHERE att.caf_id IS NULL;


-- ---- 8. Rename old table as backup ----
-- RENAME TABLE caf_list TO caf_list_backup;
-- (Uncomment above line only after verifying migration is correct)


-- ============================================================
-- CAF v3 Migration: Add 18 new columns for full CAF field set
-- Run once. Safe to re-run (IF NOT EXISTS / WHERE guards).
-- ============================================================

-- ---- 9. Add new submitter fields ----
ALTER TABLE caf_master
  ADD COLUMN IF NOT EXISTS expected_monthly_business       DECIMAL(15,2) DEFAULT 0         AFTER credit_days,
  ADD COLUMN IF NOT EXISTS payment_mode                    VARCHAR(30)   DEFAULT ''         AFTER expected_monthly_business,
  ADD COLUMN IF NOT EXISTS annual_turnover                 DECIMAL(15,2) DEFAULT 0         AFTER payment_mode,
  ADD COLUMN IF NOT EXISTS date_of_incorporation           DATE                            AFTER annual_turnover,
  ADD COLUMN IF NOT EXISTS authorized_signatory_name       VARCHAR(100)  DEFAULT ''         AFTER date_of_incorporation,
  ADD COLUMN IF NOT EXISTS authorized_signatory_designation VARCHAR(60)  DEFAULT ''         AFTER authorized_signatory_name,
  ADD COLUMN IF NOT EXISTS sanctions_screening             VARCHAR(20)   DEFAULT 'PENDING'  AFTER authorized_signatory_designation,
  ADD COLUMN IF NOT EXISTS data_consent                    TINYINT(1)    DEFAULT 0         AFTER sanctions_screening,

-- ---- 10. Add assessment fields (filled by ASSESSOR) ----
  ADD COLUMN IF NOT EXISTS risk_category                   VARCHAR(20)   DEFAULT ''         AFTER caf_approved_by,
  ADD COLUMN IF NOT EXISTS assessment_remarks              TEXT                            AFTER risk_category,
  ADD COLUMN IF NOT EXISTS recommended_credit_limit        DECIMAL(15,2) DEFAULT 0         AFTER assessment_remarks,
  ADD COLUMN IF NOT EXISTS recommended_credit_days         INT           DEFAULT 0         AFTER recommended_credit_limit,
  ADD COLUMN IF NOT EXISTS insurance_required              TINYINT(1)    DEFAULT 0         AFTER recommended_credit_days,
  ADD COLUMN IF NOT EXISTS collateral_required             TINYINT(1)    DEFAULT 0         AFTER insurance_required,
  ADD COLUMN IF NOT EXISTS final_recommendation            VARCHAR(20)   DEFAULT ''         AFTER collateral_required,

-- ---- 11. Add approval fields (filled by APPROVER) ----
  ADD COLUMN IF NOT EXISTS approved_credit_limit           DECIMAL(15,2) DEFAULT 0         AFTER final_recommendation,
  ADD COLUMN IF NOT EXISTS approved_credit_days            INT           DEFAULT 0         AFTER approved_credit_limit,
  ADD COLUMN IF NOT EXISTS approval_remarks                TEXT                            AFTER approved_credit_days;

-- Backfill approved values for records already APPROVED before this migration
UPDATE caf_master
SET approved_credit_limit = credit_limit,
    approved_credit_days  = credit_days
WHERE current_status = 'APPROVED'
  AND approved_credit_limit = 0
  AND credit_limit > 0;


-- ============================================================
-- CAF v4 Migration: CAF number, currency, SOC2 audit log, comments
-- Run once. Safe to re-run (IF NOT EXISTS / WHERE guards).
-- ============================================================

-- ---- 12. CAF number and currency ----
ALTER TABLE caf_master
  ADD COLUMN IF NOT EXISTS caf_no   VARCHAR(30) DEFAULT NULL AFTER id,
  ADD COLUMN IF NOT EXISTS currency VARCHAR(10) DEFAULT 'INR' AFTER credit_days;

-- Unique index — only after adding the column
ALTER TABLE caf_master ADD UNIQUE INDEX IF NOT EXISTS uq_caf_no (caf_no);

-- Backfill caf_no for existing records (company unknown — use SYS placeholder)
UPDATE caf_master
SET caf_no = CONCAT('CAF/SYS/',
                    LPAD(YEAR(created_date) - IF(MONTH(created_date)>=4,0,1), 4, '0'),
                    SUBSTR(YEAR(created_date) - IF(MONTH(created_date)>=4,0,1) + 1, 3),
                    '/',
                    LPAD(id, 5, '0'))
WHERE caf_no IS NULL;

-- ---- 13. SOC2 audit tables (2-level: summary + field-level detail) ----
CREATE TABLE IF NOT EXISTS audit_summary (
    summary_id    BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    date_time     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id       VARCHAR(100)     NOT NULL DEFAULT '',
    module        VARCHAR(60)      NOT NULL DEFAULT '',
    action        VARCHAR(30)      NOT NULL,
    reference_no  VARCHAR(100)     DEFAULT NULL,
    description   TEXT             DEFAULT NULL,
    change_count  INT              NOT NULL DEFAULT 0,
    ip_address    VARCHAR(45)      NOT NULL DEFAULT '',
    ua            VARCHAR(500)     NOT NULL DEFAULT '',
    session_ref   VARCHAR(16)      DEFAULT NULL,
    status        VARCHAR(10)      NOT NULL DEFAULT 'SUCCESS',
    company_id    VARCHAR(20)      DEFAULT NULL,
    location_id   VARCHAR(20)      DEFAULT NULL,
    PRIMARY KEY  (summary_id),
    INDEX idx_reference (reference_no),
    INDEX idx_user      (user_id),
    INDEX idx_date      (date_time),
    INDEX idx_module    (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS system_audit_log (
    log_id        BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    summary_id    BIGINT UNSIGNED  DEFAULT NULL,
    date_time     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id       VARCHAR(100)     NOT NULL DEFAULT '',
    module        VARCHAR(60)      NOT NULL DEFAULT '',
    action        VARCHAR(30)      NOT NULL,
    reference_no  VARCHAR(100)     DEFAULT NULL,
    field_label   VARCHAR(100)     DEFAULT NULL,
    old_value     TEXT             DEFAULT NULL,
    new_value     TEXT             DEFAULT NULL,
    ip_address    VARCHAR(45)      NOT NULL DEFAULT '',
    ua            VARCHAR(500)     NOT NULL DEFAULT '',
    session_ref   VARCHAR(16)      DEFAULT NULL,
    status        VARCHAR(10)      NOT NULL DEFAULT 'SUCCESS',
    company_id    VARCHAR(20)      DEFAULT NULL,
    location_id   VARCHAR(20)      DEFAULT NULL,
    PRIMARY KEY  (log_id),
    INDEX idx_summary   (summary_id),
    INDEX idx_reference (reference_no),
    INDEX idx_user      (user_id),
    INDEX idx_date      (date_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 14. CAF comments (internal free-form notes) ----
CREATE TABLE IF NOT EXISTS caf_comment (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    caf_id      INT             NOT NULL,
    comment     TEXT            NOT NULL,
    added_by    VARCHAR(100)    NOT NULL DEFAULT '',
    added_role  VARCHAR(30)     NOT NULL DEFAULT '',
    added_date  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_caf (caf_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---- 15. Correction cycle linkage ----
ALTER TABLE caf_master ADD COLUMN correction_of_caf_id INT NULL DEFAULT NULL;
ALTER TABLE caf_master ADD COLUMN correction_note VARCHAR(500) NULL DEFAULT NULL;

-- ---- 16. Draft (proforma) CAF number — generated at creation; caf_no is the final number generated at approval ----
ALTER TABLE caf_master ADD COLUMN draft_caf_no VARCHAR(40) NULL DEFAULT NULL;
