<?php
/**
 * NestTenders — Tanzania PPRA (NeST) public tender records.
 *
 * Data source: https://nest.go.tz/gateway/nest-data-portal-api (no auth, public OCDS feed)
 *   Populated automatically by:
 *     app/cli/nest_incremental.php   — every 30–60 min (Task Scheduler)
 *     app/cli/nest_backfill.php      — one-off historical pull
 *
 * The controller can still accept manual entries as a fallback if the sync is offline.
 * Real-world scale: hqdspera has ~12,600 rows synced. rsflow.dspera starts empty and fills as sync runs.
 */
class NestTenders extends Model
{
    /** OCID regex — matches ocds-XXX-YYYY-PROJ-FY_START-FY_END-CATEGORY-N or -CATEGORY-N-Sn. */
    public const OCID_FY_PATTERN = '/-(\d{4})-(\d{4})-(NC|[GWS])(-\d+)?(-S\d+)?/i';

    public function listAll(array $filter = []): array
    {
        $db = new DB();
        $where = ['1=1'];
        $params = [];
        if (!empty($filter['category'])) { $where[] = 'category_acronym = :c'; $params[':c'] = $filter['category']; }
        if (!empty($filter['status']))   { $where[] = 'tender_status = :s';    $params[':s'] = $filter['status']; }
        if (!empty($filter['region']))   { $where[] = 'region LIKE :rg';       $params[':rg']= '%' . $filter['region'] . '%'; }
        if (!empty($filter['fy']))       { $where[] = 'financial_year = :fy';  $params[':fy']= $filter['fy']; }
        if (!empty($filter['q'])) {
            $where[] = '(tender_title LIKE :q OR buyer_name LIKE :q OR ocid LIKE :q OR tender_ref LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }
        return $db->mySelect(
            "SELECT t.*, cp.project_no, tt.tender_no AS internal_tender_no
               FROM crm_nest_tenders t
          LEFT JOIN c_projects cp     ON cp.c0d = t.linked_project_id AND cp.deleted = 0
          LEFT JOIN tender_tracker tt ON tt.c0d = t.linked_tender_id  AND tt.deleted = 0
              WHERE " . implode(' AND ', $where) . "
              ORDER BY t.tender_end IS NULL, t.tender_end ASC, t.last_synced_at DESC
              LIMIT 500",
            $params
        ) ?: [];
    }

    public function find(int $id): ?array
    {
        $db = new DB();
        $r = $db->mySelect(
            "SELECT t.*, cp.project_no, tt.tender_no AS internal_tender_no
               FROM crm_nest_tenders t
          LEFT JOIN c_projects cp     ON cp.c0d = t.linked_project_id AND cp.deleted = 0
          LEFT JOIN tender_tracker tt ON tt.c0d = t.linked_tender_id  AND tt.deleted = 0
              WHERE t.c0d = :id",
            [':id' => $id]
        );
        return $r[0] ?? null;
    }

    public function findByOcid(string $ocid): ?array
    {
        $db = new DB();
        $r = $db->mySelect("SELECT * FROM crm_nest_tenders WHERE ocid = :o", [':o' => $ocid]);
        return $r[0] ?? null;
    }

    /** Fallback manual entry when the sync isn't running (schema-lite for typing convenience). */
    public function create(array $d): int
    {
        $db = new DB();
        $ocid = (string)($d['ocid'] ?? ('MANUAL-' . date('YmdHis')));
        $fb = $db->insertUpdate(
            "INSERT INTO crm_nest_tenders
                (ocid, buyer_name, tender_title, description, tender_status, category_acronym, region, amount, currency,
                 tender_end, tender_url, first_seen_at, last_synced_at)
             VALUES (:o, :b, :tt, :d, 'OPEN', :cat, :rg, :a, 'TZS', :te, :u, NOW(), NOW())
             ON DUPLICATE KEY UPDATE last_synced_at = NOW()",
            [
                ':o'  => $ocid,
                ':b'  => (string)($d['procuring_entity'] ?? $d['buyer_name'] ?? ''),
                ':tt' => (string)($d['tender_title'] ?? ''),
                ':d'  => (string)($d['description'] ?? $d['tender_title'] ?? ''),
                ':cat'=> strtoupper(substr((string)($d['category'] ?? ''), 0, 1)),
                ':rg' => (string)($d['region'] ?? ''),
                ':a'  => (float)($d['tender_value'] ?? 0),
                ':te' => !empty($d['closing_date']) ? $d['closing_date'] . ' 00:00:00' : null,
                ':u'  => (string)($d['tender_url'] ?? ''),
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    /**
     * Upsert from a single OCDS release payload.
     * Used by the CLI sync scripts. Returns ['inserted'|'updated'|'skipped', ocid].
     */
    public function upsertFromRelease(array $release): array
    {
        $ocid = (string)($release['ocid'] ?? '');
        if ($ocid === '') return ['skipped', ''];

        $tender     = $release['tender'] ?? [];
        $parties    = $release['parties'] ?? [];
        $buyerParty = null;
        foreach ($parties as $p) {
            $roles = $p['roles'] ?? [];
            if (in_array('buyer', $roles, true) || in_array('procuringEntity', $roles, true)) { $buyerParty = $p; break; }
        }

        // OCID FY filter — configurable, defaults to current + next FY
        if (defined('NEST_FY_FILTER') && NEST_FY_FILTER !== '' && !preg_match(NEST_FY_FILTER, $ocid)) {
            return ['skipped', $ocid];
        }

        // FY + category — from CLI-enriched extras first, else parse OCID
        $ext        = $release['_rsflow_ext'] ?? [];
        $fyStart    = $ext['financialYear'] ?? null;
        $catAcronym = $ext['tenderCategoryAcronym'] ?? null;
        $subCat     = $ext['subCategoryAcronym'] ?? null;
        if (!$fyStart || !$catAcronym) {
            if (preg_match(self::OCID_FY_PATTERN, $ocid, $m)) {
                if (!$fyStart)    $fyStart    = $m[1] . '/' . $m[2];
                if (!$catAcronym) $catAcronym = strtoupper($m[3]) === 'NC' ? 'N' : strtoupper($m[3]);
                if (!$subCat)     $subCat     = trim(($m[4] ?? '') . ($m[5] ?? ''), '-');
            }
        }
        if ($catAcronym) $catAcronym = strtoupper((string)$catAcronym);
        if ($catAcronym === 'NC') $catAcronym = 'N';

        $val    = $tender['value'] ?? [];
        $period = $tender['tenderPeriod'] ?? [];
        $bidP   = $tender['awardPeriod'] ?? [];

        $data = [
            ':o'   => $ocid,
            ':bid' => (string)($buyerParty['id'] ?? ''),
            ':bn'  => (string)($buyerParty['name'] ?? ($tender['procuringEntity']['name'] ?? '')),
            ':tr'  => (string)($tender['id'] ?? ''),
            ':tt'  => (string)($tender['title'] ?? ''),
            ':desc'=> (string)($tender['description'] ?? ''),
            ':ts'  => (string)($tender['status'] ?? ''),
            ':pm'  => (string)($tender['procurementMethod'] ?? ''),
            ':pmc' => (string)($tender['procurementCategory'] ?? ''),
            ':pmd' => (string)($tender['procurementMethodDetails'] ?? ''),
            ':cat' => $catAcronym,
            ':sub' => $subCat,
            ':fy'  => $fyStart,
            ':rg'  => (string)($buyerParty['address']['region'] ?? $tender['additionalClassifications'][0]['description'] ?? ''),
            ':a'   => isset($val['amount']) ? (float)$val['amount'] : null,
            ':cur' => (string)($val['currency'] ?? 'TZS'),
            ':ts_start' => !empty($period['startDate']) ? date('Y-m-d H:i:s', strtotime($period['startDate'])) : null,
            ':te'  => !empty($period['endDate'])   ? date('Y-m-d H:i:s', strtotime($period['endDate']))   : null,
            ':bo'  => !empty($bidP['startDate'])    ? date('Y-m-d H:i:s', strtotime($bidP['startDate']))    : null,
            ':rd'  => !empty($release['date'])      ? date('Y-m-d H:i:s', strtotime($release['date']))      : null,
            ':url' => (string)($tender['documents'][0]['url'] ?? ''),
            ':rr'  => json_encode($release, JSON_UNESCAPED_UNICODE),
            ':rp'  => json_encode($parties, JSON_UNESCAPED_UNICODE),
        ];

        $db = new DB();
        $existing = $this->findByOcid($ocid);
        if ($existing) {
            $db->insertUpdate(
                "UPDATE crm_nest_tenders SET
                    buyer_id = :bid, buyer_name = :bn, tender_ref = :tr, tender_title = :tt, description = :desc,
                    tender_status = :ts, procurement_method = :pm, procurement_method_cat = :pmc, procurement_method_detail = :pmd,
                    category_acronym = :cat, sub_category_acronym = :sub, financial_year = :fy, region = :rg,
                    amount = :a, currency = :cur, tender_start = :ts_start, tender_end = :te, bid_opening = :bo,
                    release_date = :rd, tender_url = :url, raw_release = :rr, raw_parties = :rp,
                    last_synced_at = NOW()
                  WHERE ocid = :o",
                $data
            );
            return ['updated', $ocid];
        }
        $db->insertUpdate(
            "INSERT INTO crm_nest_tenders
                (ocid, buyer_id, buyer_name, tender_ref, tender_title, description, tender_status, procurement_method,
                 procurement_method_cat, procurement_method_detail, category_acronym, sub_category_acronym, financial_year,
                 region, amount, currency, tender_start, tender_end, bid_opening, release_date, tender_url,
                 raw_release, raw_parties, first_seen_at, last_synced_at)
             VALUES (:o, :bid, :bn, :tr, :tt, :desc, :ts, :pm, :pmc, :pmd, :cat, :sub, :fy, :rg, :a, :cur,
                     :ts_start, :te, :bo, :rd, :url, :rr, :rp, NOW(), NOW())",
            $data
        );
        return ['inserted', $ocid];
    }

    /** Latest release_date already in DB — used as `since` for incremental sync. */
    public function lastReleaseDate(): ?string
    {
        $db = new DB();
        $r = $db->mySelect("SELECT MAX(release_date) AS d FROM crm_nest_tenders WHERE release_date IS NOT NULL", []);
        return $r[0]['d'] ?? null;
    }

    /**
     * Convert a NeST tender into a business lead (source = NEST).
     * (Kept for backwards compat — new preferred path is convert direct to Tenders/add?from_nest=…)
     */
    public function convertToLead(int $tenderId, int $userId, string $userName): int
    {
        $t = $this->find($tenderId);
        if (!$t) throw new RuntimeException('Tender not found.');
        if (!class_exists('BusinessLeads')) require ROOT . '/app/models/BusinessLeads.php';
        $bl = new BusinessLeads();
        $leadId = $bl->create([
            'lead_name'         => 'NeST · ' . $t['tender_title'],
            'customer_name'     => (string)$t['buyer_name'],
            'lead_source'       => 'NEST',
            'project_type'      => $this->categoryToProjectType((string)($t['category_acronym'] ?? '')),
            'opportunity_value' => (float)$t['amount'],
            'status'            => 'QUALIFYING',
            'next_action_date'  => $t['tender_end'] ? date('Y-m-d', strtotime($t['tender_end'])) : null,
            'next_action_note'  => 'Prepare bid before closing · ' . $t['tender_url'],
        ], $userId, $userName);
        $db = new DB();
        $db->insertUpdate("UPDATE crm_nest_tenders SET tender_status = 'pursuing' WHERE c0d = :id", [':id' => $tenderId]);
        return $leadId;
    }

    private function categoryToProjectType(string $acronym): string
    {
        return match (strtoupper($acronym)) {
            'G'     => 'goods',
            'W'     => 'works',
            'S'     => 'services',
            'N'     => 'services',   // Non-consulting → services in our taxonomy
            default => 'goods',
        };
    }

    /* ── Sync log helpers (used by CLI + admin dashboard) ──────────────── */

    public function logStart(string $syncType, ?string $since, ?int $pageStart): int
    {
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO crm_nest_sync_log (sync_type, since_param, page_start, started_at, status)
             VALUES (:st, :sn, :ps, NOW(), 'running')",
            [':st' => $syncType, ':sn' => $since, ':ps' => $pageStart]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function logFinish(int $logId, array $counts, string $status = 'done', ?string $notes = null, ?array $skipped = null): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE crm_nest_sync_log SET
                records_fetched = :rf, records_inserted = :ri, records_updated = :ru, records_skipped = :rs,
                error_count = :ec, finished_at = NOW(), status = :st, notes = :nt, skipped_urls = :sk,
                cursor_end = :ce, page_end = :pe
              WHERE c0d = :id",
            [
                ':rf' => (int)($counts['fetched']  ?? 0),
                ':ri' => (int)($counts['inserted'] ?? 0),
                ':ru' => (int)($counts['updated']  ?? 0),
                ':rs' => (int)($counts['skipped']  ?? 0),
                ':ec' => (int)($counts['errors']   ?? 0),
                ':st' => $status,
                ':nt' => $notes,
                ':sk' => $skipped ? json_encode($skipped) : null,
                ':ce' => (string)($counts['cursor_end'] ?? ''),
                ':pe' => (int)($counts['page_end'] ?? 0) ?: null,
                ':id' => $logId,
            ]
        );
    }

    public function recentSyncLogs(int $limit = 40): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT * FROM crm_nest_sync_log ORDER BY started_at DESC LIMIT " . (int)$limit,
            []
        ) ?: [];
    }
}
