<?php

namespace App\Controllers;

use App\Repositories\DeliveryRepository;
use App\Repositories\SupplyPlanRepository;
use App\Services\Logger;
use App\Services\SupplyBalanceService;
use App\Utils\Pagination;
use App\Utils\Response;
use App\Utils\Validator;
use PDOException;
use PDO;

class DeliveriesController
{
    private DeliveryRepository $deliveryRepository;
    private SupplyBalanceService $balanceService;
    private SupplyPlanRepository $planRepository;

    public function __construct()
    {
        $this->deliveryRepository = new DeliveryRepository();
        $this->balanceService = new SupplyBalanceService();
        $this->planRepository = new SupplyPlanRepository();
    }

    public function index(array $request): void
    {
        $requestStart = microtime(true);
        $user = $request['user'];
        $filters = [
            'plan_period_id' => $_GET['plan_period_id'] ?? null,
            'school_id' => $_GET['school_id'] ?? null,
            'commodity_id' => $_GET['commodity_id'] ?? null,
            'supplier_id' => $_GET['supplier_id'] ?? null,
            'review_status' => $_GET['review_status'] ?? null,
            'is_flagged' => $_GET['is_flagged'] ?? null,
            'date_from' => $_GET['date_from'] ?? null,
            'date_to' => $_GET['date_to'] ?? null,
        ];
        if ($user['role'] !== 'SUPER_ADMIN') {
            $filters['user_id'] = $user['id'];
        } else {
            $filters['user_id'] = $_GET['user_id'] ?? null;
        }
        [$page, $perPage] = Pagination::sanitize((int) ($_GET['page'] ?? 1), (int) ($_GET['per_page'] ?? 20), 300);
        $result = $this->deliveryRepository->list($filters, $page, $perPage);

        $durationMs = (microtime(true) - $requestStart) * 1000;

        Logger::info('deliveries.index.latency', [
            'duration_ms' => round($durationMs, 2),
            'user_id' => $user['id'],
            'role' => $user['role'],
            'filters' => array_filter($filters, fn($value) => $value !== null && $value !== ''),
            'returned_rows' => count($result['data'] ?? []),
            'page' => $page,
            'per_page' => $perPage,
        ]);

        Response::json($result, 200, 5);
    }

