<?php

namespace App\Models;

use App\Core\Model;

class SiteSeoMetric extends Model
{
    protected static string $table = 'site_seo_metrics';

    public static function existsForMonth(int $siteId, string $metricMonth): bool
    {
        $stmt = self::db()->prepare(
            'SELECT 1 FROM site_seo_metrics WHERE site_id = :site_id AND metric_month = :metric_month LIMIT 1'
        );
        $stmt->execute([
            'site_id' => $siteId,
            'metric_month' => $metricMonth,
        ]);

        return (bool) $stmt->fetchColumn();
    }

    public static function findForMonth(int $siteId, string $metricMonth): ?array
    {
        $stmt = self::db()->prepare(
            'SELECT id, metric_month, top3, top10, top20, top50, traffic, indexed_pages, source_payload, updated_at
             FROM site_seo_metrics
             WHERE site_id = :site_id AND metric_month = :metric_month
             LIMIT 1'
        );
        $stmt->execute([
            'site_id' => $siteId,
            'metric_month' => $metricMonth,
        ]);

        $row = $stmt->fetch();
        return $row ?: null;
    }

    public static function hasDataforseoForMonth(int $siteId, string $metricMonth): bool
    {
        $row = self::findForMonth($siteId, $metricMonth);
        if (!$row) {
            return false;
        }

        $payloadRaw = trim((string) ($row['source_payload'] ?? ''));
        if ($payloadRaw === '') {
            return false;
        }

        $decoded = json_decode($payloadRaw, true);
        if (!is_array($decoded) || !array_key_exists('dataforseo', $decoded)) {
            return false;
        }

        return is_string($decoded['dataforseo']) && trim($decoded['dataforseo']) !== '';
    }

    public static function upsertMonthly(int $siteId, string $metricMonth, array $metrics, ?string $payload = null): void
    {
        $stmt = self::db()->prepare(
            'INSERT INTO site_seo_metrics
                (site_id, metric_month, top3, top10, top20, top50, traffic, indexed_pages, source_payload)
             VALUES
                (:site_id, :metric_month, :top3, :top10, :top20, :top50, :traffic, :indexed_pages, :source_payload)
             ON DUPLICATE KEY UPDATE
                top3 = VALUES(top3),
                top10 = VALUES(top10),
                top20 = VALUES(top20),
                top50 = VALUES(top50),
                traffic = VALUES(traffic),
                indexed_pages = VALUES(indexed_pages),
                source_payload = VALUES(source_payload),
                updated_at = CURRENT_TIMESTAMP'
        );

        $stmt->execute([
            'site_id' => $siteId,
            'metric_month' => $metricMonth,
            'top3' => max(0, (int) ($metrics['top3'] ?? 0)),
            'top10' => max(0, (int) ($metrics['top10'] ?? 0)),
            'top20' => max(0, (int) ($metrics['top20'] ?? 0)),
            'top50' => max(0, (int) ($metrics['top50'] ?? 0)),
            'traffic' => max(0, (int) ($metrics['traffic'] ?? 0)),
            'indexed_pages' => max(0, (int) ($metrics['indexed_pages'] ?? 0)),
            'source_payload' => $payload,
        ]);
    }

    public static function findBySite(int $siteId, int $limit = 12): array
    {
        $stmt = self::db()->prepare(
            'SELECT metric_month, top3, top10, top20, top50, traffic, indexed_pages, created_at, updated_at
             FROM site_seo_metrics
             WHERE site_id = :site_id
             ORDER BY metric_month DESC
             LIMIT :limit_rows'
        );
        $stmt->bindValue(':site_id', $siteId, \PDO::PARAM_INT);
        $stmt->bindValue(':limit_rows', max(1, $limit), \PDO::PARAM_INT);
        $stmt->execute();

        $rows = $stmt->fetchAll();
        return array_reverse($rows);
    }

    public static function latestForSite(int $siteId): ?array
    {
        $stmt = self::db()->prepare(
            'SELECT metric_month, top3, top10, top20, top50, traffic, indexed_pages, updated_at
             FROM site_seo_metrics
             WHERE site_id = :site_id
             ORDER BY metric_month DESC
             LIMIT 1'
        );
        $stmt->execute(['site_id' => $siteId]);
        $result = $stmt->fetch();

        return $result ?: null;
    }

    public static function latestForAllSites(string $sort = 'traffic', string $dir = 'desc'): array
    {
        $allowedSort = ['top3', 'top10', 'top20', 'top50', 'traffic', 'indexed_pages', 'metric_month', 'updated_at', 'site_name'];
        if (!in_array($sort, $allowedSort, true)) {
            $sort = 'traffic';
        }

        $direction = strtolower($dir) === 'asc' ? 'ASC' : 'DESC';

        if ($sort === 'site_name') {
            $orderBy = "s.name {$direction}";
        } else {
            $orderBy = "(m.{$sort} IS NULL) ASC, m.{$sort} {$direction}, s.name ASC";
        }

        $stmt = self::db()->query(
            'SELECT
                s.id AS site_id,
                s.name AS site_name,
                s.url AS site_url,
                s.is_active,
                m.metric_month,
                m.top3,
                m.top10,
                m.top20,
                m.top50,
                m.traffic,
                m.indexed_pages,
                m.updated_at,
                m.created_at,
                pm.metric_month AS prev_metric_month,
                pm.top3 AS prev_top3,
                pm.top10 AS prev_top10,
                pm.top20 AS prev_top20,
                pm.top50 AS prev_top50,
                pm.traffic AS prev_traffic,
                pm.indexed_pages AS prev_indexed_pages
             FROM sites s
             LEFT JOIN site_seo_metrics m
                ON m.id = (
                    SELECT m2.id
                    FROM site_seo_metrics m2
                    WHERE m2.site_id = s.id
                    ORDER BY m2.metric_month DESC
                    LIMIT 1
                )
             LEFT JOIN site_seo_metrics pm
                ON pm.id = (
                    SELECT m3.id
                    FROM site_seo_metrics m3
                    WHERE m3.site_id = s.id
                    ORDER BY m3.metric_month DESC
                    LIMIT 1 OFFSET 1
                )
             ORDER BY ' . $orderBy
        );

        return $stmt->fetchAll();
    }
}
