-- Migration: Add Master Tables
-- Date: 2025-12-15
-- Description: Creates master tables for departments, designations, and categories

-- ============================================
-- MASTER TABLES (Global Reference Data)
-- ============================================

-- Departments Master Table (Global department list)
CREATE TABLE IF NOT EXISTS `departments_master` (
  `department_id` int(11) NOT NULL AUTO_INCREMENT,
  `department_code` varchar(50) NOT NULL,
  `department_name` varchar(255) NOT NULL,
  `description` text,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`department_id`),
  UNIQUE KEY `unique_dept_code` (`department_code`),
  KEY `idx_dept_name` (`department_name`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Designations Master Table (Global designation list)
CREATE TABLE IF NOT EXISTS `designations_master` (
  `designation_id` int(11) NOT NULL AUTO_INCREMENT,
  `designation_code` varchar(50) NOT NULL,
  `designation_name` varchar(255) NOT NULL,
  `description` text,
  `designation_level` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`designation_id`),
  UNIQUE KEY `unique_desig_code` (`designation_code`),
  KEY `idx_desig_name` (`designation_name`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Categories Master Table (Global categories list)
CREATE TABLE IF NOT EXISTS `categories_master` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `category_code` varchar(50) NOT NULL,
  `category_name` varchar(255) NOT NULL,
  `category_type` varchar(100),
  `description` text,
  `parent_category_id` int(11),
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique_category_code` (`category_code`),
  KEY `idx_category_name` (`category_name`),
  KEY `idx_category_type` (`category_type`),
  KEY `idx_status` (`status`),
  KEY `idx_parent` (`parent_category_id`),
  CONSTRAINT `fk_category_parent` FOREIGN KEY (`parent_category_id`) REFERENCES `categories_master` (`category_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