    public function store(array $request): void
    {
        $user = $request['user'];
        $input = json_decode(file_get_contents('php://input'), true) ?? [];
        $errors = Validator::requireFields($input, ['plan_period_id', 'school_id', 'commodity_id', 'delivery_date', 'actual_quantity']);
        if ($errors) {
            Response::json(['errors' => $errors], 422);
            return;
        }

        // Sanitize and validate inputs
        $deliveryDate = \App\Utils\InputSanitizer::date($input['delivery_date']);
        if (!$deliveryDate) {
            Response::json(['error' => 'Invalid delivery date format. Use YYYY-MM-DD'], 422);
            return;
        }

        $actualQuantity = \App\Utils\InputSanitizer::float($input['actual_quantity'], 0.001);
        if ($actualQuantity === null) {
            Response::json(['error' => 'Actual quantity must be a positive number'], 422);
            return;
        }

        $supplierId = isset($input['supplier_id']) ? \App\Utils\InputSanitizer::int($input['supplier_id']) : null;
        $supplierName = isset($input['supplier_name']) ? \App\Utils\InputSanitizer::text($input['supplier_name']) : null;
        $resolvedSupplierId = $this->resolveSupplierId($supplierId, $supplierName);

        // Parse optional unit details
        $units = null;
        if (!empty($input['units']) && is_array($input['units'])) {
            $units = array_map(function($unit) {
                return [
                    'unit_name' => \App\Utils\InputSanitizer::text($unit['unit_name'] ?? '', 100),
                    'unit_type' => \App\Utils\InputSanitizer::text($unit['unit_type'] ?? 'unknown', 50),
                    'unit_size' => \App\Utils\InputSanitizer::float($unit['unit_size'] ?? 1, 0.001, 99999),
                    'unit_measure' => \App\Utils\InputSanitizer::text($unit['unit_measure'] ?? 'pcs', 20),
                    'quantity' => \App\Utils\InputSanitizer::float($unit['quantity'] ?? 0, 0.001, 999999),
                    'notes' => isset($unit['notes']) ? \App\Utils\InputSanitizer::text($unit['notes'], 200) : null,
                ];
            }, $input['units']);
            
            // Filter out invalid entries
            $units = array_filter($units, fn($u) => $u['quantity'] > 0);
        }

        $payload = [
            'plan_period_id' => \App\Utils\InputSanitizer::int($input['plan_period_id']),
            'school_id' => \App\Utils\InputSanitizer::int($input['school_id']),
            'commodity_id' => \App\Utils\InputSanitizer::int($input['commodity_id']),
            'supplier_id' => $resolvedSupplierId,
            'delivery_date' => $deliveryDate,
            'actual_quantity' => $actualQuantity,
            'notes' => isset($input['notes']) ? \App\Utils\InputSanitizer::text($input['notes'], 1000) : null,
            'user_id' => $user['id'],
            'units' => $units,
        ];

        try {
            $result = $this->balanceService->applyDelivery($payload);
            
            \App\Services\Logger::info('Delivery recorded', [
                'user_id' => $user['id'],
                'delivery_id' => $result['delivery_id'] ?? null,
                'plan_period_id' => $payload['plan_period_id'],
                'school_id' => $payload['school_id'],
                'commodity_id' => $payload['commodity_id'],
                'quantity' => $actualQuantity,
                'has_unit_details' => $result['has_unit_details'] ?? false,
            ]);

            Response::json([
                'message' => 'Delivery recorded',
                'delivery_id' => $result['delivery_id'] ?? null,
                'expected_before' => $result['expected_before'],
                'remaining_after' => $result['remaining_after'],
                'review_status' => $result['review_status'] ?? null,
                'is_flagged' => $result['is_flagged'] ?? null,
                'flag_reason' => $result['flag_reason'] ?? null,
                'has_unit_details' => $result['has_unit_details'] ?? false,
                'base_unit_total' => $result['base_unit_total'] ?? null,
            ], 201);
        } catch (PDOException $e) {
            \App\Services\Logger::error('Delivery creation failed', [
                'user_id' => $user['id'],
                'error' => $e->getMessage(),
                'payload' => $payload,
            ]);
            Response::json(['error' => $e->getMessage()], 422);
        }
    }

    public function balance(array $request): void
    {
        $planPeriodId = (int) ($_GET['plan_period_id'] ?? 0);
        $schoolId = (int) ($_GET['school_id'] ?? 0);
        $commodityId = (int) ($_GET['commodity_id'] ?? 0);
        if (!$planPeriodId || !$schoolId || !$commodityId) {
            Response::json(['error' => 'Missing identifiers'], 422);
            return;
        }

        // Check if plan period is active
        $stmt = \App\Config\Database::getConnection()->prepare("SELECT is_active FROM plan_periods WHERE id = ?");
        $stmt->execute([$planPeriodId]);
        $isActive = (bool) $stmt->fetchColumn();

        if (!$isActive) {
             Response::json(['error' => 'Plan period is inactive', 'planned_quantity' => 0, 'remaining_quantity' => 0], 200, 0); // Return 0s or empty structure to indicate inactive
             return;
        }

        $balance = $this->planRepository->findBalance($planPeriodId, $schoolId, $commodityId);
        if (!$balance) {
            Response::json(['error' => 'Plan row not found'], 404);
            return;
        }
        Response::json($balance, 200, 15);
    }

