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

register_activation_hook(IREP_PLUGIN_FILE, 'irep_create_tables');

function is_sqlite()
{
    global $wpdb;

    // Check if $wpdb is an instance of an SQLite class
    if (isset($wpdb) && is_object($wpdb)) {
        $wpdb_class = get_class($wpdb);
        if (strpos($wpdb_class, 'SQLite') !== false || strpos($wpdb_class, 'Sqlite') !== false) {
            return true;
        }
    }

    // Check for WordPress Playground SQLite integration
    if (defined('WP_SQLITE_DB_PATH') || class_exists('WP_SQLite_DB') || class_exists('WP_PDO_MySQL_On_SQLite')) {
        return true;
    }

    // Check database name/type
    if (defined('DB_NAME') && (strpos(DB_NAME, '.sqlite') !== false || strpos(DB_NAME, '.db') !== false)) {
        return true;
    }

    // Check for SQLite database file in common locations
    $sqlite_files = [
        WP_CONTENT_DIR . '/database/.ht.sqlite',
        WP_CONTENT_DIR . '/db.sqlite',
        ABSPATH . 'wp-content/database.sqlite',
    ];
    foreach ($sqlite_files as $file) {
        if (file_exists($file)) {
            return true;
        }
    }

    // Try to detect by querying database type
    if (isset($wpdb) && is_object($wpdb)) {
        try {
            $db_type = $wpdb->get_var("SELECT sqlite_version()");
            if ($db_type !== null) {
                return true;
            }
        } catch (Exception $e) {
            // Not SQLite, continue
        }
    }

    return false;
}

