<?php
namespace Crawl\Repositories;

use Crawl\Lib\Database;

class ProjectRepository
{
    private Database $db;

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

    public function find(int $id): ?array
    {
        return $this->db->fetch(
            'SELECT id, business_name, website_url, ga4_property_id, gsc_site_url, gbp_location_id
             FROM projects WHERE id = ?',
            [$id]
        );
    }

    public function all(): array
    {
        return $this->db->fetchAll('SELECT id, business_name, website_url FROM projects ORDER BY business_name ASC');
    }

    public function allWithHealth(): array
    {
        return $this->db->fetchAll("
            SELECT
                p.id,
                p.business_name,
                p.website_url,
                cr.id           AS latest_run_id,
                cr.domain       AS crawl_domain,
                cr.total_pages  AS crawl_total,
                cr.status       AS crawl_status,
                cr.started_at,
                cr.finished_at,
                COALESCE(SUM(CASE WHEN cp.status_code >= 400 THEN 1 ELSE 0 END), 0) AS error_pages,
                COALESCE(SUM(CASE WHEN (cp.title IS NULL OR cp.title = '') AND cp.status_code = 200 THEN 1 ELSE 0 END), 0) AS missing_title,
                COALESCE(SUM(CASE WHEN (cp.meta_description IS NULL OR cp.meta_description = '') AND cp.status_code = 200 THEN 1 ELSE 0 END), 0) AS missing_meta,
                COALESCE(SUM(CASE WHEN (cp.h1 IS NULL OR cp.h1 = '') AND cp.status_code = 200 THEN 1 ELSE 0 END), 0) AS missing_h1,
                COALESCE(SUM(CASE WHEN cp.word_count < 300 AND cp.word_count > 0 AND cp.status_code = 200 THEN 1 ELSE 0 END), 0) AS thin_content
            FROM projects p
            LEFT JOIN crawl_runs cr ON cr.id = (
                SELECT id FROM crawl_runs WHERE project_id = p.id ORDER BY id DESC LIMIT 1
            )
            LEFT JOIN crawl_pages cp ON cp.crawl_run_id = cr.id
            GROUP BY p.id, p.business_name, p.website_url,
                     cr.id, cr.domain, cr.total_pages, cr.status, cr.started_at, cr.finished_at
            ORDER BY p.business_name ASC
        ");
    }

    public static function computeHealthScore(array $row): int
    {
        $total = (int)($row['crawl_total'] ?? 0);
        if ($total === 0) return 0;

        $deductions  = 0;
        $deductions += min(30, ((int)($row['error_pages']   ?? 0) / $total) * 60);
        $deductions += min(20, ((int)($row['missing_title'] ?? 0) / $total) * 40);
        $deductions += min(15, ((int)($row['missing_meta']  ?? 0) / $total) * 30);
        $deductions += min(10, ((int)($row['missing_h1']    ?? 0) / $total) * 20);
        $deductions += min(10, ((int)($row['thin_content']  ?? 0) / $total) * 20);

        return max(0, (int)round(100 - $deductions));
    }
}
