-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Feb 28, 2026 at 01:18 PM
-- Server version: 8.0.42
-- PHP Version: 8.4.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `Oremus`
--

-- --------------------------------------------------------

--
-- Table structure for table `ACTIVITIES`
--

CREATE TABLE `ACTIVITIES` (
  `ID` bigint NOT NULL,
  `ENTITY_TYPE` varchar(30) NOT NULL,
  `ENTITY_ID` bigint NOT NULL,
  `ACTIVITY_TYPE_ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ACTIVITY_TYPE_MASTER`
--

CREATE TABLE `ACTIVITY_TYPE_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `AUDIT_LOGS`
--

CREATE TABLE `AUDIT_LOGS` (
  `AUDIT_LOGS_ID` bigint NOT NULL,
  `TABLE_NAME` varchar(100) NOT NULL,
  `RECORD_ID` bigint NOT NULL,
  `ACTION` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `OLD_DATA` json DEFAULT NULL,
  `NEW_DATA` json DEFAULT NULL,
  `ACTION_BY` varchar(50) NOT NULL,
  `ACTION_AT` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `billing_types`
--

CREATE TABLE `billing_types` (
  `ID` int NOT NULL,
  `NAME` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `BILLING_TYPE_MASTER`
--

CREATE TABLE `BILLING_TYPE_MASTER` (
  `BILLING_TYPE_ID` bigint UNSIGNED NOT NULL,
  `BILLING_TYPE_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,
  `UPDATED_AT` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `ID` int NOT NULL,
  `CLIENT_NAME` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `CONTACT_PERSON` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `EMAIL` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `PASSWORD` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `ROLE` varchar(10) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'client',
  `CLIENT_COUNTRY` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ADDRESS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DELETED` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `CLIENT_TYPE_MASTER`
--

CREATE TABLE `CLIENT_TYPE_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `COMMENTS`
--

CREATE TABLE `COMMENTS` (
  `COMMENT_ID` bigint NOT NULL,
  `LEAD_REF_ID` bigint DEFAULT NULL,
  `USER_REF_ID` bigint DEFAULT NULL,
  `COMMENT_TYPE` enum('ORIGINAL','REPLY') NOT NULL DEFAULT 'ORIGINAL',
  `REPLY_ID` bigint DEFAULT NULL,
  `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)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `COMMENTS_ENGAGEMENT`
--

CREATE TABLE `COMMENTS_ENGAGEMENT` (
  `COMMENTS_ENGAGEMENT_ID` bigint NOT NULL,
  `USER_REF_ID` bigint DEFAULT NULL,
  `COMMENT_REF_ID` bigint DEFAULT NULL,
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `COMPANY_SIZE_MASTER`
--

CREATE TABLE `COMPANY_SIZE_MASTER` (
  `ID` bigint NOT NULL,
  `SIZE_LABEL` varchar(50) NOT NULL,
  `SIZE_CODE` varchar(20) NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `CONTRACT`
--

CREATE TABLE `CONTRACT` (
  `CONTRACT_ID` bigint UNSIGNED NOT NULL,
  `CONTRACT_CODE` varchar(50) NOT NULL,
  `LEAD_ID` bigint UNSIGNED NOT NULL,
  `COMPANY_NAME` varchar(255) NOT NULL,
  `CONTACT_INFO` json DEFAULT NULL,
  `ADDRESSES` json DEFAULT NULL,
  `DESIGNATION` varchar(150) DEFAULT NULL,
  `CONTRACT_STATUS_ID` int UNSIGNED NOT NULL,
  `INDUSTRY_ID` int UNSIGNED DEFAULT NULL,
  `COMPANY_SIZE_ID` int UNSIGNED NOT NULL,
  `ENGAGEMENT_TYPE_IDS` json NOT NULL COMMENT 'Stores multiple engagement type IDs',
  `PROJECT_TYPE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `STARTED_DATE` date DEFAULT NULL,
  `ENTITY_ID` bigint UNSIGNED NOT NULL,
  `LAST_COMMENT_DATE` datetime DEFAULT NULL,
  `COMMENT_COUNT` int UNSIGNED DEFAULT '0',
  `CREATED_BY` bigint UNSIGNED NOT NULL,
  `UPDATED_BY` bigint UNSIGNED DEFAULT NULL,
  `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' COMMENT '1 = Deleted, 0 = Not Deleted'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `CONTRACT_DOCUMENT`
--

CREATE TABLE `CONTRACT_DOCUMENT` (
  `DOCUMENT_ID` bigint UNSIGNED NOT NULL,
  `CONTRACT_ID` bigint UNSIGNED NOT NULL,
  `DOCUMENT_TYPE_ID` bigint UNSIGNED NOT NULL,
  `TITLE` varchar(255) NOT NULL,
  `ENGAGEMENT_TYPE_IDS` json NOT NULL COMMENT 'Stores multiple engagement type IDs',
  `ENTITY_ID` bigint UNSIGNED NOT NULL,
  `ESCALATION` decimal(5,2) DEFAULT NULL COMMENT 'Escalation percentage',
  `FEE` decimal(15,2) DEFAULT NULL,
  `BILLING_TYPE_ID` bigint UNSIGNED NOT NULL,
  `SIGNATORY` varchar(255) DEFAULT NULL,
  `AUTO_RENEWAL` tinyint(1) NOT NULL DEFAULT '0',
  `START_DATE` date NOT NULL,
  `END_DATE` date DEFAULT NULL COMMENT 'NULL if AUTO_RENEWAL = 1',
  `DOCUMENTS` json DEFAULT NULL,
  `RENEWAL_STATUS_ID` int UNSIGNED NOT NULL,
  `IS_TERMINATED_REQUESTED` tinyint(1) NOT NULL DEFAULT '0',
  `TERMINATED_AT` datetime DEFAULT NULL,
  `TERMINATED_BY` bigint UNSIGNED DEFAULT NULL,
  `TERMINATION_REASON` text,
  `STATUS_LAST_EVALUATED_AT` datetime DEFAULT NULL COMMENT 'Updated by cron job',
  `CREATED_BY` bigint UNSIGNED NOT NULL,
  `UPDATED_BY` bigint UNSIGNED DEFAULT NULL,
  `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,
  `TERMINATION_REQUESTED_AT` datetime DEFAULT NULL,
  `TERMINATION_REQUEST_REASON` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `CONTRACT_STATUS_MASTER`
--

CREATE TABLE `CONTRACT_STATUS_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `CURRENCY_MASTER`
--

CREATE TABLE `CURRENCY_MASTER` (
  `CURRENCY_ID` int NOT NULL,
  `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,
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `DEAL_STAGE_MASTER`
--

CREATE TABLE `DEAL_STAGE_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `ID` int NOT NULL,
  `DEPARTMENT_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DEPARTMENT_CODE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DEPARTMENT_DESRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `IS_ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DELETED` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `DOCUMENT_TYPE_MASTER`
--

CREATE TABLE `DOCUMENT_TYPE_MASTER` (
  `DOCUMENT_TYPE_ID` bigint UNSIGNED NOT NULL,
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ENGAGEMENT_TYPE_MASTER`
--

CREATE TABLE `ENGAGEMENT_TYPE_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ENTITY_TYPE_MASTER`
--

CREATE TABLE `ENTITY_TYPE_MASTER` (
  `ENTITY_TYPE_ID` int UNSIGNED NOT NULL,
  `ENTITY_CODE` varchar(20) NOT NULL,
  `ENTITY_NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `IS_ACTIVE` tinyint(1) DEFAULT '1' COMMENT '1 = Active, 0 = Inactive',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0' COMMENT '1 = Deleted, 0 = Not Deleted'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `evaluation_periods`
--

CREATE TABLE `evaluation_periods` (
  `PERIOD_ID` int NOT NULL,
  `USER_ID` int DEFAULT NULL,
  `DEPARTMENT_ID` int DEFAULT NULL,
  `DESIGNATION` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `DEPARTMENT_NAME` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `USER_ROLE` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `CYCLE` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ACTIVE` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `feedback`
--

CREATE TABLE `feedback` (
  `USER_ID` int DEFAULT NULL,
  `QUESTION1` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RESPONSE1` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `QUESTION2` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RESPONSE2` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `LEADER_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `RM_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `FL_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `CEO_RESPONSE` text COLLATE utf8mb4_general_ci NOT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL,
  `ID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `goals`
--

CREATE TABLE `goals` (
  `ID` int NOT NULL,
  `GOAL_SHEET_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `OBJECTIVE_ID` int NOT NULL,
  `GOAL_TITLE` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DETAILS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `ACTION_POINTS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `MEASURABLE` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `TIMELINE` date DEFAULT NULL,
  `PROGRESS_NOTES` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `STATUS` text,
  `SUBMITTER_REMARK` text,
  `YEAR_END_APPRAISAL_STATUS` text,
  `MID_YEAR_APPRAISER_REMARK` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `MID_YEAR_REVIEWER_REMARK` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `YEAR_END_APPRAISER_REMARK` text,
  `YEAR_END_REVIEWER_REMARK` text,
  `NEW_TIMELINE` date DEFAULT NULL,
  `SELF_APPRAISAL` decimal(5,2) DEFAULT NULL,
  `APPRAISER_APPRAISAL` decimal(10,0) DEFAULT NULL,
  `REVIEWER_APPRAISAL` decimal(5,2) DEFAULT NULL,
  `DELETED` tinyint(1) DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `goal_sheets`
--

CREATE TABLE `goal_sheets` (
  `ID` int NOT NULL,
  `SUBMITTER_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `SUBMITTER_ROLE` enum('REPORTING_MANAGER','FUNCTIONAL_LEAD') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `SHEET_NAME` varchar(255) NOT NULL,
  `REVIEW_OPEN` tinyint(1) DEFAULT '0',
  `STATUS` enum('DRAFT','SUBMITTED','FUNC_APPROVED','CEO_APPROVED','FUNC_REJECTED','CEO_REJECTED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'DRAFT',
  `FUNCTIONAL_LEAD_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `FUNC_STATUS` enum('PENDING','APPROVED','REJECTED','APPRAISED','REVIEWED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PENDING',
  `FUNC_APPROVED_AT` datetime DEFAULT NULL,
  `FUNC_APPROVED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CEO_USER_ID` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CEO_STATUS` enum('PENDING','APPROVED','REJECTED','REVIEWED') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'PENDING',
  `CEO_APPROVED_AT` datetime DEFAULT NULL,
  `CEO_APPROVED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `REJECT_REASON` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `REJECTED_BY` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `REJECTED_AT` datetime DEFAULT NULL,
  `DELETED` tinyint(1) DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `INDUSTRY_MASTER`
--

CREATE TABLE `INDUSTRY_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kpis`
--

CREATE TABLE `kpis` (
  `KPI_ID` int NOT NULL,
  `KRA_ID` int DEFAULT NULL,
  `KPI_NUMBER` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `KPI_PERCENTAGE` decimal(5,2) DEFAULT NULL,
  `KPI_DESCRIPTION` text COLLATE utf8mb4_general_ci,
  `BENCHMARK` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `WEIGHTAGE` decimal(6,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `kras`
--

CREATE TABLE `kras` (
  `KRA_ID` int NOT NULL,
  `OBJECTIVE_PERFORMANCE_ID` int DEFAULT NULL,
  `KRA_NUMBER` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `KRA_WEIGHTAGE` decimal(5,2) DEFAULT NULL,
  `KRA_DESCRIPTION` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEADS`
--

CREATE TABLE `LEADS` (
  `ID` bigint NOT NULL,
  `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,
  `STATUS_ID` bigint NOT NULL,
  `ASSIGNED_TO` bigint DEFAULT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CREATED_BY` bigint NOT NULL,
  `UPDATED_AT` datetime DEFAULT NULL,
  `UPDATED_BY` bigint DEFAULT NULL,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CLIENT_TYPE` varchar(20) NOT NULL,
  `REGION` varchar(10) NOT NULL,
  `INDUSTRY_ID` bigint NOT NULL,
  `COMPANY_SIZE_ID` bigint NOT NULL,
  `ENGAGEMENT_TYPE` json NOT NULL,
  `ENTITY` enum('OCS','OCNA') NOT NULL DEFAULT 'OCS',
  `RESTART_DATE` date DEFAULT NULL,
  `CONVERSION_DATE` date DEFAULT NULL,
  `ESTIMATED_DEAL_VALUE` varchar(20) NOT NULL DEFAULT '0',
  `CURRENCY_ID` int NOT NULL DEFAULT '138',
  `PROJECT_TYPE` enum('MONTHLY_RETAINER','ONE_TIME_PROJECT','ANNUAL_RETAINER') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'ONE_TIME_PROJECT',
  `STAGE_ID` int DEFAULT '1',
  `TO_DO_ID` int DEFAULT NULL,
  `DUE_DATE` date DEFAULT NULL,
  `CONVERTED` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEADS_HISTORY`
--

CREATE TABLE `LEADS_HISTORY` (
  `HISTORY_ID` bigint NOT NULL,
  `ORIGINAL_ID` bigint NOT NULL,
  `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,
  `CHANGED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `CHANGED_BY` bigint NOT NULL,
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEAD_COMMENT_READ_STATUS`
--

CREATE TABLE `LEAD_COMMENT_READ_STATUS` (
  `ID` int NOT NULL,
  `LEAD_ID` int NOT NULL,
  `USER_ID` int NOT NULL,
  `LAST_SEEN_COMMENT_ID` int DEFAULT NULL,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEAD_CONTACTS`
--

CREATE TABLE `LEAD_CONTACTS` (
  `ID` bigint NOT NULL,
  `LEAD_ID` bigint NOT NULL,
  `CONTACT_TYPE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CONTACT_NAME` varchar(100) DEFAULT NULL,
  `DESIGNATION` varchar(100) DEFAULT NULL,
  `LOCATION` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEAD_SOURCE_MASTER`
--

CREATE TABLE `LEAD_SOURCE_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LEAD_STATUS_MASTER`
--

CREATE TABLE `LEAD_STATUS_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `LOSS_REASON_MASTER`
--

CREATE TABLE `LOSS_REASON_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `objectives`
--

CREATE TABLE `objectives` (
  `ID` int NOT NULL,
  `OBJECTIVE` text NOT NULL,
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DELETED` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `objectives_performance`
--

CREATE TABLE `objectives_performance` (
  `OBJECTIVE_PERFORMANCE_ID` int NOT NULL,
  `USER_ID` int DEFAULT NULL,
  `BSC_REF` int DEFAULT NULL,
  `OBJECTIVE_PERFORMANCE_NAME` text COLLATE utf8mb4_general_ci,
  `OBJECTIVE_PERFORMANCE_WEIGHTAGE` decimal(5,2) DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `organizations`
--

CREATE TABLE `organizations` (
  `ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `NAME` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `EMAIL` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `performance_summary`
--

CREATE TABLE `performance_summary` (
  `SUMMARY_ID` int NOT NULL,
  `TOTAL_OBJECTIVES` int DEFAULT NULL,
  `TOTAL_KRAS` int DEFAULT NULL,
  `TOTAL_KPIS` int DEFAULT NULL,
  `TOTAL_WEIGHTAGE` decimal(6,2) DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ratings`
--

CREATE TABLE `ratings` (
  `RATING_ID` int NOT NULL,
  `USER_ID` int DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL,
  `KPI_REF_ID` int DEFAULT NULL,
  `SELF_RATING` int DEFAULT '0',
  `SELF_SCORING` float DEFAULT '0',
  `SELF_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `RM_RATING` int DEFAULT '0',
  `RM_SCORING` float DEFAULT '0',
  `RM_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `RM_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `FL_RATING` int DEFAULT '0',
  `FL_SCORING` float DEFAULT '0',
  `FL_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `FL_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `CEO_RATING` int DEFAULT '0',
  `CEO_SCORING` float DEFAULT '0',
  `CEO_COMMENTS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '',
  `CEO_REMARKS` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rating_descriptor`
--

CREATE TABLE `rating_descriptor` (
  `RATING_DESCRIPTOR_ID` int NOT NULL,
  `RATING_REF_ID` int NOT NULL,
  `RATING_KEY` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `RATING_VALUE` text COLLATE utf8mb4_general_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rating_period`
--

CREATE TABLE `rating_period` (
  `ID` int NOT NULL,
  `STARTED_DATE` date NOT NULL,
  `END_DATE` date NOT NULL,
  `RATING` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rating_ratio`
--

CREATE TABLE `rating_ratio` (
  `RATING_RATIO_ID` int NOT NULL,
  `USER_ID` int NOT NULL,
  `PERIOD_ID` int NOT NULL,
  `SELF_RATING` float NOT NULL,
  `RM_RATING` float NOT NULL,
  `FL_RATING` float NOT NULL,
  `CEO_RATING` float NOT NULL,
  `UPDATED_BY` enum('HR','CEO') COLLATE utf8mb4_general_ci DEFAULT 'CEO',
  `RATIONALISE_RATING` float NOT NULL DEFAULT '0',
  `STATUS` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `rationalise_rating`
--

CREATE TABLE `rationalise_rating` (
  `RATIONALISE_RATING_ID` int NOT NULL,
  `AVG_RATIONALISE_RATING` int DEFAULT NULL,
  `RATIONALISE_SCORE` int NOT NULL,
  `USER_ID` int DEFAULT NULL,
  `PERIOD_REF_ID` int DEFAULT NULL,
  `CEO_APPROVED` tinyint(1) DEFAULT '0',
  `HR_APPROVED` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `REGION_MASTER`
--

CREATE TABLE `REGION_MASTER` (
  `ID` bigint NOT NULL,
  `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,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `RENEWAL_STATUS_MASTER`
--

CREATE TABLE `RENEWAL_STATUS_MASTER` (
  `RENEWAL_STATUS_ID` int UNSIGNED NOT NULL,
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sheet_names`
--

CREATE TABLE `sheet_names` (
  `SHEET_NAME` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `status`
--

CREATE TABLE `status` (
  `STATUS_ID` int NOT NULL,
  `USER_ID` int DEFAULT NULL,
  `DATA_COMPLETE` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `READY_FOR_REVIEW` tinyint(1) DEFAULT NULL,
  `USER_ROLE` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'employee',
  `SUBMIT_FOR_REVIEW` tinyint(1) NOT NULL DEFAULT '0',
  `SUBMIT_FOR_REVIEW_DATE` datetime DEFAULT NULL,
  `REPORTING_MANAGER_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `RM_AUTO_APPROVED` tinyint(1) NOT NULL DEFAULT '0',
  `REPORTING_MANAGER_REVIEW_DATE` datetime DEFAULT NULL,
  `REPORTING_MANAGER_ID` int DEFAULT NULL,
  `FUNCTIONAL_HEAD_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `FL_AUTO_APPROVED` tinyint(1) NOT NULL DEFAULT '0',
  `FUNCTIONAL_HEAD_REVIEW_DATE` datetime DEFAULT NULL,
  `FUNCTIONAL_HEAD_ID` int DEFAULT NULL,
  `CEO_REVIEWED` tinyint(1) NOT NULL DEFAULT '0',
  `CEO_REVIEW_DATE` datetime DEFAULT NULL,
  `CEO_ID` int DEFAULT NULL,
  `CURRENT_STEP` varchar(50) COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'draft',
  `WORKFLOW_STATUS` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Draft',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `PERIOD_REF_ID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `ID` int NOT NULL,
  `BILLING_TYPE` int NOT NULL,
  `TITILE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DESCRIPTION` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `STATUS` enum('pending','in-progress','completed') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'pending',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DELETED` int DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `timesheets`
--

CREATE TABLE `timesheets` (
  `ID` int NOT NULL,
  `USER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `TASK_REF_ID` int NOT NULL,
  `DEPARTMENT_REF_ID` int NOT NULL,
  `CLIENT_REF_ID` int NOT NULL,
  `HOURS` double NOT NULL,
  `DATE` date NOT NULL,
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `CREATED_BY` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `FLAG` int NOT NULL DEFAULT '0',
  `REPLACED_BY_ID` int DEFAULT NULL,
  `DELETED` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `timesheet_submissions`
--

CREATE TABLE `timesheet_submissions` (
  `ID` int NOT NULL,
  `USER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `WEEK_START_DATE` date NOT NULL,
  `WEEK_END_DATE` date NOT NULL,
  `STATUS` enum('pending','approved','rejected','') DEFAULT 'pending',
  `COMMENTS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `REVIEWER_ID` char(36) NOT NULL,
  `SUBMITTED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `AUTO_APPROVED` tinyint NOT NULL DEFAULT '0',
  `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TO_DO_MASTER`
--

CREATE TABLE `TO_DO_MASTER` (
  `ID` int NOT NULL,
  `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'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_AUDIT_LOG`
--

CREATE TABLE `TTM_AUDIT_LOG` (
  `ID` bigint NOT NULL,
  `TS_MS` bigint NOT NULL,
  `ACTOR_EMP_REF_ID` bigint DEFAULT NULL,
  `ACTION` varchar(100) NOT NULL,
  `ENTITY_TYPE` varchar(50) NOT NULL,
  `BEFORE_JSON` json DEFAULT NULL,
  `AFTER_JSON` json DEFAULT NULL,
  `REASON` text,
  `IP_ADDRESS` varchar(64) DEFAULT NULL,
  `USER_AGENT` varchar(255) DEFAULT NULL,
  `RETENTION_UNTIL` date NOT NULL,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_DELAY_REASON`
--

CREATE TABLE `TTM_DELAY_REASON` (
  `ID` bigint NOT NULL,
  `TASK_FUNCTION_ID` bigint NOT NULL,
  `CATEGORY` varchar(100) NOT NULL,
  `DETAILS` text NOT NULL,
  `ATTACHMENT_URL` varchar(500) DEFAULT NULL,
  `CREATED_BY_EMP_REF_ID` bigint NOT NULL,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_FUNCTION_REVIEWER`
--

CREATE TABLE `TTM_FUNCTION_REVIEWER` (
  `ID` bigint NOT NULL,
  `TASK_FUNCTION_ID` bigint NOT NULL,
  `REVIEW_LEVEL` varchar(50) NOT NULL,
  `REVIEWER_EMP_REF_ID` bigint NOT NULL,
  `IS_REQUIRED` tinyint DEFAULT '1',
  `REJECTED` tinyint(1) NOT NULL DEFAULT '0',
  `REVIEW_STATUS_ID` int DEFAULT NULL,
  `COMMENT` text,
  `ACTION_AT` datetime DEFAULT NULL,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `EXTENDED_COMPLETED_DATE` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_PRIORITY_MASTER`
--

CREATE TABLE `TTM_PRIORITY_MASTER` (
  `ID` int NOT NULL,
  `PRIORITY_NAME` varchar(50) NOT NULL,
  `PRIORITY_CODE` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `SORT_ORDER` int DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_RECURRENCE_RULE_MASTER`
--

CREATE TABLE `TTM_RECURRENCE_RULE_MASTER` (
  `ID` int NOT NULL,
  `RULE_NAME` varchar(50) NOT NULL,
  `RULE_CODE` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `INTERVAL_MONTHS` int DEFAULT NULL,
  `SORT_ORDER` int DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_REVIEW_STATUS`
--

CREATE TABLE `TTM_REVIEW_STATUS` (
  `ID` int NOT NULL,
  `STATUS_CODE` varchar(50) NOT NULL,
  `STATUS_NAME` varchar(100) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `DISPLAY_ORDER` int DEFAULT '0',
  `ACTIVE` tinyint(1) DEFAULT '1',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TASK`
--

CREATE TABLE `TTM_TASK` (
  `ID` bigint NOT NULL,
  `TASK_CODE` varchar(60) NOT NULL,
  `TEMPLATE_ID` bigint NOT NULL,
  `CLIENT_ID` bigint NOT NULL,
  `CONTRACT_ID` bigint DEFAULT NULL,
  `ASSIGNED_FL_EMP_REF_ID` bigint DEFAULT NULL,
  `CLIENT_DEADLINE` date NOT NULL,
  `STATUS_ID` int DEFAULT NULL,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `PRIORITY_ID` int DEFAULT NULL,
  `CREATED_BY_EMP_REF_ID` bigint NOT NULL,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TASK_FUNCTION`
--

CREATE TABLE `TTM_TASK_FUNCTION` (
  `ID` bigint NOT NULL,
  `TASK_ID` bigint NOT NULL,
  `TEMPLATE_FUNCTION_ID` bigint NOT NULL,
  `SEQ_NO` int NOT NULL,
  `DEADLINE` date NOT NULL,
  `STATUS_ID` int DEFAULT NULL,
  `ACTIVE` tinyint(1) NOT NULL DEFAULT '1',
  `INITIATOR_EMP_REF_ID` bigint DEFAULT NULL,
  `STARTED_AT` date DEFAULT NULL,
  `COMPLETED_AT` date DEFAULT NULL,
  `REJECTED` tinyint(1) NOT NULL DEFAULT '0',
  `SUBMITED` tinyint(1) NOT NULL DEFAULT '0',
  `EXTENDED_DAY_COUNT` int NOT NULL DEFAULT '0',
  `EXTENDED_TIMES_COUNT` int NOT NULL DEFAULT '0',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TASK_TEMPLATE`
--

CREATE TABLE `TTM_TASK_TEMPLATE` (
  `ID` bigint NOT NULL,
  `TEMPLATE_CODE` varchar(50) NOT NULL,
  `TEMPLATE_NAME` varchar(50) NOT NULL,
  `SERVICE_NAME` varchar(255) NOT NULL,
  `TASK_TYPE_ID` int DEFAULT NULL,
  `RECURRENCE_RULE_ID` int DEFAULT NULL,
  `ACTIVE` tinyint DEFAULT '1',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CREATED_BY_EMP_REF_ID` bigint NOT NULL,
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TASK_TYPE_MASTER`
--

CREATE TABLE `TTM_TASK_TYPE_MASTER` (
  `ID` int NOT NULL,
  `TYPE_NAME` varchar(50) NOT NULL,
  `TYPE_CODE` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `SORT_ORDER` int DEFAULT '0',
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TEMPLATE_FUNCTION`
--

CREATE TABLE `TTM_TEMPLATE_FUNCTION` (
  `ID` bigint NOT NULL,
  `TEMPLATE_ID` bigint NOT NULL,
  `SEQ_NO` int NOT NULL,
  `FUNCTION_NAME` varchar(255) NOT NULL,
  `DEPT_REF_ID` bigint DEFAULT NULL,
  `INITIATOR_ROLE` varchar(50) DEFAULT 'employee',
  `MIN_REVIEW_COUNT` int DEFAULT '1',
  `REVIEW_LEVELS_JSON` json DEFAULT NULL,
  `TYPICAL_DURATION_DAYS` int DEFAULT '1',
  `ACTIVE` tinyint DEFAULT '1',
  `DELETED` tinyint(1) NOT NULL DEFAULT '0',
  `CREATED_AT` datetime DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `TTM_TEMPLATE_STATUS_MASTER`
--

CREATE TABLE `TTM_TEMPLATE_STATUS_MASTER` (
  `ID` int NOT NULL,
  `STATUS_NAME` varchar(50) NOT NULL,
  `STATUS_CODE` varchar(50) NOT NULL,
  `DESCRIPTION` varchar(255) DEFAULT NULL,
  `CREATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` tinyint(1) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `ID` int NOT NULL,
  `ORGANIZATION_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `USER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DEPARTMENT_REF_ID` int NOT NULL,
  `DEPARTMENTS` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `MANAGER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `FULL_NAME` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `EMPLOYEE_CODE` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `CONTACT_NUMBER` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `EMAIL` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DESIGNATION` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PASSWORD` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `ROLE` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `CRM_ACTIVE` tinyint(1) NOT NULL DEFAULT '0',
  `ACTIVE` int NOT NULL DEFAULT '1',
  `AADHAR_NUMBER` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `PAN_NUMBER` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `AADHAR_FLAG` int NOT NULL DEFAULT '0',
  `OTP` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `OTP_EXPIRY_DATE` datetime DEFAULT NULL,
  `OTP_VERIFIED` int NOT NULL DEFAULT '0',
  `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_AT` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `DELETED` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ACTIVITIES`
--
ALTER TABLE `ACTIVITIES`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `ACTIVITY_TYPE_MASTER`
--
ALTER TABLE `ACTIVITY_TYPE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `AUDIT_LOGS`
--
ALTER TABLE `AUDIT_LOGS`
  ADD PRIMARY KEY (`AUDIT_LOGS_ID`);

--
-- Indexes for table `billing_types`
--
ALTER TABLE `billing_types`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `NAME` (`NAME`);

--
-- Indexes for table `BILLING_TYPE_MASTER`
--
ALTER TABLE `BILLING_TYPE_MASTER`
  ADD PRIMARY KEY (`BILLING_TYPE_ID`),
  ADD UNIQUE KEY `UK_BILLING_TYPE_NAME` (`BILLING_TYPE_NAME`);

--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `CREATED_BY` (`CREATED_BY`);

--
-- Indexes for table `CLIENT_TYPE_MASTER`
--
ALTER TABLE `CLIENT_TYPE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `COMMENTS`
--
ALTER TABLE `COMMENTS`
  ADD PRIMARY KEY (`COMMENT_ID`);

--
-- Indexes for table `COMMENTS_ENGAGEMENT`
--
ALTER TABLE `COMMENTS_ENGAGEMENT`
  ADD PRIMARY KEY (`COMMENTS_ENGAGEMENT_ID`);

--
-- Indexes for table `COMPANY_SIZE_MASTER`
--
ALTER TABLE `COMPANY_SIZE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `CONTRACT`
--
ALTER TABLE `CONTRACT`
  ADD PRIMARY KEY (`CONTRACT_ID`),
  ADD UNIQUE KEY `CONTRACT_CODE` (`CONTRACT_CODE`),
  ADD UNIQUE KEY `UK_CONTRACT_CODE` (`CONTRACT_CODE`),
  ADD KEY `IDX_LEAD_ID` (`LEAD_ID`),
  ADD KEY `IDX_CONTRACT_STATUS` (`CONTRACT_STATUS_ID`),
  ADD KEY `IDX_ENTITY_ID` (`ENTITY_ID`),
  ADD KEY `IDX_COMPANY_SIZE_ID` (`COMPANY_SIZE_ID`),
  ADD KEY `IDX_INDUSTRY_ID` (`INDUSTRY_ID`);

--
-- Indexes for table `CONTRACT_DOCUMENT`
--
ALTER TABLE `CONTRACT_DOCUMENT`
  ADD PRIMARY KEY (`DOCUMENT_ID`),
  ADD KEY `IDX_CONTRACT_ID` (`CONTRACT_ID`),
  ADD KEY `IDX_DOCUMENT_TYPE_ID` (`DOCUMENT_TYPE_ID`),
  ADD KEY `IDX_RENEWAL_STATUS_ID` (`RENEWAL_STATUS_ID`),
  ADD KEY `IDX_START_DATE` (`START_DATE`),
  ADD KEY `IDX_END_DATE` (`END_DATE`),
  ADD KEY `IDX_CONTRACT_DOC_TYPE` (`CONTRACT_ID`,`DOCUMENT_TYPE_ID`);

--
-- Indexes for table `CONTRACT_STATUS_MASTER`
--
ALTER TABLE `CONTRACT_STATUS_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `CURRENCY_MASTER`
--
ALTER TABLE `CURRENCY_MASTER`
  ADD PRIMARY KEY (`CURRENCY_ID`);

--
-- Indexes for table `DEAL_STAGE_MASTER`
--
ALTER TABLE `DEAL_STAGE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `departments`
--
ALTER TABLE `departments`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `fk_created_by` (`CREATED_BY`);

--
-- Indexes for table `DOCUMENT_TYPE_MASTER`
--
ALTER TABLE `DOCUMENT_TYPE_MASTER`
  ADD PRIMARY KEY (`DOCUMENT_TYPE_ID`),
  ADD UNIQUE KEY `UK_DOCUMENT_TYPE_NAME` (`DOCUMENT_TYPE_NAME`);

--
-- Indexes for table `ENGAGEMENT_TYPE_MASTER`
--
ALTER TABLE `ENGAGEMENT_TYPE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `ENTITY_TYPE_MASTER`
--
ALTER TABLE `ENTITY_TYPE_MASTER`
  ADD PRIMARY KEY (`ENTITY_TYPE_ID`),
  ADD UNIQUE KEY `ENTITY_CODE` (`ENTITY_CODE`);

--
-- Indexes for table `evaluation_periods`
--
ALTER TABLE `evaluation_periods`
  ADD PRIMARY KEY (`PERIOD_ID`),
  ADD KEY `USER_ID` (`USER_ID`);

--
-- Indexes for table `feedback`
--
ALTER TABLE `feedback`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `USER_ID` (`USER_ID`);

--
-- Indexes for table `goals`
--
ALTER TABLE `goals`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `goal_sheets`
--
ALTER TABLE `goal_sheets`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `IDX_GOAL_SUBMITTER` (`SUBMITTER_USER_ID`),
  ADD KEY `IDX_GOAL_STATUS` (`STATUS`),
  ADD KEY `IDX_GOAL_FUNC` (`FUNCTIONAL_LEAD_USER_ID`,`FUNC_STATUS`),
  ADD KEY `IDX_GOAL_CEO` (`CEO_USER_ID`,`CEO_STATUS`);

--
-- Indexes for table `INDUSTRY_MASTER`
--
ALTER TABLE `INDUSTRY_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `kpis`
--
ALTER TABLE `kpis`
  ADD PRIMARY KEY (`KPI_ID`),
  ADD KEY `KRA_ID` (`KRA_ID`);

--
-- Indexes for table `kras`
--
ALTER TABLE `kras`
  ADD PRIMARY KEY (`KRA_ID`),
  ADD KEY `OBJECTIVE_PERFORMANCE_ID` (`OBJECTIVE_PERFORMANCE_ID`);

--
-- Indexes for table `LEADS`
--
ALTER TABLE `LEADS`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `LEADS_HISTORY`
--
ALTER TABLE `LEADS_HISTORY`
  ADD PRIMARY KEY (`HISTORY_ID`);

--
-- Indexes for table `LEAD_COMMENT_READ_STATUS`
--
ALTER TABLE `LEAD_COMMENT_READ_STATUS`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `unique_user_lead` (`LEAD_ID`,`USER_ID`);

--
-- Indexes for table `LEAD_CONTACTS`
--
ALTER TABLE `LEAD_CONTACTS`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `LEAD_SOURCE_MASTER`
--
ALTER TABLE `LEAD_SOURCE_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `LEAD_STATUS_MASTER`
--
ALTER TABLE `LEAD_STATUS_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `LOSS_REASON_MASTER`
--
ALTER TABLE `LOSS_REASON_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `objectives`
--
ALTER TABLE `objectives`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `objectives_performance`
--
ALTER TABLE `objectives_performance`
  ADD PRIMARY KEY (`OBJECTIVE_PERFORMANCE_ID`),
  ADD KEY `USER_ID` (`USER_ID`),
  ADD KEY `fk_objectives_period` (`PERIOD_REF_ID`);

--
-- Indexes for table `organizations`
--
ALTER TABLE `organizations`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `performance_summary`
--
ALTER TABLE `performance_summary`
  ADD PRIMARY KEY (`SUMMARY_ID`),
  ADD KEY `fk_performance_summary_period` (`PERIOD_REF_ID`);

--
-- Indexes for table `ratings`
--
ALTER TABLE `ratings`
  ADD PRIMARY KEY (`RATING_ID`),
  ADD KEY `USER_ID` (`USER_ID`),
  ADD KEY `fk_ratings_period` (`PERIOD_REF_ID`);

--
-- Indexes for table `rating_descriptor`
--
ALTER TABLE `rating_descriptor`
  ADD PRIMARY KEY (`RATING_DESCRIPTOR_ID`);

--
-- Indexes for table `rating_period`
--
ALTER TABLE `rating_period`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `rating_ratio`
--
ALTER TABLE `rating_ratio`
  ADD PRIMARY KEY (`RATING_RATIO_ID`),
  ADD UNIQUE KEY `unique_user_period` (`USER_ID`,`PERIOD_ID`);

--
-- Indexes for table `rationalise_rating`
--
ALTER TABLE `rationalise_rating`
  ADD PRIMARY KEY (`RATIONALISE_RATING_ID`),
  ADD UNIQUE KEY `unique_user_period` (`USER_ID`,`PERIOD_REF_ID`);

--
-- Indexes for table `REGION_MASTER`
--
ALTER TABLE `REGION_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `RENEWAL_STATUS_MASTER`
--
ALTER TABLE `RENEWAL_STATUS_MASTER`
  ADD PRIMARY KEY (`RENEWAL_STATUS_ID`),
  ADD UNIQUE KEY `UK_RENEWAL_STATUS_NAME` (`STATUS_NAME`);

--
-- Indexes for table `status`
--
ALTER TABLE `status`
  ADD PRIMARY KEY (`STATUS_ID`),
  ADD KEY `USER_ID` (`USER_ID`),
  ADD KEY `fk_status_period` (`PERIOD_REF_ID`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`ID`),
  ADD KEY `fk_clients` (`BILLING_TYPE`),
  ADD KEY `CREATED_BY` (`CREATED_BY`);

--
-- Indexes for table `timesheets`
--
ALTER TABLE `timesheets`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `timesheet_submissions`
--
ALTER TABLE `timesheet_submissions`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `USER_ID` (`USER_ID`,`WEEK_START_DATE`,`WEEK_END_DATE`);

--
-- Indexes for table `TO_DO_MASTER`
--
ALTER TABLE `TO_DO_MASTER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_AUDIT_LOG`
--
ALTER TABLE `TTM_AUDIT_LOG`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_DELAY_REASON`
--
ALTER TABLE `TTM_DELAY_REASON`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_FUNCTION_REVIEWER`
--
ALTER TABLE `TTM_FUNCTION_REVIEWER`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_PRIORITY_MASTER`
--
ALTER TABLE `TTM_PRIORITY_MASTER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `PRIORITY_NAME` (`PRIORITY_NAME`),
  ADD UNIQUE KEY `PRIORITY_CODE` (`PRIORITY_CODE`);

--
-- Indexes for table `TTM_RECURRENCE_RULE_MASTER`
--
ALTER TABLE `TTM_RECURRENCE_RULE_MASTER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `RULE_NAME` (`RULE_NAME`),
  ADD UNIQUE KEY `RULE_CODE` (`RULE_CODE`);

--
-- Indexes for table `TTM_REVIEW_STATUS`
--
ALTER TABLE `TTM_REVIEW_STATUS`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `STATUS_CODE` (`STATUS_CODE`);

--
-- Indexes for table `TTM_TASK`
--
ALTER TABLE `TTM_TASK`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `TASK_CODE` (`TASK_CODE`);

--
-- Indexes for table `TTM_TASK_FUNCTION`
--
ALTER TABLE `TTM_TASK_FUNCTION`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_TASK_TEMPLATE`
--
ALTER TABLE `TTM_TASK_TEMPLATE`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `TEMPLATE_CODE` (`TEMPLATE_CODE`);

--
-- Indexes for table `TTM_TASK_TYPE_MASTER`
--
ALTER TABLE `TTM_TASK_TYPE_MASTER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `TYPE_NAME` (`TYPE_NAME`),
  ADD UNIQUE KEY `TYPE_CODE` (`TYPE_CODE`);

--
-- Indexes for table `TTM_TEMPLATE_FUNCTION`
--
ALTER TABLE `TTM_TEMPLATE_FUNCTION`
  ADD PRIMARY KEY (`ID`);

--
-- Indexes for table `TTM_TEMPLATE_STATUS_MASTER`
--
ALTER TABLE `TTM_TEMPLATE_STATUS_MASTER`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `STATUS_NAME` (`STATUS_NAME`),
  ADD UNIQUE KEY `STATUS_CODE` (`STATUS_CODE`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`ID`),
  ADD UNIQUE KEY `USER_ID` (`USER_ID`),
  ADD KEY `fk_organization` (`ORGANIZATION_ID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `ACTIVITIES`
--
ALTER TABLE `ACTIVITIES`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ACTIVITY_TYPE_MASTER`
--
ALTER TABLE `ACTIVITY_TYPE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `AUDIT_LOGS`
--
ALTER TABLE `AUDIT_LOGS`
  MODIFY `AUDIT_LOGS_ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `billing_types`
--
ALTER TABLE `billing_types`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `BILLING_TYPE_MASTER`
--
ALTER TABLE `BILLING_TYPE_MASTER`
  MODIFY `BILLING_TYPE_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `CLIENT_TYPE_MASTER`
--
ALTER TABLE `CLIENT_TYPE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `COMMENTS`
--
ALTER TABLE `COMMENTS`
  MODIFY `COMMENT_ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `COMMENTS_ENGAGEMENT`
--
ALTER TABLE `COMMENTS_ENGAGEMENT`
  MODIFY `COMMENTS_ENGAGEMENT_ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `COMPANY_SIZE_MASTER`
--
ALTER TABLE `COMPANY_SIZE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `CONTRACT`
--
ALTER TABLE `CONTRACT`
  MODIFY `CONTRACT_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `CONTRACT_DOCUMENT`
--
ALTER TABLE `CONTRACT_DOCUMENT`
  MODIFY `DOCUMENT_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `CONTRACT_STATUS_MASTER`
--
ALTER TABLE `CONTRACT_STATUS_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `CURRENCY_MASTER`
--
ALTER TABLE `CURRENCY_MASTER`
  MODIFY `CURRENCY_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `DEAL_STAGE_MASTER`
--
ALTER TABLE `DEAL_STAGE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `departments`
--
ALTER TABLE `departments`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `DOCUMENT_TYPE_MASTER`
--
ALTER TABLE `DOCUMENT_TYPE_MASTER`
  MODIFY `DOCUMENT_TYPE_ID` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ENGAGEMENT_TYPE_MASTER`
--
ALTER TABLE `ENGAGEMENT_TYPE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ENTITY_TYPE_MASTER`
--
ALTER TABLE `ENTITY_TYPE_MASTER`
  MODIFY `ENTITY_TYPE_ID` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `evaluation_periods`
--
ALTER TABLE `evaluation_periods`
  MODIFY `PERIOD_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `feedback`
--
ALTER TABLE `feedback`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `goals`
--
ALTER TABLE `goals`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `goal_sheets`
--
ALTER TABLE `goal_sheets`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `INDUSTRY_MASTER`
--
ALTER TABLE `INDUSTRY_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `kpis`
--
ALTER TABLE `kpis`
  MODIFY `KPI_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `kras`
--
ALTER TABLE `kras`
  MODIFY `KRA_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEADS`
--
ALTER TABLE `LEADS`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEADS_HISTORY`
--
ALTER TABLE `LEADS_HISTORY`
  MODIFY `HISTORY_ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEAD_COMMENT_READ_STATUS`
--
ALTER TABLE `LEAD_COMMENT_READ_STATUS`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEAD_CONTACTS`
--
ALTER TABLE `LEAD_CONTACTS`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEAD_SOURCE_MASTER`
--
ALTER TABLE `LEAD_SOURCE_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LEAD_STATUS_MASTER`
--
ALTER TABLE `LEAD_STATUS_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `LOSS_REASON_MASTER`
--
ALTER TABLE `LOSS_REASON_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `objectives`
--
ALTER TABLE `objectives`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `objectives_performance`
--
ALTER TABLE `objectives_performance`
  MODIFY `OBJECTIVE_PERFORMANCE_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `performance_summary`
--
ALTER TABLE `performance_summary`
  MODIFY `SUMMARY_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ratings`
--
ALTER TABLE `ratings`
  MODIFY `RATING_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rating_descriptor`
--
ALTER TABLE `rating_descriptor`
  MODIFY `RATING_DESCRIPTOR_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rating_period`
--
ALTER TABLE `rating_period`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rating_ratio`
--
ALTER TABLE `rating_ratio`
  MODIFY `RATING_RATIO_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `rationalise_rating`
--
ALTER TABLE `rationalise_rating`
  MODIFY `RATIONALISE_RATING_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `REGION_MASTER`
--
ALTER TABLE `REGION_MASTER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `RENEWAL_STATUS_MASTER`
--
ALTER TABLE `RENEWAL_STATUS_MASTER`
  MODIFY `RENEWAL_STATUS_ID` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `status`
--
ALTER TABLE `status`
  MODIFY `STATUS_ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `timesheets`
--
ALTER TABLE `timesheets`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `timesheet_submissions`
--
ALTER TABLE `timesheet_submissions`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TO_DO_MASTER`
--
ALTER TABLE `TO_DO_MASTER`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_AUDIT_LOG`
--
ALTER TABLE `TTM_AUDIT_LOG`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_DELAY_REASON`
--
ALTER TABLE `TTM_DELAY_REASON`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_FUNCTION_REVIEWER`
--
ALTER TABLE `TTM_FUNCTION_REVIEWER`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_PRIORITY_MASTER`
--
ALTER TABLE `TTM_PRIORITY_MASTER`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_RECURRENCE_RULE_MASTER`
--
ALTER TABLE `TTM_RECURRENCE_RULE_MASTER`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_REVIEW_STATUS`
--
ALTER TABLE `TTM_REVIEW_STATUS`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TASK`
--
ALTER TABLE `TTM_TASK`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TASK_FUNCTION`
--
ALTER TABLE `TTM_TASK_FUNCTION`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TASK_TEMPLATE`
--
ALTER TABLE `TTM_TASK_TEMPLATE`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TASK_TYPE_MASTER`
--
ALTER TABLE `TTM_TASK_TYPE_MASTER`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TEMPLATE_FUNCTION`
--
ALTER TABLE `TTM_TEMPLATE_FUNCTION`
  MODIFY `ID` bigint NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `TTM_TEMPLATE_STATUS_MASTER`
--
ALTER TABLE `TTM_TEMPLATE_STATUS_MASTER`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `ID` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `clients`
--
ALTER TABLE `clients`
  ADD CONSTRAINT `clients_ibfk_1` FOREIGN KEY (`CREATED_BY`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `departments`
--
ALTER TABLE `departments`
  ADD CONSTRAINT `fk_created_by` FOREIGN KEY (`CREATED_BY`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `evaluation_periods`
--
ALTER TABLE `evaluation_periods`
  ADD CONSTRAINT `evaluation_periods_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`);

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`);

--
-- Constraints for table `kpis`
--
ALTER TABLE `kpis`
  ADD CONSTRAINT `kpis_ibfk_1` FOREIGN KEY (`KRA_ID`) REFERENCES `kras` (`KRA_ID`);

--
-- Constraints for table `performance_summary`
--
ALTER TABLE `performance_summary`
  ADD CONSTRAINT `fk_performance_summary_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`);

--
-- Constraints for table `ratings`
--
ALTER TABLE `ratings`
  ADD CONSTRAINT `fk_ratings_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`),
  ADD CONSTRAINT `ratings_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`);

--
-- Constraints for table `status`
--
ALTER TABLE `status`
  ADD CONSTRAINT `fk_status_period` FOREIGN KEY (`PERIOD_REF_ID`) REFERENCES `evaluation_periods` (`PERIOD_ID`),
  ADD CONSTRAINT `status_ibfk_1` FOREIGN KEY (`USER_ID`) REFERENCES `users` (`ID`);

--
-- Constraints for table `tasks`
--
ALTER TABLE `tasks`
  ADD CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`CREATED_BY`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_organization` FOREIGN KEY (`ORGANIZATION_ID`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
