<?php
require_once __DIR__ . '/../../../../helpers/response.php';
require_once __DIR__ . '/../../../Middleware/VendorAuthMiddleware.php';
require_once __DIR__ . '/../../../Models/Database.php';

class BusinessController
{
    public function __construct()
    {
        $payload = VendorAuthMiddleware::authenticate();
        VendorAuthMiddleware::enforceApproved($payload);
    }

    public function quotationRequests()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $tab = $_GET['tab'] ?? null; // pending, responded, past

        $db = Database::getInstance();
        
        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        // 1. Count query
        $countSql = "
            SELECT COUNT(*) 
            FROM quotation_requests qr
            LEFT JOIN (
                SELECT q1.* FROM quotations q1
                INNER JOIN (
                    SELECT quotation_request_id, MAX(id) as max_id 
                    FROM quotations 
                    GROUP BY quotation_request_id
                ) q2 ON q1.id = q2.max_id
            ) q ON qr.id = q.quotation_request_id
            WHERE qr.vendor_id = ?
        ";

        // 2. Select query
        $sql = "
            SELECT qr.*, 
                   c.full_name as customer_name,
                   ct.name_en as customer_city,
                   m.full_name as maid_name, m.profile_image as maid_image,
                   sm.name_en as service_model_name,
                   pn.name_en as preferred_nationality_name,
                   
                   -- Join quotation info if exists
                   q.id as quotation_id, q.total_amount as quoted_monthly_rate,
                   q.status as quotation_status, q.notes as quotation_notes,
                   q.accepted_at as quotation_accepted_at,
                   q.updated_at as quotation_updated_at,
                   q.decline_reason as quotation_decline_reason,
                   
                   -- Drafted/contracted helper details
                   qm.full_name as quoted_maid_name, qm.profile_image as quoted_maid_image
            FROM quotation_requests qr
            JOIN customers c ON qr.customer_id = c.id
            LEFT JOIN cities ct ON c.city_id = ct.id
            LEFT JOIN maids m ON qr.maid_id = m.id
            LEFT JOIN service_models sm ON qr.service_model_id = sm.id
            LEFT JOIN nationalities pn ON qr.preferred_nationality_id = pn.id
            
            -- Join quotation related to this request (latest one)
            LEFT JOIN (
                SELECT q1.* FROM quotations q1
                INNER JOIN (
                    SELECT quotation_request_id, MAX(id) as max_id 
                    FROM quotations 
                    GROUP BY quotation_request_id
                ) q2 ON q1.id = q2.max_id
            ) q ON qr.id = q.quotation_request_id
            LEFT JOIN maids qm ON q.maid_id = qm.id
            
            WHERE qr.vendor_id = ?
        ";
        
        $params = [$vendorId];
        $filterSql = "";

        if ($tab === 'pending') {
            $filterSql = " AND qr.status IN ('pending', 'vendor_reviewing') AND (q.status IS NULL OR q.status = 'draft')";
        } elseif ($tab === 'responded') {
            $filterSql = " AND qr.status = 'quoted' AND q.status = 'sent'";
        } elseif ($tab === 'past') {
            $filterSql = " AND (qr.status IN ('accepted', 'cancelled', 'declined') OR q.status IN ('accepted', 'declined', 'expired'))";
        }
        
        $countSql .= $filterSql;
        $sql .= $filterSql;

        $sql .= " ORDER BY qr.created_at DESC LIMIT " . intval($limit) . " OFFSET " . intval($offset);

        // Run Count
        $totalStmt = $db->prepare($countSql);
        $totalStmt->execute($params);
        $total = (int) $totalStmt->fetchColumn();

        // Run Select
        $stmt = $db->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll();

