<?php
/**
 * Ongoing Projects Controller
 */
class ProjectController extends Controller {
    
    public function index() {
        // Check permission
        if (!Auth::can('projects.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view projects']);
            return;
        }
        
        // Fetch projects with joins
        $projects = Database::fetchAll(
            "SELECT p.*, s.state_name, l.lga_name, z.zone_name, c.command_name 
             FROM ongoing_projects p
             LEFT JOIN states s ON p.state_id = s.id
             LEFT JOIN lgas l ON p.lga_id = l.id
             LEFT JOIN zones z ON p.zone_id = z.id
             LEFT JOIN commands c ON p.command_id = c.id
             ORDER BY p.created_at DESC"
        );
        
        if ($projects === false) $projects = [];
        
        // Calculate statistics
        $statistics = [
            'total' => count($projects),
            'by_status' => [
                'Planning' => ['count' => 0, 'value' => 0],
                'In Progress' => ['count' => 0, 'value' => 0],
                'Abandoned' => ['count' => 0, 'value' => 0],
                'Completed' => ['count' => 0, 'value' => 0],
                'Terminated' => ['count' => 0, 'value' => 0]
            ],
            'total_value' => 0,
            'overdue' => 0
        ];
        
        $today = time();
        
        foreach ($projects as $project) {
            $statistics['total_value'] += floatval($project['contract_sum'] ?? 0);
            
            $status = $project['status'] ?? 'Planning';
            
            // Normalize status to match array keys
            $normalizedStatus = $status;
            if ($status === 'In_Progress') {
                $normalizedStatus = 'In Progress';
            }
            
            if (isset($statistics['by_status'][$normalizedStatus])) {
                $statistics['by_status'][$normalizedStatus]['count']++;
                $statistics['by_status'][$normalizedStatus]['value'] += floatval($project['contract_sum'] ?? 0);
            }
            
            // Check if overdue (exclude completed, abandoned, terminated)
            if (!empty($project['expected_completion_date']) && 
                strtotime($project['expected_completion_date']) < $today && 
                !in_array($project['status'], ['Completed', 'Cancelled', 'Terminated', 'Abandoned'])) {
                $statistics['overdue']++;
            }
        }
        
        // Get zones for filter
        $zones = Database::fetchAll("SELECT * FROM zones ORDER BY zone_name");
        if ($zones === false) $zones = [];
        
