<?php

require_once __DIR__ . '/db.php';

function billing_clean(string $value): string
{
    return trim($value);
}

function billing_upload_root(): string
{
    $dir = __DIR__ . '/../assets/uploads';
    if (!is_dir($dir)) {
        mkdir($dir, 0777, true);
    }
    return $dir;
}

function billing_store_uploaded_files(string $fieldName, string $subdir = 'customer_docs'): array
{
    if (!isset($_FILES[$fieldName]) || !is_array($_FILES[$fieldName])) {
        return [];
    }

    $upload = $_FILES[$fieldName];
    $names = $upload['name'] ?? [];
    $tmpNames = $upload['tmp_name'] ?? [];
    $errors = $upload['error'] ?? [];

    if (!is_array($names) || !is_array($tmpNames) || !is_array($errors)) {
        return [];
    }

    $root = billing_upload_root();
    $targetDir = $root . '/' . trim($subdir, '/');
    if (!is_dir($targetDir)) {
        mkdir($targetDir, 0777, true);
    }

    $saved = [];
    foreach ($names as $index => $originalName) {
        $errorCode = (int) ($errors[$index] ?? UPLOAD_ERR_NO_FILE);
        if ($errorCode !== UPLOAD_ERR_OK) {
            continue;
        }

        $tmpPath = (string) ($tmpNames[$index] ?? '');
        if ($tmpPath === '' || !is_uploaded_file($tmpPath)) {
            continue;
        }

        $baseName = preg_replace('/[^A-Za-z0-9._-]/', '-', basename((string) $originalName)) ?: 'upload';
        $filename = date('YmdHis') . '-' . bin2hex(random_bytes(4)) . '-' . $baseName;
        $fullPath = $targetDir . '/' . $filename;
        if (!move_uploaded_file($tmpPath, $fullPath)) {
            continue;
        }

        $saved[] = 'assets/uploads/' . trim($subdir, '/') . '/' . $filename;
    }

    return $saved;
}

function billing_escape(string $value): string
{
    return htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
}

function billing_base_path(): string
{
    $scriptFilename = realpath((string) ($_SERVER['SCRIPT_FILENAME'] ?? ''));
    $appRoot = realpath(dirname(__DIR__));
    $scriptName = str_replace('\\', '/', (string) ($_SERVER['SCRIPT_NAME'] ?? ''));

    if ($scriptName === '') {
        return '';
    }

    $levels = 1;
    if ($scriptFilename && $appRoot && dirname($scriptFilename) !== $appRoot) {
        $levels = 2;
    }

    $base = dirname($scriptName, $levels);
    $base = str_replace('\\', '/', $base);

    if ($base === '/' || $base === '\\' || $base === '.') {
        return '';
    }

    return rtrim($base, '/');
}

function billing_url(string $path = ''): string
{
    if ($path === '') {
        $base = billing_base_path();
        return $base !== '' ? $base : '/';
    }

    if (preg_match('#^(?:https?:)?//#i', $path)) {
        return $path;
    }

    if ($path[0] === '/') {
        return $path;
    }

    $base = billing_base_path();
    if ($base === '') {
        return '/' . ltrim($path, '/');
    }

    return $base . '/' . ltrim($path, '/');
}

function billing_base_href(): string
{
    $base = billing_url('');
    return rtrim($base, '/') . '/';
}

function billing_redirect(string $path): void
{
    header('Location: ' . billing_url($path));
    exit;
}

function billing_require_auth(): void
{
    if (empty($_SESSION['billing_user_id'])) {
        billing_redirect('signin.php');
    }

    billing_ensure_soft_delete_support();
    billing_ensure_sales_module_support();
}

function billing_flash(string $key, ?string $value = null): ?string
{
    if ($value !== null) {
        $_SESSION['billing_flash'][$key] = $value;
        return null;
    }

    if (!isset($_SESSION['billing_flash'][$key])) {
        return null;
    }

    $value = $_SESSION['billing_flash'][$key];
    unset($_SESSION['billing_flash'][$key]);

    return $value;
}

function billing_fetch_one(string $sql, string $types = '', array $params = []): ?array
{
    $conn = billing_db();
    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        return null;
    }

    if ($types !== '' && $params !== []) {
        $stmt->bind_param($types, ...$params);
    }

    if (!$stmt->execute()) {
        $stmt->close();
        return null;
    }

    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();
    return $row ?: null;
}

function billing_fetch_all(string $sql, string $types = '', array $params = []): array
{
    $conn = billing_db();
    $stmt = $conn->prepare($sql);
    if ($stmt === false) {
        return [];
    }

    if ($types !== '' && $params !== []) {
        $stmt->bind_param($types, ...$params);
    }

    if (!$stmt->execute()) {
        $stmt->close();
        return [];
    }

    $result = $stmt->get_result();
    $rows = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $rows[] = $row;
        }
    }

    $stmt->close();
    return $rows;
}

function billing_table_has_column(string $tableName, string $columnName): bool
{
    static $cache = [];

    $key = $tableName . '.' . $columnName;
    if (array_key_exists($key, $cache)) {
        return $cache[$key];
    }

    $conn = billing_db();
    $tableName = $conn->real_escape_string($tableName);
    $columnName = $conn->real_escape_string($columnName);
    $cache[$key] = billing_fetch_one("SHOW COLUMNS FROM `{$tableName}` LIKE '{$columnName}'") !== null;
    return $cache[$key];
}

