<?php
/**
 * BusinessLeads — CRM prospect pipeline.
 *
 * Stages: NEW → QUALIFYING → QUOTING → SUBMITTED → WON / LOST
 * Convert-to-project fires from WON only. Sets c_projects.origin from lead_source
 * and c_projects.crm_status = PENDING so it still walks the CRM_PROJECT_ONBOARDING
 * chain before the Ops gate opens.
 */
class BusinessLeads extends Model
{
    public const STAGES = ['NEW','QUALIFYING','QUOTING','SUBMITTED','WON','LOST'];

    public function listAll(array $filter = []): array
    {
        $db = new DB();
        $where = ['bl.deleted = 0'];
        $params = [];
        if (!empty($filter['stage']))  { $where[] = 'bl.status = :st'; $params[':st'] = $filter['stage']; }
        if (!empty($filter['owner']))  { $where[] = 'bl.owner_id = :o'; $params[':o']  = (int)$filter['owner']; }
        if (!empty($filter['q'])) {
            $where[] = '(bl.lead_name LIKE :q OR bl.customer_name LIKE :q OR bl.next_action_note LIKE :q)';
            $params[':q'] = '%' . $filter['q'] . '%';
        }
        if (!empty($filter['overdue'])) {
            $where[] = "bl.next_action_date IS NOT NULL AND bl.next_action_date < CURDATE() AND bl.status NOT IN ('WON','LOST')";
        }
        return $db->mySelect(
            "SELECT bl.*, cp.project_no, cp.crm_status
               FROM business_leads bl
          LEFT JOIN c_projects cp ON cp.c0d = bl.linked_project_id AND cp.deleted = 0
              WHERE " . implode(' AND ', $where) . "
              ORDER BY bl.status = 'WON' DESC, bl.next_action_date IS NULL, bl.next_action_date ASC, bl.created_on DESC",
            $params
        ) ?: [];
    }

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

    public function followUps(int $leadId): array
    {
        $db = new DB();
        return $db->mySelect(
            "SELECT f.*, u.full_name AS creator_name
               FROM business_leads_follow_ups f
          LEFT JOIN users u ON u.c0d = f.created_by
              WHERE f.lead_id = :id
              ORDER BY f.follow_date DESC, f.c0d DESC",
            [':id' => $leadId]
        ) ?: [];
    }

    public function create(array $d, int $userId, string $userName): int
    {
        $db = new DB();
        $fb = $db->insertUpdate(
            "INSERT INTO business_leads
                (lead_name, customer_id, customer_name, lead_source, project_type,
                 opportunity_value, currency, status, next_action_date, next_action_note,
                 owner_id, owner_name, created_by, created_on)
             VALUES (:nm, :cid, :cn, :src, :pt, :ov, 'TZS', :s, :nad, :nan, :o, :on, :u, NOW())",
            [
                ':nm'  => (string)($d['lead_name'] ?? ''),
                ':cid' => (int)($d['customer_id'] ?? 0),
                ':cn'  => (string)($d['customer_name'] ?? ''),
                ':src' => (string)($d['lead_source'] ?? 'DIRECT'),
                ':pt'  => (string)($d['project_type'] ?? 'goods'),
                ':ov'  => (float)($d['opportunity_value'] ?? 0),
                ':s'   => (string)($d['status'] ?? 'NEW'),
                ':nad' => !empty($d['next_action_date']) ? (string)$d['next_action_date'] : null,
                ':nan' => (string)($d['next_action_note'] ?? ''),
                ':o'   => $userId,
                ':on'  => $userName,
                ':u'   => $userId,
            ]
        );
        return (int)($fb['last_id'] ?? 0);
    }

