<?php

namespace App\Controllers;

use App\Models\ProjectModel;
use App\Models\ServiceModel;
use App\Models\EnquiryModel;
use App\Models\UserModel;
use App\Models\SettingsModel;
use App\Models\CategoryModel;
use App\Models\GalleryModel;
use App\Models\BannerModel;
use App\Models\ExpenseModel;
use App\Models\ProjectAssignmentModel;
use App\Models\ProjectSheetModel;
use App\Models\AttendanceModel;


class Admin extends BaseController
{
    public function index()
    {
        $projectModel = new ProjectModel();
        $serviceModel = new ServiceModel();
        $attendanceModel = new AttendanceModel();

        // Dynamic Schema Check & Seeding
        $db = \Config\Database::connect();
        if (!$db->fieldExists('type', 'services')) {
            $db->query("ALTER TABLE services ADD COLUMN type VARCHAR(50) DEFAULT 'General' AFTER title");
        }

        // Synchronize and Categorize existing services
        $serviceModel->where('title', 'General Contracting')->set(['type' => 'Expertise'])->update();
        $serviceModel->where('title', 'Design-Build')->set(['type' => 'Expertise'])->update();
        $serviceModel->where('title', 'Renovation & Remodeling')->set(['type' => 'Expertise'])->update();
        $serviceModel->where('title', 'Infrastructure Development')->set(['type' => 'Infrastructure'])->update();
        $serviceModel->where('title', 'Government Projects')->set(['type' => 'Infrastructure'])->update();

        // Add missing specialized services if they don't exist
        $requiredServices = [
            ['title' => 'Railway Bridges', 'type' => 'Infrastructure', 'image' => '', 'description' => 'Precision construction of major and minor bridges for national rail networks.'],
            ['title' => 'Track & Mast Works', 'type' => 'Infrastructure', 'image' => '', 'description' => 'End-to-end track laying and OHE mast installation for electrification.'],
            ['title' => 'Gated Communities', 'type' => 'Expertise', 'image' => '', 'description' => 'Premium residential villas and luxury community developments.'],
        ];

        foreach ($requiredServices as $req) {
            if (!$serviceModel->where('title', $req['title'])->first()) {
                $serviceModel->insert($req);
            }
        }

        // About Section Dynamic Content
        $settingsModel = new SettingsModel();
        $aboutSettings = [
            'home_about_title' => 'Shaping Modern Landscapes Since 2017',
            'home_about_description' => 'Saanvi Infratech was established in 2017 with a vision to deliver world-class engineering solutions. Over the years, we have evolved into a trusted partner for both government and private sector projects.<br><br>We take immense pride in our contribution to national infrastructure, working closely with prestigious organizations like <strong>Rail Vikas Nigam Limited (RVNL)</strong> and other government bodies to deliver large-scale railway and civil projects.',
            'home_about_image' => base_url('assets/images/home/about_construction.png'),
            'home_about_feature_title' => 'Commitment to Excellence',
            'home_about_feature_desc' => 'Delivering precision engineering with unwavering quality standards.'
        ];

        foreach ($aboutSettings as $key => $val) {
            if (!$settingsModel->getSetting($key)) {
                $settingsModel->updateSetting($key, $val);
            }
        }

        $data = [
            'title' => 'Admin Dashboard',
            'project_count' => $projectModel->countAll(),
            'service_count' => $serviceModel->countAll(),
            'attendance_count' => $attendanceModel->where('approval_status', 'Pending')->countAllResults(),
            'projects' => $projectModel->orderBy('id', 'DESC')->limit(10)->find(),
            'services' => $serviceModel->findAll(),
            'settings' => $settingsModel->findAll()
        ];

        return view('admin/dashboard', $data);
    }

