<?php
/**
 * Database Migration: Minute-Based Pay-As-You-Go Billing System
 *
 * Migrates database to support dual-quota billing model:
 * - $29/month subscription with 300 included minutes (resets monthly)
 * - $10 per 100 purchased minutes (rolls over indefinitely)
 * - 30-second free threshold (calls <30s don't deduct minutes)
 * - Consumption order: Included minutes first, then purchased minutes
 *
 * @link       https://github.com/sitestaffr/sitestaffr-plugin
 * @since      1.0.0
 *
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes/migrations
 */

/**
 * Database migration class for minute-based billing.
 *
 * This class handles all database schema changes needed to support
 * minute-based billing, quota tracking, and billing transactions.
 *
 * @since      1.0.0
 * @package    SiteStaffr
 * @subpackage SiteStaffr/includes/migrations
 * @author     SiteStaffr
 */
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
class SiteStaffr_Migration_Minute_Based_V1 {

    /**
     * Migration version number.
     *
     * @since    1.0.0
     * @access   private
     * @var      string    $version    Migration version.
     */
    private static $version = '1.0.0';

    /**
     * Run the migration (UP).
     *
     * Creates new tables, adds columns to existing tables,
     * and initializes wp_options for subscription and quota tracking.
     *
     * Safe to run multiple times (idempotent).
     *
     * @since    1.0.0
     * @return   bool    True on success, false on failure.
     */
    public static function up() {
        global $wpdb;

        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Starting minute-based billing migration v' . self::$version );

        // Track if any errors occurred
        $migration_success = true;

        // Step 1: Create billing_transactions table
        if ( ! self::create_billing_transactions_table() ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to create billing_transactions table' );
            $migration_success = false;
        }

        // Step 2: Create ai_metrics table
        if ( ! self::create_ai_metrics_table() ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to create ai_metrics table' );
            $migration_success = false;
        }

        // Step 3: Add new columns to calls table
        if ( ! self::add_calls_table_columns() ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to add columns to calls table' );
            $migration_success = false;
        }

        // Step 4: Initialize subscription settings
        if ( ! self::init_subscription_settings() ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to initialize subscription settings' );
            $migration_success = false;
        }

        // Step 5: Initialize minute balance tracking
        if ( ! self::init_minute_balance() ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to initialize minute balance' );
            $migration_success = false;
        }

        // Step 6: Update database version
        if ( $migration_success ) {
            update_option( 'sitestaffr_db_version', self::$version );
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully completed migration v' . self::$version );
            return true;
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Migration completed with errors' );
            return false;
        }
    }

