<?php

namespace EasyLinkAutomations\Models;

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

use EasyLinkAutomations\Config;

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

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

    /**
     * Get automations table name.
     */
    private static function automationsTable(): 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 executions for today.
     */
    public static function countToday(): int
    {
        $today     = gmdate('Y-m-d');
        $cache_key = 'count_today_' . $today;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return (int) $cached;
        }

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

        return $val;
    }

    /**
     * Count total executions.
     */
    public static function countAll(): int
    {
        $cache_key = 'count_all';
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return (int) $cached;
        }

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

        return $val;
    }

    /**
     * Find a log entry 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 all logs for an automation.
     *
     * @return array<array>
     */
    public static function findByAutomation(int $automationId, int $limit = 100): array
    {
        $cache_key = 'automation_' . $automationId . '_' . $limit;
        $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 automation_id = %d ORDER BY started_at DESC LIMIT %d",
                $automationId,
                $limit
            ),
            ARRAY_A
        ) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }

    /**
     * Create a new log entry.
     *
     * @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) {
            $automation_id = (int) ($data['automation_id'] ?? 0);
            if ($automation_id) {
                wp_cache_delete('automation_' . $automation_id . '_100', self::CACHE_GROUP);
                wp_cache_delete('automation_' . $automation_id . '_50', self::CACHE_GROUP);
                wp_cache_delete('stats_' . $automation_id, self::CACHE_GROUP);
            }
            wp_cache_delete('count_all', self::CACHE_GROUP);
            wp_cache_delete('count_today_' . gmdate('Y-m-d'), self::CACHE_GROUP);
            wp_cache_delete('recent_50', self::CACHE_GROUP);
            return $wpdb->insert_id;
        }
        return false;
    }

    /**
     * Update a log entry by ID.
     */
    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);
            wp_cache_delete('count_all', self::CACHE_GROUP);
            wp_cache_delete('count_today_' . gmdate('Y-m-d'), self::CACHE_GROUP);
            wp_cache_delete('recent_50', self::CACHE_GROUP);
        }

        return $ok;
    }

    /**
     * Get paginated logs across all automations (joined with automation title).
     *
     * @return array{ items: array<array>, total: int, page: int, per_page: int }
     */
    public static function paginate(int $page = 1, int $perPage = 10): array
    {
        $page    = max(1, $page);
        $perPage = max(1, min(100, $perPage));
        $offset  = ($page - 1) * $perPage;

        global $wpdb;
        $logTable  = self::escapeIdentifier(self::table());
        $autoTable = self::escapeIdentifier(self::automationsTable());

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Execution logs are transient data; caching is unnecessary for paginated list views.
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$logTable}");

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Paginated query; results change on every request.
        $items = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT l.*, a.title as automation_title
                 FROM {$logTable} l
                 LEFT JOIN {$autoTable} a ON l.automation_id = a.id
                 ORDER BY l.started_at DESC
                 LIMIT %d OFFSET %d",
                $perPage,
                $offset
            ),
            ARRAY_A
        ) ?: [];

        return [
            'items'    => $items,
            'total'    => $total,
            'page'     => $page,
            'per_page' => $perPage,
        ];
    }

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

        global $wpdb;
        $logTable  = self::escapeIdentifier(self::table());
        $autoTable = self::escapeIdentifier(self::automationsTable());
        $rows      = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT l.*, a.title as automation_title 
                 FROM {$logTable} l 
                 LEFT JOIN {$autoTable} a ON l.automation_id = a.id 
                 ORDER BY l.started_at DESC 
                 LIMIT %d",
                $limit
            ),
            ARRAY_A
        ) ?: [];
        wp_cache_set($cache_key, $rows, self::CACHE_GROUP, 300);

        return $rows;
    }

    /**
     * Get statistics for an automation.
     */
    public static function stats(int $automationId): array
    {
        $cache_key = 'stats_' . $automationId;
        $cached    = wp_cache_get($cache_key, self::CACHE_GROUP);
        if (false !== $cached) {
            return $cached;
        }

        global $wpdb;
        $table = self::escapeIdentifier(self::table());

        $total = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE automation_id = %d",
            $automationId
        ));

        $successful = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE automation_id = %d AND status = 'success'",
            $automationId
        ));

        $failed = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE automation_id = %d AND status = 'failed'",
            $automationId
        ));

        $result = [
            'total'        => $total,
            'successful'   => $successful,
            'failed'       => $failed,
            'success_rate' => $total > 0 ? round(($successful / $total) * 100, 1) : 0,
        ];
        wp_cache_set($cache_key, $result, self::CACHE_GROUP, 300);

        return $result;
    }

    /**
     * Get paginated logs with optional filters.
     *
     * @param array{
     *   page?: int,
     *   per_page?: int,
     *   status?: string,
     *   automation_id?: int,
     *   search?: string,
     * } $filters
     * @return array{ items: array<array>, total: int, page: int, per_page: int }
     */
    public static function paginateFiltered(array $filters = []): array
    {
        $page    = max(1, (int) ($filters['page'] ?? 1));
        $perPage = max(1, min(100, (int) ($filters['per_page'] ?? 20)));
        $offset  = ($page - 1) * $perPage;

        global $wpdb;
        $logTable  = self::escapeIdentifier(self::table());
        $autoTable = self::escapeIdentifier(self::automationsTable());

        $where  = [];
        $values = [];

        if (!empty($filters['status'])) {
            $where[]  = 'l.status = %s';
            $values[] = sanitize_text_field($filters['status']);
        }

        if (!empty($filters['automation_id'])) {
            $where[]  = 'l.automation_id = %d';
            $values[] = (int) $filters['automation_id'];
        }

        if (!empty($filters['search'])) {
            $like     = '%' . $wpdb->esc_like(sanitize_text_field($filters['search'])) . '%';
            $where[]  = '(a.title LIKE %s OR l.error_message LIKE %s)';
            $values[] = $like;
            $values[] = $like;
        }

        $whereClause = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        // Count
        $countQuery = "SELECT COUNT(*) FROM {$logTable} l LEFT JOIN {$autoTable} a ON l.automation_id = a.id {$whereClause}";
        $total = $values
            ? (int) $wpdb->get_var($wpdb->prepare($countQuery, ...$values))
            : (int) $wpdb->get_var($countQuery);

        // Items
        $selectQuery = "SELECT l.*, a.title as automation_title
                        FROM {$logTable} l
                        LEFT JOIN {$autoTable} a ON l.automation_id = a.id
                        {$whereClause}
                        ORDER BY l.started_at DESC
                        LIMIT %d OFFSET %d";

        $queryValues = array_merge($values, [$perPage, $offset]);
        $items = $wpdb->get_results(
            $wpdb->prepare($selectQuery, ...$queryValues),
            ARRAY_A
        ) ?: [];

        return [
            'items'    => $items,
            'total'    => $total,
            'page'     => $page,
            'per_page' => $perPage,
        ];
    }

    /**
     * Delete a log entry by ID.
     */
    public static function delete(int $id): bool
    {
        global $wpdb;
        $result = $wpdb->delete(self::table(), ['id' => $id], ['%d']);

        if ($result) {
            wp_cache_delete('id_' . $id, self::CACHE_GROUP);
            wp_cache_delete('count_all', self::CACHE_GROUP);
            wp_cache_delete('count_today_' . gmdate('Y-m-d'), self::CACHE_GROUP);
            wp_cache_delete('recent_50', self::CACHE_GROUP);
        }

        return (bool) $result;
    }

    /**
     * Delete all logs, optionally filtered by automation.
     */
    public static function deleteAll(?int $automationId = null): int
    {
        global $wpdb;
        $table = self::escapeIdentifier(self::table());

        if ($automationId) {
            $rows = (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE automation_id = %d",
                $automationId
            ));
        } else {
            $rows = (int) $wpdb->query("DELETE FROM {$table}");
        }

        if ($rows > 0) {
            wp_cache_delete('count_all', self::CACHE_GROUP);
            wp_cache_delete('count_today_' . gmdate('Y-m-d'), self::CACHE_GROUP);
            wp_cache_delete('recent_50', self::CACHE_GROUP);
        }

        return $rows;
    }

    /**
     * Get all automations for the filter dropdown.
     *
     * @return array<array{id: int, title: string}>
     */
    public static function automationsForFilter(): array
    {
        global $wpdb;
        $autoTable = self::escapeIdentifier(self::automationsTable());

        return $wpdb->get_results(
            "SELECT id, title FROM {$autoTable} ORDER BY title ASC",
            ARRAY_A
        ) ?: [];
    }

    /**
     * Clean up old logs.
     */
    public static function cleanup(int $daysToKeep = 30): int
    {
        global $wpdb;

        $cutoff = gmdate('Y-m-d H:i:s', strtotime("-{$daysToKeep} days"));

        $table = self::escapeIdentifier(self::table());
        $rows  = (int) $wpdb->query($wpdb->prepare(
            "DELETE FROM {$table} WHERE started_at < %s",
            $cutoff
        ));
        if ($rows > 0) {
            wp_cache_delete('count_all', self::CACHE_GROUP);
            wp_cache_delete('count_today_' . gmdate('Y-m-d'), self::CACHE_GROUP);
            wp_cache_delete('recent_50', self::CACHE_GROUP);
        }
        return $rows;
    }
}
