<?php
function getFinancialYear() {
    $month = date('m');
    $year = date('y');

    if ($month >= 4) {
        return $year . '-' . ($year + 1);
    } else {
        return ($year - 1) . '-' . $year;
    }
}

function generateFinalInvoiceNo($client_id, $conn) {

    // Get client GST
    $client = $conn->query("SELECT gstin FROM clients WHERE id = $client_id")->fetch_assoc();
    if (!$client) {
        throw new Exception("Client not found for invoice numbering");
    }

    $type = empty($client['gstin']) ? 'B2C' : 'B2B';
    $prefix = 'MSH'; // later you can move this to settings table if needed
    $fy = getFinancialYear();

    // Lock sequence row for update (important to avoid duplicate numbers)
    $seq = $conn->query("
        SELECT * FROM invoice_sequences
        WHERE prefix = '$prefix'
          AND type = '$type'
          AND financial_year = '$fy'
        FOR UPDATE
    ")->fetch_assoc();

    if (!$seq) {
        throw new Exception("Invoice sequence not defined for $prefix / $type / $fy");
    }

    $nextNo = $seq['current_no'] + 1;

    // Update sequence
    $conn->query("
        UPDATE invoice_sequences
        SET current_no = $nextNo
        WHERE id = {$seq['id']}
    ");

    // Final invoice format
    $invoice_no = $prefix . '/' . $type . '/' . $fy . '/' . $nextNo;

    return $invoice_no;
}
