<?php

namespace Booktics;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

use Booktics\Dummy_Data\Dummy_Data_Manager;

class Installer {
    /**
     * Initializes the Booktics plugin by performing necessary setup tasks.
     *
     * This method performs the following setup operations:
     * - Creates or updates required database tables
     * - Initializes user roles and capabilities
     * - Generates initial onboarding data using dummy data manager
     */
    public static function run(): void {
        self::create_tables();
        ( new Role() )->init();
        ( new Dummy_Data_Manager() )->generate_onboarding_data();
    }

    /**
     * Creates and updates database tables required for the Booktics plugin.
     *
     * Creates the following tables if they don't exist:
     * - booktics_schedules: Stores scheduling information for team members and services
     * - booktics_carts: Stores shopping cart information
     * - booktics_cart_items: Stores individual items in shopping carts
     * - booktics_orders: Stores order information and payment details
     * - booktics_payments: Stores payment transaction records
     *
     * Also handles table structure updates by adding missing columns to existing tables
     * and performing necessary column modifications.
     */
    public static function create_tables(): void {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $table_prefix = $wpdb->prefix;
        $table_schema = array(
            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_schedules (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                team_member_id int(11) NOT NULL,
                service_id int(11) NOT NULL,
                location_id int(11) NOT NULL,
                start_time varchar(100) NOT NULL,
                end_time varchar(100) NOT NULL,
                week_day varchar(100) NOT NULL,
                custom_date varchar(100) NOT NULL,
                created_at datetime DEFAULT NULL,
                updated_at datetime DEFAULT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;",

            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_carts (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                uuid varchar(36) NOT NULL,
                user_id int(11) NOT NULL,
                coupon_code varchar(100) DEFAULT NULL,
                total decimal(10,2) DEFAULT NULL,
                created_at datetime DEFAULT NULL,
                updated_at datetime DEFAULT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;",

            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_cart_items (
                id mediumint(9) NOT NULL AUTO_INCREMENT,
                uuid varchar(36) NOT NULL,
                cart_id mediumint(9) NOT NULL,
                team_member_id int(11) DEFAULT NULL,
                service_id int(11) DEFAULT NULL,
                location_id int(11) DEFAULT NULL,
                date date DEFAULT NULL,
                start_time varchar(100) DEFAULT NULL,
                end_time varchar(100) DEFAULT NULL,
                price VARCHAR(100) DEFAULT NULL,
                created_at datetime DEFAULT NULL,
                updated_at datetime DEFAULT NULL,
                PRIMARY KEY (id)
            ) $charset_collate;",

            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_orders (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                order_no VARCHAR(100) NOT NULL,
                customer_id BIGINT UNSIGNED NOT NULL,
                customer_note VARCHAR(100) DEFAULT NULL,
                status VARCHAR(50) DEFAULT 'pending',
                payment_status VARCHAR(50) DEFAULT NULL,
                timezone VARCHAR(50) DEFAULT 'UTC',
                currency VARCHAR(10) DEFAULT NULL,
                price_breakdown JSON DEFAULT NULL,
                tax_total VARCHAR(100) DEFAULT NULL,
                coupon_code VARCHAR(100) DEFAULT NULL,
                coupon_discount VARCHAR(100) DEFAULT NULL,
                subtotal VARCHAR(100) DEFAULT 0,
                total VARCHAR(100) DEFAULT 0,
                payment_method VARCHAR(50) DEFAULT NULL,
                payment_intent_id VARCHAR(250) DEFAULT NULL,
                payment_transaction_id VARCHAR(250) DEFAULT NULL,
                directorist_id BIGINT UNSIGNED DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                INDEX (order_no),
                INDEX (directorist_id)
            ) $charset_collate;",

            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_payments (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                order_id BIGINT UNSIGNED NOT NULL,
                customer_id BIGINT UNSIGNED NOT NULL,
                amount VARCHAR(20) NOT NULL,
                currency VARCHAR(10) DEFAULT 'usd',
                intent_id VARCHAR(255),
                transaction_id VARCHAR(255),
                status varchar(100) NOT NULL DEFAULT 'pending',
                payment_method VARCHAR(50) NOT NULL,
                refunded_amount VARCHAR(20) DEFAULT '0.00',
                failure_reason TEXT,
                date DATE NOT NULL,
                date_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                INDEX (order_id),
                INDEX (customer_id),
                INDEX (intent_id),
                INDEX (transaction_id)
            ) $charset_collate;",

            "CREATE TABLE IF NOT EXISTS {$table_prefix}booktics_guests (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                wp_user_id BIGINT UNSIGNED DEFAULT NULL,
                first_name VARCHAR(100) DEFAULT NULL,
                last_name VARCHAR(100) DEFAULT NULL,
                user_login VARCHAR(100) DEFAULT NULL,
                email VARCHAR(100) DEFAULT NULL,
                phone VARCHAR(10) DEFAULT NULL,
                description VARCHAR(250) DEFAULT NULL,
                image VARCHAR(250) DEFAULT NULL,
                status INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX (wp_user_id),
                INDEX (email),
                INDEX (first_name)
            ) $charset_collate;",
        );
        foreach ( $table_schema as $table_sql ) {
            dbDelta( $table_sql );
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- Schema migration; direct DB access required, caching inappropriate for DDL, identifiers sanitized via esc_sql().

        // Add currency column to existing orders table if it doesn't exist
        $orders_table = "{$table_prefix}booktics_orders";
        $column_exists = $wpdb->get_results(
            "SHOW COLUMNS FROM `" . \esc_sql( $orders_table ) . "` LIKE 'currency'"
        );
        if ( empty( $column_exists ) ) {
            $wpdb->query(
                "ALTER TABLE `" . \esc_sql( $orders_table ) . "` ADD COLUMN currency VARCHAR(10) DEFAULT NULL AFTER timezone"
            );
        }

        // Handle booktics_guests table
        $guests_table = "{$table_prefix}booktics_guests";
        $guests_columns = $wpdb->get_col( 'DESC `' . \esc_sql( $guests_table ) . '`', 0 );

        // Drop index on first_name if it exists
        $indexes = $wpdb->get_results( "SHOW INDEX FROM `" . \esc_sql( $guests_table ) . "` WHERE Key_name = 'first_name'", ARRAY_A );
        if ( ! empty( $indexes ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` DROP INDEX `first_name`',
                    \esc_sql( $guests_table )
                )
            );
        }

        // Rename first_name to name if first_name exists and name does not
        if ( in_array( 'first_name', $guests_columns, true ) && ! in_array( 'name', $guests_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` CHANGE COLUMN `first_name` `name` VARCHAR(100) DEFAULT NULL',
                    \esc_sql( $guests_table )
                )
            );
        }

        // Remove last_name column if it exists
        if ( in_array( 'last_name', $guests_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` DROP COLUMN `last_name`',
                    \esc_sql( $guests_table )
                )
            );
        }

        // Add index on name if it does not exist
        $indexes_name = $wpdb->get_results( "SHOW INDEX FROM `" . \esc_sql( $guests_table ) . "` WHERE Key_name = 'name'", ARRAY_A );
        if ( empty( $indexes_name ) && in_array( 'name', $guests_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` ADD INDEX (`name`)',
                    \esc_sql( $guests_table )
                )
            );
        }

        // Alter first_name column if it exists
        if ( in_array( 'first_name', $guests_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` MODIFY COLUMN `first_name` VARCHAR(100) DEFAULT NULL',
                    \esc_sql( $guests_table )
                )
            );
        }

        // Add missing columns to booktics_orders table
        $orders_table = "{$table_prefix}booktics_orders";
        $orders_columns = $wpdb->get_col( 'DESC `' . esc_sql( $orders_table ) . '`', 0 );

        $orders_columns_to_add = array(
            'payment_method'         => 'VARCHAR(50) DEFAULT NULL',
            'payment_intent_id'      => 'VARCHAR(250) DEFAULT NULL',
            'payment_transaction_id' => 'VARCHAR(250) DEFAULT NULL',
            'custom_fields'          => 'TEXT DEFAULT NULL',
            'directorist_id'                 => 'BIGINT UNSIGNED DEFAULT NULL',
        );

        foreach ( $orders_columns_to_add as $column => $definition ) {
            if ( ! in_array( $column, $orders_columns, true ) ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- Schema migration; identifiers sanitized via esc_sql().
                $wpdb->query(
                    sprintf(
                        'ALTER TABLE `%s` ADD COLUMN `%s` %s',
                        esc_sql( $orders_table ),
                        esc_sql( $column ),
                        $definition
                    )
                );
            }
        }

        // Add index for directorist_id if it doesn't exist
        $indexes = $wpdb->get_results( "SHOW INDEX FROM `" . esc_sql( $orders_table ) . "` WHERE Key_name = 'directorist_id'", ARRAY_A );
        if ( empty( $indexes ) && in_array( 'directorist_id', $orders_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` ADD INDEX (`directorist_id`)',
                    esc_sql( $orders_table )
                )
            );
        }

