<?php
namespace Analytics\Repositories;

use Analytics\Lib\Database;

class ProjectRepository
{
    private Database $db;

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

    public function all(): array
    {
        return array_map([$this, 'decode'], $this->db->fetchAll(
            "SELECT id, business_id, business_name, website_url,
                    business_category, business_sub_category,
                    revenue_managed, seo_head, manager_name, sr_analyst,
                    onboard_date, brand_term_regex, ai_overview_event,
                    lead_categories, ga4_property_id, gsc_site_url, gbp_location_id,
                    is_active, created_at
             FROM projects
             ORDER BY business_name ASC"
        ));
    }

    public function find(int $id): ?array
    {
        $row = $this->db->fetch(
            "SELECT id, business_id, business_name, website_url,
                    business_category, business_sub_category,
                    revenue_managed, seo_head, manager_name, sr_analyst,
                    onboard_date, brand_term_regex, ai_overview_event,
                    lead_categories, ga4_property_id, gsc_site_url, gbp_location_id,
                    is_active
             FROM projects WHERE id = ?",
            [$id]
        );
        return $row ? $this->decode($row) : null;
    }

    // All projects that have GA4 or GSC connected (OAuth tokens exist)
    public function allConnected(): array
    {
        return array_map([$this, 'decode'], $this->db->fetchAll(
            "SELECT DISTINCT p.id, p.business_id, p.business_name, p.website_url,
                    p.business_category, p.business_sub_category,
                    p.revenue_managed, p.seo_head, p.manager_name, p.sr_analyst,
                    p.onboard_date, p.brand_term_regex, p.ai_overview_event,
                    p.lead_categories, p.ga4_property_id, p.gsc_site_url
             FROM projects p
             WHERE p.is_active = 1
               AND (
                    (
                        COALESCE(NULLIF(TRIM(p.ga4_property_id), ''), '') <> ''
                        AND EXISTS (
                            SELECT 1
                            FROM project_oauth_tokens t
                            WHERE t.service = 'ga4' AND (t.project_id = p.id OR t.project_id = 0)
                        )
                    )
                    OR
                    (
                        COALESCE(NULLIF(TRIM(p.gsc_site_url), ''), '') <> ''
                        AND EXISTS (
                            SELECT 1
                            FROM project_oauth_tokens t
                            WHERE t.service = 'gsc' AND (t.project_id = p.id OR t.project_id = 0)
                        )
                    )
               )
             ORDER BY p.business_name ASC"
        ));
    }

    public function updateAnalyticsConfig(int $id, array $data): void
    {
        if (isset($data['lead_categories']) && is_array($data['lead_categories'])) {
            $data['lead_categories'] = json_encode($data['lead_categories']);
        }
        $data['updated_at'] = date('Y-m-d H:i:s');
        $this->db->update('projects', $data, 'id = :id', ['id' => $id]);
    }

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