CREATE TABLE work_calendars (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NULL,
    code VARCHAR(60) NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    name_en VARCHAR(150) NULL,
    timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Riyadh',
    hours_per_day DECIMAL(5,2) NOT NULL DEFAULT 8.00,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_work_calendar_project_code (project_id, code),
    KEY idx_work_calendars_project (project_id, status),
    CONSTRAINT fk_work_calendars_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_work_calendars_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE work_calendar_weekdays (
    work_calendar_id BIGINT UNSIGNED NOT NULL,
    weekday_number TINYINT UNSIGNED NOT NULL,
    is_working TINYINT(1) NOT NULL DEFAULT 0,
    working_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (work_calendar_id, weekday_number),
    CONSTRAINT fk_calendar_weekdays_calendar FOREIGN KEY (work_calendar_id) REFERENCES work_calendars(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE work_calendar_exceptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    work_calendar_id BIGINT UNSIGNED NOT NULL,
    exception_date DATE NOT NULL,
    name_ar VARCHAR(150) NOT NULL,
    is_working TINYINT(1) NOT NULL DEFAULT 0,
    working_minutes SMALLINT UNSIGNED NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_calendar_exception (work_calendar_id, exception_date),
    KEY idx_calendar_exceptions_date (exception_date),
    CONSTRAINT fk_calendar_exceptions_calendar FOREIGN KEY (work_calendar_id) REFERENCES work_calendars(id) ON DELETE CASCADE,
    CONSTRAINT fk_calendar_exceptions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_schedule_settings (
    project_id BIGINT UNSIGNED PRIMARY KEY,
    default_calendar_id BIGINT UNSIGNED NULL,
    active_baseline_id BIGINT UNSIGNED NULL,
    data_date DATE NOT NULL,
    scheduling_direction VARCHAR(20) NOT NULL DEFAULT 'forward',
    enforce_weight_total TINYINT(1) NOT NULL DEFAULT 1,
    next_wbs_sequence INT UNSIGNED NOT NULL DEFAULT 1,
    next_activity_sequence INT UNSIGNED NOT NULL DEFAULT 1,
    last_calculated_at DATETIME NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_schedule_settings_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_schedule_settings_calendar FOREIGN KEY (default_calendar_id) REFERENCES work_calendars(id) ON DELETE SET NULL,
    CONSTRAINT fk_schedule_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wbs_nodes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    name_en VARCHAR(190) NULL,
    node_type VARCHAR(40) NOT NULL DEFAULT 'work_package',
    description TEXT NULL,
    level_number SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    weight_percentage DECIMAL(9,6) NOT NULL DEFAULT 0.000000,
    progress_method VARCHAR(40) NOT NULL DEFAULT 'weighted_children',
    is_control_account TINYINT(1) NOT NULL DEFAULT 0,
    cached_planned_progress DECIMAL(8,4) NULL,
    cached_actual_progress DECIMAL(8,4) NULL,
    cached_start_date DATE NULL,
    cached_finish_date DATE NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    archived_at DATETIME NULL,
    UNIQUE KEY uq_wbs_project_code (project_id, code),
    KEY idx_wbs_parent (project_id, parent_id, sort_order),
    KEY idx_wbs_level (project_id, level_number),
    CONSTRAINT fk_wbs_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_wbs_parent FOREIGN KEY (parent_id) REFERENCES wbs_nodes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_wbs_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wbs_node_closure (
    ancestor_id BIGINT UNSIGNED NOT NULL,
    descendant_id BIGINT UNSIGNED NOT NULL,
    depth SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (ancestor_id, descendant_id),
    KEY idx_wbs_closure_descendant (descendant_id, ancestor_id),
    CONSTRAINT fk_wbs_closure_ancestor FOREIGN KEY (ancestor_id) REFERENCES wbs_nodes(id) ON DELETE CASCADE,
    CONSTRAINT fk_wbs_closure_descendant FOREIGN KEY (descendant_id) REFERENCES wbs_nodes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schedule_activities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NOT NULL,
    wbs_node_id BIGINT UNSIGNED NOT NULL,
    work_calendar_id BIGINT UNSIGNED NULL,
    activity_code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    name_en VARCHAR(190) NULL,
    activity_type VARCHAR(30) NOT NULL DEFAULT 'activity',
    scheduling_mode VARCHAR(20) NOT NULL DEFAULT 'auto',
    responsible_employee_id BIGINT UNSIGNED NULL,
    original_duration_days DECIMAL(10,2) NULL,
    current_duration_days DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    remaining_duration_days DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    current_start_date DATE NULL,
    current_finish_date DATE NULL,
    actual_start_date DATE NULL,
    actual_finish_date DATE NULL,
    calculated_early_start DATE NULL,
    calculated_early_finish DATE NULL,
    calculated_late_start DATE NULL,
    calculated_late_finish DATE NULL,
    total_float_days DECIMAL(10,2) NULL,
    free_float_days DECIMAL(10,2) NULL,
    is_critical TINYINT(1) NOT NULL DEFAULT 0,
    constraint_type VARCHAR(50) NULL,
    constraint_date DATE NULL,
    progress_weight DECIMAL(12,6) NOT NULL DEFAULT 1.000000,
    physical_progress DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    duration_progress DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    status VARCHAR(30) NOT NULL DEFAULT 'not_started',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    archived_at DATETIME NULL,
    UNIQUE KEY uq_activity_project_code (project_id, activity_code),
    KEY idx_activities_wbs (wbs_node_id, archived_at),
    KEY idx_activities_dates (project_id, current_start_date, current_finish_date),
    KEY idx_activities_responsible (responsible_employee_id, status),
    KEY idx_activities_critical (project_id, is_critical, archived_at),
    CONSTRAINT fk_activities_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_activities_wbs FOREIGN KEY (wbs_node_id) REFERENCES wbs_nodes(id) ON DELETE RESTRICT,
    CONSTRAINT fk_activities_calendar FOREIGN KEY (work_calendar_id) REFERENCES work_calendars(id) ON DELETE SET NULL,
    CONSTRAINT fk_activities_responsible FOREIGN KEY (responsible_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    CONSTRAINT fk_activities_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_dependencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    predecessor_activity_id BIGINT UNSIGNED NOT NULL,
    successor_activity_id BIGINT UNSIGNED NOT NULL,
    dependency_type CHAR(2) NOT NULL DEFAULT 'FS',
    lag_days DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_dependency (predecessor_activity_id, successor_activity_id),
    KEY idx_dependencies_successor (successor_activity_id),
    KEY idx_dependencies_project (project_id),
    CONSTRAINT fk_dependencies_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_dependencies_predecessor FOREIGN KEY (predecessor_activity_id) REFERENCES schedule_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_dependencies_successor FOREIGN KEY (successor_activity_id) REFERENCES schedule_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_dependencies_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activity_progress_updates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    activity_id BIGINT UNSIGNED NOT NULL,
    data_date DATE NOT NULL,
    previous_physical_progress DECIMAL(8,4) NOT NULL,
    physical_progress DECIMAL(8,4) NOT NULL,
    previous_remaining_duration_days DECIMAL(10,2) NULL,
    remaining_duration_days DECIMAL(10,2) NULL,
    actual_start_date DATE NULL,
    actual_finish_date DATE NULL,
    notes TEXT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_progress_activity_date (activity_id, data_date, created_at),
    CONSTRAINT fk_progress_updates_activity FOREIGN KEY (activity_id) REFERENCES schedule_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_progress_updates_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE schedule_calculation_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    data_date DATE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'running',
    activities_count INT UNSIGNED NOT NULL DEFAULT 0,
    critical_activities_count INT UNSIGNED NOT NULL DEFAULT 0,
    project_finish_date DATE NULL,
    error_message TEXT NULL,
    calculated_by BIGINT UNSIGNED NULL,
    started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    KEY idx_schedule_runs_project (project_id, started_at),
    CONSTRAINT fk_schedule_runs_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_schedule_runs_user FOREIGN KEY (calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_schedule_baselines (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    description TEXT NULL,
    data_date DATE NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    approval_request_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approved_by BIGINT UNSIGNED NULL,
    approved_at DATETIME NULL,
    UNIQUE KEY uq_baseline_project_version (project_id, version_number),
    KEY idx_baselines_project_status (project_id, status, is_primary),
    CONSTRAINT fk_baselines_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_baselines_approval FOREIGN KEY (approval_request_id) REFERENCES approval_requests(id) ON DELETE SET NULL,
    CONSTRAINT fk_baselines_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_baselines_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE project_schedule_settings
    ADD CONSTRAINT fk_schedule_settings_baseline FOREIGN KEY (active_baseline_id) REFERENCES project_schedule_baselines(id) ON DELETE SET NULL;

CREATE TABLE baseline_wbs_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    baseline_id BIGINT UNSIGNED NOT NULL,
    source_wbs_node_id BIGINT UNSIGNED NOT NULL,
    source_parent_wbs_node_id BIGINT UNSIGNED NULL,
    code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    node_type VARCHAR(40) NOT NULL,
    level_number SMALLINT UNSIGNED NOT NULL,
    sort_order INT NOT NULL,
    weight_percentage DECIMAL(9,6) NOT NULL,
    UNIQUE KEY uq_baseline_wbs_source (baseline_id, source_wbs_node_id),
    CONSTRAINT fk_baseline_wbs_baseline FOREIGN KEY (baseline_id) REFERENCES project_schedule_baselines(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE baseline_activity_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    baseline_id BIGINT UNSIGNED NOT NULL,
    source_activity_id BIGINT UNSIGNED NOT NULL,
    source_wbs_node_id BIGINT UNSIGNED NOT NULL,
    work_calendar_id BIGINT UNSIGNED NULL,
    activity_code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    activity_type VARCHAR(30) NOT NULL,
    duration_days DECIMAL(10,2) NOT NULL,
    baseline_start_date DATE NULL,
    baseline_finish_date DATE NULL,
    progress_weight DECIMAL(12,6) NOT NULL,
    responsible_employee_id BIGINT UNSIGNED NULL,
    UNIQUE KEY uq_baseline_activity_source (baseline_id, source_activity_id),
    KEY idx_baseline_activity_dates (baseline_id, baseline_start_date, baseline_finish_date),
    CONSTRAINT fk_baseline_activities_baseline FOREIGN KEY (baseline_id) REFERENCES project_schedule_baselines(id) ON DELETE CASCADE,
    CONSTRAINT fk_baseline_activities_calendar FOREIGN KEY (work_calendar_id) REFERENCES work_calendars(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE baseline_dependency_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    baseline_id BIGINT UNSIGNED NOT NULL,
    predecessor_source_activity_id BIGINT UNSIGNED NOT NULL,
    successor_source_activity_id BIGINT UNSIGNED NOT NULL,
    dependency_type CHAR(2) NOT NULL,
    lag_days DECIMAL(10,2) NOT NULL,
    UNIQUE KEY uq_baseline_dependency (baseline_id, predecessor_source_activity_id, successor_source_activity_id),
    CONSTRAINT fk_baseline_dependencies_baseline FOREIGN KEY (baseline_id) REFERENCES project_schedule_baselines(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_progress_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    data_date DATE NOT NULL,
    baseline_id BIGINT UNSIGNED NULL,
    planned_progress DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    actual_progress DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    progress_variance DECIMAL(8,4) NOT NULL DEFAULT 0.0000,
    elapsed_time_percentage DECIMAL(8,4) NULL,
    forecast_finish_date DATE NULL,
    calculated_by BIGINT UNSIGNED NULL,
    calculated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_progress_date (project_id, data_date),
    KEY idx_progress_snapshots_baseline (baseline_id, data_date),
    CONSTRAINT fk_project_progress_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_progress_baseline FOREIGN KEY (baseline_id) REFERENCES project_schedule_baselines(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_progress_user FOREIGN KEY (calculated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    code VARCHAR(80) NOT NULL UNIQUE,
    name_ar VARCHAR(190) NOT NULL,
    name_en VARCHAR(190) NULL,
    project_type_id BIGINT UNSIGNED NULL,
    description TEXT NULL,
    active_version_id BIGINT UNSIGNED NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_project_templates_type FOREIGN KEY (project_type_id) REFERENCES project_types(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_template_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_template_id BIGINT UNSIGNED NOT NULL,
    version_number INT UNSIGNED NOT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'draft',
    published_by BIGINT UNSIGNED NULL,
    published_at DATETIME NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_template_version (project_template_id, version_number),
    CONSTRAINT fk_template_versions_template FOREIGN KEY (project_template_id) REFERENCES project_templates(id) ON DELETE RESTRICT,
    CONSTRAINT fk_template_versions_publisher FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_template_versions_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE project_templates
    ADD CONSTRAINT fk_project_templates_active_version FOREIGN KEY (active_version_id) REFERENCES project_template_versions(id) ON DELETE SET NULL;

CREATE TABLE template_wbs_nodes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_template_version_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    node_type VARCHAR(40) NOT NULL DEFAULT 'work_package',
    level_number SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    weight_percentage DECIMAL(9,6) NOT NULL DEFAULT 0.000000,
    UNIQUE KEY uq_template_wbs_code (project_template_version_id, code),
    CONSTRAINT fk_template_wbs_version FOREIGN KEY (project_template_version_id) REFERENCES project_template_versions(id) ON DELETE CASCADE,
    CONSTRAINT fk_template_wbs_parent FOREIGN KEY (parent_id) REFERENCES template_wbs_nodes(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE template_activities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_template_version_id BIGINT UNSIGNED NOT NULL,
    template_wbs_node_id BIGINT UNSIGNED NOT NULL,
    activity_code VARCHAR(80) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    activity_type VARCHAR(30) NOT NULL DEFAULT 'activity',
    duration_days DECIMAL(10,2) NOT NULL DEFAULT 1.00,
    progress_weight DECIMAL(12,6) NOT NULL DEFAULT 1.000000,
    sort_order INT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_template_activity_code (project_template_version_id, activity_code),
    CONSTRAINT fk_template_activities_version FOREIGN KEY (project_template_version_id) REFERENCES project_template_versions(id) ON DELETE CASCADE,
    CONSTRAINT fk_template_activities_wbs FOREIGN KEY (template_wbs_node_id) REFERENCES template_wbs_nodes(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE template_activity_dependencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_template_version_id BIGINT UNSIGNED NOT NULL,
    predecessor_template_activity_id BIGINT UNSIGNED NOT NULL,
    successor_template_activity_id BIGINT UNSIGNED NOT NULL,
    dependency_type CHAR(2) NOT NULL DEFAULT 'FS',
    lag_days DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    UNIQUE KEY uq_template_dependency (predecessor_template_activity_id, successor_template_activity_id),
    CONSTRAINT fk_template_dependencies_version FOREIGN KEY (project_template_version_id) REFERENCES project_template_versions(id) ON DELETE CASCADE,
    CONSTRAINT fk_template_dependencies_predecessor FOREIGN KEY (predecessor_template_activity_id) REFERENCES template_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_template_dependencies_successor FOREIGN KEY (successor_template_activity_id) REFERENCES template_activities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions (module_code, code, name_ar) VALUES
('planning', 'planning.view', 'عرض خطة المشروع والجدول'),
('planning', 'planning.manage_wbs', 'إدارة هيكل تقسيم العمل'),
('planning', 'planning.manage_schedule', 'إدارة الأنشطة والعلاقات'),
('planning', 'planning.update_progress', 'تحديث تقدم الأنشطة'),
('planning', 'planning.calculate', 'إعادة احتساب الجدول والإنجاز'),
('planning', 'planning.baselines.create', 'إنشاء نسخة Baseline'),
('planning', 'planning.baselines.approve', 'اعتماد وتفعيل Baseline'),
('planning', 'planning.templates.manage', 'إدارة قوالب المشاريع');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.code = 'administrator' AND r.scope_type = 'global' AND p.module_code = 'planning';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.module_code = 'planning'
WHERE r.code = 'general_manager' AND r.scope_type = 'global';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN (
    'planning.view', 'planning.manage_wbs', 'planning.manage_schedule', 'planning.update_progress',
    'planning.calculate', 'planning.baselines.create', 'planning.templates.manage'
) WHERE r.code = 'project_manager' AND r.scope_type = 'project';

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN ('planning.view', 'planning.update_progress')
WHERE r.code = 'project_member' AND r.scope_type = 'project';

INSERT INTO work_calendars
    (public_id, project_id, code, name_ar, name_en, timezone, hours_per_day, is_default, status)
VALUES
    ('01SAUDICALENDAR00000000000', NULL, 'SA-5D', 'تقويم العمل السعودي', 'Saudi 5-Day Calendar', 'Asia/Riyadh', 8.00, 1, 'active');

SET @default_calendar_id = LAST_INSERT_ID();

INSERT INTO work_calendar_weekdays (work_calendar_id, weekday_number, is_working, working_minutes) VALUES
(@default_calendar_id, 1, 1, 480),
(@default_calendar_id, 2, 1, 480),
(@default_calendar_id, 3, 1, 480),
(@default_calendar_id, 4, 1, 480),
(@default_calendar_id, 5, 0, 0),
(@default_calendar_id, 6, 0, 0),
(@default_calendar_id, 7, 1, 480);

INSERT INTO project_schedule_settings (project_id, default_calendar_id, data_date)
SELECT p.id, @default_calendar_id, CURRENT_DATE FROM projects p
ON DUPLICATE KEY UPDATE default_calendar_id = COALESCE(project_schedule_settings.default_calendar_id, VALUES(default_calendar_id));

INSERT INTO project_templates
    (public_id, code, name_ar, name_en, project_type_id, description, status)
VALUES
    ('01CONSTRUCTIONTPL000000000', 'construction_standard', 'قالب مشروع إنشائي قياسي', 'Standard Construction Project',
     (SELECT id FROM project_types WHERE code = 'construction' LIMIT 1), 'هيكل بداية قابل للتخصيص للمشاريع الإنشائية.', 'active');

SET @construction_template_id = LAST_INSERT_ID();

INSERT INTO project_template_versions
    (project_template_id, version_number, status, published_at)
VALUES
    (@construction_template_id, 1, 'published', UTC_TIMESTAMP());

SET @construction_template_version_id = LAST_INSERT_ID();

UPDATE project_templates SET active_version_id = @construction_template_version_id
WHERE id = @construction_template_id;

INSERT INTO template_wbs_nodes
    (project_template_version_id, code, name_ar, node_type, level_number, sort_order, weight_percentage)
VALUES
(@construction_template_version_id, '1', 'التجهيز وبدء المشروع', 'phase', 1, 10, 10.000000),
(@construction_template_version_id, '2', 'التصميم والاعتمادات', 'phase', 1, 20, 15.000000),
(@construction_template_version_id, '3', 'المشتريات والتوريد', 'phase', 1, 30, 20.000000),
(@construction_template_version_id, '4', 'التنفيذ', 'phase', 1, 40, 45.000000),
(@construction_template_version_id, '5', 'الاختبارات والتسليم', 'phase', 1, 50, 10.000000);

INSERT INTO template_activities
    (project_template_version_id, template_wbs_node_id, activity_code, name_ar, activity_type, duration_days, progress_weight, sort_order)
VALUES
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '1'), 'A-001', 'أمر المباشرة', 'milestone', 0.00, 1.000000, 10),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '1'), 'A-010', 'التجهيز بالموقع', 'activity', 10.00, 1.000000, 20),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '2'), 'A-020', 'مراجعة التصميم والمخططات', 'activity', 20.00, 4.000000, 30),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '2'), 'A-030', 'اعتماد التصميم', 'milestone', 0.00, 1.000000, 40),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '3'), 'A-040', 'توريد المواد طويلة الأجل', 'activity', 30.00, 1.000000, 50),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '4'), 'A-050', 'تنفيذ الأعمال بالموقع', 'activity', 90.00, 1.000000, 60),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '5'), 'A-060', 'الاختبارات والتشغيل', 'activity', 10.00, 4.000000, 70),
(@construction_template_version_id, (SELECT id FROM template_wbs_nodes WHERE project_template_version_id = @construction_template_version_id AND code = '5'), 'A-070', 'التسليم الابتدائي', 'milestone', 0.00, 1.000000, 80);

INSERT INTO template_activity_dependencies
    (project_template_version_id, predecessor_template_activity_id, successor_template_activity_id, dependency_type, lag_days)
VALUES
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-001'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-010'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-001'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-020'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-020'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-030'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-030'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-040'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-010'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-050'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-040'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-050'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-050'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-060'), 'FS', 0.00),
(@construction_template_version_id, (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-060'), (SELECT id FROM template_activities WHERE project_template_version_id = @construction_template_version_id AND activity_code = 'A-070'), 'FS', 0.00);

UPDATE modules SET version = '0.2.0', is_enabled = 1 WHERE code = 'planning';
UPDATE modules SET version = '0.2.0' WHERE code IN ('core', 'projects');