<?php

defined('ABSPATH') || exit;

class BundlePress_DB
{
    public static function init()
    {
        self::create_tables();
        
        // 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);

        // Settings table (General)
        $table_settings = $wpdb->prefix . 'bundlepress_settings';
        $sql_settings = "CREATE TABLE $table_settings (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            setting_key varchar(100) NOT NULL,
            setting_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 setting_key (setting_key)
        ) $charset_collate;";
        dbDelta($sql_settings);

        // Insert default settings if not exists
        self::insert_default_settings();

        // 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 insert_default_settings()
    {
        global $wpdb;
        $table_settings = $wpdb->prefix . 'bundlepress_settings';

        // Check if settings already exist
        $existing = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}bundlepress_settings WHERE setting_key = %s",
            'feature_settings'
        ));

        if ($existing == 0) {
            // Default feature settings
            $default_features = json_encode([
                'usage_tracking' => true,
                'product_bundles' => true,
                'bundle_placement' => 'woocommerce_after_add_to_cart_form',
                'card_style' => 'card',
                'product_alignment' => 'horizontal',
            ]);

            $wpdb->insert(
                $table_settings,
                [
                    'setting_key' => 'feature_settings',
                    'setting_value' => $default_features
                ],
                ['%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;
    }
}