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

class ClosureRequestsController {
    private $conn;

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

    // CREATE (by agent)
    public function create($data, $userId) {
        $required = ['invoice_id', 'closure_type', 'original_amount', 'final_amount', 'reason', 'due_date'];
        foreach ($required as $field) {
            if (!isset($data[$field]) || trim($data[$field]) === '') {
                return json_encode(['status' => 'error', 'message' => "Missing: $field"]);
            }
        }

        $this->conn->beginTransaction();

        // Generate closure_id
        $query = "SELECT MAX(CAST(SUBSTRING(closure_id, 4) AS UNSIGNED)) AS max_id FROM closure_requests";
        $stmt = $this->conn->prepare($query);
        $stmt->execute();
        $maxId = $stmt->fetchColumn();
        $newId = $maxId ? $maxId + 1 : 1;
        $closureId = 'CL' . str_pad($newId, 5, '0', STR_PAD_LEFT);

        try {
            // Insert request
            $query = "INSERT INTO closure_requests (
                invoice_id, closure_id, closure_type, original_amount, final_amount, reason,
                requested_by, requested_by_name, due_date, ipo_comments
            ) VALUES (
                :invoice_id, :closure_id, :closure_type, :original_amount, :final_amount, :reason,
                :requested_by, :requested_by_name, :due_date, :ipo_comments
            )";

            $stmt = $this->conn->prepare($query);
            $stmt->bindValue(':invoice_id', $data['invoice_id']);
            $stmt->bindValue(':closure_id', $closureId);
            $stmt->bindValue(':closure_type', $data['closure_type']);
            $stmt->bindValue(':original_amount', $data['original_amount']);
            $stmt->bindValue(':final_amount', $data['final_amount']);
            $stmt->bindValue(':reason', $data['reason']);
            $stmt->bindValue(':requested_by', $userId);
            $stmt->bindValue(':requested_by_name', $data['requested_by_name']);
            $stmt->bindValue(':due_date', $data['due_date']);
            $stmt->bindValue(':ipo_comments', $data['ipo_comments'] ?? '');
            $stmt->execute();

            $requestId = $this->conn->lastInsertId();

            // Insert history
            $this->addHistory($requestId, 'submitted', $userId, $data['requested_by_name'], 'Initial closure request submitted');

            $this->conn->commit();
            return json_encode(['status' => 'success', 'id' => $requestId]);
        } catch (Exception $e) {
            $this->conn->rollBack();
            return json_encode(['status' => 'error', 'message' => 'Failed to create']);
        }
    }

    // LIST + FILTER
    public function getList($userId, $userRole, $filters = []) {
        // normalize role/id types to avoid strict comparison mismatches and accidental binding of missing params
        $userRole = (int)$userRole;
        $userId = (int)$userId;

        $query = "SELECT 
            cr.*, i.tax_number,
            CONCAT(i.first_name, ' ', i.last_name) AS taxpayer_name
        FROM closure_requests cr
        LEFT JOIN invoices i ON cr.invoice_id = i.invoice_number
        WHERE cr.deleted_at IS NULL";

        $params = [];

        // Role filter
        if ($userRole === 3) { // Agent
            $query .= " AND cr.requested_by = :user_id";
            $params[':user_id'] = $userId;
        } elseif ($userRole === 2) { // Supervisor
            $query .= " AND (cr.requested_by = :user_id OR cr.supervisor_id = :user_id OR cr.supervisor_id IS NULL)";
            $params[':user_id'] = $userId;
        }

        // Search
        if (!empty($filters['search'])) {
            $search = "%" . $filters['search'] . "%";
            $query .= " AND (cr.invoice_id LIKE :search OR cr.id LIKE :search OR i.first_name LIKE :search OR i.last_name LIKE :search)";
            $params[':search'] = $search;
        }

        // Filters
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $query .= " AND cr.status = :status";
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['closure_type']) && $filters['closure_type'] !== 'all') {
            $query .= " AND cr.closure_type = :closure_type";
            $params[':closure_type'] = $filters['closure_type'];
        }

        $query .= " ORDER BY cr.due_date ASC";

        $stmt = $this->conn->prepare($query);
        $stmt->execute($params);
        $requests = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Attach documents & history
        foreach ($requests as &$req) {
            $req['supporting_docs'] = $this->getDocuments($req['id']);
            $req['approval_history'] = $this->getHistory($req['id']);
        }

        return json_encode([
            'status' => 'success',
            'data' => $requests,
            'counts' => $this->getCounts($userId, $userRole)
        ]);
    }

    private function getCounts($userId, $userRole) {
        // normalize role/id types to avoid strict comparison mismatches and accidental binding of missing params
        $userRole = (int)$userRole;
        $userId = (int)$userId;

        $query = "SELECT 
            COUNT(*) as total,
            SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
            SUM(CASE WHEN status = 'requires_info' THEN 1 ELSE 0 END) as requires_info,
            SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
            SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected
        FROM closure_requests WHERE deleted_at IS NULL";

        if ($userRole === 3) {
            $query .= " AND requested_by = :user_id";
        } elseif ($userRole === 2) {
            $query .= " AND (supervisor_id = :user_id OR supervisor_id IS NULL)";
        }

        $stmt = $this->conn->prepare($query);
        if ($userRole !== 1) $stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }


    // GET ONE
    public function getOne($id, $userId, $userRole) {
        $query = "SELECT cr.*, i.amount as invoice_amount, i.tax_number,
                  CONCAT(i.first_name, ' ', i.last_name) AS taxpayer_name
                  FROM closure_requests cr
                  LEFT JOIN invoices i ON cr.invoice_id = i.invoice_number
                  WHERE cr.id = :id AND cr.deleted_at IS NULL";

        if ($userRole === 3) $query .= " AND cr.requested_by = :user_id";
        elseif ($userRole === 2) $query .= " AND (cr.supervisor_id = :user_id OR cr.supervisor_id IS NULL)";

        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':id', $id);
        if ($userRole !== 1) $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        $req = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$req) return json_encode(['status' => 'error', 'message' => 'Not found']);

        $req['supporting_docs'] = $this->getDocuments($id);
        $req['approval_history'] = $this->getHistory($id);

        return json_encode(['status' => 'success', 'data' => $req]);
    }

    private function getDocuments($requestId) {
        $stmt = $this->conn->prepare("SELECT filename FROM closure_documents WHERE closure_request_id = :id");
        $stmt->bindParam(':id', $requestId);
        $stmt->execute();
        return array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'filename');
    }

    private function getHistory($requestId) {
        $stmt = $this->conn->prepare("SELECT * FROM closure_approval_history WHERE closure_request_id = :id ORDER BY created_at ASC");
        $stmt->bindParam(':id', $requestId);
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function addHistory($requestId, $action, $userId, $userName, $comments = '') {
        $stmt = $this->conn->prepare("INSERT INTO closure_approval_history (
            closure_request_id, action, actor_id, actor_name, comments
        ) VALUES (:req_id, :action, :actor_id, :actor_name, :comments)");
        $stmt->bindParam(':req_id', $requestId);
        $stmt->bindParam(':action', $action);
        $stmt->bindParam(':actor_id', $userId);
        $stmt->bindParam(':actor_name', $userName);
        $stmt->bindParam(':comments', $comments);
        $stmt->execute();
    }

    // REVIEW (approve/reject/request_info)
    public function review($id, $data, $userId, $userName) {
        $action = $data['action'] ?? '';
        $comments = $data['comments'] ?? '';

        if (!in_array($action, ['approve','reject','request_info'])) {
            return json_encode(['status' => 'error', 'message' => 'Invalid action']);
        }

        $this->conn->beginTransaction();
        try {
            $statusMap = [
                'approve' => 'approved',
                'reject' => 'rejected',
                'request_info' => 'requires_info'
            ];
            $status = $statusMap[$action];

            $query = "UPDATE closure_requests 
                      SET status = :status, 
                          supervisor_id = :supervisor_id,
                          supervisor_name = :supervisor_name,
                          supervisor_comments = :comments
                      WHERE id = :id AND deleted_at IS NULL";

            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':status', $status);
            $stmt->bindParam(':supervisor_id', $userId);
            $stmt->bindParam(':supervisor_name', $userName);
            $stmt->bindParam(':comments', $comments);
            $stmt->bindParam(':id', $id);
            $stmt->execute();

            $this->addHistory($id, $action, $userId, $userName, $comments);

            $this->conn->commit();
            return json_encode(['status' => 'success', 'message' => 'Review submitted']);
        } catch (Exception $e) {
            $this->conn->rollBack();
            return json_encode(['status' => 'error', 'message' => 'Failed']);
        }
    }

    // BULK APPROVE
    public function bulkApprove($ids, $comments, $userId, $userName) {
        $this->conn->beginTransaction();
        try {
            $stmt = $this->conn->prepare("UPDATE closure_requests 
                                          SET status = 'approved', 
                                              supervisor_id = ?, 
                                              supervisor_name = ?, 
                                              supervisor_comments = ?
                                          WHERE id = ? AND status = 'pending' AND deleted_at IS NULL");

            foreach ($ids as $id) {
                $stmt->execute([$userId, $userName, $comments, $id]);
                $this->addHistory($id, 'approved', $userId, $userName, $comments);
            }

            $this->conn->commit();
            return json_encode(['status' => 'success', 'count' => count($ids)]);
        } catch (Exception $e) {
            $this->conn->rollBack();
            return json_encode(['status' => 'error', 'message' => 'Bulk failed']);
        }
    }
}
?>