        $requests = [];
        foreach ($rows as $row) {
            // Fetch specialties
            $stmtSpec = $db->prepare("
                SELECT s.name_en 
                FROM quotation_request_specialties qrs
                JOIN specialties s ON qrs.specialty_id = s.id
                WHERE qrs.quotation_request_id = ?
            ");
            $stmtSpec->execute([$row['id']]);
            $row['specialties'] = $stmtSpec->fetchAll(PDO::FETCH_COLUMN) ?: [];

            // Fetch skills
            $stmtSkill = $db->prepare("
                SELECT s.name_en 
                FROM quotation_request_skills qrs
                JOIN skills s ON qrs.skill_id = s.id
                WHERE qrs.quotation_request_id = ?
            ");
            $stmtSkill->execute([$row['id']]);
            $row['skills'] = $stmtSkill->fetchAll(PDO::FETCH_COLUMN) ?: [];

            $row['duration_label'] = $row['duration_months'] ? "{$row['duration_months']} Months" : "Not specified";
            $row['start_date_formatted'] = $row['start_date'] ? date('D, M d', strtotime($row['start_date'])) : "Not specified";
            
            $timeDiff = time() - strtotime($row['created_at']);
            if ($timeDiff < 60) {
                $row['time_ago'] = "Just now";
            } elseif ($timeDiff < 3600) {
                $row['time_ago'] = round($timeDiff / 60) . " mins ago";
            } elseif ($timeDiff < 86400) {
                $row['time_ago'] = round($timeDiff / 3600) . " hours ago";
            } else {
                $row['time_ago'] = round($timeDiff / 86400) . " days ago";
            }

            $row['is_urgent'] = $row['status'] === 'pending' && $timeDiff > 14400; // >4 hours

            $requests[] = $row;
        }

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Quotation requests loaded', ['requests' => $requests], $pagination);
    }

    public function contracts()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        $totalStmt = $db->prepare("SELECT COUNT(*) FROM contracts WHERE vendor_id = ?");
        $totalStmt->execute([$vendorId]);
        $total = (int) $totalStmt->fetchColumn();

        $stmt = $db->prepare("
            SELECT con.*, c.full_name as customer_name, m.full_name as maid_name
            FROM contracts con
            JOIN customers c ON con.customer_id = c.id
            LEFT JOIN maids m ON con.maid_id = m.id
            WHERE con.vendor_id = ?
            ORDER BY con.created_at DESC
            LIMIT " . intval($limit) . " OFFSET " . intval($offset) . "
        ");
        $stmt->execute([$vendorId]);
        $contracts = $stmt->fetchAll();

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Contracts loaded', ['contracts' => $contracts], $pagination);
    }

    public function payments()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        // Check if vendor has payments, if not seed them
        $checkStmt = $db->prepare("SELECT COUNT(*) FROM payments WHERE payer_type = 'vendor' AND payer_id = ?");
        $checkStmt->execute([$vendorId]);
        $hasPayments = (int) $checkStmt->fetchColumn();

        if ($hasPayments === 0) {
            // Find active subscription id
            $subStmt = $db->prepare("SELECT id FROM vendor_subscriptions WHERE vendor_id = ? LIMIT 1");
            $subStmt->execute([$vendorId]);
            $subId = $subStmt->fetchColumn() ?: 1;

            $mockPayments = [
                ['created_at' => '2024-11-15 10:00:00', 'amount' => 599.00, 'reference' => 'TXN-SUB-98124'],
                ['created_at' => '2024-10-15 10:00:00', 'amount' => 599.00, 'reference' => 'TXN-SUB-97103'],
                ['created_at' => '2024-09-15 10:00:00', 'amount' => 599.00, 'reference' => 'TXN-SUB-96024']
            ];

            foreach ($mockPayments as $p) {
                $db->prepare("
                    INSERT INTO payments (payer_type, payer_id, related_type, related_id, amount, currency, payment_method, transaction_reference, status, paid_at, created_at)
                    VALUES ('vendor', ?, 'subscription', ?, ?, 'AED', 'Mastercard', ?, 'paid', ?, ?)
                ")->execute([
                    $vendorId,
                    $subId,
                    $p['amount'],
                    $p['reference'],
                    $p['created_at'],
                    $p['created_at']
                ]);
            }
        }

        $totalStmt = $db->prepare("SELECT COUNT(*) FROM payments WHERE payer_type = 'vendor' AND payer_id = ?");
        $totalStmt->execute([$vendorId]);
        $total = (int) $totalStmt->fetchColumn();

        $stmt = $db->prepare("
            SELECT p.*
            FROM payments p
            WHERE p.payer_type = 'vendor' AND p.payer_id = ?
            ORDER BY p.created_at DESC
            LIMIT " . intval($limit) . " OFFSET " . intval($offset) . "
        ");
        $stmt->execute([$vendorId]);
        $payments = $stmt->fetchAll();

        // Format dates and status
        foreach ($payments as &$p) {
            $p['formatted_date'] = date('M d, Y', strtotime($p['created_at']));
            $p['download_url'] = '/api/vendor/business/payments/' . $p['id'] . '/invoice';
        }

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Payments loaded', ['payments' => $payments], $pagination);
    }

    public function updateRequestStatus($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;
        
        $errors = validateRequired($data, ['status']);
        if (!empty($errors)) {
            sendError('Validation error', $errors, 422);
        }

        $allowed = ['vendor_reviewing', 'declined'];
        if (!in_array($data['status'], $allowed)) {
            sendError('Invalid status', ['allowed' => $allowed], 422);
        }

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM quotation_requests WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Quotation request not found', [], 404);
        }

        $declineReason = $data['decline_reason'] ?? null;
        if ($data['status'] === 'declined') {
            if (empty($declineReason)) {
                sendError('Validation error', ['decline_reason' => 'Decline reason is required when status is declined.'], 422);
            }
            
            $declineMessage = $data['decline_message'] ?? null;
            if (!empty($declineMessage)) {
                $declineReason = $declineReason . " - " . $declineMessage;
            }
        }

        $db->prepare("UPDATE quotation_requests SET status = ?, decline_reason = ?, updated_at = NOW() WHERE id = ?")
            ->execute([$data['status'], $declineReason, $id]);

