-- إنشاء جداول حفظ كشوف الرواتب الشهرية (رأس + تفاصيل)
-- ملاحظة: هذا السكربت لا يغير APIs الحالية، إنما يجهز التخزين الدائم.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `monthly_payroll` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payroll_month` CHAR(7) NOT NULL COMMENT 'صيغة YYYY-MM',
  `status` ENUM('draft', 'finalized', 'cancelled') NOT NULL DEFAULT 'draft',
  `notes` TEXT NULL,
  `created_by_user_id` INT NOT NULL,
  `finalized_by_user_id` INT NULL,
  `finalized_at` DATETIME NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_monthly_payroll_month` (`payroll_month`),
  KEY `idx_monthly_payroll_status` (`status`),
  KEY `idx_monthly_payroll_created_by` (`created_by_user_id`),
  KEY `idx_monthly_payroll_finalized_by` (`finalized_by_user_id`),
  CONSTRAINT `fk_monthly_payroll_created_by`
    FOREIGN KEY (`created_by_user_id`) REFERENCES `users`(`id_user`)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_monthly_payroll_finalized_by`
    FOREIGN KEY (`finalized_by_user_id`) REFERENCES `users`(`id_user`)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `monthly_payroll_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `payroll_id` BIGINT UNSIGNED NOT NULL,
  `employee_id` INT NOT NULL,
  `employee_name_snapshot` VARCHAR(255) NOT NULL,
  `base_salary` INT NOT NULL DEFAULT 0,
  `absence_deduction` INT NOT NULL DEFAULT 0,
  `advance_deduction` INT NOT NULL DEFAULT 0,
  `overtime_total` INT NOT NULL DEFAULT 0,
  `bonuses_total` INT NOT NULL DEFAULT 0,
  `current_entitlement` INT NOT NULL DEFAULT 0,
  `notes` TEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_monthly_payroll_lines_payroll_employee` (`payroll_id`, `employee_id`),
  KEY `idx_monthly_payroll_lines_employee` (`employee_id`),
  CONSTRAINT `fk_monthly_payroll_lines_payroll`
    FOREIGN KEY (`payroll_id`) REFERENCES `monthly_payroll`(`id`)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_monthly_payroll_lines_employee`
    FOREIGN KEY (`employee_id`) REFERENCES `Emp`(`ID`)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;