-- ============================================================
-- OREMUS - SHARED / COMMON TABLES
-- Used across: Timesheet, BSC, GoalSheet, CRM, TTM modules
-- Database: Oremus
-- ============================================================

-- --------------------------------------------------------
-- organizations
-- Root entity - org that owns all users and clients
-- --------------------------------------------------------
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,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- departments
-- Shared across Timesheet, TTM, CRM contexts
-- FK: CREATED_BY -> organizations.ID
-- --------------------------------------------------------
CREATE TABLE `departments` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `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',
  PRIMARY KEY (`ID`),
  KEY `fk_created_by` (`CREATED_BY`),
  CONSTRAINT `fk_created_by` FOREIGN KEY (`CREATED_BY`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------
-- users
-- All internal employees across every module
-- Roles: employee, team_lead, reporting_manager, functional_lead, ceo, crm, hr, admin
-- CRM_ACTIVE flag controls CRM access
-- FK: ORGANIZATION_ID -> organizations.ID
-- FK: DEPARTMENT_REF_ID -> departments.ID (logical, no FK constraint in DB)
-- --------------------------------------------------------
CREATE TABLE `users` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `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 COMMENT 'Comma-separated or JSON of additional department IDs',
  `MANAGER_ID` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'References USER_ID of the reporting manager',
  `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' COMMENT '1 = has CRM access',
  `TTM_ACTIVE` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 = user participates in TTM system',
  `TTM_ROLE` varchar(20) DEFAULT NULL COMMENT 'PA = Process Associate, TL = Team Lead (only when TTM_ACTIVE=1 and ROLE=employee)',
  `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',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `USER_ID` (`USER_ID`),
  KEY `fk_organization` (`ORGANIZATION_ID`),
  CONSTRAINT `fk_organization` FOREIGN KEY (`ORGANIZATION_ID`) REFERENCES `organizations` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ============================================================
-- RELATIONSHIPS SUMMARY
-- organizations (1) -> (*) departments
-- organizations (1) -> (*) users
-- organizations (1) -> (*) clients      [Timesheet]
-- organizations (1) -> (*) tasks        [Timesheet]
-- users (1) -> (*) timesheets           [Timesheet]
-- users (1) -> (*) goal_sheets          [GoalSheet]
-- users (1) -> (*) evaluation_periods   [BSC]
-- users.ID -> TTM_TASK.ASSIGNED_FL_EMP_REF_ID [TTM]
-- ============================================================