    public function show(array $request): void
    {
        $id = (int) ($request['params'][0] ?? 0);
        $delivery = $this->deliveryRepository->find($id);

        if (!$delivery) {
            Response::json(['error' => 'Delivery not found'], 404);
            return;
        }

        // Check if user has permission to view this delivery
        $user = $request['user'] ?? null; // AuthMiddleware should populate this
        // If user is FIELD_STAFF, they should only see their own deliveries? 
        // The requirement doesn't explicitly say, but usually yes.
        // However, the list method filters by user_id for FIELD_STAFF.
        // Let's apply the same logic.
        
        // Note: The router passes the request which includes 'user' from AuthMiddleware.
        // But wait, the router dispatch logic in index.php passes $request to the handler.
        // The AuthMiddleware adds 'user' to the request array?
        // Let's check AuthMiddleware.
        
        // Assuming AuthMiddleware adds 'user' to $request.
        // But wait, Router.php:
        // $request = ['params' => $matches];
        // foreach ($route['middleware'] as $middleware) { $result = $middleware($request); ... $request = $result; }
        // So yes, middleware modifies $request.
        
        if ($user && $user['role'] !== 'SUPER_ADMIN' && $delivery['user_id'] != $user['id']) {
             Response::json(['error' => 'Unauthorized'], 403);
             return;
        }

        Response::json($delivery);
    }

    public function destroy(array $request): void
    {
        $id = (int) ($request['params'][0] ?? 0);
        $user = $request['user'];

        try {
            $repo = new \App\Repositories\DeliveryRepository();
            $delivery = $repo->find($id);
            
            if (!$delivery) {
                Response::json(['error' => 'Delivery not found'], 404);
                return;
            }

            if ($user['role'] !== 'SUPER_ADMIN' && $delivery['user_id'] != $user['id']) {
                Response::json(['error' => 'Unauthorized'], 403);
                return;
            }

            $this->balanceService->deleteDelivery($id, $user['id']);
            Response::json(['message' => 'Delivery deleted'], 200);

        } catch (PDOException $e) {
            Logger::error('Delivery deletion failed', ['error' => $e->getMessage(), 'id' => $id]);
            Response::json(['error' => 'Failed to delete delivery'], 500);
        }
    }

    public function updateStatus(array $request): void
    {
        $user = $request['user'];
        if ($user['role'] !== 'SUPER_ADMIN') {
            Response::json(['error' => 'Unauthorized'], 403);
            return;
        }

        $id = (int) ($request['params'][0] ?? 0);
        $input = json_decode(file_get_contents('php://input'), true);
        
        if (!isset($input['status']) || !in_array($input['status'], ['APPROVED', 'REJECTED', 'PENDING_REVIEW'])) {
            Response::json(['error' => 'Invalid status. Allowed: APPROVED, REJECTED, PENDING_REVIEW'], 422);
            return;
        }

        $success = $this->deliveryRepository->updateStatus($id, $input['status']);

        if ($success) {
            \App\Services\Logger::info('Delivery status updated', [
                'delivery_id' => $id,
                'new_status' => $input['status'],
                'admin_id' => $user['id']
            ]);

            // TRIGGER QA FIX: Recalculate remaining quantity immediately
            try {
                $delivery = $this->deliveryRepository->find($id);
                if ($delivery) {
                    $qa = new \App\Services\QAQuantitiesService();
                    $qa->recalculateAndFix(
                        (int)$delivery['plan_period_id'], 
                        (int)$delivery['school_id'], 
                        (int)$delivery['commodity_id']
                    );
                }
            } catch (\Exception $e) {
                // Do not block the response, but log the failure
                \App\Services\Logger::error('QA Fix failed in controller', ['error' => $e->getMessage()]);
            }

            Response::json(['message' => 'Status updated successfully']);
        } else {
            Response::json(['error' => 'Failed to update status'], 500);
        }
    }

    private function resolveSupplierId(?int $id, ?string $name): ?int
    {
        $db = \App\Config\Database::getConnection();
        $trimmedName = $name !== null ? trim($name) : null;

        if ($id) {
            $stmt = $db->prepare('SELECT id FROM suppliers WHERE id = :id LIMIT 1');
            $stmt->bindValue(':id', $id, PDO::PARAM_INT);
            $stmt->execute();
            $existing = $stmt->fetchColumn();
            if ($existing) {
                return (int) $existing;
            }

            if ($trimmedName && $trimmedName !== '') {
                $this->insertSupplierRecord($db, $trimmedName, $id);
                return $id;
            }

            Logger::warning('deliveries.supplier_missing_name_for_id', [
                'supplier_id' => $id,
                'message' => 'Supplier ID not found locally and no name provided to seed record.',
            ]);
            return null;
        }

        if (!$trimmedName) {
            return null;
        }

        $stmt = $db->prepare('SELECT id FROM suppliers WHERE name = :name LIMIT 1');
        $stmt->bindValue(':name', $trimmedName);
        $stmt->execute();
        $existing = $stmt->fetchColumn();

        if ($existing) {
            return (int) $existing;
        }

        return $this->insertSupplierRecord($db, $trimmedName);
    }

