<?php
if ( ! defined( 'ABSPATH' ) ) exit;

class EMPR_Activator {

    public static function run() {
        self::create_tables();
        EMPR_Roles::create_roles();
        self::seed_default_settings();
        self::create_pages();
        self::ensure_job_sheet_term();
        update_option( 'empr_version', EMPR_VERSION );
        update_option( 'empr_activated_at', current_time( 'mysql' ) );
        add_option( 'empr_wizard_completed', '0' );
        flush_rewrite_rules();
    }

    /**
     * Creates the "EMP Job Sheet" term in the emr_attachment_type taxonomy.
     * Safe to call repeatedly — only inserts if the term is absent.
     */
    private static function ensure_job_sheet_term() {
        register_taxonomy( 'empr_attachment_type', 'attachment', array(
            'public'   => false,
            'show_ui'  => false,
        ) );
        if ( ! term_exists( 'emp-job-sheet', 'empr_attachment_type' ) ) {
            wp_insert_term(
                __( 'EMP Job Sheet', 'employee-roster' ),
                'empr_attachment_type',
                array( 'slug' => 'emp-job-sheet' )
            );
        }
    }

    private static function create_tables() {
        global $wpdb;
        $c = $wpdb->get_charset_collate();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_shifts (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            title           VARCHAR(200)    NOT NULL DEFAULT '',
            start_datetime  DATETIME        NOT NULL,
            end_datetime    DATETIME        NOT NULL,
            location        VARCHAR(200)    NOT NULL DEFAULT '',
            department      VARCHAR(120)    NOT NULL DEFAULT '',
            assignment_type VARCHAR(20)     NOT NULL DEFAULT 'direct',
            break_minutes   SMALLINT UNSIGNED NOT NULL DEFAULT 30,
            color           VARCHAR(20)     NOT NULL DEFAULT '#2563EB',
            slot_limit      SMALLINT UNSIGNED DEFAULT NULL,
            notes           TEXT            DEFAULT NULL,
            attachment_id   BIGINT UNSIGNED DEFAULT NULL,
            status          VARCHAR(20)     NOT NULL DEFAULT 'published',
            created_by      BIGINT UNSIGNED NOT NULL DEFAULT 0,
            created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY     (id),
            KEY             idx_start (start_datetime),
            KEY             idx_status (status)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_assignments (
            id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            shift_id    BIGINT UNSIGNED NOT NULL,
            user_id     BIGINT UNSIGNED NOT NULL,
            status      VARCHAR(20)     NOT NULL DEFAULT 'confirmed',
            assigned_by BIGINT UNSIGNED NOT NULL DEFAULT 0,
            assigned_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            reviewed_by BIGINT UNSIGNED DEFAULT NULL,
            reviewed_at DATETIME        DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY  uq_shift_user (shift_id, user_id),
            KEY         idx_user  (user_id),
            KEY         idx_shift (shift_id),
            KEY         idx_status (status)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_leave (
            id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id      BIGINT UNSIGNED NOT NULL,
            leave_type   VARCHAR(20)     NOT NULL DEFAULT 'annual',
            start_date   DATE            NOT NULL,
            end_date     DATE            NOT NULL,
            reason       TEXT            DEFAULT NULL,
            status       VARCHAR(20)     NOT NULL DEFAULT 'pending',
            reviewed_by  BIGINT UNSIGNED DEFAULT NULL,
            reviewed_at  DATETIME        DEFAULT NULL,
            created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY          idx_user   (user_id),
            KEY          idx_dates  (start_date, end_date),
            KEY          idx_status (status)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_attendance (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id        BIGINT UNSIGNED NOT NULL,
            shift_id       BIGINT UNSIGNED DEFAULT NULL,
            clock_in       DATETIME        DEFAULT NULL,
            clock_out      DATETIME        DEFAULT NULL,
            break_minutes  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            notes          TEXT            DEFAULT NULL,
            status         VARCHAR(20)     NOT NULL DEFAULT 'active',
            created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY    (id),
            KEY            idx_user     (user_id),
            KEY            idx_shift    (shift_id),
            KEY            idx_clock_in (clock_in)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_swap_requests (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            requester_id        BIGINT UNSIGNED NOT NULL,
            requester_shift_id  BIGINT UNSIGNED NOT NULL,
            target_user_id      BIGINT UNSIGNED DEFAULT NULL,
            reason              TEXT            DEFAULT NULL,
            status              VARCHAR(20)     NOT NULL DEFAULT 'pending',
            reviewed_by         BIGINT UNSIGNED DEFAULT NULL,
            reviewed_at         DATETIME        DEFAULT NULL,
            created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY         (id),
            KEY                 idx_requester (requester_id),
            KEY                 idx_status    (status)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_availability (
            id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id        BIGINT UNSIGNED NOT NULL,
            day_of_week    TINYINT UNSIGNED NOT NULL,
            start_time     TIME            NOT NULL,
            end_time       TIME            NOT NULL,
            is_available   TINYINT(1)      NOT NULL DEFAULT 1,
            created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY    (id),
            KEY            idx_user_day (user_id, day_of_week)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_leave_balances (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id             BIGINT UNSIGNED NOT NULL,
            leave_type          VARCHAR(20)     NOT NULL,
            year                SMALLINT UNSIGNED NOT NULL,
            accrued             DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
            used                DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
            adjustment          DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
            last_accrual_date   DATE            DEFAULT NULL,
            created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY         (id),
            UNIQUE KEY          uq_user_type_year (user_id, leave_type, year),
            KEY                 idx_user (user_id)
        ) $c;" );

        dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_login_logs (
            id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id          BIGINT UNSIGNED DEFAULT NULL,
            username_attempt VARCHAR(60)     DEFAULT NULL,
            event_type       VARCHAR(10)     NOT NULL DEFAULT 'login',
            ip_address       VARCHAR(45)     DEFAULT NULL,
            user_agent       VARCHAR(500)    DEFAULT NULL,
            login_at         DATETIME        NOT NULL,
            PRIMARY KEY      (id),
            KEY              idx_user (user_id),
            KEY              idx_login_at (login_at),
            KEY              idx_event (event_type)
        ) $c;" );
    }

    public static function maybe_upgrade() {
        // ── v2.3.0: migrate emr_* option keys → empr_* (4-char prefix) ──────
        if ( false === get_option( 'empr_version' ) && false !== get_option( 'emr_version' ) ) {
            $option_map = array(
                'emr_version'                        => 'empr_version',
                'emr_activated_at'                   => 'empr_activated_at',
                'emr_wizard_completed'               => 'empr_wizard_completed',
                'emr_company_name'                   => 'empr_company_name',
                'emr_departments'                    => 'empr_departments',
                'emr_locations'                      => 'empr_locations',
                'emr_week_start'                     => 'empr_week_start',
                'emr_primary_color'                  => 'empr_primary_color',
                'emr_allow_self_clock'               => 'empr_allow_self_clock',
                'emr_leave_enabled'                  => 'empr_leave_enabled',
                'emr_manager_settings_access'        => 'empr_manager_settings_access',
                'emr_notifications_enabled'          => 'empr_notifications_enabled',
                'emr_profile_mode_enabled'           => 'empr_profile_mode_enabled',
                'emr_job_sheet_enabled'              => 'empr_job_sheet_enabled',
                'emr_job_sheet_auto_delete'          => 'empr_job_sheet_auto_delete',
                'emr_payroll_enabled'                => 'empr_payroll_enabled',
                'emr_tax_enabled'                    => 'empr_tax_enabled',
                'emr_tax_rate'                       => 'empr_tax_rate',
                'emr_daily_budget_enabled'           => 'empr_daily_budget_enabled',
                'emr_daily_budget'                   => 'empr_daily_budget',
                'emr_supervisor_see_pay_rate'        => 'empr_supervisor_see_pay_rate',
                'emr_leave_balance_enabled'          => 'empr_leave_balance_enabled',
                'emr_public_roster_shortcode'        => 'empr_public_roster_shortcode',
                'emr_penalty_rate_enabled'           => 'empr_penalty_rate_enabled',
                'emr_saturday_rate'                  => 'empr_saturday_rate',
                'emr_sunday_rate'                    => 'empr_sunday_rate',
                'emr_annual_leave_ft'                => 'empr_annual_leave_ft',
                'emr_annual_leave_pt'                => 'empr_annual_leave_pt',
                'emr_sick_leave_ft'                  => 'empr_sick_leave_ft',
                'emr_sick_leave_pt'                  => 'empr_sick_leave_pt',
                'emr_hours_fulltime'                 => 'empr_hours_fulltime',
                'emr_hours_parttime'                 => 'empr_hours_parttime',
                'emr_admin_include_self'             => 'empr_admin_include_self',
                'emr_admin_include_all'              => 'empr_admin_include_all',
                'emr_page_dashboard'                 => 'empr_page_dashboard',
                'emr_page_roster'                    => 'empr_page_roster',
                'emr_page_leave'                     => 'empr_page_leave',
                'emr_page_attendance'                => 'empr_page_attendance',
                'emr_page_availability'              => 'empr_page_availability',
                'emr_trial_expired_notice_dismissed' => 'empr_trial_expired_notice_dismissed',
            );
            foreach ( $option_map as $old_key => $new_key ) {
                $val = get_option( $old_key );
                if ( false !== $val ) {
                    update_option( $new_key, $val );
                    delete_option( $old_key );
                }
            }
            global $wpdb;
            $user_meta_map = array(
                'emr_deactivated'           => 'empr_deactivated',
                'emr_pay_rate'              => 'empr_pay_rate',
                'emr_job_type'              => 'empr_job_type',
                'emr_skills'                => 'empr_skills',
                'emr_clockin_lockout_until' => 'empr_clockin_lockout_until',
            );
            foreach ( $user_meta_map as $old_meta => $new_meta ) {
                $wpdb->query(
                    $wpdb->prepare(
                        "UPDATE {$wpdb->usermeta} SET meta_key = %s WHERE meta_key = %s",
                        $new_meta,
                        $old_meta
                    )
                );
            }
            update_option( 'empr_version', '2.3.0' );
        }

        // One-time rescue for empr_manager_settings_access which was missing
        // from the original v2.3.0 migration. Runs on every admin_init until
        // the old key is gone; the condition is false once deleted.
        if ( get_option( 'emr_manager_settings_access' ) !== false &&
             get_option( 'empr_manager_settings_access' ) === false ) {
            update_option(
                'empr_manager_settings_access',
                get_option( 'emr_manager_settings_access' )
            );
            delete_option( 'emr_manager_settings_access' );
        }

        $installed = get_option( 'empr_version', '0.0.0' );
        global $wpdb;

        // ── v2.4.2: add login_logs table ─────────────────────────────────────
        // Must run before the old-migration early-return so existing installs
        // on 2.4.1+ get the table even though they skip the sequential blocks below.
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        if ( version_compare( $installed, '2.4.2', '<' ) ) {
            $tables = $wpdb->get_col( "SHOW TABLES LIKE '{$wpdb->prefix}emr_login_logs'" );
            if ( empty( $tables ) ) {
                require_once ABSPATH . 'wp-admin/includes/upgrade.php';
                $c2 = $wpdb->get_charset_collate();
                dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_login_logs (
                    id               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                    user_id          BIGINT UNSIGNED DEFAULT NULL,
                    username_attempt VARCHAR(60)     DEFAULT NULL,
                    event_type       VARCHAR(10)     NOT NULL DEFAULT 'login',
                    ip_address       VARCHAR(45)     DEFAULT NULL,
                    user_agent       VARCHAR(500)    DEFAULT NULL,
                    login_at         DATETIME        NOT NULL,
                    PRIMARY KEY      (id),
                    KEY              idx_user (user_id),
                    KEY              idx_login_at (login_at),
                    KEY              idx_event (event_type)
                ) $c2;" );
            }
            update_option( 'empr_version', '2.4.2' );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v2.4.6: drop logout_at column from login_logs ────────────────────
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        if ( version_compare( $installed, '2.4.6', '<' ) ) {
            $tables = $wpdb->get_col( "SHOW TABLES LIKE '{$wpdb->prefix}emr_login_logs'" );
            if ( ! empty( $tables ) ) {
                $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_login_logs" );
                if ( in_array( 'logout_at', $cols, true ) ) {
                    $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_login_logs DROP COLUMN logout_at" );
                }
            }
            update_option( 'empr_version', '2.4.6' );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v2.4.8: migrate empr_admin_include_self global option → per-admin user meta ──
        if ( version_compare( $installed, '2.4.8', '<' ) ) {
            $old_self = get_option( 'empr_admin_include_self', '0' );
            if ( $old_self === '1' ) {
                // The original admin who enabled this can't be determined — set for all admins
                // so no one loses their roster visibility on upgrade.
                $admins = get_users( array( 'role' => 'administrator', 'fields' => 'ID', 'number' => -1 ) );
                foreach ( $admins as $uid ) {
                    update_user_meta( (int) $uid, 'empr_admin_include_self', '1' );
                }
            }
            delete_option( 'empr_admin_include_self' );
            update_option( 'empr_version', '2.4.8' );
        }

        // ── v2.5: add reviewed_by / reviewed_at to assignments for request history ──
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW COLUMNS, ALTER TABLE) on an internal
        // plugin table. The table name is a hardcoded {$wpdb->prefix}emr_*
        // constant, never user input. prepare() cannot be used for DDL statements.
        if ( version_compare( $installed, '2.5', '<' ) ) {
            $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_assignments" );
            if ( ! in_array( 'reviewed_by', $cols, true ) ) {
                $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_assignments ADD COLUMN reviewed_by BIGINT UNSIGNED DEFAULT NULL AFTER assigned_at" );
            }
            if ( ! in_array( 'reviewed_at', $cols, true ) ) {
                $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_assignments ADD COLUMN reviewed_at DATETIME DEFAULT NULL AFTER reviewed_by" );
            }
            update_option( 'empr_version', '2.5' );
        }
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v2.5.12: migrate plugin page shortcodes emp_roster_* → empr_* ──────────
        // Legacy emp_roster_* SPA shortcodes were removed; existing plugin pages still
        // hold them in post_content and would render blank. Rewrite the five plugin
        // pages' stored content to the canonical empr_* names.
        if ( ! get_option( 'empr_shortcode_migration_done' ) ) {
            if ( is_admin() ) {
                $shortcode_page_opts = array(
                    'empr_page_dashboard', 'empr_page_roster', 'empr_page_leave',
                    'empr_page_attendance', 'empr_page_availability',
                );
                $sc_replacements = array(
                    'emp_roster_dashboard'    => 'empr_dashboard',
                    'emp_roster_shifts'       => 'empr_shifts',
                    'emp_roster_leave'        => 'empr_leave',
                    'emp_roster_attendance'   => 'empr_attendance',
                    'emp_roster_availability' => 'empr_availability',
                );
                foreach ( $shortcode_page_opts as $opt ) {
                    $page_id = (int) get_option( $opt );
                    if ( ! $page_id ) continue;
                    $page = get_post( $page_id );
                    if ( ! $page ) continue;
                    $new_content = str_replace(
                        array_keys( $sc_replacements ),
                        array_values( $sc_replacements ),
                        $page->post_content
                    );
                    if ( $new_content !== $page->post_content ) {
                        wp_update_post( array(
                            'ID'           => $page_id,
                            'post_content' => $new_content,
                        ) );
                    }
                }
                update_option( 'empr_shortcode_migration_done', '1' );
            }
        }

        // ── Retire the legacy Leave / Clock In/Out pages ──────────────────────
        // These two pages were created by earlier versions and hold [empr_leave] /
        // [empr_attendance]. This build registers neither shortcode, so WordPress
        // would print them as literal text inside a styled-but-empty SPA shell.
        // Drafting the page makes it 404, which makes is_page() false, which stops
        // EMPR_Public::maybe_assets() enqueueing the SPA onto it.
        //
        // Guarded by a one-shot option rather than version_compare(): $installed is
        // captured above and later blocks bump empr_version behind it, so a version
        // guard here would compare a stale value.
        if ( ! get_option( 'empr_legacy_pages_migration_done' ) ) {
            if ( is_admin() ) {
                self::retire_legacy_pages();
                update_option( 'empr_legacy_pages_migration_done', '1' );
            }
        }

        // One-time dismissible notice naming whatever was drafted. The migration
        // cannot see customization that lives outside post_content (a renamed title,
        // a nav-menu entry), so the user is always told what changed and how to undo it.
        if ( get_option( 'empr_legacy_pages_drafted' ) ) {
            $empr_dismiss_nonce = isset( $_GET['_wpnonce'] ) ? sanitize_key( wp_unslash( $_GET['_wpnonce'] ) ) : '';
            if ( isset( $_GET['empr_dismiss_legacy_pages'] )
                 && wp_verify_nonce( $empr_dismiss_nonce, 'empr_dismiss_legacy_pages' ) ) {
                delete_option( 'empr_legacy_pages_drafted' );
            } else {
                add_action( 'admin_notices', array( __CLASS__, 'legacy_pages_notice' ) );
            }
        }

        if ( version_compare( $installed, '1.6.5', '>=' ) ) return;
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $c = $wpdb->get_charset_collate();
        // ── v1.1.0: assignment_type / break_minutes / max_staff / idx_status ─
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        // Add assignment_type and break_minutes to shifts if missing
        $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_shifts" );
        if ( ! in_array( 'assignment_type', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts ADD COLUMN assignment_type VARCHAR(20) NOT NULL DEFAULT 'direct' AFTER department" );
        }
        if ( ! in_array( 'break_minutes', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts ADD COLUMN break_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 30 AFTER assignment_type" );
        }
        if ( in_array( 'max_staff', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts DROP COLUMN max_staff" );
        }
        // Add status index to assignments if missing
        $idx = $wpdb->get_results( "SHOW INDEX FROM {$wpdb->prefix}emr_assignments WHERE Key_name = 'idx_status'" );
        if ( empty( $idx ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_assignments ADD KEY idx_status (status)" );
        }
        update_option( 'empr_version', '1.1.0' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v1.2.0: rename role_label → department column ─
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_shifts" );
        if ( in_array( 'role_label', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts CHANGE COLUMN role_label department VARCHAR(120) NOT NULL DEFAULT ''" );
        }
        update_option( 'empr_version', '1.2.0' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v1.3.0: add swap_requests table ──────────────────────────────────
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        $tables = $wpdb->get_col( "SHOW TABLES LIKE '{$wpdb->prefix}emr_swap_requests'" );
        if ( empty( $tables ) ) {
            $c = $wpdb->get_charset_collate();
            dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_swap_requests (
                id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                requester_id        BIGINT UNSIGNED NOT NULL,
                requester_shift_id  BIGINT UNSIGNED NOT NULL,
                target_user_id      BIGINT UNSIGNED DEFAULT NULL,
                reason              TEXT            DEFAULT NULL,
                status              VARCHAR(20)     NOT NULL DEFAULT 'pending',
                reviewed_by         BIGINT UNSIGNED DEFAULT NULL,
                reviewed_at         DATETIME        DEFAULT NULL,
                created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY         (id),
                KEY                 idx_requester (requester_id),
                KEY                 idx_status    (status)
            ) $c;" );
        }
        update_option( 'empr_version', '1.3.0' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v1.4.0: add slot_limit to shifts ─────────────────────────────────
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_shifts" );
        if ( ! in_array( 'slot_limit', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts ADD COLUMN slot_limit SMALLINT UNSIGNED DEFAULT NULL AFTER color" );
        }
        update_option( 'empr_version', '1.4.0' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v1.5.0: leave balances table ─────────────────────────────────────
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        $tables = $wpdb->get_col( "SHOW TABLES LIKE '{$wpdb->prefix}emr_leave_balances'" );
        if ( empty( $tables ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $c = $wpdb->get_charset_collate();
            dbDelta( "CREATE TABLE IF NOT EXISTS {$wpdb->prefix}emr_leave_balances (
                id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id             BIGINT UNSIGNED NOT NULL,
                leave_type          VARCHAR(20)     NOT NULL,
                year                SMALLINT UNSIGNED NOT NULL,
                accrued             DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
                used                DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
                adjustment          DECIMAL(8,2)    NOT NULL DEFAULT '0.00',
                last_accrual_date   DATE            DEFAULT NULL,
                created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY         (id),
                UNIQUE KEY          uq_user_type_year (user_id, leave_type, year),
                KEY                 idx_user (user_id)
            ) $c;" );
        }
        update_option( 'empr_version', '1.5.0' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange

        // ── v1.6.0: supervisor role + rename role display names ───────────────
        EMPR_Roles::create_roles();
        update_option( 'empr_version', '1.6.0' );

        // ── v1.6.4: rename frontend page slugs emp-roster-* → employee-roster-* ──
        $slug_map = array(
            'emr_page_dashboard'    => array( 'emp-roster-dashboard',    'employee-roster-dashboard' ),
            'emr_page_roster'       => array( 'emp-roster-shifts',       'employee-roster-shifts' ),
            'emr_page_leave'        => array( 'emp-roster-leave',        'employee-roster-leave' ),
            'emr_page_attendance'   => array( 'emp-roster-attendance',   'employee-roster-attendance' ),
            'emr_page_availability' => array( 'emp-roster-availability', 'employee-roster-availability' ),
        );
        foreach ( $slug_map as $option => $slugs ) {
            $old_page = get_page_by_path( $slugs[0] );
            if ( $old_page ) {
                wp_update_post( array( 'ID' => $old_page->ID, 'post_name' => $slugs[1] ) );
                update_option( $option, $old_page->ID );
            }
        }
        flush_rewrite_rules();
        update_option( 'empr_version', '1.6.4' );

        // ── v1.6.5: add attachment_id column to shifts; ensure job sheet term ──
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        // Reason: Schema operations (SHOW TABLES, SHOW COLUMNS,
        // ALTER TABLE, CREATE TABLE) on internal plugin tables.
        // Table names are hardcoded {$wpdb->prefix}emr_* constants,
        // never user input. prepare() cannot be used for DDL statements.
        $cols = $wpdb->get_col( "SHOW COLUMNS FROM {$wpdb->prefix}emr_shifts" );
        if ( ! in_array( 'attachment_id', $cols, true ) ) {
            $wpdb->query( "ALTER TABLE {$wpdb->prefix}emr_shifts ADD COLUMN attachment_id BIGINT UNSIGNED DEFAULT NULL AFTER notes" );
        }
        self::ensure_job_sheet_term();
        update_option( 'empr_version', '1.6.5' );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
    }

    private static function seed_default_settings() {
        $defaults = array(
            'empr_company_name'             => get_bloginfo( 'name' ),
            'empr_departments'              => "General Department",
            'empr_locations'                => wp_json_encode( array( array( 'name' => 'Main Location', 'color' => '#2563EB' ) ) ),
            'empr_week_start'               => '1',
            'empr_primary_color'            => '#2563EB',
            'empr_allow_self_clock'         => '1',
            'empr_notifications_enabled'    => '0',
            'empr_profile_mode_enabled'     => '0',
            'empr_job_sheet_enabled'        => '0',
            'empr_job_sheet_auto_delete'    => 'never',
        );
        foreach ( $defaults as $key => $val ) {
            if ( false === get_option( $key ) ) {
                add_option( $key, $val );
            }
        }
    }

    /**
     * Drafts the legacy Leave / Clock In/Out pages when they still hold nothing but
     * the bare shortcode, and drops their now-orphaned page-ID options.
     *
     * A page whose content the user has changed is left completely alone — not
     * drafted, and its option is kept, so is_roster_page() still enqueues the SPA
     * there and whatever they built keeps working.
     */
    private static function retire_legacy_pages() {
        $targets = array(
            'empr_page_leave'      => '[empr_leave]',
            'empr_page_attendance' => '[empr_attendance]',
        );

        $drafted = array();

        foreach ( $targets as $option => $shortcode ) {
            $page_id = (int) get_option( $option );
            if ( ! $page_id ) {
                continue; // nothing recorded — nothing to retire
            }

            $page = get_post( $page_id );
            if ( ! $page ) {
                delete_option( $option ); // option outlived its page
                continue;
            }

            // Untouched pages only. Anything else is the user's content now.
            if ( self::normalize_shortcode_content( $page->post_content ) !== $shortcode ) {
                continue;
            }

            if ( 'draft' !== $page->post_status ) {
                wp_update_post( array(
                    'ID'          => $page_id,
                    'post_status' => 'draft',
                ) );
                $drafted[] = $page->post_title;
            }

            delete_option( $option );
        }

        if ( ! empty( $drafted ) ) {
            update_option( 'empr_legacy_pages_drafted', implode( ', ', $drafted ) );
        }
    }

    /**
     * Reduces stored page content to its bare shortcode for comparison.
     *
     * The block editor rewrites a bare shortcode to
     *   <!-- wp:shortcode -->[empr_leave]<!-- /wp:shortcode -->
     * on the first Update, even when the user changed nothing. Comparing the literal
     * string would treat that as customization and skip the page.
     */
    private static function normalize_shortcode_content( $content ) {
        $content = trim( $content );
        $content = preg_replace( '#^<!--\s*wp:shortcode\s*-->#', '', $content );
        $content = preg_replace( '#<!--\s*/wp:shortcode\s*-->$#', '', $content );
        return trim( $content );
    }

    /** One-time notice naming the pages that were drafted, with a dismiss link. */
    public static function legacy_pages_notice() {
        $names = get_option( 'empr_legacy_pages_drafted' );
        if ( ! $names ) {
            return;
        }

        $dismiss_url = wp_nonce_url(
            add_query_arg( 'empr_dismiss_legacy_pages', '1' ),
            'empr_dismiss_legacy_pages'
        );

        echo '<div class="notice notice-info"><p><strong>Employee Roster:</strong> ';
        printf(
            /* translators: %s = comma-separated list of page titles */
            esc_html__( 'The following page(s) were unpublished because the features they hosted are not part of this version: %s.', 'employee-roster' ),
            '<em>' . esc_html( $names ) . '</em>'
        );
        echo ' ';
        printf(
            /* translators: %s = URL of the Pages admin screen */
            wp_kses(
                __( 'They were moved to <strong>Draft</strong>, not deleted — you can restore them at any time from <a href="%s">Pages</a>.', 'employee-roster' ),
                array( 'strong' => array(), 'a' => array( 'href' => array() ) )
            ),
            esc_url( admin_url( 'edit.php?post_type=page&post_status=draft' ) )
        );
        echo ' <a href="' . esc_url( $dismiss_url ) . '">' . esc_html__( 'Dismiss', 'employee-roster' ) . '</a>';
        echo '</p></div>';
    }
    private static function create_pages() {
        $pages = array(
            'empr_page_dashboard'    => array( 'EmpRoster – Dashboard',    'employee-roster-dashboard',    '[empr_dashboard]' ),
            'empr_page_roster'       => array( 'EmpRoster – My Shifts',    'employee-roster-shifts',       '[empr_shifts]' ),
            'empr_page_availability' => array( 'EmpRoster – Availability', 'employee-roster-availability', '[empr_availability]' ),
        );
        foreach ( $pages as $option => $page ) {
            if ( get_option( $option ) ) continue;
            $q = new WP_Query( array(
                'post_type'      => 'page',
                'post_status'    => array( 'publish', 'draft' ),
                'name'           => $page[1],
                'posts_per_page' => 1,
                'no_found_rows'  => true,
            ) );
            if ( $q->have_posts() ) {
                $found = $q->posts[0];
                update_option( $option, $found->ID );
                wp_reset_postdata();
                // Re-publish a page we are re-adopting from draft. We only get here
                // when the page-ID option is ABSENT, so this cannot override a user
                // who deliberately drafted one of these pages — their option is still
                // set and the `continue` above already skipped them. The reachable
                // case is an orphaned page: notably the retire_legacy_pages()
                // migration, which drafts the Leave / Clock In/Out pages and deletes
                // their options. Without this, a later build that restores those
                // features would leave their pages permanently drafted and 404ing,
                // silently.
                if ( 'draft' === $found->post_status ) {
                    wp_update_post( array( 'ID' => $found->ID, 'post_status' => 'publish' ) );
                }
                continue;
            }
            $id = wp_insert_post( array(
                'post_title'   => $page[0],
                'post_name'    => $page[1],
                'post_content' => $page[2],
                'post_status'  => 'publish',
                'post_type'    => 'page',
                'post_author'  => get_current_user_id(),
            ) );
            if ( $id && ! is_wp_error( $id ) ) update_option( $option, $id );
        }
    }
}
