-- ============================================================
-- OREMUS - CRM MODULE TABLES
-- Database: Oremus
-- Dependencies: Shared (users, departments, organizations)
-- ============================================================

-- ============================================================
-- MASTER / LOOKUP TABLES
-- ============================================================

-- --------------------------------------------------------
-- REGION_MASTER
-- Geographic regions for lead classification
-- --------------------------------------------------------
CREATE TABLE `REGION_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `REGION_NAME` varchar(50) NOT NULL,
  `REGION_CODE` varchar(10) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- INDUSTRY_MASTER
-- Industry verticals for lead / contract classification
-- --------------------------------------------------------
CREATE TABLE `INDUSTRY_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `INDUSTRY_NAME` varchar(100) NOT NULL,
  `INDUSTRY_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- COMPANY_SIZE_MASTER
-- Company size buckets (e.g., 1-50, 51-200, 200+)
-- --------------------------------------------------------
CREATE TABLE `COMPANY_SIZE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `SIZE_LABEL` varchar(50) NOT NULL COMMENT 'e.g. "1-50 Employees"',
  `SIZE_CODE` varchar(20) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- ENGAGEMENT_TYPE_MASTER
-- Type of engagement (e.g., Payroll, Accounting, Audit)
-- Used in LEADS.ENGAGEMENT_TYPE (JSON) and CONTRACT.ENGAGEMENT_TYPE_IDS (JSON)
-- --------------------------------------------------------
CREATE TABLE `ENGAGEMENT_TYPE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `ENGAGEMENT_NAME` varchar(100) NOT NULL,
  `ENGAGEMENT_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- CLIENT_TYPE_MASTER
-- Classification of clients (e.g., Domestic, Overseas)
-- --------------------------------------------------------
CREATE TABLE `CLIENT_TYPE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `CLIENT_TYPE_NAME` varchar(50) NOT NULL,
  `CLIENT_TYPE_CODE` varchar(20) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LEAD_SOURCE_MASTER
-- How leads are acquired (e.g., Referral, LinkedIn, Walk-in)
-- --------------------------------------------------------
CREATE TABLE `LEAD_SOURCE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `SOURCE_NAME` varchar(150) NOT NULL,
  `SOURCE_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LEAD_STATUS_MASTER
-- Pipeline stages for leads (e.g., New, Contacted, Proposal, Won, Lost)
-- DISPLAY_ORDER: for pipeline column ordering
-- --------------------------------------------------------
CREATE TABLE `LEAD_STATUS_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `STATUS_NAME` varchar(100) NOT NULL,
  `STATUS_CODE` varchar(50) NOT NULL,
  `DISPLAY_ORDER` int NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- DEAL_STAGE_MASTER
-- Sales deal stages (separate from lead status, CRM pipeline)
-- DISPLAY_ORDER: column order in Kanban view
-- --------------------------------------------------------
CREATE TABLE `DEAL_STAGE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `STAGE_NAME` varchar(100) NOT NULL,
  `STAGE_CODE` varchar(50) NOT NULL,
  `DISPLAY_ORDER` int NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LOSS_REASON_MASTER
-- Reasons for losing a lead (captured when lead marked Lost)
-- --------------------------------------------------------
CREATE TABLE `LOSS_REASON_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `REASON_NAME` varchar(150) NOT NULL,
  `REASON_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- ACTIVITY_TYPE_MASTER
-- Types of activities logged against leads/contracts
-- (e.g., Call, Email, Meeting, Demo)
-- --------------------------------------------------------
CREATE TABLE `ACTIVITY_TYPE_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `ACTIVITY_NAME` varchar(100) NOT NULL,
  `ACTIVITY_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- ENTITY_TYPE_MASTER
-- Defines entity types used across CRM (e.g., OCS, OCNA)
-- ENTITY_CODE is used in LEADS.ENTITY enum
-- --------------------------------------------------------
CREATE TABLE `ENTITY_TYPE_MASTER` (
  `ENTITY_TYPE_ID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `ENTITY_CODE` varchar(20) NOT NULL,
  `ENTITY_NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `IS_ACTIVE` tinyint(1) DEFAULT '1',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`ENTITY_TYPE_ID`),
  UNIQUE KEY `ENTITY_CODE` (`ENTITY_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- CONTRACT_STATUS_MASTER
