<?php
/**
 * Database Schema and Migrations Engine
 *
 * @package    24TT_Unrestricted_Forms
 * @subpackage 24TT_Unrestricted_Forms/includes
 * @since      1.0.0
 */

// SECURITY FIRST: Prevent Direct Access
// If this file is called directly outside of WordPress, abort immediately.
if ( ! defined( 'ABSPATH' ) ) {
    exit( 'Direct script access denied.' );
}

/**
 * TT24_DB Class
 * Handles the creation and updates of custom database tables.
 * We use custom tables to guarantee data ownership, prevent wp_postmeta bloat,
 * and ensure lightning-fast queries even with millions of entries.
 */
class TT24_DB {

    /**
     * Create or update custom tables for the plugin.
     * This method is triggered strictly during plugin activation via our main bootstrap file.
     * It uses WordPress's native dbDelta() function to safely execute SQL statements. 
     * dbDelta requires very specific formatting (e.g., exactly two spaces after PRIMARY KEY).
     *
     * @since 1.0.0
     * @return void
     */
    public static function create_tables() {
        // Global $wpdb object for secure database interactions.
        global $wpdb;

        // Fetch the default character set and collation for the current WordPress installation.
        // This ensures our custom tables match the rest of the site's database perfectly.
        $charset_collate = $wpdb->get_charset_collate();

        // Define table names dynamically using the site's specific database prefix.
        // Example: If the site uses "wp_", this becomes "wp_tt24_forms".
        $table_forms        = $wpdb->prefix . 'tt24_forms';
        $table_entries      = $wpdb->prefix . 'tt24_entries';
        $table_entry_fields = $wpdb->prefix . 'tt24_entry_fields';

        /*
         * 1. Forms Table
         * Stores the actual form configurations, titles, and JSON-based builder layouts.
         */
        $sql_forms = "CREATE TABLE {$table_forms} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            title varchar(255) NOT NULL,
            form_settings longtext NOT NULL,
            status varchar(20) DEFAULT 'publish' NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id)
        ) {$charset_collate};";

        /*
         * 2. Entries Table
         * Acts as the parent record for a single form submission. 
         * Stores metadata like the IP address, user agent, and read/unread status.
         */
        $sql_entries = "CREATE TABLE {$table_entries} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            form_id bigint(20) unsigned NOT NULL,
            ip_address varchar(100) NOT NULL,
            user_agent text NOT NULL,
            status varchar(20) DEFAULT 'unread' NOT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (id),
            KEY form_id (form_id)
        ) {$charset_collate};";

        /*
         * 3. Entry Fields Table
         * This is the relational data table. Instead of storing complex, hard-to-search 
         * JSON in one row, we store each submitted field (e.g., First Name, Email) 
         * as its own row linked to the parent Entry ID. This allows blazing-fast sorting and exporting.
         */
        $sql_entry_fields = "CREATE TABLE {$table_entry_fields} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            entry_id bigint(20) unsigned NOT NULL,
            form_id bigint(20) unsigned NOT NULL,
            field_name varchar(255) NOT NULL,
            field_value longtext NOT NULL,
            PRIMARY KEY  (id),
            KEY entry_id (entry_id),
            KEY form_id (form_id)
        ) {$charset_collate};";

        // Include the WordPress upgrade API, which contains the dbDelta() function.
        // This file is not loaded by default on the frontend, so we must require it.
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        
        // Execute the table creation safely using dbDelta.
        // It will create the tables if they don't exist, or update them if the schema changes.
        dbDelta( $sql_forms );
        dbDelta( $sql_entries );
        dbDelta( $sql_entry_fields );
    }
}