<?php

namespace App\Controllers;

use App\Models\TipModel;
use App\Models\PredictionModel;
use App\Models\TipsterStatsModel;
use Spatie\SimpleExcel\SimpleExcelWriter;
use Spatie\SimpleExcel\SimpleExcelReader;

class Admin extends BaseController
{
    protected $db;
    protected $tipModel;
    protected $predictionModel;
    protected $tipsterStatsModel;
    protected $pointsModel;
    protected $notificationModel;

    public function __construct()
    {
        $this->db = \Config\Database::connect();
        $this->tipModel = new TipModel();
        $this->predictionModel = new PredictionModel();
        $this->tipsterStatsModel = new TipsterStatsModel();
        $this->pointsModel = new \App\Models\PointsModel();
        $this->notificationModel = new \App\Models\NotificationModel();
    }

    public function index()
    {
        // Get total tips count
        $data['totalTips'] = $this->tipModel->countAll();

        // Get active predictions count (pending predictions)
        $data['activePredictions'] = $this->predictionModel->where('prediction_outcome', 'pending')->countAllResults();

        // Get total unique authors
        $data['totalAuthors'] = $this->predictionModel->select('author')->distinct()->countAllResults();

        // Get recent transactions count (last 24 hours)
        $data['recentTransactions'] = $this->db->table('transactions')
            ->where('created_at >=', date('Y-m-d H:i:s', strtotime('-24 hours')))
            ->countAllResults();

        // Get global daily hit count
        $tipHitModel = new \App\Models\TipHitModel();
        $globalHit = $tipHitModel->where('tip_id', null)->first();
        $data['globalDailyHits'] = $globalHit ? $globalHit['daily_hits'] : 0;

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

    public function stats()
    {
        return view('admin/dashboard');
    }

    public function addTip()
    {
        $data = [
            'tipModel' => $this->tipModel,
            'nextTipId' => $this->tipModel->getNextId()
        ];
        return view('admin/add_tip', $data);
    }

    public function saveTip()
    {
        $data = $this->request->getPost();

        // Format the matches with all fields
        $matches = [];
        foreach ($data['matches'] as $index => $match) {
            $matches[] = format_tip_match_leg($match);
        }

        // Process booking codes
        $bookingCodes = [];
        if (!empty($data['booking_codes']) && is_array($data['booking_codes'])) {
            foreach ($data['booking_codes'] as $code) {
                if (!empty($code['provider']) && !empty($code['code'])) {
                    $bookingCodes[] = [
                        'provider' => $code['provider'],
                        'code' => $code['code']
                    ];
                }
            }
        }

        // Data to insert into database
        $tip = [
            'title' => $data['title'],
            'tag' => $data['tag'],
            'category' => $data['category'],
            'matches' => json_encode($matches),
            'booking_codes' => json_encode($bookingCodes),
            'amount' => $data['amount'],
            'author' => $data['author'],
            'odds_source' => $data['odds_source'] ?? null,
            'match_time' => !empty($data['match_time']) ? date('Y-m-d H:i:s', strtotime($data['match_time'])) : null,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_at' => date('Y-m-d H:i:s'),
            'is_hot' => isset($data['is_hot']) ? 1 : 0,
            'is_subscriber_only' => isset($data['is_subscriber_only']) ? 1 : 0
        ];

        // Insert data into database
        $this->tipModel->insert($tip);

        return redirect()->to('/admin')->with('success', 'Tip added successfully');
    }

    public function deleteTip($tip_id)
    {
        // Verify the request is POST (security measure)
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            // Start transaction (optional but recommended for data integrity)
            $this->db->transStart();

            // Delete related transactions
            $this->db->table('transactions')->where('tip_id', $tip_id)->delete();

            // Delete related unlock records
            $this->db->table('tips_unlock')->where('tip_id', $tip_id)->delete();

            // Finally, delete the tip itself
            $this->db->table('tips')->where('id', $tip_id)->delete();

            // Complete transaction
            $this->db->transComplete();

            return redirect()->to('/admin/tips')->with('success', 'Tip deleted successfully');
        } catch (\Exception $e) {
            // Rollback transaction if error occurs
            $this->db->transRollback();
            return redirect()->back()->with('error', 'Failed to delete tip: ' . $e->getMessage());
        }
    }

    public function update($id)
    {
        $data = $this->request->getPost();

        $tip = $this->tipModel->find($id);
        if (!$tip) {
            return redirect()->to('/admin')->with('error', 'Tip not found');
        }

        // Process matches data with all fields
        $matches = $data['matches'];
        if (is_array($matches)) {
            $processedMatches = [];
            foreach ($matches as $match) {
                $processedMatches[] = format_tip_match_leg($match);
            }
            $matches = json_encode($processedMatches);
        }

        // Process booking codes
        $bookingCodes = [];
        if (!empty($data['booking_codes']) && is_array($data['booking_codes'])) {
            foreach ($data['booking_codes'] as $code) {
                if (!empty($code['provider']) && !empty($code['code'])) {
                    $bookingCodes[] = [
                        'provider' => $code['provider'],
                        'code' => $code['code']
                    ];
                }
            }
        }

        $updateData = [
            'title' => $data['title'],
            'tag' => $data['tag'],
            'category' => $data['category'],
            'matches' => $matches,
            'booking_codes' => json_encode($bookingCodes),
            'amount' => $data['amount'],
            'author' => $data['author'],
            'odds_source' => $data['odds_source'] ?? null,
            'match_time' => !empty($data['match_time']) ? date('Y-m-d H:i:s', strtotime($data['match_time'])) : null,
            'updated_at' => date('Y-m-d H:i:s'),
            'is_hot' => isset($data['is_hot']) ? 1 : 0,
            'is_subscriber_only' => isset($data['is_subscriber_only']) ? 1 : 0
        ];

        // Add validation if needed
        $validation = \Config\Services::validation();
        $validation->setRules([
            'title' => 'required',
            'tag' => 'required',
            'matches' => 'required',
            'amount' => 'required|numeric',
        ]);

        if (!$validation->withRequest($this->request)->run()) {
            return redirect()->back()->withInput()->with('errors', $validation->getErrors());
        }

        $this->tipModel->update($id, $updateData);
        return redirect()->to('/admin')->with('success', 'Tip updated successfully');
    }

    public function editTip($id)
    {
        $tip = $this->tipModel->find($id);

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

        // Decode the JSON matches field if it's a string
        if (is_string($tip['matches'])) {
            $tip['matches'] = json_decode($tip['matches'], true);
        }

        // Decode the JSON booking_codes field if it's a string
        if (is_string($tip['booking_codes'])) {
            $tip['booking_codes'] = json_decode($tip['booking_codes'], true);
        } else if ($tip['booking_codes'] === null) {
            $tip['booking_codes'] = [];
        }

        // Pass the data to the view
        return view('admin/edit_tip', [
            'tip' => $tip
        ]);
    }

    public function viewTip($id)
    {
        // Fetch the tip from the database by ID
        $tip = $this->tipModel->find($id);

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

        // Decode the matches JSON to display as individual fields
        $matches = json_decode($tip['matches'], true);

        // Pass the tip data to the view
        return view('admin/view_tip', [
            'tip' => $tip,
            'matches' => $matches,
        ]);
    }

    public function getAllTransactions()
    {
        $builder = $this->db->table('transactions');

        // Get filter parameters from request
        $authorName = $this->request->getGet('author');
        $status = $this->request->getGet('status');
        $type = $this->request->getGet('type') ?? '';
        $search = $this->request->getGet('search');

        // Base query (include tips.author so FinFlo and other flows show author name)
        $builder->select('transactions.id, transactions.transaction_id, transactions.msisdn, 
                      transactions.amount, transactions.status, transactions.tip_id, 
                      transactions.plan_id, transactions.naration as narration, transactions.created_at, 
                      transactions.tip_unlocked, transactions.points_awarded,
                      transactions.payment_method,
                      tips.title as tip_title, tips.category, tips.author as tip_author');
        $builder->join('tips', 'tips.id = transactions.tip_id', 'left');

        // Apply filters
        if (!empty($authorName)) {
            $builder->like('transactions.naration', 'by ' . $authorName, 'after');
        }

        if ($status !== null && $status !== '') {
            $builder->where('transactions.status', $status);
        }

        // Apply type filter
        if ($type === 'tip') {
            $builder->where('transactions.plan_id IS NULL');
        } elseif ($type === 'subscription') {
            $builder->where('transactions.plan_id IS NOT NULL');
        }

        // Apply search
        if (!empty($search)) {
            $builder->groupStart()
                ->like('transactions.transaction_id', $search)
                ->orLike('transactions.msisdn', $search)
                ->groupEnd();
        }

        // Order by most recent first
        $builder->orderBy('transactions.created_at', 'DESC');

        return $builder->get()->getResult();
    }

    public function transactions()
    {
        // Get filter parameters from request
        $authorName = $this->request->getGet('author');
        $status = $this->request->getGet('status');
        $type = $this->request->getGet('type');
        $search = $this->request->getGet('search');

        $data['transactions'] = $this->getAllTransactions();

        // Get unique authors for filter dropdown
        $builder = $this->db->table('transactions');
        $builder->select('DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(naration, " by ", -1), " ", 1) as author_name');
        $builder->where('naration LIKE', '% by %');
        $data['authors'] = $builder->get()->getResult();

        // Pass filter values to view
        $data['current_author'] = $authorName;
        $data['current_status'] = $status;
        $data['current_type'] = $type;
        $data['current_search'] = $search;

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

    public function predictions()
    {
        $author = $this->request->getGet('author');
        $outcome = $this->request->getGet('outcome');
        $page = $this->request->getGet('page') ?? 1;

        $query = $this->predictionModel;
        if ($author) {
            $query = $query->where('author', $author);
        }
        if ($outcome) {
            $query = $query->where('prediction_outcome', $outcome);
        }

        $predictions = $query->orderBy('created_at', 'DESC')
            ->paginate(20, 'default', $page);
        $pager = $query->pager;

        $authors = $this->predictionModel->select('author')->distinct()->findAll();

        foreach ($predictions as &$p) {
            if (isset($p['match_data'])) {
                $p['match_data'] = json_decode($p['match_data'], true);
            }
        }

        // Grouped by tipster then ticket: W, L, TBD, %
        $groupedTickets = [];
        $authorsForGroup = $author ? [['author' => $author]] : $authors;
        $tipsterStatsModel = new \App\Models\TipsterStatsModel();
        foreach ($authorsForGroup as $a) {
            $authorName = $a['author'];
            $tickets = $tipsterStatsModel->getAllTicketsWithStats($authorName);
            foreach ($tickets as &$t) {
                $settled = $t['correct'] + $t['wrong'];
                $t['success_pct'] = $settled > 0
                    ? round($t['correct'] / $settled * 100, 1)
                    : null;
            }
            unset($t);
            $groupedTickets[$authorName] = $tickets;
        }

        $data = [
            'predictions' => $predictions,
            'pager' => $pager,
            'authors' => $authors,
            'currentAuthor' => $author,
            'currentOutcome' => $outcome,
            'groupedTickets' => $groupedTickets,
        ];

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

    public function authorStats()
    {
        $stats = $this->tipsterStatsModel
            ->orderBy('success_rate', 'DESC')
            ->findAll();

        $data = [
            'stats' => $stats
        ];

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

    public function markPrediction($id = null)
    {
        // Check if request is AJAX
        $isAjax = $this->request->isAJAX();

        // Validate prediction ID
        if (!$id) {
            return $isAjax
                ? $this->response->setJSON(['status' => 'error', 'message' => 'Prediction ID is required'])
                : redirect()->to('/admin/predictions')->with('error', 'Prediction ID is required');
        }

        // Get input data
        $outcome = $this->request->getPost('outcome');
        $actualScore = $this->request->getPost('actual_score');
        $htScore = $this->request->getPost('ht_score');
        $ftScore = $this->request->getPost('ft_score');

        // Validate outcome
        if (!in_array($outcome, ['correct', 'wrong'])) {
            return $isAjax
                ? $this->response->setJSON(['status' => 'error', 'message' => 'Invalid outcome'])
                : redirect()->to('/admin/predictions')->with('error', 'Invalid outcome');
        }

        // Validate score format if not pending
        if ($outcome !== 'pending' && !preg_match('/^\d+-\d+$/', $actualScore)) {
            return $isAjax
                ? $this->response->setJSON(['status' => 'error', 'message' => 'Invalid score format. Use format like 2-1'])
                : redirect()->to('/admin/predictions')->with('error', 'Invalid score format. Use format like 2-1');
        }

        // Find the prediction
        $prediction = $this->predictionModel->find($id);
        if (!$prediction) {
            return $isAjax
                ? $this->response->setJSON(['status' => 'error', 'message' => 'Prediction not found'])
                : redirect()->to('/admin/predictions')->with('error', 'Prediction not found');
        }

        try {
            // Prepare update data
            $updateData = [
                'prediction_outcome' => $outcome,
                'actual_score' => $actualScore,
                'updated_at' => date('Y-m-d H:i:s')
            ];

            // Add optional scores if provided
            if ($htScore)
                $updateData['ht_score'] = $htScore;
            if ($ftScore)
                $updateData['ft_score'] = $ftScore;

            // Update the prediction
            $this->predictionModel->update($id, $updateData);

            // Update author stats
            $this->tipsterStatsModel->updateSuccessRate($prediction['author']);

            // Return response
            $message = "Prediction #{$id} marked as {$outcome}";
            if ($actualScore)
                $message .= " with score {$actualScore}";

            $redirectUrl = '/admin/author-tickets/' . urlencode($prediction['author']);
            return $isAjax
                ? $this->response->setJSON(['status' => 'success', 'message' => $message])
                : redirect()->to($redirectUrl)->with('message', $message);
        } catch (\Exception $e) {
            log_message('error', 'Failed to mark prediction: ' . $e->getMessage());
            return $isAjax
                ? $this->response->setJSON(['status' => 'error', 'message' => 'Error updating prediction: ' . $e->getMessage()])
                : redirect()->to('/admin/predictions')->with('error', 'Error updating prediction: ' . $e->getMessage());
        }
    }

    public function processTip($id = null)
    {
        log_message('debug', 'processTip called with ID: ' . $id);

        if (!$id) {
            log_message('error', 'No tip ID provided');
            return redirect()->to('/admin/tips')->with('error', 'Tip ID is required');
        }

        try {
            // Get the tip
            $tip = $this->tipModel->find($id);
            log_message('debug', 'Tip data: ' . json_encode($tip));

            if (!$tip) {
                log_message('error', 'Tip not found for ID: ' . $id);
                return redirect()->to('/admin/tips')->with('error', 'Tip not found');
            }

            // Check if matches data exists and is valid
            if (!isset($tip['matches'])) {
                log_message('error', 'No matches data found in tip: ' . $id);
                return redirect()->to('/admin/tips')->with('error', 'No matches data found in this tip');
            }

            // Get matches data - handle both string and array cases
            $matches = is_string($tip['matches']) ? json_decode($tip['matches'], true) : $tip['matches'];
            log_message('debug', 'Matches data: ' . json_encode($matches));

            if (!is_array($matches)) {
                log_message('error', 'Invalid matches data in tip: ' . $id);
                return redirect()->to('/admin/tips')->with('error', 'Invalid matches data');
            }

            // Process each match
            $added = 0;
            foreach ($matches as $match) {
                // Check if this match prediction already exists
                $matchData = json_encode($match);
                $existing = $this->predictionModel->where('tip_id', $id)
                    ->where('match_data', $matchData)
                    ->first();

                // Skip if already processed
                if ($existing) {
                    log_message('debug', 'Match already exists, skipping: ' . $matchData);
                    continue;
                }

                // Add new prediction record
                $predictionData = [
                    'tip_id' => $id,
                    'author' => $tip['author'],
                    'match_data' => $matchData,
                    'match_date' => date('Y-m-d'), // Set actual match date if available
                    'prediction_outcome' => 'pending'
                ];

                log_message('debug', 'Inserting prediction: ' . json_encode($predictionData));
                $this->predictionModel->insert($predictionData);
                $added++;
            }

            log_message('debug', "Successfully added $added new predictions for tip #$id");
            return redirect()->to('/admin/predictions')->with('message', "$added new predictions extracted from tip #$id");
        } catch (\Exception $e) {
            log_message('error', 'Error processing tip: ' . $e->getMessage());
            log_message('error', 'Stack trace: ' . $e->getTraceAsString());
            return redirect()->to('/admin/tips')->with('error', 'Error processing tip: ' . $e->getMessage());
        }
    }

    public function tips()
    {
        $tips = $this->tipModel->where('is_archived', 0)->orderBy('created_at', 'DESC')->findAll();
        $tipHitModel = new \App\Models\TipHitModel();
        // Fetch all tip daily hits in one go
        $hitsData = $tipHitModel->select('tip_id, daily_hits')->findAll();
        $hitsMap = [];
        foreach ($hitsData as $hit) {
            if ($hit['tip_id'] !== null) {
                $hitsMap[$hit['tip_id']] = $hit;
            }
        }
        // Attach daily hit counts to each tip
        foreach ($tips as &$tip) {
            $tipId = $tip['id'];
            $tip['daily_hits'] = isset($hitsMap[$tipId]) ? $hitsMap[$tipId]['daily_hits'] : 0;
        }
        unset($tip);
        $data['tips'] = $tips;
        return view('admin/tips', $data);
    }

    public function downloadTipsTemplate()
    {
        $writer = SimpleExcelWriter::create('php://output', 'csv');

        $writer->addRow([
            'title', 'tag', 'category', 'amount', 'author', 'odds_source',
            'match_time (Y-m-d H:i:s)', 'is_subscriber_only (1 or 0)',
            'matches (team1 vs team2;league;prediction;odds|...)'
        ]);

        $this->response->setHeader('Content-Type', 'text/csv');
        $this->response->setHeader('Content-Disposition', 'attachment; filename="tips_template.csv"');

        $writer->close();
        return $this->response;
    }

    public function importTips()
    {
        $file = $this->request->getFile('import_file');

        $validMimeTypes = ['text/csv', 'application/csv', 'application/vnd.ms-excel', 'text/plain'];
        if (!$file->isValid() || !in_array($file->getMimeType(), $validMimeTypes)) {
            return redirect()->to('admin/tips')->with('error', 'Please upload a valid CSV file.');
        }

        $reader = SimpleExcelReader::create($file->getRealPath(), 'csv');
        $rows = $reader->getRows();

        // Check headers from the first row to ensure the file is valid.
        $firstRow = $rows->first();
        if (!$firstRow || !isset($firstRow['title']) || !isset($firstRow['author'])) {
            return redirect()->to('admin/tips')->with('error', "The CSV file is missing required headers or is empty. Please download and use the provided template to ensure the format is correct.");
        }

        $errors = [];
        $successfulImports = 0;

        $rows->each(function(array $rowProperties) use (&$errors, &$successfulImports) {
            try {
                // Basic validation
                if (empty($rowProperties['title']) || empty($rowProperties['author'])) {
                    $errors[] = "Row with title '{$rowProperties['title']}' is missing required fields (title, author).";
                    return;
                }

                // Parse matches
                $matches = [];
                if (!empty($rowProperties['matches (team1 vs team2;league;prediction;odds|...)'])) {
                    $matchesParts = explode('|', $rowProperties['matches (team1 vs team2;league;prediction;odds|...)']);
                    foreach ($matchesParts as $part) {
                        $details = explode(';', $part);
                        $teams = explode(' vs ', $details[0] ?? '');
                        if (count($details) === 4 && count($teams) === 2) {
                            $matches[] = [
                                'team1' => trim($teams[0]),
                                'team2' => trim($teams[1]),
                                'league' => trim($details[1]),
                                'prediction' => trim($details[2]),
                                'odds' => trim($details[3]),
                            ];
                        }
                    }
                }
                
                if (empty($matches)) {
                    $errors[] = "Row with title '{$rowProperties['title']}' has invalid or empty matches format.";
                    return;
                }

                $this->tipModel->insert([
                    'title'                 => $rowProperties['title'],
                    'tag'                   => $rowProperties['tag'],
                    'category'              => $rowProperties['category'],
                    'amount'                => $rowProperties['amount'],
                    'author'                => $rowProperties['author'],
                    'odds_source'           => $rowProperties['odds_source'],
                    'match_time'            => $rowProperties['match_time (Y-m-d H:i:s)'] ?? null,
                    'is_subscriber_only'    => (int)($rowProperties['is_subscriber_only (1 or 0)'] ?? 0),
                    'matches'               => json_encode($matches),
                    'created_at'            => date('Y-m-d H:i:s'),
                    'updated_at'            => date('Y-m-d H:i:s'),
                ]);

                $successfulImports++;
            } catch (\Exception $e) {
                $errors[] = "Error processing row with title '{$rowProperties['title']}': " . $e->getMessage();
            }
        });

        $message = "Import complete. Successfully imported {$successfulImports} tips.";
        if (!empty($errors)) {
            $message .= " The following errors occurred:";
            session()->setFlashdata('import_errors', $errors);
        }

        return redirect()->to('admin/tips')->with('message', $message);
    }

    public function recalculateStats()
    {
        try {
            $predictionsController = new Predictions();
            $result = $predictionsController->recalculateAll();

            if ($result['status'] === 'success') {
                return redirect()->to('/admin/author-stats')->with('message', $result['message']);
            } else {
                return redirect()->to('/admin/author-stats')->with('error', $result['message']);
            }
        } catch (\Exception $e) {
            return redirect()->to('/admin/author-stats')->with('error', 'Error recalculating stats: ' . $e->getMessage());
        }
    }

    public function tipsUnlock()
    {
        $unlockModel = new \App\Models\TipsUnlockModel();

        // Get all unlock records with related tip information
        $builder = $this->db->table('tips_unlock');
        $builder->select('tips_unlock.*, tips.title as tip_title');
        $builder->join('tips', 'tips.id = tips_unlock.tip_id', 'left');
        $builder->orderBy('tips_unlock.created_at', 'DESC');

        $unlocks = $builder->get()->getResultArray();

        return view('admin/tips_unlock', ['unlocks' => $unlocks]);
    }

    public function manualUnlock($transactionId)
    {
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            $txnModel = new \App\Models\TransactionModel();
            $transaction = $txnModel->where('transaction_id', $transactionId)->first();

            if (!$transaction) {
                return redirect()->back()->with('error', 'Transaction not found');
            }

            $isCrypto = isset($transaction['payment_method']) && $transaction['payment_method'] === 'crypto';
            $status = is_numeric($transaction['status']) ? (int) $transaction['status'] : 0;

            // For crypto: allow approving pending (0) — update to success then create unlock
            if ($isCrypto && $status === 0) {
                $txnModel->update($transaction['id'], ['status' => 1, 'tip_unlocked' => 1]);
                $transaction['msisdn'] = 'crypto';
                $transaction['status'] = 1;
            } elseif ($status != 1) {
                return redirect()->back()->with('error', 'Cannot unlock tip for unsuccessful transaction');
            }

            if (empty($transaction['tip_id'])) {
                return redirect()->back()->with('error', 'No tip associated with this transaction');
            }

            $unlockModel = new \App\Models\TipsUnlockModel();
            $msisdn = $transaction['msisdn'] ?? 'crypto';
            $existingUnlock = $unlockModel->where([
                'transaction_id' => $transactionId,
                'tip_id' => $transaction['tip_id']
            ])->first();

            if ($existingUnlock) {
                return redirect()->back()->with('info', 'Tip is already unlocked');
            }

            $unlockData = [
                'msisdn' => $msisdn,
                'tip_id' => $transaction['tip_id'],
                'transaction_id' => $transactionId,
                'unlock_status' => 1,
                'created_at' => date('Y-m-d H:i:s')
            ];

            if ($unlockModel->insert($unlockData)) {
                $txnModel->update($transaction['id'], ['tip_unlocked' => 1]);
                return redirect()->back()->with('success', 'Tip unlocked successfully');
            }
            return redirect()->back()->with('error', 'Failed to unlock tip');
        } catch (\Exception $e) {
            log_message('error', 'Manual unlock error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while unlocking the tip');
        }
    }

    public function awardPointsView()
    {
        $data['msisdn'] = $this->request->getGet('msisdn');
        return view('admin/award_points', $data);
    }

    public function awardPoints()
    {
        $msisdn = $this->request->getPost('msisdn');
        $points = (int) $this->request->getPost('points');
        $reason = $this->request->getPost('reason');

        // Validate input
        if (empty($msisdn) || $points <= 0) {
            return redirect()->back()->with('error', 'Invalid phone number or points value');
        }

        // Format phone number if needed (remove spaces, ensure it starts with country code)
        $msisdn = preg_replace('/[^0-9]/', '', $msisdn);
        if (strlen($msisdn) === 10) {
            $msisdn = '256' . substr($msisdn, 1);
        }

        try {
            // Award points using the PointsModel with isManualAward set to true
            $result = $this->pointsModel->addPoints($msisdn, $points, true);

            if ($result) {
                // Log the points award if reason is provided
                if (!empty($reason)) {
                    $this->db->table('points_log')->insert([
                        'msisdn' => $msisdn,
                        'points' => $points, // Award exact points without division
                        'reason' => $reason,
                        'created_at' => date('Y-m-d H:i:s')
                    ]);
                }

                return redirect()->back()->with('success', "Successfully awarded {$points} points to {$msisdn}");
            } else {
                return redirect()->back()->with('error', 'Failed to award points');
            }
        } catch (\Exception $e) {
            return redirect()->back()->with('error', 'Error awarding points: ' . $e->getMessage());
        }
    }

    public function userPoints()
    {
        // Get all users with points, ordered by points balance (highest first)
        $users = $this->pointsModel->orderBy('points', 'DESC')->findAll();

        return view('admin/user_points', [
            'users' => $users
        ]);
    }

    public function getTipsterHistory($author = null)
    {
        if (!$author) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Author name is required'
            ]);
        }

        $tipsterStatsModel = new \App\Models\TipsterStatsModel();
        $matchHistory = $tipsterStatsModel->getMatchHistory($author);

        return $this->response->setJSON([
            'status' => 'success',
            'data' => [
                'author' => $author,
                'match_history' => $matchHistory
            ]
        ]);
    }

    public function createNotification()
    {
        if (!$this->request->isAJAX()) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Invalid request method'
            ]);
        }

        $title = $this->request->getPost('title');
        $message = $this->request->getPost('message');

        if (empty($title) || empty($message)) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Title and message are required'
            ]);
        }

        try {
            $this->notificationModel->insert([
                'title' => $title,
                'message' => $message,
                'is_dismissed' => 0
            ]);

            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Notification created successfully'
            ]);
        } catch (\Exception $e) {
            log_message('error', 'Failed to create notification: ' . $e->getMessage());
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Failed to create notification: ' . $e->getMessage()
            ]);
        }
    }

    public function getNotifications()
    {
        try {
            $notifications = $this->notificationModel->getActiveNotifications();

            return $this->response->setJSON([
                'status' => 'success',
                'data' => $notifications
            ]);
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Failed to fetch notifications: ' . $e->getMessage()
            ]);
        }
    }

    public function apiGetNotifications()
    {
        try {
            $notifications = $this->notificationModel->getActiveNotifications();

            return $this->response->setJSON([
                'status' => 'success',
                'data' => $notifications
            ]);
        } catch (\Exception $e) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Failed to fetch notifications: ' . $e->getMessage()
            ]);
        }
    }

    public function dismissNotification($id)
    {
        if (!$this->request->isAJAX()) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Invalid request method'
            ]);
        }

        try {
            $notification = $this->notificationModel->find($id);

            if (!$notification) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'Notification not found'
                ]);
            }

            $this->notificationModel->dismissNotification($id);

            return $this->response->setJSON([
                'status' => 'success',
                'message' => 'Notification dismissed successfully'
            ]);
        } catch (\Exception $e) {
            log_message('error', 'Failed to dismiss notification: ' . $e->getMessage());
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Failed to dismiss notification: ' . $e->getMessage()
            ]);
        }
    }

    public function notificationsView()
    {
        $notifications = $this->notificationModel->orderBy('created_at', 'DESC')->findAll();
        return view('admin/notifications', ['notifications' => $notifications]);
    }

    public function awardPointsFromTransaction($transactionId)
    {
        try {
            // Get transaction details
            $txnModel = new \App\Models\TransactionModel();
            $transaction = $txnModel->where('transaction_id', $transactionId)->first();

            if (!$transaction) {
                return redirect()->back()->with('error', 'Transaction not found');
            }

            // Check if transaction is successful
            if ($transaction['status'] != '1') {
                return redirect()->back()->with('error', 'Can only award points for successful transactions');
            }

            // Check if points were already awarded
            if ($transaction['points_awarded'] == 1) {
                return redirect()->back()->with('error', 'Points were already awarded for this transaction');
            }

            // Award points
            $result = $this->pointsModel->addPoints($transaction['msisdn'], $transaction['amount']);

            if ($result) {
                // Update transaction to mark points as awarded
                $txnModel->update($transaction['id'], ['points_awarded' => 1]);

                // Log the points award
                $this->db->table('points_log')->insert([
                    'msisdn' => $transaction['msisdn'],
                    'points' => floor($transaction['amount'] / 1000), // Convert amount to points (1000 UGX = 1 point)
                    'reason' => 'Manual points award for transaction ' . $transactionId,
                    'created_at' => date('Y-m-d H:i:s')
                ]);

                return redirect()->back()->with('success', 'Points awarded successfully');
            } else {
                return redirect()->back()->with('error', 'Failed to award points');
            }
        } catch (\Exception $e) {
            log_message('error', 'Manual points award error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while awarding points');
        }
    }

    public function updateTransactionStatus($transactionId)
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        $newStatus = $this->request->getPost('status');
        
        // Validate status - allow updating to success (1) or failed (2) from pending (0)
        if (!in_array($newStatus, ['0', '1', '2'])) {
            return redirect()->back()->with('error', 'Invalid status value');
        }

        try {
            // Get transaction details
            $txnModel = new \App\Models\TransactionModel();
            $transaction = $txnModel->where('transaction_id', $transactionId)->first();

            if (!$transaction) {
                return redirect()->back()->with('error', 'Transaction not found');
            }

            // Only allow updating pending transactions
            if ($transaction['status'] != 0) {
                return redirect()->back()->with('error', 'Can only update pending transactions');
            }

            // Update transaction status
            $updateData = ['status' => $newStatus];
            
            // If updating to success status, ensure tip_unlocked is set to 0 initially
            if ($newStatus == '1') {
                $updateData['tip_unlocked'] = 0;
            }

            if ($txnModel->update($transaction['id'], $updateData)) {
                $statusText = $newStatus == '1' ? 'Success' : ($newStatus == '2' ? 'Failed' : 'Pending');
                return redirect()->back()->with('success', "Transaction status updated to {$statusText}");
            } else {
                return redirect()->back()->with('error', 'Failed to update transaction status');
            }
        } catch (\Exception $e) {
            log_message('error', 'Manual transaction status update error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while updating transaction status');
        }
    }

    public function deleteFailedTransactions()
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            $txnModel = new \App\Models\TransactionModel();
            
            // Delete all failed transactions (status = 2)
            $deletedCount = $txnModel->where('status', 2)->delete();
            
            if ($deletedCount > 0) {
                return redirect()->back()->with('success', "Successfully deleted {$deletedCount} failed transactions");
            } else {
                return redirect()->back()->with('info', 'No failed transactions found to delete');
            }
        } catch (\Exception $e) {
            log_message('error', 'Delete failed transactions error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while deleting failed transactions');
        }
    }

    public function deleteExpiredSubscriptionTransactions()
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            $txnModel = new \App\Models\TransactionModel();
            $subscriptionModel = new \App\Models\SubscriptionModel();
            
            // Get all expired subscriptions
            $expiredSubscriptions = $subscriptionModel->where('end_date <', date('Y-m-d H:i:s'))->findAll();
            
            $deletedCount = 0;
            foreach ($expiredSubscriptions as $subscription) {
                // Delete related transactions for this expired subscription
                $deleted = $txnModel->where('transaction_id', $subscription['transaction_id'])->delete();
                if ($deleted) {
                    $deletedCount++;
                }
            }
            
            if ($deletedCount > 0) {
                return redirect()->back()->with('success', "Successfully deleted {$deletedCount} expired subscription transactions");
            } else {
                return redirect()->back()->with('info', 'No expired subscription transactions found to delete');
            }
        } catch (\Exception $e) {
            log_message('error', 'Delete expired subscription transactions error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while deleting expired subscription transactions');
        }
    }

    public function cronDeleteExpiredSubscriptionTransactions()
    {
        try {
            $txnModel = new \App\Models\TransactionModel();
            $subscriptionModel = new \App\Models\SubscriptionModel();
            
            // Get all expired subscriptions
            $expiredSubscriptions = $subscriptionModel->where('end_date <', date('Y-m-d H:i:s'))->findAll();
            
            $deletedCount = 0;
            foreach ($expiredSubscriptions as $subscription) {
                // Delete related transactions for this expired subscription
                $deleted = $txnModel->where('transaction_id', $subscription['transaction_id'])->delete();
                if ($deleted) {
                    $deletedCount++;
                }
            }
            
            log_message('info', "Cron job deleted {$deletedCount} expired subscription transactions");
            
            return $this->response->setJSON([
                'status' => 'success',
                'message' => "Successfully deleted {$deletedCount} expired subscription transactions",
                'deleted_count' => $deletedCount
            ]);
        } catch (\Exception $e) {
            log_message('error', 'Cron delete expired subscription transactions error: ' . $e->getMessage());
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'An error occurred while deleting expired subscription transactions',
                'error' => $e->getMessage()
            ]);
        }
    }

    public function awardPendingSubscriptions()
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            $txnModel = new \App\Models\TransactionModel();
            $subscriptionModel = new \App\Models\SubscriptionModel();
            $planModel = new \App\Models\PlanModel();
            
            // Get all successful subscription transactions that don't have subscriptions yet
            $pendingSubscriptions = $txnModel->where('status', 1)
                ->where('plan_id IS NOT NULL')
                ->findAll();
            
            $awardedCount = 0;
            foreach ($pendingSubscriptions as $transaction) {
                // Check if subscription already exists
                $existingSub = $subscriptionModel->where('transaction_id', $transaction['transaction_id'])->first();
                if ($existingSub) {
                    continue; // Skip if already awarded
                }
                
                // Get plan details
                $plan = $planModel->find($transaction['plan_id']);
                if (!$plan) {
                    log_message('error', "Plan not found for transaction {$transaction['transaction_id']}");
                    continue;
                }
                
                // Create subscription
                $now = date('Y-m-d H:i:s');
                $end = date('Y-m-d H:i:s', strtotime("+{$plan['duration_days']} days", strtotime($now)));
                
                $subscriptionData = [
                    'msisdn' => $transaction['msisdn'],
                    'plan_id' => $transaction['plan_id'],
                    'transaction_id' => $transaction['transaction_id'],
                    'start_date' => $now,
                    'end_date' => $end,
                    'status' => 'active',
                    'created_at' => $now,
                    'updated_at' => $now,
                ];
                
                if ($subscriptionModel->insert($subscriptionData)) {
                    $awardedCount++;
                }
            }
            
            if ($awardedCount > 0) {
                return redirect()->back()->with('success', "Successfully awarded {$awardedCount} pending subscriptions");
            } else {
                return redirect()->back()->with('info', 'No pending subscriptions found to award');
            }
        } catch (\Exception $e) {
            log_message('error', 'Award pending subscriptions error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while awarding pending subscriptions');
        }
    }

    /**
     * View all subscribers (subscriptions)
     */
    public function subscribers()
    {
        $subscriptionModel = new \App\Models\SubscriptionModel();
        $planModel = new \App\Models\PlanModel();
        $subs = $subscriptionModel->orderBy('created_at', 'DESC')->findAll();
        $plans = $planModel->findAll();
        $planMap = [];
        foreach ($plans as $plan) {
            $planMap[$plan['id']] = $plan['name'];
        }
        foreach ($subs as &$sub) {
            $sub['plan_name'] = $planMap[$sub['plan_id']] ?? $sub['plan_id'];
        }
        unset($sub);
        return view('admin/subscribers', ['subscribers' => $subs]);
    }

    /**
     * List and manage plans
     */
    public function plans()
    {
        $planModel = new \App\Models\PlanModel();
        $plans = $planModel->orderBy('id', 'DESC')->findAll();
        return view('admin/plans', ['plans' => $plans]);
    }

    /**
     * Save (add or update) a plan
     */
    public function savePlan()
    {
        $planModel = new \App\Models\PlanModel();
        $data = [
            'name' => $this->request->getPost('name'),
            'tips_per_day' => $this->request->getPost('tips_per_day'),
            'duration_days' => $this->request->getPost('duration_days'),
            'price' => $this->request->getPost('price'),
        ];
        $id = $this->request->getPost('id');
        if ($id) {
            $planModel->update($id, $data);
            session()->setFlashdata('message', 'Plan updated successfully.');
        } else {
            $planModel->insert($data);
            session()->setFlashdata('message', 'Plan added successfully.');
        }
        return redirect()->to('admin/plans');
    }

    /**
     * Delete a plan
     */
    public function deletePlan($id)
    {
        $planModel = new \App\Models\PlanModel();
        $planModel->delete($id);
        session()->setFlashdata('message', 'Plan deleted successfully.');
        return redirect()->to('admin/plans');
    }

    public function manualSubscription($transactionId)
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }

        try {
            $txnModel = new \App\Models\TransactionModel();
            $transaction = $txnModel->where('transaction_id', $transactionId)->first();

            if (!$transaction) {
                return redirect()->back()->with('error', 'Transaction not found');
            }

            // Check if transaction was successful and is for a subscription
            if ($transaction['status'] != 1 || empty($transaction['plan_id'])) {
                return redirect()->back()->with('error', 'Cannot create subscription for unsuccessful or non-subscription transaction');
            }

            $subModel = new \App\Models\SubscriptionModel();
            $existingSub = $subModel->where('transaction_id', $transactionId)->first();

            if ($existingSub) {
                return redirect()->back()->with('info', 'Subscription already exists for this transaction');
            }

            $now = date('Y-m-d H:i:s');
            $planModel = new \App\Models\PlanModel();
            $plan = $planModel->find($transaction['plan_id']);
            if (!$plan) {
                return redirect()->back()->with('error', 'Plan not found');
            }

            $end = date('Y-m-d H:i:s', strtotime("+{$plan['duration_days']} days", strtotime($now)));
            $subModel->insert([
                'msisdn' => $transaction['msisdn'],
                'plan_id' => $transaction['plan_id'],
                'transaction_id' => $transactionId,
                'start_date' => $now,
                'end_date' => $end,
                'status' => 'active',
                'created_at' => $now,
                'updated_at' => $now,
            ]);

            return redirect()->back()->with('success', 'Subscription created successfully');
        } catch (\Exception $e) {
            log_message('error', 'Manual subscription error: ' . $e->getMessage());
            return redirect()->back()->with('error', 'An error occurred while creating the subscription');
        }
    }

    /**
     * Resets all daily_hits counters to zero. Protect with a simple secret key in the URL.
     * Example: /admin/resetDailyHits?key=YOUR_SECRET_KEY
     */
    public function resetDailyHits()
    {
        $secret = $this->request->getGet('key');
        $expected = getenv('DAILY_HIT_RESET_KEY') ?: 'changeme';
        if ($secret !== $expected) {
            return $this->response->setStatusCode(403)->setBody('Forbidden');
        }
        $tipHitModel = new \App\Models\TipHitModel();
        $tipHitModel->builder()->set(['daily_hits' => 0])->update();
        return $this->response->setStatusCode(200)->setBody('Daily hits reset.');
    }

    /**
     * Deletes all tip_hits records except the global (tip_id is null) record.
     * Used by the dashboard Reset Hit Stats button.
     */
    public function deleteHitStats()
    {
        $tipHitModel = new \App\Models\TipHitModel();
        // Delete all where tip_id is not null
        $tipHitModel->where('tip_id IS NOT NULL', null, false)->delete();
        session()->setFlashdata('success', 'All hit stats have been reset.');
        return redirect()->to('/admin');
    }

    /**
     * Show all tickets (tips) for a given author
     */
    public function authorTickets($author = null)
    {
        if (!$author) {
            return redirect()->to('/admin/predictions')->with('error', 'Author is required');
        }
        $author = urldecode($author);
        $tipModel = new \App\Models\TipModel();
        $tips = $tipModel->where('author', $author)->where('is_archived', 0)->orderBy('created_at', 'DESC')->findAll();
        return view('admin/author_tickets', [
            'author' => $author,
            'tickets' => $tips
        ]);
    }

    /**
     * Show/manage all predictions for a given ticket (tip)
     */
    public function ticketPredictions($tipId = null)
    {
        if (!$tipId) {
            return redirect()->to('/admin/predictions')->with('error', 'Tip ID is required');
        }
        $tipModel = new \App\Models\TipModel();
        $predictionModel = new \App\Models\PredictionModel();
        $tip = $tipModel->find($tipId);
        if (!$tip) {
            return redirect()->to('/admin/predictions')->with('error', 'Tip not found');
        }
        $predictions = $predictionModel->where('tip_id', $tipId)->findAll();
        // Decode match data for each prediction
        foreach ($predictions as &$p) {
            if (isset($p['match_data'])) {
                $p['match_data'] = json_decode($p['match_data'], true);
            }
        }
        return view('admin/ticket_predictions', [
            'tip' => $tip,
            'predictions' => $predictions
        ]);
    }

    /**
     * Show a list of all authors, each linking to their tickets page
     */
    public function authorList()
    {
        $tipModel = new \App\Models\TipModel();
        $authors = $tipModel->select('author')->distinct()->orderBy('author', 'ASC')->findAll();
        return view('admin/author_list', [
            'authors' => $authors
        ]);
    }

    /**
     * Send FCM notification for a tip
     * POST /admin/send-notification/{tipId}
     */
    public function sendNotification($tipId = null)
    {
        // Verify the request is POST
        if (!$this->request->is('post')) {
            if ($this->request->isAJAX()) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => 'Invalid request method'
                ])->setStatusCode(405);
            }
            return redirect()->back()->with('error', 'Invalid request method');
        }

        if (!$tipId) {
            $message = 'Tip ID is required';
            if ($this->request->isAJAX()) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => $message
                ])->setStatusCode(400);
            }
            return redirect()->back()->with('error', $message);
        }

        try {
            // Get the tip
            $tip = $this->tipModel->find($tipId);
            
            if (!$tip) {
                $message = 'Tip not found';
                if ($this->request->isAJAX()) {
                    return $this->response->setJSON([
                        'status' => 'error',
                        'message' => $message
                    ])->setStatusCode(404);
                }
                return redirect()->back()->with('error', $message);
            }

            // Initialize FCM service
            $fcmService = new \App\Libraries\FcmNotificationService();
            
            // Send notification
            $result = $fcmService->sendNewTipNotification($tip);
            
            if ($result['success']) {
                $message = "Notification sent successfully! Delivered to {$result['sent']} device(s)";
                if ($result['failed'] > 0) {
                    $message .= ", {$result['failed']} failed";
                }
                
                if ($this->request->isAJAX()) {
                    return $this->response->setJSON([
                        'status' => 'success',
                        'message' => $message,
                        'data' => [
                            'sent' => $result['sent'],
                            'failed' => $result['failed'] ?? 0
                        ]
                    ]);
                }
                
                return redirect()->to('/admin/tips')->with('success', $message);
            } else {
                $errorMessage = $result['message'] ?? 'Failed to send notification';
                
                if ($this->request->isAJAX()) {
                    return $this->response->setJSON([
                        'status' => 'error',
                        'message' => $errorMessage,
                        'data' => $result
                    ])->setStatusCode(500);
                }
                
                return redirect()->to('/admin/tips')->with('error', $errorMessage);
            }
        } catch (\Exception $e) {
            log_message('error', 'Send notification error: ' . $e->getMessage());
            $errorMessage = 'An error occurred while sending notification: ' . $e->getMessage();
            
            if ($this->request->isAJAX()) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'message' => $errorMessage
                ])->setStatusCode(500);
            }
            
            return redirect()->to('/admin/tips')->with('error', $errorMessage);
        }
    }

    /**
     * Send FCM notification to purchasers of a ticket (tip), with optional custom message.
     * POST /admin/send-ticket-notification/{tipId}
     * Body (JSON): { "message": "optional custom text" }
     */
    public function sendTicketNotification($tipId = null)
    {
        if (!$this->request->is('post')) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Invalid request method'])->setStatusCode(405);
        }
        if (!$tipId) {
            return $this->response->setJSON(['status' => 'error', 'message' => 'Tip ID is required'])->setStatusCode(400);
        }

        try {
            $body = $this->request->getJSON(true) ?: [];
            $customMessage = trim((string) ($body['message'] ?? ''));
            $tipTitleFromRequest = trim((string) ($body['tip_title'] ?? ''));
            $tip = $this->tipModel->find($tipId);
            $tipTitle = $tip['title'] ?? $tipTitleFromRequest;
            if ($tipTitle === '') {
                $tipTitle = 'Tip #' . $tipId;
            }
            $title = 'Ticket Won';
            $message = $customMessage !== ''
                ? $customMessage
                : "Your ticket \"{$tipTitle}\" has won. Go for collections!";

            $fcmService = new \App\Libraries\FcmNotificationService();
            $result = $fcmService->sendToAll($title, $message, [
                'type' => 'ticket_won',
                'tip_id' => $tipId,
            ]);

            $msg = $result['success']
                ? "Notification sent to {$result['sent']} device(s)" . ($result['failed'] > 0 ? ", {$result['failed']} failed" : '')
                : ($result['message'] ?? 'Failed to send');
            return $this->response->setJSON([
                'status' => $result['success'] ? 'success' : 'error',
                'message' => $msg,
                'data' => ['sent' => $result['sent'] ?? 0, 'failed' => $result['failed'] ?? 0]
            ]);
        } catch (\Exception $e) {
            log_message('error', 'Send ticket notification error: ' . $e->getMessage());
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'An error occurred: ' . $e->getMessage()
            ])->setStatusCode(500);
        }
    }

    /**
     * App Version Management
     * GET /admin/app-version
     */
    public function appVersion()
    {
        $db = \Config\Database::connect();
        
        // Ensure app_settings table exists
        $this->ensureAppSettingsTable($db);
        
        // Get all app_settings for debugging
        $allSettings = $db->table('app_settings')->get()->getResultArray();
        
        // Get current app version
        $version = $db->table('app_settings')
            ->where('setting_key', 'app_version')
            ->get()
            ->getRowArray();
        $currentVersion = $version ? (int)$version['setting_value'] : 1;
        
        // Get all download links
        $downloadLinks = $db->table('app_settings')
            ->where('setting_key LIKE', 'download_link_%')
            ->get()
            ->getResultArray();
        
        $links = [];
        foreach ($downloadLinks as $link) {
            $key = str_replace('download_link_', '', $link['setting_key']);
            $links[$key] = $link['setting_value'];
        }
        
        $data = [
            'currentVersion' => $currentVersion,
            'downloadLinks' => $links,
            'requireUpdateAndroid' => $this->getAppSettingsRequireUpdateFlag($db, 'require_update_android'),
            'requireUpdateIos' => $this->getAppSettingsRequireUpdateFlag($db, 'require_update_ios'),
            'allSettings' => $allSettings, // For debugging
            'tableExists' => $db->tableExists('app_settings')
        ];
        
        return view('admin/app_version', $data);
    }

    /**
     * Update App Version and Download Links
     * POST /admin/app-version/update
     */
    public function updateAppVersion()
    {
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }
        
        $db = \Config\Database::connect();
        $this->ensureAppSettingsTable($db);
        
        $version = (int)$this->request->getPost('version');
        if ($version < 1) {
            return redirect()->back()->with('error', 'Version must be at least 1');
        }
        
        // Update version
        $this->updateSetting($db, 'app_version', $version);

        $this->updateSetting(
            $db,
            'require_update_android',
            $this->request->getPost('require_update_android') === '1' ? '1' : '0'
        );
        $this->updateSetting(
            $db,
            'require_update_ios',
            $this->request->getPost('require_update_ios') === '1' ? '1' : '0'
        );
        
        // Update download links
        $linkKeys = ['apk', 'googlePlay', 'huawei', 'oppo', 'appStore'];
        foreach ($linkKeys as $key) {
            $url = $this->request->getPost("link_$key");
            if (!empty($url) && trim($url) !== '') {
                // Update with new URL
                $this->updateSetting($db, "download_link_$key", trim($url));
            } else {
                // If empty, delete the setting (set to NULL)
                $this->deleteSetting($db, "download_link_$key");
            }
        }
        
        return redirect()->to('admin/app-version')->with('success', 'App version and download links updated successfully.');
    }

    /**
     * Ensure app_settings table exists
     */
    private function ensureAppSettingsTable($db)
    {
        if (!$db->tableExists('app_settings')) {
            $sql = "CREATE TABLE IF NOT EXISTS `app_settings` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `setting_key` varchar(100) NOT NULL,
                `setting_value` text,
                `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
                `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`),
                UNIQUE KEY `setting_key` (`setting_key`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;";
            
            $db->query($sql);
        }
    }

    /**
     * Update or insert a setting
     */
    private function updateSetting($db, $key, $value)
    {
        $exists = $db->table('app_settings')
            ->where('setting_key', $key)
            ->countAllResults() > 0;
        
        if ($exists) {
            return $db->table('app_settings')
                ->where('setting_key', $key)
                ->update(['setting_value' => $value]);
        } else {
            return $db->table('app_settings')
                ->insert([
                    'setting_key' => $key,
                    'setting_value' => $value
                ]);
        }
    }

    /**
     * Delete a setting (set to NULL or remove)
     */
    private function deleteSetting($db, $key)
    {
        return $db->table('app_settings')
            ->where('setting_key', $key)
            ->delete();
    }

    /**
     * Per-platform forced update flag; default true if missing (backward compatible).
     */
    private function getAppSettingsRequireUpdateFlag($db, string $key): bool
    {
        $row = $db->table('app_settings')
            ->where('setting_key', $key)
            ->get()
            ->getRowArray();
        if (!$row || !isset($row['setting_value'])) {
            return true;
        }
        $v = strtolower(trim((string) $row['setting_value']));
        if (in_array($v, ['0', 'false', 'no', 'off'], true)) {
            return false;
        }
        return true;
    }

    // ------------------------------------------------------------------
    // Bet Builder admin
    // ------------------------------------------------------------------

    /**
     * List bet-builder matches for admin (upcoming vs settled history tabs).
     */
    public function builderMatches()
    {
        $tab = $this->request->getGet('tab') ?? \App\Models\BuilderMatchModel::TAB_UPCOMING;
        if (!in_array($tab, [\App\Models\BuilderMatchModel::TAB_UPCOMING, \App\Models\BuilderMatchModel::TAB_SETTLED], true)) {
            $tab = \App\Models\BuilderMatchModel::TAB_UPCOMING;
        }

        $page = max(1, (int) ($this->request->getGet('page') ?? 1));
        $perPage = 25;

        $model = new \App\Models\BuilderMatchModel();
        $query = $model->forAdminTab($tab);

        if ($tab === \App\Models\BuilderMatchModel::TAB_SETTLED) {
            $query = $query->orderBy('match_time', 'DESC')->orderBy('id', 'DESC');
        } else {
            $query = $query->orderBy('match_time', 'ASC')->orderBy('id', 'ASC');
        }

        $matches = $query->paginate($perPage, 'default', $page);
        $pager = $model->pager;

        return view('admin/bet_builder_matches', [
            'matches' => $matches,
            'tiers' => \App\Libraries\BetBuilderPricing::allTiers(),
            'tab' => $tab,
            'pager' => $pager,
            'upcomingCount' => (new \App\Models\BuilderMatchModel())->countForAdminTab(\App\Models\BuilderMatchModel::TAB_UPCOMING),
            'settledCount' => (new \App\Models\BuilderMatchModel())->countForAdminTab(\App\Models\BuilderMatchModel::TAB_SETTLED),
        ]);
    }

    /**
     * Redirect back to bet-builder matches preserving tab (and optional page).
     */
    private function redirectToBetBuilderMatches(?string $tab = null, ?int $page = null)
    {
        $tab = $tab ?? \App\Models\BuilderMatchModel::TAB_UPCOMING;
        if (!in_array($tab, [\App\Models\BuilderMatchModel::TAB_UPCOMING, \App\Models\BuilderMatchModel::TAB_SETTLED], true)) {
            $tab = \App\Models\BuilderMatchModel::TAB_UPCOMING;
        }

        $params = ['tab' => $tab];
        if ($page !== null && $page > 1) {
            $params['page'] = $page;
        }

        return redirect()->to(site_url('admin/bet-builder/matches') . '?' . http_build_query($params));
    }

    private function betBuilderReturnTab(): string
    {
        $tab = $this->request->getPost('return_tab')
            ?? $this->request->getGet('tab')
            ?? \App\Models\BuilderMatchModel::TAB_UPCOMING;

        return in_array($tab, [\App\Models\BuilderMatchModel::TAB_UPCOMING, \App\Models\BuilderMatchModel::TAB_SETTLED], true)
            ? $tab
            : \App\Models\BuilderMatchModel::TAB_UPCOMING;
    }

    /**
     * Insert or update a bet-builder match.
     */
    public function saveBuilderMatch()
    {
        $model = new \App\Models\BuilderMatchModel();
        $post = $this->request->getPost();

        $payload = [
            'team1' => trim((string) ($post['team1'] ?? '')),
            'team2' => trim((string) ($post['team2'] ?? '')),
            'league' => trim((string) ($post['league'] ?? '')),
            'match_time' => !empty($post['match_time'])
                ? date('Y-m-d H:i:s', strtotime($post['match_time']))
                : null,
            'odds' => is_numeric($post['odds'] ?? null) ? (float) $post['odds'] : 1.00,
            'our_prediction' => trim((string) ($post['our_prediction'] ?? '')),
            'status' => $post['status'] ?? 'upcoming',
            'result_outcome' => $post['result_outcome'] ?? 'pending',
            'is_archived' => isset($post['is_archived']) ? 1 : 0,
        ];

        if ($payload['team1'] === '' || $payload['team2'] === '') {
            return redirect()->back()->with('error', 'Team names are required.');
        }
        if ($payload['odds'] <= 0) {
            return redirect()->back()->with('error', 'Odds must be greater than 0.');
        }

        $id = $post['id'] ?? null;
        if ($id) {
            $model->update($id, $payload);
            session()->setFlashdata('message', 'Match updated.');
        } else {
            $model->insert($payload);
            session()->setFlashdata('message', 'Match added.');
        }

        $redirectTab = $payload['status'] === 'upcoming'
            ? \App\Models\BuilderMatchModel::TAB_UPCOMING
            : \App\Models\BuilderMatchModel::TAB_SETTLED;

        return $this->redirectToBetBuilderMatches($redirectTab);
    }

    /**
     * Bulk-import bet-builder matches from cached fixtures (JSON POST).
     */
    public function bulkSaveBuilderMatches()
    {
        if (!$this->request->is('post')) {
            return $this->response->setStatusCode(405)->setJSON([
                'success' => false,
                'message' => 'Invalid request method',
            ]);
        }

        $body = $this->request->getJSON(true);
        if (!is_array($body)) {
            $body = $this->request->getPost();
        }

        $rawMatches = $body['matches'] ?? [];
        if (!is_array($rawMatches) || $rawMatches === []) {
            return $this->response->setStatusCode(422)->setJSON([
                'success' => false,
                'message' => 'No matches selected for import.',
            ]);
        }

        $model = new \App\Models\BuilderMatchModel();
        $prepared = [];
        $errors = [];

        foreach ($rawMatches as $index => $row) {
            if (!is_array($row)) {
                $errors[] = 'Row ' . ($index + 1) . ': invalid data.';
                continue;
            }

            $team1 = trim((string) ($row['team1'] ?? ''));
            $team2 = trim((string) ($row['team2'] ?? ''));
            $league = trim((string) ($row['league'] ?? ''));
            $prediction = trim((string) ($row['our_prediction'] ?? ''));
            $odds = is_numeric($row['odds'] ?? null) ? (float) $row['odds'] : 0.0;
            $matchTime = \App\Models\BuilderMatchModel::normalizeMatchTime(
                isset($row['match_time']) ? (string) $row['match_time'] : null
            );

            $label = $team1 !== '' && $team2 !== ''
                ? $team1 . ' vs ' . $team2
                : 'Row ' . ($index + 1);

            if ($team1 === '' || $team2 === '') {
                $errors[] = $label . ': team names are required.';
                continue;
            }
            if ($prediction === '') {
                $errors[] = $label . ': prediction is required.';
                continue;
            }
            if ($odds <= 0) {
                $errors[] = $label . ': odds must be greater than 0.';
                continue;
            }

            $prepared[] = [
                'team1' => $team1,
                'team2' => $team2,
                'league' => $league,
                'match_time' => $matchTime,
                'odds' => $odds,
                'our_prediction' => $prediction,
                'status' => 'upcoming',
                'result_outcome' => 'pending',
                'is_archived' => 0,
            ];
        }

        if ($errors !== []) {
            return $this->response->setStatusCode(422)->setJSON([
                'success' => false,
                'message' => 'Fix the highlighted rows before importing.',
                'errors' => $errors,
            ]);
        }

        if ($prepared === []) {
            return $this->response->setStatusCode(422)->setJSON([
                'success' => false,
                'message' => 'No valid matches to import.',
            ]);
        }

        $inserted = 0;
        $skipped = 0;

        foreach ($prepared as $payload) {
            if ($model->existsDuplicate(
                $payload['team1'],
                $payload['team2'],
                $payload['match_time']
            )) {
                $skipped++;
                continue;
            }

            if ($model->insert($payload)) {
                $inserted++;
            }
        }

        $summary = $inserted . ' match' . ($inserted === 1 ? '' : 'es') . ' added';
        if ($skipped > 0) {
            $summary .= ', ' . $skipped . ' skipped (already in pool)';
        }
        session()->setFlashdata('message', $summary);

        return $this->response->setJSON([
            'success' => true,
            'inserted' => $inserted,
            'skipped' => $skipped,
            'message' => $summary,
            'redirect' => site_url('admin/bet-builder/matches') . '?tab=upcoming',
        ]);
    }

    /**
     * Hard-delete a bet-builder match.
     */
    public function deleteBuilderMatch($id)
    {
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }
        (new \App\Models\BuilderMatchModel())->delete($id);
        session()->setFlashdata('message', 'Match deleted.');
        return $this->redirectToBetBuilderMatches($this->betBuilderReturnTab());
    }

    /**
     * Archive / unarchive a bet-builder match so it hides from the public pool
     * without losing history for already-paid tickets.
     */
    public function archiveBuilderMatch($id)
    {
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }
        $model = new \App\Models\BuilderMatchModel();
        $match = $model->find($id);
        if (!$match) {
            return redirect()->back()->with('error', 'Match not found.');
        }
        $model->update($id, ['is_archived' => $match['is_archived'] ? 0 : 1]);
        session()->setFlashdata('message', 'Match archive state toggled.');
        return $this->redirectToBetBuilderMatches($this->betBuilderReturnTab());
    }

    /**
     * Settle a match outcome (correct / wrong / void).
     */
    public function settleBuilderMatch($id)
    {
        if (!$this->request->is('post')) {
            return redirect()->back()->with('error', 'Invalid request method');
        }
        $outcome = $this->request->getPost('result_outcome');
        if (!in_array($outcome, ['pending', 'correct', 'wrong'], true)) {
            return redirect()->back()->with('error', 'Invalid outcome.');
        }
        $returnTab = $this->betBuilderReturnTab();
        $status = $outcome === 'pending' ? 'upcoming' : 'settled';
        (new \App\Models\BuilderMatchModel())->update($id, [
            'result_outcome' => $outcome,
            'status' => $status,
        ]);
        session()->setFlashdata('message', 'Match settled.');
        if ($outcome === 'pending') {
            return $this->redirectToBetBuilderMatches(\App\Models\BuilderMatchModel::TAB_UPCOMING);
        }
        return $this->redirectToBetBuilderMatches(
            $status === 'settled' ? \App\Models\BuilderMatchModel::TAB_SETTLED : $returnTab
        );
    }

    /**
     * Read-only list of bet-builder tickets for revenue visibility.
     */
    public function builderTickets()
    {
        $model = new \App\Models\BuilderTicketModel();
        $tickets = $model->orderBy('created_at', 'DESC')->findAll();

        $totalRevenue = 0;
        $paidCount = 0;
        foreach ($tickets as $t) {
            if (($t['status'] ?? '') === 'paid' || ($t['status'] ?? '') === 'settled') {
                $totalRevenue += (int) ($t['amount'] ?? 0);
                $paidCount++;
            }
        }

        return view('admin/bet_builder_tickets', [
            'tickets' => $tickets,
            'total_revenue' => $totalRevenue,
            'paid_count' => $paidCount,
        ]);
    }

    public function footballSettings()
    {
        $settingsModel = new \App\Models\FootballSettingModel();
        $settings = $settingsModel->getSettings();
        $countryCount = (new \App\Models\FootballCountryModel())->countAllResults();
        $leagueCount = (new \App\Models\FootballLeagueModel())->countAllResults();
        $fixtureCount = (new \App\Models\FootballFixtureModel())->countAllResults();

        return view('admin/football_settings', [
            'settings' => $settings,
            'country_count' => $countryCount,
            'league_count' => $leagueCount,
            'fixture_count' => $fixtureCount,
            'has_api_key' => config('ApiFootball')->getApiKey() !== '',
        ]);
    }

    public function saveFootballSettings()
    {
        $post = $this->request->getPost();
        $settingsModel = new \App\Models\FootballSettingModel();

        $season = (int) ($post['current_season'] ?? 2025);
        if ($season < 2000 || $season > 2100) {
            return redirect()->back()->with('error', 'Invalid season year.');
        }

        $settingsModel->updateSettings([
            'timezone' => $post['timezone'] ?? 'Africa/Nairobi',
            'current_season' => $season,
            'season_ends_at' => !empty($post['season_ends_at']) ? $post['season_ends_at'] : null,
        ]);

        return redirect()->to('admin/football/settings')->with('success', 'Football settings saved.');
    }

    /**
     * Cron: sync countries and leagues. Protect with DAILY_HIT_RESET_KEY or FOOTBALL_SYNC_KEY.
     * Example: /admin/cron/football-sync-reference?key=YOUR_SECRET_KEY
     */
    public function cronFootballSyncReference()
    {
        $secret = $this->request->getGet('key');
        $expected = getenv('FOOTBALL_SYNC_KEY') ?: (getenv('DAILY_HIT_RESET_KEY') ?: 'changeme');
        if ($secret !== $expected) {
            return $this->response->setStatusCode(403)->setBody('Forbidden');
        }

        try {
            set_time_limit(0);
            $result = (new \App\Libraries\FootballReferenceSync())->syncAll();

            return $this->response->setJSON([
                'success' => true,
                'countries' => $result['countries'],
                'leagues' => $result['leagues'],
                'warnings' => $result['warnings'],
            ]);
        } catch (\Throwable $e) {
            return $this->response->setStatusCode(500)->setJSON([
                'success' => false,
                'message' => $e->getMessage(),
            ]);
        }
    }

    /**
     * Cron: refresh fixture cache for all leagues. Run daily at 00:00 Africa/Nairobi.
     * Example: GET /admin/cron/football-sync-fixtures?key=YOUR_SECRET_KEY
     */
    public function cronFootballSyncFixtures()
    {
        $secret = $this->request->getGet('key');
        $expected = getenv('FOOTBALL_SYNC_KEY') ?: (getenv('DAILY_HIT_RESET_KEY') ?: 'changeme');
        if ($secret !== $expected) {
            return $this->response->setStatusCode(403)->setBody('Forbidden');
        }

        set_time_limit(0);

        try {
            $result = (new \App\Libraries\FootballFixturesDailySync())->syncAll();

            return $this->response->setJSON([
                'success' => true,
                'leagues' => $result['leagues'],
                'fixtures' => $result['fixtures'],
                'from' => $result['from'],
                'to' => $result['to'],
                'errors' => array_slice($result['errors'], 0, 10),
            ]);
        } catch (\Throwable $e) {
            return $this->response->setStatusCode(500)->setJSON([
                'success' => false,
                'message' => $e->getMessage(),
            ]);
        }
    }
}
