<?php
namespace Analytics\Repositories;

use Analytics\Lib\Database;

class SnapshotRepository
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function find(int $projectId, string $start, string $end): ?array
    {
        $row = $this->db->fetch(
            'SELECT * FROM analytics_snapshots WHERE project_id = ? AND period_start = ? AND period_end = ?',
            [$projectId, $start, $end]
        );
        return $row ? $this->decode($row) : null;
    }

    public function latest(int $projectId): ?array
    {
        $row = $this->db->fetch(
            'SELECT * FROM analytics_snapshots WHERE project_id = ? ORDER BY period_end DESC, id DESC LIMIT 1',
            [$projectId]
        );
        return $row ? $this->decode($row) : null;
    }

    // Exact previous period — same duration, immediately before.
    public function previous(int $projectId, string $start, string $end): ?array
    {
        $days      = (new \DateTime($start))->diff(new \DateTime($end))->days;
        $prevEnd   = date('Y-m-d', strtotime($start . ' -1 day'));
        $prevStart = date('Y-m-d', strtotime($prevEnd . " -{$days} days"));
        return $this->find($projectId, $prevStart, $prevEnd);
    }

    /**
     * Return the comparison snapshot for a given mode using real data only.
     *
     * Modes:
     *   'period' — same duration immediately before (identical to previous())
     *   'month'  — same day-of-month range one calendar month earlier
     *   'year'   — same dates one year back
     *
     * Returns null when no exact snapshot exists for the computed range.
     */
    public function previousByMode(int $projectId, string $start, string $end, string $mode = 'period'): ?array
    {
        switch ($mode) {
            case 'year':
                $cmpStart = date('Y-m-d', strtotime($start . ' -1 year'));
                $cmpEnd   = date('Y-m-d', strtotime($end   . ' -1 year'));
                return $this->find($projectId, $cmpStart, $cmpEnd);

            case 'month':
                $cmpStart = date('Y-m-d', strtotime($start . ' -1 month'));
                $cmpEnd   = date('Y-m-d', strtotime($end   . ' -1 month'));
                return $this->find($projectId, $cmpStart, $cmpEnd);

            default: // 'period'
                return $this->previous($projectId, $start, $end);
        }
    }

    /**
     * Compute the comparison date range for a given mode (used by view / fetch service).
     * Returns ['start' => ..., 'end' => ...].
     */
    public function comparisonRange(string $start, string $end, string $mode = 'period'): array
    {
        switch ($mode) {
            case 'year':
                return [
                    'start' => date('Y-m-d', strtotime($start . ' -1 year')),
                    'end'   => date('Y-m-d', strtotime($end   . ' -1 year')),
                ];
            case 'month':
                return [
                    'start' => date('Y-m-d', strtotime($start . ' -1 month')),
                    'end'   => date('Y-m-d', strtotime($end   . ' -1 month')),
                ];
            default: // 'period'
                $days    = (new \DateTime($start))->diff(new \DateTime($end))->days;
                $prevEnd = date('Y-m-d', strtotime($start . ' -1 day'));
                return [
                    'start' => date('Y-m-d', strtotime($prevEnd . " -{$days} days")),
                    'end'   => $prevEnd,
                ];
        }
    }

    public function listForProject(int $projectId, int $limit = 12): array
    {
        return array_map([$this, 'decode'], $this->db->fetchAll(
            'SELECT * FROM analytics_snapshots WHERE project_id = ? ORDER BY period_end DESC LIMIT ?',
            [$projectId, $limit]
        ));
    }

    // Latest snapshot for each project — used by the dashboard
    public function latestPerProject(array $projectIds): array
    {
        if (empty($projectIds)) return [];
        $ph   = implode(',', array_fill(0, count($projectIds), '?'));
        $rows = $this->db->fetchAll(
            "SELECT s.* FROM analytics_snapshots s
             INNER JOIN (
               SELECT project_id, MAX(period_end) AS max_end
               FROM analytics_snapshots WHERE project_id IN ({$ph}) GROUP BY project_id
             ) m ON s.project_id = m.project_id AND s.period_end = m.max_end
             ORDER BY s.project_id",
            $projectIds
        );
        $map = [];
        foreach ($rows as $row) {
            $map[(int)$row['project_id']] = $this->decode($row);
        }
        return $map;
    }

    public function upsert(int $projectId, string $periodType, string $start, string $end, array $data): int
    {
        // Delete existing for this exact period before re-inserting (re-fetch)
        $this->db->query(
            'DELETE FROM analytics_snapshots WHERE project_id = ? AND period_start = ? AND period_end = ?',
            [$projectId, $start, $end]
        );

        if (isset($data['lead_category_totals']) && is_array($data['lead_category_totals'])) {
            $data['lead_category_totals'] = json_encode($data['lead_category_totals']);
        }

        $row = array_merge($data, [
            'project_id'   => $projectId,
            'period_type'  => $periodType,
            'period_start' => $start,
            'period_end'   => $end,
            'fetched_at'   => date('Y-m-d H:i:s'),
            'created_at'   => date('Y-m-d H:i:s'),
        ]);

        return $this->db->insert('analytics_snapshots', $row);
    }

    private function decode(array $row): array
    {
        if (!empty($row['lead_category_totals']) && is_string($row['lead_category_totals'])) {
            $row['lead_category_totals'] = json_decode($row['lead_category_totals'], true) ?? [];
        }
        return $row;
    }
}
