<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\VirtualStage;
use App\Utils\Logger;
use PDO;
use PDOException;

class VirtualStageRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function findByContactId($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM virtual_stages WHERE contact_didar_id = ? ORDER BY entered_at DESC");
        $stmt->execute([$contactId]);
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return array_map(function($data) {
            return new VirtualStage($data);
        }, $results);
    }

    public function getActiveStage($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT stage_name FROM virtual_stages 
                               WHERE contact_didar_id = ? 
                               AND stage_name NOT IN ('with_purchase', 'without_purchase', 'refer_crm')
                               ORDER BY entered_at DESC LIMIT 1");
        $stmt->execute([$contactId]);
        return $stmt->fetchColumn();
    }

    public function getSpecialStage($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT stage_name FROM virtual_stages 
                               WHERE contact_didar_id = ? 
                               AND stage_name IN ('with_purchase', 'without_purchase', 'refer_crm')
                               ORDER BY entered_at DESC LIMIT 1");
        $stmt->execute([$contactId]);
        return $stmt->fetchColumn();
    }

    public function getMostRecentStage($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT stage_name FROM virtual_stages 
                               WHERE contact_didar_id = ? 
                               ORDER BY entered_at DESC LIMIT 1");
        $stmt->execute([$contactId]);
        return $stmt->fetchColumn();
    }

    public function save(VirtualStage $stage)
    {
        $pdo = $this->db->getPdo();
        // MySQL: INSERT ... ON DUPLICATE KEY UPDATE
        // Use DATE_ADD instead of datetime('now', '+1 second')
        $sql = "INSERT INTO virtual_stages (contact_didar_id, stage_name, entered_at, last_activity_id, notes) 
                VALUES (?, ?, DATE_ADD(NOW(), INTERVAL 1 SECOND), ?, ?)
                ON DUPLICATE KEY UPDATE 
                entered_at = DATE_ADD(NOW(), INTERVAL 1 SECOND),
                last_activity_id = VALUES(last_activity_id),
                notes = VALUES(notes)";
        
        $stmt = $pdo->prepare($sql);
        return $stmt->execute([
            $stage->contact_didar_id,
            $stage->stage_name,
            $stage->last_activity_id,
            $stage->notes
        ]);
    }

    public function updateTimestamp($contactId, $stageName)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("UPDATE virtual_stages SET entered_at = NOW() 
                               WHERE contact_didar_id = ? AND stage_name = ?");
        return $stmt->execute([$contactId, $stageName]);
    }

    public function deleteActiveStages($contactId)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM virtual_stages 
                               WHERE contact_didar_id = ? 
                               AND stage_name NOT IN ('with_purchase', 'without_purchase', 'refer_crm')");
        return $stmt->execute([$contactId]);
    }

    public function exists($contactId, $stageName)
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM virtual_stages 
                               WHERE contact_didar_id = ? AND stage_name = ?");
        $stmt->execute([$contactId, $stageName]);
        return $stmt->fetchColumn() > 0;
    }
}

