<?php
/**
 * Migration: Add meeting_availability column to contacts table
 * and assigned_contact_id column to meetings table.
 *
 * Enables per-contact availability schedules and priority-based
 * meeting assignment.
 *
 * @package BoostBot
 * @since   1.8.0
 */

// Prevent direct access
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Migration_Add_Meeting_Availability
 *
 * @since 1.8.0
 */
class BoostBot_Migration_Add_Meeting_Availability {

    /**
     * Run the migration.
     *
     * @return bool True on success.
     */
    public static function run() {
        return self::up();
    }

    /**
     * Run the migration.
     *
     * @return bool True on success.
     */
    public static function up() {
        global $wpdb;

        $success = true;

        // ── 1. Add meeting_availability to contacts table ──
        $contacts_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_contacts' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $col_exists = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SHOW COLUMNS FROM {$contacts_table} LIKE 'meeting_availability'"
        );

        if ( empty( $col_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $result = $wpdb->query(
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                "ALTER TABLE {$contacts_table} ADD COLUMN meeting_availability longtext DEFAULT NULL AFTER availability"
            );

            if ( false === $result ) {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::error( 'Migration failed: Could not add meeting_availability to contacts', array(
                        'error' => $wpdb->last_error,
                    ) );
                }
                $success = false;
            } else {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::info( 'Migration: Added meeting_availability column to contacts table' );
                }
            }
        }

        // ── 2. Add assigned_contact_id to meetings table ──
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $col_exists_2 = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SHOW COLUMNS FROM {$meetings_table} LIKE 'assigned_contact_id'"
        );

        if ( empty( $col_exists_2 ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $result_2 = $wpdb->query(
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                "ALTER TABLE {$meetings_table} ADD COLUMN assigned_contact_id bigint(20) unsigned DEFAULT NULL AFTER attendee_contacts"
            );

            if ( false === $result_2 ) {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::error( 'Migration failed: Could not add assigned_contact_id to meetings', array(
                        'error' => $wpdb->last_error,
                    ) );
                }
                $success = false;
            } else {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::info( 'Migration: Added assigned_contact_id column to meetings table' );
                }

                // Add index for assigned_contact_id
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                $wpdb->query(
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                    "ALTER TABLE {$meetings_table} ADD KEY idx_assigned_contact_id (assigned_contact_id)"
                );
            }
        }

        return $success;
    }

    /**
     * Rollback the migration.
     *
     * @return bool True on success.
     */
    public static function down() {
        global $wpdb;

        $contacts_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_contacts' );
        $meetings_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_meetings' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $col_exists = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SHOW COLUMNS FROM {$contacts_table} LIKE 'meeting_availability'"
        );

        if ( ! empty( $col_exists ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $wpdb->query(
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                "ALTER TABLE {$contacts_table} DROP COLUMN meeting_availability"
            );
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $col_exists_2 = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SHOW COLUMNS FROM {$meetings_table} LIKE 'assigned_contact_id'"
        );

        if ( ! empty( $col_exists_2 ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
            $wpdb->query(
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
                "ALTER TABLE {$meetings_table} DROP COLUMN assigned_contact_id"
            );
        }

        return true;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}
