<?php
/**
 * One-time import of project config from "Performance Tracker - Milan.xlsx"
 * Data Source sheet → projects table
 *
 * Matching logic:
 * 1. Match by GSC URL domain to existing projects.website_url
 * 2. Match by GA4 ID to existing projects.ga4_property_id
 * 3. If no match → create new project
 *
 * Events 1-10 are auto-grouped into lead categories using keyword matching.
 */

define('ANALYTICS_ROOT', __DIR__ . '/..');
define('ANALYTICS_VIEWS', ANALYTICS_ROOT . '/views');
define('ROOT_PATH', ANALYTICS_ROOT . '/../Audit');

require_once ANALYTICS_ROOT . '/lib/Env.php';
\Analytics\Lib\Env::load(ROOT_PATH . '/.env');

spl_autoload_register(function (string $class): void {
    $prefix = 'Analytics\\';
    if (strpos($class, $prefix) !== 0) return;
    $file = ANALYTICS_ROOT . '/' . str_replace('\\', '/', substr($class, strlen($prefix))) . '.php';
    if (file_exists($file)) require_once $file;
});

if (file_exists(ANALYTICS_ROOT . '/vendor/autoload.php')) {
    require_once ANALYTICS_ROOT . '/vendor/autoload.php';
}

use PhpOffice\PhpSpreadsheet\IOFactory;

$xlsxPath = ANALYTICS_ROOT . '/../Performance Tracker - Milan.xlsx';
if (!file_exists($xlsxPath)) {
    die("ERROR: Excel file not found at: $xlsxPath\n");
}

echo "Loading Excel: $xlsxPath\n";
$spreadsheet = IOFactory::load($xlsxPath);
$ws          = $spreadsheet->getSheetByName('Data Source');
if (!$ws) {
    die("ERROR: Sheet 'Data Source' not found.\n");
}

// Read headers from row 1
$headers = [];
$maxCol  = $ws->getHighestColumn();
$maxColIdx = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($maxCol);
for ($c = 1; $c <= $maxColIdx; $c++) {
    $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($c);
    $h = trim((string)$ws->getCell($colLetter . '1')->getValue());
    if ($h) $headers[$h] = $c;
}

echo 'Headers found: ' . implode(', ', array_keys($headers)) . "\n\n";

$db       = \Analytics\Lib\Database::getInstance();
$maxRow   = $ws->getHighestRow();
$created  = 0;
$updated  = 0;
$skipped  = 0;

/**
 * Auto-group events into lead categories based on keywords.
 */
function autoGroupEvents(array $events): array
{
    $patterns = [
        'calls'    => '/call|phone/i',
        'whatsapp' => '/whatsapp|wa/i',
        'email'    => '/mail|email/i',
        'forms'    => '/form|submit|enquir|inquiry|contact/i',
        'chats'    => '/chat|live\s?chat/i',
        'gmb'      => '/gmb|google\s?business/i',
    ];

    $categories = [];
    $used = [];

    foreach ($patterns as $key => $regex) {
        $matched = [];
        foreach ($events as $event) {
            if (!$event || in_array($event, $used, true)) continue;
            if (preg_match($regex, $event)) {
                $matched[] = $event;
                $used[]    = $event;
            }
        }
        if ($matched) {
            $categories[] = [
                'key'          => $key,
                'label'        => ucfirst($key),
                'events'       => $matched,
                'cpl_override' => null,
            ];
        }
    }

    // Remaining events → "Other"
    $remaining = array_filter($events, fn($e) => $e && !in_array($e, $used, true));
    if ($remaining) {
        $categories[] = [
            'key'          => 'other',
            'label'        => 'Other',
            'events'       => array_values($remaining),
            'cpl_override' => null,
        ];
    }

    return $categories;
}

