<?php
// ============================================================
//  MediCore — Database Connection
//  File: includes/db.php
//  Edit DB_* constants to match your server before first run.
// ============================================================

// ── App base URL (auto-detected, works in any subfolder) ─────
// __DIR__ = .../htdocs/medicore/includes
// dirname(__DIR__) = .../htdocs/medicore  ← always the project root
if (!defined('APP_BASE')) {
    $__docRoot = rtrim(str_replace('\\', '/', $_SERVER['DOCUMENT_ROOT'] ?? ''), '/');
    $__appRoot = rtrim(str_replace('\\', '/', dirname(__DIR__)), '/');
    $__base    = str_replace($__docRoot, '', $__appRoot);
    define('APP_BASE', rtrim($__base, '/'));   // e.g. '/medicore'
    unset($__docRoot, $__appRoot, $__base);
}

define('DB_HOST', 'localhost');
define('DB_PORT', '3306');
define('DB_NAME', 'medicore');
define('DB_USER', 'root');       // ← change to your DB username
define('DB_PASS', '');           // ← change to your DB password
define('DB_CHARSET', 'utf8mb4');

// ── PDO singleton ────────────────────────────────────────────
function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $dsn = sprintf(
            'mysql:host=%s;port=%s;dbname=%s;charset=%s',
            DB_HOST, DB_PORT, DB_NAME, DB_CHARSET
        );
        $options = [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ];
        try {
            $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
        } catch (PDOException $e) {
            http_response_code(500);
            die(json_encode([
                'success' => false,
                'error'   => 'Database connection failed. Check includes/db.php settings.',
                'detail'  => $e->getMessage()
            ]));
        }
    }
    return $pdo;
}

// ── Quick query helpers ──────────────────────────────────────

/** Run a SELECT and return all rows. */
function db_rows(string $sql, array $params = []): array {
    $st = db()->prepare($sql);
    $st->execute($params);
    return $st->fetchAll();
}

/** Run a SELECT and return ONE row (or null). */
function db_row(string $sql, array $params = []): ?array {
    $st = db()->prepare($sql);
    $st->execute($params);
    $row = $st->fetch();
    return $row ?: null;
}

/** Run a SELECT and return a single scalar value (or null). */
function db_val(string $sql, array $params = []) {
    $st = db()->prepare($sql);
    $st->execute($params);
    $row = $st->fetch(PDO::FETCH_NUM);
    return $row ? $row[0] : null;
}

/** Run INSERT / UPDATE / DELETE. Returns affected row count. */
function db_run(string $sql, array $params = []): int {
    $st = db()->prepare($sql);
    $st->execute($params);
    return $st->rowCount();
}

/** Run INSERT and return the new auto-increment ID. */
function db_insert(string $sql, array $params = []): int {
    $st = db()->prepare($sql);
    $st->execute($params);
    return (int) db()->lastInsertId();
}