-- Status of contracts (e.g., Active, Expired, Terminated)
-- --------------------------------------------------------
CREATE TABLE `CONTRACT_STATUS_MASTER` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `STATUS_NAME` varchar(100) NOT NULL,
  `STATUS_CODE` varchar(50) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- DOCUMENT_TYPE_MASTER
-- Types of contract documents (e.g., MSA, SOW, NDA, Invoice)
-- --------------------------------------------------------
CREATE TABLE `DOCUMENT_TYPE_MASTER` (
  `DOCUMENT_TYPE_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `DOCUMENT_TYPE_NAME` varchar(150) NOT NULL,
  `DESCRIPTION` text,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`DOCUMENT_TYPE_ID`),
  UNIQUE KEY `UK_DOCUMENT_TYPE_NAME` (`DOCUMENT_TYPE_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- BILLING_TYPE_MASTER  (also in Timesheet module - shared master)
-- Referenced in CONTRACT_DOCUMENT for billing model
-- --------------------------------------------------------
-- Note: Table is defined in Timesheet/schema.sql
-- Cross-referenced here for clarity

-- --------------------------------------------------------
-- RENEWAL_STATUS_MASTER
-- Contract document renewal status (e.g., Active, Due, Renewed, Expired)
-- --------------------------------------------------------
CREATE TABLE `RENEWAL_STATUS_MASTER` (
  `RENEWAL_STATUS_ID` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `STATUS_NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`RENEWAL_STATUS_ID`),
  UNIQUE KEY `UK_RENEWAL_STATUS_NAME` (`STATUS_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- CURRENCY_MASTER
-- Supported currencies for deal valuation
-- CURRENCY_RATE: exchange rate to base (auto-updated by cron)
-- --------------------------------------------------------
CREATE TABLE `CURRENCY_MASTER` (
  `CURRENCY_ID` int NOT NULL AUTO_INCREMENT,
  `CURRENCY_NAME` varchar(100) NOT NULL,
  `CURRENCY_CODE` varchar(10) NOT NULL,
  `CURRENCY_SYMBOL` varchar(10) DEFAULT NULL,
  `CURRENCY_LOGO` varchar(255) DEFAULT NULL,
  `COUNTRY_FLAG` text,
  `CURRENCY_COUNTRY` varchar(100) NOT NULL,
  `CURRENCY_RATE` decimal(20,16) DEFAULT NULL COMMENT 'Updated by autoSaveCurrency cron',
  `ACTIVE` tinyint(1) DEFAULT '1',
  `DELETED` tinyint(1) DEFAULT '0',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CURRENCY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- TO_DO_MASTER
-- Pre-defined to-do actions associated with leads
-- Used in LEADS.TO_DO_ID to set a next action type
-- --------------------------------------------------------
CREATE TABLE `TO_DO_MASTER` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `TO_DO_NAME` varchar(255) NOT NULL,
  `TO_DO_CODE` varchar(255) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- CORE CRM TABLES
-- ============================================================

-- --------------------------------------------------------
-- LEADS
-- Central CRM entity - all prospects/clients start here
-- ENTITY: OCS or OCNA (which Oremus legal entity)
-- ENGAGEMENT_TYPE: JSON array of engagement type IDs
-- PROJECT_TYPE: MONTHLY_RETAINER | ONE_TIME_PROJECT | ANNUAL_RETAINER
-- CONVERTED: 1 = lead has become a client (CONTRACT created)
-- STAGE_ID -> DEAL_STAGE_MASTER.ID
-- TO_DO_ID -> TO_DO_MASTER.ID (next action)
-- FK: SOURCE_ID -> LEAD_SOURCE_MASTER.ID
-- FK: STATUS_ID -> LEAD_STATUS_MASTER.ID
-- FK: ASSIGNED_TO -> users.ID
-- FK: CREATED_BY -> users.ID
-- FK: INDUSTRY_ID -> INDUSTRY_MASTER.ID
-- FK: COMPANY_SIZE_ID -> COMPANY_SIZE_MASTER.ID
-- FK: CURRENCY_ID -> CURRENCY_MASTER.CURRENCY_ID
-- --------------------------------------------------------
CREATE TABLE `LEADS` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `LEAD_NAME` varchar(200) NOT NULL,
  `COMPANY_NAME` varchar(200) DEFAULT NULL,
  `PRIMARY_PHONE` varchar(20) DEFAULT NULL,
  `PRIMARY_EMAIL` varchar(150) DEFAULT NULL,
  `SOURCE_ID` bigint NOT NULL COMMENT 'FK -> LEAD_SOURCE_MASTER.ID',
  `STATUS_ID` bigint NOT NULL COMMENT 'FK -> LEAD_STATUS_MASTER.ID',
  `ASSIGNED_TO` bigint DEFAULT NULL COMMENT 'FK -> users.ID (CRM exec handling this lead)',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `UPDATED_AT` datetime DEFAULT NULL,
  `UPDATED_BY` bigint DEFAULT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CLIENT_TYPE` varchar(20) NOT NULL COMMENT 'FK -> CLIENT_TYPE_MASTER.CLIENT_TYPE_CODE',
  `REGION` varchar(10) NOT NULL COMMENT 'FK -> REGION_MASTER.REGION_CODE',
  `INDUSTRY_ID` bigint NOT NULL COMMENT 'FK -> INDUSTRY_MASTER.ID',
  `COMPANY_SIZE_ID` bigint NOT NULL COMMENT 'FK -> COMPANY_SIZE_MASTER.ID',
  `ENGAGEMENT_TYPE` json NOT NULL COMMENT 'JSON array of ENGAGEMENT_TYPE_MASTER IDs',
  `ENTITY` enum('OCS','OCNA') NOT NULL DEFAULT 'OCS' COMMENT 'Which Oremus legal entity',
  `RESTART_DATE` date DEFAULT NULL COMMENT 'If lost and revived',
  `CONVERSION_DATE` date DEFAULT NULL COMMENT 'Date lead converted to client',
  `ESTIMATED_DEAL_VALUE` varchar(20) NOT NULL DEFAULT '0',
  `CURRENCY_ID` int NOT NULL DEFAULT '138' COMMENT 'FK -> CURRENCY_MASTER.CURRENCY_ID (INR default)',
  `PROJECT_TYPE` enum('MONTHLY_RETAINER','ONE_TIME_PROJECT','ANNUAL_RETAINER') NOT NULL DEFAULT 'ONE_TIME_PROJECT',
  `STAGE_ID` int DEFAULT '1' COMMENT 'FK -> DEAL_STAGE_MASTER.ID',
  `TO_DO_ID` int DEFAULT NULL COMMENT 'FK -> TO_DO_MASTER.ID (next action)',
  `DUE_DATE` date DEFAULT NULL COMMENT 'Due date for the next to-do action',
  `CONVERTED` tinyint NOT NULL DEFAULT '0' COMMENT '1 = lead converted, CONTRACT record created',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LEADS_HISTORY
-- Full audit trail of every change made to a lead record
-- ACTION_TYPE: e.g., CREATED, UPDATED, STATUS_CHANGED, ASSIGNED
-- --------------------------------------------------------
CREATE TABLE `LEADS_HISTORY` (
  `HISTORY_ID` bigint NOT NULL AUTO_INCREMENT,
  `ORIGINAL_ID` bigint NOT NULL COMMENT 'FK -> LEADS.ID',
  `LEAD_NAME` varchar(200) DEFAULT NULL,
  `COMPANY_NAME` varchar(200) DEFAULT NULL,
  `PRIMARY_PHONE` varchar(20) DEFAULT NULL,
  `PRIMARY_EMAIL` varchar(150) DEFAULT NULL,
  `SOURCE_ID` bigint DEFAULT NULL,
  `STATUS_ID` bigint DEFAULT NULL,
  `ASSIGNED_TO` bigint DEFAULT NULL,
  `ACTION_TYPE` varchar(30) NOT NULL COMMENT 'e.g. CREATED, UPDATED, STATUS_CHANGED',
  `CHANGED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CHANGED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `CLIENT_TYPE` varchar(20) DEFAULT NULL,
  `REGION` varchar(10) DEFAULT NULL,
  `INDUSTRY_ID` bigint DEFAULT NULL,
  `COMPANY_SIZE_ID` bigint DEFAULT NULL,
  `ENGAGEMENT_TYPE` varchar(50) DEFAULT NULL,
  `RESTART_DATE` date DEFAULT NULL,
  `ENTITY` enum('OCS','OCNA') NOT NULL DEFAULT 'OCS',
  `CONVERSION_DATE` date DEFAULT NULL,
  `ESTIMATED_DEAL_VALUE` decimal(11,0) NOT NULL DEFAULT '0',
  `PROJECT_TYPE` varchar(30) DEFAULT NULL,
  `STAGE_ID` int NOT NULL DEFAULT '1',
  PRIMARY KEY (`HISTORY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LEAD_CONTACTS
-- Multiple contact persons per lead
-- IS_PRIMARY: 1 = main contact
-- CONTACT_TYPE: e.g., EMAIL, PHONE, LINKEDIN
-- --------------------------------------------------------
CREATE TABLE `LEAD_CONTACTS` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `LEAD_ID` bigint NOT NULL COMMENT 'FK -> LEADS.ID',
  `CONTACT_TYPE` varchar(30) DEFAULT NULL COMMENT 'e.g. EMAIL, PHONE, LINKEDIN',
  `CONTACT_VALUE` varchar(255) NOT NULL,
  `IS_PRIMARY` tinyint(1) DEFAULT '0',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CONTACT_NAME` varchar(100) DEFAULT NULL,
  `DESIGNATION` varchar(100) DEFAULT NULL,
  `LOCATION` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- ACTIVITIES
-- CRM activity log against leads or contracts
-- ENTITY_TYPE: 'LEAD' | 'CONTRACT' (polymorphic)
-- ENTITY_ID: refers to LEADS.ID or CONTRACT.CONTRACT_ID
-- NEXT_FOLLOWUP_DATE: when to follow up next
-- FK: ACTIVITY_TYPE_ID -> ACTIVITY_TYPE_MASTER.ID
-- FK: CREATED_BY -> users.ID
-- --------------------------------------------------------
CREATE TABLE `ACTIVITIES` (
  `ID` bigint NOT NULL AUTO_INCREMENT,
  `ENTITY_TYPE` varchar(30) NOT NULL COMMENT 'LEAD or CONTRACT',
  `ENTITY_ID` bigint NOT NULL COMMENT 'FK -> LEADS.ID or CONTRACT.CONTRACT_ID',
  `ACTIVITY_TYPE_ID` bigint NOT NULL COMMENT 'FK -> ACTIVITY_TYPE_MASTER.ID',
  `ACTIVITY_NOTE` text,
  `ACTIVITY_DATE` date NOT NULL,
  `NEXT_FOLLOWUP_DATE` date DEFAULT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL COMMENT 'FK -> users.ID',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- COMMENTS
-- Threaded comments on leads
-- COMMENT_TYPE: ORIGINAL or REPLY
-- REPLY_ID: if REPLY, points to parent COMMENT_ID
-- IS_ATTACHEMENT: 1 = has file attachment
-- --------------------------------------------------------
CREATE TABLE `COMMENTS` (
  `COMMENT_ID` bigint NOT NULL AUTO_INCREMENT,
  `LEAD_REF_ID` bigint DEFAULT NULL COMMENT 'FK -> LEADS.ID',
  `USER_REF_ID` bigint DEFAULT NULL COMMENT 'FK -> users.ID',
  `COMMENT_TYPE` enum('ORIGINAL','REPLY') NOT NULL DEFAULT 'ORIGINAL',
  `REPLY_ID` bigint DEFAULT NULL COMMENT 'FK -> COMMENTS.COMMENT_ID (parent)',
  `TEXT` varchar(250) DEFAULT NULL,
  `ATTACHMENT_URL` varchar(250) DEFAULT NULL,
  `IS_ATTACHEMENT` tinyint(1) NOT NULL DEFAULT '0',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `UPDATED_AT` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `CREATED_AT` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`COMMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- COMMENTS_ENGAGEMENT
-- Reactions/likes on CRM comments
-- ENGAGEMENT_TYPE: LIKE or REACTION
-- EMOJI: emoji character for reaction type
-- FK: USER_REF_ID -> users.ID
-- FK: COMMENT_REF_ID -> COMMENTS.COMMENT_ID
-- --------------------------------------------------------
CREATE TABLE `COMMENTS_ENGAGEMENT` (
  `COMMENTS_ENGAGEMENT_ID` bigint NOT NULL AUTO_INCREMENT,
  `USER_REF_ID` bigint DEFAULT NULL COMMENT 'FK -> users.ID',
  `COMMENT_REF_ID` bigint DEFAULT NULL COMMENT 'FK -> COMMENTS.COMMENT_ID',
  `ENGAGEMENT_TYPE` enum('LIKE','REACTION') NOT NULL DEFAULT 'LIKE',
  `EMOJI` varchar(10) DEFAULT NULL,
  `CREATED_AT` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`COMMENTS_ENGAGEMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- LEAD_COMMENT_READ_STATUS
-- Tracks per-user read position in lead comment threads
-- Unique per (LEAD_ID, USER_ID)
-- LAST_SEEN_COMMENT_ID: last comment the user has read
-- --------------------------------------------------------
CREATE TABLE `LEAD_COMMENT_READ_STATUS` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `LEAD_ID` int NOT NULL COMMENT 'FK -> LEADS.ID',
  `USER_ID` int NOT NULL COMMENT 'FK -> users.ID',
  `LAST_SEEN_COMMENT_ID` int DEFAULT NULL COMMENT 'FK -> COMMENTS.COMMENT_ID',
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `unique_user_lead` (`LEAD_ID`,`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- AUDIT_LOGS  (CRM audit trail)
-- Tracks every CREATE/UPDATE/DELETE on CRM records
-- OLD_DATA / NEW_DATA: full JSON snapshots of changed record
-- --------------------------------------------------------
CREATE TABLE `AUDIT_LOGS` (
  `AUDIT_LOGS_ID` bigint NOT NULL AUTO_INCREMENT,
  `TABLE_NAME` varchar(100) NOT NULL COMMENT 'Which CRM table was changed',
  `RECORD_ID` bigint NOT NULL COMMENT 'PK of the changed record',
  `ACTION` varchar(50) DEFAULT NULL COMMENT 'CREATE | UPDATE | DELETE',
  `OLD_DATA` json DEFAULT NULL,
  `NEW_DATA` json DEFAULT NULL,
  `ACTION_BY` varchar(50) NOT NULL COMMENT 'User who made the change',
  `ACTION_AT` datetime DEFAULT NULL,
  PRIMARY KEY (`AUDIT_LOGS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- CONTRACT TABLES
-- ============================================================

-- --------------------------------------------------------
-- CONTRACT
-- A client (converted lead) has one or more contracts
-- COMPANY_NAME: denormalized for quick display
-- CONTACT_INFO: JSON { name, email, phone, designation }
-- ADDRESSES: JSON array of address objects
-- ENGAGEMENT_TYPE_IDS: JSON array of ENGAGEMENT_TYPE_MASTER IDs
-- ENTITY_ID -> ENTITY_TYPE_MASTER.ENTITY_TYPE_ID
-- FK: LEAD_ID -> LEADS.ID
-- FK: CONTRACT_STATUS_ID -> CONTRACT_STATUS_MASTER.ID
-- FK: INDUSTRY_ID -> INDUSTRY_MASTER.ID
-- FK: COMPANY_SIZE_ID -> COMPANY_SIZE_MASTER.ID
-- FK: CREATED_BY, UPDATED_BY -> users.ID
-- --------------------------------------------------------
CREATE TABLE `CONTRACT` (
  `CONTRACT_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `CONTRACT_CODE` varchar(50) NOT NULL COMMENT 'Unique human-readable code e.g. CNT-2025-001',
  `LEAD_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> LEADS.ID (converted lead)',
  `COMPANY_NAME` varchar(255) NOT NULL,
  `CONTACT_INFO` json DEFAULT NULL COMMENT '{ name, email, phone, designation }',
  `ADDRESSES` json DEFAULT NULL COMMENT 'Array of address objects',
  `DESIGNATION` varchar(150) DEFAULT NULL,
  `CONTRACT_STATUS_ID` int UNSIGNED NOT NULL COMMENT 'FK -> CONTRACT_STATUS_MASTER.ID',
  `INDUSTRY_ID` int UNSIGNED DEFAULT NULL COMMENT 'FK -> INDUSTRY_MASTER.ID',
  `COMPANY_SIZE_ID` int UNSIGNED NOT NULL COMMENT 'FK -> COMPANY_SIZE_MASTER.ID',
  `ENGAGEMENT_TYPE_IDS` json NOT NULL COMMENT 'JSON array of ENGAGEMENT_TYPE_MASTER IDs',
  `PROJECT_TYPE` varchar(255) DEFAULT NULL,
  `STARTED_DATE` date DEFAULT NULL,
  `ENTITY_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> ENTITY_TYPE_MASTER.ENTITY_TYPE_ID',
  `LAST_COMMENT_DATE` datetime DEFAULT NULL,
  `COMMENT_COUNT` int UNSIGNED DEFAULT '0',
  `CREATED_BY` bigint UNSIGNED NOT NULL COMMENT 'FK -> users.ID',
  `UPDATED_BY` bigint UNSIGNED DEFAULT NULL COMMENT 'FK -> users.ID',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `DELETED` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`CONTRACT_ID`),
  UNIQUE KEY `CONTRACT_CODE` (`CONTRACT_CODE`),
  KEY `IDX_LEAD_ID` (`LEAD_ID`),
  KEY `IDX_CONTRACT_STATUS` (`CONTRACT_STATUS_ID`),
  KEY `IDX_ENTITY_ID` (`ENTITY_ID`),
  KEY `IDX_COMPANY_SIZE_ID` (`COMPANY_SIZE_ID`),
  KEY `IDX_INDUSTRY_ID` (`INDUSTRY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- CONTRACT_DOCUMENT
-- Individual engagement documents within a contract
-- (e.g., MSA, SOW, Addendum per service)
-- AUTO_RENEWAL: 1 = no END_DATE, auto-renews
-- DOCUMENTS: JSON array of uploaded file URLs
-- ESCALATION: annual fee escalation %
-- IS_TERMINATED_REQUESTED: 1 = termination pending CRM approval
-- STATUS_LAST_EVALUATED_AT: updated by cron (renewal status check)
-- FK: CONTRACT_ID -> CONTRACT.CONTRACT_ID
-- FK: DOCUMENT_TYPE_ID -> DOCUMENT_TYPE_MASTER.DOCUMENT_TYPE_ID
-- FK: BILLING_TYPE_ID -> BILLING_TYPE_MASTER.BILLING_TYPE_ID
-- FK: RENEWAL_STATUS_ID -> RENEWAL_STATUS_MASTER.RENEWAL_STATUS_ID
-- FK: CREATED_BY, UPDATED_BY, TERMINATED_BY -> users.ID
-- --------------------------------------------------------
CREATE TABLE `CONTRACT_DOCUMENT` (
  `DOCUMENT_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `CONTRACT_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> CONTRACT.CONTRACT_ID',
  `DOCUMENT_TYPE_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> DOCUMENT_TYPE_MASTER.DOCUMENT_TYPE_ID',
  `TITLE` varchar(255) NOT NULL,
  `ENGAGEMENT_TYPE_IDS` json NOT NULL COMMENT 'JSON array of ENGAGEMENT_TYPE_MASTER IDs for this doc',
  `ENTITY_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> ENTITY_TYPE_MASTER.ENTITY_TYPE_ID',
  `ESCALATION` decimal(5,2) DEFAULT NULL COMMENT 'Annual escalation %',
  `FEE` decimal(15,2) DEFAULT NULL,
  `BILLING_TYPE_ID` bigint UNSIGNED NOT NULL COMMENT 'FK -> BILLING_TYPE_MASTER.BILLING_TYPE_ID',
  `SIGNATORY` varchar(255) DEFAULT NULL,
  `AUTO_RENEWAL` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = auto-renews, no END_DATE',
  `START_DATE` date NOT NULL,
  `END_DATE` date DEFAULT NULL COMMENT 'NULL if AUTO_RENEWAL = 1',
  `DOCUMENTS` json DEFAULT NULL COMMENT 'JSON array of file storage URLs',
  `RENEWAL_STATUS_ID` int UNSIGNED NOT NULL COMMENT 'FK -> RENEWAL_STATUS_MASTER.RENEWAL_STATUS_ID',
  `IS_TERMINATED_REQUESTED` tinyint(1) NOT NULL DEFAULT '0',
  `TERMINATED_AT` datetime DEFAULT NULL,
  `TERMINATED_BY` bigint UNSIGNED DEFAULT NULL COMMENT 'FK -> users.ID',
  `TERMINATION_REASON` text,
  `STATUS_LAST_EVALUATED_AT` datetime DEFAULT NULL COMMENT 'Set by renewal cron job',
  `CREATED_BY` bigint UNSIGNED NOT NULL COMMENT 'FK -> users.ID',
  `UPDATED_BY` bigint UNSIGNED DEFAULT NULL COMMENT 'FK -> users.ID',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `TERMINATION_REQUESTED_BY` bigint DEFAULT NULL COMMENT 'FK -> users.ID',
  `TERMINATION_REQUESTED_AT` datetime DEFAULT NULL,
  `TERMINATION_REQUEST_REASON` text,
  PRIMARY KEY (`DOCUMENT_ID`),
  KEY `IDX_CONTRACT_ID` (`CONTRACT_ID`),
  KEY `IDX_DOCUMENT_TYPE_ID` (`DOCUMENT_TYPE_ID`),
  KEY `IDX_RENEWAL_STATUS_ID` (`RENEWAL_STATUS_ID`),
  KEY `IDX_START_DATE` (`START_DATE`),
  KEY `IDX_END_DATE` (`END_DATE`),
  KEY `IDX_CONTRACT_DOC_TYPE` (`CONTRACT_ID`,`DOCUMENT_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ============================================================
-- CRM MODULE - TABLE RELATIONSHIPS
--
-- LEADS (1) -> (*) LEAD_CONTACTS
-- LEADS (1) -> (*) LEADS_HISTORY
-- LEADS (1) -> (*) ACTIVITIES
-- LEADS (1) -> (*) COMMENTS -> (*) COMMENTS_ENGAGEMENT
-- LEADS (1) -> (*) LEAD_COMMENT_READ_STATUS
-- LEADS (1) ->  (1) CONTRACT  (when CONVERTED=1)
-- CONTRACT (1) -> (*) CONTRACT_DOCUMENT
-- CONTRACT (1) -> (*) ACTIVITIES
--
-- CRON JOBS:
--   autoSaveCurrency.js -> updates CURRENCY_MASTER.CURRENCY_RATE
--   autoSendReminderForContract.js -> checks CONTRACT_DOCUMENT renewal
--                                     updates RENEWAL_STATUS_ID
--                                     updates STATUS_LAST_EVALUATED_AT
--
-- CRM -> TTM INTEGRATION:
--   LEADS.ID -> TTM_TASK.CLIENT_ID  (CRM client is TTM task target)
--   CONTRACT.CONTRACT_ID -> TTM_TASK.CONTRACT_ID
--   TTM_TASK_TEMPLATE.SERVICE_NAME maps to ENGAGEMENT_TYPE_MASTER
-- ============================================================
