cbsnexora/backend-schema/V1__client_schema_extensions.sql

-- ============================================================
-- CBS Nexora — Client Schema Extensions
-- Fineract DB Migration Script
-- Run this AFTER backing up your database
-- ============================================================

-- ─── 1. m_client — New columns ───
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS nationality VARCHAR(20) DEFAULT 'INDIAN';
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS annual_turnover DECIMAL(18,2);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS credit_limit DECIMAL(18,2);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS is_minor TINYINT(1) DEFAULT 0;
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS age_years INT;
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS age_months INT;
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS age_days INT;
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS guardian_name VARCHAR(200);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS guardian_relation VARCHAR(50);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS guardian_mobile VARCHAR(15);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS guardian_id_number VARCHAR(50);
ALTER TABLE m_client ADD COLUMN IF NOT EXISTS guardian_address TEXT;

-- ─── 2. m_client_non_person — New columns ───
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS date_of_registration DATE;
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS enterprise_pan VARCHAR(10);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS cin_number VARCHAR(21);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS authorized_signatory VARCHAR(200);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS additional_info VARCHAR(500);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS credit_limit DECIMAL(18,2);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS is_msme TINYINT(1) DEFAULT 0;
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS udyam_number VARCHAR(19);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS enterprise_name VARCHAR(300);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS business_sector VARCHAR(50);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS msme_turnover DECIMAL(18,2);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS msme_investment DECIMAL(18,2);
ALTER TABLE m_client_non_person ADD COLUMN IF NOT EXISTS msme_type VARCHAR(20);

-- ─── 3. m_client_kyc — New columns ───
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS has_pan TINYINT(1) DEFAULT 1;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS pan_aadhaar_link_status VARCHAR(20);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS kyc_document_name VARCHAR(100);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS kyc_document_number VARCHAR(100);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS kyc_doc_issue_date DATE;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS kyc_doc_expiry_date DATE;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS kyc_expiry_date DATE;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS aml_check_status VARCHAR(20) DEFAULT 'CLEAR';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS banned_entity_match VARCHAR(20) DEFAULT 'NO';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS willful_defaulter VARCHAR(5) DEFAULT 'NO';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS ecgc_sal_flag VARCHAR(5) DEFAULT 'NO';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS dormant_flag VARCHAR(20) DEFAULT 'ACTIVE';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS account_status VARCHAR(20) DEFAULT 'ACTIVE';
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS freeze_reason VARCHAR(50);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS freeze_authority VARCHAR(50);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS form60_type VARCHAR(10);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS form60_date DATE;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS form15_applicable TINYINT(1) DEFAULT 0;
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS form15_type VARCHAR(5);
ALTER TABLE m_client_kyc ADD COLUMN IF NOT EXISTS form15_fy VARCHAR(10);

-- ─── 4. m_client_entity_kyc — New table ───
CREATE TABLE IF NOT EXISTS m_client_entity_kyc (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  client_id BIGINT NOT NULL,
  constitution_type VARCHAR(50),
  -- MSME
  is_msme TINYINT(1) DEFAULT 0,
  udyam_number VARCHAR(19),
  enterprise_name VARCHAR(300),
  business_sector VARCHAR(50),
  msme_turnover DECIMAL(18,2),
  msme_investment DECIMAL(18,2),
  msme_type VARCHAR(20),
  -- Entity KYC Document
  entity_kyc_doc_type VARCHAR(100),
  entity_kyc_doc_number VARCHAR(100),
  entity_kyc_doc_issue_date DATE,
  entity_kyc_doc_expiry_date DATE,
  -- Authorized Signatory
  signatory_name VARCHAR(200),
  signatory_designation VARCHAR(100),
  signatory_pan VARCHAR(10),
  signatory_aadhaar_masked VARCHAR(12),
  signatory_kyc_doc_type VARCHAR(100),
  signatory_kyc_doc_number VARCHAR(100),
  -- Authorization Document
  authorization_doc_type VARCHAR(100),
  authorization_doc_number VARCHAR(100),
  authorization_date DATE,
  -- Risk
  entity_risk_category INT DEFAULT 1,
  entity_remarks TEXT,
  -- Audit
  created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES m_client(id) ON DELETE CASCADE,
  UNIQUE KEY uk_client_entity_kyc (client_id)
);

-- ─── 5. m_client_address_india — New table ───
CREATE TABLE IF NOT EXISTS m_client_address_india (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  client_id BIGINT NOT NULL,
  address_type ENUM('PERMANENT','CURRENT','OFFICE','CORRESPONDENCE') NOT NULL,
  address_line_1 VARCHAR(500),
  address_line_2 VARCHAR(500),
  pin_code VARCHAR(6),
  locality VARCHAR(200),
  city VARCHAR(200),
  district VARCHAR(200),
  state VARCHAR(200),
  country VARCHAR(100) DEFAULT 'India',
  landmark VARCHAR(300),
  -- Permanent-specific
  proof_type VARCHAR(100),
  proof_doc_number VARCHAR(100),
  -- Current-specific
  residence_type VARCHAR(50),
  years_at_address INT,
  same_as_permanent TINYINT(1) DEFAULT 0,
  -- Office-specific
  org_name VARCHAR(300),
  department VARCHAR(200),
  office_phone VARCHAR(20),
  employment_type VARCHAR(100),
  employee_id VARCHAR(50),
  years_with_org INT,
  -- Correspondence-specific
  comm_mode VARCHAR(50),
  contact_number VARCHAR(20),
  -- Audit
  created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_modified_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES m_client(id) ON DELETE CASCADE,
  UNIQUE KEY uk_client_address_type (client_id, address_type)
);

-- ─── 6. Constitution Type code values (if not using API-driven options) ───
-- Find Gender code and add missing values
SET @gender_code_id = (SELECT id FROM m_code WHERE code_name = 'Gender' LIMIT 1);
INSERT IGNORE INTO m_code_value (code_id, code_value, order_position, is_active)
VALUES
  (@gender_code_id, 'Transgender', 3, 1),
  (@gender_code_id, 'Other', 4, 1);

-- ─── 7. Indexes for performance ───
CREATE INDEX IF NOT EXISTS idx_client_entity_kyc_client ON m_client_entity_kyc(client_id);
CREATE INDEX IF NOT EXISTS idx_client_address_india_client ON m_client_address_india(client_id);
CREATE INDEX IF NOT EXISTS idx_client_address_india_type ON m_client_address_india(client_id, address_type);
CREATE INDEX IF NOT EXISTS idx_client_kyc_expiry ON m_client_kyc(kyc_expiry_date);
CREATE INDEX IF NOT EXISTS idx_client_kyc_aml ON m_client_kyc(aml_check_status);
CREATE INDEX IF NOT EXISTS idx_client_minor ON m_client(is_minor);