    public function project_sheet($id, $sheet_id = null)
    {
        $projectModel = new ProjectModel();
        $sheetModel = new ProjectSheetModel();
        $userModel = new UserModel();
        $assignModel = new ProjectAssignmentModel();

        // Ensure table_name and folder_name columns exist
        $db = \Config\Database::connect();
        if (!$db->fieldExists('table_name', 'project_sheets')) {
            $db->query("ALTER TABLE project_sheets ADD COLUMN table_name VARCHAR(255) DEFAULT 'All' AFTER project_id");
        }
        if (!$db->fieldExists('folder_name', 'project_sheets')) {
            $db->query("ALTER TABLE project_sheets ADD COLUMN folder_name VARCHAR(255) DEFAULT NULL AFTER table_name");
        }

        // Get all sheets for this project for navigation
        $all_sheets = $sheetModel->where('project_id', $id)->findAll();

        if ($sheet_id) {
            $sheet = $sheetModel->find($sheet_id);
        } else {
            $sheet = $sheetModel->where('project_id', $id)->first();
        }
        
        // Default config if none exists
        if (!$sheet) {
            $config = [
                ['name' => 'Category', 'type' => 'text'],
                ['name' => 'Description', 'type' => 'text'],
                ['name' => 'Amount', 'type' => 'number'],
                ['name' => 'Date', 'type' => 'text']
            ];
            $sheet_data = [];
            $table_name = 'All';
        } else {
            $config = json_decode($sheet['sheet_config'], true);
            $sheet_data = json_decode($sheet['sheet_data'], true);
            $table_name = $sheet['table_name'] ?? 'All';
        }

        // Get only employees assigned to this project
        $assignments = $assignModel->where('project_id', $id)->findAll();
        $assignedIds = array_column($assignments, 'user_id');
        
        $employees = [];
        if (!empty($assignedIds)) {
            $employees = $userModel->whereIn('id', $assignedIds)->findAll();
        }

        $data = [
            'title' => 'Project Spreadsheet',
            'project' => $projectModel->find($id),
            'config' => $config,
            'sheet_data' => $sheet_data,
            'employees' => $employees,
            'all_sheets' => $all_sheets,
            'current_sheet' => $sheet,
            'table_name' => $table_name
        ];

        return view('admin/project_sheet', $data);
    }

