<?php

if (!defined('ABSPATH')) {
    exit;
}

class BSPP_DB_Schema {

    public const VERSION = '1.0.3';

    public static function create_tables() {
        global $wpdb;

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

        $charset_collate = $wpdb->get_charset_collate();

        /**
         * Settings Table
         */
        $settings_table = $wpdb->prefix . 'bobasoftware_pp_settings';

        $sql_settings = "CREATE TABLE $settings_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            setting_key VARCHAR(191) NOT NULL,
            setting_value LONGTEXT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY setting_key (setting_key)
        ) $charset_collate;";

        dbDelta($sql_settings);

        /**
         * Purchases Table
         */
        $purchases_table = $wpdb->prefix . 'bobasoftware_pp_purchases';

        $sql_purchases = "CREATE TABLE $purchases_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NULL,
            email VARCHAR(191) NULL,
            type VARCHAR(50) NOT NULL,
            amount DECIMAL(10,2) NULL,
            status VARCHAR(50) NOT NULL,
            stripe_session_id VARCHAR(191) NULL,
            stripe_payment_intent_id VARCHAR(191) NULL,
            stripe_invoice_id VARCHAR(191) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY stripe_session_id (stripe_session_id),
            KEY stripe_payment_intent_id (stripe_payment_intent_id),
            KEY stripe_invoice_id (stripe_invoice_id)
        ) $charset_collate;";

        dbDelta($sql_purchases);

        /**
         * Entitlements Table
         */
        $entitlements_table = $wpdb->prefix . 'bobasoftware_pp_entitlements';

        $sql_entitlements = "CREATE TABLE $entitlements_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            post_id BIGINT UNSIGNED NULL,
            type VARCHAR(50) NOT NULL,
            expires_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset_collate;";

        dbDelta($sql_entitlements);



        /**
         * Memberships Table
         */
        $memberships_table = $wpdb->prefix . 'bobasoftware_pp_memberships';

        $sql_memberships = "CREATE TABLE $memberships_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            status VARCHAR(50) NOT NULL,
            plan VARCHAR(50) NOT NULL,
            current_period_end DATETIME NULL,
            stripe_customer_id VARCHAR(191) NULL,
            stripe_subscription_id VARCHAR(191) NULL,
            cancel_at_period_end TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY stripe_subscription_id (stripe_subscription_id)
        ) $charset_collate;";

        dbDelta($sql_memberships);

        /**
         * Usage Meters Table
         */
        $usage_table = $wpdb->prefix . 'bobasoftware_pp_usage_meters';
        $sql_usage = "CREATE TABLE $usage_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            meter_key VARCHAR(191) NOT NULL,
            used_count INT UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY user_meter (user_id, meter_key)
        ) $charset_collate;";
        dbDelta($sql_usage);

        /**
         * Article Access Log Table
         */
        $access_log_table = $wpdb->prefix . 'bobasoftware_pp_article_access_log';
        $sql_access_log = "CREATE TABLE $access_log_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            post_id BIGINT UNSIGNED NOT NULL,
            access_type VARCHAR(50) NOT NULL,
            period_key VARCHAR(191) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_user_post_type_period (user_id, post_id, access_type, period_key)
        ) $charset_collate;";
        dbDelta($sql_access_log);

        /**
         * Magic Links Table
         */
        $magic_links_table = $wpdb->prefix . 'bobasoftware_pp_magic_links';
        $sql_magic_links = "CREATE TABLE $magic_links_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT UNSIGNED NOT NULL,
            token_hash VARCHAR(255) NOT NULL,
            expires_at DATETIME NOT NULL,
            used_at DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY user_id (user_id),
            KEY token_hash (token_hash),
            KEY expires_at (expires_at)
        ) $charset_collate;";

        dbDelta($sql_magic_links);

        /**
         * Webhook Events Table (Stripe idempotency).
         *
         * Stored per Stripe event id so webhook processing is safe to retry.
         */
        $webhook_events_table = $wpdb->prefix . 'bobasoftware_pp_webhook_events';
        $sql_webhook_events = "CREATE TABLE $webhook_events_table (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            stripe_event_id VARCHAR(191) NOT NULL,
            stripe_event_type VARCHAR(191) NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY stripe_event_id (stripe_event_id)
        ) $charset_collate;";

        dbDelta($sql_webhook_events);
    }
}