<?php
declare(strict_types=1);

namespace App\Models;

final class Opportunity extends BaseModel
{
    protected static string $table = 'opportunities';
    protected static bool $softDelete = true;

    /**
     * @param array{q?:string,stage_id?:int,owner_id?:?int,account_id?:int,outcome?:string,close?:string,created?:string,next_step?:string,probability?:string,hide_lost?:bool} $filters
     * @return array{0:string,1:array<string,mixed>}
     */
    public static function filterClause(array $filters): array
    {
        $where = 'o.tenant_id = :tid AND o.deleted_at IS NULL';
        $params = ['tid' => static::tenantId()];
        $q = trim((string) ($filters['q'] ?? ''));
        if ($q !== '') {
            $where .= ' AND (o.name LIKE :q OR a.name LIKE :q2)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
        }
        $accountId = (int) ($filters['account_id'] ?? 0);
        if ($accountId > 0) {
            $where .= ' AND o.account_id = :aid';
            $params['aid'] = $accountId;
        }
        $stageId = (int) ($filters['stage_id'] ?? 0);
        if ($stageId > 0) {
            $where .= ' AND o.stage_id = :sid';
            $params['sid'] = $stageId;
        }
        $ownerId = $filters['owner_id'] ?? null;
        if ($ownerId) {
            $where .= ' AND o.owner_id = :oid';
            $params['oid'] = (int) $ownerId;
        }
        $outcome = (string) ($filters['outcome'] ?? 'all');
        if ($outcome === 'open') {
            $where .= ' AND s.is_won = 0 AND s.is_lost = 0';
        } elseif ($outcome === 'won') {
            $where .= ' AND s.is_won = 1';
        } elseif ($outcome === 'lost') {
            $where .= ' AND s.is_lost = 1';
        }
        if (!empty($filters['hide_lost']) && $outcome !== 'lost') {
            $where .= ' AND s.is_lost = 0';
        }
        $created = (string) ($filters['created'] ?? 'all');
        if ($created === 'today') {
            $where .= ' AND DATE(o.created_at) = CURDATE()';
        } elseif ($created === 'week') {
            $where .= ' AND YEARWEEK(o.created_at, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($created === 'month') {
            $where .= ' AND YEAR(o.created_at) = YEAR(CURDATE()) AND MONTH(o.created_at) = MONTH(CURDATE())';
        } elseif ($created === 'last_week') {
            $where .= ' AND YEARWEEK(o.created_at, 1) = YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 7 DAY), 1)';
        } elseif ($created === 'last_month') {
            $where .= " AND DATE(o.created_at) >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND DATE(o.created_at) < DATE_FORMAT(CURDATE(), '%Y-%m-01')";
        }
        $close = (string) ($filters['close'] ?? 'all');
        if ($close === 'week') {
            $where .= ' AND o.close_date IS NOT NULL AND o.close_date >= CURDATE() AND YEARWEEK(o.close_date, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($close === 'month') {
            $where .= ' AND o.close_date IS NOT NULL AND o.close_date >= CURDATE() AND YEAR(o.close_date) = YEAR(CURDATE()) AND MONTH(o.close_date) = MONTH(CURDATE())';
        } elseif ($close === 'overdue') {
            $where .= ' AND o.close_date IS NOT NULL AND o.close_date < CURDATE()';
        }
        $next = (string) ($filters['next_step'] ?? 'all');
        if ($next === 'overdue') {
            $where .= ' AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) < CURDATE()';
        } elseif ($next === 'today') {
            $where .= ' AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) = CURDATE()';
        } elseif ($next === 'week') {
            $where .= ' AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) >= CURDATE() AND YEARWEEK(o.next_step_at, 1) = YEARWEEK(CURDATE(), 1)';
        } elseif ($next === 'none') {
            $where .= ' AND o.next_step_at IS NULL';
        }
        $probability = (string) ($filters['probability'] ?? 'all');
        if ($probability === 'low') {
            $where .= ' AND o.probability < 34';
        } elseif ($probability === 'medium') {
            $where .= ' AND o.probability BETWEEN 34 AND 66';
        } elseif ($probability === 'high') {
            $where .= ' AND o.probability >= 67';
        }
        return [$where, $params];
    }

