<?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_test');
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();
}

// ── Settings helper ──────────────────────────────────────────
function get_settings(): array {
    $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',
    ];
    $rows = db_rows("SELECT setting_key, setting_value FROM settings");
    $db   = [];
    foreach ($rows as $r) {
        $db[$r['setting_key']] = $r['setting_value'];
    }
    return 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]
    );
}

function save_settings(array $map): void {
    foreach ($map as $k => $v) {
        save_setting($k, (string)$v);
    }
}

// ── 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);
}