    public function update(int $id, array $d, int $userId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "UPDATE business_leads
                SET lead_name = :nm, customer_id = :cid, customer_name = :cn,
                    lead_source = :src, project_type = :pt,
                    opportunity_value = :ov, status = :s,
                    next_action_date = :nad, next_action_note = :nan,
                    edited_by = :u, edited_on = NOW()
              WHERE c0d = :id",
            [
                ':nm'  => (string)($d['lead_name'] ?? ''),
                ':cid' => (int)($d['customer_id'] ?? 0),
                ':cn'  => (string)($d['customer_name'] ?? ''),
                ':src' => (string)($d['lead_source'] ?? 'DIRECT'),
                ':pt'  => (string)($d['project_type'] ?? 'goods'),
                ':ov'  => (float)($d['opportunity_value'] ?? 0),
                ':s'   => (string)($d['status'] ?? 'NEW'),
                ':nad' => !empty($d['next_action_date']) ? (string)$d['next_action_date'] : null,
                ':nan' => (string)($d['next_action_note'] ?? ''),
                ':u'   => $userId,
                ':id'  => $id,
            ]
        );
    }

    public function addFollowUp(int $leadId, string $date, string $note, int $userId): void
    {
        $db = new DB();
        $db->insertUpdate(
            "INSERT INTO business_leads_follow_ups (lead_id, follow_date, follow_note, created_by, created_on)
             VALUES (:id, :d, :n, :u, NOW())",
            [':id' => $leadId, ':d' => $date, ':n' => $note, ':u' => $userId]
        );
    }

    public function setStage(int $leadId, string $stage, int $userId): void
    {
        if (!in_array($stage, self::STAGES, true)) throw new InvalidArgumentException('Invalid stage.');
        $db = new DB();
        $db->insertUpdate("UPDATE business_leads SET status = :s, edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':s' => $stage, ':u' => $userId, ':id' => $leadId]);
    }

    /**
     * Convert a WON lead to a c_projects row.
     * origin defaults to DIRECT_AWARD unless the lead came from a tender source.
     * Sets crm_status = PENDING so the Onboarding chain gates the project.
     */
    public function convertToProject(int $leadId, int $userId, string $userName): int
    {
        $lead = $this->find($leadId);
        if (!$lead) throw new RuntimeException('Lead not found.');
        if ($lead['status'] !== 'WON') throw new RuntimeException('Only WON leads can convert. Set stage to WON first.');
        if ((int)$lead['linked_project_id'] > 0) throw new RuntimeException('Lead already converted to project #' . (int)$lead['linked_project_id'] . '.');

        $db = new DB();
        $seq = ((int)$db->mySelect("SELECT COUNT(*) n FROM c_projects", [])[0]['n']) + 1;
        $projectNo = 'P-' . date('Y') . '-' . str_pad((string)$seq, 4, '0', STR_PAD_LEFT);

        // Origin from source
        $origin = 'DIRECT_AWARD';
        $justification = 'Converted from CRM lead #' . $leadId . ' · ' . $lead['lead_source'];
        if (stripos((string)$lead['lead_source'], 'TENDER') !== false || stripos((string)$lead['lead_source'], 'BID') !== false || stripos((string)$lead['lead_source'], 'BDM') !== false) {
            $origin = 'TENDER_BDM';
            $justification = null;
        } elseif (stripos((string)$lead['lead_source'], 'NEST') !== false) {
            $origin = 'TENDER_PUBLIC';
            $justification = null;
        }

        $fb = $db->insertUpdate(
            "INSERT INTO c_projects
                (project_no, project_name, project_type, project_customer_id, project_customer,
                 origin, direct_award_justification, contract_value, currency,
                 crm_status, project_status, project_lifecycle_stage,
                 created_by, created_on)
             VALUES (:pn, :nm, :pt, :cid, :cn, :o, :da, :cv, 'TZS',
                     'PENDING', 'ACTIVE', 'ONBOARDING', :u, NOW())",
            [
                ':pn'  => $projectNo,
                ':nm'  => $lead['lead_name'],
                ':pt'  => $lead['project_type'],
                ':cid' => (int)$lead['customer_id'],
                ':cn'  => (string)$lead['customer_name'],
                ':o'   => $origin,
                ':da'  => $justification,
                ':cv'  => (float)$lead['opportunity_value'],
                ':u'   => $userId,
            ]
        );
        $projectId = (int)($fb['last_id'] ?? 0);

        $db->insertUpdate(
            "UPDATE business_leads SET linked_project_id = :pid, edited_by = :u, edited_on = NOW() WHERE c0d = :id",
            [':pid' => $projectId, ':u' => $userId, ':id' => $leadId]
        );

        // Note: Onboarding chain (CRM_PROJECT_ONBOARDING) is separately submitted from the Onboarding queue
        // once the project's onboarding checklist is complete. Convert only creates the project row.

        return $projectId;
    }

    /** Kanban buckets — for the Bids pipeline board. */
    public function kanban(array $filter = []): array
    {
        $out = [];
        foreach (self::STAGES as $s) $out[$s] = [];
        foreach ($this->listAll($filter) as $l) {
            $out[$l['status']][] = $l;
        }
        return $out;
    }
}
