-- Balimeen Enterprise OS: Employee Master SQL Schema

-- 1. Extend Employees Table
ALTER TABLE `employees` 
ADD COLUMN `father_name` VARCHAR(100) NULL AFTER `name`,
ADD COLUMN `dob` DATE NULL AFTER `father_name`,
ADD COLUMN `gender` ENUM('Male', 'Female', 'Other') NULL AFTER `dob`,
ADD COLUMN `mobile` VARCHAR(20) NULL AFTER `email`,
ADD COLUMN `blood_group` VARCHAR(5) NULL AFTER `mobile`,
ADD COLUMN `marital_status` ENUM('Single', 'Married', 'Divorced', 'Widowed') NULL AFTER `blood_group`,
ADD COLUMN `designation` VARCHAR(100) NULL AFTER `department`,
ADD COLUMN `shift` VARCHAR(50) NULL AFTER `designation`,
ADD COLUMN `reporting_manager` INT NULL AFTER `shift`,
ADD COLUMN `employment_type` ENUM('Permanent', 'Contract', 'Probation', 'Intern') DEFAULT 'Permanent' AFTER `verification_status`,
ADD COLUMN `profile_completion` INT DEFAULT 0 AFTER `employment_type`;

-- 2. Employee Bank & Statutory Details
CREATE TABLE IF NOT EXISTS `employee_bank` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `bank_name` VARCHAR(100),
    `account_number` VARCHAR(50),
    `ifsc_code` VARCHAR(20),
    `pan_number` VARCHAR(20),
    `aadhaar_number` VARCHAR(20),
    `pf_number` VARCHAR(50),
    `esic_number` VARCHAR(50),
    `uan_number` VARCHAR(50),
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
);

-- 3. Employee Salary
CREATE TABLE IF NOT EXISTS `employee_salary` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `basic_salary` DECIMAL(10,2) DEFAULT 0,
    `hra` DECIMAL(10,2) DEFAULT 0,
    `special_allowance` DECIMAL(10,2) DEFAULT 0,
    `pf_deduction` DECIMAL(10,2) DEFAULT 0,
    `esic_deduction` DECIMAL(10,2) DEFAULT 0,
    `tax_deduction` DECIMAL(10,2) DEFAULT 0,
    `net_salary` DECIMAL(10,2) DEFAULT 0,
    `effective_date` DATE,
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
);

-- 4. Employee Activity Logs
CREATE TABLE IF NOT EXISTS `employee_activity_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `action` VARCHAR(255) NOT NULL,
    `performed_by` INT,
    `ip_address` VARCHAR(45),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE
);
