-- ترقية من جدول employees القديم (أعمدة office/activity/national_id/department_id...) إلى النموذج الجديد
-- راجع أسماء القيود الأجنبية في قاعدتك (SHOW CREATE TABLE employees;)
-- نفّذ سطراً تلو الآخر وتجاهل «Duplicate column» إن وُجد العمود مسبقاً.

CREATE TABLE IF NOT EXISTS `job_titles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name_ar` varchar(100) NOT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_job_titles_name_ar` (`name_ar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `job_titles` (`name_ar`, `sort_order`) VALUES
('مشرف عمل', 1),
('فني', 2),
('مدير المشرفين', 3),
('المدير المالي', 4),
('مدير الموارد البشرية', 5)
ON DUPLICATE KEY UPDATE `sort_order` = VALUES(`sort_order`);

-- إسقاط القيد القديم على department_id (عدّل الاسم إن اختلف)
-- ALTER TABLE employees DROP FOREIGN KEY fk_employees_department;

ALTER TABLE employees ADD COLUMN work_end_date date DEFAULT NULL AFTER hire_date;
ALTER TABLE employees ADD COLUMN main_department_id int(11) DEFAULT NULL AFTER work_end_date;
ALTER TABLE employees ADD COLUMN sub_department_id int(11) DEFAULT NULL AFTER main_department_id;
ALTER TABLE employees ADD COLUMN job_title_id int(11) DEFAULT NULL AFTER sub_department_id;
ALTER TABLE employees ADD COLUMN salary decimal(14,2) DEFAULT NULL AFTER job_title_id;
ALTER TABLE employees ADD COLUMN employee_address text AFTER salary;
ALTER TABLE employees ADD COLUMN nationality varchar(80) DEFAULT NULL AFTER employee_address;
ALTER TABLE employees ADD COLUMN gender varchar(20) DEFAULT NULL AFTER nationality;
ALTER TABLE employees ADD COLUMN dhaman tinyint(1) NOT NULL DEFAULT 0 AFTER gender;

-- نقل department_id القديم إلى main_department_id إن وُجد
-- UPDATE employees SET main_department_id = department_id WHERE main_department_id IS NULL AND department_id IS NOT NULL;

-- حذف أعمدة قديمة (بعد التأكد)
-- ALTER TABLE employees DROP COLUMN national_id;
-- ALTER TABLE employees DROP COLUMN office;
-- ALTER TABLE employees DROP COLUMN activity;
-- ALTER TABLE employees DROP COLUMN job_title;
-- ALTER TABLE employees DROP COLUMN department_id;

-- فهارس وقيود (نفّذ بعد حذف department_id وإلا قد يتعارض)
-- ALTER TABLE employees ADD KEY idx_emp_main_dept (main_department_id);
-- ALTER TABLE employees ADD KEY idx_emp_sub_dept (sub_department_id);
-- ALTER TABLE employees ADD KEY idx_emp_job (job_title_id);
-- ALTER TABLE employees ADD CONSTRAINT fk_emp_main_dept FOREIGN KEY (main_department_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE employees ADD CONSTRAINT fk_emp_sub_dept FOREIGN KEY (sub_department_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE;
-- ALTER TABLE employees ADD CONSTRAINT fk_emp_job_title FOREIGN KEY (job_title_id) REFERENCES job_titles(id) ON DELETE SET NULL ON UPDATE CASCADE;