<?php

namespace PloverCRM\Modules\Automations\Database;

/**
 * Automations database schema management.
 */
class Schema
{
    public const AUTOMATIONS_TABLE = 'plover_crm_automations';
    public const RUNS_TABLE = 'plover_crm_automation_runs';

    public static function maybeCreateTables(): void
    {
        global $wpdb;

        $automationsTable = $wpdb->prefix . self::AUTOMATIONS_TABLE;
        $runsTable = $wpdb->prefix . self::RUNS_TABLE;

        if ($wpdb->get_var("SHOW TABLES LIKE '$automationsTable'") !== $automationsTable) {
            self::createAutomationsTable();
        }

        if ($wpdb->get_var("SHOW TABLES LIKE '$runsTable'") !== $runsTable) {
            self::createRunsTable();
        }
    }

    private static function createAutomationsTable(): void
    {
        global $wpdb;

        $table = $wpdb->prefix . self::AUTOMATIONS_TABLE;
        $charsetCollate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'draft',
            builder_mode varchar(20) NOT NULL DEFAULT 'guided',
            trigger_event varchar(100) NOT NULL,
            definition_json longtext NOT NULL,
            last_run_at datetime DEFAULT NULL,
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY status (status),
            KEY trigger_event (trigger_event)
        ) $charsetCollate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    private static function createRunsTable(): void
    {
        global $wpdb;

        $table = $wpdb->prefix . self::RUNS_TABLE;
        $charsetCollate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            automation_id bigint(20) unsigned NOT NULL,
            event_name varchar(100) NOT NULL,
            contact_id bigint(20) unsigned DEFAULT NULL,
            status varchar(20) NOT NULL DEFAULT 'running',
            payload_json longtext DEFAULT NULL,
            result_json longtext DEFAULT NULL,
            error_message text DEFAULT NULL,
            started_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            finished_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY automation_id (automation_id),
            KEY event_name (event_name),
            KEY status (status)
        ) $charsetCollate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }
}