    private function insertSupplierRecord(\PDO $db, string $name, ?int $desiredId = null): int
    {
        if ($desiredId) {
            $stmt = $db->prepare('INSERT INTO suppliers (id, name, is_active) VALUES (:id, :name, 1)');
            $stmt->bindValue(':id', $desiredId, PDO::PARAM_INT);
        } else {
            $stmt = $db->prepare('INSERT INTO suppliers (name, is_active) VALUES (:name, 1)');
        }
        $stmt->bindValue(':name', $name);
        $stmt->execute();

        return $desiredId ? $desiredId : (int) $db->lastInsertId();
    }
    public function export(array $request): void
    {
        $user = $request['user'];
        $filters = [
            'plan_period_id' => $_GET['plan_period_id'] ?? null,
            'school_id' => $_GET['school_id'] ?? null,
            'commodity_id' => $_GET['commodity_id'] ?? null,
            'supplier_id' => $_GET['supplier_id'] ?? null,
            'date_from' => $_GET['date_from'] ?? null,
            'date_to' => $_GET['date_to'] ?? null,
        ];
        if ($user['role'] !== 'SUPER_ADMIN') {
            $filters['user_id'] = $user['id'];
        } else {
            $filters['user_id'] = $_GET['user_id'] ?? null;
        }

        // Fetch all records without pagination
        $result = $this->deliveryRepository->list($filters, 1, 100000);
        $data = $result['data'] ?? [];

        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="deliveries_export_' . date('Y-m-d_H-i') . '.csv"');

        $output = fopen('php://output', 'w');
        
        // Header row
        fputcsv($output, [
            'delivery_id',
            'plan_period_id',
            'school_id',
            'school_name',
            'commodity_id',
            'commodity_name',
            'supplier_id',
            'supplier_name',
            'user_id',
            'user_name',
            'delivery_date',
            'actual_quantity',
            'expected_quantity_before',
            'remaining_quantity_after',
            'review_status',
            'is_flagged',
            'flag_reason',
            'notes',
        ]);

        foreach ($data as $row) {
            fputcsv($output, [
                $row['id'],
                $row['plan_period_id'],
                $row['school_id'],
                $row['school_name'],
                $row['commodity_id'],
                $row['commodity_name'],
                $row['supplier_id'] ?? '',
                $row['supplier_name'] ?? '',
                $row['user_id'],
                $row['user_name'],
                $row['delivery_date'],
                $row['actual_quantity'],
                $row['expected_quantity_before'] ?? '',
                $row['remaining_quantity_after'] ?? '',
                $row['review_status'] ?? '',
                $row['is_flagged'] ?? 0,
                $row['flag_reason'] ?? '',
                $row['notes'] ?? ''
            ]);
        }

        fclose($output);
        exit;
    }