function irep_create_tables()
{
    global $wpdb;

    // Include dbDelta for table creation
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    $charset_collate = $wpdb->get_charset_collate();

    $is_sqlite = is_sqlite();

    // Define table names
    $projects_table = $wpdb->prefix . 'irep_projects';
    $blocks_table = $wpdb->prefix . 'irep_blocks';
    $floors_table = $wpdb->prefix . 'irep_floors';
    $types_table = $wpdb->prefix . 'irep_types';
    $flats_table = $wpdb->prefix . 'irep_flats';
    $meta_table = $wpdb->prefix . 'irep_project_meta';
    $tooltip_table = $wpdb->prefix . 'irep_tooltip';
    $reservation_table = $wpdb->prefix . 'irep_reservations';

    // Use LONGTEXT for JSON data to ensure compatibility with older MySQL versions
    $json_type = 'LONGTEXT';

    // For SQLite (WordPress Playground), use MySQL syntax but avoid ON UPDATE CURRENT_TIMESTAMP
    // WordPress Playground's SQLite integration expects MySQL syntax and translates it
    $updated_at_sql = $is_sqlite
        ? 'updated_at DATETIME DEFAULT CURRENT_TIMESTAMP'
        : 'updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP';

    // Projects table
    $projects_sql = "CREATE TABLE $projects_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        svg LONGTEXT,
        `360images` $json_type,
        project_image BIGINT(20) UNSIGNED NOT NULL,
        slug VARCHAR(255),
        polygon_data $json_type,
        mobile_svg LONGTEXT,
        mobile_polygon_data $json_type,
        project_views $json_type,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($projects_sql);
    // Blocks table
    $blocks_sql = "CREATE TABLE $blocks_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        is_active TINYINT(1) DEFAULT 1,
        title VARCHAR(255) NOT NULL,
        conf VARCHAR(255),
        `360images` $json_type,
        block_image BIGINT(20) UNSIGNED NOT NULL,
        polygon_data $json_type,
        mobile_svg LONGTEXT,
        mobile_polygon_data $json_type,
        primary_mobile_image TEXT,
        block_views $json_type,
        primary_view_title VARCHAR(255),
        svg LONGTEXT,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($blocks_sql);

    // Floors table
    $floors_sql = "CREATE TABLE $floors_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        floor_number VARCHAR(255) NOT NULL,
        title VARCHAR(255),
        conf VARCHAR(255),
        floor_image BIGINT(20) UNSIGNED NOT NULL,
        polygon_data $json_type,
        svg LONGTEXT NOT NULL,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        block_id BIGINT(20) UNSIGNED,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id),
        UNIQUE KEY unique_floor (project_id, floor_number, block_id)
    ) $charset_collate;";
    dbDelta($floors_sql);

    // Types table
    $types_sql = "CREATE TABLE $types_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        title VARCHAR(255) NOT NULL,
        teaser TEXT,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        image_2d $json_type,
        image_3d $json_type,
        gallery $json_type,
        area_m2 DECIMAL(10,2),
        rooms_count DECIMAL(10,1),
        other $json_type,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($types_sql);

    // Flats table
    $flats_sql = "CREATE TABLE $flats_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        is_active TINYINT(1) DEFAULT 1,
        block_id BIGINT(20) UNSIGNED,
        floor_id BIGINT(20) UNSIGNED,
        type_id BIGINT(20) UNSIGNED,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        conf VARCHAR(255),
        flat_number VARCHAR(255) NOT NULL,
        click_action VARCHAR(255),
        follow_link $json_type,
        request_price TINYINT(1) DEFAULT 0,
        price DECIMAL(10,2) NOT NULL,
        offer_price DECIMAL(10,2),
        use_type TINYINT(1) DEFAULT 1,
        type $json_type,
        files $json_type,
        whatsapp $json_type,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($flats_sql);

    // Project metadata table
    $meta_sql = "CREATE TABLE $meta_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        meta_key VARCHAR(255) NOT NULL,
        meta_value TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($meta_sql);

    // Tooltip table
    $tooltip_sql = "CREATE TABLE $tooltip_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        project_id BIGINT(20) UNSIGNED NOT NULL,
        title VARCHAR(255) NOT NULL,
        data $json_type,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($tooltip_sql);

    // Reservations table
    $reservation_sql = "CREATE TABLE $reservation_table (
        id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
        flat_id BIGINT(20) UNSIGNED NOT NULL,
        name VARCHAR(255) NOT NULL,
        phone VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL,
        comment TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        $updated_at_sql,
        PRIMARY KEY (id)
    ) $charset_collate;";
    dbDelta($reservation_sql);

    // Add foreign keys for MySQL only (skip for SQLite)
    if (!$is_sqlite) {
        // Upgrade image ID columns from INT -> BIGINT on existing installs.
        // WordPress attachment IDs are commonly > 2^31-1, and INT overflows to ~2147483647.
        $maybeUpgradeImageColumnToBigint = function (string $table, string $column) use ($wpdb) {
            $dataType = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT DATA_TYPE
                     FROM information_schema.COLUMNS
                     WHERE TABLE_SCHEMA = %s
                       AND TABLE_NAME = %s
                       AND COLUMN_NAME = %s",
                    DB_NAME,
                    $table,
                    $column
                )
            );

            // DATA_TYPE is usually `int` for INT columns.
            if ($dataType === "int") {
                $wpdb->query("ALTER TABLE {$table} MODIFY {$column} BIGINT(20) UNSIGNED NOT NULL");
            }
        };

        $maybeUpgradeImageColumnToBigint($projects_table, "project_image");
        $maybeUpgradeImageColumnToBigint($blocks_table, "block_image");
        $maybeUpgradeImageColumnToBigint($floors_table, "floor_image");

        // Check if foreign key already exists before adding
        $add_fk_if_not_exists = function ($table, $constraint_name, $fk_sql) use ($wpdb) {
            $constraint_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS 
                WHERE CONSTRAINT_SCHEMA = %s 
                AND TABLE_NAME = %s 
                AND CONSTRAINT_NAME = %s 
                AND CONSTRAINT_TYPE = 'FOREIGN KEY'",
                DB_NAME,
                $table,
                $constraint_name
            ));

            if (!$constraint_exists) {
                $wpdb->query($fk_sql);
            }
        };

        // Blocks table
        $add_fk_if_not_exists(
            $blocks_table,
            'fk_blocks_project_id',
            "ALTER TABLE $blocks_table ADD CONSTRAINT fk_blocks_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );

        // Floors table
        $add_fk_if_not_exists(
            $floors_table,
            'fk_floors_project_id',
            "ALTER TABLE $floors_table ADD CONSTRAINT fk_floors_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );
        $add_fk_if_not_exists(
            $floors_table,
            'fk_floors_block_id',
            "ALTER TABLE $floors_table ADD CONSTRAINT fk_floors_block_id FOREIGN KEY (block_id) REFERENCES $blocks_table(id) ON DELETE CASCADE"
        );

        // Types table
        $add_fk_if_not_exists(
            $types_table,
            'fk_types_project_id',
            "ALTER TABLE $types_table ADD CONSTRAINT fk_types_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );

        // Flats table
        $add_fk_if_not_exists(
            $flats_table,
            'fk_flats_project_id',
            "ALTER TABLE $flats_table ADD CONSTRAINT fk_flats_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );
        $add_fk_if_not_exists(
            $flats_table,
            'fk_flats_block_id',
            "ALTER TABLE $flats_table ADD CONSTRAINT fk_flats_block_id FOREIGN KEY (block_id) REFERENCES $blocks_table(id) ON DELETE CASCADE"
        );
        $add_fk_if_not_exists(
            $flats_table,
            'fk_flats_type_id',
            "ALTER TABLE $flats_table ADD CONSTRAINT fk_flats_type_id FOREIGN KEY (type_id) REFERENCES $types_table(id) ON DELETE CASCADE"
        );

        // Project meta table
        $add_fk_if_not_exists(
            $meta_table,
            'fk_meta_project_id',
            "ALTER TABLE $meta_table ADD CONSTRAINT fk_meta_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );

        // Tooltip table
        $add_fk_if_not_exists(
            $tooltip_table,
            'fk_tooltip_project_id',
            "ALTER TABLE $tooltip_table ADD CONSTRAINT fk_tooltip_project_id FOREIGN KEY (project_id) REFERENCES $projects_table(id) ON DELETE CASCADE"
        );
    }

    // Note: Foreign keys are handled by WordPress Playground's SQLite integration automatically
}
