<?php

namespace App\Models;

use App\Core\Model;
use PDO;

class Lead extends Model {
    public function search($params = []) {
        $sql = "SELECT l.*, c.name as course_name,
                (SELECT next_reminder_date FROM lead_followups lf WHERE lf.lead_id = l.id AND lf.next_reminder_date >= NOW() ORDER BY lf.next_reminder_date ASC LIMIT 1) as next_reminder
                FROM leads l
                LEFT JOIN courses c ON l.course_id = c.id
                WHERE 1=1";
        $binds = [];

        if (!empty($params['search'])) {
            $sql .= " AND (l.name LIKE :search OR l.phone LIKE :search OR l.email LIKE :search)";
            $binds['search'] = '%' . $params['search'] . '%';
        }

        if (!empty($params['status'])) {
            $sql .= " AND l.status = :status";
            $binds['status'] = $params['status'];
        }

        $sql .= " ORDER BY l.id DESC";
        $stmt = $this->db->prepare($sql);
        $stmt->execute($binds);
        return $stmt->fetchAll();
    }

    public function find($id) {
        $stmt = $this->db->prepare("SELECT l.*, c.name as course_name FROM leads l LEFT JOIN courses c ON l.course_id = c.id WHERE l.id = :id LIMIT 1");
        $stmt->execute(['id' => $id]);
        return $stmt->fetch();
    }

    public function getFollowups($leadId) {
        $stmt = $this->db->prepare("SELECT * FROM lead_followups WHERE lead_id = :lead_id ORDER BY interaction_date DESC, id DESC");
        $stmt->execute(['lead_id' => $leadId]);
        return $stmt->fetchAll();
    }

    public function save($data) {
        if (!empty($data['id'])) {
            $sql = "UPDATE leads SET name = :name, phone = :phone, email = :email, 
                    source = :source, course_id = :course_id, status = :status, notes = :notes 
                    WHERE id = :id";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                'id' => $data['id'],
                'name' => $data['name'],
                'phone' => $data['phone'],
                'email' => $data['email'] ?? null,
                'source' => $data['source'] ?? null,
                'course_id' => $data['course_id'] ?? null,
                'status' => $data['status'] ?? 'New',
                'notes' => $data['notes'] ?? null
            ]);
            return $data['id'];
        } else {
            $sql = "INSERT INTO leads (name, phone, email, source, course_id, status, notes) 
                    VALUES (:name, :phone, :email, :source, :course_id, :status, :notes)";
            $stmt = $this->db->prepare($sql);
            $stmt->execute([
                'name' => $data['name'],
                'phone' => $data['phone'],
                'email' => $data['email'] ?? null,
                'source' => $data['source'] ?? null,
                'course_id' => $data['course_id'] ?? null,
                'status' => $data['status'] ?? 'New',
                'notes' => $data['notes'] ?? null
            ]);
            return $this->db->lastInsertId();
        }
    }

    public function addFollowup($data) {
        $sql = "INSERT INTO lead_followups (lead_id, interaction_date, type, notes, next_reminder_date) 
                VALUES (:lead_id, :interaction_date, :type, :notes, :next_reminder_date)";
        $stmt = $this->db->prepare($sql);
        return $stmt->execute([
            'lead_id' => $data['lead_id'],
            'interaction_date' => $data['interaction_date'],
            'type' => $data['type'] ?? 'Note',
            'notes' => $data['notes'],
            'next_reminder_date' => $data['next_reminder_date'] ?: null
        ]);
    }

    public function delete($id) {
        $stmt = $this->db->prepare("DELETE FROM leads WHERE id = :id");
        return $stmt->execute(['id' => $id]);
    }

    public function updateStatus($id, $status) {
        $stmt = $this->db->prepare("UPDATE leads SET status = :status WHERE id = :id");
        return $stmt->execute(['status' => $status, 'id' => $id]);
    }
}
