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

class InvoicesController {
    private $conn;

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

    public function getAllOpenInvoices($filters) {
        try{
            $limit = isset($filters['limit']) ? (int)$filters['limit'] : 10;
            $offset = isset($filters['offset']) ? (int)$filters['offset'] : 0;
            $page = isset($filters['page']) ? (int)$filters['page'] : 1;
            
            // Calculate offset from page if not provided
            if ($offset === 0 && $page > 1) {
                $offset = ($page - 1) * $limit;
            }

            // Fixed JOIN - using taxpayer_id instead of tax_number
            $query = "SELECT i.*, CONCAT(u.first_name, ' ', u.last_name) as assignedTo
                    FROM invoices i 
                    LEFT JOIN users u ON i.assigned_to = u.id
                    WHERE i.status = 'open'";
            
            $params = [];
            
            if (!empty($filters['mda'])) {
                $query .= " AND i.mda LIKE :mda";
                $params[':mda'] = "%" . $filters['mda'] . "%";
            }
            if (!empty($filters['amount_min'])) {
                $query .= " AND i.amount >= :amount_min";
                $params[':amount_min'] = (float)$filters['amount_min'];
            }
            if (!empty($filters['amount_max'])) {
                $query .= " AND i.amount <= :amount_max";
                $params[':amount_max'] = (float)$filters['amount_max'];
            }
            if (!empty($filters['overdue_days_min'])) {
                $query .= " AND i.overdue_days >= :overdue_days_min";
                $params[':overdue_days_min'] = (int)$filters['overdue_days_min'];
            }
            if (!empty($filters['invoice_type'])) {
                $query .= " AND i.invoice_type = :invoice_type";
                $params[':invoice_type'] = $filters['invoice_type'];
            }
            if (!empty($filters['tax_number'])) {
                $query .= " AND i.tax_number LIKE :tax_number";
                $params[':tax_number'] = "%" . $filters['tax_number'] . "%";
            }
            if (!empty($filters['assigned_to'])) {
                $query .= " AND i.assigned_to LIKE :assigned_to";
                $params[':assigned_to'] = "%" . $filters['assigned_to'] . "%";
            }
            // supervisor_id
            if (!empty($filters['supervisor_id'])) {
                $query .= " AND u.supervisor_id LIKE :supervisor_id";
                $params[':supervisor_id'] = "%" . $filters['supervisor_id'] . "%";
            }

            $query .= " ORDER BY i.amount DESC LIMIT :limit OFFSET :offset";
            $params[':limit'] = $limit;
            $params[':offset'] = $offset;

            $stmt = $this->conn->prepare($query);
            
            // Bind parameters with proper types
            foreach ($params as $key => $value) {
                if ($key === ':limit' || $key === ':offset' || $key === ':overdue_days_min') {
                    $stmt->bindValue($key, $value, PDO::PARAM_INT);
                } else if ($key === ':amount_min' || $key === ':amount_max') {
                    $stmt->bindValue($key, $value, PDO::PARAM_STR); // Use STR for decimal
                } else {
                    $stmt->bindValue($key, $value, PDO::PARAM_STR);
                }
            }

            $stmt->execute();
            $invoices = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Count query without JOIN for better performance
            $count_query = "SELECT COUNT(*) as total FROM invoices i LEFT JOIN users u ON i.assigned_to = u.id WHERE i.status = 'open'";
            $count_params = [];
            
            if (!empty($filters['mda'])) {
                $count_query .= " AND i.mda LIKE :mda";
                $count_params[':mda'] = "%" . $filters['mda'] . "%";
            }
            if (!empty($filters['amount_min'])) {
                $count_query .= " AND i.amount >= :amount_min";
                $count_params[':amount_min'] = (float)$filters['amount_min'];
            }
            if (!empty($filters['amount_max'])) {
                $count_query .= " AND i.amount <= :amount_max";
                $count_params[':amount_max'] = (float)$filters['amount_max'];
            }
            if (!empty($filters['overdue_days_min'])) {
                $count_query .= " AND i.overdue_days >= :overdue_days_min";
                $count_params[':overdue_days_min'] = (int)$filters['overdue_days_min'];
            }
            if (!empty($filters['invoice_type'])) {
                $count_query .= " AND i.invoice_type = :invoice_type";
                $count_params[':invoice_type'] = $filters['invoice_type'];
            }
            if (!empty($filters['tax_number'])) {
                $count_query .= " AND i.tax_number LIKE :tax_number";
                $count_params[':tax_number'] = "%" . $filters['tax_number'] . "%";
            }
            if (!empty($filters['assigned_to'])) {
                $count_query .= " AND i.assigned_to LIKE :assigned_to";
                $count_params[':assigned_to'] = "%" . $filters['assigned_to'] . "%";
            }
            if (!empty($filters['supervisor_id'])) {
                $count_query .= " AND u.supervisor_id LIKE :supervisor_id";
                $count_params[':supervisor_id'] = "%" . $filters['supervisor_id'] . "%";
            }

            $stmt_count = $this->conn->prepare($count_query);
            
            foreach ($count_params as $key => $value) {
                if ($key === ':overdue_days_min') {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_INT);
                } else if ($key === ':amount_min' || $key === ':amount_max') {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_STR);
                } else {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_STR);
                }
            }
            
            $stmt_count->execute();
            $totalRecords = $stmt_count->fetch(PDO::FETCH_ASSOC)['total'];

            $totalPages = $totalRecords > 0 ? ceil($totalRecords / $limit) : 1;

            return json_encode([
                'status' => 'success',
                'data' => [
                    'invoices' => $invoices,
                    'pagination' => [
                        'current_page' => $page,
                        'total_pages' => $totalPages,
                        'total_records' => (int)$totalRecords,
                    ]
                ]
            ], JSON_PRETTY_PRINT);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function getActiveInvoices($filters) {
        // Similar to getAllOpenInvoices, but status = 'active'
        $filters['status'] = 'active';
        return $this->getInvoicesByStatus($filters);
    }

    public function getClosedInvoices($filters) {
        // Similar, status = 'closed'
        $filters['status'] = 'closed';
        return $this->getInvoicesByStatus($filters);
    }

    private function getInvoicesByStatus($filters) {
        try{
            $limit = isset($filters['limit']) ? (int)$filters['limit'] : 10;
            $offset = isset($filters['offset']) ? (int)$filters['offset'] : 0;
            $page = isset($filters['page']) ? (int)$filters['page'] : 1;
            $status = isset($filters['status']) ? $filters['status'] : 'open';
            
            if ($offset === 0 && $page > 1) {
                $offset = ($page - 1) * $limit;
            }

            $query = "SELECT i.* 
                    FROM invoices i 
                    WHERE i.status = :status";
            
            $params = [':status' => $status];
            
            // Add filters (same as getAllOpenInvoices)
            if (!empty($filters['mda'])) {
                $query .= " AND i.mda LIKE :mda";
                $params[':mda'] = "%" . $filters['mda'] . "%";
            }
            if (!empty($filters['amount_min'])) {
                $query .= " AND i.amount >= :amount_min";
                $params[':amount_min'] = (float)$filters['amount_min'];
            }
            if (!empty($filters['amount_max'])) {
                $query .= " AND i.amount <= :amount_max";
                $params[':amount_max'] = (float)$filters['amount_max'];
            }
            if (!empty($filters['overdue_days_min'])) {
                $query .= " AND i.overdue_days >= :overdue_days_min";
                $params[':overdue_days_min'] = (int)$filters['overdue_days_min'];
            }

            $query .= " ORDER BY i.updated_at DESC LIMIT :limit OFFSET :offset";
            $params[':limit'] = $limit;
            $params[':offset'] = $offset;

            $stmt = $this->conn->prepare($query);
            
            foreach ($params as $key => $value) {
                if ($key === ':limit' || $key === ':offset' || $key === ':overdue_days_min') {
                    $stmt->bindValue($key, $value, PDO::PARAM_INT);
                } else if ($key === ':amount_min' || $key === ':amount_max') {
                    $stmt->bindValue($key, $value, PDO::PARAM_STR);
                } else {
                    $stmt->bindValue($key, $value, PDO::PARAM_STR);
                }
            }

            $stmt->execute();
            $invoices = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Count query
            $count_query = "SELECT COUNT(*) as total FROM invoices i WHERE i.status = :status";
            $count_params = [':status' => $status];
            
            if (!empty($filters['mda'])) {
                $count_query .= " AND i.mda LIKE :mda";
                $count_params[':mda'] = "%" . $filters['mda'] . "%";
            }
            if (!empty($filters['amount_min'])) {
                $count_query .= " AND i.amount >= :amount_min";
                $count_params[':amount_min'] = (float)$filters['amount_min'];
            }
            if (!empty($filters['amount_max'])) {
                $count_query .= " AND i.amount <= :amount_max";
                $count_params[':amount_max'] = (float)$filters['amount_max'];
            }
            if (!empty($filters['overdue_days_min'])) {
                $count_query .= " AND i.overdue_days >= :overdue_days_min";
                $count_params[':overdue_days_min'] = (int)$filters['overdue_days_min'];
            }

            $stmt_count = $this->conn->prepare($count_query);
            
            foreach ($count_params as $key => $value) {
                if ($key === ':overdue_days_min') {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_INT);
                } else if ($key === ':amount_min' || $key === ':amount_max') {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_STR);
                } else {
                    $stmt_count->bindValue($key, $value, PDO::PARAM_STR);
                }
            }
            
            $stmt_count->execute();
            $totalRecords = $stmt_count->fetch(PDO::FETCH_ASSOC)['total'];

            $totalPages = $totalRecords > 0 ? ceil($totalRecords / $limit) : 1;

            return json_encode([
                'status' => 'success',
                'data' => [
                    'invoices' => $invoices,
                    'pagination' => [
                        'current_page' => $page,
                        'total_pages' => $totalPages,
                        'total_records' => (int)$totalRecords,
                    ]
                ]
            ], JSON_PRETTY_PRINT);
        } catch (Exception $e) {
            return json_encode(['status' => 'error', 'message' => 'Exception: ' . $e->getMessage()]);
        }
    }

    public function assignInvoices($data) {
        if (
            !isset($data['invoice_ids'], $data['assigned_to']) ||
            !is_array($data['invoice_ids']) ||
            empty($data['invoice_ids'])
        ) {
            return json_encode(['status' => 'error', 'message' => 'Missing or invalid invoice_ids or assigned_to']);
        }

        // Assign invoices to user
        $placeholders = implode(',', array_fill(0, count($data['invoice_ids']), '?'));
        $query = "UPDATE invoices SET assigned_to = ?, updated_at = NOW() WHERE id IN ($placeholders)";
        $stmt = $this->conn->prepare($query);
        $params = array_merge([$data['assigned_to']], $data['invoice_ids']);

        foreach ($params as $index => $value) {
            $stmt->bindValue($index + 1, $value, PDO::PARAM_INT);
        }

        $assignResult = $stmt->execute();

        // If role is set, handle follow-up assignment for each invoice
        if (!empty($data['role'])) {
            foreach ($data['invoice_ids'] as $invoice_id) {
                // Check if follow-up already exists for this invoice and user
                $checkQuery = "SELECT id FROM follow_ups WHERE invoice_id = :invoice_id AND ipo_id = :ipo_id";
                $checkStmt = $this->conn->prepare($checkQuery);
                $checkStmt->bindParam(':invoice_id', $invoice_id, PDO::PARAM_INT);
                $checkStmt->bindParam(':ipo_id', $data['assigned_to'], PDO::PARAM_INT);
                $checkStmt->execute();
                $existing = $checkStmt->fetch(PDO::FETCH_ASSOC);

                if ($existing) {
                    // Update status to 'active' and reset attempts
                    $updateQuery = "UPDATE follow_ups SET status = 'active', attempts = 0 WHERE id = :id";
                    $updateStmt = $this->conn->prepare($updateQuery);
                    $updateStmt->bindParam(':id', $existing['id'], PDO::PARAM_INT);
                    $updateStmt->execute();
                } else {
                    // Insert new follow-up
                    $insertQuery = "INSERT INTO follow_ups (invoice_id, ipo_id, status, attempts) VALUES (:invoice_id, :ipo_id, 'active', 0)";
                    $insertStmt = $this->conn->prepare($insertQuery);
                    $insertStmt->bindParam(':invoice_id', $invoice_id, PDO::PARAM_INT);
                    $insertStmt->bindParam(':ipo_id', $data['assigned_to'], PDO::PARAM_INT);
                    $insertStmt->execute();
                }
            }
        }

        if ($assignResult) {
            return json_encode(['status' => 'success', 'message' => 'Invoices assigned successfully']);
        } else {
            return json_encode(['status' => 'error', 'message' => 'Error assigning invoices']);
        }
    }

    public function getInvoiceStatistics($filters = []) {
        try {
            $hasAssigned = !empty($filters['assigned_to']);
            $assignedWhere = $hasAssigned ? " AND assigned_to = :assigned_to" : "";
            $sumWhere = $hasAssigned ? " WHERE i.assigned_to = :assigned_to" : "";

            $query = "SELECT 
                COALESCE(SUM(i.amount), 0) as total_amount,
                (SELECT COUNT(*) FROM invoices WHERE 1=1{$assignedWhere}) as all_invoices,
                (SELECT COUNT(*) FROM invoices WHERE status = 'open'{$assignedWhere}) as open_invoices,
                (SELECT COUNT(*) FROM invoices WHERE status = 'active'{$assignedWhere}) as active_invoices,
                (SELECT COUNT(*) FROM invoices WHERE status = 'closed'{$assignedWhere}) as closed_invoices,
                (SELECT COUNT(*) FROM invoices WHERE overdue_days > 0{$assignedWhere}) as overdue_invoices
                FROM invoices i{$sumWhere}";

            $stmt = $this->conn->prepare($query);

            if ($hasAssigned) {
                // assigned_to is expected to be an integer user ID
                $stmt->bindValue(':assigned_to', (int)$filters['assigned_to'], PDO::PARAM_INT);
            }

            $stmt->execute();
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode([
                'status' => 'success',
                'data' => [
                    'totalAmount' => (float)$stats['total_amount'],
                    'all' => (int)$stats['all_invoices'],
                    'open' => (int)$stats['open_invoices'],
                    'active' => (int)$stats['active_invoices'],
                    'closed' => (int)$stats['closed_invoices'],
                    'overdue' => (int)$stats['overdue_invoices']
                ]
            ]);
        } catch (PDOException $e) {
            return json_encode([
                'status' => 'error',
                'message' => 'Database error: ' . $e->getMessage()
            ]);
        } catch (Exception $e) {
            return json_encode([
                'status' => 'error',
                'message' => 'Unexpected error: ' . $e->getMessage()
            ]);
        }
    }

    public function getTeamInvoiceStatistics($filters = []) {
        try {
            $hasAssigned = !empty($filters['assigned_to']);
            $hasSupervisor = !empty($filters['supervisor_id']);
            
            // Modified to include JOIN with users table
            $query = "SELECT 
                COALESCE(SUM(i.amount), 0) as total_amount,
                COUNT(*) as all_invoices,
                SUM(CASE WHEN i.status = 'open' THEN 1 ELSE 0 END) as open_invoices,
                SUM(CASE WHEN i.status = 'active' THEN 1 ELSE 0 END) as active_invoices,
                SUM(CASE WHEN i.status = 'closed' THEN 1 ELSE 0 END) as closed_invoices,
                SUM(CASE WHEN i.overdue_days > 0 THEN 1 ELSE 0 END) as overdue_invoices
                FROM invoices i 
                LEFT JOIN users u ON i.assigned_to = u.id
                WHERE 1=1";

            $params = [];

            if ($hasAssigned) {
                $query .= " AND i.assigned_to = :assigned_to";
                $params[':assigned_to'] = (int)$filters['assigned_to'];
            }

            if ($hasSupervisor) {
                $query .= " AND u.supervisor_id = :supervisor_id";
                $params[':supervisor_id'] = $filters['supervisor_id'];
            }

            $stmt = $this->conn->prepare($query);

            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value, PDO::PARAM_INT);
            }

            $stmt->execute();
            $stats = $stmt->fetch(PDO::FETCH_ASSOC);

            return json_encode([
                'status' => 'success',
                'data' => [
                    'totalAmount' => (float)$stats['total_amount'],
                    'all' => (int)$stats['all_invoices'],
                    'open' => (int)$stats['open_invoices'],
                    'active' => (int)$stats['active_invoices'],
                    'closed' => (int)$stats['closed_invoices'],
                    'overdue' => (int)$stats['overdue_invoices']
                ]
            ]);
        } catch (PDOException $e) {
            return json_encode([
                'status' => 'error',
                'message' => 'Database error: ' . $e->getMessage()
            ]);
        } catch (Exception $e) {
            return json_encode([
                'status' => 'error', 
                'message' => 'Unexpected error: ' . $e->getMessage()
            ]);
        }
    }

    public function markAsPaid($data) {
        if (!isset($data['id'])) {
            return json_encode(['status' => 'error', 'message' => 'Missing invoice ID']);
        }

        $query = "UPDATE invoices SET status = 'paid', paid_at = NOW(), updated_at = NOW() WHERE id = :id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $data['id'], PDO::PARAM_INT);

        if ($stmt->execute()) {
            return json_encode(['status' => 'success', 'message' => 'Invoice marked as paid']);
        } else {
            return json_encode(['status' => 'error', 'message' => 'Error updating invoice']);
        }
    }
}
?>