<?php
require_once 'config/database.php';

class FollowUpsController {
    private $conn;

    public function __construct() {
        $database = new Database();
        $this->conn = $database->getConnection();
    }

    public function createFollowUp($data) {
        try {
            if (!isset($data['invoice_id'], $data['ipo_id'])) {
                return json_encode(['status' => 'error', 'message' => 'Missing required fields']);
            }

            // Check if follow-up exists (locked)
            $checkQuery = "
                SELECT id FROM follow_ups
                WHERE invoice_id = :invoice_id AND ipo_id = :ipo_id
                LIMIT 1
                FOR UPDATE";

            $checkStmt = $this->conn->prepare($checkQuery);
            $checkStmt->bindParam(':invoice_id', $data['invoice_id']);
            $checkStmt->bindParam(':ipo_id', $data['ipo_id']);
            $checkStmt->execute();

            $existing = $checkStmt->fetch(PDO::FETCH_ASSOC);
            if ($existing) {
                return json_encode(['status' => 'error', 'message' => 'Follow-up already exists', 'follow_up_id' => $existing['id']]);
            }

            // Insert new follow-up
            $query = "INSERT INTO follow_ups (invoice_id, ipo_id, status, attempts, priority, notes, created_at, updated_at) 
                    VALUES (:invoice_id, :ipo_id, 'active', 0, :priority, :notes, NOW(), NOW())";

            $priority = $data['priority'] ?? 'medium';
            $notes = $data['notes'] ?? null;

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':invoice_id', $data['invoice_id']);
            $stmt->bindParam(':ipo_id', $data['ipo_id']);
            $stmt->bindParam(':priority', $priority);
            $stmt->bindParam(':notes', $notes);

            if ($stmt->execute()) {
                return json_encode([
                    'status' => 'success',
                    'message' => 'Follow-up created',
                    'follow_up_id' => $this->conn->lastInsertId()
                ]);
            }

            return json_encode(['status' => 'error', 'message' => 'Failed to create follow-up']);

        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }


    public function getFollowUps($params = []) {
        try {
            $wb = $this->buildWhereAndBindings($params);
            $whereSql = $wb['whereSql'];
            $bindings = $wb['bindings'];

            // Add search if provided (searches concatenated first+last name or invoice id)
            if (!empty($params['search'])) {
                $bindings[':search'] = '%' . $params['search'] . '%';
                if ($whereSql) {
                    $whereSql .= " AND (CONCAT(i.first_name, ' ', i.last_name) LIKE :search OR i.id LIKE :search)";
                } else {
                    $whereSql = "WHERE (CONCAT(i.first_name, ' ', i.last_name) LIKE :search OR i.id LIKE :search)";
                }
            }

            if(!empty($params['assigned_to'])) {
                $bindings[':assigned_to'] = $params['assigned_to'];
                if ($whereSql) {
                    $whereSql .= " AND i.assigned_to = :assigned_to";
                } else {
                    $whereSql = "WHERE i.assigned_to = :assigned_to";
                }
            }
            
            // supervisor filter
            if(!empty($params['supervisor_id'])) {
                $bindings[':supervisor_id'] = $params['supervisor_id'];
                if ($whereSql) {
                    $whereSql .= " AND u.supervisor_id = :supervisor_id";
                } else {
                    $whereSql = "WHERE u.supervisor_id = :supervisor_id";
                }
            }

            // Pagination and sorting
            $page = isset($params['page']) ? max(1, (int)$params['page']) : 1;
            $perPage = isset($params['per_page']) ? max(1, min(100, (int)$params['per_page'])) : 20;
            $offset = ($page - 1) * $perPage;

            $allowedSort = ['f.id', 'f.created_at', 'f.updated_at', 'f.last_contact', 'f.attempts', 'f.next_action'];
            $sortByParam = $params['sort_by'] ?? 'f.created_at';
            $sortBy = in_array($sortByParam, $allowedSort) ? $sortByParam : 'f.created_at';
            $sortDir = (isset($params['sort_dir']) && strtoupper($params['sort_dir']) === 'ASC') ? 'ASC' : 'DESC';

            // Main query with joins
            $dataSql = "
                SELECT 
                    f.*,
                    i.amount, i.invoice_number,
                    i.due_date,
                    DATEDIFF(CURDATE(), i.due_date) AS days_past_due,
                    CONCAT(i.first_name, ' ', i.last_name) AS taxpayer_name,
                    i.phone AS taxpayer_phone,
                    CONCAT(u.first_name, ' ', u.last_name) AS assigned_to,
                    (SELECT COUNT(*) FROM calls c WHERE c.follow_up_id = f.id) AS call_attempts
                FROM follow_ups f
                LEFT JOIN invoices i ON f.invoice_id = i.id
                LEFT JOIN users u ON f.ipo_id = u.id
                $whereSql
                ORDER BY $sortBy $sortDir
                LIMIT :limit OFFSET :offset
            ";
            $stmt = $this->conn->prepare($dataSql);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->bindValue(':limit', (int)$perPage, PDO::PARAM_INT);
            $stmt->bindValue(':offset', (int)$offset, PDO::PARAM_INT);
            $stmt->execute();
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Get total count for pagination
            $countSql = "
                SELECT COUNT(*) AS total
                FROM follow_ups f
                LEFT JOIN invoices i ON f.invoice_id = i.id
                LEFT JOIN users u ON f.ipo_id = u.id
                $whereSql
            ";
            $countStmt = $this->conn->prepare($countSql);
            foreach ($bindings as $key => $val) {
                $countStmt->bindValue($key, $val);
            }
            $countStmt->execute();
            $total = $countStmt->fetchColumn();

            return json_encode([
                'status' => 'success',
                'data' => $rows,
                'pagination' => [
                    'page' => $page,
                    'per_page' => $perPage,
                    'total' => (int)$total,
                    'returned' => count($rows)
                ]
            ]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getSingleFollowUp($id) {
        try {
            $query = "
                SELECT 
                    f.*,
                    i.amount,  i.invoice_number,
                    i.due_date,
                    DATEDIFF(CURDATE(), i.due_date) AS days_past_due,
                    CONCAT(i.first_name, ' ', i.last_name) AS taxpayer_name,
                    i.phone AS taxpayer_phone,
                    CONCAT(u.first_name, ' ', u.last_name) AS assigned_to,
                    (SELECT COUNT(*) FROM calls c WHERE c.follow_up_id = f.id) AS call_attempts
                FROM follow_ups f
                LEFT JOIN invoices i ON f.invoice_id = i.id
                LEFT JOIN users u ON f.ipo_id = u.id
                WHERE f.id = :id
            ";
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':id', $id);
            $stmt->execute();
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$row) {
                return json_encode(['status' => 'error', 'message' => 'Follow-up not found']);
            }

            // Fetch associated calls
            $callsQuery = "SELECT * FROM calls WHERE follow_up_id = :follow_up_id ORDER BY created_at DESC";
            $callsStmt = $this->conn->prepare($callsQuery);
            $callsStmt->bindParam(':follow_up_id', $id);
            $callsStmt->execute();
            $calls = $callsStmt->fetchAll(PDO::FETCH_ASSOC);

            $row['calls'] = $calls;

            return json_encode([
                'status' => 'success',
                'data' => $row
            ]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function updateFollowUp($id, $data) {
        try {
            if (empty($data)) {
                return json_encode(['status' => 'error', 'message' => 'No data provided for update']);
            }

            $setClauses = [];
            $bindings = [];

            if (isset($data['status'])) {
                $setClauses[] = "status = :status";
                $bindings[':status'] = $data['status'];
            }
            if (isset($data['ipo_id'])) {
                $setClauses[] = "ipo_id = :ipo_id";
                $bindings[':ipo_id'] = $data['ipo_id'];
            }
            if (isset($data['priority'])) {
                $setClauses[] = "priority = :priority";
                $bindings[':priority'] = $data['priority'];
            }
            if (isset($data['next_action'])) {
                $setClauses[] = "next_action = :next_action";
                $bindings[':next_action'] = $data['next_action'];
            }
            if (isset($data['notes'])) {
                $setClauses[] = "notes = :notes";
                $bindings[':notes'] = $data['notes'];
            }
            if (isset($data['escalation_reason'])) {
                $setClauses[] = "escalation_reason = :escalation_reason";
                $bindings[':escalation_reason'] = $data['escalation_reason'];
            }

            if (empty($setClauses)) {
                return json_encode(['status' => 'error', 'message' => 'No valid fields provided for update']);
            }

            $setSql = implode(', ', $setClauses);
            $query = "UPDATE follow_ups SET $setSql, updated_at = NOW() WHERE id = :id";
            $stmt = $this->conn->prepare($query);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->bindParam(':id', $id);

            if ($stmt->execute()) {
                return $this->getSingleFollowUp($id);
            } else {
                return json_encode(['status' => 'error', 'message' => 'Error updating follow-up']);
            }
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function closeFollowUp($id, $data = []) {
        $data['status'] = 'closed';
        return $this->updateFollowUp($id, $data);
    }

    public function recordCall($data) {
        try {
            if (!isset($data['follow_up_id'], $data['start_time'], $data['status'])) {
                return json_encode(['status' => 'error', 'message' => 'Missing required fields']);
            }

            $duration = $data['duration'] ?? null;
            $duration = $data['duration'] ?? null;
            $callSid = $data['call_sid'] ?? null;
            $failure_reason = $data['failure_reason'] ?? null;
            $recording_file = $data['recording_file'] ?? null;

            // Insert call record
            $query = "INSERT INTO calls (follow_up_id, call_sid, start_time, duration, status, failure_reason, recording_file, created_at, updated_at) 
                    VALUES (:follow_up_id, :call_sid, :start_time, :duration, :status, :failure_reason, :recording_file, NOW(), NOW())";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':follow_up_id', $data['follow_up_id']);
            $stmt->bindParam(':call_sid', $callSid);
            $stmt->bindParam(':start_time', $data['start_time']);
            $stmt->bindParam(':duration', $duration);
            $stmt->bindParam(':status', $data['status']);
            $stmt->bindParam(':failure_reason', $failure_reason);
            $stmt->bindParam(':recording_file', $recording_file);

            if (!$stmt->execute()) {
                return json_encode(['status' => 'error', 'message' => 'Error recording call']);
            }

            return json_encode([
                'status' => 'success',
                'message' => 'Call recorded',
                'call_id' => $this->conn->lastInsertId()
            ]);

        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }


    public function getFollowUpsStatistics($params = []) {
        try {
            $wb = $this->buildWhereAndBindings($params);
            $whereSql = $wb['whereSql'];
            $bindings = $wb['bindings'];

            // supervisor filter
            if(!empty($params['supervisor_id'])) {
                $bindings[':supervisor_id'] = $params['supervisor_id'];
                if ($whereSql) {
                    $whereSql .= " AND u.supervisor_id = :supervisor_id";
                } else {
                    $whereSql = "WHERE u.supervisor_id = :supervisor_id";
                }
            }

            // Aggregated statistics
            $statsSql = "
                SELECT
                    COUNT(*) AS total_followups,
                    SUM(CASE WHEN f.status IN ('active', 'in_progress') THEN 1 ELSE 0 END) AS pending,
                    SUM(CASE WHEN f.status = 'rescheduled' THEN 1 ELSE 0 END) AS scheduled,
                    SUM(CASE WHEN f.status = 'in_progress' THEN 1 ELSE 0 END) AS in_progress,
                    SUM(CASE WHEN f.status = 'escalated' THEN 1 ELSE 0 END) AS escalated,
                    SUM(CASE WHEN f.status = 'closed' THEN 1 ELSE 0 END) AS completed,
                    AVG(f.attempts) AS avg_attempts
                FROM follow_ups f
                LEFT JOIN invoices i ON f.invoice_id = i.id
                LEFT JOIN taxpayer t ON i.taxpayer_id = t.id
                LEFT JOIN users u ON f.ipo_id = u.id
                $whereSql
            ";
            $stmt = $this->conn->prepare($statsSql);
            foreach ($bindings as $key => $val) {
                $stmt->bindValue($key, $val);
            }
            $stmt->execute();
            $stats = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

            // Total calls
            $callsSql = "
                SELECT COUNT(*) AS total_calls
                FROM calls c
                JOIN follow_ups f ON c.follow_up_id = f.id
                LEFT JOIN invoices i ON f.invoice_id = i.id
                LEFT JOIN taxpayer t ON i.taxpayer_id = t.id
                LEFT JOIN users u ON f.ipo_id = u.id
                $whereSql
            ";
            $callsStmt = $this->conn->prepare($callsSql);
            foreach ($bindings as $key => $val) {
                $callsStmt->bindValue($key, $val);
            }
            $callsStmt->execute();
            $callsRow = $callsStmt->fetch(PDO::FETCH_ASSOC) ?: [];
            $total_calls = isset($callsRow['total_calls']) ? (int)$callsRow['total_calls'] : 0;

            return json_encode([
                'status' => 'success',
                'statistics' => [
                    'total_followups' => isset($stats['total_followups']) ? (int)$stats['total_followups'] : 0,
                    'pending' => isset($stats['pending']) ? (int)$stats['pending'] : 0,
                    'scheduled' => isset($stats['scheduled']) ? (int)$stats['scheduled'] : 0,
                    'in_progress' => isset($stats['in_progress']) ? (int)$stats['in_progress'] : 0,
                    'escalated' => isset($stats['escalated']) ? (int)$stats['escalated'] : 0,
                    'completed' => isset($stats['completed']) ? (int)$stats['completed'] : 0,
                    'avg_attempts' => isset($stats['avg_attempts']) ? (float)$stats['avg_attempts'] : 0,
                    'total_calls' => $total_calls
                ]
            ]);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function rescheduleFollowUp($data) {
        try {
            if (!isset($data['follow_up_id'], $data['reschedule_date'])) {
                return json_encode(['status' => 'error', 'message' => 'Missing required fields']);
            }

            $updateData = [
                'status' => 'rescheduled',
                // 'next_action' => $data['next_action'] ?? 'reschedule'
            ];

            // Parse and format the reschedule date
            $rescheduleDate = new DateTime($data['reschedule_date']);
            $updateData['next_action'] = $rescheduleDate->format('Y-m-d H:i:s');

            if (isset($data['notes'])) {
                $updateData['notes'] = $data['notes'];
            }

            return $this->updateFollowUp($data['follow_up_id'], $updateData);

        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    private function buildWhereAndBindings($params) {
        $where = [];
        $bindings = [];

        if (!empty($params['ipo_id'])) {
            $where[] = "f.ipo_id = :ipo_id";
            $bindings[':ipo_id'] = $params['ipo_id'];
        }
        if (!empty($params['invoice_id'])) {
            $where[] = "f.invoice_id = :invoice_id";
            $bindings[':invoice_id'] = $params['invoice_id'];
        }
        if (!empty($params['status']) && $params['status'] !== 'all') {
            if ($params['status'] === 'pending') {
                $where[] = "f.status IN ('active', 'in_progress')";
            } else {
                $where[] = "f.status = :status";
                $bindings[':status'] = $params['status'];
            }
        }
        if (!empty($params['priority']) && $params['priority'] !== 'all') {
            $where[] = "f.priority = :priority";
            $bindings[':priority'] = $params['priority'];
        }
        if (!empty($params['days_past_due']) && $params['days_past_due'] !== 'all') {
            $range = $params['days_past_due'];
            if ($range === '0-7') {
                $where[] = "DATEDIFF(CURDATE(), i.due_date) BETWEEN 0 AND 7";
            } elseif ($range === '8-15') {
                $where[] = "DATEDIFF(CURDATE(), i.due_date) BETWEEN 8 AND 15";
            } elseif ($range === '16+') {
                $where[] = "DATEDIFF(CURDATE(), i.due_date) >= 16";
            }
        }
        if (!empty($params['from_date'])) {
            $where[] = "f.created_at >= :from_date";
            $bindings[':from_date'] = $params['from_date'];
        }
        if (!empty($params['to_date'])) {
            $where[] = "f.created_at <= :to_date";
            $bindings[':to_date'] = $params['to_date'];
        }

        $whereSql = !empty($where) ? 'WHERE ' . implode(' AND ', $where) : '';
        return ['whereSql' => $whereSql, 'bindings' => $bindings];
    }
}

