--
-- Host: 127.0.0.1    Database: smdummy572026
-- ------------------------------------------------------


--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `employee_id` varchar(15) NOT NULL,
  `biometric_employee_no` varchar(50) DEFAULT NULL,
  `company_id` int(11) NOT NULL DEFAULT 1,
  `brand_id` int(11) NOT NULL DEFAULT 1,
  `first_name` varchar(50) NOT NULL,
  `middle_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `hire_date` date DEFAULT NULL,
  `termination_date` date DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `department_id` varchar(11) DEFAULT NULL,
  `position_id` varchar(50) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `description` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `base_salary` decimal(10,2) NOT NULL DEFAULT 0.00,
  `salary_type` enum('daily','monthly') DEFAULT 'daily',
  `monthly_rate` decimal(10,2) DEFAULT 0.00,
  `hourly_rate` decimal(10,2) DEFAULT 0.00,
  `reset_code` varchar(10) DEFAULT NULL,
  `employee_type` enum('Regular','Probationary','Part-time','OJT','Contractual','Project-Based','Fixed-Term','Seasonal','Casual','Temporary','Reliever') DEFAULT 'Regular',
  `payroll_type_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `branch_name` varchar(100) DEFAULT NULL,
  `employee_punching_method` enum('biometrics','mobile') DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  KEY `fk_employees_payroll_type` (`payroll_type_id`),
  KEY `idx_emp_status_type_name` (`status`,`employee_type`,`last_name`,`first_name`,`employee_id`),
  KEY `idx_employees_company_brand_status` (`company_id`,`brand_id`,`status`,`last_name`,`first_name`),
  KEY `idx_employees_brand_status` (`company_id`,`brand_id`,`status`),
  KEY `idx_employees_brand_status_branch_dept_type` (`company_id`,`brand_id`,`status`,`branch_id`,`department_id`,`employee_type`),
  KEY `idx_employees_employee_brand` (`employee_id`,`company_id`,`brand_id`),
  CONSTRAINT `fk_employees_payroll_type` FOREIGN KEY (`payroll_type_id`) REFERENCES `payroll_types` (`payroll_type_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employees`
--


DELIMITER ;;
;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_employees_before_insert`
BEFORE INSERT ON `employees`
FOR EACH ROW
BEGIN
    DECLARE v_branch_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_branch_brand_id INT DEFAULT NULL;
    DECLARE v_branch_company_id INT DEFAULT NULL;
    DECLARE v_not_found TINYINT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_not_found = 1;

    IF NEW.branch_id IS NULL OR NEW.branch_id = 0 THEN
        SET NEW.branch_id = NULL;
        SET NEW.branch_name = NULL;
    ELSE
        SELECT br.branch_name, br.brand_id, br.company_id
        INTO v_branch_name, v_branch_brand_id, v_branch_company_id
        FROM `branches` br
        WHERE br.branch_id = NEW.branch_id
          AND br.is_active = 1
        LIMIT 1;

        IF v_not_found = 1 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Selected branch does not exist or is inactive.';
        END IF;

        IF NEW.brand_id <> v_branch_brand_id OR NEW.company_id <> v_branch_company_id THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Selected branch does not belong to the employee brand.';
        END IF;

        SET NEW.branch_name = v_branch_name;
    END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_employee_insert` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
    DECLARE full_name VARCHAR(150);
    DECLARE dept_name VARCHAR(100);
    DECLARE pos_name VARCHAR(100);

    -- Merge first_name, middle_name, and last_name into full_name
    SET full_name = CONCAT(
        NEW.first_name, 
        IF(NEW.middle_name IS NOT NULL AND NEW.middle_name != '', CONCAT(' ', NEW.middle_name), ''), 
        ' ', 
        NEW.last_name
    );

    -- Get department name from departments table
    SELECT department_name INTO dept_name 
    FROM departments 
    WHERE department_id = NEW.department_id;

    -- Get position name from positions table using the position_id from employees table
    SELECT position_name INTO pos_name 
    FROM positions 
    WHERE position_id = NEW.position_id;

    -- Insert the data into salary_for_employee with default values for position_level and step
    INSERT INTO salary_for_employee (
        employee_id, 
        employee_name, 
        department_name, 
        position_name, 
        position_level, 
        step
    ) VALUES (
        NEW.employee_id, 
        full_name, 
        IFNULL(dept_name, 'Unknown'), 
        IFNULL(pos_name, 'Unknown'),  -- Use the fetched position name or 'Unknown' if not found
        1, -- Default Position Level
        1  -- Default Step
    );
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_insert_employee` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
    -- Insert a default attendance record for the new employee
    INSERT INTO attendance (employee_id, employee_name, attendance_date)
    VALUES (NEW.employee_id, CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name), CURDATE());
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `insert_user_after_employee_password` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
  IF NOT EXISTS (
      SELECT 1 
      FROM users 
      WHERE user_id = NEW.employee_id
         OR username = NEW.employee_id
  ) THEN
    INSERT INTO users (
      user_id,
      username,
      password,
      role,
      status
    )
    VALUES (
      NEW.employee_id,
      NEW.employee_id,
      SHA2(NEW.password, 256),
      'employee',
      CASE
        WHEN NEW.status = 'active' THEN 'active'
        ELSE 'disabled'
      END
    );
  END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `new_after_employee_insert` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
    INSERT INTO payroll (employee_id, name)
    VALUES (NEW.employee_id, CONCAT(NEW.first_name, ' ', COALESCE(NEW.middle_name, ''), ' ', NEW.last_name));
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_employee_insert_leave_balances` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
    INSERT IGNORE INTO employee_leave_balances (
        employee_id,
        leave_type_id,
        year,
        leave_limit,
        leave_used
    )
    SELECT
        NEW.employee_id,
        lt.leave_type_id,
        COALESCE(YEAR(NEW.hire_date), YEAR(CURDATE())),
        lt.leave_limit,
        0.00
    FROM leave_types lt;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_insert_base_salary` AFTER INSERT ON `employees` FOR EACH ROW BEGIN
  INSERT INTO base_salary (
    employee_id,
    first_name,
    middle_name,
    last_name,
    basic_salary
  ) VALUES (
    NEW.employee_id,
    NEW.first_name,
    NEW.middle_name,
    NEW.last_name,
    NEW.base_salary
  );
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_employees_before_update`
BEFORE UPDATE ON `employees`
FOR EACH ROW
BEGIN
    DECLARE v_branch_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_branch_brand_id INT DEFAULT NULL;
    DECLARE v_branch_company_id INT DEFAULT NULL;
    DECLARE v_not_found TINYINT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_not_found = 1;

    IF NEW.branch_id IS NULL OR NEW.branch_id = 0 THEN
        SET NEW.branch_id = NULL;
        SET NEW.branch_name = NULL;
    ELSE
        SELECT br.branch_name, br.brand_id, br.company_id
        INTO v_branch_name, v_branch_brand_id, v_branch_company_id
        FROM `branches` br
        WHERE br.branch_id = NEW.branch_id
          AND br.is_active = 1
        LIMIT 1;

        IF v_not_found = 1 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Selected branch does not exist or is inactive.';
        END IF;

        IF NEW.brand_id <> v_branch_brand_id OR NEW.company_id <> v_branch_company_id THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Selected branch does not belong to the employee brand.';
        END IF;

        SET NEW.branch_name = v_branch_name;
    END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_employee_name_update` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    DECLARE full_name VARCHAR(150);

    -- Merge first_name, middle_name, and last_name into full_name
    SET full_name = CONCAT(
        NEW.first_name, 
        IF(NEW.middle_name IS NOT NULL AND NEW.middle_name != '', CONCAT(' ', NEW.middle_name), ''), 
        ' ', 
        NEW.last_name
    );

    -- Update the employee_name in salary_for_employee table
    UPDATE salary_for_employee 
    SET employee_name = full_name
    WHERE employee_id = NEW.employee_id;

END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `after_update_employee` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    -- Update the employee_name in the attendance table
    UPDATE attendance
    SET employee_name = CONCAT_WS(' ', NEW.first_name, NEW.middle_name, NEW.last_name)
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `employee_update` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    -- Update the record in the base_salary table when an existing employee is updated
    UPDATE base_salary
    SET first_name = NEW.first_name,
        middle_name = NEW.middle_name,
        last_name = NEW.last_name
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `new_employee_payroll_update` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    -- Ensure it updates the existing record instead of inserting a new one
    UPDATE payroll
    SET name = CONCAT(NEW.first_name, ' ', COALESCE(NEW.middle_name, ''), ' ', NEW.last_name)
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_update_base_salary` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
  IF NEW.base_salary <> OLD.base_salary THEN

    -- Update current base_salary table (keeps your existing structure)
    UPDATE base_salary
    SET basic_salary = NEW.base_salary,
        first_name = NEW.first_name,
        middle_name = NEW.middle_name,
        last_name = NEW.last_name
    WHERE employee_id = NEW.employee_id;

    -- Insert history; read @app_user session var if provided
    INSERT INTO employee_rate_history (
      employee_id,
      old_rate,
      new_rate,
      changed_by,
      source
    ) VALUES (
      NEW.employee_id,
      OLD.base_salary,
      NEW.base_salary,
      COALESCE(@app_user, 'system'),
      COALESCE(@app_source, NULL)
    );

  END IF;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `update_department_name` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    DECLARE dept_name VARCHAR(100);
    
    -- Fetch the new department name based on the new department_id
    SELECT department_name INTO dept_name FROM departments WHERE department_id = NEW.department_id;

    -- Update the salary_for_employee table with the new department name
    UPDATE salary_for_employee
    SET department_name = dept_name
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `update_employee_name` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    UPDATE attendance
    SET employee_name = CONCAT(NEW.first_name, ' ', COALESCE(NEW.middle_name, ''), ' ', NEW.last_name)
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `update_position_name` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    DECLARE pos_name VARCHAR(100);
    
    -- Fetch the new position name based on the new position_id
    SELECT position_name INTO pos_name FROM positions WHERE position_id = NEW.position_id;

    -- Update the salary_for_employee table with the new position name
    UPDATE salary_for_employee
    SET position_name = pos_name
    WHERE employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `update_salary_when_employee_updates` AFTER UPDATE ON `employees` FOR EACH ROW BEGIN
    UPDATE salary_for_employee sfe
    JOIN salary_grades sg ON sfe.position_level = sg.GradeID
    SET sfe.salary = CASE sfe.step
        WHEN 1 THEN sg.Step1
        WHEN 2 THEN sg.Step2
        WHEN 3 THEN sg.Step3
        ELSE sfe.salary
    END
    WHERE sfe.employee_id = NEW.employee_id;
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_delete_user_after_employee` AFTER DELETE ON `employees` FOR EACH ROW BEGIN
    DELETE FROM users
    WHERE username = OLD.employee_id
      AND role = 'employee';
END */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_employee_delete_leave_balances` AFTER DELETE ON `employees` FOR EACH ROW BEGIN
    DELETE FROM employee_leave_balances
    WHERE employee_id = OLD.employee_id;
END */;;
DELIMITER ;


