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

if (!class_exists('DevAccountingRunMigrationV1')) {
    class DevsAccounting_Run_MigrationV1
    {
        public function __construct()
        {
            // echo "migration run";
            // exit;
            $this->run_migrations();
        }

        public function run_migrations()
        {
            $this->create_items_table();
            $this->create_category_table();
            $this->create_contact_table();
            $this->create_invoice_table();
            $this->create_invoice_items_table();
            $this->create_accounts_table();
            $this->create_transactions_table();
            $this->create_inventories_table();
            $this->insert_initial_data();
            $this->insert_initial_option();
            $this->add_inventories_type_column();
            $this->add_meta_column_to_items();
            $this->create_subscription_table();
            $this->create_installation_table();
            $this->add_data_subscription_columns_to_transactions();
            $this->create_tax_table();
            $this->create_item_tax_table();
            $this->create_transfer_table();
            $this->create_order_status_table();
            $this->add_enabled_items_field_into_items();
            $this->add_enabled_column_into_accounts();
            $this->add_checkin_checkout_into_subscriptions();
            $this->add_post_id_into_dac_items();
            $this->add_invoice_no_into_subscriptions();
        }

        private function create_items_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(255) NULL,
                name VARCHAR(255),
                description TEXT NULL,
                sku VARCHAR(255),
                sale_price DOUBLE NULL,
                purchase_price DOUBLE NULL,
                category_id INT,
                stock_limit BIGINT UNSIGNED NULL,
                quantity BIGINT UNSIGNED NULL,
                comment VARCHAR(255) NULL,
                exp_date DATETIME NULL,
                enabled TINYINT NULL,
                image VARCHAR(255) NULL,
                meta TEXT NULL,
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_category_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(255) NULL,
                name VARCHAR(255),
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_contact_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(255) NULL,
                name VARCHAR(255),
                email VARCHAR(255),
                user_id INT NULL,
                tax_number VARCHAR(255) NULL,
                address VARCHAR(255),
                country VARCHAR(100) DEFAULT NULL,
                states VARCHAR(100) DEFAULT NULL,
                phone VARCHAR(255),
                website VARCHAR(255) NULL,
                currency VARCHAR(255) NULL,
                enabled TINYINT NULL,
                reference VARCHAR(500) NULL,
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

            $columns = $wpdb->get_col("DESC $table_name");

            if (!in_array('country', $columns)) {
                $wpdb->query("ALTER TABLE $table_name ADD country VARCHAR(100) DEFAULT NULL;");
            }

            if (!in_array('states', $columns)) {
                $wpdb->query("ALTER TABLE $table_name ADD states VARCHAR(100) DEFAULT NULL;");
            }
        }


        private function create_invoice_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(255) NULL,
                invoice_no VARCHAR(255) NULL,
                order_no VARCHAR(255) NULL,
                status VARCHAR(255) NULL,
                issued_at DATETIME NULL,
                due_at DATETIME NULL,
                amount DOUBLE NULL,
                discount DOUBLE NULL,
                currency_code VARCHAR(255) NULL,
                currency_rate DOUBLE NULL,
                category_id INT NULL,
                contact_id INT NULL,
                contact_name VARCHAR(255) NULL, 
                contact_email VARCHAR(255) NULL,
                contact_tax_number VARCHAR(255) NULL,
                contact_phone VARCHAR(255) NULL,
                contact_address TEXT NULL,
                uuid BIGINT NULL,
                notes TEXT NULL,
                footer TEXT NULL,
                parent_id INT NULL,
                share_code VARCHAR(255) NULL,
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_invoice_items_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                invoice_no VARCHAR(255) NULL,
                invoice_items_data TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_accounts_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NULL,
                number VARCHAR(255) NULL,
                currency_code VARCHAR(255) NULL,
                opening_balance DOUBLE NULL,
                bank_name VARCHAR(255) NULL,
                bank_phone VARCHAR(255) NULL,
                bank_address TEXT NULL,
                enabled TINYINT NULL ,
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_transactions_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                type VARCHAR(255) NULL,
                paid_at DATETIME NULL,
                amount DOUBLE NULL,
                currency_code VARCHAR(255) NULL,
                currency_rate DOUBLE NULL,
                account_id INT NULL,
                document_id INT NULL,
                contact_id INT NULL,
                category_id INT NULL,
                description TEXT NULL,
                payment_method VARCHAR(255) NULL,
                reference VARCHAR(255) NULL,
                parent_id INT NULL,
                reconciled BOOLEAN NULL,
                enabled TINYINT NULL ,
                deleted_at DATETIME NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function create_inventories_table()
        {
            global $wpdb;

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

            $charset_collate = $wpdb->get_charset_collate();

            $sql = "CREATE TABLE $table_name  (
                id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                item_id BIGINT UNSIGNED NOT NULL,
                in_out_qty INT NOT NULL,
                remarks TEXT NULL,
                user_id BIGINT UNSIGNED NOT NULL,
                variant_id BIGINT UNSIGNED NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) $charset_collate;";

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

        private function insert_initial_data()
        {
            global $wpdb;

            // Insert initial category
            $table_name = $wpdb->prefix . 'dac_categories';
            $wpdb->insert($table_name, [
                'type' => 'expense',
                'name' => 'Sale'
            ]);

            // Insert initial account
            $table_name = $wpdb->prefix . 'dac_accounts';
            $wpdb->insert($table_name, [
                'name' => 'Cash',
                'bank_name' => 'Cash'
            ]);
        }

        private function insert_initial_option()
        {
            $option_name = 'devs_accounting_settings';
            $option_value = json_encode([
                'company_name' => '',
                'address' => '',
                'phone' => '',
                'page' => '',
                'payment' => ['Cash']
            ]);

            if (!get_option($option_name)) {
                add_option($option_name, $option_value);
            } else {
                update_option($option_name, $option_value);
            }
        }

        private function add_inventories_type_column()
		{
			global $wpdb;

			$table_name = esc_sql($wpdb->prefix . 'dac_inventories');

            $column_exists = $wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %s LIKE 'type'", $table_name));

			if ( ! $column_exists ) {
				$sql = "ALTER TABLE {$table_name} ADD COLUMN type VARCHAR(255) NULL AFTER remarks;";

				require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
				$wpdb->query($sql); // $sql is constructed above with validated table name
			}
		}


        private function create_order_status_table()
        {
            global $wpdb;
            $table_name = $wpdb->prefix . 'dac_order_statuses';
            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();
                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    slug VARCHAR(100) NOT NULL,
                    name VARCHAR(255) NOT NULL,
                    color VARCHAR(50) DEFAULT '#000000',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    deleted_at DATETIME NULL
                ) $charset_collate;";
                require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
                dbDelta($sql);
            }
        }

        private function add_meta_column_to_items()
		{
			global $wpdb;
            $table_name = $wpdb->prefix . 'dac_items';

			$column_exists = $wpdb->get_var("SHOW COLUMNS FROM {$table_name} LIKE 'meta'");

			if (!$column_exists) {
				$sql = "ALTER TABLE {$table_name} ADD meta TEXT NULL;";
                require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
                dbDelta($sql);
			}
		}

        private function create_subscription_table()
        {
            global $wpdb;
            $table_name = $wpdb->prefix . 'dac_subscriptions';

            // Check if the table already exists
            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();

                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    user_id BIGINT UNSIGNED NULL,
                    item_id BIGINT UNSIGNED NULL,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    valid_till DATETIME NULL,
                    status VARCHAR(255) NULL,
                    license_key TEXT NULL,
                    site_limit INT NULL
                ) $charset_collate;";

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

                if ($wpdb->last_error) {
                    error_log('DB Error: ' . $wpdb->last_error);
                }
            }
        }

        private function create_installation_table()
        {
            global $wpdb;
            $table_name = $wpdb->prefix . 'dac_installations';

            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();

                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    user_id BIGINT UNSIGNED NULL,
                    item_id BIGINT UNSIGNED NULL,
                    website VARCHAR(255) NULL,
                    ip VARCHAR(255) NULL,
                    status VARCHAR(255) NULL,
                    subscription_id BIGINT UNSIGNED NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) $charset_collate;";

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

                if ($wpdb->last_error) {
                    error_log('DB Error: ' . $wpdb->last_error);
                }
            }
        }

        private function add_data_subscription_columns_to_transactions()
        {
            global $wpdb;

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

            if (!$wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %s LIKE 'data'", $table_name))) {
                $wpdb->query($wpdb->prepare("ALTER TABLE %s ADD COLUMN data LONGTEXT NULL;", $table_name));
            }

            if (!$wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM %s LIKE 'transaction_id'", $table_name))) {
                $wpdb->query($wpdb->prepare("ALTER TABLE %s ADD COLUMN transaction_id TEXT NULL;", $table_name));
            }
        }

        private function create_tax_table()
        {
            global $wpdb;

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

            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();

                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    tax_name VARCHAR(255) NOT NULL,
                    tax_rate DOUBLE NOT NULL,
                    tax_type VARCHAR(50) NOT NULL,
                    country VARCHAR(100) DEFAULT NULL,
                    states VARCHAR(100) DEFAULT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    deleted_at DATETIME NULL
                ) $charset_collate;";

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

                if ($wpdb->last_error) {
                    error_log('DB Error: ' . $wpdb->last_error);
                }
            } else {
                // Add missing columns if table already exists
                $columns = $wpdb->get_col("DESC $table_name");

                if (!in_array('country', $columns)) {
                    $wpdb->query("ALTER TABLE $table_name ADD country VARCHAR(100) DEFAULT NULL;");
                }

                if (!in_array('states', $columns)) {
                    $wpdb->query("ALTER TABLE $table_name ADD states VARCHAR(100) DEFAULT NULL;");
                }
            }
        }

        private function create_item_tax_table()
        {
            global $wpdb;
            $table_name = $wpdb->prefix . 'dac_item_tax';
            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();

                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    item_id BIGINT UNSIGNED NOT NULL,
                    tax_id BIGINT UNSIGNED NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    deleted_at DATETIME NULL
                ) $charset_collate;";

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

                if ($wpdb->last_error) {
                    error_log('DB Error: ' . $wpdb->last_error);
                }
            }
        }


        private function create_transfer_table()
        {
            global $wpdb;

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

            if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") !== $table_name) {
                $charset_collate = $wpdb->get_charset_collate();

                $sql = "CREATE TABLE $table_name (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    from_account_id BIGINT UNSIGNED NOT NULL,
                    to_account_id BIGINT UNSIGNED NOT NULL,
                    amount DOUBLE NOT NULL,
                    currency_code VARCHAR(255) NULL,
                    currency_rate DOUBLE NULL,
                    transfer_date DATETIME NOT NULL,
                    reference VARCHAR(255) NULL,
                    status VARCHAR(255) NULL,
                    payment_method VARCHAR(255) NULL, 
                    deleted_at DATETIME NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) $charset_collate;";


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


                if ($wpdb->last_error) {
                    error_log('DB Error: ' . $wpdb->last_error);
                }
            }
        }

        private function add_enabled_items_field_into_items()
        {
            global $wpdb;

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

            if (!$wpdb->get_var("SHOW COLUMNS FROM $table_name LIKE 'enabled'")) {
                $wpdb->query("ALTER TABLE $table_name ADD COLUMN enabled TINYINT DEFAULT 1;");
            }
        }
        private function add_enabled_column_into_accounts()
        {
            global $wpdb;
            $table_name = $wpdb->prefix . 'dac_accounts';

            // Simple check if column exists
            if ($wpdb->get_var("SHOW COLUMNS FROM $table_name LIKE 'enabled'") === null) {
                $wpdb->query("ALTER TABLE $table_name ADD enabled TINYINT(1) DEFAULT 1");
            }
        }
        private function add_checkin_checkout_into_subscriptions()
        {
            global $wpdb;
            $table = $wpdb->prefix . 'dac_subscriptions';

            if ($wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'checkin'") === null) {
                $wpdb->query("ALTER TABLE $table ADD checkin DATETIME NULL AFTER item_id");
            }

            if ($wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'checkout'") === null) {
                $wpdb->query("ALTER TABLE $table ADD checkout DATETIME NULL AFTER checkin");
            }
        }
        private function add_post_id_into_dac_items(){
            global $wpdb;
            $table = $wpdb->prefix . 'dac_items';
            if($wpdb->get_var("SHOW COLUMNS FROM $table LIKE 'post_id'") === null)
            {
                $wpdb->query("ALTER TABLE $table ADD post_id INT NULL AFTER type");
            }

        }
        private function add_invoice_no_into_subscriptions()
        {
            global $wpdb;
            $table = $wpdb->prefix . 'dac_subscriptions';
            
            if ($wpdb->get_var($wpdb->prepare("SHOW COLUMNS FROM $table LIKE 'invoice_no'")) === null) {
                $wpdb->query("ALTER TABLE $table ADD invoice_no VARCHAR(255) DEFAULT NULL AFTER item_id");
            }
        }

    }

    $dev_migrations = new DevsAccounting_Run_MigrationV1();
}
