<?php
// Controllers/KPIController.php
// KPI business logic: funnel metrics, trendlines, role filters.
// Assumes PDO connection is provided by api/_pdo_db_connect.php when needed.

class KPIController
{
    protected $pdo;
    protected $params = [];

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Get KPI summary for a date range and optional role filters.
     *
     * @param string $start YYYY-MM-DD
     * @param string $end   YYYY-MM-DD
     * @param array  $filters ['role' => 'Captain'|'SolarDoot'|'StateHead'|'Admin', 'user_id' => int, 'team_id' => int]
     * @return array
     */
    public function getKpiSummary($start, $end, $filters = [])
    {
        // Normalize dates
        $startDate = $start . ' 00:00:00';
        $endDate   = $end . ' 23:59:59';

        // Fetch funnel counts
        $leads = $this->countLeads($startDate, $endDate, $filters);
        $followups = $this->countFollowups($startDate, $endDate, $filters);
        $proposals = $this->countByCustomerStatus($startDate, $endDate, ['Proposal', 'Quoted'], $filters);
        $closures = $this->countByCustomerStatus($startDate, $endDate, ['Closed', 'Won'], $filters);

        // Conversion rates (safe)
        $leadToFollowup = $leads ? round(($followups / $leads) * 100, 2) : 0.0;
        $followupToProposal = $followups ? round(($proposals / $followups) * 100, 2) : 0.0;
        $proposalToClose = $proposals ? round(($closures / $proposals) * 100, 2) : 0.0;
        $overallConversion = $leads ? round(($closures / $leads) * 100, 2) : 0.0;

        // Trendline sample (7-day counts)
        $trend = $this->getTrend($startDate, $endDate, $filters);

        return [
            'period' => ['start' => $start, 'end' => $end],
            'counts' => [
                'leads' => (int)$leads,
                'followups' => (int)$followups,
                'proposals' => (int)$proposals,
                'closures' => (int)$closures
            ],
            'conversion' => [
                'lead_to_followup_pct' => $leadToFollowup,
                'followup_to_proposal_pct' => $followupToProposal,
                'proposal_to_close_pct' => $proposalToClose,
                'overall_conversion_pct' => $overallConversion
            ],
            'trend' => $trend
        ];
    }

    /**
     * Count leads (sd_customers created) in range with optional filters.
     */
    protected function countLeads($start, $end, $filters = [])
    {
        $sql = "SELECT COUNT(*) as cnt FROM sd_customers WHERE created_at BETWEEN :start AND :end";
        $conds = [];
        $params = [':start' => $start, ':end' => $end];

        $this->applyFiltersToQuery($filters, $conds, $params);

        if ($conds) $sql .= ' AND ' . implode(' AND ', $conds);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Count followups in range
     */
    protected function countFollowups($start, $end, $filters = [])
    {
        $sql = "SELECT COUNT(*) as cnt FROM sd_followups WHERE created_at BETWEEN :start AND :end";
        $conds = [];
        $params = [':start' => $start, ':end' => $end];

        // If filtering by assigned user/team, join to customers via customer_id
        if ($this->hasAssignmentFilter($filters)) {
            $sql = "SELECT COUNT(f.id) as cnt
                    FROM sd_followups f
                    INNER JOIN sd_customers c ON c.id = f.customer_id
                    WHERE f.created_at BETWEEN :start AND :end";
            $this->applyFiltersToQuery($filters, $conds, $params, 'c'); // apply filters on customer alias 'c'
        } else {
            $this->applyFiltersToQuery($filters, $conds, $params);
        }

        if ($conds) $sql .= ' AND ' . implode(' AND ', $conds);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Count customers by status (array of statuses) created in range or transitioned in range.
     * This implementation counts customers created in range who currently have those statuses.
     */
    protected function countByCustomerStatus($start, $end, $statuses = [], $filters = [])
    {
        if (!$statuses) return 0;
        $placeholders = [];
        $params = [':start' => $start, ':end' => $end];
        foreach ($statuses as $idx => $st) {
            $k = ':st' . $idx;
            $placeholders[] = $k;
            $params[$k] = $st;
        }

        // Use created_at window for customers who were created within the period.
        $sql = "SELECT COUNT(*) FROM sd_customers WHERE created_at BETWEEN :start AND :end AND customer_status IN (" . implode(',', $placeholders) . ")";
        $conds = [];
        $this->applyFiltersToQuery($filters, $conds, $params);

        if ($conds) $sql .= ' AND ' . implode(' AND ', $conds);

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int)$stmt->fetchColumn();
    }

    /**
     * Basic trend aggregator by day over the interval (max 90 days recommended).
     * Returns an array of ['date' => 'YYYY-MM-DD', 'leads' => int, 'followups' => int]
     */
    protected function getTrend($start, $end, $filters = [])
    {
        // limit to 90 days for performance
        $startDt = new DateTime(substr($start,0,10));
        $endDt   = new DateTime(substr($end,0,10));
        $interval = $startDt->diff($endDt)->days;
        if ($interval > 90) {
            // shrink to last 90 days ending at $end
            $endDt = new DateTime(substr($end,0,10));
            $startDt = (clone $endDt)->modify('-89 days');
            $start = $startDt->format('Y-m-d') . ' 00:00:00';
            $end = $endDt->format('Y-m-d') . ' 23:59:59';
        }

        $dates = [];
        $period = new DatePeriod($startDt, new DateInterval('P1D'), $endDt->modify('+1 day'));
        foreach ($period as $dt) {
            $dates[] = $dt->format('Y-m-d');
        }

        $trend = [];
        foreach ($dates as $d) {
            $s = $d . ' 00:00:00';
            $e = $d . ' 23:59:59';
            $trend[] = [
                'date' => $d,
                'leads' => $this->countLeads($s, $e, $filters),
                'followups' => $this->countFollowups($s, $e, $filters)
            ];
        }
        return $trend;
    }

    /**
     * Apply role/team/user filters to SQL WHERE building.
     * $alias allows filtering on different table aliases (e.g. customers table alias 'c')
     */
    protected function applyFiltersToQuery($filters, &$conds, &$params, $alias = null)
    {
        $a = $alias ? ($alias . '.') : '';

        if (!empty($filters['user_id'])) {
            $conds[] = "{$a}assigned_to = :assigned_to";
            $params[':assigned_to'] = $filters['user_id'];
        }
        if (!empty($filters['team_id'])) {
            $conds[] = "{$a}team_id = :team_id";
            $params[':team_id'] = $filters['team_id'];
        }
        if (!empty($filters['role'])) {
            // role-level scoping: for example, StateHead can see team_id = X; handled by passing team_id
            // no-op here unless you have role-to-team logic
        }
    }

    protected function hasAssignmentFilter($filters)
    {
        return !empty($filters['user_id']) || !empty($filters['team_id']);
    }

    /**
     * Utility: map possible status synonyms to canonical stages.
     */
    public static function statusToStage($status)
    {
        $map = [
            'New' => 'lead',
            'Contacted' => 'lead',
            'Followup' => 'followup',
            'Proposal' => 'proposal',
            'Quoted' => 'proposal',
            'Closed' => 'closed',
            'Won' => 'closed',
            'Lost' => 'closed'
        ];
        return isset($map[$status]) ? $map[$status] : strtolower($status);
    }
}