        // Handle booktics_cart_items table
        $cart_items_table = "{$table_prefix}booktics_cart_items";
        $cart_items_columns = $wpdb->get_col( 'DESC `' . esc_sql( $cart_items_table ) . '`', 0 );

        // Rename sub_total to subtotal if exists
        if ( in_array( 'sub_total', $cart_items_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` CHANGE COLUMN `sub_total` `subtotal` VARCHAR(20) NOT NULL',
                    esc_sql( $cart_items_table )
                )
            );
        } elseif ( ! in_array( 'subtotal', $cart_items_columns, true ) ) {
            $wpdb->query(
                sprintf(
                    'ALTER TABLE `%s` ADD COLUMN `subtotal` VARCHAR(20) NOT NULL',
                    esc_sql( $cart_items_table )
                )
            );
        }

        $cart_items_columns_to_add = array(
            'extra_services'         => 'TEXT DEFAULT NULL',
            'total'                  => 'VARCHAR(20) NOT NULL',
            'additional_duration_id' => 'VARCHAR(100) NOT NULL',
            'group_booking'          => 'TEXT DEFAULT NULL',
            'time_format'            => 'VARCHAR(10) DEFAULT NULL',
        );

        foreach ( $cart_items_columns_to_add as $column => $definition ) {
            if ( ! in_array( $column, $cart_items_columns, true ) ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared -- Schema migration; identifiers sanitized via esc_sql().
                $wpdb->query(
                    sprintf(
                        'ALTER TABLE `%s` ADD COLUMN `%s` %s',
                        esc_sql( $cart_items_table ),
                        esc_sql( $column ),
                        $definition
                    )
                );
            }
        }

        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared
    }
}