<?php
class Contact extends Model {
//     public function getAllContacts($userId, $role) {
//     if ($role === 'admin') {
//         $stmt = $this->db->query("
//             SELECT contacts.*, users.name AS owner_name
//             FROM contacts
//             JOIN users ON contacts.user_id = users.id
//             ORDER BY contacts.created_at DESC
//         ");
//         return $stmt->fetchAll(PDO::FETCH_ASSOC);
//     } else {
//         $stmt = $this->db->prepare("
//             SELECT * FROM contacts WHERE user_id = ? ORDER BY created_at DESC
//         ");
//         $stmt->execute([$userId]);
//         return $stmt->fetchAll(PDO::FETCH_ASSOC);
//     }
// }

    public function getAllContacts($userId, $role) {
        if ($role === 'admin') {
            $stmt = $this->db->query("
                SELECT contacts.*, users.name AS owner_name
                FROM contacts
                LEFT JOIN users ON contacts.assigned_user_id = users.id
                ORDER BY contacts.created_at DESC
            ");
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {
            $stmt = $this->db->prepare("
                SELECT * FROM contacts WHERE assigned_user_id = ?
            ");
            $stmt->execute([$userId]);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        }
    }

    public function getContactById($id) {
        $stmt = $this->db->prepare("SELECT * FROM contacts WHERE id = ?");
        $stmt->execute([$id]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    public function addContact($data) {
        $stmt = $this->db->prepare("
            INSERT INTO contacts (user_id, name, email, phone, company, assigned_user_id)
            VALUES (:user_id, :name, :email, :phone, :company, :assigned_user_id)
        ");
    
        return $stmt->execute([
            'user_id' => $data['user_id'],
            'name' => $data['name'],
            'email' => $data['email'],
            'phone' => $data['phone'],
            'company' => $data['company'],
            'assigned_user_id' => $data['assigned_user_id']
        ]);
    }

    public function updateContact($id, $data) {
        $stmt = $this->db->prepare("UPDATE contacts SET name = ?, email = ?, phone = ?, company = ? WHERE id = ?");
        return $stmt->execute([
            $data['name'],
            $data['email'],
            $data['phone'],
            $data['company'],
            $id
        ]);
    }

    public function deleteContact($id) {
    // Check for related contact notes
    $stmt = $this->db->prepare("SELECT COUNT(*) FROM contact_notes WHERE contact_id = ?");
    $stmt->execute([$id]);
    $noteCount = $stmt->fetchColumn();

    if ($noteCount > 0) {
        return ['error' => '❌ Cannot delete contact: This contact has notes. Please delete them first.'];
    }
    
    // Check for related interactions
    $stmt = $this->db->prepare("SELECT COUNT(*) FROM interactions WHERE contact_id = ?");
    $stmt->execute([$id]);
    $interactionCount = $stmt->fetchColumn();
    
    if ($interactionCount > 0) {
        return ['error' => '❌ Cannot delete contact: This contact has interactions. Please delete them first.'];
    }

    // Check for related tasks
    $stmt = $this->db->prepare("SELECT COUNT(*) FROM tasks WHERE contact_id = ?");
    $stmt->execute([$id]);
    $taskCount = $stmt->fetchColumn();

    if ($taskCount > 0) {
        return ['error' => '❌ Cannot delete contact: This contact has tasks. Please delete or reassign them first.'];
    }

    try {
        $stmt = $this->db->prepare("DELETE FROM contacts WHERE id = ?");
        $stmt->execute([$id]);
        return ['success' => true];
    } catch (PDOException $e) {
        return ['error' => '❌ Failed to delete contact due to a system error.'];
    }
}




    public function assignContact($contactId, $agentId) {
        $stmt = $this->db->prepare("UPDATE contacts SET assigned_user_id = ? WHERE id = ?");
        return $stmt->execute([$agentId, $contactId]);
    }

    public function getUnassignedContacts($search = '', $limit = 10, $offset = 0) {
        $sql = "SELECT * FROM contacts WHERE assigned_user_id IS NULL";
        $params = [];
    
        if (!empty($search)) {
            $sql .= " AND (name LIKE ? OR email LIKE ?)";
            $params[] = "%$search%";
            $params[] = "%$search%";
        }
    
        $sql .= " ORDER BY created_at DESC LIMIT ? OFFSET ?";
        $params[] = (int)$limit;
        $params[] = (int)$offset;
    
        $stmt = $this->db->prepare($sql);
    
        // Bind manually to avoid issues with LIMIT/OFFSET being quoted
        $i = 1;
        foreach ($params as $param) {
            $type = is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR;
            $stmt->bindValue($i++, $param, $type);
        }
    
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    public function updateAssignment($contactId, $newAgentId = null) {
        // Get old assignment
        $stmtOld = $this->db->prepare("SELECT assigned_user_id FROM contacts WHERE id = ?");
        $stmtOld->execute([$contactId]);
        $oldAgentId = $stmtOld->fetchColumn();
    
        // Update assignment
        $stmtUpdate = $this->db->prepare("UPDATE contacts SET assigned_user_id = ? WHERE id = ?");
        $stmtUpdate->execute([$newAgentId, $contactId]);
    
        // Log the change
        $stmtLog = $this->db->prepare("
            INSERT INTO contact_assignment_logs (contact_id, old_agent_id, new_agent_id, changed_by)
            VALUES (?, ?, ?, ?)
        ");
        $stmtLog->execute([
            $contactId,
            $oldAgentId,
            $newAgentId,
            $_SESSION['user_id'] ?? 0
        ]);
    }

    public function getAllAssignedContacts($filter = [], $limit = 10, $offset = 0) {
    $sql = "
        SELECT contacts.*, users.name AS assigned_name
        FROM contacts
        LEFT JOIN users ON contacts.assigned_user_id = users.id
        WHERE contacts.assigned_user_id IS NOT NULL
    ";

    $params = [];

    if (!empty($filter['q'])) {
        $sql .= " AND (contacts.name LIKE ? OR contacts.email LIKE ?)";
        $params[] = '%' . $filter['q'] . '%';
        $params[] = '%' . $filter['q'] . '%';
    }

    if (!empty($filter['agent'])) {
        $sql .= " AND contacts.assigned_user_id = ?";
        $params[] = $filter['agent'];
    }

    $sql .= " ORDER BY contacts.created_at DESC LIMIT $limit OFFSET $offset";

    $stmt = $this->db->prepare($sql);
    $stmt->execute($params);

    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

    public function countAssignedContacts($filter = []) {
    $sql = "SELECT COUNT(*) FROM contacts WHERE assigned_user_id IS NOT NULL";
    $params = [];

    if (!empty($filter['q'])) {
        $sql .= " AND (name LIKE ? OR email LIKE ?)";
        $params[] = '%' . $filter['q'] . '%';
        $params[] = '%' . $filter['q'] . '%';
    }

    if (!empty($filter['agent'])) {
        $sql .= " AND assigned_user_id = ?";
        $params[] = $filter['agent'];
    }

    $stmt = $this->db->prepare($sql);
    $stmt->execute($params);

    return (int)$stmt->fetchColumn();
}

    public function getContactsPaginated($userId, $role, $limit, $offset) {
    if ($role === 'admin') {
        $sql = "
            SELECT contacts.*, users.name AS owner_name
            FROM contacts
            LEFT JOIN users ON contacts.assigned_user_id = users.id
            ORDER BY contacts.created_at DESC
            LIMIT ? OFFSET ?
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(1, $limit, PDO::PARAM_INT);
        $stmt->bindValue(2, $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    } else {
        $sql = "
            SELECT * FROM contacts 
            WHERE assigned_user_id = ? OR user_id = ?
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        ";
        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(1, $userId, PDO::PARAM_INT);
        $stmt->bindValue(2, $userId, PDO::PARAM_INT);
        $stmt->bindValue(3, $limit, PDO::PARAM_INT);
        $stmt->bindValue(4, $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}


    public function countContacts($userId, $role) {
        if ($role === 'admin') {
            $stmt = $this->db->query("SELECT COUNT(*) FROM contacts");
            return $stmt->fetchColumn();
        } else {
            $stmt = $this->db->prepare("SELECT COUNT(*) FROM contacts WHERE assigned_user_id = ? OR user_id = ?");
            $stmt->execute([$userId, $userId]);
            return $stmt->fetchColumn();
        }
    }

    public function countUnassignedContacts($search = '') {
    $sql = "SELECT COUNT(*) FROM contacts WHERE assigned_user_id IS NULL";
    $params = [];

    if (!empty($search)) {
        $sql .= " AND (name LIKE ? OR email LIKE ?)";
        $params[] = "%$search%";
        $params[] = "%$search%";
    }

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

public function addNotes($data) {
        $stmt = $this->db->prepare("
            INSERT INTO contacts (user_id, name, email, phone, company, assigned_user_id)
            VALUES (:user_id, :name, :email, :phone, :company, :assigned_user_id)
        ");
    
        return $stmt->execute([
            'user_id' => $data['user_id'],
            'name' => $data['name'],
            'email' => $data['email'],
            'phone' => $data['phone'],
            'company' => $data['company'],
            'assigned_user_id' => $data['assigned_user_id']
        ]);
    }
 


}