// ── Stock events (real-time sync outbox) ───────────────────────
// Every stock-mutating endpoint (sales, purchases, sale returns,
// purchase returns) writes one row here — inside the SAME transaction
// as its `products.stock_pcs` UPDATE — right after that update. The
// auto-increment `id` is a strictly monotonic cursor that api/stock-
// stream.php (SSE) and any catching-up client can page through with
// `WHERE id > ?`, and it doubles as the SSE wire protocol's native
// `id:` field, so a reconnecting EventSource's `Last-Event-ID` header
// gives exact, gap-free catch-up for free — no separate bookkeeping.
// Since the INSERT happens inside the caller's open transaction, a
// rollback anywhere in that transaction takes the event with it, so a
// failed sale can never publish a phantom stock change.
function ensure_stock_events_table(): void {
    static $done = false;
    if ($done) return;
    $done = true;
    db_run("CREATE TABLE IF NOT EXISTS stock_events (
        id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        product_id   INT UNSIGNED NOT NULL,
        event_type   VARCHAR(30)  NOT NULL,
        qty_delta    INT          NOT NULL,
        new_stock    INT          NOT NULL,
        source_type  VARCHAR(30)  NOT NULL,
        source_id    INT UNSIGNED DEFAULT NULL,
        created_by   INT UNSIGNED DEFAULT NULL,
        created_at   TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
        INDEX idx_product (product_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

/**
 * Record a stock change for real-time push. Call this from INSIDE the
 * same DB transaction as the `products` UPDATE that caused it, right
 * after that UPDATE (and, since our writes are relative deltas like
 * `stock_pcs = stock_pcs - ?`, after a follow-up `SELECT stock_pcs`
 * on the same connection/transaction — safe to read back because the
 * UPDATE already holds that row's exclusive lock until COMMIT, so the
 * SELECT can't see anyone else's interleaved change).
 */
function publish_stock_event(
    int $productId, string $eventType, int $qtyDelta, int $newStock,
    string $sourceType, ?int $sourceId = null
): void {
    ensure_stock_events_table();
    db_run(
        "INSERT INTO stock_events
            (product_id, event_type, qty_delta, new_stock, source_type, source_id, created_by)
         VALUES (?,?,?,?,?,?,?)",
        [$productId, $eventType, $qtyDelta, $newStock, $sourceType, $sourceId,
         function_exists('current_user_id') ? (current_user_id() ?: null) : null]
    );
}

// ── Local → Cloud sync outbox (see SYNC_ARCHITECTURE.md) ────────
// This is the write side of the Local↔Cloud database synchronization
// system. Local is the master; every operational write (sale,
// purchase, return, payment, receipt, stock change...) enqueues one
// row here in the SAME transaction as the actual write — exactly the
// same transactional-outbox pattern already used for stock_events, so
// a rolled-back transaction can never leave a phantom queue entry,
// and a committed transaction can never "forget" to enqueue.
//
// A separate CLI process (sync/worker.php) drains this table over
// HTTPS to the Cloud Server. It is NOT a web request handler, so it
// carries none of the Apache-worker-thread-exhaustion risk that
// already ruled out SSE/WebSockets elsewhere in this app (that
// incident was about browsers holding inbound Apache connections
// open; this is a single outbound-only OS process on a timer).
//
// Versioning: we deliberately do NOT maintain a separate per-record
// "version" counter. `sync_queue.id` is already a global, strictly
// monotonic, gap-free auto-increment — it IS the version number
// (a Local Sequence Number / LSN). The Cloud receiver stores the
// highest applied queue id per (branch_id, table_name, record_id) in
// `sync_applied` and only applies an incoming row if its id is
// greater, which is what makes replays/out-of-order delivery safe.
function ensure_sync_tables(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    db_run("CREATE TABLE IF NOT EXISTS sync_queue (
        id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        branch_id      INT UNSIGNED NOT NULL DEFAULT 1,
        table_name     VARCHAR(64)  NOT NULL,
        record_id      BIGINT UNSIGNED NOT NULL,
        operation_type ENUM('INSERT','UPDATE','DELETE') NOT NULL,
        payload        MEDIUMTEXT   DEFAULT NULL,
        status         ENUM('pending','sent','failed','done') NOT NULL DEFAULT 'pending',
        attempts       INT UNSIGNED NOT NULL DEFAULT 0,
        last_error     TEXT DEFAULT NULL,
        next_retry_at  TIMESTAMP(6) NULL DEFAULT NULL,
        created_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
        sent_at        TIMESTAMP(6) NULL DEFAULT NULL,
        INDEX idx_status_id (status, id),
        INDEX idx_table_record (table_name, record_id),
        INDEX idx_branch (branch_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    db_run("CREATE TABLE IF NOT EXISTS sync_log (
        id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        queue_id       BIGINT UNSIGNED NOT NULL,
        branch_id      INT UNSIGNED NOT NULL DEFAULT 1,
        table_name     VARCHAR(64)  NOT NULL,
        record_id      BIGINT UNSIGNED NOT NULL,
        operation_type VARCHAR(10)  NOT NULL,
        batch_id       VARCHAR(40)  DEFAULT NULL,
        http_status    INT DEFAULT NULL,
        success        TINYINT(1)   NOT NULL DEFAULT 0,
        duration_ms    INT DEFAULT NULL,
        response_snip  VARCHAR(500) DEFAULT NULL,
        created_at     TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
        INDEX idx_queue (queue_id),
        INDEX idx_created (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    db_run("CREATE TABLE IF NOT EXISTS sync_status (
        branch_id            INT UNSIGNED PRIMARY KEY,
        branch_name          VARCHAR(100) DEFAULT NULL,
        last_synced_queue_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
        last_attempt_at      TIMESTAMP(6) NULL DEFAULT NULL,
        last_success_at      TIMESTAMP(6) NULL DEFAULT NULL,
        is_online             TINYINT(1)  NOT NULL DEFAULT 0,
        consecutive_failures  INT UNSIGNED NOT NULL DEFAULT 0,
        pending_count         INT UNSIGNED NOT NULL DEFAULT 0,
        updated_at            TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    db_run("INSERT IGNORE INTO sync_status (branch_id, branch_name) VALUES (1, 'Main Branch')");
}

/**
 * Enqueue a row-level change for sync to the Cloud Server. Call this
 * from INSIDE the same transaction as the write that produced the
 * change, right after that write — same rule as publish_stock_event().
 *
 * $snapshot should be the full current row (associative array, e.g.
 * from `db_row("SELECT * FROM sales WHERE id = ?", [$id])`) for
 * INSERT/UPDATE. Pass null for DELETE — the Cloud side only needs
 * table_name + record_id to delete/soft-delete its copy.
 *
 * De-duplication of rapid repeated changes to the same record happens
 * on the READ side (sync/worker.php coalesces pending rows for the
 * same table+record before sending) — every call here still gets its
 * own immutable queue row so sync_log keeps a complete audit trail.
 */
function enqueue_sync(
    string $table, int $recordId, string $op,
    ?array $snapshot = null, int $branchId = 1
): void {
    ensure_sync_tables();
    db_run(
        "INSERT INTO sync_queue (branch_id, table_name, record_id, operation_type, payload)
         VALUES (?,?,?,?,?)",
        [$branchId, $table, $recordId, $op, $snapshot !== null ? json_encode($snapshot) : null]
    );
}

// ── Centralized stock ledger (Product History / audit trail) ────
// Every stock-affecting transaction anywhere in the app — sales,
// purchases, both kinds of returns, and manual Stock Modifications —
// writes ONE immutable row here, in the SAME transaction as the
// `products.stock_pcs` UPDATE it describes (same rule as
// publish_stock_event() above: call this right after that UPDATE,
// inside the open transaction, so a rollback anywhere in the caller's
// transaction takes the ledger entry with it too).
//
// This is deliberately a SEPARATE table from `stock_events` (the
// real-time cross-terminal push outbox) and `stock_history` (the
// older, narrower log written only by the returns endpoints) — this
// table is the one source of truth Product History and the Stock
// Modification Log are built from, per-product, forever. It is never
// updated or deleted after insert; there is intentionally no
// UPDATE/DELETE code path anywhere against this table.
function ensure_stock_ledger_table(): void {
    static $done = false;
    if ($done) return;
    $done = true;
    db_run("CREATE TABLE IF NOT EXISTS stock_ledger (
        id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        product_id       INT UNSIGNED NOT NULL,
        transaction_type VARCHAR(30)  NOT NULL,
        reference_table  VARCHAR(50)  DEFAULT NULL,
        reference_id     INT UNSIGNED DEFAULT NULL,
        reference_no     VARCHAR(60)  DEFAULT NULL,
        qty_in           INT UNSIGNED NOT NULL DEFAULT 0,
        qty_out          INT UNSIGNED NOT NULL DEFAULT 0,
        balance_after    INT          NOT NULL,
        branch_id        INT UNSIGNED NOT NULL DEFAULT 1,
        remarks          VARCHAR(255) DEFAULT '',
        created_by       INT UNSIGNED DEFAULT NULL,
        created_at       TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
        INDEX idx_product_created (product_id, created_at),
        INDEX idx_type (transaction_type),
        INDEX idx_reference (reference_table, reference_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
}

/**
 * Record one stock ledger entry. Call from INSIDE the same
 * transaction as the `products` UPDATE that caused it — see the
 * class comment above. Exactly one of $qtyIn/$qtyOut should be
 * non-zero for any single call (a movement is either in or out;
 * split a transaction into two calls if it's genuinely both, which
 * should not normally happen for any of the built-in transaction
 * types).
 */
function record_stock_ledger(
    int $productId, string $transactionType, int $qtyIn, int $qtyOut,
    int $balanceAfter, string $referenceTable = '', ?int $referenceId = null,
    string $referenceNo = '', string $remarks = '', int $branchId = 1
): void {
    ensure_stock_ledger_table();
    db_run(
        "INSERT INTO stock_ledger
            (product_id, transaction_type, reference_table, reference_id, reference_no,
             qty_in, qty_out, balance_after, branch_id, remarks, created_by)
         VALUES (?,?,?,?,?,?,?,?,?,?,?)",
        [
            $productId, $transactionType, $referenceTable ?: null, $referenceId, $referenceNo ?: null,
            $qtyIn, $qtyOut, $balanceAfter, $branchId, $remarks,
            function_exists('current_user_id') ? (current_user_id() ?: null) : null,
        ]
    );
}

// One-time backfill: seed an 'opening_stock' ledger row for every
// existing product reflecting its CURRENT stock_pcs at the moment
// this migration runs, so Product History reconciles correctly from
// day one instead of showing a mysterious gap between "nothing in the
// ledger" and the product's actual current stock for anything that
// existed before this feature shipped. Batched (LIMIT 200/call) and
// flagged in `settings` — same progressive-migration pattern already
// used for the product-thumbnail backfill in api/products.php.
function ensure_stock_ledger_opening_backfill(): void {
    static $done = false;
    if ($done) return;
    $done = true;
    ensure_stock_ledger_table();

    if ((get_settings()['stock_ledger_opening_migrated'] ?? '0') === '1') return;

    $rows = db_rows(
        "SELECT p.id, p.stock_pcs FROM products p
          WHERE NOT EXISTS (SELECT 1 FROM stock_ledger sl WHERE sl.product_id = p.id)
          LIMIT 200"
    );
    foreach ($rows as $r) {
        record_stock_ledger(
            (int) $r['id'], 'opening_stock', (int) $r['stock_pcs'], 0,
            (int) $r['stock_pcs'], '', null, '', 'Opening stock (auto-migrated)'
        );
    }
    if (count($rows) < 200) {
        save_setting('stock_ledger_opening_migrated', '1');
    }
}

// ── Soft-delete column auto-migration ──────────────────────────
// Adds is_deleted / deleted_at / deleted_by to a table if missing.
// Cheap information_schema check, cached per table per request, so
// it's safe to call defensively from any module file that queries
// is_deleted — it no longer has to depend on api/recycle.php having
// run first. (Previously this only lived in recycle.php and used
// "ADD COLUMN ... AFTER is_active", which silently failed — via the
// catch-and-ignore below it — on every table except `products`,
// since `products` is the only one of the seven soft-delete tables
// that actually has an is_active column. That's why is_deleted was
// missing on suppliers/customers/sales/purchases/etc.)
function ensure_soft_delete(string $tbl): void {
    static $checked = [];
    if (!empty($checked[$tbl])) return;
    $checked[$tbl] = true;
    try {
        $hasDel = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = ?
                AND COLUMN_NAME  = 'is_deleted'",
            [$tbl]
        );
        if (!$hasDel) {
            db_run("ALTER TABLE `$tbl`
                ADD COLUMN is_deleted TINYINT(1)   DEFAULT 0,
                ADD COLUMN deleted_at DATETIME     DEFAULT NULL,
                ADD COLUMN deleted_by INT UNSIGNED DEFAULT NULL");
            db_run("ALTER TABLE `$tbl` ADD INDEX idx_{$tbl}_deleted (is_deleted)");
        }
    } catch (Throwable $e) {
        error_log("ensure_soft_delete($tbl) failed: " . $e->getMessage());
    }
}

// ── Product categories (search/filter/report feature) ────────
// A lightweight id+name lookup table, mirroring generics/companies
// exactly, plus the products.category_id FK column and a handful of
// missing indexes (generic_id/company_id/form) that speed up the
// broadened multi-field search added to api/products.php and
// api/customer-shop.php. Safe to call from any page/API — cached
// per-request via the static guard, same pattern as ensure_soft_delete().
function ensure_categories_schema(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        db_run("CREATE TABLE IF NOT EXISTS categories (
            id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            name       VARCHAR(100) NOT NULL UNIQUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } catch (Throwable $e) {
        error_log("ensure_categories_schema (create table) failed: " . $e->getMessage());
    }

    try {
        $hasCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'products'
                AND COLUMN_NAME  = 'category_id'"
        );
        if (!$hasCol) {
            db_run("ALTER TABLE products ADD COLUMN category_id INT UNSIGNED DEFAULT NULL AFTER company_id");
            db_run("ALTER TABLE products ADD INDEX idx_category (category_id)");
        }
    } catch (Throwable $e) {
        error_log("ensure_categories_schema (category_id column) failed: " . $e->getMessage());
    }

    // Search-optimization indexes — generic_id/company_id/form were
    // never indexed, so filtering/joining on them (used by the
    // broadened search) had to scan the whole table.
    foreach (['generic_id' => 'idx_generic', 'company_id' => 'idx_company', 'form' => 'idx_form'] as $col => $idx) {
        try {
            $hasIdx = db_val(
                "SELECT COUNT(*) FROM information_schema.STATISTICS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME   = 'products'
                    AND INDEX_NAME   = ?",
                [$idx]
            );
            if (!$hasIdx) {
                db_run("ALTER TABLE products ADD INDEX $idx ($col)");
            }
        } catch (Throwable $e) {
            error_log("ensure_categories_schema ($idx) failed: " . $e->getMessage());
        }
    }
}

// ── Performance indexes for hot, ever-growing tables ───────────
// purchase_items.exp (expiry, 'YYYY-MM' string) had NO index at all —
// it's read on every single page load (includes/header.php's expired-
// product nav badge), on every dashboard.php load, AND once/hour from
// notify.php's expiry sweep, always as a JOIN against products plus a
// range scan (`exp != '' AND exp < ...`). With no index, every one of
// those queries did a full table scan of purchase_items — the single
// fastest-growing table in the app (one row per purchase line item,
// forever) — which is exactly why the app gets slower over time as the
// database grows. Called from ensure_topbar_tables() below, which is
// already reached from both header.php (every page) and api/status.php
// (every health-check ping), so this only needs to be defined here.
function ensure_perf_indexes(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    // Fast path once this has fully completed: one cheap settings lookup
    // (already statically cached per-request by get_settings() — see
    // above) instead of two information_schema queries on every request.
    if ((get_settings()['perf_indexes_v1'] ?? '') === '1') return;

    // Atomic one-shot claim — REQUIRED here, not optional. This function
    // is reached from ensure_topbar_tables(), which runs on EVERY page
    // load app-wide AND every api/status.php health-check ping (every
    // 10s, per open browser tab). Without a claim, every one of those
    // concurrent requests independently checks "does the index exist
    // yet?" via information_schema and — until the first ALTER actually
    // commits — many of them can see "no" at once and all fire
    // `ALTER TABLE ... ADD INDEX` simultaneously on the same large,
    // actively-written table. Concurrent ALTERs queue behind each
    // other's metadata lock, and on this app's small XAMPP/Apache worker
    // pool that's enough to stall the entire site — which is exactly
    // what happened the first time this shipped. INSERT IGNORE on a
    // UNIQUE key lets only the very first request "win" (1 affected
    // row); every other concurrent request gets 0 and returns
    // immediately without touching information_schema or ALTER at all.
    $claimed = db_run(
        "INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('perf_indexes_claim', '1')"
    );
    if ($claimed === 0) return;

    $wanted = [
        'purchase_items' => ['idx_exp' => 'exp'],
        // due-balance lookups (dashboard's "pending POs" count, supplier/
        // customer due reports) filter on due > 0 with no supporting index.
        'purchases'       => ['idx_due' => 'due'],
    ];
    foreach ($wanted as $table => $indexes) {
        foreach ($indexes as $idx => $col) {
            try {
                $hasIdx = db_val(
                    "SELECT COUNT(*) FROM information_schema.STATISTICS
                      WHERE TABLE_SCHEMA = DATABASE()
                        AND TABLE_NAME   = ?
                        AND INDEX_NAME   = ?",
                    [$table, $idx]
                );
                if (!$hasIdx) {
                    db_run("ALTER TABLE `$table` ADD INDEX $idx ($col)");
                }
            } catch (Throwable $e) {
                error_log("ensure_perf_indexes ($table.$idx) failed: " . $e->getMessage());
                // Don't retry-storm on repeated failures either — the
                // claim above already prevents that (only one request
                // ever gets here), but make it explicit: a failed
                // attempt is not re-attempted automatically. Fix the
                // underlying issue (see error log) and re-run manually
                // if needed.
            }
        }
    }

    save_setting('perf_indexes_v1', '1');
}

// ── Product default supplier (Smart Requisition feature) ──────
// Lets a default/preferred supplier be assigned per product, so
// Smart Requisition (product/smart-requisition.php) can group a
// reorder under a real supplier even for products that have never
// been through a purchase order yet — previously it could only infer
// a supplier from past purchase history, so brand-new products always
// fell into "Unknown Supplier". Shared here (rather than living only
// in api/products.php) so every page that queries products.supplier_id
// — smart-requisition.php, product/list.php, api/products.php — can
// call this defensively before running its query, regardless of
// which page happens to run first on a given database.
function ensure_products_supplier_column(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $hasCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'products'
                AND COLUMN_NAME  = 'supplier_id'"
        );
        if (!$hasCol) {
            db_run("ALTER TABLE products ADD COLUMN supplier_id INT UNSIGNED DEFAULT NULL AFTER company_id");
            try {
                db_run("ALTER TABLE products ADD CONSTRAINT fk_products_supplier
                            FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL");
            } catch (Throwable $e) { /* index/FK best-effort — column itself is what matters */ }
        }
    } catch (Throwable $e) {
        error_log('ensure_products_supplier_column failed: ' . $e->getMessage());
    }
}

// ── Requisition items: audit-snapshot columns ───────────────────
// requisition_items' base schema (medicore_schema.sql) only ever
// defined (requisition_id, product_id, product_name, qty_box, btp_vat,
// total_price) — it never actually had current_stock/alert_qty/
// suggested_qty. But api/requisitions.php's api_save() has always
// unconditionally listed all three of those columns in its INSERT
// statement (a snapshot of what stock level / alert threshold /
// suggested quantity looked like AT THE TIME the requisition was
// created). With the columns missing, EVERY requisition save — from
// Smart Requisition's "Create Requisitions by Supplier", Manual
// Requisition, anywhere — fails outright with an "Unknown column
// 'current_stock' in 'field list'" SQL error, no matter what the
// caller sends. Added defensively so it's fixed regardless of
// whatever state an existing database happens to be in.
function ensure_requisition_items_columns(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    $wanted = [
        'current_stock' => 'INT DEFAULT 0',
        'alert_qty'     => 'INT DEFAULT 0',
        'suggested_qty' => 'INT DEFAULT 0',
    ];
    foreach ($wanted as $col => $def) {
        try {
            $hasCol = db_val(
                "SELECT COUNT(*) FROM information_schema.COLUMNS
                  WHERE TABLE_SCHEMA = DATABASE()
                    AND TABLE_NAME   = 'requisition_items'
                    AND COLUMN_NAME  = ?",
                [$col]
            );
            if (!$hasCol) {
                db_run("ALTER TABLE requisition_items ADD COLUMN $col $def");
            }
        } catch (Throwable $e) {
            error_log("ensure_requisition_items_columns ($col) failed: " . $e->getMessage());
        }
    }
}

// ── Users.photo column (self-service "My Profile" photo upload) ──
// Same defensive-migration shape as ensure_products_supplier_column()
// above — any entry point that needs the column (api/profile.php,
// includes/auth.php's current_user_photo(), profile.php) can call
// this first regardless of which page runs first on a given DB.
// MEDIUMTEXT (not VARCHAR) because the value is a base64 data-URI,
// same storage pattern already used for settings.logo and
// customers.profile_pic — this app has no server-side file storage.
function ensure_users_photo_column(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $hasCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'users'
                AND COLUMN_NAME  = 'photo'"
        );
        if (!$hasCol) {
            db_run("ALTER TABLE users ADD COLUMN photo MEDIUMTEXT DEFAULT NULL AFTER avatar");
        }
    } catch (Throwable $e) {
        error_log('ensure_users_photo_column failed: ' . $e->getMessage());
    }
}

// ── Products.image column (box/packaging photo, for visual ID in the
//    product list and the Sale Entry search dropdown) ─────────────
// Same shape as ensure_users_photo_column() — base64 data-URI, added
// lazily/idempotently so it self-installs on first use. The client
// (product/list.php) downscales the photo via <canvas> before upload,
// so a typical stored value is only ~5-15KB even though the column
// itself is MEDIUMTEXT — this keeps it cheap to include inline in
// api_list()'s "SELECT p.*", which both the product list AND Sale
// Entry's live search reuse, without meaningfully slowing either down.
function ensure_products_image_column(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $hasCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'products'
                AND COLUMN_NAME  = 'image'"
        );
        if (!$hasCol) {
            db_run("ALTER TABLE products ADD COLUMN image MEDIUMTEXT DEFAULT NULL AFTER barcode");
        }
    } catch (Throwable $e) {
        error_log('ensure_products_image_column failed: ' . $e->getMessage());
    }
}

// ── Products.image_thumb column (search-optimization pass) ───────
// A small (~100px, few-KB) server-generated JPEG derived from `image`.
// Search/list/browse endpoints that return many product rows at once
// (Sale Entry & Purchase Entry search-as-you-type, Product List,
// Requisition browses) return THIS instead of the full-size photo —
// see products_columns_except()/make_product_thumbnail() in
// api/products.php. The full-size photo is untouched and still used
// by the single-record edit form / detail view.
function ensure_products_thumb_column(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $hasCol = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'products'
                AND COLUMN_NAME  = 'image_thumb'"
        );
        if (!$hasCol) {
            db_run("ALTER TABLE products ADD COLUMN image_thumb MEDIUMTEXT DEFAULT NULL AFTER image");
        }
    } catch (Throwable $e) {
        error_log('ensure_products_thumb_column failed: ' . $e->getMessage());
    }
}

// ── Dynamic "all columns except X" helper ─────────────────────────
// Any query that lists/searches many product rows at once should never
// pull the (up to ~700KB) `image` column for every row. Rather than
// hand-maintain a column list that could silently drift from the real
// schema, this builds it from information_schema — callers just name
// what to leave out (typically ['image'] or ['image','image_thumb']
// for pages that don't render a photo at all).
function products_columns_except(array $exclude, string $alias = 'p'): string {
    static $all = null;
    if ($all === null) {
        $rows = db_rows(
            "SELECT COLUMN_NAME FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products'
              ORDER BY ORDINAL_POSITION"
        );
        $all = array_column($rows, 'COLUMN_NAME');
    }
    $keep = array_values(array_diff($all, $exclude));
    return implode(', ', array_map(fn($c) => "$alias.`$c`", $keep));
}

// ── Permissions table ──────────────────────────────────────────
// Moved here from api/settings.php (its original home) so it's
// reachable from includes/auth.php's can()/can_view_profit() path
// too, not just the Settings admin UI — both need the table to
// exist before querying/writing it, and this file (db.php) is the
// one place both auth.php and api/settings.php already require.
function ensure_permissions_table(): void {
    db_run("CREATE TABLE IF NOT EXISTS user_permissions (
        id        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id   INT UNSIGNED NOT NULL,
        module    VARCHAR(50)  NOT NULL,
        level     ENUM('view','full','custom') NOT NULL DEFAULT 'view',
        actions   VARCHAR(100) DEFAULT NULL,
        UNIQUE KEY uq_user_module (user_id, module),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // ── Migration for tables created before granular permissions ──
    try {
        $hasActions = db_val(
            "SELECT COUNT(*) FROM information_schema.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME   = 'user_permissions'
                AND COLUMN_NAME  = 'actions'"
        );
        if (!$hasActions) {
            db_run("ALTER TABLE user_permissions ADD COLUMN actions VARCHAR(100) DEFAULT NULL");
        }
    } catch (Throwable $e) { /* best-effort migration, ignore */ }

    try {
        db_run("ALTER TABLE user_permissions
                 MODIFY COLUMN level ENUM('view','full','custom') NOT NULL DEFAULT 'view'");
    } catch (Throwable $e) { /* already correct, or insufficient privileges — ignore */ }
}

// ── One-time backfill: profit_view permission for existing managers ──
// 'profit_view' is a brand-new permission (see APP_MODULES in auth.php).
// Managers created before this shipped could already see profit figures
// via a hardcoded "is_admin() || role==='manager'" check in Sale Entry,
// and already had full access to the Profit Report page via the older
// 'profit' module. Without this backfill, the moment can_view_profit()
// starts gating those surfaces, every existing manager would silently
// lose visibility they already had (new managers get 'profit_view' from
// the role template automatically — this only covers pre-existing ones).
//
// Guarded by a settings-table flag (NOT just a "does any profit_view row
// exist" check) so this only ever runs once, ever. That distinction
// matters: if an admin later explicitly revokes profit_view from a
// manager, that revoke must stick — a "re-grant if missing" check would
// silently undo it on the next request.
function ensure_profit_view_migrated(): void {
    static $done = false;
    if ($done) return;
    $done = true;

    try {
        $flag = db_val(
            "SELECT setting_value FROM settings WHERE setting_key = 'profit_view_migrated'"
        );
        if ($flag === '1') return;

        ensure_permissions_table();

        db_run(
            "INSERT INTO user_permissions (user_id, module, level)
             SELECT id, 'profit_view', 'full' FROM users
              WHERE role = 'manager'
                AND id NOT IN (
                  SELECT user_id FROM user_permissions WHERE module = 'profit_view'
                )"
        );

        save_setting('profit_view_migrated', '1');
    } catch (Throwable $e) {
        error_log('ensure_profit_view_migrated failed: ' . $e->getMessage());
    }
}

// ── Settings helper ──────────────────────────────────────────
// Statically cached per-request — this used to run a full
// `SELECT * FROM settings` on EVERY call with zero caching, and it's
// called repeatedly within a single request (header.php calls it once
// on every single page load app-wide; some API files like sales.php
// call it 5+ times in one request). None of that data changes mid-
// request, so the DB round trip only needs to happen once. Cache is
// invalidated by save_setting()/save_settings() so a save-then-reread
// in the same request still sees the fresh value.
function get_settings(bool $forceReload = false): array {
    static $cache = null;
    if ($cache !== null && !$forceReload) return $cache;

    $defaults = [
        'shop_name'      => 'My Pharmacy',
        'address'        => '',
        'phone'          => '',
        'email'          => '',
        'reg_no'         => '',
        'logo'           => '',
        'vat_rate'       => '17.4',
        'currency'       => '৳',
        'invoice_note'   => 'Thank you for your purchase.',
        'inv_prefix'     => 'INV',
        'po_prefix'      => 'PO',
        'req_prefix'     => 'REQ',
        'theme_color'    => '#2d3a8c',
        'low_stock_days' => '30',
        'allow_negative_stock' => '0',
        'rounding_enabled' => '0',
        'rounding_mode'    => 'nearest',  // nearest | up | down
        'po_approval_enabled'   => '0',   // Purchase Order Approval workflow — off by default
        'po_approval_threshold' => '0',   // 0 = every PO needs approval once enabled
        'print_format'          => 'a4',  // Default print format: a4 | thermal80 | thermal58 (Sale)
        'purchase_print_format' => 'a4',  // Default print format: a4 | thermal80 | thermal58 (Purchase)
        'portal_enabled'        => '0',   // Customer web-ordering portal — off by default
        'portal_shop_name'      => '',    // Falls back to shop_name when blank
        'portal_welcome_text'   => '',    // Falls back to a generic message when blank
        'portal_min_order'      => '0',   // Minimum order total (0 = no minimum)
        'portal_allow_backorder'=> '1',   // Allow ordering out-of-stock items (1 = yes, backorder allowed)

        // ── Notification gateways (SMS / WhatsApp / Email) ──────────
        // Generic HTTP gateway for SMS/WhatsApp — most providers accept
        // either a GET request with placeholders in the URL, or a POST
        // with form fields. auth_header lets you set a raw header line
        // (e.g. "Authorization: Bearer xxxx") for providers that need
        // one instead of / in addition to an API key query param.
        'gw_sms_enabled'      => '0',
        'gw_sms_api_url'      => '',   // may contain {phone} {message} {apikey} {sender}
        'gw_sms_method'       => 'GET', // GET | POST
        'gw_sms_api_key'      => '',
        'gw_sms_sender_id'    => '',
        'gw_sms_auth_header'  => '',   // optional raw header, e.g. "Authorization: Bearer xxxx"

        'gw_wa_enabled'      => '0',
        'gw_wa_api_url'      => '',
        'gw_wa_method'       => 'POST',
        'gw_wa_api_key'      => '',
        'gw_wa_sender_id'    => '',
        'gw_wa_auth_header'  => '',

        'gw_email_enabled'        => '0',
        'gw_email_smtp_host'      => '',
        'gw_email_smtp_port'      => '587',
        'gw_email_smtp_user'      => '',
        'gw_email_smtp_pass'      => '',
        'gw_email_smtp_encryption'=> 'tls', // none | tls | ssl
        'gw_email_from_address'   => '',
        'gw_email_from_name'      => '',
    ];
    $rows = db_rows("SELECT setting_key, setting_value FROM settings");
    $db   = [];
    foreach ($rows as $r) {
        $db[$r['setting_key']] = $r['setting_value'];
    }
    return $cache = array_merge($defaults, $db);
}

function save_setting(string $key, string $value): void {
    db_run(
        "INSERT INTO settings (setting_key, setting_value)
         VALUES (?, ?)
         ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)",
        [$key, $value]
    );
    // Force get_settings()'s static cache to pick this up if the same
    // request reads settings again (e.g. Settings page: save, then
    // re-render using $settings = get_settings()).
    get_settings(true);
}

function save_settings(array $map): void {
    foreach ($map as $k => $v) {
        db_run(
            "INSERT INTO settings (setting_key, setting_value)
             VALUES (?, ?)
             ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value)",
            [$k, (string)$v]
        );
    }
    get_settings(true);
}

// ── Utility helpers ──────────────────────────────────────────
function fmt(float $n, string $currency = ''): string {
    return $currency . number_format($n, 2);
}

function today(): string {
    return date('Y-m-d');
}

function next_ref(string $prefix, string $table, string $col): string {
    $year  = date('Y');
    $count = (int) db_val(
        "SELECT COUNT(*) FROM `$table` WHERE `$col` LIKE ?",
        ["$prefix-$year-%"]
    );
    return sprintf('%s-%s-%06d', $prefix, $year, $count + 1);
}
