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

// includes/vmo_admin_tables.php

// This file ONLY handles admin/affiliate/transaction tables for the central (admin) plugin.

// Register hook to create admin/affiliate/transaction tables
// ✅ FIX: register_activation_hook must use the MAIN plugin file, not __FILE__
// from an included file, and must not be inside plugins_loaded (too late).
if (defined('VMO_PLUGIN_FILE')) {
    register_activation_hook(VMO_PLUGIN_FILE, 'vmo_create_admin_tables');
}

// Table creation logic
function vmo_create_admin_tables() {
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    require_once ABSPATH . 'wp-admin/includes/upgrade.php';

    // === Affiliates Table ===
    $affiliates_table = $wpdb->prefix . 'vmo_affiliates';
    $sql1 = "CREATE TABLE IF NOT EXISTS $affiliates_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        wp_user_id BIGINT UNSIGNED DEFAULT NULL, -- optional, if affiliate is a WP user
        name VARCHAR(255),
        email VARCHAR(255),
        referral_code VARCHAR(32) NOT NULL UNIQUE,
        payment_address VARCHAR(255),
        total_earnings DECIMAL(18,2) DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ) $charset_collate;";

    // === Affiliate Signups Table ===
    $signups_table = $wpdb->prefix . 'vmo_affiliate_signups';
    $sql2 = "CREATE TABLE IF NOT EXISTS $signups_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        affiliate_id BIGINT UNSIGNED NOT NULL,
        referred_user_id BIGINT UNSIGNED DEFAULT NULL,
        referred_email VARCHAR(255) NOT NULL,
        registered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (affiliate_id) REFERENCES $affiliates_table(id)
            ON DELETE CASCADE
    ) $charset_collate;";

    // === Transactions Table ===
    $transactions_table = $wpdb->prefix . 'vmo_transactions';
    $sql3 = "CREATE TABLE IF NOT EXISTS $transactions_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        affiliate_id BIGINT UNSIGNED DEFAULT NULL,
        amount DECIMAL(18,2) NOT NULL,
        tokens_awarded INT UNSIGNED NOT NULL,
        stripe_charge_id VARCHAR(128),
        transaction_type ENUM('purchase','bonus','promo','refund') DEFAULT 'purchase',
        meta LONGTEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (affiliate_id) REFERENCES $affiliates_table(id)
            ON DELETE SET NULL
    ) $charset_collate;";

    dbDelta($sql1);
    dbDelta($sql2);
    dbDelta($sql3);

    // === Conversation Threads Table ===
    $conversations_table = $wpdb->prefix . 'vmo_conversations';
    $sql4 = "CREATE TABLE IF NOT EXISTS $conversations_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        thread_title VARCHAR(255) DEFAULT 'New Conversation',
        project_id VARCHAR(255) DEFAULT NULL,
        context_data TEXT DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_id (user_id),
        INDEX idx_updated (updated_at)
    ) $charset_collate;";

    // === Conversation Messages Table ===
    $messages_table = $wpdb->prefix . 'vmo_conversation_messages';
    $sql5 = "CREATE TABLE IF NOT EXISTS $messages_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        conversation_id BIGINT UNSIGNED NOT NULL,
        message_type ENUM('user', 'assistant', 'system') NOT NULL,
        content LONGTEXT NOT NULL,
        metadata JSON DEFAULT NULL,
        tokens_used INT UNSIGNED DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (conversation_id) REFERENCES $conversations_table(id) 
            ON DELETE CASCADE,
        INDEX idx_conversation_id (conversation_id),
        INDEX idx_created (created_at)
    ) $charset_collate;";

    // === Voice Recordings Table ===
    $recordings_table = $wpdb->prefix . 'vmo_voice_recordings';
    $sql6 = "CREATE TABLE IF NOT EXISTS $recordings_table (
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT UNSIGNED NOT NULL,
        conversation_id BIGINT UNSIGNED DEFAULT NULL,
        audio_file_path VARCHAR(500) DEFAULT NULL,
        transcript TEXT DEFAULT NULL,
        confidence_score DECIMAL(3,2) DEFAULT NULL,
        processing_method ENUM('whisper', 'browser', 'azure') DEFAULT 'whisper',
        duration_seconds DECIMAL(5,2) DEFAULT NULL,
        file_size_bytes INT UNSIGNED DEFAULT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (conversation_id) REFERENCES $conversations_table(id) 
            ON DELETE SET NULL,
        INDEX idx_user_id (user_id),
        INDEX idx_conversation_id (conversation_id)
    ) $charset_collate;";

    dbDelta($sql4);
    dbDelta($sql5);
    dbDelta($sql6);
}