    public function save_sheet($id, $sheet_id = null)
    {
        $sheetModel = new ProjectSheetModel();
        
        $config = $this->request->getPost('col_name');
        $types = $this->request->getPost('col_type');
        $options = $this->request->getPost('col_options');
        $table_name = $this->request->getPost('table_name') ?? 'All';

        // Prefer JSON data if available for better reliability
        $json_data = $this->request->getPost('json_data');
        if (!empty($json_data)) {
            $row_data = json_decode($json_data, true);
        } else {
            $row_data = $this->request->getPost('cell') ?? []; 
        }

        $formatted_config = [];
        if ($config) {
            foreach ($config as $idx => $name) {
                $formatted_config[] = [
                    'name' => $name,
                    'type' => $types[$idx],
                    'options' => isset($options[$idx]) ? json_decode($options[$idx], true) : []
                ];
            }
        }

        // Handle Image Uploads in row_data
        $files = $this->request->getFiles();
        if (isset($files['cell_files'])) {
            foreach ($files['cell_files'] as $row_idx => $cols) {
                foreach ($cols as $col_idx => $file) {
                    if ($file->isValid() && !$file->hasMoved()) {
                        $newName = $file->getRandomName();
                        $file->move(FCPATH . 'uploads/sheets', $newName);
                        $row_data[$row_idx][$col_idx] = base_url('uploads/sheets/' . $newName);
                    }
                }
            }
        }

        $data = [
            'project_id' => $id,
            'table_name' => $table_name,
            'folder_name' => $this->request->getPost('folder_name') ?: 'Main',
            'sheet_config' => json_encode($formatted_config),
            'sheet_data' => json_encode(array_values($row_data)) // array_values to ensure sequential array
        ];

        if ($sheet_id) {
            $sheetModel->update($sheet_id, $data);
            $redirect_url = '/admin/project_sheet/' . $id . '/' . $sheet_id;
            $final_id = $sheet_id;
        } else {
            $final_id = $sheetModel->insert($data);
            $redirect_url = '/admin/project_sheet/' . $id . '/' . $final_id;
        }

        if ($this->request->isAJAX()) {
            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Saved successfully!',
                'sheet_id' => $final_id
            ]);
        }

        return redirect()->to($redirect_url)->with('success', 'Sheet saved successfully!');
    }

    public function create_table($id)
    {
        $sheetModel = new ProjectSheetModel();
        $name = $this->request->getPost('table_name') ?? 'New Table';
        
        $config = [
            ['name' => 'S.No.', 'type' => 'number'],
            ['name' => 'Name', 'type' => 'text']
        ];

        $data = [
            'project_id' => $id,
            'table_name' => $name,
            'folder_name' => $this->request->getPost('folder_name'),
            'sheet_config' => json_encode($config),
            'sheet_data' => json_encode([])
        ];

        $sheet_id = $sheetModel->insert($data);
        return redirect()->to('/admin/project_sheet/' . $id . '/' . $sheet_id);
    }

    public function duplicate_table($project_id, $sheet_id)
    {
        $sheetModel = new ProjectSheetModel();
        $sheet = $sheetModel->find($sheet_id);
        if ($sheet) {
            $data = [
                'project_id' => $project_id,
                'table_name' => $sheet['table_name'] . ' (Copy)',
                'folder_name' => $sheet['folder_name'],
                'sheet_config' => $sheet['sheet_config'],
                'sheet_data' => $sheet['sheet_data']
            ];
            $new_id = $sheetModel->insert($data);
            return redirect()->to('/admin/project_sheet/' . $project_id . '/' . $new_id)->with('success', 'Table duplicated!');
        }
        return redirect()->to('/admin/project_sheet/' . $project_id)->with('error', 'Table not found.');
    }

    public function rename_table($project_id)
    {
        $sheetModel = new ProjectSheetModel();
        $sheet_id = $this->request->getPost('table_id');
        $name = $this->request->getPost('table_name');
        if ($sheet_id && $name) {
            $sheetModel->update($sheet_id, ['table_name' => $name]);
            return redirect()->to('/admin/project_sheet/' . $project_id . '/' . $sheet_id)->with('success', 'Table renamed!');
        }
        return redirect()->to('/admin/project_sheet/' . $project_id)->with('error', 'Invalid data.');
    }

    public function move_table($project_id)
    {
        $sheetModel = new ProjectSheetModel();
        $sheet_id = $this->request->getPost('table_id');
        $folder = $this->request->getPost('folder_name');
        
        if ($folder === '__new__') {
            $folder = $this->request->getPost('new_folder_name');
        }

        if ($sheet_id) {
            $sheetModel->update($sheet_id, ['folder_name' => $folder]);
            return redirect()->to('/admin/project_sheet/' . $project_id . '/' . $sheet_id)->with('success', 'Table moved!');
        }
        return redirect()->to('/admin/project_sheet/' . $project_id)->with('error', 'Invalid data.');
    }

    public function delete_folder($project_id)
    {
        $sheetModel = new ProjectSheetModel();
        $folderName = $this->request->getPost('folder_name');
        
        if ($folderName) {
            $sheetModel->where('project_id', $project_id)
                       ->where('folder_name', $folderName)
                       ->delete();
            return redirect()->to('/admin/project_sheet/' . $project_id)->with('success', "Folder '$folderName' deleted successfully!");
        }
        
        return redirect()->to('/admin/project_sheet/' . $project_id)->with('error', "Folder name missing.");
    }

    public function project_assign($id)
    {
        $projectModel = new ProjectModel();
        $userModel = new UserModel();
        $assignModel = new ProjectAssignmentModel();

        $assigned = $assignModel->where('project_id', $id)->findAll();
        $assigned_ids = array_column($assigned, 'user_id');

        $data = [
            'title' => 'Assign Team',
            'project' => $projectModel->find($id),
            'employees' => $userModel->getEmployees(),
            'assigned_ids' => $assigned_ids
        ];

        return view('admin/project_assign', $data);
    }

    public function project_assign_save($id)
    {
        $assignModel = new ProjectAssignmentModel();
        
        // Clear existing assignments
        $assignModel->where('project_id', $id)->delete();

        $employee_ids = $this->request->getPost('employee_ids');
        if ($employee_ids) {
            foreach ($employee_ids as $emp_id) {
                $assignModel->save([
                    'project_id' => $id,
                    'user_id' => $emp_id
                ]);
            }
        }

        return redirect()->to('/admin/projects')->with('success', 'Project team updated successfully!');
    }

    public function project_expenses($id)
    {
        $projectModel = new ProjectModel();
        $expenseModel = new ExpenseModel();
        $userModel = new UserModel();
        $assignModel = new ProjectAssignmentModel();

        // Get only employees assigned to this project
        $assignments = $assignModel->where('project_id', $id)->findAll();
        $assignedIds = array_column($assignments, 'user_id');
        
        $employees = [];
        if (!empty($assignedIds)) {
            $employees = $userModel->whereIn('id', $assignedIds)->findAll();
        }

        $data = [
            'title' => 'Project Expenses',
            'project' => $projectModel->find($id),
            'expenses' => $expenseModel->where('project_id', $id)->findAll(),
            'employees' => $employees
        ];

        return view('admin/project_expenses', $data);
    }

    public function save_expenses($id)
    {
        $expenseModel = new ExpenseModel();
        
        // Remove existing expenses for this project to overwrite
        $expenseModel->where('project_id', $id)->delete();

        $categories = $this->request->getPost('category');
        $employee_ids = $this->request->getPost('employee_id');
        $descriptions = $this->request->getPost('description');
        $amounts = $this->request->getPost('amount');
        $dates = $this->request->getPost('date');

        if ($categories) {
            foreach ($categories as $index => $cat) {
                if (!empty($amounts[$index])) {
                    $expenseModel->save([
                        'project_id' => $id,
                        'employee_id' => $employee_ids[$index],
                        'category' => $cat,
                        'description' => $descriptions[$index],
                        'amount' => $amounts[$index],
                        'date' => $dates[$index]
                    ]);
                }
            }
        }

        return redirect()->to('/admin/project_expenses/' . $id);
    }

    public function projects()
    {
        $projectModel = new ProjectModel();
        $assignModel = new ProjectAssignmentModel();
        $userModel = new UserModel();
        
        $projects = $projectModel->findAll();
        
        foreach ($projects as &$project) {
            $assignments = $assignModel->where('project_id', $project['id'])->findAll();
            $employeeNames = [];
            foreach ($assignments as $assign) {
                $user = $userModel->find($assign['user_id']);
                if ($user) {
                    $employeeNames[] = $user['name'];
                }
            }
            $project['assigned_employees'] = $employeeNames;
        }

        $data = [
            'title' => 'Manage Projects',
            'projects' => $projects
        ];
        return view('admin/projects', $data);
    }

    public function project_add()
    {
        $catModel = new CategoryModel();
        $data = [
            'title' => 'Add Project',
            'categories' => $catModel->findAll()
        ];
        return view('admin/project_form', $data);
    }

    public function project_save()
    {
        $projectModel = new ProjectModel();
        $data = $this->request->getPost();
        
        $id = $this->request->getPost('id');

        $img = $this->request->getFile('image');
        if ($img && $img->isValid() && !$img->hasMoved()) {
            $newName = $img->getRandomName();
            $img->move(FCPATH . 'uploads/projects', $newName);
            $data['image'] = base_url('uploads/projects/' . $newName);
        }

        if ($id) {
            $projectModel->update($id, $data);
            $msg = 'Project updated successfully!';
        } else {
            $projectModel->save($data);
            $msg = 'Project created successfully!';
        }
        
        return redirect()->to('/admin/projects')->with('success', $msg);
    }

    public function project_edit($id)
    {
        $projectModel = new ProjectModel();
        $catModel = new CategoryModel();
        $project = $projectModel->find($id);

        if (!$project) {
            return redirect()->to('/admin/projects')->with('error', 'Project not found');
        }

        $data = [
            'title' => 'Edit Project',
            'categories' => $catModel->findAll(),
            'project' => $project
        ];
        return view('admin/project_form', $data);
    }

    public function project_delete($id)
    {
        $projectModel = new ProjectModel();
        $projectModel->delete($id);
        return redirect()->to('/admin/projects')->with('success', 'Project deleted successfully!');
    }

    // Employee Management
    public function employees()
    {
        $userModel = new UserModel();
        $data = [
            'title' => 'Manage Employees',
            'employees' => $userModel->getEmployees()
        ];
        return view('admin/employees', $data);
    }

    public function employee_add()
    {
        return view('admin/employee_form', ['title' => 'Register Employee']);
    }

    public function employee_save()
    {
        $userModel = new UserModel();
        $data = $this->request->getPost();
        
        $img = $this->request->getFile('profile_pic');
        if ($img && $img->isValid() && !$img->hasMoved()) {
            $newName = $img->getRandomName();
            $img->move(FCPATH . 'uploads/employees', $newName);
            $data['profile_pic'] = 'uploads/employees/' . $newName;
        }

        $data['password'] = password_hash($data['password'], PASSWORD_DEFAULT);
        $data['role'] = 'employee';
        
        $userModel->save($data);
        return redirect()->to('/admin/employees');
    }

    public function employee_edit($id)
    {
        $userModel = new UserModel();
        $data = [
            'title' => 'Edit Employee',
            'employee' => $userModel->find($id)
        ];
        return view('admin/employee_form', $data);
    }

    public function employee_delete($id)
    {
        $userModel = new UserModel();
        $userModel->delete($id);
        return redirect()->to('/admin/employees')->with('success', 'Employee deleted successfully!');
    }

    public function employee_import()
    {
        $file = $this->request->getFile('csv_file');
        if ($file && $file->isValid() && !$file->hasMoved()) {
            $userModel = new UserModel();
            $handle = fopen($file->getTempName(), "r");
            $header = fgetcsv($handle); // Skip header row
            
            $count = 0;
            while (($data = fgetcsv($handle)) !== FALSE) {
                // Expected CSV Format: Name, Email, Phone, Designation, Department, Salary, Joining Date
                if (empty($data[0]) || empty($data[1])) continue;

                $userData = [
                    'name'         => $data[0] ?? '',
                    'email'        => $data[1] ?? '',
                    'phone'        => $data[2] ?? '',
                    'designation'  => $data[3] ?? '',
                    'department'   => $data[4] ?? '',
                    'salary'       => $data[5] ?? 0,
                    'joining_date' => !empty($data[6]) ? date('Y-m-d', strtotime($data[6])) : date('Y-m-d'),
                    'role'         => 'employee',
                    'password'     => password_hash('Saanvi@123', PASSWORD_DEFAULT) // Default password
                ];
                
                // Check if user already exists
                $existing = $userModel->where('email', $userData['email'])->first();
                if (!$existing) {
                    $userModel->insert($userData);
                    $count++;
                }
            }
            fclose($handle);
            return redirect()->to('/admin/employees')->with('success', "$count new employees imported successfully!");
        }
        return redirect()->to('/admin/employees')->with('error', 'Please upload a valid CSV file.');
    }

    public function employee_sample_csv()
    {
        $filename = 'employee_import_sample.csv';
        $header = ['Name', 'Email', 'Phone', 'Designation', 'Department', 'Salary', 'Joining Date'];
        $sample_data = [
            ['John Doe', 'john@example.com', '9876543210', 'Site Engineer', 'Civil', '45000', '2024-01-15'],
            ['Jane Smith', 'jane@example.com', '9876543211', 'Project Manager', 'Electrical', '65000', '2023-11-20']
        ];

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=' . $filename);
        $output = fopen('php://output', 'w');
        fputcsv($output, $header);
        foreach ($sample_data as $row) {
            fputcsv($output, $row);
        }
        fclose($output);
        exit;
    }

    // Service Management
    public function services()
    {
        $serviceModel = new ServiceModel();
        $data = [
            'title' => 'Manage Services',
            'services' => $serviceModel->findAll()
        ];
        return view('admin/services', $data);
    }

    public function service_add()
    {
        return view('admin/service_form', ['title' => 'Add New Service']);
    }

    public function service_save()
    {
        $serviceModel = new ServiceModel();
        $data = $this->request->getPost();
        
        $img = $this->request->getFile('image');
        if ($img->isValid() && !$img->hasMoved()) {
            $newName = $img->getRandomName();
            $img->move(FCPATH . 'uploads/services', $newName);
            $data['image'] = base_url('uploads/services/' . $newName);
        }

        $serviceModel->save($data);
        return redirect()->to('/admin/services');
    }

    // Settings
    public function settings()
    {
        $settingsModel = new SettingsModel();
        $data = [
            'title' => 'System Settings',
            'settings' => [
                'site_name' => $settingsModel->getSetting('site_name'),
                'site_logo' => $settingsModel->getSetting('site_logo'),
                'contact_email' => $settingsModel->getSetting('contact_email'),
                'contact_phone' => $settingsModel->getSetting('contact_phone'),
                'about_title' => $settingsModel->getSetting('about_title'),
                'about_description' => $settingsModel->getSetting('about_description'),
                'about_content' => $settingsModel->getSetting('about_content'),
                'about_image' => $settingsModel->getSetting('about_image'),
                'mission_content' => $settingsModel->getSetting('mission_content'),
                'vision_content' => $settingsModel->getSetting('vision_content'),
                'values_content' => $settingsModel->getSetting('values_content'),
                'projects_completed' => $settingsModel->getSetting('projects_completed'),

                'years_experience' => $settingsModel->getSetting('years_experience'),
                'happy_clients' => $settingsModel->getSetting('happy_clients'),
                'active_projects' => $settingsModel->getSetting('active_projects'),
                'site_address' => $settingsModel->getSetting('site_address'),
                'facebook_url' => $settingsModel->getSetting('facebook_url'),
                'twitter_url' => $settingsModel->getSetting('twitter_url'),
                'linkedin_url' => $settingsModel->getSetting('linkedin_url'),
                'instagram_url' => $settingsModel->getSetting('instagram_url'),
            ]


        ];
        return view('admin/settings', $data);
    }

    public function settings_save()
    {
        $settingsModel = new SettingsModel();
        $posts = $this->request->getPost();
        
        foreach ($posts as $key => $value) {
            $settingsModel->updateSetting($key, $value);
        }

        // Handle Logo Upload
        $logo = $this->request->getFile('site_logo');
        if ($logo && $logo->isValid() && !$logo->hasMoved()) {
            $newName = 'logo_' . time() . '.' . $logo->getExtension();
            $logo->move(FCPATH . 'uploads', $newName);
            $settingsModel->updateSetting('site_logo', base_url('uploads/' . $newName));
        }

        // Handle About Image Upload
        $aboutImg = $this->request->getFile('about_image');
        if ($aboutImg && $aboutImg->isValid() && !$aboutImg->hasMoved()) {
            $newName = 'about_' . time() . '.' . $aboutImg->getExtension();
            $aboutImg->move(FCPATH . 'uploads', $newName);
            $settingsModel->updateSetting('about_image', base_url('uploads/' . $newName));
        }

        return redirect()->to('/admin/settings');
    }



    public function categories()
    {
        $catModel = new CategoryModel();
        $data = [
            'title' => 'Manage Categories',
            'categories' => $catModel->findAll()
        ];
        return view('admin/categories', $data);
    }

    public function category_save()
    {
        $catModel = new CategoryModel();
        $data = $this->request->getPost();
        
        $img = $this->request->getFile('image');
        if ($img && $img->isValid() && !$img->hasMoved()) {
            $newName = $img->getRandomName();
            $img->move(FCPATH . 'uploads/categories', $newName);
            $data['image'] = base_url('uploads/categories/' . $newName);
        }

        $catModel->save($data);
        return redirect()->to('/admin/categories');
    }

    // Gallery Management
    public function gallery()
    {
        $galleryModel = new GalleryModel();
        $data = [
            'title' => 'Manage Gallery',
            'gallery' => $galleryModel->findAll()
        ];
        return view('admin/gallery', $data);
    }

    public function gallery_add()
    {
        return view('admin/gallery_form', ['title' => 'Add to Gallery']);
    }

    public function gallery_save()
    {
        $galleryModel = new GalleryModel();
        $data = $this->request->getPost();
        
        $type = $data['type'];

        if ($type == 'image') {
            $file = $this->request->getFile('file');
            if ($file && $file->isValid() && !$file->hasMoved()) {
                $newName = $file->getRandomName();
                $file->move(FCPATH . 'uploads/gallery', $newName);
                $data['file_path'] = base_url('uploads/gallery/' . $newName);
            }
        } else {
            // Handle video
            $videoFile = $this->request->getFile('video_file');
            if ($videoFile && $videoFile->isValid() && !$videoFile->hasMoved()) {
                $newName = $videoFile->getRandomName();
                $videoFile->move(FCPATH . 'uploads/gallery', $newName);
                $data['file_path'] = base_url('uploads/gallery/' . $newName);
            }
            // video_url is already in $data from post
        }

        $galleryModel->save($data);
        return redirect()->to('/admin/gallery');
    }

    public function gallery_delete($id)
    {
        $galleryModel = new GalleryModel();
        $galleryModel->delete($id);
        return redirect()->to('/admin/gallery');
    }

    // Banner Management
    public function banners()
    {
        $bannerModel = new BannerModel();
        $data = [
            'title' => 'Manage Header Banners',
            'banners' => $bannerModel->orderBy('order_index', 'ASC')->findAll()
        ];
        return view('admin/banners', $data);
    }

    public function banner_add()
    {
        return view('admin/banner_form', ['title' => 'Add New Banner']);
    }

    public function banner_save()
    {
        $bannerModel = new BannerModel();
        $data = $this->request->getPost();
        
        $img = $this->request->getFile('image');
        if ($img && $img->isValid() && !$img->hasMoved()) {
            $newName = $img->getRandomName();
            $img->move(FCPATH . 'uploads/banners', $newName);
            $data['image_url'] = base_url('uploads/banners/' . $newName);
        }

        $bannerModel->save($data);
        return redirect()->to('/admin/banners');
    }

    public function banner_delete($id)
    {
        $bannerModel = new BannerModel();
        $bannerModel->delete($id);
        return redirect()->to('/admin/banners');
    }

    public function reports()
    {
        $data = [
            'title' => 'Data Exports (Excel)',
        ];
        return view('admin/reports', $data);
    }

    public function export($type)
    {
        switch ($type) {
            case 'enquiries':
                $model = new EnquiryModel();
                $filename = 'enquiries_' . date('Ymd') . '.csv';
                $header = ['ID', 'Name', 'Email', 'Phone', 'Subject', 'Message', 'Date'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['name'], $item['email'], $item['phone'], $item['subject'], $item['message'], $item['created_at']];
                }
                break;
            case 'projects':
                $model = new ProjectModel();
                $filename = 'projects_' . date('Ymd') . '.csv';
                $header = ['ID', 'Title', 'Status', 'Budget', 'Start Date'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['title'], $item['status'], $item['budget'], $item['start_date']];
                }
                break;
            case 'services':
                $model = new ServiceModel();
                $filename = 'services_' . date('Ymd') . '.csv';
                $header = ['ID', 'Title', 'Description'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['title'], $item['description']];
                }
                break;
            case 'categories':
                $model = new CategoryModel();
                $filename = 'categories_' . date('Ymd') . '.csv';
                $header = ['ID', 'Name', 'Created At'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['name'], $item['created_at']];
                }
                break;
            case 'employees':
                $model = new \App\Models\EmployeeModel();
                $filename = 'employees_' . date('Ymd') . '.csv';
                $header = ['ID', 'Name', 'Email', 'Role', 'Joining Date'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['name'], $item['email'], $item['role'], $item['created_at']];
                }
                break;
            case 'registrations':
                $model = new EnquiryModel();
                $filename = 'registrations_' . date('Ymd') . '.csv';
                $header = ['ID', 'Name', 'Email', 'Project Type', 'Message', 'Status', 'Date'];
                $data = $model->findAll();
                $rows = [];
                foreach ($data as $item) {
                    $rows[] = [$item['id'], $item['name'], $item['email'], $item['project_type'], $item['message'], $item['status'], $item['created_at']];
                }
                break;
            default:
                return redirect()->back();
        }

        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename=' . $filename);
        $output = fopen('php://output', 'w');
        fputcsv($output, $header);
        foreach ($rows as $row) {
            fputcsv($output, $row);
        }
        fclose($output);
        exit;
    }
    
    public function registers()
    {
        $projectModel = new ProjectModel();
        
        // Fetch projects, ensuring one card per project even with multiple tables
        $projects = $projectModel->select('projects.id, projects.title as project_name, MAX(project_sheets.updated_at) as updated_at')
                                ->join('project_sheets', 'project_sheets.project_id = projects.id', 'left')
                                ->groupBy('projects.id')
                                ->findAll();
        
        $data = [
            'title' => 'Registers',
            'sheets' => $projects
        ];

        return view('admin/registers', $data);
    }

    public function register_new()
    {
        $data = [
            'title' => 'New Registers'
        ];
        return view('admin/register_new', $data);
    }

    public function delete_table($project_id, $sheet_id)
    {
        $sheetModel = new ProjectSheetModel();
        $sheetModel->delete($sheet_id);
        return redirect()->to('/admin/project_sheet/' . $project_id)->with('success', 'Table deleted successfully!');
    }

    public function attendance()
    {
        $attendanceModel = new AttendanceModel();
        $data = [
            'title' => 'Employee Attendance',
            'pending_attendance' => $attendanceModel->getPendingAttendance(),
            'all_attendance' => $attendanceModel->getAllAttendance()
        ];
        return view('admin/attendance', $data);
    }

    public function attendance_approve($id)
    {
        $attendanceModel = new AttendanceModel();
        $attendanceModel->update($id, ['approval_status' => 'Approved']);
        return redirect()->to('/admin/attendance')->with('success', 'Attendance approved successfully!');
    }

    public function attendance_reject($id)
    {
        $attendanceModel = new AttendanceModel();
        $attendanceModel->update($id, ['approval_status' => 'Rejected']);
        return redirect()->to('/admin/attendance')->with('success', 'Attendance rejected successfully!');
    }

    // Proposal Management
    public function proposals()
    {
        $proposalModel = new \App\Models\ProposalModel();
        $data = [
            'title' => 'Proposals',
            'proposals' => $proposalModel->orderBy('created_at', 'DESC')->findAll()
        ];
        return view('admin/proposals', $data);
    }

    public function proposal_add()
    {
        $data = [
            'title' => 'Add Proposal'
        ];
        return view('admin/proposal_form', $data);
    }

    public function proposal_save()
    {
        $proposalModel = new \App\Models\ProposalModel();
        
        // Ensure table and columns exist
        $db = \Config\Database::connect();
        if (!$db->tableExists('proposals')) {
            $db->query("CREATE TABLE proposals (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                created_at DATETIME,
                updated_at DATETIME
            )");
        } else {
            if (!$db->fieldExists('name', 'proposals')) {
                $db->query("ALTER TABLE proposals ADD COLUMN name VARCHAR(255) AFTER id");
            }
            if (!$db->fieldExists('description', 'proposals')) {
                $db->query("ALTER TABLE proposals ADD COLUMN description TEXT AFTER name");
            }
            if (!$db->fieldExists('created_at', 'proposals')) {
                $db->query("ALTER TABLE proposals ADD COLUMN created_at DATETIME");
            }
            if (!$db->fieldExists('updated_at', 'proposals')) {
                $db->query("ALTER TABLE proposals ADD COLUMN updated_at DATETIME");
            }
        }

        $data = $this->request->getPost();
        
        $proposalModel->save($data);
        return redirect()->to('/admin/proposals')->with('success', 'Proposal saved successfully!');
    }

    public function proposal_delete($id)
    {
        $proposalModel = new \App\Models\ProposalModel();
        $proposalModel->delete($id);
        return redirect()->to('/admin/proposals')->with('success', 'Proposal deleted successfully!');
    }
}