    public static function paginate(array $filters, int $page, int $perPage): array
    {
        [$where, $params] = self::filterClause($filters);
        $total = (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM opportunities o INNER JOIN accounts a ON a.id = o.account_id INNER JOIN pipeline_stages s ON s.id = o.stage_id WHERE $where",
            $params
        );
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT o.*, a.name AS account_name, s.name AS stage_name, s.is_won, s.is_lost, s.probability AS stage_probability,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             LEFT JOIN users u ON u.id = o.owner_id
             WHERE $where
             ORDER BY o.updated_at DESC, o.id DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function kanban(array $filters): array
    {
        $filters['hide_lost'] = ($filters['outcome'] ?? 'all') !== 'lost';
        [$where, $params] = self::filterClause($filters);
        return static::db()->fetchAll(
            "SELECT o.*, a.name AS account_name, s.name AS stage_name, s.sort_order,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             LEFT JOIN users u ON u.id = o.owner_id
             WHERE $where
             ORDER BY s.sort_order, o.amount DESC",
            $params
        );
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT o.*, a.name AS account_name, s.name AS stage_name, s.is_won, s.is_lost, s.sort_order,
                    s.probability AS stage_probability,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             LEFT JOIN users u ON u.id = o.owner_id
             WHERE o.id = :id AND o.tenant_id = :tid AND o.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    public static function forAccount(int $accountId): array
    {
        return static::db()->fetchAll(
            "SELECT o.*, s.name AS stage_name, s.is_won, s.is_lost
             FROM opportunities o
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             WHERE o.tenant_id = :tid AND o.account_id = :aid AND o.deleted_at IS NULL
             ORDER BY o.created_at DESC",
            ['tid' => static::tenantId(), 'aid' => $accountId]
        );
    }

    public static function search(string $q, int $limit = 8): array
    {
        $like = '%' . $q . '%';
        return static::db()->fetchAll(
            "SELECT o.id, o.name, o.amount, a.name AS account_name, s.name AS stage_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             WHERE o.tenant_id = :tid AND o.deleted_at IS NULL
               AND (o.name LIKE :q OR a.name LIKE :q2)
             ORDER BY o.updated_at DESC LIMIT $limit",
            ['tid' => static::tenantId(), 'q' => $like, 'q2' => $like]
        );
    }

    public static function allForSelect(): array
    {
        return static::db()->fetchAll(
            "SELECT o.id, o.name, a.name AS account_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_won = 0 AND s.is_lost = 0
             ORDER BY o.name
             LIMIT 200",
            ['tid' => static::tenantId()]
        );
    }

    public static function pipelineByStage(?int $ownerId = null): array
    {
        $sql = "SELECT s.id, s.name, s.sort_order, s.probability, s.is_won, s.is_lost,
                       COUNT(o.id) AS deal_count, COALESCE(SUM(o.amount),0) AS deal_value
                FROM pipeline_stages s
                LEFT JOIN opportunities o ON o.stage_id = s.id AND o.tenant_id = s.tenant_id AND o.deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= ' WHERE s.tenant_id = :tid AND s.is_active = 1 GROUP BY s.id ORDER BY s.sort_order';
        return static::db()->fetchAll($sql, $params);
    }

    public static function pipelineByRep(): array
    {
        return static::db()->fetchAll(
            "SELECT u.id, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                    COUNT(o.id) AS deal_count, COALESCE(SUM(o.amount),0) AS deal_value,
                    COALESCE(SUM(CASE WHEN s.is_won = 1 THEN o.amount ELSE 0 END),0) AS won_value
             FROM users u
             INNER JOIN user_roles ur ON ur.user_id = u.id AND ur.tenant_id = :tid
             INNER JOIN roles r ON r.id = ur.role_id AND r.code IN ('salesperson','manager')
             LEFT JOIN opportunities o ON o.owner_id = u.id AND o.tenant_id = :tid2 AND o.deleted_at IS NULL
             LEFT JOIN pipeline_stages s ON s.id = o.stage_id
             WHERE u.deleted_at IS NULL
             GROUP BY u.id
             ORDER BY deal_value DESC",
            ['tid' => static::tenantId(), 'tid2' => static::tenantId()]
        );
    }

    public static function needingAction(?int $ownerId = null, int $limit = 8): array
    {
        $sql = "SELECT o.*, a.name AS account_name, a.email AS account_email, a.phone AS account_phone, s.name AS stage_name
                FROM opportunities o
                INNER JOIN accounts a ON a.id = o.account_id
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_won = 0 AND s.is_lost = 0
                  AND (o.next_step_at IS NULL OR o.next_step_at <= NOW() OR o.next_step IS NULL OR o.next_step = '')";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $sql .= " ORDER BY o.next_step_at IS NULL, o.next_step_at ASC LIMIT $limit";
        return static::db()->fetchAll($sql, $params);
    }

    public static function forecast(?int $ownerId = null): array
    {
        $sql = "SELECT COALESCE(SUM(o.amount),0) AS pipeline_value,
                       COALESCE(SUM(o.amount * s.probability / 100),0) AS weighted_value,
                       COALESCE(SUM(CASE WHEN s.is_won = 1 AND MONTH(o.close_date)=MONTH(CURDATE()) AND YEAR(o.close_date)=YEAR(CURDATE()) THEN o.amount ELSE 0 END),0) AS won_this_month
                FROM opportunities o
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        return static::db()->fetch($sql, $params) ?? [];
    }

    /** @return array{count:int,value:float} */
    public static function openTotals(bool $createdBeforeToday = false): array
    {
        $sql = "SELECT COUNT(*) AS deal_count, COALESCE(SUM(o.amount),0) AS deal_value
                FROM opportunities o
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_won = 0 AND s.is_lost = 0";
        if ($createdBeforeToday) {
            $sql .= ' AND o.created_at < CURDATE()';
        }
        $row = static::db()->fetch($sql, ['tid' => static::tenantId()]);
        return [
            'count' => (int) ($row['deal_count'] ?? 0),
            'value' => (float) ($row['deal_value'] ?? 0),
        ];
    }

    public static function wonCount(bool $beforeToday = false): int
    {
        $sql = "SELECT COUNT(*) FROM opportunities o
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL AND s.is_won = 1";
        if ($beforeToday) {
            $sql .= ' AND COALESCE(o.close_date, o.created_at) < CURDATE()';
        }
        return (int) static::db()->fetchColumn($sql, ['tid' => static::tenantId()]);
    }

    /** @return list<array<string,mixed>> */
    public static function openNextStepDue(): array
    {
        return static::db()->fetchAll(
            "SELECT o.id, o.name, o.owner_id, o.next_step, o.next_step_at, a.name AS account_name,
                    CONCAT(u.first_name,' ',u.last_name) AS owner_name
             FROM opportunities o
             INNER JOIN accounts a ON a.id = o.account_id
             INNER JOIN pipeline_stages s ON s.id = o.stage_id
             LEFT JOIN users u ON u.id = o.owner_id
             WHERE o.tenant_id = :tid AND o.deleted_at IS NULL
               AND s.is_won = 0 AND s.is_lost = 0
               AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) <= CURDATE()
             ORDER BY o.next_step_at ASC
             LIMIT 40",
            ['tid' => static::tenantId()]
        );
    }

    public static function boardCounts(?int $ownerId = null): array
    {
        $sql = "SELECT
                    SUM(s.is_won = 0 AND s.is_lost = 0) AS open_n,
                    COALESCE(SUM(CASE WHEN s.is_won = 0 AND s.is_lost = 0 THEN o.amount ELSE 0 END), 0) AS open_value,
                    SUM(s.is_won = 1 AND MONTH(COALESCE(o.close_date, o.updated_at)) = MONTH(CURDATE()) AND YEAR(COALESCE(o.close_date, o.updated_at)) = YEAR(CURDATE())) AS won_n,
                    COALESCE(SUM(CASE WHEN s.is_won = 1 AND MONTH(COALESCE(o.close_date, o.updated_at)) = MONTH(CURDATE()) AND YEAR(COALESCE(o.close_date, o.updated_at)) = YEAR(CURDATE()) THEN o.amount ELSE 0 END), 0) AS won_value,
                    SUM(s.is_won = 0 AND s.is_lost = 0 AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) < CURDATE()) AS due_n,
                    SUM(s.is_won = 0 AND s.is_lost = 0 AND o.next_step_at IS NOT NULL AND DATE(o.next_step_at) = CURDATE()) AS today_n
                FROM opportunities o
                INNER JOIN pipeline_stages s ON s.id = o.stage_id
                WHERE o.tenant_id = :tid AND o.deleted_at IS NULL";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND o.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        return [
            'open' => (int) ($row['open_n'] ?? 0),
            'open_value' => (float) ($row['open_value'] ?? 0),
            'won' => (int) ($row['won_n'] ?? 0),
            'won_value' => (float) ($row['won_value'] ?? 0),
            'due' => (int) ($row['due_n'] ?? 0),
            'today' => (int) ($row['today_n'] ?? 0),
        ];
    }
}
