<?php
namespace App\Models;

use App\Core\Model;
use PDO;

class Deal extends Model {
    protected static string $table = 'deals';

    public static function getByStage(int $stageId): array {
        $stmt = self::db()->prepare("
            SELECT d.*, 
                   c.name as contact_name, c.phone_number, c.whatsapp_id,
                   u.name as assigned_user_name
            FROM deals d
            JOIN contacts c ON d.contact_id = c.id
            LEFT JOIN users u ON d.assigned_to = u.id
            WHERE d.stage_id = ?
            ORDER BY d.updated_at DESC
        ");
        $stmt->execute([$stageId]);
        return $stmt->fetchAll();
    }

    public static function getWithDetails(int $id): ?array {
        $stmt = self::db()->prepare("
            SELECT d.*, 
                   c.name as contact_name, c.phone_number, c.whatsapp_id,
                   ps.name as stage_name, ps.color as stage_color,
                   u.name as assigned_user_name
            FROM deals d
            JOIN contacts c ON d.contact_id = c.id
            JOIN pipeline_stages ps ON d.stage_id = ps.id
            LEFT JOIN users u ON d.assigned_to = u.id
            WHERE d.id = ?
            LIMIT 1
        ");
        $stmt->execute([$id]);
        $deal = $stmt->fetch();
        return $deal ?: null;
    }

    public static function getByContact(int $contactId): array {
        $stmt = self::db()->prepare("
            SELECT d.*, ps.name as stage_name, ps.color as stage_color 
            FROM deals d
            JOIN pipeline_stages ps ON d.stage_id = ps.id
            WHERE d.contact_id = ?
            ORDER BY d.created_at DESC
        ");
        $stmt->execute([$contactId]);
        return $stmt->fetchAll();
    }

    public static function getPipelineSummary(): array {
        $stmt = self::db()->query("
            SELECT 
                COUNT(*) as total_deals,
                SUM(value) as total_pipeline_value,
                SUM(CASE WHEN status = 'won' THEN value ELSE 0 END) as won_value,
                SUM(CASE WHEN status = 'lost' THEN value ELSE 0 END) as lost_value,
                SUM(CASE WHEN status = 'open' THEN value ELSE 0 END) as open_value
            FROM deals
        ");
        return $stmt->fetch() ?: [
            'total_deals'          => 0,
            'total_pipeline_value' => 0.00,
            'won_value'            => 0.00,
            'lost_value'           => 0.00,
            'open_value'           => 0.00
        ];
    }
}
