<?php

namespace EasyLinkAutomations\Models;

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

use EasyLinkAutomations\Config;

/**
 * Model for automations table.
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name from Config::withDBPrefix(), escaped via escapeIdentifier().
 */
final class Automation
{
    private const CACHE_GROUP = 'easylink_automations_automations';

    /**
     * Get table name with prefix.
     */
    private static function table(): string
    {
        return Config::withDBPrefix('automations');
    }

    /**
     * Escape a SQL identifier (table name) for use in queries.
     */
    private static function escapeIdentifier(string $identifier): string
    {
        return '`' . str_replace('`', '``', $identifier) . '`';
    }

    /**
     * Count all automations.
     */
    public static function count(?string $status = null): int
    {
        $cache_key = 'count_' . (string) $status;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return (int) $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());
        if ($status) {
            $val = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE status = %s",
                $status
            ));
        } else {
            $val = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
        }
        wp_cache_set($cache_key, $val, self::CACHE_GROUP, 300);

        return $val;
    }

    /**
     * Find an automation by ID.
     */
    public static function find(int $id): ?array
    {
        $cache_key = 'id_' . $id;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table  = self::escapeIdentifier(self::table());
        $result = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE id = %d",
                $id
            ),
            ARRAY_A
        );
        $row = $result ?: null;
        wp_cache_set($cache_key, $row, self::CACHE_GROUP, 300);

        return $row;
    }

    /**
     * Find automations by trigger slug.
     *
     * @return array<array>
     */
    public static function findByTrigger(string $triggerSlug): array
    {
        $cache_key = 'trigger_' . $triggerSlug;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());
        $rows  = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE trigger_type = %s AND status = 'active'",
                $triggerSlug
            ),
            ARRAY_A
        ) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }

    /**
     * Create a new automation.
     *
     * @return int|false Insert ID or false on failure
     */
    public static function create(array $data): int|false
    {
        global $wpdb;

        $result = $wpdb->insert(self::table(), $data);
        if ($result) {
            self::invalidateListCache();
            return $wpdb->insert_id;
        }
        return false;
    }

    /**
     * Update an automation.
     */
    public static function update(int $id, array $data): bool
    {
        global $wpdb;

        $ok = $wpdb->update(self::table(), $data, ['id' => $id]) !== false;
        if ($ok) {
            wp_cache_delete('id_' . $id, self::CACHE_GROUP);
            if (isset($data['trigger_type'])) {
                wp_cache_delete('trigger_' . $data['trigger_type'], self::CACHE_GROUP);
            }
            if (isset($data['status'])) {
                wp_cache_delete('count_' . $data['status'], self::CACHE_GROUP);
            }
            self::invalidateListCache();
        }
        return $ok;
    }

    /**
     * Delete an automation.
     */
    public static function delete(int $id): bool
    {
        global $wpdb;

        $ok = $wpdb->delete(self::table(), ['id' => $id]) !== false;
        if ($ok) {
            wp_cache_delete('id_' . $id, self::CACHE_GROUP);
            self::invalidateListCache();
        }
        return $ok;
    }

    /**
     * Invalidate list/count caches after create/update/delete.
     */
    private static function invalidateListCache(): void
    {
        wp_cache_delete('all', self::CACHE_GROUP);
        wp_cache_delete('count_', self::CACHE_GROUP);
    }

    /**
     * Paginated list of automations.
     */
    public static function paginate(int $page = 1, int $perPage = 20, ?string $status = null): array
    {
        $cache_key = 'pag_' . $page . '_' . $perPage . '_' . (string) $status;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $offset = ($page - 1) * $perPage;
        $table  = self::escapeIdentifier(self::table());

        if ($status) {
            $total = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE status = %s",
                $status
            ));
            $items = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table} WHERE status = %s ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $status,
                    $perPage,
                    $offset
                ),
                ARRAY_A
            ) ?: [];
        } else {
            $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
            $items = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d OFFSET %d",
                    $perPage,
                    $offset
                ),
                ARRAY_A
            ) ?: [];
        }

        $result = [
            'items'       => $items,
            'total'       => $total,
            'page'        => $page,
            'per_page'    => $perPage,
            'total_pages' => (int) ceil($total / $perPage),
        ];
        wp_cache_set($cache_key, $result, self::CACHE_GROUP, 300);

        return $result;
    }

    /**
     * Get all automations.
     *
     * @return array<array>
     */
    public static function all(): array
    {
        $cache_key = 'all';
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());
        $rows  = $wpdb->get_results(
            "SELECT * FROM {$table} ORDER BY created_at DESC",
            ARRAY_A
        ) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }
}