function billing_ensure_soft_delete_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();
    $schema = $conn->real_escape_string(DB_NAME);
    $tables = billing_fetch_all(
        "SELECT TABLE_NAME
         FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = ?
           AND TABLE_TYPE = 'BASE TABLE'
           AND TABLE_NAME NOT IN ('deleted_logs', 'customer_delete_log')",
        's',
        [DB_NAME]
    );

    foreach ($tables as $row) {
        $tableName = (string) ($row['TABLE_NAME'] ?? '');
        if ($tableName === '') {
            continue;
        }

        if (!billing_table_has_column($tableName, 'is_deleted')) {
            $conn->query("ALTER TABLE `{$tableName}` ADD COLUMN `is_deleted` TINYINT(1) NOT NULL DEFAULT 0");
        }
        if (!billing_table_has_column($tableName, 'deleted_at')) {
            $conn->query("ALTER TABLE `{$tableName}` ADD COLUMN `deleted_at` DATETIME DEFAULT NULL");
        }
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS deleted_logs (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            table_name VARCHAR(190) NOT NULL,
            record_id BIGINT UNSIGNED NOT NULL,
            deleted_data JSON DEFAULT NULL,
            deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_deleted_logs_table_record (table_name, record_id),
            KEY idx_deleted_logs_deleted_at (deleted_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_log_deleted_record(string $tableName, int $recordId, array $record): bool
{
    billing_ensure_soft_delete_support();

    $payload = json_encode($record, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO deleted_logs (table_name, record_id, deleted_data, deleted_at)
         VALUES (?, ?, ?, CURRENT_TIMESTAMP)'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('sis', $tableName, $recordId, $payload);
    $saved = $stmt->execute();
    $stmt->close();
    return $saved;
}

function billing_soft_delete_record(
    string $tableName,
    string $idColumn,
    int $recordId,
    ?int $userId = null,
    ?int $organizationId = null,
    array $extraUpdates = []
): bool {
    billing_ensure_soft_delete_support();

    if ($recordId <= 0) {
        return false;
    }

    $conn = billing_db();
    $safeTable = preg_replace('/[^A-Za-z0-9_]/', '', $tableName);
    $safeIdColumn = preg_replace('/[^A-Za-z0-9_]/', '', $idColumn);
    if ($safeTable === '' || $safeIdColumn === '') {
        return false;
    }

    $where = ["`{$safeIdColumn}` = ?", 'COALESCE(is_deleted, 0) = 0'];
    $types = 'i';
    $params = [$recordId];

    if ($userId !== null && billing_table_has_column($safeTable, 'user_id')) {
        $where[] = 'user_id = ?';
        $types .= 'i';
        $params[] = $userId;
    }
    if ($organizationId !== null && billing_table_has_column($safeTable, 'organization_id')) {
        $where[] = 'organization_id = ?';
        $types .= 'i';
        $params[] = $organizationId;
    }

    $record = billing_fetch_one(
        "SELECT * FROM `{$safeTable}` WHERE " . implode(' AND ', $where) . ' LIMIT 1',
        $types,
        $params
    );
    if (!$record) {
        return false;
    }

    $conn->begin_transaction();
    try {
        if (!billing_log_deleted_record($safeTable, $recordId, $record)) {
            throw new RuntimeException('Unable to write deleted log.');
        }

        $setParts = ['is_deleted = 1', 'deleted_at = CURRENT_TIMESTAMP'];
        $updateTypes = '';
        $updateParams = [];
        foreach ($extraUpdates as $column => $value) {
            $safeColumn = preg_replace('/[^A-Za-z0-9_]/', '', (string) $column);
            if ($safeColumn === '' || !billing_table_has_column($safeTable, $safeColumn)) {
                continue;
            }
            $setParts[] = "`{$safeColumn}` = ?";
            $updateTypes .= is_int($value) ? 'i' : (is_float($value) ? 'd' : 's');
            $updateParams[] = $value;
        }

        $sql = "UPDATE `{$safeTable}` SET " . implode(', ', $setParts) . ' WHERE ' . implode(' AND ', $where) . ' LIMIT 1';
        $stmt = $conn->prepare($sql);
        if ($stmt === false) {
            throw new RuntimeException('Unable to prepare soft delete statement.');
        }

        $bindTypes = $updateTypes . $types;
        $bindParams = array_merge($updateParams, $params);
        if ($bindTypes !== '') {
            $stmt->bind_param($bindTypes, ...$bindParams);
        }
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            throw new RuntimeException('Unable to soft delete record.');
        }

        $conn->commit();
        return true;
    } catch (Throwable $exception) {
        $conn->rollback();
        return false;
    }
}

function billing_restore_soft_deleted_record(
    string $tableName,
    string $idColumn,
    int $recordId,
    ?int $userId = null,
    ?int $organizationId = null
): bool {
    billing_ensure_soft_delete_support();

    $safeTable = preg_replace('/[^A-Za-z0-9_]/', '', $tableName);
    $safeIdColumn = preg_replace('/[^A-Za-z0-9_]/', '', $idColumn);
    if ($safeTable === '' || $safeIdColumn === '' || $recordId <= 0) {
        return false;
    }

    $where = ["`{$safeIdColumn}` = ?"];
    $types = 'i';
    $params = [$recordId];

    if ($userId !== null && billing_table_has_column($safeTable, 'user_id')) {
        $where[] = 'user_id = ?';
        $types .= 'i';
        $params[] = $userId;
    }
    if ($organizationId !== null && billing_table_has_column($safeTable, 'organization_id')) {
        $where[] = 'organization_id = ?';
        $types .= 'i';
        $params[] = $organizationId;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        "UPDATE `{$safeTable}`
         SET is_deleted = 0,
             deleted_at = NULL
         WHERE " . implode(' AND ', $where) . '
         LIMIT 1'
    );
    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param($types, ...$params);
    $saved = $stmt->execute();
    $stmt->close();
    return $saved;
}

function billing_ensure_customer_delete_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();

    $hasIsDeleted = billing_fetch_one("SHOW COLUMNS FROM customers LIKE 'is_deleted'") !== null;
    if (!$hasIsDeleted) {
        $conn->query("ALTER TABLE customers ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0 AFTER outstanding_amount");
    }

    $hasDeletedAt = billing_fetch_one("SHOW COLUMNS FROM customers LIKE 'deleted_at'") !== null;
    if (!$hasDeletedAt) {
        $conn->query("ALTER TABLE customers ADD COLUMN deleted_at DATETIME DEFAULT NULL AFTER is_deleted");
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS customer_delete_log (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            customer_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            customer_name VARCHAR(160) NOT NULL,
            email VARCHAR(190) NOT NULL,
            status_label VARCHAR(50) NOT NULL DEFAULT "Deleted",
            outstanding_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            deleted_payload LONGTEXT DEFAULT NULL,
            deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_customer_delete_log_user (user_id),
            KEY idx_customer_delete_log_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS customer_meta (
            customer_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            meta_json LONGTEXT DEFAULT NULL,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (customer_id),
            KEY idx_customer_meta_user (user_id),
            KEY idx_customer_meta_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_ensure_customer_view_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();
    $conn->query(
        'CREATE TABLE IF NOT EXISTS customer_views (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            view_key VARCHAR(190) NOT NULL,
            view_name VARCHAR(190) NOT NULL,
            criteria_logic VARCHAR(10) NOT NULL DEFAULT "AND",
            criteria_json LONGTEXT DEFAULT NULL,
            selected_columns_json LONGTEXT DEFAULT NULL,
            visibility_scope VARCHAR(30) NOT NULL DEFAULT "only_me",
            is_favorite TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_customer_view_key (user_id, organization_id, view_key),
            KEY idx_customer_views_user (user_id),
            KEY idx_customer_views_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_ensure_timesheet_view_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();
    $conn->query(
        'CREATE TABLE IF NOT EXISTS timesheet_views (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            view_key VARCHAR(190) NOT NULL,
            view_name VARCHAR(190) NOT NULL,
            criteria_logic VARCHAR(10) NOT NULL DEFAULT "AND",
            criteria_json LONGTEXT DEFAULT NULL,
            selected_columns_json LONGTEXT DEFAULT NULL,
            visibility_scope VARCHAR(30) NOT NULL DEFAULT "only_me",
            is_favorite TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_timesheet_view_key (user_id, organization_id, view_key),
            KEY idx_timesheet_views_user (user_id),
            KEY idx_timesheet_views_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_countries(): array
{
    return [
        'India' => ['Tamil Nadu', 'Karnataka', 'Kerala', 'Maharashtra', 'Delhi', 'Telangana', 'Andhra Pradesh', 'Gujarat'],
        'United States' => ['California', 'Florida', 'New York', 'Texas', 'Washington'],
        'United Kingdom' => ['England', 'Scotland', 'Wales', 'Northern Ireland'],
        'United Arab Emirates' => ['Abu Dhabi', 'Dubai', 'Sharjah'],
        'Singapore' => ['Central Singapore', 'North East', 'North West', 'South East', 'South West'],
    ];
}

function billing_industries(): array
{
    return [
        'Agency or Sales House',
        'Agriculture',
        'Art and Design',
        'Automotive',
        'Construction',
        'Consulting',
        'Ecommerce',
        'Education',
        'Finance',
        'Healthcare',
        'Hospitality',
        'IT Services',
        'Marketing',
        'Mining and Logistics',
        'Non-Profit',
        'Publishing and Web Media',
        'Real Estate',
        'Retail',
        'SaaS',
        'Telecommunications',
    ];
}

function billing_fiscal_years(): array
{
    return [
        'January - December',
        'February - January',
        'March - February',
        'April - March',
        'May - April',
        'June - May',
    ];
}

function billing_languages(): array
{
    return ['English', 'Tamil', 'Hindi', 'Malayalam', 'Kannada'];
}

function billing_billing_sources(): array
{
    return ['Pen and Paper', 'Spreadsheet/Excel', 'QuickBooks', 'Sage', 'This is new business', 'Others'];
}

function billing_billing_modes(): array
{
    return [
        'one_time' => [
            'title' => 'One-time billing',
            'description' => 'Generate one-time invoices.',
            'icon' => 'fa-regular fa-file-lines',
        ],
        'recurring' => [
            'title' => 'Recurring billing',
            'description' => 'Create subscriptions, addons, plans, and coupons. Automate invoices and refunds.',
            'icon' => 'fa-regular fa-calendar',
        ],
        'hybrid' => [
            'title' => 'One-time & recurring billing',
            'description' => 'Create both one-time invoices and subscriptions, as required.',
            'icon' => 'fa-solid fa-circle-check',
        ],
    ];
}

function billing_generate_otp(): string
{
    return (string) random_int(100000, 999999);
}

function billing_generate_org_code(int $userId): string
{
    return 'BILL' . date('Y') . str_pad((string) $userId, 4, '0', STR_PAD_LEFT);
}

function billing_current_user(): ?array
{
    billing_ensure_soft_delete_support();

    $userId = (int) ($_SESSION['billing_user_id'] ?? 0);
    if ($userId <= 0) {
        return null;
    }

    return billing_fetch_one(
        'SELECT id, business_name, email, phone_country_code, phone_number, is_phone_verified, created_at
         FROM users
         WHERE id = ? AND COALESCE(is_deleted, 0) = 0',
        'i',
        [$userId]
    );
}

function billing_current_organization(): ?array
{
    billing_ensure_soft_delete_support();

    $userId = (int) ($_SESSION['billing_user_id'] ?? 0);
    if ($userId <= 0) {
        return null;
    }

    return billing_fetch_one(
        'SELECT *
         FROM organizations
         WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'i',
        [$userId]
    );
}

function billing_save_signup_otp(string $email, string $phone, string $otp): void
{
    $conn = billing_db();
    $expiresAt = date('Y-m-d H:i:s', time() + 600);

    $stmt = $conn->prepare(
        'INSERT INTO signup_otps (email, phone, otp_code, expires_at)
         VALUES (?, ?, ?, ?)'
    );

    if ($stmt !== false) {
        $stmt->bind_param('ssss', $email, $phone, $otp, $expiresAt);
        $stmt->execute();
        $stmt->close();
    }
}

function billing_mark_otp_verified(string $email, string $otp): bool
{
    $conn = billing_db();
    $now = date('Y-m-d H:i:s');

    $find = $conn->prepare(
        'SELECT id
         FROM signup_otps
         WHERE email = ? AND otp_code = ? AND verified_at IS NULL AND expires_at >= ?
         ORDER BY id DESC
         LIMIT 1'
    );

    if ($find === false) {
        return false;
    }

    $find->bind_param('sss', $email, $otp, $now);
    $find->execute();
    $result = $find->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $find->close();

    if (!$row) {
        return false;
    }

    $otpId = (int) $row['id'];
    $update = $conn->prepare('UPDATE signup_otps SET verified_at = ? WHERE id = ?');
    if ($update === false) {
        return false;
    }

    $update->bind_param('si', $now, $otpId);
    $update->execute();
    $update->close();

    return true;
}

function billing_create_user_from_signup(array $signupData): ?int
{
    $conn = billing_db();
    $email = (string) $signupData['email'];
    $phone = (string) $signupData['phone'];
    $phoneCode = (string) $signupData['phone_code'];
    $fullPhone = $phoneCode . $phone;

    $existing = billing_fetch_one('SELECT id FROM users WHERE email = ? LIMIT 1', 's', [$email]);
    if ($existing) {
        return (int) $existing['id'];
    }

    $company = (string) $signupData['company_name'];
    $passwordHash = password_hash((string) $signupData['password'], PASSWORD_DEFAULT);
    $state = (string) $signupData['state'];
    $country = (string) $signupData['country'];
    $signupId = 0;

    $existingSignup = billing_fetch_one(
        'SELECT id, org_id FROM signup WHERE email = ? OR CONCAT(phone_code, phone) = ? ORDER BY id DESC LIMIT 1',
        'ss',
        [$email, $fullPhone]
    );

    if ($existingSignup) {
        $signupId = (int) $existingSignup['id'];
        $orgId = (string) ($existingSignup['org_id'] ?? '');

        $updateSignup = $conn->prepare(
            'UPDATE signup
             SET company_name = ?, email = ?, phone = ?, phone_code = ?, password = ?, state = ?, country = ?
             WHERE id = ?'
        );

        if ($updateSignup !== false) {
            $updateSignup->bind_param('sssssssi', $company, $email, $phone, $phoneCode, $passwordHash, $state, $country, $signupId);
            $updateSignup->execute();
            $updateSignup->close();
        }

        if ($orgId === '') {
            $orgId = billing_generate_org_code($signupId);
            $fillOrgId = $conn->prepare('UPDATE signup SET org_id = ? WHERE id = ?');
            if ($fillOrgId !== false) {
                $fillOrgId->bind_param('si', $orgId, $signupId);
                $fillOrgId->execute();
                $fillOrgId->close();
            }
        }
    } else {
        $orgId = '';
        $signupStmt = $conn->prepare(
            'INSERT INTO signup (company_name, email, phone, phone_code, password, state, country, org_id)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
        );

        if ($signupStmt !== false) {
            $signupStmt->bind_param('ssssssss', $company, $email, $phone, $phoneCode, $passwordHash, $state, $country, $orgId);
            if ($signupStmt->execute()) {
                $signupId = (int) $signupStmt->insert_id;
            }
            $signupStmt->close();
        }

        if ($signupId > 0) {
            $orgId = billing_generate_org_code($signupId);
            $updateSignup = $conn->prepare('UPDATE signup SET org_id = ? WHERE id = ?');
            if ($updateSignup !== false) {
                $updateSignup->bind_param('si', $orgId, $signupId);
                $updateSignup->execute();
                $updateSignup->close();
            }
        }
    }

    $userStmt = $conn->prepare(
        'INSERT INTO users (business_name, email, password_hash, phone_country_code, phone_number, is_phone_verified)
         VALUES (?, ?, ?, ?, ?, 1)'
    );

    if ($userStmt === false) {
        return null;
    }

    $userStmt->bind_param('sssss', $company, $email, $passwordHash, $phoneCode, $phone);
    $executed = $userStmt->execute();
    $userId = (int) $userStmt->insert_id;
    $userStmt->close();

    if (!$executed) {
        $existing = billing_fetch_one('SELECT id FROM users WHERE email = ? LIMIT 1', 's', [$email]);
        if ($existing) {
            return (int) $existing['id'];
        }
    }

    return $userId > 0 ? $userId : null;
}

function billing_upsert_organization(int $userId, array $data): int
{
    $existing = billing_fetch_one('SELECT id FROM organizations WHERE user_id = ? LIMIT 1', 'i', [$userId]);
    $conn = billing_db();

    $organizationName = (string) ($data['organization_name'] ?? '');
    $country = (string) ($data['country'] ?? 'India');
    $state = (string) ($data['state'] ?? '');
    $currency = (string) ($data['base_currency'] ?? 'INR - Indian Rupee');
    $fiscalYear = (string) ($data['fiscal_year'] ?? 'April - March');
    $language = (string) ($data['language'] ?? 'English');
    $timezone = (string) ($data['timezone_label'] ?? '(GMT 5:30) India Standard Time (Asia/Kolkata)');
    $gstRegistered = !empty($data['gst_registered']) ? 1 : 0;
    $industry = (string) ($data['industry_type'] ?? '');
    $billingMode = (string) ($data['billing_mode'] ?? 'hybrid');
    $billingSource = (string) ($data['billing_source'] ?? '');
    $wantsImport = !empty($data['wants_import']) ? 1 : 0;

    if ($existing) {
        $organizationId = (int) $existing['id'];
        $stmt = $conn->prepare(
            'UPDATE organizations
             SET organization_name = ?, location_country = ?, state_name = ?, base_currency = ?, fiscal_year = ?, language_pref = ?, timezone_label = ?,
                 gst_registered = ?, industry_type = ?, billing_mode = ?, billing_source = ?, wants_import = ?, onboarding_completed_at = CASE WHEN ? = 1 THEN NOW() ELSE onboarding_completed_at END
             WHERE id = ?'
        );
        if ($stmt !== false) {
            $completed = $billingSource !== '' ? 1 : 0;
            $stmt->bind_param('sssssssisssiii', $organizationName, $country, $state, $currency, $fiscalYear, $language, $timezone, $gstRegistered, $industry, $billingMode, $billingSource, $wantsImport, $completed, $organizationId);
            $stmt->execute();
            $stmt->close();
        }
        return $organizationId;
    }

    $stmt = $conn->prepare(
        'INSERT INTO organizations (
            user_id, organization_name, location_country, state_name, base_currency, fiscal_year, language_pref, timezone_label,
            gst_registered, industry_type, billing_mode, billing_source, wants_import, onboarding_completed_at
         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE WHEN ? = 1 THEN NOW() ELSE NULL END)'
    );

    if ($stmt === false) {
        return 0;
    }

    $completed = $billingSource !== '' ? 1 : 0;
    $stmt->bind_param('isssssssisssii', $userId, $organizationName, $country, $state, $currency, $fiscalYear, $language, $timezone, $gstRegistered, $industry, $billingMode, $billingSource, $wantsImport, $completed);
    $stmt->execute();
    $organizationId = (int) $stmt->insert_id;
    $stmt->close();

    return $organizationId;
}

function billing_seed_demo_data(int $userId, int $organizationId): void
{
    billing_ensure_customer_delete_support();

    $existingCustomer = billing_fetch_one('SELECT id FROM customers WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0 LIMIT 1', 'i', [$userId]);
    if ($existingCustomer) {
        return;
    }

    $conn = billing_db();

    $customers = [
        ['Patricia Gomez', 'patricia@zykler.co', 'Active', 8400.00],
        ['Aaron Brown', 'aaron@zykler.co', 'Trial', 2400.00],
        ['Sri Ramakrishna Math', 'accounts@chennaimath.org', 'Active', 12200.00],
    ];

    $customerStmt = $conn->prepare(
        'INSERT INTO customers (user_id, organization_id, customer_name, email, status_label, outstanding_amount)
         VALUES (?, ?, ?, ?, ?, ?)'
    );
    if ($customerStmt !== false) {
        foreach ($customers as $customer) {
            [$name, $email, $status, $amount] = $customer;
            $customerStmt->bind_param('iisssd', $userId, $organizationId, $name, $email, $status, $amount);
            $customerStmt->execute();
        }
        $customerStmt->close();
    }

    $items = [
        ['Premium Monthly', 'Plan', 1000.00, 'Active'],
        ['On-premise Implementation', 'Service', 200.00, 'Active'],
        ['Website Revamp', 'Service', 275.00, 'Draft'],
    ];

    $itemStmt = $conn->prepare(
        'INSERT INTO catalog_items (user_id, organization_id, item_name, item_type, unit_price, status_label)
         VALUES (?, ?, ?, ?, ?, ?)'
    );
    if ($itemStmt !== false) {
        foreach ($items as $item) {
            [$name, $type, $price, $status] = $item;
            $itemStmt->bind_param('iissds', $userId, $organizationId, $name, $type, $price, $status);
            $itemStmt->execute();
        }
        $itemStmt->close();
    }

    $invoices = [
        ['INV-00102', 'Sri Ramakrishna Math', 1200.00, 'Paid', 'one_time', date('Y-m-d', strtotime('+5 days'))],
        ['INV-00103', 'Patricia Gomez', 275.00, 'Overdue', 'one_time', date('Y-m-d', strtotime('-2 days'))],
        ['SUB-00014', 'Aaron Brown', 1000.00, 'Live', 'recurring', date('Y-m-d', strtotime('+10 days'))],
    ];

    $invoiceStmt = $conn->prepare(
        'INSERT INTO invoices (user_id, organization_id, invoice_number, customer_name, total_amount, status_label, invoice_type, due_date)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
    );
    if ($invoiceStmt !== false) {
        foreach ($invoices as $invoice) {
            [$number, $customerName, $amount, $status, $type, $dueDate] = $invoice;
            $invoiceStmt->bind_param('iissdsss', $userId, $organizationId, $number, $customerName, $amount, $status, $type, $dueDate);
            $invoiceStmt->execute();
        }
        $invoiceStmt->close();
    }
}

function billing_remove_legacy_seeded_customers(int $userId, int $organizationId): void
{
    billing_ensure_customer_delete_support();
    billing_ensure_soft_delete_support();

    $activeCustomers = billing_fetch_all(
        'SELECT id, customer_name, email
         FROM customers
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id ASC',
        'ii',
        [$userId, $organizationId]
    );

    if (count($activeCustomers) !== 3) {
        return;
    }

    $seededCustomers = [
        'Patricia Gomez|patricia@zykler.co',
        'Aaron Brown|aaron@zykler.co',
        'Sri Ramakrishna Math|accounts@chennaimath.org',
    ];

    $activeKeys = [];
    foreach ($activeCustomers as $customer) {
        $activeKeys[] = trim((string) $customer['customer_name']) . '|' . trim((string) $customer['email']);
    }

    sort($activeKeys);
    sort($seededCustomers);

    if ($activeKeys !== $seededCustomers) {
        return;
    }

    foreach ($activeCustomers as $customer) {
        billing_soft_delete_record(
            'customers',
            'id',
            (int) ($customer['id'] ?? 0),
            $userId,
            $organizationId,
            ['status_label' => 'Deleted']
        );
    }

    if (isset($_SESSION['billing_customer_meta']) && is_array($_SESSION['billing_customer_meta'])) {
        foreach ($activeCustomers as $customer) {
            unset($_SESSION['billing_customer_meta'][(int) $customer['id']]);
        }
    }
}

function billing_dashboard_stats(int $userId): array
{
    billing_ensure_customer_delete_support();

    $customerCount = billing_fetch_one('SELECT COUNT(*) AS total FROM customers WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0', 'i', [$userId]);
    $itemCount = billing_fetch_one('SELECT COUNT(*) AS total FROM catalog_items WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0', 'i', [$userId]);
    $invoiceCount = billing_fetch_one('SELECT COUNT(*) AS total FROM invoices WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0', 'i', [$userId]);
    $revenue = billing_fetch_one('SELECT COALESCE(SUM(total_amount), 0) AS total FROM invoices WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0 AND status_label IN ("Paid", "Live")', 'i', [$userId]);
    $receivable = billing_fetch_one('SELECT COALESCE(SUM(total_amount), 0) AS total FROM invoices WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0 AND status_label IN ("Overdue", "Sent")', 'i', [$userId]);

    return [
        'customers' => (int) ($customerCount['total'] ?? 0),
        'items' => (int) ($itemCount['total'] ?? 0),
        'invoices' => (int) ($invoiceCount['total'] ?? 0),
        'revenue' => (float) ($revenue['total'] ?? 0),
        'receivable' => (float) ($receivable['total'] ?? 0),
    ];
}

function billing_get_customers(int $userId): array
{
    billing_ensure_customer_delete_support();

    return billing_fetch_all(
        'SELECT id, customer_name, email, status_label, outstanding_amount, created_at
         FROM customers
         WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id DESC',
        'i',
        [$userId]
    );
}

function billing_get_customer(int $userId, int $customerId): ?array
{
    billing_ensure_customer_delete_support();

    return billing_fetch_one(
        'SELECT id, customer_name, email, status_label, outstanding_amount, created_at
         FROM customers
         WHERE user_id = ? AND id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'ii',
        [$userId, $customerId]
    );
}

function billing_create_customer(int $userId, int $organizationId, array $data): ?int
{
    billing_ensure_customer_delete_support();

    $displayName = billing_clean((string) ($data['display_name'] ?? ''));
    $companyName = billing_clean((string) ($data['company_name'] ?? ''));
    $email = billing_clean((string) ($data['email'] ?? ''));
    $customerName = $displayName !== '' ? $displayName : $companyName;

    if ($customerName === '') {
        return null;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO customers (user_id, organization_id, customer_name, email, status_label, outstanding_amount)
         VALUES (?, ?, ?, ?, "Active", 0.00)'
    );

    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param('iiss', $userId, $organizationId, $customerName, $email);
    $executed = $stmt->execute();
    $customerId = $executed ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $customerId > 0 ? $customerId : null;
}

function billing_customer_meta_all(): array
{
    billing_ensure_customer_delete_support();

    $userId = (int) ($_SESSION['billing_user_id'] ?? 0);
    if ($userId <= 0) {
        return $_SESSION['billing_customer_meta'] ?? [];
    }

    $rows = billing_fetch_all(
        'SELECT customer_id, meta_json
         FROM customer_meta
         WHERE user_id = ? AND COALESCE(is_deleted, 0) = 0',
        'i',
        [$userId]
    );

    $allMeta = [];
    foreach ($rows as $row) {
        $meta = json_decode((string) ($row['meta_json'] ?? '{}'), true);
        $allMeta[(int) $row['customer_id']] = is_array($meta) ? $meta : [];
    }

    if ($allMeta === [] && isset($_SESSION['billing_customer_meta']) && is_array($_SESSION['billing_customer_meta'])) {
        return $_SESSION['billing_customer_meta'];
    }

    $_SESSION['billing_customer_meta'] = $allMeta;
    return $allMeta;
}

function billing_customer_meta(int $customerId): array
{
    $allMeta = billing_customer_meta_all();
    return is_array($allMeta[$customerId] ?? null) ? $allMeta[$customerId] : [];
}

function billing_save_customer_meta(int $customerId, array $data): void
{
    billing_ensure_customer_delete_support();

    if (!isset($_SESSION['billing_customer_meta']) || !is_array($_SESSION['billing_customer_meta'])) {
        $_SESSION['billing_customer_meta'] = [];
    }

    $_SESSION['billing_customer_meta'][$customerId] = $data;

    $customer = billing_fetch_one(
        'SELECT user_id, organization_id
         FROM customers
         WHERE id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'i',
        [$customerId]
    );

    if (!$customer) {
        return;
    }

    $metaJson = json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    $userId = (int) ($customer['user_id'] ?? 0);
    $organizationId = (int) ($customer['organization_id'] ?? 0);
    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO customer_meta (customer_id, user_id, organization_id, meta_json)
         VALUES (?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
             user_id = VALUES(user_id),
             organization_id = VALUES(organization_id),
             meta_json = VALUES(meta_json)'
    );

    if ($stmt === false) {
        return;
    }

    $stmt->bind_param('iiis', $customerId, $userId, $organizationId, $metaJson);
    $stmt->execute();
    $stmt->close();
}

function billing_find_customer_duplicate(int $userId, int $organizationId, string $customerName, string $email): ?array
{
    billing_ensure_customer_delete_support();

    $email = billing_clean($email);
    $customerName = billing_clean($customerName);

    if ($email !== '') {
        $customer = billing_fetch_one(
            'SELECT id, customer_name, email
             FROM customers
             WHERE user_id = ? AND organization_id = ? AND email = ? AND COALESCE(is_deleted, 0) = 0
             ORDER BY id DESC
             LIMIT 1',
            'iis',
            [$userId, $organizationId, $email]
        );
        if ($customer) {
            return $customer;
        }
    }

    if ($customerName === '') {
        return null;
    }

    return billing_fetch_one(
        'SELECT id, customer_name, email
         FROM customers
         WHERE user_id = ? AND organization_id = ? AND customer_name = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id DESC
         LIMIT 1',
        'iis',
        [$userId, $organizationId, $customerName]
    );
}

function billing_get_customer_views(int $userId, int $organizationId): array
{
    billing_ensure_customer_view_support();

    $rows = billing_fetch_all(
        'SELECT id, view_key, view_name, criteria_logic, criteria_json, selected_columns_json, visibility_scope, is_favorite
         FROM customer_views
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY created_at ASC',
        'ii',
        [$userId, $organizationId]
    );

    $views = [];
    foreach ($rows as $row) {
        $criteria = json_decode((string) ($row['criteria_json'] ?? '[]'), true);
        $selectedColumns = json_decode((string) ($row['selected_columns_json'] ?? '[]'), true);

        $views[(string) $row['view_key']] = [
            'id' => (int) ($row['id'] ?? 0),
            'name' => (string) ($row['view_name'] ?? ''),
            'logic' => (string) ($row['criteria_logic'] ?? 'AND'),
            'criteria' => is_array($criteria) ? $criteria : [],
            'selected_columns' => is_array($selectedColumns) ? $selectedColumns : ['name', 'email'],
            'visibility' => (string) ($row['visibility_scope'] ?? 'only_me'),
            'favorite' => !empty($row['is_favorite']),
        ];
    }

    return $views;
}

function billing_save_customer_view(int $userId, int $organizationId, string $viewKey, array $data): bool
{
    billing_ensure_customer_view_support();

    $viewKey = billing_clean($viewKey);
    $viewName = billing_clean((string) ($data['name'] ?? ''));
    if ($viewKey === '' || $viewName === '') {
        return false;
    }

    $logic = strtoupper((string) ($data['logic'] ?? 'AND')) === 'OR' ? 'OR' : 'AND';
    $criteriaJson = json_encode($data['criteria'] ?? [], JSON_UNESCAPED_UNICODE);
    $selectedColumnsJson = json_encode($data['selected_columns'] ?? ['name', 'email'], JSON_UNESCAPED_UNICODE);
    $visibility = billing_clean((string) ($data['visibility'] ?? 'only_me'));
    $isFavorite = !empty($data['favorite']) ? 1 : 0;

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO customer_views (user_id, organization_id, view_key, view_name, criteria_logic, criteria_json, selected_columns_json, visibility_scope, is_favorite)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
             view_name = VALUES(view_name),
             criteria_logic = VALUES(criteria_logic),
             criteria_json = VALUES(criteria_json),
             selected_columns_json = VALUES(selected_columns_json),
             visibility_scope = VALUES(visibility_scope),
             is_favorite = VALUES(is_favorite)'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param(
        'iissssssi',
        $userId,
        $organizationId,
        $viewKey,
        $viewName,
        $logic,
        $criteriaJson,
        $selectedColumnsJson,
        $visibility,
        $isFavorite
    );

    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_get_timesheet_views(int $userId, int $organizationId): array
{
    billing_ensure_timesheet_view_support();

    $rows = billing_fetch_all(
        'SELECT id, view_key, view_name, criteria_logic, criteria_json, selected_columns_json, visibility_scope, is_favorite
         FROM timesheet_views
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY created_at ASC',
        'ii',
        [$userId, $organizationId]
    );

    $views = [];
    foreach ($rows as $row) {
        $criteria = json_decode((string) ($row['criteria_json'] ?? '[]'), true);
        $selectedColumns = json_decode((string) ($row['selected_columns_json'] ?? '[]'), true);

        $views[(string) $row['view_key']] = [
            'id' => (int) ($row['id'] ?? 0),
            'name' => (string) ($row['view_name'] ?? ''),
            'logic' => (string) ($row['criteria_logic'] ?? 'AND'),
            'criteria' => is_array($criteria) ? $criteria : [],
            'selected_columns' => is_array($selectedColumns) && $selectedColumns !== [] ? $selectedColumns : ['project_name'],
            'visibility' => (string) ($row['visibility_scope'] ?? 'only_me'),
            'favorite' => !empty($row['is_favorite']),
        ];
    }

    return $views;
}

function billing_save_timesheet_view(int $userId, int $organizationId, string $viewKey, array $data): bool
{
    billing_ensure_timesheet_view_support();

    $viewKey = billing_clean($viewKey);
    $viewName = billing_clean((string) ($data['name'] ?? ''));
    if ($viewKey === '' || $viewName === '') {
        return false;
    }

    $logic = strtoupper((string) ($data['logic'] ?? 'AND')) === 'OR' ? 'OR' : 'AND';
    $criteriaJson = json_encode($data['criteria'] ?? [], JSON_UNESCAPED_UNICODE);
    $selectedColumnsJson = json_encode($data['selected_columns'] ?? ['project_name'], JSON_UNESCAPED_UNICODE);
    $visibility = billing_clean((string) ($data['visibility'] ?? 'only_me'));
    $isFavorite = !empty($data['favorite']) ? 1 : 0;

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO timesheet_views (user_id, organization_id, view_key, view_name, criteria_logic, criteria_json, selected_columns_json, visibility_scope, is_favorite)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
         ON DUPLICATE KEY UPDATE
             view_name = VALUES(view_name),
             criteria_logic = VALUES(criteria_logic),
             criteria_json = VALUES(criteria_json),
             selected_columns_json = VALUES(selected_columns_json),
             visibility_scope = VALUES(visibility_scope),
             is_favorite = VALUES(is_favorite)'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param(
        'iissssssi',
        $userId,
        $organizationId,
        $viewKey,
        $viewName,
        $logic,
        $criteriaJson,
        $selectedColumnsJson,
        $visibility,
        $isFavorite
    );

    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_update_customer(int $customerId, int $userId, int $organizationId, array $data): bool
{
    billing_ensure_customer_delete_support();

    $displayName = billing_clean((string) ($data['display_name'] ?? ''));
    $companyName = billing_clean((string) ($data['company_name'] ?? ''));
    $email = billing_clean((string) ($data['email'] ?? ''));
    $customerName = $displayName !== '' ? $displayName : $companyName;

    if ($customerName === '') {
        return false;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE customers
         SET customer_name = ?, email = ?
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('ssiii', $customerName, $email, $customerId, $userId, $organizationId);
    $executed = $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    return $executed && $affected >= 0;
}

function billing_prepare_customer_meta(array $data): array
{
    $meta = $data;
    $meta['contact_full_name'] = trim(((string) ($data['first_name'] ?? '')) . ' ' . ((string) ($data['last_name'] ?? '')));
    $meta['billing_address'] = trim(
        ((string) ($data['billing_address_1'] ?? '')) . ' ' .
        ((string) ($data['billing_address_2'] ?? '')) . ' ' .
        ((string) ($data['billing_city'] ?? ''))
    );
    $meta['shipping_address'] = trim(
        ((string) ($data['shipping_address_1'] ?? '')) . ' ' .
        ((string) ($data['shipping_address_2'] ?? '')) . ' ' .
        ((string) ($data['shipping_city'] ?? ''))
    );

    return $meta;
}

function billing_soft_delete_customer(int $customerId, int $userId, int $organizationId): bool
{
    billing_ensure_customer_delete_support();
    billing_ensure_soft_delete_support();

    return billing_soft_delete_record(
        'customers',
        'id',
        $customerId,
        $userId,
        $organizationId,
        ['status_label' => 'Deleted']
    );
}

function billing_import_customer_record(int $userId, int $organizationId, array $data, string $duplicateHandling = 'skip'): string
{
    $displayName = billing_clean((string) ($data['display_name'] ?? ''));
    $companyName = billing_clean((string) ($data['company_name'] ?? ''));
    $email = billing_clean((string) ($data['email'] ?? ''));
    $customerName = $displayName !== '' ? $displayName : $companyName;

    if ($customerName === '') {
        return 'ignored';
    }

    $duplicateHandling = in_array($duplicateHandling, ['skip', 'overwrite', 'add'], true) ? $duplicateHandling : 'skip';
    $existing = billing_find_customer_duplicate($userId, $organizationId, $customerName, $email);
    $meta = billing_prepare_customer_meta($data);

    if ($existing && $duplicateHandling === 'skip') {
        return 'skipped';
    }

    if ($existing && $duplicateHandling === 'overwrite') {
        $updated = billing_update_customer((int) $existing['id'], $userId, $organizationId, $data);
        if (!$updated) {
            return 'failed';
        }
        billing_save_customer_meta((int) $existing['id'], $meta);
        return 'updated';
    }

    $customerId = billing_create_customer($userId, $organizationId, $data);
    if (!$customerId) {
        return 'failed';
    }

    billing_save_customer_meta($customerId, $meta);
    return 'created';
}

function billing_catalog_units(): array
{
    return [
        'BOX - box',
        'CMS - cm',
        'DOZ - dz',
        'FTS - ft',
        'GMS - g',
        'INC - in',
        'KGS - kg',
        'LTR - l',
        'MTR - m',
        'NOS - nos',
        'PCS - pcs',
    ];
}

function billing_catalog_accounts(): array
{
    return [
        'Sales',
        'Discount',
        'General Income',
        'Interest Income',
        'Late Fee Income',
        'Other Charges',
        'Opening Balance Adjustments',
        'Prepaid Expenses',
        'Tax Payable',
        'TDS Payable',
    ];
}

function billing_expense_accounts_base(): array
{
    return [
        'Advertising And Marketing',
        'Advance Tax',
        'Automobile Expense',
        'Bad Debt',
        'Bank Fees and Charges',
        'Consultant Expense',
        'Contract Assets',
        'Cost of Goods Sold',
        'Employee Advance',
        'Furniture and Equipment',
        'GST Payable',
        'Job Costing',
        'Labor',
        'Materials',
        'Office Supplies',
        'Other Expenses',
        'Parking',
        'Postage',
        'Prepaid Expenses',
        'Printing and Stationery',
        'Purchase Discounts',
        'Raw Materials And Consumables',
        'Rent Expense',
        'Repairs and Maintenance',
        'Subcontractor',
        'TDS Payable',
        'TDS Receivable',
        'Tax Payable',
        'Travel Expense',
        'Vehicle Expense',
        'Withholding Tax',
        'Zoho Payroll - Adjustment Asset Account',
    ];
}

function billing_expense_accounts(int $userId = 0, int $organizationId = 0): array
{
    $accounts = billing_expense_accounts_base();

    if ($userId > 0 && $organizationId > 0) {
        billing_ensure_expense_support();

        $rows = billing_fetch_all(
            'SELECT category_name, expense_account
             FROM expenses
             WHERE user_id = ? AND organization_id = ?
             ORDER BY id DESC',
            'ii',
            [$userId, $organizationId]
        );

        foreach ($rows as $row) {
            foreach (['category_name', 'expense_account'] as $field) {
                $value = trim((string) ($row[$field] ?? ''));
                if ($value !== '') {
                    $accounts[] = $value;
                }
            }
        }

        $itemRows = billing_fetch_all(
            'SELECT expense_account
             FROM expense_line_items
             WHERE user_id = ? AND organization_id = ?
             ORDER BY id DESC',
            'ii',
            [$userId, $organizationId]
        );

        foreach ($itemRows as $row) {
            $value = trim((string) ($row['expense_account'] ?? ''));
            if ($value !== '') {
                $accounts[] = $value;
            }
        }
    }

    $accounts = array_values(array_unique(array_filter($accounts, static fn ($value): bool => trim((string) $value) !== '')));
    natcasesort($accounts);
    return array_values($accounts);
}

function billing_ensure_expense_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();

    $conn->query(
        'CREATE TABLE IF NOT EXISTS expenses (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            expense_date DATE DEFAULT NULL,
            category_name VARCHAR(180) NOT NULL DEFAULT "",
            expense_account VARCHAR(180) NOT NULL DEFAULT "",
            currency_code VARCHAR(20) NOT NULL DEFAULT "INR",
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            invoice_number VARCHAR(80) NOT NULL DEFAULT "",
            reference_number VARCHAR(80) NOT NULL DEFAULT "",
            customer_name VARCHAR(180) NOT NULL DEFAULT "",
            status_label VARCHAR(50) NOT NULL DEFAULT "NON-BILLABLE",
            notes_text TEXT DEFAULT NULL,
            attachment_paths LONGTEXT DEFAULT NULL,
            is_itemized TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_expenses_user (user_id),
            KEY idx_expenses_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $expenseColumns = [
        'expense_account' => 'ALTER TABLE expenses ADD COLUMN expense_account VARCHAR(180) NOT NULL DEFAULT "" AFTER category_name',
        'currency_code' => 'ALTER TABLE expenses ADD COLUMN currency_code VARCHAR(20) NOT NULL DEFAULT "INR" AFTER expense_account',
        'reference_number' => 'ALTER TABLE expenses ADD COLUMN reference_number VARCHAR(80) NOT NULL DEFAULT "" AFTER invoice_number',
        'attachment_paths' => 'ALTER TABLE expenses ADD COLUMN attachment_paths LONGTEXT DEFAULT NULL AFTER notes_text',
        'is_itemized' => 'ALTER TABLE expenses ADD COLUMN is_itemized TINYINT(1) NOT NULL DEFAULT 0 AFTER attachment_paths',
        'updated_at' => 'ALTER TABLE expenses ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
    ];
    foreach ($expenseColumns as $column => $sql) {
        if (billing_fetch_one("SHOW COLUMNS FROM expenses LIKE '{$column}'") === null) {
            $conn->query($sql);
        }
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS expense_line_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            expense_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            expense_account VARCHAR(180) NOT NULL DEFAULT "",
            notes_text VARCHAR(500) NOT NULL DEFAULT "",
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            sort_order INT UNSIGNED NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_expense_line_items_expense (expense_id),
            KEY idx_expense_line_items_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_ensure_recurring_expense_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    billing_ensure_expense_support();
    $conn = billing_db();

    $conn->query(
        'CREATE TABLE IF NOT EXISTS recurring_expenses (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            profile_name VARCHAR(180) NOT NULL DEFAULT "",
            repeat_every VARCHAR(20) NOT NULL DEFAULT "Week",
            start_date DATE DEFAULT NULL,
            next_run_date DATE DEFAULT NULL,
            ends_on DATE DEFAULT NULL,
            never_expires TINYINT(1) NOT NULL DEFAULT 1,
            category_name VARCHAR(180) NOT NULL DEFAULT "",
            currency_code VARCHAR(20) NOT NULL DEFAULT "INR",
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            notes_text TEXT DEFAULT NULL,
            customer_name VARCHAR(180) NOT NULL DEFAULT "",
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_recurring_expenses_user (user_id),
            KEY idx_recurring_expenses_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_save_recurring_expense(int $userId, int $organizationId, array $data): ?int
{
    billing_ensure_recurring_expense_support();

    $profileName = billing_clean((string) ($data['profile_name'] ?? ''));
    $repeatEvery = billing_clean((string) ($data['repeat_every'] ?? 'Week'));
    $startDate = billing_clean((string) ($data['start_date'] ?? ''));
    $nextRunDate = billing_clean((string) ($data['next_run_date'] ?? ''));
    $endsOn = billing_clean((string) ($data['ends_on'] ?? ''));
    $neverExpires = !empty($data['never_expires']) ? 1 : 0;
    $categoryName = billing_clean((string) ($data['category_name'] ?? ''));
    $currencyCode = billing_clean((string) ($data['currency_code'] ?? 'INR'));
    $amount = (float) ($data['amount'] ?? 0);
    $notesText = billing_clean((string) ($data['notes_text'] ?? ''));
    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));

    if ($profileName === '' || $categoryName === '' || $startDate === '' || $amount <= 0) {
        return null;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO recurring_expenses
         (user_id, organization_id, profile_name, repeat_every, start_date, next_run_date, ends_on, never_expires, category_name, currency_code, amount, notes_text, customer_name, status_label)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "Active")'
    );

    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param(
        'iisssssissdss',
        $userId,
        $organizationId,
        $profileName,
        $repeatEvery,
        $startDate,
        $nextRunDate,
        $endsOn,
        $neverExpires,
        $categoryName,
        $currencyCode,
        $amount,
        $notesText,
        $customerName
    );

    $saved = $stmt->execute();
    $newId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $newId > 0 ? $newId : null;
}

function billing_get_recurring_expenses(int $userId, int $organizationId): array
{
    billing_ensure_recurring_expense_support();

    return billing_fetch_all(
        'SELECT id, profile_name, repeat_every, start_date, next_run_date, ends_on, never_expires, category_name, currency_code, amount, notes_text, customer_name, status_label, created_at
         FROM recurring_expenses
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_recurring_expense(int $userId, int $organizationId, int $recurringExpenseId): ?array
{
    billing_ensure_recurring_expense_support();

    return billing_fetch_one(
        'SELECT id, profile_name, repeat_every, start_date, next_run_date, ends_on, never_expires, category_name, currency_code, amount, notes_text, customer_name, status_label, created_at
         FROM recurring_expenses
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$recurringExpenseId, $userId, $organizationId]
    );
}

function billing_project_billing_methods(): array
{
    return [
        'fixed_project_cost' => 'Fixed Cost for Project',
        'project_hours' => 'Based on Project Hours',
        'task_hours' => 'Based on Task Hours',
        'staff_hours' => 'Based on Staff Hours',
    ];
}

function billing_project_billing_method_label(string $value): string
{
    $methods = billing_project_billing_methods();
    return $methods[$value] ?? 'Hourly Rate Per Project';
}

function billing_project_rate_suffix(string $value): string
{
    return match ($value) {
        'fixed_project_cost' => '',
        'task_hours' => ' per task hour',
        'staff_hours' => ' per staff hour',
        default => ' per hour',
    };
}

function billing_ensure_project_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();

    $conn->query(
        'CREATE TABLE IF NOT EXISTS projects (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            project_name VARCHAR(180) NOT NULL DEFAULT "",
            project_code VARCHAR(80) NOT NULL DEFAULT "",
            customer_name VARCHAR(180) NOT NULL DEFAULT "",
            billing_method VARCHAR(60) NOT NULL DEFAULT "project_hours",
            billing_rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            description_text TEXT DEFAULT NULL,
            cost_budget DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            revenue_budget DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            watchlist_enabled TINYINT(1) NOT NULL DEFAULT 1,
            logged_seconds INT UNSIGNED NOT NULL DEFAULT 0,
            billed_seconds INT UNSIGNED NOT NULL DEFAULT 0,
            unbilled_seconds INT UNSIGNED NOT NULL DEFAULT 0,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_projects_user (user_id),
            KEY idx_projects_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS project_users (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            project_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            display_name VARCHAR(180) NOT NULL DEFAULT "",
            email VARCHAR(190) NOT NULL DEFAULT "",
            role_label VARCHAR(80) NOT NULL DEFAULT "Admin",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_project_users_project (project_id),
            KEY idx_project_users_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS project_tasks (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            project_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            task_name VARCHAR(180) NOT NULL DEFAULT "",
            description_text TEXT DEFAULT NULL,
            is_billable TINYINT(1) NOT NULL DEFAULT 1,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_project_tasks_project (project_id),
            KEY idx_project_tasks_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS project_time_entries (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            project_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            entry_date DATE NOT NULL,
            project_name VARCHAR(180) NOT NULL DEFAULT "",
            customer_name VARCHAR(180) NOT NULL DEFAULT "",
            task_name VARCHAR(180) NOT NULL DEFAULT "",
            user_display_name VARCHAR(180) NOT NULL DEFAULT "",
            duration_seconds INT UNSIGNED NOT NULL DEFAULT 0,
            notes_text TEXT DEFAULT NULL,
            billing_status VARCHAR(40) NOT NULL DEFAULT "Unbilled",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_project_time_entries_project (project_id),
            KEY idx_project_time_entries_user (user_id),
            KEY idx_project_time_entries_org (organization_id),
            KEY idx_project_time_entries_date (entry_date)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_save_project(int $userId, int $organizationId, array $data): ?int
{
    billing_ensure_project_support();

    $projectName = billing_clean((string) ($data['project_name'] ?? ''));
    $projectCode = billing_clean((string) ($data['project_code'] ?? ''));
    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $billingMethod = billing_clean((string) ($data['billing_method'] ?? 'project_hours'));
    $billingRate = (float) ($data['billing_rate'] ?? 0);
    $descriptionText = billing_clean((string) ($data['description_text'] ?? ''));
    $costBudget = (float) ($data['cost_budget'] ?? 0);
    $revenueBudget = (float) ($data['revenue_budget'] ?? 0);
    $watchlistEnabled = !empty($data['watchlist_enabled']) ? 1 : 0;
    $users = is_array($data['users'] ?? null) ? $data['users'] : [];
    $tasks = is_array($data['tasks'] ?? null) ? $data['tasks'] : [];

    if ($projectName === '' || $customerName === '' || $billingMethod === '') {
        return null;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO projects
         (user_id, organization_id, project_name, project_code, customer_name, billing_method, billing_rate, description_text, cost_budget, revenue_budget, watchlist_enabled, status_label)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "Active")'
    );
    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param(
        'iissssdsddi',
        $userId,
        $organizationId,
        $projectName,
        $projectCode,
        $customerName,
        $billingMethod,
        $billingRate,
        $descriptionText,
        $costBudget,
        $revenueBudget,
        $watchlistEnabled
    );
    $saved = $stmt->execute();
    $projectId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    if ($projectId <= 0) {
        return null;
    }

    foreach ($users as $row) {
        $displayName = billing_clean((string) ($row['display_name'] ?? ''));
        $email = billing_clean((string) ($row['email'] ?? ''));
        $roleLabel = billing_clean((string) ($row['role_label'] ?? 'Admin'));
        if ($displayName === '' && $email === '') {
            continue;
        }
        $userStmt = $conn->prepare(
            'INSERT INTO project_users (project_id, user_id, organization_id, display_name, email, role_label)
             VALUES (?, ?, ?, ?, ?, ?)'
        );
        if ($userStmt === false) {
            continue;
        }
        $userStmt->bind_param('iiisss', $projectId, $userId, $organizationId, $displayName, $email, $roleLabel);
        $userStmt->execute();
        $userStmt->close();
    }

    foreach ($tasks as $row) {
        $taskName = billing_clean((string) ($row['task_name'] ?? ''));
        $taskDescription = billing_clean((string) ($row['description_text'] ?? ''));
        $isBillable = !empty($row['is_billable']) ? 1 : 0;
        if ($taskName === '' && $taskDescription === '') {
            continue;
        }
        $taskStmt = $conn->prepare(
            'INSERT INTO project_tasks (project_id, user_id, organization_id, task_name, description_text, is_billable)
             VALUES (?, ?, ?, ?, ?, ?)'
        );
        if ($taskStmt === false) {
            continue;
        }
        $taskStmt->bind_param('iiissi', $projectId, $userId, $organizationId, $taskName, $taskDescription, $isBillable);
        $taskStmt->execute();
        $taskStmt->close();
    }

    return $projectId;
}

function billing_create_project_from_timer(int $userId, int $organizationId, array $timerData, string $defaultCustomerName = 'General'): ?int
{
    billing_ensure_project_support();

    $projectName = billing_clean((string) ($timerData['project_name'] ?? ''));
    if ($projectName === '') {
        $projectName = billing_clean((string) ($timerData['task_name'] ?? ''));
    }
    if ($projectName === '') {
        $projectName = billing_clean((string) ($timerData['notes'] ?? ''));
    }
    if ($projectName === '') {
        $projectName = 'Timer Entry';
    }

    $customerName = billing_clean($defaultCustomerName);
    if ($customerName === '') {
        $customerName = 'General';
    }

    $taskName = billing_clean((string) ($timerData['task_name'] ?? ''));
    $notes = billing_clean((string) ($timerData['notes'] ?? ''));
    $isBillable = !empty($timerData['billable']);

    return billing_save_project(
        $userId,
        $organizationId,
        [
            'project_name' => $projectName,
            'project_code' => '',
            'customer_name' => $customerName,
            'billing_method' => 'project_hours',
            'billing_rate' => 0,
            'description_text' => $notes,
            'cost_budget' => 0,
            'revenue_budget' => 0,
            'watchlist_enabled' => 1,
            'users' => [],
            'tasks' => $taskName !== '' ? [[
                'task_name' => $taskName,
                'description_text' => $notes,
                'is_billable' => $isBillable ? '1' : '',
            ]] : [],
        ]
    );
}

function billing_get_projects(int $userId, int $organizationId): array
{
    billing_ensure_project_support();

    $projects = billing_fetch_all(
        'SELECT id, project_name, project_code, customer_name, billing_method, billing_rate, description_text, cost_budget, revenue_budget, watchlist_enabled, logged_seconds, billed_seconds, unbilled_seconds, status_label, created_at
         FROM projects
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );

    if ($projects === []) {
        return [];
    }

    $tasks = billing_fetch_all(
        'SELECT id, project_id, task_name, description_text, is_billable
         FROM project_tasks
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY project_id ASC, id ASC',
        'ii',
        [$userId, $organizationId]
    );

    $tasksByProject = [];
    foreach ($tasks as $task) {
        $projectId = (int) ($task['project_id'] ?? 0);
        if ($projectId <= 0) {
            continue;
        }
        if (!isset($tasksByProject[$projectId])) {
            $tasksByProject[$projectId] = [];
        }
        $tasksByProject[$projectId][] = $task;
    }

    foreach ($projects as &$project) {
        $projectId = (int) ($project['id'] ?? 0);
        $project['tasks'] = $tasksByProject[$projectId] ?? [];
    }
    unset($project);

    return $projects;
}

function billing_get_project(int $userId, int $organizationId, int $projectId): ?array
{
    billing_ensure_project_support();

    $project = billing_fetch_one(
        'SELECT id, project_name, project_code, customer_name, billing_method, billing_rate, description_text, cost_budget, revenue_budget, watchlist_enabled, logged_seconds, billed_seconds, unbilled_seconds, status_label, created_at
         FROM projects
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$projectId, $userId, $organizationId]
    );

    if (!$project) {
        return null;
    }

    $project['users'] = billing_fetch_all(
        'SELECT id, display_name, email, role_label
         FROM project_users
         WHERE project_id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id ASC',
        'iii',
        [$projectId, $userId, $organizationId]
    );

    $project['tasks'] = billing_fetch_all(
        'SELECT id, task_name, description_text, is_billable
         FROM project_tasks
         WHERE project_id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id ASC',
        'iii',
        [$projectId, $userId, $organizationId]
    );

    return $project;
}

function billing_add_project_logged_time(int $userId, int $organizationId, int $projectId, int $seconds, bool $isBillable): bool
{
    billing_ensure_project_support();

    if ($projectId <= 0 || $seconds <= 0) {
        return false;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE projects
         SET logged_seconds = logged_seconds + ?,
             unbilled_seconds = unbilled_seconds + ?,
             updated_at = CURRENT_TIMESTAMP
         WHERE id = ? AND user_id = ? AND organization_id = ?'
    );

    if ($stmt === false) {
        return false;
    }

    $unbilledSeconds = $isBillable ? $seconds : 0;
    $stmt->bind_param('iiiii', $seconds, $unbilledSeconds, $projectId, $userId, $organizationId);
    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_adjust_project_logged_time(int $userId, int $organizationId, int $projectId, int $secondsDelta, int $unbilledDelta = 0): bool
{
    billing_ensure_project_support();

    if ($projectId <= 0 || ($secondsDelta === 0 && $unbilledDelta === 0)) {
        return false;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE projects
         SET logged_seconds = GREATEST(logged_seconds + ?, 0),
             unbilled_seconds = GREATEST(unbilled_seconds + ?, 0),
             updated_at = CURRENT_TIMESTAMP
         WHERE id = ? AND user_id = ? AND organization_id = ?'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('iiiii', $secondsDelta, $unbilledDelta, $projectId, $userId, $organizationId);
    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_add_project_time_entry(int $userId, int $organizationId, array $entry): ?int
{
    billing_ensure_project_support();

    $projectId = (int) ($entry['project_id'] ?? 0);
    $durationSeconds = (int) ($entry['duration_seconds'] ?? 0);
    if ($projectId <= 0 || $durationSeconds <= 0) {
        return null;
    }

    $entryDate = billing_clean((string) ($entry['entry_date'] ?? date('Y-m-d')));
    $projectName = billing_clean((string) ($entry['project_name'] ?? ''));
    $customerName = billing_clean((string) ($entry['customer_name'] ?? ''));
    $taskName = billing_clean((string) ($entry['task_name'] ?? ''));
    $userDisplayName = billing_clean((string) ($entry['user_display_name'] ?? ''));
    $notesText = billing_clean((string) ($entry['notes_text'] ?? ''));
    $billingStatus = billing_clean((string) ($entry['billing_status'] ?? 'Unbilled'));

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO project_time_entries
         (project_id, user_id, organization_id, entry_date, project_name, customer_name, task_name, user_display_name, duration_seconds, notes_text, billing_status)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    );

    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param(
        'iiisssssiss',
        $projectId,
        $userId,
        $organizationId,
        $entryDate,
        $projectName,
        $customerName,
        $taskName,
        $userDisplayName,
        $durationSeconds,
        $notesText,
        $billingStatus
    );

    $saved = $stmt->execute();
    $entryId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $entryId > 0 ? $entryId : null;
}

function billing_get_project_time_entries(int $userId, int $organizationId): array
{
    billing_ensure_project_support();

    return billing_fetch_all(
        'SELECT id, project_id, entry_date, project_name, customer_name, task_name, user_display_name, duration_seconds, notes_text, billing_status, created_at
         FROM project_time_entries
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY entry_date DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_project_time_entry(int $userId, int $organizationId, int $entryId): ?array
{
    billing_ensure_project_support();

    if ($entryId <= 0) {
        return null;
    }

    return billing_fetch_one(
        'SELECT id, project_id, entry_date, project_name, customer_name, task_name, user_display_name, duration_seconds, notes_text, billing_status, created_at
         FROM project_time_entries
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$entryId, $userId, $organizationId]
    );
}

function billing_update_project_time_entry(int $userId, int $organizationId, int $entryId, array $entry): bool
{
    billing_ensure_project_support();

    if ($entryId <= 0) {
        return false;
    }

    $projectId = (int) ($entry['project_id'] ?? 0);
    $durationSeconds = max(0, (int) ($entry['duration_seconds'] ?? 0));
    if ($projectId <= 0 || $durationSeconds <= 0) {
        return false;
    }

    $entryDate = billing_clean((string) ($entry['entry_date'] ?? date('Y-m-d')));
    $projectName = billing_clean((string) ($entry['project_name'] ?? ''));
    $customerName = billing_clean((string) ($entry['customer_name'] ?? ''));
    $taskName = billing_clean((string) ($entry['task_name'] ?? ''));
    $userDisplayName = billing_clean((string) ($entry['user_display_name'] ?? ''));
    $notesText = billing_clean((string) ($entry['notes_text'] ?? ''));
    $billingStatus = billing_clean((string) ($entry['billing_status'] ?? 'Unbilled'));

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE project_time_entries
         SET project_id = ?,
             entry_date = ?,
             project_name = ?,
             customer_name = ?,
             task_name = ?,
             user_display_name = ?,
             duration_seconds = ?,
             notes_text = ?,
             billing_status = ?
         WHERE id = ? AND user_id = ? AND organization_id = ?
         LIMIT 1'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param(
        'isssssissiii',
        $projectId,
        $entryDate,
        $projectName,
        $customerName,
        $taskName,
        $userDisplayName,
        $durationSeconds,
        $notesText,
        $billingStatus,
        $entryId,
        $userId,
        $organizationId
    );

    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_ensure_product_catalog_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();

    $conn->query(
        'CREATE TABLE IF NOT EXISTS catalog_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_type VARCHAR(60) NOT NULL DEFAULT "Service",
            unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            item_description TEXT DEFAULT NULL,
            sales_account VARCHAR(120) NOT NULL DEFAULT "Sales",
            image_path VARCHAR(255) DEFAULT NULL,
            is_inactive TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_catalog_user (user_id),
            KEY idx_catalog_org (organization_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $itemColumns = [
        'unit_name' => 'ALTER TABLE catalog_items ADD COLUMN unit_name VARCHAR(60) NOT NULL DEFAULT "" AFTER status_label',
        'item_description' => 'ALTER TABLE catalog_items ADD COLUMN item_description TEXT DEFAULT NULL AFTER unit_name',
        'sales_account' => 'ALTER TABLE catalog_items ADD COLUMN sales_account VARCHAR(120) NOT NULL DEFAULT "Sales" AFTER item_description',
        'image_path' => 'ALTER TABLE catalog_items ADD COLUMN image_path VARCHAR(255) DEFAULT NULL AFTER sales_account',
        'is_inactive' => 'ALTER TABLE catalog_items ADD COLUMN is_inactive TINYINT(1) NOT NULL DEFAULT 0 AFTER image_path',
        'updated_at' => 'ALTER TABLE catalog_items ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
    ];
    foreach ($itemColumns as $column => $sql) {
        if (billing_fetch_one("SHOW COLUMNS FROM catalog_items LIKE '{$column}'") === null) {
            $conn->query($sql);
        }
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS catalog_item_history (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            item_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            action_label VARCHAR(80) NOT NULL,
            actor_label VARCHAR(190) NOT NULL DEFAULT "",
            details_text VARCHAR(255) NOT NULL DEFAULT "",
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_catalog_item_history_item (item_id),
            KEY idx_catalog_item_history_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS catalog_plans (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            product_item_id INT UNSIGNED NOT NULL,
            plan_name VARCHAR(180) NOT NULL,
            plan_code VARCHAR(80) NOT NULL DEFAULT "",
            plan_description TEXT DEFAULT NULL,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            billing_frequency_value INT UNSIGNED NOT NULL DEFAULT 1,
            billing_frequency_unit VARCHAR(20) NOT NULL DEFAULT "Month(s)",
            billing_cycles_mode VARCHAR(60) NOT NULL DEFAULT "Auto-renews until cancelled",
            billing_cycles_value INT UNSIGNED DEFAULT NULL,
            pricing_model VARCHAR(60) NOT NULL DEFAULT "Per Unit",
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            setup_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            trial_days INT UNSIGNED NOT NULL DEFAULT 0,
            item_type VARCHAR(20) NOT NULL DEFAULT "Service",
            plan_account VARCHAR(120) NOT NULL DEFAULT "Sales",
            setup_fee_account VARCHAR(120) NOT NULL DEFAULT "Sales",
            pricing_widget_enabled TINYINT(1) NOT NULL DEFAULT 0,
            image_path VARCHAR(255) DEFAULT NULL,
            is_inactive TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_catalog_plans_user (user_id),
            KEY idx_catalog_plans_product (product_item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS catalog_addons (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            product_item_id INT UNSIGNED NOT NULL,
            addon_name VARCHAR(180) NOT NULL,
            addon_code VARCHAR(80) NOT NULL DEFAULT "",
            addon_description TEXT DEFAULT NULL,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            addon_type VARCHAR(40) NOT NULL DEFAULT "One Time",
            pricing_model VARCHAR(60) NOT NULL DEFAULT "Package",
            billing_frequency_value INT UNSIGNED DEFAULT NULL,
            billing_frequency_unit VARCHAR(20) NOT NULL DEFAULT "Month(s)",
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            item_type VARCHAR(20) NOT NULL DEFAULT "Service",
            associate_plans_mode VARCHAR(60) NOT NULL DEFAULT "All Plans",
            associate_plan_ids TEXT DEFAULT NULL,
            display_in_widgets TINYINT(1) NOT NULL DEFAULT 0,
            allow_portal_associate TINYINT(1) NOT NULL DEFAULT 0,
            account_name VARCHAR(120) NOT NULL DEFAULT "Sales",
            image_path VARCHAR(255) DEFAULT NULL,
            is_inactive TINYINT(1) NOT NULL DEFAULT 0,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_catalog_addons_user (user_id),
            KEY idx_catalog_addons_product (product_item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $addonColumns = [
        'unit_name' => 'ALTER TABLE catalog_addons ADD COLUMN unit_name VARCHAR(60) NOT NULL DEFAULT "" AFTER billing_frequency_unit',
        'item_type' => 'ALTER TABLE catalog_addons ADD COLUMN item_type VARCHAR(20) NOT NULL DEFAULT "Service" AFTER price',
        'associate_plans_mode' => 'ALTER TABLE catalog_addons ADD COLUMN associate_plans_mode VARCHAR(60) NOT NULL DEFAULT "All Plans" AFTER item_type',
        'associate_plan_ids' => 'ALTER TABLE catalog_addons ADD COLUMN associate_plan_ids TEXT DEFAULT NULL AFTER associate_plans_mode',
        'display_in_widgets' => 'ALTER TABLE catalog_addons ADD COLUMN display_in_widgets TINYINT(1) NOT NULL DEFAULT 0 AFTER associate_plan_ids',
        'allow_portal_associate' => 'ALTER TABLE catalog_addons ADD COLUMN allow_portal_associate TINYINT(1) NOT NULL DEFAULT 0 AFTER display_in_widgets',
    ];
    foreach ($addonColumns as $column => $sql) {
        if (billing_fetch_one("SHOW COLUMNS FROM catalog_addons LIKE '{$column}'") === null) {
            $conn->query($sql);
        }
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS pricing_widgets (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            widget_name VARCHAR(180) NOT NULL,
            product_item_id INT UNSIGNED NOT NULL,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_pricing_widgets_user (user_id),
            KEY idx_pricing_widgets_product (product_item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS coupons (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            product_item_id INT UNSIGNED NOT NULL,
            coupon_name VARCHAR(180) NOT NULL,
            coupon_code VARCHAR(80) NOT NULL,
            discount_value DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            discount_by VARCHAR(30) NOT NULL DEFAULT "Flat",
            redemption_type VARCHAR(40) NOT NULL DEFAULT "One-Time",
            associate_plans_mode VARCHAR(60) NOT NULL DEFAULT "All Plans",
            associate_plan_ids TEXT DEFAULT NULL,
            associate_addons_mode VARCHAR(80) NOT NULL DEFAULT "All Recurring Addons",
            associate_addon_ids TEXT DEFAULT NULL,
            expiration_date DATE DEFAULT NULL,
            maximum_redemptions INT UNSIGNED DEFAULT NULL,
            status_label VARCHAR(50) NOT NULL DEFAULT "Active",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_coupons_user (user_id),
            KEY idx_coupons_product (product_item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_ensure_sales_module_support(): void
{
    static $ensured = false;

    if ($ensured) {
        return;
    }

    $conn = billing_db();

    $conn->query(
        'CREATE TABLE IF NOT EXISTS quotes (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            customer_id INT UNSIGNED DEFAULT NULL,
            quote_number VARCHAR(40) NOT NULL,
            customer_name VARCHAR(160) NOT NULL,
            reference_number VARCHAR(80) NOT NULL DEFAULT "",
            salesperson_name VARCHAR(160) NOT NULL DEFAULT "",
            project_name VARCHAR(160) NOT NULL DEFAULT "",
            subject VARCHAR(255) NOT NULL DEFAULT "",
            quote_date DATE DEFAULT NULL,
            expiry_date DATE DEFAULT NULL,
            sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            status_label VARCHAR(50) NOT NULL DEFAULT "Draft",
            quote_type VARCHAR(40) NOT NULL DEFAULT "Quote",
            customer_notes TEXT DEFAULT NULL,
            terms_text TEXT DEFAULT NULL,
            pdf_template_name VARCHAR(120) NOT NULL DEFAULT "Spreadsheet Template",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_quotes_number (organization_id, quote_number),
            KEY idx_quotes_user (user_id),
            KEY idx_quotes_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS quote_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            quote_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_id INT UNSIGNED DEFAULT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_description TEXT DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            account_name VARCHAR(120) NOT NULL DEFAULT "Sales",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_quote_items_quote (quote_id),
            KEY idx_quote_items_item (item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS subscriptions (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            customer_id INT UNSIGNED DEFAULT NULL,
            subscription_number VARCHAR(40) NOT NULL,
            customer_name VARCHAR(160) NOT NULL,
            plan_name VARCHAR(180) NOT NULL DEFAULT "",
            reference_number VARCHAR(80) NOT NULL DEFAULT "",
            salesperson_name VARCHAR(160) NOT NULL DEFAULT "",
            start_date DATE DEFAULT NULL,
            next_billing_date DATE DEFAULT NULL,
            current_cycle_start DATE DEFAULT NULL,
            current_cycle_end DATE DEFAULT NULL,
            billing_frequency_value INT UNSIGNED NOT NULL DEFAULT 1,
            billing_frequency_unit VARCHAR(30) NOT NULL DEFAULT "week(s)",
            expires_after_cycles INT UNSIGNED DEFAULT NULL,
            renews_forever TINYINT(1) NOT NULL DEFAULT 1,
            payment_terms VARCHAR(120) NOT NULL DEFAULT "Due on Receipt",
            payment_mode VARCHAR(40) NOT NULL DEFAULT "offline",
            autocharge_enabled TINYINT(1) NOT NULL DEFAULT 0,
            prorate_enabled TINYINT(1) NOT NULL DEFAULT 1,
            generate_invoice_on_prorate TINYINT(1) NOT NULL DEFAULT 1,
            sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            status_label VARCHAR(50) NOT NULL DEFAULT "Draft",
            customer_notes TEXT DEFAULT NULL,
            terms_text TEXT DEFAULT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_subscriptions_number (organization_id, subscription_number),
            KEY idx_subscriptions_user (user_id),
            KEY idx_subscriptions_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS subscription_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            subscription_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_id INT UNSIGNED DEFAULT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_description TEXT DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            item_role VARCHAR(30) NOT NULL DEFAULT "plan",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_subscription_items_subscription (subscription_id),
            KEY idx_subscription_items_item (item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS delivery_challans (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            customer_id INT UNSIGNED DEFAULT NULL,
            delivery_challan_number VARCHAR(40) NOT NULL,
            customer_name VARCHAR(160) NOT NULL,
            reference_number VARCHAR(80) NOT NULL DEFAULT "",
            challan_date DATE DEFAULT NULL,
            challan_type VARCHAR(80) NOT NULL DEFAULT "",
            sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            status_label VARCHAR(50) NOT NULL DEFAULT "Draft",
            customer_notes TEXT DEFAULT NULL,
            terms_text TEXT DEFAULT NULL,
            pdf_template_name VARCHAR(120) NOT NULL DEFAULT "Standard Template",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_delivery_challans_number (organization_id, delivery_challan_number),
            KEY idx_delivery_challans_user (user_id),
            KEY idx_delivery_challans_customer (customer_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS delivery_challan_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            delivery_challan_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_id INT UNSIGNED DEFAULT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_description TEXT DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_delivery_challan_items_doc (delivery_challan_id),
            KEY idx_delivery_challan_items_item (item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS credit_notes (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            customer_id INT UNSIGNED DEFAULT NULL,
            invoice_id INT UNSIGNED DEFAULT NULL,
            credit_note_number VARCHAR(40) NOT NULL,
            customer_name VARCHAR(160) NOT NULL,
            reference_number VARCHAR(80) NOT NULL DEFAULT "",
            salesperson_name VARCHAR(160) NOT NULL DEFAULT "",
            subject VARCHAR(255) NOT NULL DEFAULT "",
            credit_note_date DATE DEFAULT NULL,
            sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            credits_remaining DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            status_label VARCHAR(50) NOT NULL DEFAULT "Draft",
            customer_notes TEXT DEFAULT NULL,
            terms_text TEXT DEFAULT NULL,
            pdf_template_name VARCHAR(120) NOT NULL DEFAULT "Spreadsheet Template",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_credit_notes_number (organization_id, credit_note_number),
            KEY idx_credit_notes_user (user_id),
            KEY idx_credit_notes_customer (customer_id),
            KEY idx_credit_notes_invoice (invoice_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $conn->query(
        'CREATE TABLE IF NOT EXISTS credit_note_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            credit_note_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_id INT UNSIGNED DEFAULT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_description TEXT DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            account_name VARCHAR(120) NOT NULL DEFAULT "Sales",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_credit_note_items_note (credit_note_id),
            KEY idx_credit_note_items_item (item_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $invoiceColumns = [
        'customer_id' => 'ALTER TABLE invoices ADD COLUMN customer_id INT UNSIGNED DEFAULT NULL AFTER customer_name',
        'order_number' => 'ALTER TABLE invoices ADD COLUMN order_number VARCHAR(80) NOT NULL DEFAULT "" AFTER customer_id',
        'salesperson_name' => 'ALTER TABLE invoices ADD COLUMN salesperson_name VARCHAR(160) NOT NULL DEFAULT "" AFTER order_number',
        'subject' => 'ALTER TABLE invoices ADD COLUMN subject VARCHAR(255) NOT NULL DEFAULT "" AFTER salesperson_name',
        'sub_total' => 'ALTER TABLE invoices ADD COLUMN sub_total DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER total_amount',
        'discount_amount' => 'ALTER TABLE invoices ADD COLUMN discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER sub_total',
        'adjustment_amount' => 'ALTER TABLE invoices ADD COLUMN adjustment_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER discount_amount',
        'tax_total' => 'ALTER TABLE invoices ADD COLUMN tax_total DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER adjustment_amount',
        'invoice_date' => 'ALTER TABLE invoices ADD COLUMN invoice_date DATE DEFAULT NULL AFTER invoice_type',
        'notes_text' => 'ALTER TABLE invoices ADD COLUMN notes_text TEXT DEFAULT NULL AFTER due_date',
        'terms_text' => 'ALTER TABLE invoices ADD COLUMN terms_text TEXT DEFAULT NULL AFTER notes_text',
        'pdf_template_name' => 'ALTER TABLE invoices ADD COLUMN pdf_template_name VARCHAR(120) NOT NULL DEFAULT "Spreadsheet Template" AFTER terms_text',
        'updated_at' => 'ALTER TABLE invoices ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at',
    ];

    foreach ($invoiceColumns as $columnName => $ddl) {
        if (billing_fetch_one("SHOW COLUMNS FROM invoices LIKE '" . $conn->real_escape_string($columnName) . "'") === null) {
            $conn->query($ddl);
        }
    }

    $conn->query(
        'CREATE TABLE IF NOT EXISTS invoice_items (
            id INT UNSIGNED NOT NULL AUTO_INCREMENT,
            invoice_id INT UNSIGNED NOT NULL,
            user_id INT UNSIGNED NOT NULL,
            organization_id INT UNSIGNED NOT NULL,
            item_name VARCHAR(180) NOT NULL,
            item_description TEXT DEFAULT NULL,
            quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
            unit_name VARCHAR(60) NOT NULL DEFAULT "",
            rate DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
            account_name VARCHAR(120) NOT NULL DEFAULT "Sales",
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_invoice_items_invoice (invoice_id),
            KEY idx_invoice_items_user (user_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
    );

    $ensured = true;
}

function billing_catalog_log_item_history(int $itemId, int $userId, int $organizationId, string $action, string $actorLabel, string $details = ''): void
{
    billing_ensure_product_catalog_support();

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO catalog_item_history (item_id, user_id, organization_id, action_label, actor_label, details_text)
         VALUES (?, ?, ?, ?, ?, ?)'
    );

    if ($stmt === false) {
        return;
    }

    $stmt->bind_param('iiisss', $itemId, $userId, $organizationId, $action, $actorLabel, $details);
    $stmt->execute();
    $stmt->close();
}

function billing_get_items(int $userId, int $organizationId, string $view = 'active'): array
{
    billing_ensure_product_catalog_support();

    $where = 'user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0';
    if ($view === 'active') {
        $where .= ' AND COALESCE(is_inactive, 0) = 0';
    } elseif ($view === 'inactive') {
        $where .= ' AND COALESCE(is_inactive, 0) = 1';
    }

    return billing_fetch_all(
        "SELECT *
         FROM catalog_items
         WHERE {$where}
         ORDER BY created_at DESC, id DESC",
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_item(int $userId, int $organizationId, int $itemId): ?array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_one(
        'SELECT *
         FROM catalog_items
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$itemId, $userId, $organizationId]
    );
}

function billing_create_item(int $userId, int $organizationId, array $data, string $actorLabel = ''): ?int
{
    billing_ensure_product_catalog_support();

    $name = billing_clean((string) ($data['item_name'] ?? ''));
    if ($name === '') {
        return null;
    }

    $itemType = strtolower((string) ($data['item_type'] ?? 'goods')) === 'service' ? 'Service' : 'Goods';
    $unitName = billing_clean((string) ($data['unit_name'] ?? ''));
    $description = billing_clean((string) ($data['item_description'] ?? ''));
    $salesAccount = billing_clean((string) ($data['sales_account'] ?? 'Sales'));
    $unitPrice = (float) ($data['unit_price'] ?? 0);

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO catalog_items (user_id, organization_id, item_name, item_type, unit_price, status_label, unit_name, item_description, sales_account, is_inactive)
         VALUES (?, ?, ?, ?, ?, "Active", ?, ?, ?, 0)'
    );

    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param('iissdsss', $userId, $organizationId, $name, $itemType, $unitPrice, $unitName, $description, $salesAccount);
    $saved = $stmt->execute();
    $itemId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    if ($itemId > 0) {
        billing_catalog_log_item_history($itemId, $userId, $organizationId, 'created', $actorLabel, 'Item created');
        return $itemId;
    }

    return null;
}

function billing_update_item(int $itemId, int $userId, int $organizationId, array $data, string $actorLabel = ''): bool
{
    billing_ensure_product_catalog_support();

    $name = billing_clean((string) ($data['item_name'] ?? ''));
    if ($name === '') {
        return false;
    }

    $itemType = strtolower((string) ($data['item_type'] ?? 'goods')) === 'service' ? 'Service' : 'Goods';
    $unitName = billing_clean((string) ($data['unit_name'] ?? ''));
    $description = billing_clean((string) ($data['item_description'] ?? ''));
    $salesAccount = billing_clean((string) ($data['sales_account'] ?? 'Sales'));
    $unitPrice = (float) ($data['unit_price'] ?? 0);

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE catalog_items
         SET item_name = ?, item_type = ?, unit_price = ?, unit_name = ?, item_description = ?, sales_account = ?
         WHERE id = ? AND user_id = ? AND organization_id = ?
         LIMIT 1'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('ssdsssiii', $name, $itemType, $unitPrice, $unitName, $description, $salesAccount, $itemId, $userId, $organizationId);
    $saved = $stmt->execute();
    $stmt->close();

    if ($saved) {
        billing_catalog_log_item_history($itemId, $userId, $organizationId, 'updated', $actorLabel, 'Item updated');
    }

    return $saved;
}

function billing_clone_item(int $itemId, int $userId, int $organizationId, string $actorLabel = ''): ?int
{
    $item = billing_get_item($userId, $organizationId, $itemId);
    if (!$item) {
        return null;
    }

    $cloneData = [
        'item_name' => (string) $item['item_name'] . ' Copy',
        'item_type' => (string) $item['item_type'],
        'unit_name' => (string) ($item['unit_name'] ?? ''),
        'item_description' => (string) ($item['item_description'] ?? ''),
        'sales_account' => (string) ($item['sales_account'] ?? 'Sales'),
        'unit_price' => (float) ($item['unit_price'] ?? 0),
    ];

    $newId = billing_create_item($userId, $organizationId, $cloneData, $actorLabel);
    if ($newId) {
        billing_catalog_log_item_history($newId, $userId, $organizationId, 'cloned', $actorLabel, 'Cloned from item #' . $itemId);
    }

    return $newId;
}

function billing_set_item_inactive(int $itemId, int $userId, int $organizationId, bool $inactive, string $actorLabel = ''): bool
{
    billing_ensure_product_catalog_support();

    $conn = billing_db();
    $status = $inactive ? 'Inactive' : 'Active';
    $flag = $inactive ? 1 : 0;
    $stmt = $conn->prepare(
        'UPDATE catalog_items
         SET is_inactive = ?, status_label = ?
         WHERE id = ? AND user_id = ? AND organization_id = ?
         LIMIT 1'
    );

    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('isiii', $flag, $status, $itemId, $userId, $organizationId);
    $saved = $stmt->execute();
    $stmt->close();

    if ($saved) {
        billing_catalog_log_item_history(
            $itemId,
            $userId,
            $organizationId,
            $inactive ? 'marked inactive' : 'marked active',
            $actorLabel,
            $inactive ? 'Item marked as inactive' : 'Item marked as active'
        );
    }

    return $saved;
}

function billing_get_item_history(int $itemId, int $userId, int $organizationId): array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_all(
        'SELECT created_at, action_label, actor_label, details_text
         FROM catalog_item_history
         WHERE item_id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id DESC',
        'iii',
        [$itemId, $userId, $organizationId]
    );
}

function billing_active_products(int $userId, int $organizationId): array
{
    billing_ensure_product_catalog_support();
    return billing_fetch_all(
        'SELECT id, item_name, unit_name, unit_price
         FROM catalog_items
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0 AND COALESCE(is_inactive, 0) = 0
         ORDER BY item_name ASC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_plans(int $userId, int $organizationId, string $view = 'active'): array
{
    billing_ensure_product_catalog_support();

    $where = 'p.user_id = ? AND p.organization_id = ? AND COALESCE(p.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0';
    if ($view === 'active') {
        $where .= ' AND COALESCE(p.is_inactive, 0) = 0';
    } elseif ($view === 'inactive') {
        $where .= ' AND COALESCE(p.is_inactive, 0) = 1';
    }

    return billing_fetch_all(
        "SELECT p.*, i.item_name AS product_name
         FROM catalog_plans p
         INNER JOIN catalog_items i ON i.id = p.product_item_id
         WHERE {$where}
         ORDER BY p.created_at DESC, p.id DESC",
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_plan(int $userId, int $organizationId, int $planId): ?array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_one(
        'SELECT p.*, i.item_name AS product_name
         FROM catalog_plans p
         INNER JOIN catalog_items i ON i.id = p.product_item_id
         WHERE p.id = ? AND p.user_id = ? AND p.organization_id = ? AND COALESCE(p.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$planId, $userId, $organizationId]
    );
}

function billing_save_plan(int $userId, int $organizationId, array $data, ?int $planId = null): ?int
{
    billing_ensure_product_catalog_support();

    $productId = (int) ($data['product_item_id'] ?? 0);
    $planName = billing_clean((string) ($data['plan_name'] ?? ''));
    if ($productId <= 0 || $planName === '') {
        return null;
    }

    $planCode = billing_clean((string) ($data['plan_code'] ?? ''));
    $description = billing_clean((string) ($data['plan_description'] ?? ''));
    $billingFrequencyValue = max(1, (int) ($data['billing_frequency_value'] ?? 1));
    $billingFrequencyUnit = billing_clean((string) ($data['billing_frequency_unit'] ?? 'Month(s)'));
    $billingCyclesMode = billing_clean((string) ($data['billing_cycles_mode'] ?? 'Auto-renews until cancelled'));
    $billingCyclesValue = trim((string) ($data['billing_cycles_value'] ?? '')) === '' ? null : max(1, (int) $data['billing_cycles_value']);
    $pricingModel = billing_clean((string) ($data['pricing_model'] ?? 'Per Unit'));
    $unitName = billing_clean((string) ($data['unit_name'] ?? ''));
    $price = (float) ($data['price'] ?? 0);
    $setupFee = (float) ($data['setup_fee'] ?? 0);
    $trialDays = max(0, (int) ($data['trial_days'] ?? 0));
    $itemType = strtolower((string) ($data['item_type'] ?? 'service')) === 'goods' ? 'Goods' : 'Service';
    $planAccount = billing_clean((string) ($data['plan_account'] ?? 'Sales'));
    $setupFeeAccount = billing_clean((string) ($data['setup_fee_account'] ?? 'Sales'));
    $pricingWidgetEnabled = !empty($data['pricing_widget_enabled']) ? 1 : 0;

    $conn = billing_db();
    if ($planId) {
        $stmt = $conn->prepare(
            'UPDATE catalog_plans
             SET product_item_id = ?, plan_name = ?, plan_code = ?, plan_description = ?, billing_frequency_value = ?, billing_frequency_unit = ?,
                 billing_cycles_mode = ?, billing_cycles_value = ?, pricing_model = ?, unit_name = ?, price = ?, setup_fee = ?, trial_days = ?,
                 item_type = ?, plan_account = ?, setup_fee_account = ?, pricing_widget_enabled = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }
        $stmt->bind_param(
            'isssississddisssiiii',
            $productId,
            $planName,
            $planCode,
            $description,
            $billingFrequencyValue,
            $billingFrequencyUnit,
            $billingCyclesMode,
            $billingCyclesValue,
            $pricingModel,
            $unitName,
            $price,
            $setupFee,
            $trialDays,
            $itemType,
            $planAccount,
            $setupFeeAccount,
            $pricingWidgetEnabled,
            $planId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        return $saved ? $planId : null;
    }

    $stmt = $conn->prepare(
        'INSERT INTO catalog_plans (
            user_id, organization_id, product_item_id, plan_name, plan_code, plan_description, status_label,
            billing_frequency_value, billing_frequency_unit, billing_cycles_mode, billing_cycles_value, pricing_model, unit_name, price,
            setup_fee, trial_days, item_type, plan_account, setup_fee_account, pricing_widget_enabled, is_inactive
         ) VALUES (?, ?, ?, ?, ?, ?, "Active", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)'
    );
    if ($stmt === false) {
        return null;
    }
    $stmt->bind_param(
        'iiisssississddisssi',
        $userId,
        $organizationId,
        $productId,
        $planName,
        $planCode,
        $description,
        $billingFrequencyValue,
        $billingFrequencyUnit,
        $billingCyclesMode,
        $billingCyclesValue,
        $pricingModel,
        $unitName,
        $price,
        $setupFee,
        $trialDays,
        $itemType,
        $planAccount,
        $setupFeeAccount,
        $pricingWidgetEnabled
    );
    $saved = $stmt->execute();
    $newId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $newId > 0 ? $newId : null;
}

function billing_get_addons(int $userId, int $organizationId, string $view = 'active'): array
{
    billing_ensure_product_catalog_support();

    $where = 'a.user_id = ? AND a.organization_id = ? AND COALESCE(a.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0';
    if ($view === 'active') {
        $where .= ' AND COALESCE(a.is_inactive, 0) = 0';
    } elseif ($view === 'inactive') {
        $where .= ' AND COALESCE(a.is_inactive, 0) = 1';
    }

    return billing_fetch_all(
        "SELECT a.*, i.item_name AS product_name
         FROM catalog_addons a
         INNER JOIN catalog_items i ON i.id = a.product_item_id
         WHERE {$where}
         ORDER BY a.created_at DESC, a.id DESC",
        'ii',
        [$userId, $organizationId]
    );
}

function billing_active_plans_for_product(int $userId, int $organizationId, int $productId): array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_all(
        'SELECT id, plan_name, plan_code
         FROM catalog_plans
         WHERE user_id = ? AND organization_id = ? AND product_item_id = ? AND COALESCE(is_deleted, 0) = 0 AND COALESCE(is_inactive, 0) = 0
         ORDER BY plan_name ASC, id ASC',
        'iii',
        [$userId, $organizationId, $productId]
    );
}

function billing_active_addons_for_product(int $userId, int $organizationId, int $productId): array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_all(
        'SELECT id, addon_name, addon_code, addon_type
         FROM catalog_addons
         WHERE user_id = ? AND organization_id = ? AND product_item_id = ? AND COALESCE(is_deleted, 0) = 0 AND COALESCE(is_inactive, 0) = 0
         ORDER BY addon_name ASC, id ASC',
        'iii',
        [$userId, $organizationId, $productId]
    );
}

function billing_get_addon(int $userId, int $organizationId, int $addonId): ?array
{
    billing_ensure_product_catalog_support();

    $addon = billing_fetch_one(
        'SELECT a.*, i.item_name AS product_name
         FROM catalog_addons a
         INNER JOIN catalog_items i ON i.id = a.product_item_id
         WHERE a.id = ? AND a.user_id = ? AND a.organization_id = ? AND COALESCE(a.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$addonId, $userId, $organizationId]
    );

    if (!$addon) {
        return null;
    }

    $addon['associate_plan_ids'] = json_decode((string) ($addon['associate_plan_ids'] ?? '[]'), true) ?: [];
    return $addon;
}

function billing_save_addon(int $userId, int $organizationId, array $data, ?int $addonId = null): ?int
{
    billing_ensure_product_catalog_support();

    $productId = (int) ($data['product_item_id'] ?? 0);
    $addonName = billing_clean((string) ($data['addon_name'] ?? ''));
    if ($productId <= 0 || $addonName === '') {
        return null;
    }

    $addonCode = billing_clean((string) ($data['addon_code'] ?? ''));
    $description = billing_clean((string) ($data['addon_description'] ?? ''));
    $addonType = billing_clean((string) ($data['addon_type'] ?? 'One Time'));
    $pricingModel = billing_clean((string) ($data['pricing_model'] ?? 'Package'));
    $billingFrequencyValue = trim((string) ($data['billing_frequency_value'] ?? '')) === '' ? null : max(1, (int) $data['billing_frequency_value']);
    $billingFrequencyUnit = billing_clean((string) ($data['billing_frequency_unit'] ?? 'Month(s)'));
    $unitName = billing_clean((string) ($data['unit_name'] ?? ''));
    $price = (float) ($data['price'] ?? 0);
    $itemType = strtolower((string) ($data['item_type'] ?? 'service')) === 'goods' ? 'Goods' : 'Service';
    $associatePlansMode = billing_clean((string) ($data['associate_plans_mode'] ?? 'All Plans'));
    $associatePlanIds = json_encode(array_values(array_map('intval', (array) ($data['associate_plan_ids'] ?? []))), JSON_UNESCAPED_UNICODE);
    $displayInWidgets = !empty($data['display_in_widgets']) ? 1 : 0;
    $allowPortalAssociate = !empty($data['allow_portal_associate']) ? 1 : 0;
    $accountName = billing_clean((string) ($data['account_name'] ?? 'Sales'));

    $conn = billing_db();
    if ($addonId) {
        $stmt = $conn->prepare(
            'UPDATE catalog_addons
             SET product_item_id = ?, addon_name = ?, addon_code = ?, addon_description = ?, addon_type = ?, pricing_model = ?,
                 billing_frequency_value = ?, billing_frequency_unit = ?, unit_name = ?, price = ?, item_type = ?, associate_plans_mode = ?,
                 associate_plan_ids = ?, display_in_widgets = ?, allow_portal_associate = ?, account_name = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }
        $stmt->bind_param('isssssissdsssiisiii', $productId, $addonName, $addonCode, $description, $addonType, $pricingModel, $billingFrequencyValue, $billingFrequencyUnit, $unitName, $price, $itemType, $associatePlansMode, $associatePlanIds, $displayInWidgets, $allowPortalAssociate, $accountName, $addonId, $userId, $organizationId);
        $saved = $stmt->execute();
        $stmt->close();
        return $saved ? $addonId : null;
    }

      $stmt = $conn->prepare(
          'INSERT INTO catalog_addons (
              user_id, organization_id, product_item_id, addon_name, addon_code, addon_description, status_label, addon_type,
              pricing_model, billing_frequency_value, billing_frequency_unit, unit_name, price, item_type, associate_plans_mode,
              associate_plan_ids, display_in_widgets, allow_portal_associate, account_name, is_inactive
           ) VALUES (?, ?, ?, ?, ?, ?, "Active", ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0)'
      );
    if ($stmt === false) {
        return null;
    }
    $stmt->bind_param('iiisssssissdsssiis', $userId, $organizationId, $productId, $addonName, $addonCode, $description, $addonType, $pricingModel, $billingFrequencyValue, $billingFrequencyUnit, $unitName, $price, $itemType, $associatePlansMode, $associatePlanIds, $displayInWidgets, $allowPortalAssociate, $accountName);
    $saved = $stmt->execute();
    $newId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $newId > 0 ? $newId : null;
}

function billing_get_pricing_widgets(int $userId, int $organizationId): array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_all(
        'SELECT w.*, i.item_name AS product_name
         FROM pricing_widgets w
         INNER JOIN catalog_items i ON i.id = w.product_item_id
         WHERE w.user_id = ? AND w.organization_id = ? AND COALESCE(w.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0
         ORDER BY w.created_at DESC, w.id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_product_has_pricing_widget_plan(int $userId, int $organizationId, int $productId): bool
{
    billing_ensure_product_catalog_support();

    $row = billing_fetch_one(
        'SELECT id
           FROM catalog_plans
           WHERE user_id = ? AND organization_id = ? AND product_item_id = ? AND COALESCE(is_deleted, 0) = 0 AND COALESCE(is_inactive, 0) = 0 AND COALESCE(pricing_widget_enabled, 0) = 1
         LIMIT 1',
        'iii',
        [$userId, $organizationId, $productId]
    );

    return $row !== null;
}

function billing_create_pricing_widget(int $userId, int $organizationId, string $name, int $productId): ?int
{
    billing_ensure_product_catalog_support();

    $name = billing_clean($name);
    if ($name === '' || $productId <= 0) {
        return null;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'INSERT INTO pricing_widgets (user_id, organization_id, widget_name, product_item_id, status_label)
         VALUES (?, ?, ?, ?, "Active")'
    );

    if ($stmt === false) {
        return null;
    }

    $stmt->bind_param('iisi', $userId, $organizationId, $name, $productId);
    $saved = $stmt->execute();
    $widgetId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $widgetId > 0 ? $widgetId : null;
}

function billing_get_coupons(int $userId, int $organizationId): array
{
    billing_ensure_product_catalog_support();

    return billing_fetch_all(
        'SELECT c.*, i.item_name AS product_name
         FROM coupons c
         INNER JOIN catalog_items i ON i.id = c.product_item_id
         WHERE c.user_id = ? AND c.organization_id = ? AND COALESCE(c.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0
         ORDER BY c.created_at DESC, c.id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_coupon(int $userId, int $organizationId, int $couponId): ?array
{
    billing_ensure_product_catalog_support();

    $coupon = billing_fetch_one(
        'SELECT c.*, i.item_name AS product_name
         FROM coupons c
         INNER JOIN catalog_items i ON i.id = c.product_item_id
         WHERE c.id = ? AND c.user_id = ? AND c.organization_id = ? AND COALESCE(c.is_deleted, 0) = 0 AND COALESCE(i.is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$couponId, $userId, $organizationId]
    );

    if (!$coupon) {
        return null;
    }

    $coupon['associate_plan_ids'] = json_decode((string) ($coupon['associate_plan_ids'] ?? '[]'), true) ?: [];
    $coupon['associate_addon_ids'] = json_decode((string) ($coupon['associate_addon_ids'] ?? '[]'), true) ?: [];

    return $coupon;
}

function billing_save_coupon(int $userId, int $organizationId, array $data, ?int $couponId = null): ?int
{
    billing_ensure_product_catalog_support();

    $productId = (int) ($data['product_item_id'] ?? 0);
    $couponName = billing_clean((string) ($data['coupon_name'] ?? ''));
    $couponCode = billing_clean((string) ($data['coupon_code'] ?? ''));
    if ($productId <= 0 || $couponName === '' || $couponCode === '') {
        return null;
    }

    $discountValue = (float) ($data['discount_value'] ?? 0);
    $discountBy = billing_clean((string) ($data['discount_by'] ?? 'Flat'));
    $redemptionType = billing_clean((string) ($data['redemption_type'] ?? 'One-Time'));
    $associatePlansMode = billing_clean((string) ($data['associate_plans_mode'] ?? 'All Plans'));
    $associatePlanIds = json_encode(array_values(array_map('intval', (array) ($data['associate_plan_ids'] ?? []))), JSON_UNESCAPED_UNICODE);
    $associateAddonsMode = billing_clean((string) ($data['associate_addons_mode'] ?? 'All Recurring Addons'));
    $associateAddonIds = json_encode(array_values(array_map('intval', (array) ($data['associate_addon_ids'] ?? []))), JSON_UNESCAPED_UNICODE);
    $expirationDate = billing_clean((string) ($data['expiration_date'] ?? ''));
    $expirationDate = $expirationDate !== '' ? $expirationDate : null;
    $maximumRedemptionsRaw = trim((string) ($data['maximum_redemptions'] ?? ''));
    $maximumRedemptions = $maximumRedemptionsRaw === '' ? null : max(1, (int) $maximumRedemptionsRaw);
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Active'));

    $conn = billing_db();
    if ($couponId) {
        $stmt = $conn->prepare(
            'UPDATE coupons
             SET product_item_id = ?, coupon_name = ?, coupon_code = ?, discount_value = ?, discount_by = ?, redemption_type = ?,
                 associate_plans_mode = ?, associate_plan_ids = ?, associate_addons_mode = ?, associate_addon_ids = ?,
                 expiration_date = ?, maximum_redemptions = ?, status_label = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }
        $stmt->bind_param(
            'issdsssssssisiii',
            $productId,
            $couponName,
            $couponCode,
            $discountValue,
            $discountBy,
            $redemptionType,
            $associatePlansMode,
            $associatePlanIds,
            $associateAddonsMode,
            $associateAddonIds,
            $expirationDate,
            $maximumRedemptions,
            $statusLabel,
            $couponId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        return $saved ? $couponId : null;
    }

    $stmt = $conn->prepare(
        'INSERT INTO coupons (
            user_id, organization_id, product_item_id, coupon_name, coupon_code, discount_value, discount_by, redemption_type,
            associate_plans_mode, associate_plan_ids, associate_addons_mode, associate_addon_ids, expiration_date, maximum_redemptions, status_label
         ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
    );
    if ($stmt === false) {
        return null;
    }
    $stmt->bind_param(
        'iiissdsssssssis',
        $userId,
        $organizationId,
        $productId,
        $couponName,
        $couponCode,
        $discountValue,
        $discountBy,
        $redemptionType,
        $associatePlansMode,
        $associatePlanIds,
        $associateAddonsMode,
        $associateAddonIds,
        $expirationDate,
        $maximumRedemptions,
        $statusLabel
    );
    $saved = $stmt->execute();
    $newId = $saved ? (int) $stmt->insert_id : 0;
    $stmt->close();

    return $newId > 0 ? $newId : null;
}

function billing_set_coupon_status(int $userId, int $organizationId, int $couponId, string $statusLabel): bool
{
    billing_ensure_product_catalog_support();

    $statusLabel = billing_clean($statusLabel);
    if (!in_array($statusLabel, ['Active', 'Inactive'], true)) {
        return false;
    }

    $conn = billing_db();
    $stmt = $conn->prepare(
        'UPDATE coupons
         SET status_label = ?
         WHERE id = ? AND user_id = ? AND organization_id = ?
         LIMIT 1'
    );
    if ($stmt === false) {
        return false;
    }

    $stmt->bind_param('siii', $statusLabel, $couponId, $userId, $organizationId);
    $saved = $stmt->execute();
    $stmt->close();

    return $saved;
}

function billing_sales_customer_options(int $userId, int $organizationId): array
{
    billing_ensure_sales_module_support();

    $customers = billing_fetch_all(
        'SELECT id, customer_name, email
         FROM customers
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY customer_name ASC, id ASC',
        'ii',
        [$userId, $organizationId]
    );

    foreach ($customers as &$customer) {
        $meta = billing_customer_meta((int) ($customer['id'] ?? 0));
        $customer['billing_meta'] = $meta;
        $customer['shipping_meta'] = $meta;
    }
    unset($customer);

    return $customers;
}

function billing_sales_customer_snapshot(int $userId, int $organizationId, int $customerId): ?array
{
    $customer = billing_fetch_one(
        'SELECT id, customer_name, email
         FROM customers
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$customerId, $userId, $organizationId]
    );

    if (!$customer) {
        return null;
    }

    $meta = billing_customer_meta($customerId);

    return [
        'id' => (int) $customer['id'],
        'name' => (string) ($customer['customer_name'] ?? ''),
        'email' => (string) ($customer['email'] ?? ''),
        'billing' => [
            'attention' => (string) ($meta['billing_attention'] ?? ''),
            'country' => (string) ($meta['billing_country'] ?? ''),
            'address_1' => (string) ($meta['billing_address_1'] ?? ''),
            'address_2' => (string) ($meta['billing_address_2'] ?? ''),
            'city' => (string) ($meta['billing_city'] ?? ''),
            'state' => (string) ($meta['billing_state'] ?? ''),
            'pin' => (string) ($meta['billing_pin'] ?? ''),
            'phone' => (string) ($meta['billing_phone'] ?? ($meta['mobile'] ?? '')),
        ],
        'shipping' => [
            'attention' => (string) ($meta['shipping_attention'] ?? ''),
            'country' => (string) ($meta['shipping_country'] ?? ''),
            'address_1' => (string) ($meta['shipping_address_1'] ?? ''),
            'address_2' => (string) ($meta['shipping_address_2'] ?? ''),
            'city' => (string) ($meta['shipping_city'] ?? ''),
            'state' => (string) ($meta['shipping_state'] ?? ''),
            'pin' => (string) ($meta['shipping_pin'] ?? ''),
            'phone' => (string) ($meta['shipping_phone'] ?? ($meta['mobile'] ?? '')),
        ],
    ];
}

function billing_expense_customer_options(int $userId, int $organizationId): array
{
    return billing_sales_customer_options($userId, $organizationId);
}

function billing_get_expenses(int $userId, int $organizationId): array
{
    billing_ensure_expense_support();

    $rows = billing_fetch_all(
        'SELECT id, expense_date, category_name, expense_account, reference_number, customer_name, status_label, total_amount, attachment_paths, created_at
         FROM expenses
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY expense_date DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );

    foreach ($rows as &$row) {
        $attachments = json_decode((string) ($row['attachment_paths'] ?? '[]'), true);
        $row['attachments'] = is_array($attachments) ? $attachments : [];
    }
    unset($row);

    return $rows;
}

function billing_get_expense(int $userId, int $organizationId, int $expenseId): ?array
{
    billing_ensure_expense_support();

    $expense = billing_fetch_one(
        'SELECT *
         FROM expenses
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$expenseId, $userId, $organizationId]
    );

    if (!$expense) {
        return null;
    }

    $attachments = json_decode((string) ($expense['attachment_paths'] ?? '[]'), true);
    $expense['attachments'] = is_array($attachments) ? $attachments : [];
    $expense['items'] = billing_fetch_all(
        'SELECT id, expense_account, notes_text, amount, sort_order
         FROM expense_line_items
         WHERE expense_id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY sort_order ASC, id ASC',
        'iii',
        [$expenseId, $userId, $organizationId]
    );

    return $expense;
}

function billing_save_expense(int $userId, int $organizationId, array $data, ?int $expenseId = null): ?int
{
    billing_ensure_expense_support();

    $expenseDate = billing_clean((string) ($data['expense_date'] ?? ''));
    $categoryName = billing_clean((string) ($data['category_name'] ?? ''));
    $expenseAccount = billing_clean((string) ($data['expense_account'] ?? $categoryName));
    $currencyCode = billing_clean((string) ($data['currency_code'] ?? 'INR'));
    $invoiceNumber = billing_clean((string) ($data['invoice_number'] ?? ''));
    $referenceNumber = billing_clean((string) ($data['reference_number'] ?? ''));
    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'NON-BILLABLE'));
    $notesText = billing_clean((string) ($data['notes_text'] ?? ''));
    $isItemized = !empty($data['is_itemized']) ? 1 : 0;
    $attachments = array_values(array_filter(is_array($data['attachments'] ?? null) ? $data['attachments'] : []));
    $items = is_array($data['items'] ?? null) ? $data['items'] : [];

    $totalAmount = 0.0;
    $normalizedItems = [];
    foreach ($items as $index => $item) {
        $itemAccount = billing_clean((string) ($item['expense_account'] ?? ''));
        $itemNotes = billing_clean((string) ($item['notes_text'] ?? ''));
        $itemAmountRaw = billing_clean((string) ($item['amount'] ?? ''));
        $itemAmount = is_numeric($itemAmountRaw) ? (float) $itemAmountRaw : 0.0;
        if ($itemAccount === '' && $itemNotes === '' && $itemAmount <= 0) {
            continue;
        }

        $normalizedItems[] = [
            'expense_account' => $itemAccount,
            'notes_text' => $itemNotes,
            'amount' => $itemAmount,
            'sort_order' => $index,
        ];
        $totalAmount += $itemAmount;
    }

    if (!$isItemized || $normalizedItems === []) {
        $amountRaw = billing_clean((string) ($data['total_amount'] ?? '0'));
        $totalAmount = is_numeric($amountRaw) ? (float) $amountRaw : 0.0;
        $normalizedItems = [];
    }

    $attachmentJson = json_encode($attachments, JSON_UNESCAPED_SLASHES);
    if ($attachmentJson === false) {
        $attachmentJson = '[]';
    }

    $conn = billing_db();

    if ($expenseId !== null && $expenseId > 0) {
        $stmt = $conn->prepare(
            'UPDATE expenses
             SET expense_date = ?, category_name = ?, expense_account = ?, currency_code = ?, total_amount = ?, invoice_number = ?, reference_number = ?, customer_name = ?, status_label = ?, notes_text = ?, attachment_paths = ?, is_itemized = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?'
        );
        if ($stmt === false) {
            return null;
        }
        $stmt->bind_param(
            'ssssdssssssiiii',
            $expenseDate,
            $categoryName,
            $expenseAccount,
            $currencyCode,
            $totalAmount,
            $invoiceNumber,
            $referenceNumber,
            $customerName,
            $statusLabel,
            $notesText,
            $attachmentJson,
            $isItemized,
            $expenseId,
            $userId,
            $organizationId
        );
        $ok = $stmt->execute();
        $stmt->close();
        if (!$ok) {
            return null;
        }
        $savedId = $expenseId;
        $deleteStmt = $conn->prepare('DELETE FROM expense_line_items WHERE expense_id = ? AND user_id = ? AND organization_id = ?');
        if ($deleteStmt) {
            $deleteStmt->bind_param('iii', $savedId, $userId, $organizationId);
            $deleteStmt->execute();
            $deleteStmt->close();
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO expenses
             (user_id, organization_id, expense_date, category_name, expense_account, currency_code, total_amount, invoice_number, reference_number, customer_name, status_label, notes_text, attachment_paths, is_itemized)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }
        $stmt->bind_param(
            'iissssdssssssi',
            $userId,
            $organizationId,
            $expenseDate,
            $categoryName,
            $expenseAccount,
            $currencyCode,
            $totalAmount,
            $invoiceNumber,
            $referenceNumber,
            $customerName,
            $statusLabel,
            $notesText,
            $attachmentJson,
            $isItemized
        );
        $ok = $stmt->execute();
        $savedId = $ok ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if (!$ok || $savedId <= 0) {
            return null;
        }
    }

    if ($normalizedItems !== []) {
        $itemStmt = $conn->prepare(
            'INSERT INTO expense_line_items
             (expense_id, user_id, organization_id, expense_account, notes_text, amount, sort_order)
             VALUES (?, ?, ?, ?, ?, ?, ?)'
        );
        if ($itemStmt === false) {
            return $savedId;
        }
        foreach ($normalizedItems as $item) {
            $itemStmt->bind_param(
                'iiissdi',
                $savedId,
                $userId,
                $organizationId,
                $item['expense_account'],
                $item['notes_text'],
                $item['amount'],
                $item['sort_order']
            );
            $itemStmt->execute();
        }
        $itemStmt->close();
    }

    return $savedId;
}

function billing_next_document_number(int $userId, int $organizationId, string $tableName, string $columnName, string $prefix, int $padLength = 5): string
{
    billing_ensure_sales_module_support();

    $row = billing_fetch_one(
        "SELECT {$columnName} AS document_number
         FROM {$tableName}
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id DESC
         LIMIT 1",
        'ii',
        [$userId, $organizationId]
    );

    $nextNumber = 1;
    $current = (string) ($row['document_number'] ?? '');
    if ($current !== '') {
        if (preg_match('/(\d+)$/', $current, $matches)) {
            $nextNumber = ((int) $matches[1]) + 1;
        } else {
            $nextNumber = 2;
        }
    }

    return $prefix . str_pad((string) $nextNumber, $padLength, '0', STR_PAD_LEFT);
}

function billing_sales_normalize_items(array $items): array
{
    $normalized = [];
    foreach ($items as $item) {
        $name = billing_clean((string) ($item['item_name'] ?? ''));
        $description = billing_clean((string) ($item['item_description'] ?? ''));
        $quantity = (float) ($item['quantity'] ?? 0);
        $rate = (float) ($item['rate'] ?? 0);
        $unitName = billing_clean((string) ($item['unit_name'] ?? ''));
        $accountName = billing_clean((string) ($item['account_name'] ?? 'Sales'));
        $itemId = (int) ($item['item_id'] ?? 0);
        $itemRole = billing_clean((string) ($item['item_role'] ?? 'plan'));

        if ($name === '' && $description === '' && $quantity <= 0 && $rate <= 0) {
            continue;
        }

        if ($name === '') {
            continue;
        }

        if ($quantity <= 0) {
            $quantity = 1;
        }

        $normalized[] = [
            'item_id' => $itemId > 0 ? $itemId : null,
            'item_name' => $name,
            'item_description' => $description,
            'quantity' => $quantity,
            'unit_name' => $unitName,
            'rate' => $rate,
            'amount' => $quantity * $rate,
            'account_name' => $accountName !== '' ? $accountName : 'Sales',
            'item_role' => $itemRole !== '' ? $itemRole : 'plan',
        ];
    }

    return $normalized;
}

function billing_sales_calculate_totals(array $items, float $discountAmount = 0, float $adjustmentAmount = 0, float $taxTotal = 0): array
{
    $subTotal = 0.0;
    foreach ($items as $item) {
        $subTotal += (float) ($item['amount'] ?? 0);
    }

    $totalAmount = $subTotal - $discountAmount + $adjustmentAmount + $taxTotal;

    return [
        'sub_total' => $subTotal,
        'discount_amount' => $discountAmount,
        'adjustment_amount' => $adjustmentAmount,
        'tax_total' => $taxTotal,
        'total_amount' => max(0, $totalAmount),
    ];
}

function billing_sales_fetch_items(string $tableName, string $foreignKey, int $recordId, int $userId, int $organizationId): array
{
    billing_ensure_sales_module_support();

    return billing_fetch_all(
        "SELECT *
         FROM {$tableName}
         WHERE {$foreignKey} = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY id ASC",
        'iii',
        [$recordId, $userId, $organizationId]
    );
}

function billing_sales_replace_items(
    string $tableName,
    string $foreignKey,
    int $recordId,
    int $userId,
    int $organizationId,
    array $items,
    bool $supportsAccount = true,
    bool $supportsRole = false
): void {
    billing_ensure_sales_module_support();

    $conn = billing_db();
    $deleteStmt = $conn->prepare(
        "DELETE FROM {$tableName}
         WHERE {$foreignKey} = ? AND user_id = ? AND organization_id = ?"
    );

    if ($deleteStmt !== false) {
        $deleteStmt->bind_param('iii', $recordId, $userId, $organizationId);
        $deleteStmt->execute();
        $deleteStmt->close();
    }

    foreach ($items as $item) {
        if ($supportsAccount && $supportsRole) {
            $stmt = $conn->prepare(
                "INSERT INTO {$tableName}
                 ({$foreignKey}, user_id, organization_id, item_id, item_name, item_description, quantity, unit_name, rate, amount, account_name, item_role)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            if ($stmt === false) {
                continue;
            }

            $itemId = $item['item_id'];
            $itemName = $item['item_name'];
            $description = $item['item_description'];
            $quantity = $item['quantity'];
            $unitName = $item['unit_name'];
            $rate = $item['rate'];
            $amount = $item['amount'];
            $accountName = $item['account_name'];
            $itemRole = $item['item_role'];
            $stmt->bind_param('iiiissdssddd', $recordId, $userId, $organizationId, $itemId, $itemName, $description, $quantity, $unitName, $rate, $amount, $accountName, $itemRole);
            $stmt->execute();
            $stmt->close();
            continue;
        }

        if ($supportsAccount) {
            $stmt = $conn->prepare(
                "INSERT INTO {$tableName}
                 ({$foreignKey}, user_id, organization_id, item_id, item_name, item_description, quantity, unit_name, rate, amount, account_name)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            if ($stmt === false) {
                continue;
            }

            $itemId = $item['item_id'];
            $itemName = $item['item_name'];
            $description = $item['item_description'];
            $quantity = $item['quantity'];
            $unitName = $item['unit_name'];
            $rate = $item['rate'];
            $amount = $item['amount'];
            $accountName = $item['account_name'];
            $stmt->bind_param('iiiissdssdd', $recordId, $userId, $organizationId, $itemId, $itemName, $description, $quantity, $unitName, $rate, $amount, $accountName);
            $stmt->execute();
            $stmt->close();
            continue;
        }

        if ($supportsRole) {
            $stmt = $conn->prepare(
                "INSERT INTO {$tableName}
                 ({$foreignKey}, user_id, organization_id, item_id, item_name, item_description, quantity, unit_name, rate, amount, item_role)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
            );
            if ($stmt === false) {
                continue;
            }

            $itemId = $item['item_id'];
            $itemName = $item['item_name'];
            $description = $item['item_description'];
            $quantity = $item['quantity'];
            $unitName = $item['unit_name'];
            $rate = $item['rate'];
            $amount = $item['amount'];
            $itemRole = $item['item_role'];
            $stmt->bind_param('iiiissdssdd', $recordId, $userId, $organizationId, $itemId, $itemName, $description, $quantity, $unitName, $rate, $amount, $itemRole);
            $stmt->execute();
            $stmt->close();
            continue;
        }

        $stmt = $conn->prepare(
            "INSERT INTO {$tableName}
             ({$foreignKey}, user_id, organization_id, item_id, item_name, item_description, quantity, unit_name, rate, amount)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );
        if ($stmt === false) {
            continue;
        }

        $itemId = $item['item_id'];
        $itemName = $item['item_name'];
        $description = $item['item_description'];
        $quantity = $item['quantity'];
        $unitName = $item['unit_name'];
        $rate = $item['rate'];
        $amount = $item['amount'];
        $stmt->bind_param('iiiissdssd', $recordId, $userId, $organizationId, $itemId, $itemName, $description, $quantity, $unitName, $rate, $amount);
        $stmt->execute();
        $stmt->close();
    }
}

function billing_get_quotes(int $userId, int $organizationId): array
{
    return billing_fetch_all(
        'SELECT *
         FROM quotes
         WHERE user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_quote(int $userId, int $organizationId, int $quoteId): ?array
{
    $quote = billing_fetch_one(
        'SELECT *
         FROM quotes
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$quoteId, $userId, $organizationId]
    );

    if ($quote) {
        $quote['items'] = billing_sales_fetch_items('quote_items', 'quote_id', $quoteId, $userId, $organizationId);
    }

    return $quote;
}

function billing_save_quote(int $userId, int $organizationId, array $data, ?int $quoteId = null): ?int
{
    billing_ensure_sales_module_support();

    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $quoteNumber = billing_clean((string) ($data['quote_number'] ?? ''));
    if ($customerName === '' || $quoteNumber === '') {
        return null;
    }

    $items = billing_sales_normalize_items($data['items'] ?? []);
    if ($items === []) {
        return null;
    }

    $totals = billing_sales_calculate_totals(
        $items,
        (float) ($data['discount_amount'] ?? 0),
        (float) ($data['adjustment_amount'] ?? 0),
        (float) ($data['tax_total'] ?? 0)
    );

    $customerId = (int) ($data['customer_id'] ?? 0);
    $referenceNumber = billing_clean((string) ($data['reference_number'] ?? ''));
    $salespersonName = billing_clean((string) ($data['salesperson_name'] ?? ''));
    $projectName = billing_clean((string) ($data['project_name'] ?? ''));
    $subject = billing_clean((string) ($data['subject'] ?? ''));
    $quoteDate = billing_clean((string) ($data['quote_date'] ?? ''));
    $expiryDate = billing_clean((string) ($data['expiry_date'] ?? ''));
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Draft'));
    $quoteType = billing_clean((string) ($data['quote_type'] ?? 'Quote'));
    $customerNotes = billing_clean((string) ($data['customer_notes'] ?? ''));
    $termsText = billing_clean((string) ($data['terms_text'] ?? ''));
    $pdfTemplateName = billing_clean((string) ($data['pdf_template_name'] ?? 'Spreadsheet Template'));

    $conn = billing_db();
    if ($quoteId) {
        $stmt = $conn->prepare(
            'UPDATE quotes
             SET customer_id = ?, quote_number = ?, customer_name = ?, reference_number = ?, salesperson_name = ?, project_name = ?, subject = ?,
                 quote_date = ?, expiry_date = ?, sub_total = ?, discount_amount = ?, adjustment_amount = ?, tax_total = ?, total_amount = ?,
                 status_label = ?, quote_type = ?, customer_notes = ?, terms_text = ?, pdf_template_name = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'issssssssddddddsssiii',
            $customerId,
            $quoteNumber,
            $customerName,
            $referenceNumber,
            $salespersonName,
            $projectName,
            $subject,
            $quoteDate,
            $expiryDate,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $totals['total_amount'],
            $statusLabel,
            $quoteType,
            $customerNotes,
            $termsText,
            $pdfTemplateName,
            $quoteId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            return null;
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO quotes
             (user_id, organization_id, customer_id, quote_number, customer_name, reference_number, salesperson_name, project_name, subject, quote_date, expiry_date, sub_total, discount_amount, adjustment_amount, tax_total, total_amount, status_label, quote_type, customer_notes, terms_text, pdf_template_name)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iiissssssssddddddssss',
            $userId,
            $organizationId,
            $customerId,
            $quoteNumber,
            $customerName,
            $referenceNumber,
            $salespersonName,
            $projectName,
            $subject,
            $quoteDate,
            $expiryDate,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $totals['total_amount'],
            $statusLabel,
            $quoteType,
            $customerNotes,
            $termsText,
            $pdfTemplateName
        );
        $saved = $stmt->execute();
        $quoteId = $saved ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if ($quoteId <= 0) {
            return null;
        }
    }

    billing_sales_replace_items('quote_items', 'quote_id', (int) $quoteId, $userId, $organizationId, $items, true, false);
    return (int) $quoteId;
}

function billing_get_invoices(int $userId, int $organizationId): array
{
    return billing_fetch_all(
        'SELECT invoices.*,
                COALESCE(customers.email, "") AS customer_email,
                COALESCE(customers.outstanding_amount, invoices.total_amount) AS balance_amount
         FROM invoices
         LEFT JOIN customers
           ON customers.id = invoices.customer_id
          AND customers.user_id = invoices.user_id
          AND customers.organization_id = invoices.organization_id
         WHERE invoices.user_id = ? AND invoices.organization_id = ? AND COALESCE(invoices.is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_invoice(int $userId, int $organizationId, int $invoiceId): ?array
{
    $invoice = billing_fetch_one(
        'SELECT *
         FROM invoices
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$invoiceId, $userId, $organizationId]
    );

    if ($invoice) {
        $invoice['items'] = billing_sales_fetch_items('invoice_items', 'invoice_id', $invoiceId, $userId, $organizationId);
    }

    return $invoice;
}

function billing_save_invoice(int $userId, int $organizationId, array $data, ?int $invoiceId = null): ?int
{
    billing_ensure_sales_module_support();

    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $invoiceNumber = billing_clean((string) ($data['invoice_number'] ?? ''));
    if ($customerName === '' || $invoiceNumber === '') {
        return null;
    }

    $items = billing_sales_normalize_items($data['items'] ?? []);
    if ($items === []) {
        return null;
    }

    $totals = billing_sales_calculate_totals(
        $items,
        (float) ($data['discount_amount'] ?? 0),
        (float) ($data['adjustment_amount'] ?? 0),
        (float) ($data['tax_total'] ?? 0)
    );

    $customerId = (int) ($data['customer_id'] ?? 0);
    $orderNumber = billing_clean((string) ($data['order_number'] ?? ''));
    $salespersonName = billing_clean((string) ($data['salesperson_name'] ?? ''));
    $subject = billing_clean((string) ($data['subject'] ?? ''));
    $invoiceDate = billing_clean((string) ($data['invoice_date'] ?? ''));
    $dueDate = billing_clean((string) ($data['due_date'] ?? ''));
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Draft'));
    $invoiceType = billing_clean((string) ($data['invoice_type'] ?? 'one_time'));
    $notesText = billing_clean((string) ($data['notes_text'] ?? ''));
    $termsText = billing_clean((string) ($data['terms_text'] ?? ''));
    $pdfTemplateName = billing_clean((string) ($data['pdf_template_name'] ?? 'Spreadsheet Template'));

    $conn = billing_db();
    if ($invoiceId) {
        $stmt = $conn->prepare(
            'UPDATE invoices
             SET customer_id = ?, invoice_number = ?, customer_name = ?, order_number = ?, salesperson_name = ?, subject = ?, total_amount = ?, sub_total = ?, discount_amount = ?, adjustment_amount = ?, tax_total = ?, status_label = ?, invoice_type = ?, invoice_date = ?, due_date = ?, notes_text = ?, terms_text = ?, pdf_template_name = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'isssssddddsssssssiii',
            $customerId,
            $invoiceNumber,
            $customerName,
            $orderNumber,
            $salespersonName,
            $subject,
            $totals['total_amount'],
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $statusLabel,
            $invoiceType,
            $invoiceDate,
            $dueDate,
            $notesText,
            $termsText,
            $pdfTemplateName,
            $invoiceId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            return null;
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO invoices
             (user_id, organization_id, invoice_number, customer_name, customer_id, order_number, salesperson_name, subject, total_amount, sub_total, discount_amount, adjustment_amount, tax_total, status_label, invoice_type, invoice_date, due_date, notes_text, terms_text, pdf_template_name)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iississsdddddsssssss',
            $userId,
            $organizationId,
            $invoiceNumber,
            $customerName,
            $customerId,
            $orderNumber,
            $salespersonName,
            $subject,
            $totals['total_amount'],
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $statusLabel,
            $invoiceType,
            $invoiceDate,
            $dueDate,
            $notesText,
            $termsText,
            $pdfTemplateName
        );
        $saved = $stmt->execute();
        $invoiceId = $saved ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if ($invoiceId <= 0) {
            return null;
        }
    }

    billing_sales_replace_items('invoice_items', 'invoice_id', (int) $invoiceId, $userId, $organizationId, $items, true, false);
    return (int) $invoiceId;
}

function billing_get_subscriptions(int $userId, int $organizationId): array
{
    return billing_fetch_all(
        'SELECT subscriptions.*,
                COALESCE(customers.email, "") AS customer_email
         FROM subscriptions
         LEFT JOIN customers
           ON customers.id = subscriptions.customer_id
          AND customers.user_id = subscriptions.user_id
          AND customers.organization_id = subscriptions.organization_id
         WHERE subscriptions.user_id = ? AND subscriptions.organization_id = ? AND COALESCE(subscriptions.is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_subscription(int $userId, int $organizationId, int $subscriptionId): ?array
{
    $subscription = billing_fetch_one(
        'SELECT *
         FROM subscriptions
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$subscriptionId, $userId, $organizationId]
    );

    if ($subscription) {
        $subscription['items'] = billing_sales_fetch_items('subscription_items', 'subscription_id', $subscriptionId, $userId, $organizationId);
    }

    return $subscription;
}

function billing_save_subscription(int $userId, int $organizationId, array $data, ?int $subscriptionId = null): ?int
{
    billing_ensure_sales_module_support();

    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $subscriptionNumber = billing_clean((string) ($data['subscription_number'] ?? ''));
    if ($customerName === '' || $subscriptionNumber === '') {
        return null;
    }

    $items = billing_sales_normalize_items($data['items'] ?? []);
    if ($items === []) {
        return null;
    }

    $totals = billing_sales_calculate_totals($items, 0, 0, 0);

    $customerId = (int) ($data['customer_id'] ?? 0);
    $planName = billing_clean((string) ($data['plan_name'] ?? ($items[0]['item_name'] ?? '')));
    $referenceNumber = billing_clean((string) ($data['reference_number'] ?? ''));
    $salespersonName = billing_clean((string) ($data['salesperson_name'] ?? ''));
    $startDate = billing_clean((string) ($data['start_date'] ?? date('Y-m-d')));
    $frequencyValue = max(1, (int) ($data['billing_frequency_value'] ?? 1));
    $frequencyUnit = billing_clean((string) ($data['billing_frequency_unit'] ?? 'week(s)'));
    $renewsForever = !empty($data['renews_forever']) ? 1 : 0;
    $expiresAfterCycles = $renewsForever ? null : max(1, (int) ($data['expires_after_cycles'] ?? 1));
    $paymentTerms = billing_clean((string) ($data['payment_terms'] ?? 'Due on Receipt'));
    $paymentMode = billing_clean((string) ($data['payment_mode'] ?? 'offline'));
    $autochargeEnabled = !empty($data['autocharge_enabled']) ? 1 : 0;
    $prorateEnabled = !empty($data['prorate_enabled']) ? 1 : 0;
    $generateInvoiceOnProrate = !empty($data['generate_invoice_on_prorate']) ? 1 : 0;
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Live'));
    $customerNotes = billing_clean((string) ($data['customer_notes'] ?? 'Thanks for your business.'));
    $termsText = billing_clean((string) ($data['terms_text'] ?? ''));
    $startTimestamp = strtotime($startDate) ?: time();
    $currentCycleStart = date('Y-m-d', $startTimestamp);
    $nextBillingDate = date('Y-m-d', strtotime('+' . $frequencyValue . ' ' . str_replace(['(s)', 's)'], ['', ''], $frequencyUnit), $startTimestamp));
    $currentCycleEnd = $nextBillingDate;

    $conn = billing_db();
    if ($subscriptionId) {
        $stmt = $conn->prepare(
            'UPDATE subscriptions
             SET customer_id = ?, subscription_number = ?, customer_name = ?, plan_name = ?, reference_number = ?, salesperson_name = ?, start_date = ?, next_billing_date = ?, current_cycle_start = ?, current_cycle_end = ?, billing_frequency_value = ?, billing_frequency_unit = ?, expires_after_cycles = ?, renews_forever = ?, payment_terms = ?, payment_mode = ?, autocharge_enabled = ?, prorate_enabled = ?, generate_invoice_on_prorate = ?, sub_total = ?, tax_total = ?, total_amount = ?, status_label = ?, customer_notes = ?, terms_text = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'isssssssssisiissiiidddsssiii',
            $customerId,
            $subscriptionNumber,
            $customerName,
            $planName,
            $referenceNumber,
            $salespersonName,
            $startDate,
            $nextBillingDate,
            $currentCycleStart,
            $currentCycleEnd,
            $frequencyValue,
            $frequencyUnit,
            $expiresAfterCycles,
            $renewsForever,
            $paymentTerms,
            $paymentMode,
            $autochargeEnabled,
            $prorateEnabled,
            $generateInvoiceOnProrate,
            $totals['sub_total'],
            $totals['tax_total'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText,
            $subscriptionId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            return null;
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO subscriptions
             (user_id, organization_id, customer_id, subscription_number, customer_name, plan_name, reference_number, salesperson_name, start_date, next_billing_date, current_cycle_start, current_cycle_end, billing_frequency_value, billing_frequency_unit, expires_after_cycles, renews_forever, payment_terms, payment_mode, autocharge_enabled, prorate_enabled, generate_invoice_on_prorate, sub_total, tax_total, total_amount, status_label, customer_notes, terms_text)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iiisssssssssisiissiiidddsss',
            $userId,
            $organizationId,
            $customerId,
            $subscriptionNumber,
            $customerName,
            $planName,
            $referenceNumber,
            $salespersonName,
            $startDate,
            $nextBillingDate,
            $currentCycleStart,
            $currentCycleEnd,
            $frequencyValue,
            $frequencyUnit,
            $expiresAfterCycles,
            $renewsForever,
            $paymentTerms,
            $paymentMode,
            $autochargeEnabled,
            $prorateEnabled,
            $generateInvoiceOnProrate,
            $totals['sub_total'],
            $totals['tax_total'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText
        );
        $saved = $stmt->execute();
        $subscriptionId = $saved ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if ($subscriptionId <= 0) {
            return null;
        }
    }

    billing_sales_replace_items('subscription_items', 'subscription_id', (int) $subscriptionId, $userId, $organizationId, $items, false, true);
    return (int) $subscriptionId;
}

function billing_get_delivery_challans(int $userId, int $organizationId): array
{
    return billing_fetch_all(
        'SELECT delivery_challans.*,
                COALESCE(customers.email, "") AS customer_email
         FROM delivery_challans
         LEFT JOIN customers
           ON customers.id = delivery_challans.customer_id
          AND customers.user_id = delivery_challans.user_id
          AND customers.organization_id = delivery_challans.organization_id
         WHERE delivery_challans.user_id = ? AND delivery_challans.organization_id = ? AND COALESCE(delivery_challans.is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_delivery_challan(int $userId, int $organizationId, int $challanId): ?array
{
    $challan = billing_fetch_one(
        'SELECT *
         FROM delivery_challans
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$challanId, $userId, $organizationId]
    );

    if ($challan) {
        $challan['items'] = billing_sales_fetch_items('delivery_challan_items', 'delivery_challan_id', $challanId, $userId, $organizationId);
    }

    return $challan;
}

function billing_save_delivery_challan(int $userId, int $organizationId, array $data, ?int $challanId = null): ?int
{
    billing_ensure_sales_module_support();

    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $documentNumber = billing_clean((string) ($data['delivery_challan_number'] ?? ''));
    if ($customerName === '' || $documentNumber === '') {
        return null;
    }

    $items = billing_sales_normalize_items($data['items'] ?? []);
    if ($items === []) {
        return null;
    }

    $totals = billing_sales_calculate_totals(
        $items,
        (float) ($data['discount_amount'] ?? 0),
        (float) ($data['adjustment_amount'] ?? 0),
        0
    );

    $customerId = (int) ($data['customer_id'] ?? 0);
    $referenceNumber = billing_clean((string) ($data['reference_number'] ?? ''));
    $challanDate = billing_clean((string) ($data['challan_date'] ?? ''));
    $challanType = billing_clean((string) ($data['challan_type'] ?? 'Supply on Approval'));
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Draft'));
    $customerNotes = billing_clean((string) ($data['customer_notes'] ?? ''));
    $termsText = billing_clean((string) ($data['terms_text'] ?? ''));
    $pdfTemplateName = billing_clean((string) ($data['pdf_template_name'] ?? 'Standard Template'));

    $conn = billing_db();
    if ($challanId) {
        $stmt = $conn->prepare(
            'UPDATE delivery_challans
             SET customer_id = ?, delivery_challan_number = ?, customer_name = ?, reference_number = ?, challan_date = ?, challan_type = ?, sub_total = ?, discount_amount = ?, adjustment_amount = ?, total_amount = ?, status_label = ?, customer_notes = ?, terms_text = ?, pdf_template_name = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'isssssddddsssiiii',
            $customerId,
            $documentNumber,
            $customerName,
            $referenceNumber,
            $challanDate,
            $challanType,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText,
            $pdfTemplateName,
            $challanId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            return null;
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO delivery_challans
             (user_id, organization_id, customer_id, delivery_challan_number, customer_name, reference_number, challan_date, challan_type, sub_total, discount_amount, adjustment_amount, total_amount, status_label, customer_notes, terms_text, pdf_template_name)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iiisssssddddssss',
            $userId,
            $organizationId,
            $customerId,
            $documentNumber,
            $customerName,
            $referenceNumber,
            $challanDate,
            $challanType,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText,
            $pdfTemplateName
        );
        $saved = $stmt->execute();
        $challanId = $saved ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if ($challanId <= 0) {
            return null;
        }
    }

    billing_sales_replace_items('delivery_challan_items', 'delivery_challan_id', (int) $challanId, $userId, $organizationId, $items, false, false);
    return (int) $challanId;
}

function billing_get_credit_notes(int $userId, int $organizationId): array
{
    return billing_fetch_all(
        'SELECT credit_notes.*,
                COALESCE(invoices.invoice_number, "") AS invoice_number,
                COALESCE(customers.email, "") AS customer_email
         FROM credit_notes
         LEFT JOIN invoices
           ON invoices.id = credit_notes.invoice_id
          AND invoices.user_id = credit_notes.user_id
          AND invoices.organization_id = credit_notes.organization_id
         LEFT JOIN customers
           ON customers.id = credit_notes.customer_id
          AND customers.user_id = credit_notes.user_id
          AND customers.organization_id = credit_notes.organization_id
         WHERE credit_notes.user_id = ? AND credit_notes.organization_id = ? AND COALESCE(credit_notes.is_deleted, 0) = 0
         ORDER BY created_at DESC, id DESC',
        'ii',
        [$userId, $organizationId]
    );
}

function billing_get_credit_note(int $userId, int $organizationId, int $creditNoteId): ?array
{
    $creditNote = billing_fetch_one(
        'SELECT *
         FROM credit_notes
         WHERE id = ? AND user_id = ? AND organization_id = ? AND COALESCE(is_deleted, 0) = 0
         LIMIT 1',
        'iii',
        [$creditNoteId, $userId, $organizationId]
    );

    if ($creditNote) {
        $creditNote['items'] = billing_sales_fetch_items('credit_note_items', 'credit_note_id', $creditNoteId, $userId, $organizationId);
    }

    return $creditNote;
}

function billing_save_credit_note(int $userId, int $organizationId, array $data, ?int $creditNoteId = null): ?int
{
    billing_ensure_sales_module_support();

    $customerName = billing_clean((string) ($data['customer_name'] ?? ''));
    $documentNumber = billing_clean((string) ($data['credit_note_number'] ?? ''));
    if ($customerName === '' || $documentNumber === '') {
        return null;
    }

    $items = billing_sales_normalize_items($data['items'] ?? []);
    if ($items === []) {
        return null;
    }

    $totals = billing_sales_calculate_totals(
        $items,
        (float) ($data['discount_amount'] ?? 0),
        (float) ($data['adjustment_amount'] ?? 0),
        (float) ($data['tax_total'] ?? 0)
    );

    $customerId = (int) ($data['customer_id'] ?? 0);
    $invoiceId = (int) ($data['invoice_id'] ?? 0);
    $referenceNumber = billing_clean((string) ($data['reference_number'] ?? ''));
    $salespersonName = billing_clean((string) ($data['salesperson_name'] ?? ''));
    $subject = billing_clean((string) ($data['subject'] ?? ''));
    $creditNoteDate = billing_clean((string) ($data['credit_note_date'] ?? ''));
    $statusLabel = billing_clean((string) ($data['status_label'] ?? 'Open'));
    $customerNotes = billing_clean((string) ($data['customer_notes'] ?? 'Will be displayed on the credit note'));
    $termsText = billing_clean((string) ($data['terms_text'] ?? ''));
    $pdfTemplateName = billing_clean((string) ($data['pdf_template_name'] ?? 'Spreadsheet Template'));

    $conn = billing_db();
    if ($creditNoteId) {
        $stmt = $conn->prepare(
            'UPDATE credit_notes
             SET customer_id = ?, invoice_id = ?, credit_note_number = ?, customer_name = ?, reference_number = ?, salesperson_name = ?, subject = ?, credit_note_date = ?, sub_total = ?, discount_amount = ?, adjustment_amount = ?, tax_total = ?, total_amount = ?, credits_remaining = ?, status_label = ?, customer_notes = ?, terms_text = ?, pdf_template_name = ?
             WHERE id = ? AND user_id = ? AND organization_id = ?
             LIMIT 1'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iissssssddddddsssiii',
            $customerId,
            $invoiceId,
            $documentNumber,
            $customerName,
            $referenceNumber,
            $salespersonName,
            $subject,
            $creditNoteDate,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $totals['total_amount'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText,
            $pdfTemplateName,
            $creditNoteId,
            $userId,
            $organizationId
        );
        $saved = $stmt->execute();
        $stmt->close();
        if (!$saved) {
            return null;
        }
    } else {
        $stmt = $conn->prepare(
            'INSERT INTO credit_notes
             (user_id, organization_id, customer_id, invoice_id, credit_note_number, customer_name, reference_number, salesperson_name, subject, credit_note_date, sub_total, discount_amount, adjustment_amount, tax_total, total_amount, credits_remaining, status_label, customer_notes, terms_text, pdf_template_name)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
        );
        if ($stmt === false) {
            return null;
        }

        $stmt->bind_param(
            'iiiissssssddddddssss',
            $userId,
            $organizationId,
            $customerId,
            $invoiceId,
            $documentNumber,
            $customerName,
            $referenceNumber,
            $salespersonName,
            $subject,
            $creditNoteDate,
            $totals['sub_total'],
            $totals['discount_amount'],
            $totals['adjustment_amount'],
            $totals['tax_total'],
            $totals['total_amount'],
            $totals['total_amount'],
            $statusLabel,
            $customerNotes,
            $termsText,
            $pdfTemplateName
        );
        $saved = $stmt->execute();
        $creditNoteId = $saved ? (int) $stmt->insert_id : 0;
        $stmt->close();
        if ($creditNoteId <= 0) {
            return null;
        }
    }

    billing_sales_replace_items('credit_note_items', 'credit_note_id', (int) $creditNoteId, $userId, $organizationId, $items, true, false);
    return (int) $creditNoteId;
}