        sendResponse([], 'Quotation request status updated.');
    }

    public function listQuotations()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        $totalStmt = $db->prepare("SELECT COUNT(*) FROM quotations WHERE vendor_id = ?");
        $totalStmt->execute([$vendorId]);
        $total = (int) $totalStmt->fetchColumn();

        $stmt = $db->prepare("
            SELECT q.*, c.full_name as customer_name, m.full_name as maid_name
            FROM quotations q
            JOIN customers c ON q.customer_id = c.id
            LEFT JOIN maids m ON q.maid_id = m.id
            WHERE q.vendor_id = ?
            ORDER BY q.created_at DESC
            LIMIT " . intval($limit) . " OFFSET " . intval($offset) . "
        ");
        $stmt->execute([$vendorId]);
        $quotations = $stmt->fetchAll();

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Quotations loaded successfully.', ['quotations' => $quotations], $pagination);
    }

    public function createQuotation()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;
        
        $required = ['quotation_request_id', 'customer_id', 'base_salary', 'visa_processing', 'health_insurance', 'service_fee'];
        $errors = validateRequired($data, $required);
        if (!empty($errors)) {
            sendError('Validation error', $errors, 422);
        }

        $total = (float) $data['base_salary'] + (float) $data['visa_processing'] + (float) $data['health_insurance'] + (float) $data['service_fee'];

        $db = Database::getInstance();

        // Check if a draft quotation already exists for this request and vendor
        $stmtExist = $db->prepare("SELECT id FROM quotations WHERE quotation_request_id = ? AND vendor_id = ? AND status = 'draft' LIMIT 1");
        $stmtExist->execute([$data['quotation_request_id'], $vendorId]);
        $existingId = $stmtExist->fetchColumn();

        if ($existingId) {
            $quotationId = $existingId;
            $stmt = $db->prepare("
                UPDATE quotations 
                SET customer_id = ?, maid_id = ?, base_salary = ?, visa_processing = ?, health_insurance = ?, service_fee = ?, total_amount = ?, notes = ?, valid_until = ?, updated_at = NOW()
                WHERE id = ?
            ");
            $stmt->execute([
                $data['customer_id'],
                $data['maid_id'] ?? null,
                $data['base_salary'],
                $data['visa_processing'],
                $data['health_insurance'],
                $data['service_fee'],
                $total,
                $data['notes'] ?? '',
                $data['valid_until'] ?? null,
                $quotationId
            ]);
            
            // Delete old inclusions
            $db->prepare("DELETE FROM quotation_inclusions WHERE quotation_id = ?")->execute([$quotationId]);
        } else {
            $stmt = $db->prepare("
                INSERT INTO quotations (quotation_request_id, vendor_id, customer_id, maid_id, base_salary, visa_processing, health_insurance, service_fee, total_amount, currency, notes, valid_until, status, created_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'AED', ?, ?, 'draft', NOW())
            ");
            $stmt->execute([
                $data['quotation_request_id'],
                $vendorId,
                $data['customer_id'],
                $data['maid_id'] ?? null,
                $data['base_salary'],
                $data['visa_processing'],
                $data['health_insurance'],
                $data['service_fee'],
                $total,
                $data['notes'] ?? '',
                $data['valid_until'] ?? null
            ]);
            $quotationId = $db->lastInsertId();
        }

        // Save inclusions
        $backgroundChecks = isset($data['background_checks']) ? (bool)$data['background_checks'] : true;
        $medicalCoverage = isset($data['medical_coverage']) ? (bool)$data['medical_coverage'] : true;
        $replacementGuarantee = isset($data['replacement_guarantee']) ? (bool)$data['replacement_guarantee'] : false;

        $inclusions = [
            [
                'title' => 'Background Checks',
                'description' => 'Full criminal and professional history verified before placement.',
                'is_included' => $backgroundChecks ? 1 : 0
            ],
            [
                'title' => 'Medical Coverage',
                'description' => 'Premium inpatient and outpatient insurance compliant with UAE law.',
                'is_included' => $medicalCoverage ? 1 : 0
            ],
            [
                'title' => 'Replacement Guarantee',
                'description' => 'Free replacement within the first 90 days if requirements are not met.',
                'is_included' => $replacementGuarantee ? 1 : 0
            ]
        ];

        foreach ($inclusions as $inc) {
            $stmtInc = $db->prepare("
                INSERT INTO quotation_inclusions (quotation_id, title, description, is_included)
                VALUES (?, ?, ?, ?)
            ");
            $stmtInc->execute([
                $quotationId,
                $inc['title'],
                $inc['description'],
                $inc['is_included']
            ]);
        }

        // Auto transition status to vendor_reviewing if it is currently pending
        $db->prepare("UPDATE quotation_requests SET status = 'vendor_reviewing' WHERE id = ? AND status = 'pending'")
            ->execute([$data['quotation_request_id']]);

        sendResponse([
            'id' => (int)$quotationId,
            'quotation_request_id' => (int)$data['quotation_request_id'],
            'total_amount' => $total,
            'currency' => 'AED',
            'status' => 'draft'
        ], 'Quotation created as draft.', 201);
    }

    public function showQuotationRequest($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("
            SELECT qr.*, 
                   c.full_name as customer_name, c.email as customer_email, c.phone as customer_phone, c.profile_image as customer_image, c.created_at as customer_created_at,
                   ct.name_en as customer_city,
                   m.full_name as maid_name, m.profile_image as maid_image,
                   sm.name_en as service_model_name,
                   at.name_en as accommodation_type_name,
                   pn.name_en as preferred_nationality_name
            FROM quotation_requests qr
            JOIN customers c ON qr.customer_id = c.id
            LEFT JOIN cities ct ON c.city_id = ct.id
            LEFT JOIN maids m ON qr.maid_id = m.id
            LEFT JOIN service_models sm ON qr.service_model_id = sm.id
            LEFT JOIN accommodation_types at ON qr.accommodation_type_id = at.id
            LEFT JOIN nationalities pn ON qr.preferred_nationality_id = pn.id
            WHERE qr.id = ? AND qr.vendor_id = ?
            LIMIT 1
        ");
        $stmt->execute([$id, $vendorId]);
        $row = $stmt->fetch();

        if (!$row) {
            sendError('Quotation request not found.', [], 404);
        }

        // Fetch specialties
        $stmtSpec = $db->prepare("
            SELECT s.name_en 
            FROM quotation_request_specialties qrs
            JOIN specialties s ON qrs.specialty_id = s.id
            WHERE qrs.quotation_request_id = ?
        ");
        $stmtSpec->execute([$id]);
        $row['specialties'] = $stmtSpec->fetchAll(PDO::FETCH_COLUMN) ?: [];

        // Fetch skills
        $stmtSkill = $db->prepare("
            SELECT s.name_en 
            FROM quotation_request_skills qrs
            JOIN skills s ON qrs.skill_id = s.id
            WHERE qrs.quotation_request_id = ?
        ");
        $stmtSkill->execute([$id]);
        $row['skills'] = $stmtSkill->fetchAll(PDO::FETCH_COLUMN) ?: [];

        $row['duration_label'] = $row['duration_months'] ? "{$row['duration_months']} Months" : "Not specified";
        $row['start_date_formatted'] = $row['start_date'] ? date('D, M d', strtotime($row['start_date'])) : "Not specified";
        $row['customer_member_since'] = $row['customer_created_at'] ? "Member since " . date('Y', strtotime($row['customer_created_at'])) : "Member since 2022";

        // Join quotation if exists
        $stmtQuote = $db->prepare("
            SELECT q.* 
            FROM quotations q
            WHERE q.quotation_request_id = ? AND q.vendor_id = ?
            ORDER BY q.id DESC
            LIMIT 1
        ");
        $stmtQuote->execute([$id, $vendorId]);
        $quotation = $stmtQuote->fetch();

        if ($quotation) {
            // Inclusions
            $stmtInc = $db->prepare("SELECT title, description, is_included FROM quotation_inclusions WHERE quotation_id = ?");
            $stmtInc->execute([$quotation['id']]);
            $inclusions = $stmtInc->fetchAll();
            
            // Format inclusion booleans
            foreach ($inclusions as &$inc) {
                $inc['is_included'] = (bool)$inc['is_included'];
            }
            
            $quotation['inclusions'] = $inclusions;
            $row['quotation'] = $quotation;
        } else {
            $row['quotation'] = null;
        }

        sendResponse($row, 'Quotation request loaded successfully.');
    }

    public function showQuotation($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("
            SELECT q.*,
                   c.full_name as customer_name, c.email as customer_email, c.phone as customer_phone, c.profile_image as customer_image,
                   m.full_name as maid_name, m.profile_image as maid_image,
                   qr.start_date, qr.duration_months, qr.number_of_helpers, qr.request_type,
                   sm.name_en as service_model_name,
                   at.name_en as accommodation_type_name
            FROM quotations q
            JOIN customers c ON q.customer_id = c.id
            LEFT JOIN maids m ON q.maid_id = m.id
            LEFT JOIN quotation_requests qr ON q.quotation_request_id = qr.id
            LEFT JOIN service_models sm ON qr.service_model_id = sm.id
            LEFT JOIN accommodation_types at ON qr.accommodation_type_id = at.id
            WHERE q.id = ? AND q.vendor_id = ?
            LIMIT 1
        ");
        $stmt->execute([$id, $vendorId]);
        $q = $stmt->fetch();

        if (!$q) {
            sendError('Quotation not found.', [], 404);
        }

        // Fetch inclusions
        $stmtInc = $db->prepare("SELECT title, description, is_included FROM quotation_inclusions WHERE quotation_id = ?");
        $stmtInc->execute([$id]);
        $inclusions = $stmtInc->fetchAll();

        foreach ($inclusions as &$inc) {
            $inc['is_included'] = (bool)$inc['is_included'];
        }
        $q['inclusions'] = $inclusions;

        sendResponse($q, 'Quotation loaded successfully.');
    }

    public function sendQuotation($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id, quotation_request_id FROM quotations WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        $quotation = $stmt->fetch();
        if (!$quotation) {
            sendError('Quotation not found', [], 404);
        }

        $db->prepare("UPDATE quotations SET status = 'sent', sent_at = NOW() WHERE id = ?")->execute([$id]);

        $db->prepare("UPDATE quotation_requests SET status = 'quoted', updated_at = NOW() WHERE id = ?")
            ->execute([$quotation['quotation_request_id']]);

        sendResponse([], 'Quotation sent successfully.');
    }

    public function cancelQuotation($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM quotations WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Quotation not found', [], 404);
        }

        $db->prepare("UPDATE quotations SET status = 'declined', updated_at = NOW() WHERE id = ?")->execute([$id]);
        sendResponse([], 'Quotation cancelled successfully.');
    }

    public function updateContractStatus($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        $errors = validateRequired($data, ['status']);
        if (!empty($errors)) {
            sendError('Validation error', $errors, 422);
        }

        $allowed = ['approved', 'active', 'completed', 'cancelled', 'rejected'];
        if (!in_array($data['status'], $allowed)) {
            sendError('Invalid status', ['allowed' => $allowed], 422);
        }

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM contracts WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Contract not found', [], 404);
        }

        $updateSql = "UPDATE contracts SET status = ?, updated_at = NOW()";
        $params = [$data['status']];

        if ($data['status'] === 'approved') {
            $updateSql .= ", signed_by_vendor_at = NOW()";
        }

        $updateSql .= " WHERE id = ?";
        $params[] = $id;

        $db->prepare($updateSql)->execute($params);

        sendResponse([], 'Contract status updated successfully.');
    }

    public function subscription()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        // 1. Check if vendor has an active subscription in database
        $stmt = $db->prepare("
            SELECT vs.id, vs.plan_id, vs.start_date, vs.end_date, vs.status
            FROM vendor_subscriptions vs
            WHERE vs.vendor_id = ? AND vs.status = 'active'
            ORDER BY vs.end_date DESC LIMIT 1
        ");
        $stmt->execute([$vendorId]);
        $subDb = $stmt->fetch();

        if (!$subDb) {
            // Find Professional plan
            $planStmt = $db->prepare("SELECT id FROM subscription_plans WHERE name_en = 'Professional' LIMIT 1");
            $planStmt->execute();
            $planId = $planStmt->fetchColumn();
            if (!$planId) {
                // Try any plan
                $planId = $db->query("SELECT id FROM subscription_plans LIMIT 1")->fetchColumn();
            }

            if ($planId) {
                $startDate = date('Y-m-15');
                $endDate = date('Y-m-15', strtotime('+1 month'));

                $insertStmt = $db->prepare("
                    INSERT INTO vendor_subscriptions (vendor_id, plan_id, start_date, end_date, auto_renew, status, created_at)
                    VALUES (?, ?, ?, ?, 1, 'active', NOW())
                ");
                $insertStmt->execute([$vendorId, $planId, $startDate, $endDate]);
            }
        } else {
            // If the database subscription has expired, update its dates directly in the database to be current
            if (strtotime($subDb['end_date']) < time()) {
                $day = date('d', strtotime($subDb['end_date']));
                $currentDay = (int) date('d');
                if ($currentDay < (int) $day) {
                    $newEndDate = date("Y-m-$day");
                    $newStartDate = date("Y-m-$day", strtotime("-1 month"));
                } else {
                    $newEndDate = date("Y-m-$day", strtotime("+1 month"));
                    $newStartDate = date("Y-m-$day");
                }

                $updateStmt = $db->prepare("
                    UPDATE vendor_subscriptions 
                    SET start_date = ?, end_date = ? 
                    WHERE id = ?
                ");
                $updateStmt->execute([$newStartDate, $newEndDate, $subDb['id']]);
            }
        }

        // 2. Fetch the updated subscription from database
        $stmt = $db->prepare("
            SELECT vs.id, vs.plan_id, vs.start_date, vs.end_date, vs.auto_renew, vs.status,
                   sp.name_en as plan_name, sp.max_maids, sp.interview_requests_limit, sp.ads_quota, sp.price, sp.currency, sp.billing_cycle
            FROM vendor_subscriptions vs
            JOIN subscription_plans sp ON vs.plan_id = sp.id
            WHERE vs.vendor_id = ? AND vs.status = 'active'
            ORDER BY vs.end_date DESC LIMIT 1
        ");
        $stmt->execute([$vendorId]);
        $sub = $stmt->fetch();

        if (!$sub) {
            sendError('Subscription not found.', 404);
        }

        $sub['renews_on_formatted'] = date('M d, Y', strtotime($sub['end_date']));
        $sub['auto_renew'] = (bool)$sub['auto_renew'];

        // Fetch default payment method details for the UI toggle display
        $cardStmt = $db->prepare("
            SELECT card_brand, card_last_four 
            FROM vendor_payment_methods 
            WHERE vendor_id = ? AND is_default = 1 
            LIMIT 1
        ");
        $cardStmt->execute([$vendorId]);
        $defaultCard = $cardStmt->fetch();
        $sub['default_card'] = $defaultCard ? [
            'card_brand' => $defaultCard['card_brand'],
            'card_last_four' => $defaultCard['card_last_four']
        ] : null;

        // Fetch all available subscription plans for the Upgrade Plan option
        $plansStmt = $db->prepare("SELECT * FROM subscription_plans WHERE status = 'active'");
        $plansStmt->execute();
        $availablePlans = $plansStmt->fetchAll();

        sendResponse([
            'subscription' => $sub,
            'available_plans' => $availablePlans
        ], 'Active subscription loaded.');
    }

    public function quota()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        // Ensure active subscription exists in database first
        $stmt = $db->prepare("
            SELECT vs.id, vs.plan_id, vs.start_date, vs.end_date, vs.status
            FROM vendor_subscriptions vs
            WHERE vs.vendor_id = ? AND vs.status = 'active'
            ORDER BY vs.end_date DESC LIMIT 1
        ");
        $stmt->execute([$vendorId]);
        $subDb = $stmt->fetch();

        if (!$subDb) {
            // Find Professional plan
            $planStmt = $db->prepare("SELECT id FROM subscription_plans WHERE name_en = 'Professional' LIMIT 1");
            $planStmt->execute();
            $planId = $planStmt->fetchColumn();
            if (!$planId) {
                $planId = $db->query("SELECT id FROM subscription_plans LIMIT 1")->fetchColumn();
            }
            if ($planId) {
                $startDate = date('Y-m-15');
                $endDate = date('Y-m-15', strtotime('+1 month'));

                $insertStmt = $db->prepare("
                    INSERT INTO vendor_subscriptions (vendor_id, plan_id, start_date, end_date, auto_renew, status, created_at)
                    VALUES (?, ?, ?, ?, 1, 'active', NOW())
                ");
                $insertStmt->execute([$vendorId, $planId, $startDate, $endDate]);
            }
        } else {
            // Update dates if expired
            if (strtotime($subDb['end_date']) < time()) {
                $day = date('d', strtotime($subDb['end_date']));
                $currentDay = (int) date('d');
                if ($currentDay < (int) $day) {
                    $newEndDate = date("Y-m-$day");
                    $newStartDate = date("Y-m-$day", strtotime("-1 month"));
                } else {
                    $newEndDate = date("Y-m-$day", strtotime("+1 month"));
                    $newStartDate = date("Y-m-$day");
                }

                $updateStmt = $db->prepare("
                    UPDATE vendor_subscriptions 
                    SET start_date = ?, end_date = ? 
                    WHERE id = ?
                ");
                $updateStmt->execute([$newStartDate, $newEndDate, $subDb['id']]);
            }
        }

        // 1. Fetch active subscription to get ads quota
        $stmt = $db->prepare("
            SELECT sp.ads_quota
            FROM vendor_subscriptions vs
            JOIN subscription_plans sp ON vs.plan_id = sp.id
            WHERE vs.vendor_id = ? AND vs.status = 'active'
            ORDER BY vs.end_date DESC LIMIT 1
        ");
        $stmt->execute([$vendorId]);
        $sub = $stmt->fetch();

        $totalQuota = $sub ? (int) $sub['ads_quota'] : 0;

        // 2. Fetch used quota (active ads count) from vendor_ads
        $stmtUsed = $db->prepare("
            SELECT COUNT(*) FROM vendor_ads 
            WHERE vendor_id = ? AND status = 'active'
        ");
        $stmtUsed->execute([$vendorId]);
        $usedCount = (int) $stmtUsed->fetchColumn();

        $remainingQuota = max(0, $totalQuota - $usedCount);
        $limitReachPercentage = $totalQuota > 0 ? round(($usedCount / $totalQuota) * 100) : 0;

        $quotaData = [
            'ad_quota_total' => $totalQuota,
            'ad_quota_used' => $usedCount,
            'ad_quota_remaining' => $remainingQuota,
            'limit_reach_percentage' => (int) $limitReachPercentage,
        ];
        sendResponse(['quota' => $quotaData], 'Quota usage loaded successfully.');
    }

    public function cancelSubscription()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $db->prepare("UPDATE vendor_subscriptions SET status = 'cancelled' WHERE vendor_id = ? AND status = 'active'")
            ->execute([$vendorId]);

        sendResponse([], 'Subscription cancelled successfully.');
    }

    public function toggleAutoRenew()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;

        $autoRenew = isset($data['auto_renew']) ? ($data['auto_renew'] ? 1 : 0) : 1;

        $db = Database::getInstance();
        $db->prepare("
            UPDATE vendor_subscriptions 
            SET auto_renew = ? 
            WHERE vendor_id = ? AND status = 'active'
        ")->execute([$autoRenew, $vendorId]);

        sendResponse(['auto_renew' => (bool)$autoRenew], 'Auto-renewal preference updated.');
    }

    public function getPaymentMethods()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        // Ensure at least one seeded card if none exist
        $checkStmt = $db->prepare("SELECT COUNT(*) FROM vendor_payment_methods WHERE vendor_id = ?");
        $checkStmt->execute([$vendorId]);
        $count = (int) $checkStmt->fetchColumn();

        if ($count === 0) {
            $pmId = $db->query("SELECT id FROM payment_methods WHERE type = 'card' LIMIT 1")->fetchColumn();
            if ($pmId) {
                $db->prepare("
                    INSERT INTO vendor_payment_methods (vendor_id, payment_method_id, card_last_four, card_brand, expiry_month, expiry_year, is_default)
                    VALUES (?, ?, '4242', 'Mastercard', '12', '26', 1)
                ")->execute([$vendorId, $pmId]);
            }
        }

        $totalStmt = $db->prepare("
            SELECT COUNT(*)
            FROM vendor_payment_methods vpm
            JOIN payment_methods pm ON vpm.payment_method_id = pm.id
            WHERE vpm.vendor_id = ?
        ");
        $totalStmt->execute([$vendorId]);
        $total = (int) $totalStmt->fetchColumn();

        $stmt = $db->prepare("
            SELECT vpm.*, pm.name as method_name
            FROM vendor_payment_methods vpm
            JOIN payment_methods pm ON vpm.payment_method_id = pm.id
            WHERE vpm.vendor_id = ?
            ORDER BY vpm.is_default DESC, vpm.id DESC
            LIMIT " . intval($limit) . " OFFSET " . intval($offset) . "
        ");
        $stmt->execute([$vendorId]);
        $cards = $stmt->fetchAll();

        foreach ($cards as &$card) {
            $card['is_default'] = (bool)$card['is_default'];
        }

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Saved payment methods loaded.', ['cards' => $cards], $pagination);
    }

    public function addPaymentMethod()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;

        $db = Database::getInstance();

        $paymentMethodId = $data['payment_method_id'] ?? null;
        if (!$paymentMethodId) {
            $pmId = $db->query("SELECT id FROM payment_methods WHERE type = 'card' LIMIT 1")->fetchColumn();
            $paymentMethodId = $pmId ?: 1;
        }

        $cardLastFour = $data['card_last_four'] ?? null;
        $cardBrand = $data['card_brand'] ?? 'Mastercard';
        $expiryMonth = $data['expiry_month'] ?? null;
        $expiryYear = $data['expiry_year'] ?? null;

        if (isset($data['card_number']) && !empty($data['card_number'])) {
            $cleanCardNum = preg_replace('/\s+/', '', $data['card_number']);
            $cardLastFour = substr($cleanCardNum, -4);
            if (strpos($cleanCardNum, '4') === 0) {
                $cardBrand = 'Visa';
            } elseif (strpos($cleanCardNum, '5') === 0) {
                $cardBrand = 'Mastercard';
            }
        }

        if (isset($data['expiry']) && !empty($data['expiry'])) {
            $parts = explode('/', $data['expiry']);
            $expiryMonth = trim($parts[0] ?? '12');
            $expiryYear = trim($parts[1] ?? '26');
        }

        // Fallbacks
        if (!$cardLastFour) $cardLastFour = '4242';
        if (!$expiryMonth) $expiryMonth = '12';
        if (!$expiryYear) $expiryYear = '26';

        $isDefault = isset($data['is_default']) ? ($data['is_default'] ? 1 : 0) : 0;
        if ($isDefault) {
            $db->prepare("UPDATE vendor_payment_methods SET is_default = 0 WHERE vendor_id = ?")->execute([$vendorId]);
        }

        $stmt = $db->prepare("
            INSERT INTO vendor_payment_methods (vendor_id, payment_method_id, card_last_four, card_brand, expiry_month, expiry_year, is_default)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $vendorId,
            $paymentMethodId,
            $cardLastFour,
            $cardBrand,
            $expiryMonth,
            $expiryYear,
            $isDefault
        ]);

        sendResponse([], 'Payment method saved successfully.', 201);
    }

    public function deletePaymentMethod($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM vendor_payment_methods WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Payment method not found', [], 404);
        }

        $db->prepare("DELETE FROM vendor_payment_methods WHERE id = ? AND vendor_id = ?")->execute([$id, $vendorId]);
        sendResponse([], 'Payment method deleted successfully.');
    }

    public function downloadInvoice($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("
            SELECT p.*, vs.plan_id, sp.name_en as plan_name 
            FROM payments p
            LEFT JOIN vendor_subscriptions vs ON p.related_type = 'subscription' AND p.related_id = vs.id
            LEFT JOIN subscription_plans sp ON vs.plan_id = sp.id
            WHERE p.id = ? AND p.payer_type = 'vendor' AND p.payer_id = ?
            LIMIT 1
        ");
        $stmt->execute([$id, $vendorId]);
        $payment = $stmt->fetch();

        if (!$payment) {
            sendError('Invoice not found', [], 404);
        }

        // Output clean HTML or text simulating an invoice
        header('Content-Type: text/plain');
        header('Content-Disposition: attachment; filename="Invoice_' . $payment['id'] . '.txt"');
        
        echo "=========================================\n";
        echo "              HELPERLINK INVOICE         \n";
        echo "=========================================\n";
        echo "Invoice ID: " . $payment['id'] . "\n";
        echo "Date: " . date('M d, Y', strtotime($payment['created_at'])) . "\n";
        echo "Payer: Vendor #" . $payment['payer_id'] . "\n";
        echo "Status: " . strtoupper($payment['status']) . "\n";
        echo "-----------------------------------------\n";
        echo "Description: Subscription - " . ($payment['plan_name'] ?: 'Professional') . "\n";
        echo "Amount: " . $payment['currency'] . " " . number_format($payment['amount'], 2) . "\n";
        echo "=========================================\n";
        echo "Thank you for your business!\n";
        exit;
    }

    public function getBillingAddresses()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();

        $page = isset($_GET['page']) ? max(1, intval($_GET['page'])) : 1;
        $limit = isset($_GET['limit']) ? max(1, intval($_GET['limit'])) : 10;
        $offset = ($page - 1) * $limit;

        // Ensure at least one seeded billing address if none exist
        $checkStmt = $db->prepare("SELECT COUNT(*) FROM vendor_billing_addresses WHERE vendor_id = ?");
        $checkStmt->execute([$vendorId]);
        $count = (int) $checkStmt->fetchColumn();

        if ($count === 0) {
            $vendorStmt = $db->prepare("
                SELECT agency_name, address, (SELECT name_en FROM cities WHERE id = city_id) as city_name 
                FROM vendors WHERE id = ?
            ");
            $vendorStmt->execute([$vendorId]);
            $v = $vendorStmt->fetch();
            if ($v) {
                $insertStmt = $db->prepare("
                    INSERT INTO vendor_billing_addresses (vendor_id, address_label, name_or_company, country_region, street_address, city, is_default)
                    VALUES (?, 'Office', ?, 'United Arab Emirates', ?, ?, 1)
                ");
                $insertStmt->execute([
                    $vendorId,
                    $v['agency_name'],
                    $v['address'] ?: 'Office 402, Business Bay',
                    $v['city_name'] ?: 'Dubai'
                ]);
            }
        }

        $totalStmt = $db->prepare("SELECT COUNT(*) FROM vendor_billing_addresses WHERE vendor_id = ?");
        $totalStmt->execute([$vendorId]);
        $total = (int) $totalStmt->fetchColumn();

        $stmt = $db->prepare("
            SELECT * FROM vendor_billing_addresses 
            WHERE vendor_id = ? 
            ORDER BY is_default DESC, id DESC
            LIMIT " . intval($limit) . " OFFSET " . intval($offset) . "
        ");
        $stmt->execute([$vendorId]);
        $addresses = $stmt->fetchAll();

        // Format is_default as boolean
        foreach ($addresses as &$addr) {
            $addr['is_default'] = (bool)$addr['is_default'];
        }

        $pagination = [
            'total' => $total,
            'page' => $page,
            'limit' => $limit,
            'total_pages' => ceil($total / $limit)
        ];

        jsonResponse(true, 'Billing addresses loaded.', ['addresses' => $addresses], $pagination);
    }

    public function addBillingAddress()
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;

        $required = ['name_or_company', 'country_region', 'street_address', 'city'];
        $errors = validateRequired($data, $required);
        if (!empty($errors)) {
            sendError('Validation error', $errors, 422);
        }

        $db = Database::getInstance();

        $isDefault = isset($data['is_default']) ? ($data['is_default'] ? 1 : 0) : 0;
        if ($isDefault) {
            // Reset other defaults
            $db->prepare("UPDATE vendor_billing_addresses SET is_default = 0 WHERE vendor_id = ?")->execute([$vendorId]);
        }

        $stmt = $db->prepare("
            INSERT INTO vendor_billing_addresses (vendor_id, address_label, name_or_company, country_region, street_address, apt_suite_office, city, postal_zip, is_default)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            $vendorId,
            $data['address_label'] ?? 'Office',
            $data['name_or_company'],
            $data['country_region'],
            $data['street_address'],
            $data['apt_suite_office'] ?? null,
            $data['city'],
            $data['postal_zip'] ?? null,
            $isDefault
        ]);

        sendResponse([], 'Billing address added successfully.', 201);
    }

    public function updateBillingAddress($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $data = json_decode(file_get_contents('php://input'), true);
        if (!$data) $data = $_POST;

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM vendor_billing_addresses WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Billing address not found', [], 404);
        }

        $isDefault = isset($data['is_default']) ? ($data['is_default'] ? 1 : 0) : 0;
        if ($isDefault) {
            $db->prepare("UPDATE vendor_billing_addresses SET is_default = 0 WHERE vendor_id = ?")->execute([$vendorId]);
        }

        $stmtUpdate = $db->prepare("
            UPDATE vendor_billing_addresses 
            SET address_label = ?, name_or_company = ?, country_region = ?, street_address = ?, apt_suite_office = ?, city = ?, postal_zip = ?, is_default = ?
            WHERE id = ? AND vendor_id = ?
        ");
        $stmtUpdate->execute([
            $data['address_label'] ?? 'Office',
            $data['name_or_company'] ?? '',
            $data['country_region'] ?? '',
            $data['street_address'] ?? '',
            $data['apt_suite_office'] ?? null,
            $data['city'] ?? '',
            $data['postal_zip'] ?? null,
            $isDefault,
            $id,
            $vendorId
        ]);

        sendResponse([], 'Billing address updated successfully.');
    }

    public function deleteBillingAddress($id)
    {
        $payload = VendorAuthMiddleware::authenticate();
        $vendorId = $payload['id'];

        $db = Database::getInstance();
        $stmt = $db->prepare("SELECT id FROM vendor_billing_addresses WHERE id = ? AND vendor_id = ? LIMIT 1");
        $stmt->execute([$id, $vendorId]);
        if (!$stmt->fetchColumn()) {
            sendError('Billing address not found', [], 404);
        }

        $db->prepare("DELETE FROM vendor_billing_addresses WHERE id = ? AND vendor_id = ?")->execute([$id, $vendorId]);
        sendResponse([], 'Billing address deleted successfully.');
    }
}