    public function import(array $request): void
    {
        $user = $request['user'];
        if ($user['role'] !== 'SUPER_ADMIN') {
            Response::json(['error' => 'Unauthorized'], 403);
            return;
        }

        if (empty($_FILES['file']) || $_FILES['file']['error'] !== UPLOAD_ERR_OK) {
            Response::json(['error' => 'Missing or invalid CSV file'], 422);
            return;
        }

        $overwrite = isset($_POST['overwrite']) && $_POST['overwrite'] === '1';
        $file = $_FILES['file'];

        $handle = fopen($file['tmp_name'], 'r');
        if (!$handle) {
            Response::json(['error' => 'Unable to read CSV file'], 422);
            return;
        }

        $header = fgetcsv($handle);
        if (!$header) {
            fclose($handle);
            Response::json(['error' => 'CSV header missing'], 422);
            return;
        }

        $headerMap = [];
        foreach ($header as $index => $name) {
            $key = strtolower(trim($name));
            $headerMap[$key] = $index;
        }

        $hasCanonical = array_key_exists('plan_period_id', $headerMap)
            && array_key_exists('school_id', $headerMap)
            && array_key_exists('commodity_id', $headerMap)
            && array_key_exists('delivery_date', $headerMap)
            && array_key_exists('actual_quantity', $headerMap);

        $hasLegacy = array_key_exists('date', $headerMap)
            && array_key_exists('school', $headerMap)
            && array_key_exists('commodity', $headerMap)
            && array_key_exists('quantity', $headerMap);

        if (!$hasCanonical && !$hasLegacy) {
            fclose($handle);
            Response::json(['error' => 'CSV missing required columns. Use export template.'], 422);
            return;
        }

        $db = \App\Config\Database::getConnection();

        $activePlanPeriodId = null;
        if (!$hasCanonical) {
            $activePlanPeriodId = $this->getActivePlanPeriodId($db);
            if (!$activePlanPeriodId) {
                fclose($handle);
                Response::json(['error' => 'Active plan period not found. Please include plan_period_id in CSV.'], 422);
                return;
            }
        }

        $imported = 0;
        $errors = 0;
        $planPeriodIds = [];

        if ($overwrite) {
            $db->beginTransaction();
            try {
                // Clear delivery units first
                $db->exec('DELETE FROM delivery_units');
                $db->exec('DELETE FROM deliveries');
                // Reset remaining to planned
                $db->exec('UPDATE supply_plans SET remaining_quantity = planned_quantity');
                $db->commit();
            } catch (\Throwable $e) {
                $db->rollBack();
                fclose($handle);
                Response::json(['error' => 'Failed to overwrite deliveries', 'message' => $e->getMessage()], 500);
                return;
            }
        }

        while (($row = fgetcsv($handle)) !== false) {
            try {
                $planPeriodId = $hasCanonical
                    ? (int) ($row[$headerMap['plan_period_id']] ?? 0)
                    : (int) $activePlanPeriodId;

                $schoolId = $hasCanonical
                    ? (int) ($row[$headerMap['school_id']] ?? 0)
                    : $this->resolveSchoolIdByName($db, $row[$headerMap['school']] ?? null);

                $commodityId = $hasCanonical
                    ? (int) ($row[$headerMap['commodity_id']] ?? 0)
                    : $this->resolveCommodityIdByName($db, $row[$headerMap['commodity']] ?? null);

                $deliveryDate = $hasCanonical
                    ? ($row[$headerMap['delivery_date']] ?? null)
                    : ($row[$headerMap['date']] ?? null);

                $actualQuantity = $hasCanonical
                    ? (float) ($row[$headerMap['actual_quantity']] ?? 0)
                    : (float) ($row[$headerMap['quantity']] ?? 0);

                if (!$planPeriodId || !$schoolId || !$commodityId || !$deliveryDate || $actualQuantity <= 0) {
                    $errors++;
                    continue;
                }

                $supplierId = isset($headerMap['supplier_id']) ? (int) ($row[$headerMap['supplier_id']] ?? 0) : null;
                $supplierName = isset($headerMap['supplier_name']) ? ($row[$headerMap['supplier_name']] ?? null) : null;
                if (!$supplierName && isset($headerMap['supplier'])) {
                    $supplierName = $row[$headerMap['supplier']] ?? null;
                }
                if ($supplierName && strtolower(trim($supplierName)) === 'n/a') {
                    $supplierName = null;
                }

                $userId = isset($headerMap['user_id']) ? (int) ($row[$headerMap['user_id']] ?? 0) : 0;
                if (!$userId && isset($headerMap['user'])) {
                    $userId = $this->resolveUserIdByName($db, $row[$headerMap['user']] ?? null) ?? 0;
                }
                if (!$userId) {
                    $userId = $user['id'];
                }

                $notes = isset($headerMap['notes']) ? ($row[$headerMap['notes']] ?? null) : null;

                $reviewStatus = isset($headerMap['review_status']) ? strtoupper(trim((string) ($row[$headerMap['review_status']] ?? ''))) : null;
                $isFlagged = isset($headerMap['is_flagged']) ? (int) ($row[$headerMap['is_flagged']] ?? 0) : 0;
                $flagReason = isset($headerMap['flag_reason']) ? ($row[$headerMap['flag_reason']] ?? null) : null;

                $resolvedSupplierId = $this->resolveSupplierId($supplierId ?: null, $supplierName ?: null);

                $payload = [
                    'plan_period_id' => $planPeriodId,
                    'school_id' => $schoolId,
                    'commodity_id' => $commodityId,
                    'supplier_id' => $resolvedSupplierId,
                    'delivery_date' => $deliveryDate,
                    'actual_quantity' => $actualQuantity,
                    'notes' => $notes,
                    'user_id' => $userId,
                    'units' => null,
                ];

                $result = $this->balanceService->applyDelivery($payload);
                $deliveryId = $result['delivery_id'] ?? null;

                if ($deliveryId && $reviewStatus && in_array($reviewStatus, ['APPROVED', 'REJECTED', 'PENDING_REVIEW'])) {
                    $this->deliveryRepository->updateStatusAndFlags(
                        (int) $deliveryId,
                        $reviewStatus,
                        $isFlagged,
                        $flagReason
                    );
                }

                $planPeriodIds[$planPeriodId] = true;
                $imported++;
            } catch (\Throwable $e) {
                $errors++;
            }
        }

        fclose($handle);

        // Recompute metrics for affected plan periods
        $metrics = new \App\Services\PlanPeriodMetricsService($db);
        foreach (array_keys($planPeriodIds) as $pid) {
            $metrics->syncFromPlans((int) $pid);
        }

        // Run QA to normalize any remaining discrepancies
        try {
            $qa = new \App\Services\QAQuantitiesService();
            $qa->recalculateAll();
        } catch (\Throwable $e) {
            // Non-fatal
        }

        Response::json([
            'message' => 'Import completed',
            'imported' => $imported,
            'errors' => $errors,
            'overwrite' => $overwrite,
        ]);
    }