for ($row = 2; $row <= $maxRow; $row++) {
    $get = function (string $col) use ($ws, $headers, $row): string {
        if (!isset($headers[$col])) return '';
        $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($headers[$col]);
        $cell      = $ws->getCell($colLetter . $row);
        $val       = $cell->getValue();
        // Handle Excel date serial numbers
        if (is_float($val) && \PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
            return date('Y-m-d', \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($val));
        }
        return trim((string)$val);
    };

    $businessName = $get('Business Name');
    if (!$businessName) continue;

    $businessId  = $get('Business ID');
    $ga4Id       = $get('GA4 ID');
    $gscUrl      = $get('GSC URL');
    $brandRegex  = $get('Brand Term Regex');
    $aiOverEvent = $get('Ai Overview Event Name');
    $category    = $get('Business Category');
    $subCat      = $get('Business Sub-Category');
    $revManaged  = (float)str_replace(',', '', $get('Revenue Managed') ?: '0');
    $seoHead     = $get('SEO Head');
    $manager     = $get('Manager');
    $srAnalyst   = $get('Sr Analyst');
    $onboardRaw  = $get('Onboard Date');

    // Parse onboard date
    $onboardDate = '';
    if ($onboardRaw) {
        $ts = strtotime($onboardRaw);
        if ($ts) $onboardDate = date('Y-m-d', $ts);
    }

    // Collect events 1–10
    $eventNames = [];
    for ($i = 1; $i <= 10; $i++) {
        $ev = $get("Event Name $i");
        if ($ev) $eventNames[] = $ev;
    }

    $leadCategories = autoGroupEvents($eventNames);

    // ── Find existing project ─────────────────────────────────────────────────
    $existing = null;

    // Match by GA4 ID
    if ($ga4Id) {
        $existing = $db->fetch('SELECT id FROM projects WHERE ga4_property_id = ? LIMIT 1', [$ga4Id]);
    }

    // Match by GSC URL domain if no GA4 match
    if (!$existing && $gscUrl) {
        $domain = parse_url($gscUrl, PHP_URL_HOST) ?: $gscUrl;
        $domain = preg_replace('/^www\./', '', $domain);
        $existing = $db->fetch(
            "SELECT id FROM projects WHERE website_url LIKE ? OR website_url LIKE ? LIMIT 1",
            ["%{$domain}%", "%{$domain}%"]
        );
    }

    // Match by business name
    if (!$existing) {
        $existing = $db->fetch(
            'SELECT id FROM projects WHERE business_name = ? LIMIT 1',
            [$businessName]
        );
    }

    $analyticsData = [
        'business_id'           => $businessId,
        'business_category'     => $category,
        'business_sub_category' => $subCat,
        'revenue_managed'       => $revManaged ?: null,
        'seo_head'              => $seoHead,
        'manager_name'          => $manager,
        'sr_analyst'            => $srAnalyst,
        'onboard_date'          => $onboardDate ?: null,
        'brand_term_regex'      => $brandRegex,
        'ai_overview_event'     => $aiOverEvent,
        'lead_categories'       => json_encode($leadCategories),
        'ga4_property_id'       => $ga4Id,
        'gsc_site_url'          => $gscUrl,
        'updated_at'            => date('Y-m-d H:i:s'),
    ];

    if ($existing) {
        $db->update('projects', $analyticsData, 'id = :id', ['id' => (int)$existing['id']]);
        echo "UPDATED  [{$businessId}] $businessName (id={$existing['id']}) — "
           . count($leadCategories) . " lead categories\n";
        $updated++;
    } else {
        $createData = array_merge($analyticsData, [
            'business_name' => $businessName,
            'website_url'   => $gscUrl,
            'is_active'     => 1,
            'created_at'    => date('Y-m-d H:i:s'),
        ]);
        $newId = $db->insert('projects', $createData);
        echo "CREATED  [{$businessId}] $businessName (id=$newId) — "
           . count($leadCategories) . " lead categories\n";
        $created++;
    }
}

echo "\n=== DONE ===\n";
echo "Updated: $updated\n";
echo "Created: $created\n";
echo "Skipped: $skipped\n";
