-- ========================================
-- Module Employees (Salariés) - Database Tables
-- ========================================

-- Table: tblemployees (Salariés)
CREATE TABLE IF NOT EXISTS `tblemployees` (
  `userid` INT AUTO_INCREMENT PRIMARY KEY,
  `identifier` VARCHAR(20) UNIQUE COMMENT 'Format: SAAAA-XX',
  `firstname` VARCHAR(191),
  `lastname` VARCHAR(191),
  `date_of_birth` DATE,
  `place_of_birth` VARCHAR(191),
  `gender` ENUM('male', 'female', 'other'),
  `social_security_number` VARCHAR(50),
  `email` VARCHAR(191),
  `phonenumber` VARCHAR(30),
  `address` TEXT,
  `city` VARCHAR(100),
  `zip` VARCHAR(15),
  `country` INT DEFAULT 0,
  `department` INT,
  `position` VARCHAR(191),
  `hire_date` DATE,
  `contract_type` ENUM('cdi', 'cdd', 'interim', 'stage', 'apprentissage'),
  `weekly_hours` DECIMAL(5,2),
  `gross_salary` DECIMAL(15,2),
  `bank_account` VARCHAR(50),
  `bank_name` VARCHAR(100),
  `education_level` VARCHAR(191),
  `emergency_contact_name` VARCHAR(191),
  `emergency_contact_phone` VARCHAR(30),
  `active` TINYINT(1) DEFAULT 1,
  `datecreated` DATETIME,
  `addedfrom` INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: tblemployee_contracts (Contrats de travail)
CREATE TABLE IF NOT EXISTS `tblemployee_contracts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT NOT NULL,
  `contract_number` VARCHAR(50),
  `contract_type` ENUM('cdi', 'cdd', 'interim', 'stage', 'apprentissage', 'avenant'),
  `position` VARCHAR(191),
  `start_date` DATE NOT NULL,
  `end_date` DATE,
  `trial_period_end` DATE,
  `gross_salary` DECIMAL(15,2),
  `weekly_hours` DECIMAL(5,2),
  `description` TEXT,
  `file_path` VARCHAR(500),
  `status` ENUM('active', 'expired', 'terminated', 'pending') DEFAULT 'pending',
  `signed_date` DATE,
  `dateadded` DATETIME,
  `addedfrom` INT,
  INDEX (`employee_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tblemployees`(`userid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: tblemployee_payslips (Fiches de paie)
CREATE TABLE IF NOT EXISTS `tblemployee_payslips` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT NOT NULL,
  `period_month` INT NOT NULL,
  `period_year` INT NOT NULL,
  `gross_salary` DECIMAL(15,2),
  `net_salary` DECIMAL(15,2),
  `employee_contributions` DECIMAL(15,2) DEFAULT 0,
  `employer_contributions` DECIMAL(15,2) DEFAULT 0,
  `bonuses` DECIMAL(15,2) DEFAULT 0,
  `deductions` DECIMAL(15,2) DEFAULT 0,
  `file_path` VARCHAR(500),
  `file_name` VARCHAR(191),
  `status` ENUM('draft', 'validated', 'sent', 'paid') DEFAULT 'draft',
  `payment_date` DATE,
  `notes` TEXT,
  `dateadded` DATETIME,
  `addedfrom` INT,
  UNIQUE KEY (`employee_id`, `period_month`, `period_year`),
  INDEX (`employee_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tblemployees`(`userid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: tblemployee_attachments (Pièces jointes)
CREATE TABLE IF NOT EXISTS `tblemployee_attachments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `employee_id` INT NOT NULL,
  `attachment_name` VARCHAR(255),
  `file_name` VARCHAR(191),
  `file_path` VARCHAR(500),
  `file_type` VARCHAR(50),
  `attachment_type` VARCHAR(50) DEFAULT 'other' COMMENT 'cv, diploma, certificate, id_card, rib, other',
  `description` TEXT,
  `dateadded` DATETIME,
  `addedfrom` INT,
  INDEX (`employee_id`),
  FOREIGN KEY (`employee_id`) REFERENCES `tblemployees`(`userid`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: tblemployee_departments (Services / Départements)
CREATE TABLE IF NOT EXISTS `tblemployee_departments` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(191) NOT NULL,
  `description` TEXT,
  `manager_id` INT,
  `dateadded` DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