    private function getActivePlanPeriodId(\PDO $db): ?int
    {
        $stmt = $db->prepare('SELECT id FROM plan_periods WHERE is_active = 1 ORDER BY id DESC LIMIT 1');
        $stmt->execute();
        $id = $stmt->fetchColumn();
        return $id ? (int) $id : null;
    }

    private function resolveSchoolIdByName(\PDO $db, ?string $name): ?int
    {
        $trimmed = $name ? trim($name) : null;
        if (!$trimmed) {
            return null;
        }
        $stmt = $db->prepare('SELECT id FROM schools WHERE name = :name LIMIT 1');
        $stmt->bindValue(':name', $trimmed);
        $stmt->execute();
        $id = $stmt->fetchColumn();
        return $id ? (int) $id : null;
    }

    private function resolveCommodityIdByName(\PDO $db, ?string $name): ?int
    {
        $trimmed = $name ? trim($name) : null;
        if (!$trimmed) {
            return null;
        }
        $stmt = $db->prepare('SELECT id FROM commodities WHERE name = :name LIMIT 1');
        $stmt->bindValue(':name', $trimmed);
        $stmt->execute();
        $id = $stmt->fetchColumn();
        return $id ? (int) $id : null;
    }

    private function resolveUserIdByName(\PDO $db, ?string $name): ?int
    {
        $trimmed = $name ? trim($name) : null;
        if (!$trimmed) {
            return null;
        }
        $stmt = $db->prepare('SELECT id FROM users WHERE name = :name LIMIT 1');
        $stmt->bindValue(':name', $trimmed);
        $stmt->execute();
        $id = $stmt->fetchColumn();
        return $id ? (int) $id : null;
    }
}