<?php

defined('ABSPATH') || exit;

class BundlePress_DB
{
    const DB_VERSION = '1.3.5';

    public static function init()
    {
        self::create_tables();
        self::maybe_update_db();

        // Cleanup data when a bundle post is deleted
        add_action('before_delete_post', [__CLASS__, 'cleanup_bundle_data']);
    }

    public static function create_tables()
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

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

        // Main Bundles Table
        $table_bundles = $wpdb->prefix . 'bundlepress_bundles';

        $sql_bundles = "CREATE TABLE $table_bundles (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            type varchar(50) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'publish',
            description longtext,
            short_description text,
            image_id bigint(20) UNSIGNED DEFAULT NULL,
            image_url varchar(255) DEFAULT '',
            gallery longtext,
            product_id bigint(20) UNSIGNED DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY type (type),
            KEY product_id (product_id)
        ) $charset_collate;";
        dbDelta($sql_bundles);

        // Fixed Bundle Settings table
        $table_fixed = $wpdb->prefix . 'bundlepress_fixed_settings';
        $sql_fixed = "CREATE TABLE $table_fixed (
            bundle_id bigint(20) UNSIGNED NOT NULL,
            regular_price decimal(10,2) DEFAULT 0.00,
            sale_price decimal(10,2) DEFAULT NULL,
            discount_type varchar(20) DEFAULT '',
            discount_value decimal(10,2) DEFAULT 0.00,
            layout varchar(50) DEFAULT 'list',
            show_thumbnails tinyint(1) DEFAULT 1,
            allow_quantity_change tinyint(1) DEFAULT 1,
            PRIMARY KEY  (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_fixed);

        // Volume Discount Settings/Rules table
        $table_volume = $wpdb->prefix . 'bundlepress_volume_settings';
        $sql_volume = "CREATE TABLE $table_volume (
            bundle_id bigint(20) UNSIGNED NOT NULL,
            tiers longtext NOT NULL,
            target_type varchar(20) DEFAULT 'all',
            trigger_products longtext DEFAULT NULL,
            PRIMARY KEY  (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_volume);

        // Buy X Get Y Settings table
        $table_bxgy = $wpdb->prefix . 'bundlepress_bxgy_settings';
        $sql_bxgy = "CREATE TABLE $table_bxgy (
            bundle_id bigint(20) UNSIGNED NOT NULL,
            buy_qty int(11) DEFAULT 1,
            get_qty int(11) DEFAULT 1,
            offer_type varchar(50) DEFAULT 'free_gift',
            offer_products longtext DEFAULT NULL,
            discount_type varchar(20) DEFAULT 'percentage',
            discount_amount decimal(10,2) DEFAULT 0.00,
            target_type varchar(20) DEFAULT 'all',
            trigger_products longtext DEFAULT NULL,
            PRIMARY KEY  (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_bxgy);

        // Frequently bought together Settings table
        $table_fbt = $wpdb->prefix . 'bundlepress_fbt_settings';
        $sql_fbt = "CREATE TABLE $table_fbt (
            bundle_id bigint(20) UNSIGNED NOT NULL,
            fbt_source varchar(20) DEFAULT 'manual',
            fbt_limit int(11) DEFAULT 4,
            discount_type varchar(20) DEFAULT '',
            discount_value decimal(10,2) DEFAULT 0.00,
            target_type varchar(20) DEFAULT 'all',
            trigger_products longtext DEFAULT NULL,
            PRIMARY KEY  (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_fbt);

        // Mix & Match Settings table
        $table_mixmatch = $wpdb->prefix . 'bundlepress_mixmatch_settings';
        $sql_mixmatch = "CREATE TABLE $table_mixmatch (
            bundle_id bigint(20) UNSIGNED NOT NULL,
            tiers longtext NOT NULL,
            min_items int(11) DEFAULT 1,
            max_items int(11) DEFAULT NULL,
            target_type varchar(20) DEFAULT 'specific',
            trigger_products longtext DEFAULT NULL,
            PRIMARY KEY  (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_mixmatch);

        // Bundle Items Mapping table
        $table_items = $wpdb->prefix . 'bundlepress_bundle_items';
        $sql_items = "CREATE TABLE $table_items (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            bundle_id bigint(20) UNSIGNED NOT NULL,
            product_id bigint(20) UNSIGNED NOT NULL,
            variation_id bigint(20) UNSIGNED DEFAULT NULL,
            item_type varchar(20) DEFAULT 'bundle_item',
            quantity int(11) DEFAULT 1,
            min_quantity int(11) DEFAULT 1,
            max_quantity int(11) DEFAULT NULL,
            discount_type varchar(20) DEFAULT '',
            discount_value decimal(10,2) DEFAULT 0.00,
            position int(11) DEFAULT 0,
            PRIMARY KEY  (id),
            KEY bundle_id (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_items);

        // Customizations table (UI text overrides)
        $table_customizations = $wpdb->prefix . 'bundlepress_customizations';
        $sql_customizations = "CREATE TABLE $table_customizations (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            customization_key varchar(120) NOT NULL,
            customization_value longtext NOT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY customization_key (customization_key)
        ) $charset_collate;";
        dbDelta($sql_customizations);

        // Analytics table
        $table_analytics = $wpdb->prefix . 'bundlepress_analytics';
        $sql_analytics = "CREATE TABLE $table_analytics (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            bundle_id bigint(20) UNSIGNED NOT NULL,
            event_type varchar(20) NOT NULL,
            user_id bigint(20) DEFAULT 0,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY bundle_id (bundle_id),
            KEY event_type (event_type)
        ) $charset_collate;";
        dbDelta($sql_analytics);

        // Sales table
        $table_sales = $wpdb->prefix . 'bundlepress_sales';
        $sql_sales = "CREATE TABLE $table_sales (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            order_id bigint(20) UNSIGNED NOT NULL,
            bundle_id bigint(20) UNSIGNED NOT NULL,
            product_id bigint(20) UNSIGNED NOT NULL,
            quantity int(11) NOT NULL DEFAULT 1,
            revenue decimal(10,2) NOT NULL DEFAULT 0.00,
            discount decimal(10,2) NOT NULL DEFAULT 0.00,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            KEY order_id (order_id),
            KEY bundle_id (bundle_id)
        ) $charset_collate;";
        dbDelta($sql_sales);
    }

    public static function maybe_update_db()
    {
        $current_version = get_option('bundlepress_db_version');
        if (version_compare($current_version, self::DB_VERSION, '<')) {
            self::insert_default_customizations();
            update_option('bundlepress_db_version', self::DB_VERSION);
        }
    }

    public static function insert_default_customizations()
    {
        global $wpdb;
        $table = $wpdb->prefix . 'bundlepress_customizations';

        // Use the API as the single source of truth for default keys
        if (!class_exists('BundlePress_Customization_API')) {
            require_once BUNDLEPRESS_PATH . 'includes/Customization/class-bundlepress-customization-api.php';
        }
        
        $defaults = BundlePress_Customization_API::get_default_flat();

        foreach ($defaults as $key => $value) {
            $existing = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM {$wpdb->prefix}bundlepress_customizations WHERE customization_key = %s",
                    $key
                )
            );

            if ((int)$existing === 0) {
                $wpdb->insert(
                    $table,
                    [
                        'customization_key' => $key,
                        'customization_value' => wp_json_encode($value),
                    ],
                    ['%s', '%s']
                );
            }
        }
    }

    /**
     * Delete related data when a bundle is permanently deleted
     */
    public static function cleanup_bundle_data($id)
    {
        global $wpdb;
        $table_bundles = $wpdb->prefix . 'bundlepress_bundles';

        // Find the actual bundle ID if $id is a product_id (from the hook) or just the bundle ID itself
        $bundle_id = $wpdb->get_var($wpdb->prepare(
            "SELECT id FROM $table_bundles WHERE id = %d OR product_id = %d",
            $id,
            $id
        ));

        if (!$bundle_id) return;

        // Tables to clean
        $tables = [
            $table_bundles                                  => 'id',
            $wpdb->prefix . 'bundlepress_fixed_settings'    => 'bundle_id',
            $wpdb->prefix . 'bundlepress_volume_settings'   => 'bundle_id',
            $wpdb->prefix . 'bundlepress_bxgy_settings'     => 'bundle_id',
            $wpdb->prefix . 'bundlepress_fbt_settings'      => 'bundle_id',
            $wpdb->prefix . 'bundlepress_mixmatch_settings' => 'bundle_id',
            $wpdb->prefix . 'bundlepress_bundle_items'      => 'bundle_id',
            $wpdb->prefix . 'bundlepress_analytics'         => 'bundle_id',
            $wpdb->prefix . 'bundlepress_sales'             => 'bundle_id',
        ];

        foreach ($tables as $table => $column) {
            $wpdb->delete($table, [$column => $bundle_id], ['%d']);
        }
    }

    public static function get_bundle_data($id)
    {
        global $wpdb;
        $id = (int) $id;
        $bundle = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}bundlepress_bundles WHERE id = %d OR product_id = %d",
            $id,
            $id
        ), ARRAY_A);

        if (!$bundle) return null;

        $bundle_id = (int)$bundle['id'];
        $type = $bundle['type'];
        $bundle['settings'] = [];

        // Sanitize text fields per WP guidelines — preserve safe formatting HTML
        if (isset($bundle['short_description'])) {
            $bundle['short_description'] = wp_kses_post($bundle['short_description']);
        }
        if (isset($bundle['description'])) {
            $bundle['description'] = wp_kses_post($bundle['description']);
        }

        // Hydrate type-specific settings
        $settings_table = $wpdb->prefix . 'bundlepress_' . $type . '_settings';
        $settings = $wpdb->get_row($wpdb->prepare("SELECT * FROM $settings_table WHERE bundle_id = %d", $bundle_id), ARRAY_A);

        if ($settings) {
            unset($settings['bundle_id']);
            $bundle['settings'] = $settings;
            // Decode JSON fields if they exist
            if (isset($bundle['settings']['tiers'])) {
                $bundle['settings']['tiers'] = json_decode($bundle['settings']['tiers'], true);
            }
            if (isset($bundle['settings']['trigger_products'])) {
                $bundle['settings']['trigger_products'] = json_decode($bundle['settings']['trigger_products'], true);
            }
            if (isset($bundle['settings']['offer_products'])) {
                $bundle['settings']['offer_products'] = json_decode($bundle['settings']['offer_products'], true);
            }

            // Map BXGY keys to match frontend expectation (with bxgy_ prefix)
            if ($type === 'bxgy') {
                $bxgy_map = [
                    'buy_qty'         => 'bxgy_buy_qty',
                    'get_qty'         => 'bxgy_get_qty',
                    'offer_type'      => 'bxgy_offer_type',
                    'offer_products'  => 'offer_product',
                    'discount_type'   => 'bxgy_discount_type',
                    'discount_amount' => 'bxgy_discount_amount',
                ];
                foreach ($bxgy_map as $db_key => $js_key) {
                    if (isset($bundle['settings'][$db_key])) {
                        $bundle['settings'][$js_key] = $bundle['settings'][$db_key];
                    }
                }
            }

            // Map common pricing fields from settings back to top level for UI compatibility
            if (isset($settings['regular_price'])) $bundle['price'] = $settings['regular_price'];
            if (isset($settings['sale_price']))    $bundle['sale_price'] = $settings['sale_price'];
            if (isset($settings['discount_type'])) $bundle['discount_type'] = $settings['discount_type'];
            if (isset($settings['discount_value'])) $bundle['discount_value'] = $settings['discount_value'];
        }

        if (isset($bundle['gallery'])) {
            $bundle['gallery'] = json_decode($bundle['gallery'], true);
        }

        return $bundle;
    }
}
