<?php
declare(strict_types=1);

return [
    'up' => "
CREATE TABLE IF NOT EXISTS hr_weekly_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_profile_id BIGINT UNSIGNED NOT NULL,
    day_of_week ENUM('saturday','sunday','monday','tuesday','wednesday','thursday','friday') NOT NULL,
    shift_id INT UNSIGNED NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    is_rest_day TINYINT(1) NOT NULL DEFAULT 0,
    notes VARCHAR(200) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_profile_id) REFERENCES hr_employee_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY (shift_id) REFERENCES hr_shifts(id) ON DELETE SET NULL,
    UNIQUE KEY uk_emp_day (employee_profile_id, day_of_week, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS hr_job_descriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    job_title_id BIGINT UNSIGNED NOT NULL,
    purpose_ar TEXT NULL,
    responsible_authority VARCHAR(200) NULL,
    relationships VARCHAR(200) NULL,
    authority_limits TEXT NULL,
    required_qualification VARCHAR(200) NULL,
    duties_ar TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT UNSIGNED NULL,
    is_archived TINYINT(1) NOT NULL DEFAULT 0,
    FOREIGN KEY (job_title_id) REFERENCES hr_job_titles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

UPDATE hr_departments SET parent_id = 1 WHERE department_code != 'EXEC' AND parent_id IS NULL AND is_archived = 0
",
    'down' => "
DROP TABLE IF EXISTS hr_weekly_schedules;
DROP TABLE IF EXISTS hr_job_descriptions;
UPDATE hr_departments SET parent_id = NULL WHERE parent_id = 1
",
];
