<?php
function getFinancialYear() {
    $month = date('m');
    $year  = date('y');
    return ($month >= 4)
        ? $year . '-' . ($year + 1)
        : ($year - 1) . '-' . $year;
}

// Derive prefix from company name: first letter of each word, uppercase, max 5 chars
// e.g. "Modern Sanitory & Hardware" → MSH, "Ajay Trader" → AT
function derivePrefix(string $name): string {
    $words  = preg_split('/\s+/', trim($name));
    $prefix = '';
    foreach ($words as $w) {
        if (strlen($w) > 1 && $w !== '&') {
            $prefix .= strtoupper($w[0]);
        }
    }
    return substr($prefix ?: 'INV', 0, 5);
}

function generateFinalInvoiceNo($client_id, $conn, $company_id = null) {

    // Resolve company_id
    if ($company_id === null) {
        $company_id = (int)($_SESSION['active_company_id'] ?? 0);
    }
    $company_id = (int)$company_id;

    // Get client GST type — determines B2B vs B2C invoice series
    $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';

    // Get company prefix
    $company = $conn->query("SELECT company_name, invoice_prefix FROM company_master WHERE id = $company_id LIMIT 1")->fetch_assoc();
    if (!$company) {
        throw new Exception("Company not found for invoice numbering");
    }
    $prefix = !empty($company['invoice_prefix'])
        ? $company['invoice_prefix']
        : derivePrefix($company['company_name']);

    $fy = getFinancialYear();

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

    if (!$seq) {
        // Auto-create. INSERT IGNORE avoids fatal error if a legacy row exists
        // with company_id=0 for the same prefix/type/fy (old schema had no company_id
        // in the UNIQUE KEY, so duplicates were possible).
        $conn->query("
            INSERT IGNORE INTO invoice_sequences (company_id, prefix, type, financial_year, current_no)
            VALUES ($company_id, '$prefix', '$type', '$fy', 0)
        ");

        $seq = $conn->query("
            SELECT * FROM invoice_sequences
            WHERE company_id = $company_id
              AND prefix = '$prefix'
              AND type = '$type'
              AND financial_year = '$fy'
            FOR UPDATE
        ")->fetch_assoc();
    }

    // Fallback: if the UNIQUE KEY still lacks company_id (old schema), the INSERT
    // above was silently ignored because a row with company_id=0 already occupies
    // the (prefix, type, fy) slot. In that case, use the shared row.
    if (!$seq) {
        $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 found for $prefix / $type / $fy. Please set it up in Settings → Invoice Sequences.");
    }

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

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

    return $prefix . '/' . $type . '/' . $fy . '/' . $nextNo;
}