    /**
     * Rollback the migration (DOWN).
     *
     * Removes tables and columns created by this migration.
     * CAUTION: This will delete billing and metrics data.
     *
     * @since    1.0.0
     * @return   bool    True on success, false on failure.
     */
    public static function down() {
        global $wpdb;

        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Rolling back minute-based billing migration v' . self::$version );

        // Drop billing_transactions table
        $billing_table = $wpdb->prefix . 'phoneease_billing_transactions';
        $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $billing_table ) );
        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Dropped table ' . $billing_table );

        // Drop ai_metrics table
        $metrics_table = $wpdb->prefix . 'phoneease_ai_metrics';
        $wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $metrics_table ) );
        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Dropped table ' . $metrics_table );

        // Remove columns from calls table (if they exist)
        $calls_table  = $wpdb->prefix . 'phoneease_calls';
        $drop_columns = array( 'billable_minutes', 'minute_quota_exceeded', 'deducted_from' );

        foreach ( $drop_columns as $col_name ) {
            $column_exists = $wpdb->get_results(
                $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, $col_name )
            );

            if ( ! empty( $column_exists ) ) {
                $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i DROP COLUMN %i', $calls_table, $col_name ) );
                SiteStaffr_Logger::legacy( "SiteStaffr Migration: Dropped column {$col_name} from calls table" );
            }
        }

        // Remove wp_options entries
        delete_option( 'sitestaffr_subscription' );
        delete_option( 'sitestaffr_minute_balance' );
        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Removed subscription and minute balance options' );

        // Revert database version to previous
        update_option( 'sitestaffr_db_version', '0.9.0' );
        SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Rollback complete' );

        return true;
    }

    /**
     * Create billing_transactions table.
     *
     * Stores all billing transactions (subscriptions, purchases, refunds).
     *
     * @since    1.0.0
     * @access   private
     * @return   bool    True on success, false on failure.
     */
    private static function create_billing_transactions_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_billing_transactions';

        // Check if table already exists
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );

        if ( $table_exists === $table_name ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Table {$table_name} already exists, skipping" );
            return true;
        }

        $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            user_id BIGINT UNSIGNED NOT NULL,
            transaction_type VARCHAR(20) NOT NULL,
            amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
            minutes_added INT NOT NULL DEFAULT 0,
            description TEXT NULL,
            stripe_transaction_id VARCHAR(100) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_id (user_id),
            INDEX idx_transaction_type (transaction_type),
            INDEX idx_created_at (created_at)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );

        // Verify table was created
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );

        if ( $table_exists === $table_name ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Successfully created table {$table_name}" );
            return true;
        } else {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Failed to create table {$table_name} - " . $wpdb->last_error );
            return false;
        }
    }

    /**
     * Create ai_metrics table.
     *
     * Stores token usage and cost metrics for AI operations.
     *
     * @since    1.0.0
     * @access   private
     * @return   bool    True on success, false on failure.
     */
    private static function create_ai_metrics_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'phoneease_ai_metrics';

        // Check if table already exists
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );

        if ( $table_exists === $table_name ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Table {$table_name} already exists, skipping" );
            return true;
        }

        $charset_collate = 'ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        $calls_table = $wpdb->prefix . 'phoneease_calls';

        $sql = "CREATE TABLE {$table_name} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            PRIMARY KEY (id),
            call_id BIGINT UNSIGNED NOT NULL,
            metric_type VARCHAR(20) NOT NULL,
            tokens_input INT UNSIGNED NOT NULL DEFAULT 0,
            tokens_output INT UNSIGNED NOT NULL DEFAULT 0,
            tokens_cached INT UNSIGNED NOT NULL DEFAULT 0,
            cost DECIMAL(10,6) NOT NULL DEFAULT 0.000000,
            model_used VARCHAR(100) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_call_id (call_id),
            INDEX idx_metric_type (metric_type),
            INDEX idx_created_at (created_at)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );

        // Verify table was created
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table_name ) )
        );

        if ( $table_exists === $table_name ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Successfully created table {$table_name}" );
            return true;
        } else {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Failed to create table {$table_name} - " . $wpdb->last_error );
            return false;
        }
    }

    /**
     * Add new columns to calls table.
     *
     * Adds billing-related columns:
     * - billable_minutes: Computed minutes charged (excludes <30s calls)
     * - minute_quota_exceeded: Flag if call blocked due to no minutes
     * - deducted_from: Which quota was used ('included' or 'purchased')
     *
     * @since    1.0.0
     * @access   private
     * @return   bool    True on success, false on failure.
     */
    private static function add_calls_table_columns() {
        global $wpdb;

        $calls_table = $wpdb->prefix . 'phoneease_calls';
        $success = true;

        /*
         * Dynamic table identifiers in this migration path are trusted values
         * based on $wpdb->prefix and fixed column names.
         */
        // Check if table exists
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $calls_table ) )
        );

        if ( $table_exists !== $calls_table ) {
            SiteStaffr_Logger::legacy( "SiteStaffr Migration: Calls table does not exist, cannot add columns" );
            return false;
        }

        // Add billable_minutes column
        $column_exists = $wpdb->get_results(
            $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, 'billable_minutes' )
        );

        if ( empty( $column_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN billable_minutes DECIMAL(10,2) DEFAULT 0.00 AFTER call_duration', $calls_table )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to add billable_minutes column - ' . $wpdb->last_error );
                $success = false;
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully added billable_minutes column' );
            }
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: billable_minutes column already exists, skipping' );
        }

        // Add minute_quota_exceeded column
        $column_exists = $wpdb->get_results(
            $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, 'minute_quota_exceeded' )
        );

        if ( empty( $column_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN minute_quota_exceeded TINYINT(1) DEFAULT 0 AFTER billable_minutes', $calls_table )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to add minute_quota_exceeded column - ' . $wpdb->last_error );
                $success = false;
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully added minute_quota_exceeded column' );
            }
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: minute_quota_exceeded column already exists, skipping' );
        }

        // Add deducted_from column
        $column_exists = $wpdb->get_results(
            $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $calls_table, 'deducted_from' )
        );

        if ( empty( $column_exists ) ) {
            $result = $wpdb->query(
                $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN deducted_from VARCHAR(20) NULL AFTER minute_quota_exceeded', $calls_table )
            );

            if ( $result === false ) {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to add deducted_from column - ' . $wpdb->last_error );
                $success = false;
            } else {
                SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully added deducted_from column' );
            }
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: deducted_from column already exists, skipping' );
        }

        return $success;
    }

    /**
     * Initialize subscription settings in wp_options.
     *
     * Creates default subscription configuration for new installations.
     * For existing installations, preserves current settings.
     *
     * @since    1.0.0
     * @access   private
     * @return   bool    True on success, false on failure.
     */
    private static function init_subscription_settings() {
        // Check if subscription settings already exist
        $existing_subscription = get_option( 'sitestaffr_subscription' );

        if ( $existing_subscription !== false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Subscription settings already exist, skipping initialization' );
            return true;
        }

        // Default subscription settings
        $default_subscription = array(
            'plan'                   => 'starter',
            'status'                 => 'active',
            'monthly_price'          => 29.00,
            'included_minutes'       => 300,
            'billing_cycle_start'    => current_time( 'Y-m-d' ),
            'next_billing_date'      => current_datetime()->modify( '+1 month' )->format( 'Y-m-d' ),
            'stripe_subscription_id' => null,
        );

        $result = add_option( 'sitestaffr_subscription', $default_subscription, '', 'no' );

        if ( $result ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully initialized subscription settings' );
            return true;
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to initialize subscription settings' );
            return false;
        }
    }

    /**
     * Initialize minute balance tracking in wp_options.
     *
     * Creates default minute balance for new installations.
     * For existing installations with calls, calculates appropriate starting balance.
     *
     * @since    1.0.0
     * @access   private
     * @return   bool    True on success, false on failure.
     */
    private static function init_minute_balance() {
        // Check if minute balance already exists
        $existing_balance = get_option( 'sitestaffr_minute_balance' );

        if ( $existing_balance !== false ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Minute balance already exists, skipping initialization' );
            return true;
        }

        // Default minute balance
        $default_balance = array(
            'included_minutes_remaining'       => 300,
            'purchased_minutes_remaining'      => 0,
            'last_reset_date'                  => current_time( 'Y-m-d' ),
            'total_included_used_this_period'  => 0,
            'total_purchased_used_lifetime'    => 0,
        );

        $result = add_option( 'sitestaffr_minute_balance', $default_balance, '', 'no' );

        if ( $result ) {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Successfully initialized minute balance with 300 included minutes' );
            return true;
        } else {
            SiteStaffr_Logger::legacy( 'SiteStaffr Migration: Failed to initialize minute balance' );
            return false;
        }
    }

    /**
     * Get migration version.
     *
     * @since    1.0.0
     * @return   string    Migration version number.
     */
    public static function get_version() {
        return self::$version;
    }
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter
