<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\FinancingApplication;
use App\Models\ContactMessage;
use App\Models\ServiceBooking;
use App\Models\NewsletterSubscriber;
use App\Models\AdminUser;
use App\Models\Setting;
use App\Models\Page;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class DashboardController extends Controller
{
    public function index()
    {
        global $conn;
        
        $stats = [
            'total_applications' => $conn->query("SELECT COUNT(*) FROM financing_applications")->fetchColumn(),
            'pending_applications' => $conn->query("SELECT COUNT(*) FROM financing_applications WHERE status = 'pending'")->fetchColumn(),
            'approved_applications' => $conn->query("SELECT COUNT(*) FROM financing_applications WHERE status = 'approved'")->fetchColumn(),
            'total_messages' => $conn->query("SELECT COUNT(*) FROM contact_messages")->fetchColumn(),
            'unread_messages' => $conn->query("SELECT COUNT(*) FROM contact_messages WHERE status = 'unread'")->fetchColumn(),
            'total_bookings' => $conn->query("SELECT COUNT(*) FROM service_bookings")->fetchColumn(),
            'pending_bookings' => $conn->query("SELECT COUNT(*) FROM service_bookings WHERE status = 'pending'")->fetchColumn(),
            'newsletter_subscribers' => $conn->query("SELECT COUNT(*) FROM newsletter_subscribers WHERE is_active = 1")->fetchColumn(),
            'total_blogs' => $conn->query("SELECT COUNT(*) FROM blogs")->fetchColumn(),
            'total_services' => $conn->query("SELECT COUNT(*) FROM services")->fetchColumn(),
            'optimized_count' => (int)$conn->query("SELECT COUNT(*) FROM cars WHERE meta_title IS NOT NULL AND meta_title != ''")->fetchColumn() + 
                                 (int)$conn->query("SELECT COUNT(*) FROM services WHERE meta_title IS NOT NULL AND meta_title != ''")->fetchColumn(),
        ];

        // Monthly applications for chart (last 6 months)
        $sixMonthsAgo = date('Y-m-d H:i:s', strtotime('-6 months'));
        $stmt = $conn->prepare("SELECT MONTH(created_at) as month, YEAR(created_at) as year, COUNT(*) as count FROM financing_applications WHERE created_at >= ? GROUP BY year, month ORDER BY year, month");
        $stmt->execute([$sixMonthsAgo]);
        $monthlyApplications = array_map(fn($item) => (object)$item, $stmt->fetchAll());

        // Application status breakdown
        $statusBreakdown = array_map(fn($item) => (object)$item, $conn->query("SELECT status, COUNT(*) as count FROM financing_applications GROUP BY status")->fetchAll());

        // Recent activity
        $recentApplications_raw = $conn->query("SELECT * FROM financing_applications ORDER BY created_at DESC LIMIT 5")->fetchAll();
        $recentApplications = array_map(function($item) {
            $obj = (object)$item;
            $obj->full_name = ($obj->first_name ?? '') . ' ' . ($obj->last_name ?? '');
            return $obj;
        }, $recentApplications_raw);

        $recentMessages = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM contact_messages ORDER BY created_at DESC LIMIT 5")->fetchAll());
        $recentBookings = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM service_bookings ORDER BY created_at DESC LIMIT 5")->fetchAll());

        return view('admin.dashboard', compact(
            'stats', 'monthlyApplications', 'statusBreakdown',
            'recentApplications', 'recentMessages', 'recentBookings'
        ));
    }

    public function seoSettings()
    {
        global $conn;
        $settings_raw = $conn->query("SELECT `key`, `value` FROM settings")->fetchAll();
        $settings = [];
        foreach ($settings_raw as $s) {
            $settings[$s['key']] = $s['value'];
        }

        $pages = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM pages")->fetchAll());
        $blogs = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM blogs")->fetchAll());
        $services = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM services")->fetchAll());
        $cars = array_map(fn($item) => (object)$item, $conn->query("SELECT * FROM cars")->fetchAll());
        
        return view('admin.settings.seo', compact('settings', 'pages', 'blogs', 'services', 'cars'));
    }

    public function updateSeoSettings(Request $request)
    {
        global $conn;
        foreach ($request->except('_token') as $key => $value) {
            $stmt = $conn->prepare("SELECT id FROM settings WHERE `key` = ?");
            $stmt->execute([$key]);
            if ($stmt->fetch()) {
                $upd = $conn->prepare("UPDATE settings SET `value` = ? WHERE `key` = ?");
                $upd->execute([$value, $key]);
            } else {
                $ins = $conn->prepare("INSERT INTO settings (`key`, `value`, created_at, updated_at) VALUES (?, ?, NOW(), NOW())");
                $ins->execute([$key, $value]);
            }
        }
        return redirect()->back()->with('success', 'SEO settings updated successfully.');
    }
}