        $this->view('projects/index', [
            'projects' => $projects,
            'statistics' => $statistics,
            'zones' => $zones
        ]);
    }
    
    public function create() {
        // Check permission
        if (!Auth::can('projects.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to create projects']);
            return;
        }
        
        $states = Database::fetchAll("SELECT * FROM states ORDER BY state_name");
        $zones = Database::fetchAll("SELECT * FROM zones ORDER BY zone_name");
        $commands = Database::fetchAll("SELECT * FROM commands ORDER BY command_name");
        
        if ($states === false) $states = [];
        if ($zones === false) $zones = [];
        if ($commands === false) $commands = [];
        
        $this->view('projects/create', [
            'states' => $states,
            'zones' => $zones,
            'commands' => $commands
        ]);
    }
    
    public function store() {
        // Check permission
        if (!Auth::can('projects.create')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to create projects']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect('projects/create', ['error' => 'Invalid security token']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['project_title'])) {
            $errors['project_title'] = 'Project title is required';
        } elseif (strlen($_POST['project_title']) > 255) {
            $errors['project_title'] = 'Project title must not exceed 255 characters';
        }
        
        if (empty($_POST['project_type'])) {
            $errors['project_type'] = 'Project type is required';
        }
        
        if (empty($_POST['state_id']) || !is_numeric($_POST['state_id'])) {
            $errors['state_id'] = 'State is required';
        }
        
        if (empty($_POST['lga_id']) || !is_numeric($_POST['lga_id'])) {
            $errors['lga_id'] = 'LGA is required';
        }
        
        if (empty($_POST['zone_id']) || !is_numeric($_POST['zone_id'])) {
            $errors['zone_id'] = 'Zone is required';
        }
        
        if (empty($_POST['command_id']) || !is_numeric($_POST['command_id'])) {
            $errors['command_id'] = 'Command is required';
        }
        
        if (empty($_POST['contract_sum']) || !is_numeric($_POST['contract_sum'])) {
            $errors['contract_sum'] = 'Contract sum is required and must be a number';
        }
        
        if (empty($_POST['source_funding'])) {
            $errors['source_funding'] = 'Funding source is required';
        }
        
        if (empty($_POST['status'])) {
            $errors['status'] = 'Project status is required';
        }
        
        // Validate dates if provided
        if (!empty($_POST['date_awarded']) && !empty($_POST['expected_completion_date'])) {
            if (strtotime($_POST['expected_completion_date']) < strtotime($_POST['date_awarded'])) {
                $errors['expected_completion_date'] = 'Completion date must be after award date';
            }
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            Session::set('old', $_POST);
            $this->redirect('projects/create');
            return;
        }
        
        // Generate project code
        $projectCode = $this->generateProjectCode();
        
        Database::beginTransaction();
        
        try {
            // Calculate balance
            $contractSum = floatval($_POST['contract_sum']);
            $depositAmount = floatval($_POST['deposit_amount'] ?? 0);
            $balance = $contractSum - $depositAmount;
            
            // Handle project type other
            $projectType = $_POST['project_type'];
            if ($projectType === 'Other' && !empty($_POST['project_type_other'])) {
                $projectType = $_POST['project_type_other'];
            }
            
            // Handle funding source other
            $fundingSource = $_POST['source_funding'];
            if ($fundingSource === 'Other' && !empty($_POST['funding_other'])) {
                $fundingSource = $_POST['funding_other'];
            }
            
            // Prepare data for insertion
            $data = [
                'project_code' => $projectCode,
                'project_title' => $_POST['project_title'],
                'project_type' => $projectType,
                'state_id' => $_POST['state_id'],
                'lga_id' => $_POST['lga_id'],
                'zone_id' => $_POST['zone_id'],
                'command_id' => $_POST['command_id'],
                'contractor' => $_POST['contractor'] ?? null,
                'contract_sum' => $contractSum,
                'deposit_amount' => $depositAmount,
                'balance' => $balance,
                'date_awarded' => !empty($_POST['date_awarded']) ? $_POST['date_awarded'] : null,
                'expected_completion_date' => !empty($_POST['expected_completion_date']) ? $_POST['expected_completion_date'] : null,
                'physical_progress' => !empty($_POST['physical_progress']) ? floatval($_POST['physical_progress']) : 0,
                'financial_progress' => !empty($_POST['financial_progress']) ? floatval($_POST['financial_progress']) : 0,
                'source_funding' => $fundingSource,
                'supervising_officer' => $_POST['supervising_officer'] ?? null,
                'status' => $_POST['status'],
                'remarks' => $_POST['remarks'] ?? null,
                'created_by' => Auth::id(),
                'created_at' => date('Y-m-d H:i:s')
            ];
            
            $projectId = Database::insert('ongoing_projects', $data);
            
            if (!$projectId) {
                throw new Exception("Failed to insert project record");
            }
            
            // Handle document uploads
            if (isset($_FILES['documents']) && !empty($_FILES['documents']['name'][0])) {
                $this->uploadDocuments($projectId, 'project', $_FILES['documents']);
            }
            
            Database::commit();
            
            AuditLogger::logCreate('ongoing_projects', $projectId, $_POST);
            
            $this->redirect("projects/show/$projectId", ['success' => 'Project created successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Project creation error: " . $e->getMessage());
            Session::set('errors', ['general' => 'Failed to create project: ' . $e->getMessage()]);
            Session::set('old', $_POST);
            $this->redirect('projects/create');
        }
    }
    
    public function show($id) {
        // Check permission
        if (!Auth::can('projects.view')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to view projects']);
            return;
        }
        
        $project = Database::fetchOne(
            "SELECT p.*, s.state_name, l.lga_name, z.zone_name, c.command_name,
                    u.full_name as created_by_name
             FROM ongoing_projects p
             LEFT JOIN states s ON p.state_id = s.id
             LEFT JOIN lgas l ON p.lga_id = l.id
             LEFT JOIN zones z ON p.zone_id = z.id
             LEFT JOIN commands c ON p.command_id = c.id
             LEFT JOIN users u ON p.created_by = u.id
             WHERE p.id = ?",
            [$id]
        );
        
        if (!$project) {
            $this->redirect('projects', ['error' => 'Project not found']);
            return;
        }
        
        // Get documents
        $documents = Database::fetchAll(
            "SELECT * FROM documents WHERE asset_type = 'project' AND asset_id = ? ORDER BY uploaded_at DESC",
            [$id]
        );
        
        if ($documents === false) $documents = [];
        
        AuditLogger::logView('ongoing_projects', $id);
        
        $this->view('projects/show', [
            'project' => $project,
            'documents' => $documents
        ]);
    }
    
    public function edit($id) {
        // Check permission
        if (!Auth::can('projects.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit projects']);
            return;
        }
        
        $project = Database::fetchOne("SELECT * FROM ongoing_projects WHERE id = ?", [$id]);
        
        if (!$project) {
            $this->redirect('projects', ['error' => 'Project not found']);
            return;
        }
        
        $states = Database::fetchAll("SELECT * FROM states ORDER BY state_name");
        $zones = Database::fetchAll("SELECT * FROM zones ORDER BY zone_name");
        $commands = Database::fetchAll("SELECT * FROM commands ORDER BY command_name");
        
        // Get LGAs for the selected state
        $lgas = Database::fetchAll("SELECT * FROM lgas WHERE state_id = ? ORDER BY lga_name", [$project['state_id']]);
        if ($lgas === false) $lgas = [];
        
        $documents = Database::fetchAll(
            "SELECT * FROM documents WHERE asset_type = 'project' AND asset_id = ? ORDER BY uploaded_at DESC",
            [$id]
        );
        if ($documents === false) $documents = [];
        
        $this->view('projects/edit', [
            'project' => $project,
            'states' => $states,
            'lgas' => $lgas,
            'zones' => $zones,
            'commands' => $commands,
            'documents' => $documents
        ]);
    }
    
    public function update($id) {
        // Check permission
        if (!Auth::can('projects.edit')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to edit projects']);
            return;
        }
        
        if (!Security::validateCsrfToken($_POST['csrf_token'] ?? '')) {
            $this->redirect("projects/edit/$id", ['error' => 'Invalid security token']);
            return;
        }
        
        $oldData = Database::fetchOne("SELECT * FROM ongoing_projects WHERE id = ?", [$id]);
        
        if (!$oldData) {
            $this->redirect('projects', ['error' => 'Project not found']);
            return;
        }
        
        // Manual validation
        $errors = [];
        
        if (empty($_POST['project_title'])) {
            $errors['project_title'] = 'Project title is required';
        } elseif (strlen($_POST['project_title']) > 255) {
            $errors['project_title'] = 'Project title must not exceed 255 characters';
        }
        
        if (empty($_POST['project_type'])) {
            $errors['project_type'] = 'Project type is required';
        }
        
        if (empty($_POST['state_id']) || !is_numeric($_POST['state_id'])) {
            $errors['state_id'] = 'State is required';
        }
        
        if (empty($_POST['lga_id']) || !is_numeric($_POST['lga_id'])) {
            $errors['lga_id'] = 'LGA is required';
        }
        
        if (empty($_POST['zone_id']) || !is_numeric($_POST['zone_id'])) {
            $errors['zone_id'] = 'Zone is required';
        }
        
        if (empty($_POST['command_id']) || !is_numeric($_POST['command_id'])) {
            $errors['command_id'] = 'Command is required';
        }
        
        if (empty($_POST['contract_sum']) || !is_numeric($_POST['contract_sum'])) {
            $errors['contract_sum'] = 'Contract sum is required and must be a number';
        }
        
        if (empty($_POST['source_funding'])) {
            $errors['source_funding'] = 'Funding source is required';
        }
        
        if (empty($_POST['status'])) {
            $errors['status'] = 'Project status is required';
        }
        
        // Validate dates if provided
        if (!empty($_POST['date_awarded']) && !empty($_POST['expected_completion_date'])) {
            if (strtotime($_POST['expected_completion_date']) < strtotime($_POST['date_awarded'])) {
                $errors['expected_completion_date'] = 'Completion date must be after award date';
            }
        }
        
        if (!empty($errors)) {
            Session::set('errors', $errors);
            $this->redirect("projects/edit/$id");
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Calculate balance
            $contractSum = floatval($_POST['contract_sum']);
            $depositAmount = floatval($_POST['deposit_amount'] ?? 0);
            $balance = $contractSum - $depositAmount;
            
            // Handle project type other
            $projectType = $_POST['project_type'];
            if ($projectType === 'Other' && !empty($_POST['project_type_other'])) {
                $projectType = $_POST['project_type_other'];
            }
            
            // Handle funding source other
            $fundingSource = $_POST['source_funding'];
            if ($fundingSource === 'Other' && !empty($_POST['funding_other'])) {
                $fundingSource = $_POST['funding_other'];
            }
            
            // Prepare update data
            $data = [
                'project_title' => $_POST['project_title'],
                'project_type' => $projectType,
                'state_id' => $_POST['state_id'],
                'lga_id' => $_POST['lga_id'],
                'zone_id' => $_POST['zone_id'],
                'command_id' => $_POST['command_id'],
                'contractor' => $_POST['contractor'] ?? null,
                'contract_sum' => $contractSum,
                'deposit_amount' => $depositAmount,
                'balance' => $balance,
                'date_awarded' => !empty($_POST['date_awarded']) ? $_POST['date_awarded'] : null,
                'expected_completion_date' => !empty($_POST['expected_completion_date']) ? $_POST['expected_completion_date'] : null,
                'physical_progress' => !empty($_POST['physical_progress']) ? floatval($_POST['physical_progress']) : 0,
                'financial_progress' => !empty($_POST['financial_progress']) ? floatval($_POST['financial_progress']) : 0,
                'source_funding' => $fundingSource,
                'supervising_officer' => $_POST['supervising_officer'] ?? null,
                'status' => $_POST['status'],
                'remarks' => $_POST['remarks'] ?? null,
                'updated_at' => date('Y-m-d H:i:s')
            ];
            
            Database::update('ongoing_projects', $data, 'id = ?', [$id]);
            
            // Handle document deletions
            if (!empty($_POST['remove_docs'])) {
                foreach ($_POST['remove_docs'] as $docId) {
                    $doc = Database::fetchOne("SELECT * FROM documents WHERE id = ?", [$docId]);
                    if ($doc && !empty($doc['file_path']) && file_exists($doc['file_path'])) {
                        unlink($doc['file_path']);
                    }
                    Database::delete('documents', 'id = ?', [$docId]);
                }
            }
            
            // Handle new document uploads
            if (isset($_FILES['documents']) && !empty($_FILES['documents']['name'][0])) {
                $this->uploadDocuments($id, 'project', $_FILES['documents']);
            }
            
            Database::commit();
            
            AuditLogger::logUpdate('ongoing_projects', $id, $oldData, $_POST);
            
            $this->redirect("projects/show/$id", ['success' => 'Project updated successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Project update error: " . $e->getMessage());
            Session::set('errors', ['general' => 'Failed to update project: ' . $e->getMessage()]);
            $this->redirect("projects/edit/$id");
        }
    }
    
    public function delete($id) {
        // Check permission
        if (!Auth::can('projects.delete')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to delete projects']);
            return;
        }
        
        $project = Database::fetchOne("SELECT * FROM ongoing_projects WHERE id = ?", [$id]);
        
        if (!$project) {
            $this->redirect('projects', ['error' => 'Project not found']);
            return;
        }
        
        Database::beginTransaction();
        
        try {
            // Delete associated documents
            $documents = Database::fetchAll(
                "SELECT * FROM documents WHERE asset_type = 'project' AND asset_id = ?",
                [$id]
            );
            
            if ($documents && is_array($documents)) {
                foreach ($documents as $doc) {
                    if (isset($doc['file_path']) && !empty($doc['file_path']) && file_exists($doc['file_path'])) {
                        unlink($doc['file_path']);
                    }
                }
            }
            
            Database::delete('documents', "asset_type = 'project' AND asset_id = ?", [$id]);
            Database::delete('ongoing_projects', 'id = ?', [$id]);
            
            Database::commit();
            
            AuditLogger::logDelete('ongoing_projects', $id, $project);
            
            $this->redirect('projects', ['success' => 'Project deleted successfully']);
            
        } catch (Exception $e) {
            Database::rollBack();
            error_log("Project deletion error: " . $e->getMessage());
            $this->redirect('projects', ['error' => 'Failed to delete project: ' . $e->getMessage()]);
        }
    }
    
    public function export() {
        // Check permission
        if (!Auth::can('reports.export')) {
            $this->redirect('dashboard', ['error' => 'You do not have permission to export data']);
            return;
        }
        
        $projects = Database::fetchAll(
            "SELECT p.*, s.state_name, l.lga_name, z.zone_name, c.command_name 
             FROM ongoing_projects p
             LEFT JOIN states s ON p.state_id = s.id
             LEFT JOIN lgas l ON p.lga_id = l.id
             LEFT JOIN zones z ON p.zone_id = z.id
             LEFT JOIN commands c ON p.command_id = c.id
             ORDER BY p.created_at DESC"
        );
        
        if ($projects === false) $projects = [];
        
        $filename = 'projects_' . date('Y-m-d') . '.csv';
        
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        
        $output = fopen('php://output', 'w');
        
        // Headers
        fputcsv($output, [
            'Project Code', 'Project Title', 'Project Type', 'State', 'LGA', 'Zone', 'Command',
            'Contractor', 'Contract Sum', 'Deposit Amount', 'Balance', 'Date Awarded', 'Expected Completion',
            'Physical Progress %', 'Financial Progress %', 'Funding Source',
            'Supervising Officer', 'Status', 'Remarks', 'Created At'
        ]);
        
        // Data
        foreach ($projects as $project) {
            fputcsv($output, [
                $project['project_code'] ?? '',
                $project['project_title'] ?? '',
                $project['project_type'] ?? '',
                $project['state_name'] ?? '',
                $project['lga_name'] ?? '',
                $project['zone_name'] ?? '',
                $project['command_name'] ?? '',
                $project['contractor'] ?? '',
                $project['contract_sum'] ?? '',
                $project['deposit_amount'] ?? '0',
                $project['balance'] ?? '0',
                $project['date_awarded'] ?? '',
                $project['expected_completion_date'] ?? '',
                $project['physical_progress'] ?? '',
                $project['financial_progress'] ?? '',
                $project['source_funding'] ?? '',
                $project['supervising_officer'] ?? '',
                $project['status'] ?? '',
                $project['remarks'] ?? '',
                $project['created_at'] ?? ''
            ]);
        }
        
        fclose($output);
        
        AuditLogger::logExport('projects', 'csv');
        exit;
    }
    
    private function generateProjectCode() {
        $year = date('Y');
        $month = date('m');
        
        $last = Database::fetchOne(
            "SELECT project_code FROM ongoing_projects 
             WHERE project_code LIKE 'PRJ-{$year}{$month}%' 
             ORDER BY id DESC LIMIT 1"
        );
        
        if ($last && isset($last['project_code'])) {
            $parts = explode('-', $last['project_code']);
            $seq = intval(end($parts)) + 1;
        } else {
            $seq = 1;
        }
        
        return sprintf("PRJ-%s%s-%04d", $year, $month, $seq);
    }
    
    private function uploadDocuments($assetId, $type, $files) {
        $uploadDir = Config::get('upload_path') . $type . '_documents/';
        
        if (!is_dir($uploadDir)) {
            mkdir($uploadDir, 0755, true);
        }
        
        $allowedTypes = Config::get('allowed_file_types', ['pdf', 'jpg', 'jpeg', 'png', 'doc', 'docx']);
        $maxSize = Config::get('max_upload_size', 5242880); // 5MB
        
        // Check if files were uploaded
        if (empty($files) || empty($files['name'][0])) {
            return;
        }
        
        // Get document types from POST
        $documentTypes = isset($_POST['document_types']) ? $_POST['document_types'] : [];
        
        // Loop through each file
        $uploadedCount = 0;
        for ($i = 0; $i < count($files['name']); $i++) {
            // Skip if no file was uploaded for this index
            if ($files['error'][$i] !== UPLOAD_ERR_OK) {
                continue;
            }
            
            // Skip if file name is empty
            if (empty($files['name'][$i])) {
                continue;
            }
            
            $fileName = $files['name'][$i];
            $fileTmp = $files['tmp_name'][$i];
            $fileSize = $files['size'][$i];
            $fileExt = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));
            
            // Validate file extension
            if (!in_array($fileExt, $allowedTypes)) {
                error_log("File type not allowed: $fileName. Allowed: " . implode(', ', $allowedTypes));
                continue;
            }
            
            // Validate file size
            if ($fileSize > $maxSize) {
                error_log("File too large: $fileName. Max size: " . ($maxSize / 1024 / 1024) . "MB");
                continue;
            }
            
            // Generate unique filename
            $newFileName = uniqid() . '_' . preg_replace("/[^a-zA-Z0-9\.]/", "_", $fileName);
            $destination = $uploadDir . $newFileName;
            
            // Move uploaded file
            if (move_uploaded_file($fileTmp, $destination)) {
                // Store relative path in database
                $relativePath = str_replace($_SERVER['DOCUMENT_ROOT'], '', $destination);
                
                // Get document type from POST if available, default to 'other'
                $documentType = isset($documentTypes[$i]) && !empty($documentTypes[$i]) ? $documentTypes[$i] : 'other';
                
                // Insert into database
                $insertData = [
                    'asset_type' => $type,
                    'asset_id' => $assetId,
                    'file_name' => $fileName,
                    'file_path' => $relativePath,
                    'file_size' => $fileSize,
                    'file_mime' => $files['type'][$i],
                    'document_type' => $documentType,
                    'uploaded_by' => Auth::id(),
                    'uploaded_at' => date('Y-m-d H:i:s')
                ];
                
                $result = Database::insert('documents', $insertData);
                
                if ($result) {
                    $uploadedCount++;
                } else {
                    error_log("Failed to insert document record for: $fileName");
                }
            } else {
                error_log("Failed to move uploaded file: $fileName");
            }
        }
        
        if ($uploadedCount > 0) {
            error_log("Successfully uploaded $uploadedCount documents for $type ID: $assetId");
        }
    }
}