<?php
// Exit if accessed directly.
if (! defined('ABSPATH')) {
    exit;
}

// Check if class already exists to prevent redeclaration.
if (! class_exists('INTEGRAZO_WCZO_DB_Setup')) {

    class INTEGRAZO_WCZO_DB_Setup
    {

        /**
         * WordPress DB instance.
         *
         * @var wpdb
         */
        private $wpdb;

        /**
         * Charset and collation for DB table creation.
         *
         * @var string
         */
        private $charset_collate;

        /**
         * Constructor - Initialize DB and charset.
         */
        public function __construct()
        {
            global $wpdb;
            $this->wpdb            = $wpdb;
            $this->charset_collate = $wpdb->get_charset_collate();
        }

        /**
         * Creates all required tables for the plugin.
         */
        public function create_tables()
        {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            $this->create_accounts_table();
            $this->create_integrations_table();
            $this->create_logs_table();
        }

        /**
         * Create the `integrazo_accounts` table.
         */
        private function create_accounts_table()
        {
            $table_name = $this->wpdb->prefix . 'integrazo_wczo_accounts';

            if ($this->table_exists($table_name)) {
                return;
            }

            $sql = "CREATE TABLE `$table_name` (
        id INT(11) NOT NULL AUTO_INCREMENT,
        account_name VARCHAR(255) NOT NULL,
        product_name VARCHAR(255) NOT NULL,
        data_center VARCHAR(255) DEFAULT NULL,
        account_data LONGTEXT NOT NULL,
        input_data LONGTEXT NOT NULL,
        created_by BIGINT(20) UNSIGNED DEFAULT NULL,
        updated_by BIGINT(20) UNSIGNED 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)
    ) ENGINE=InnoDB $this->charset_collate;";

            dbDelta($sql);
        }


        /**
         * Create the `integrazo_integrations` table.
         */
        private function create_integrations_table()
        {
            $table_name = $this->wpdb->prefix . 'integrazo_wczo_integrations';
            if ($this->table_exists($table_name)) {
                return;
            }

            // Make FK name unique across the schema (<= 64 chars)
            $fk_account = substr($this->wpdb->prefix . 'integrazo_wczo_integrations_fk_account', 0, 63);

            $sql = "CREATE TABLE `$table_name` (
        id INT(11) NOT NULL AUTO_INCREMENT,
        account_id INT(11),
        integration_name VARCHAR(255) NOT NULL,
        from_plugin VARCHAR(100) NOT NULL,
        to_product VARCHAR(100) NOT NULL,
        integration_data LONGTEXT,
        record_action VARCHAR(255),
        primary_key VARCHAR(255),
        filter_criteria VARCHAR(255),
        filter_criteria_json LONGTEXT,
        field_mapping LONGTEXT,
        integration_status TINYINT(1) DEFAULT 1,
        created_by BIGINT(20) UNSIGNED,
        created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (id),
        KEY account_id (account_id),
        CONSTRAINT `$fk_account`
            FOREIGN KEY (account_id)
            REFERENCES `{$this->wpdb->prefix}integrazo_wczo_accounts` (id)
            ON DELETE CASCADE
    ) ENGINE=InnoDB $this->charset_collate;";

            dbDelta($sql);
        }

        private function create_logs_table()
        {
            $table_name = $this->wpdb->prefix . 'integrazo_wczo_logs';
            if ($this->table_exists($table_name)) {
                return;
            }

            // Make FK name unique across the schema (<= 64 chars)
            $fk_integration = substr($this->wpdb->prefix . 'integrazo_wczo_logs_fk_integration', 0, 63);

            $sql = "CREATE TABLE `$table_name` (
        id INT(11) NOT NULL AUTO_INCREMENT,
        integration_id INT(11) NOT NULL,
        category VARCHAR(50) NOT NULL,
        message LONGTEXT,
        submitted_data LONGTEXT,
        response_data LONGTEXT,
        retry_count INT 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 integration_id (integration_id),
        CONSTRAINT `$fk_integration`
            FOREIGN KEY (integration_id)
            REFERENCES `{$this->wpdb->prefix}integrazo_wczo_integrations` (id)
            ON DELETE CASCADE
    ) ENGINE=InnoDB $this->charset_collate;";

            dbDelta($sql);
        }


        /**
         * Check if a table exists in the database.
         *
         * @param string $table_name The full table name including prefix.
         * @return bool True if table exists, false otherwise.
         */
        private function table_exists($table_name)
        {
            global $wpdb;
            $like = $wpdb->esc_like($table_name); // escape % & _
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $found = $wpdb->get_var(
                $wpdb->prepare('SHOW TABLES LIKE %s', $like)
            );
            return $found === $table_name;
        }
    }
}
