<?php
require_once __DIR__ . '/config.php';

// Main Database Connection Function
function db(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;

    $dsn = 'mysql:host=' . DB_HOST . ';dbname=' . DB_NAME . ';charset=utf8mb4';
    $opt = [
        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, $opt);
    } catch (PDOException $e) {
        die("Database Connection Failed: " . $e->getMessage());
    }

    // Optional: Schema check (Keep your existing logic here if needed)
    try {
        // Check for 'logo' column in 'settings' table
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'settings' AND COLUMN_NAME = 'logo'");
        $stmt->execute([DB_NAME]);
        if ($stmt->fetchColumn() == 0) {
            $pdo->exec("ALTER TABLE settings ADD COLUMN logo VARCHAR(255) DEFAULT NULL");
        }

        // Auto-create orders table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `orders` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `business_id` int(11) NOT NULL,
          `created_at` datetime NOT NULL DEFAULT current_timestamp(),
          `table_no` varchar(20) NOT NULL DEFAULT '',
          `status` varchar(15) NOT NULL DEFAULT 'OPEN',
          `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
          `discount` decimal(10,2) NOT NULL DEFAULT 0.00,
          `tax` decimal(10,2) NOT NULL DEFAULT 0.00,
          `total` decimal(10,2) NOT NULL DEFAULT 0.00,
          `payment_method` varchar(20) NOT NULL DEFAULT '',
          `gst_enabled` tinyint(1) NOT NULL DEFAULT 1,
          `customer_sno` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Auto-create order_items table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `order_items` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `business_id` int(11) DEFAULT NULL,
          `order_id` int(11) NOT NULL,
          `item_id` int(11) DEFAULT NULL,
          `item_name` varchar(120) NOT NULL,
          `price` decimal(10,2) NOT NULL DEFAULT 0.00,
          `qty` int(11) NOT NULL DEFAULT 1,
          `notes` varchar(255) DEFAULT NULL,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Auto-create customer table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `customer` (
          `sno` int(11) NOT NULL AUTO_INCREMENT,
          `business_id` int(11) DEFAULT NULL,
          `name` varchar(225) NOT NULL,
          `mobile_no` varchar(15) NOT NULL,
          PRIMARY KEY (`sno`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

        // Auto-create purchases table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `purchases` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `business_id` int(11) NOT NULL,
          `party_id` int(11) NOT NULL,
          `bill_no` varchar(50) DEFAULT NULL,
          `purchase_date` date DEFAULT NULL,
          `payment_terms` varchar(50) DEFAULT 'Credit',
          `subtotal` decimal(12,2) DEFAULT 0.00,
          `total_tax` decimal(12,2) DEFAULT 0.00,
          `grand_total` decimal(12,2) DEFAULT 0.00,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

        // Check if payment_terms column exists in purchases
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'purchases' AND COLUMN_NAME = 'payment_terms'");
        $stmt->execute([DB_NAME]);
        if ($stmt->fetchColumn() == 0) {
            $pdo->exec("ALTER TABLE purchases ADD COLUMN payment_terms VARCHAR(50) DEFAULT 'Credit' AFTER purchase_date");
        }

        // Auto-create purchase_items table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `purchase_items` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `purchase_id` int(11) NOT NULL,
          `item_id` int(11) NOT NULL,
          `qty` decimal(12,2) DEFAULT 0.00,
          `rate` decimal(12,2) DEFAULT 0.00,
          `amount` decimal(12,2) DEFAULT 0.00,
          `tax_per` decimal(5,2) DEFAULT 0.00,
          `tax_amt` decimal(12,2) DEFAULT 0.00,
          `net_value` decimal(12,2) DEFAULT 0.00,
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");

        // Auto-create payments table if missing
        $pdo->exec("CREATE TABLE IF NOT EXISTS `payments` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `business_id` int(11) NOT NULL,
          `party_id` int(11) NOT NULL,
          `amount` decimal(12,2) NOT NULL,
          `payment_date` date NOT NULL,
          `payment_mode` varchar(50) NOT NULL,
          `payment_type` enum('RECEIPT','PAYMENT') NOT NULL,
          `remarks` text DEFAULT NULL,
          `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
          PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;");
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'sales' AND COLUMN_NAME = 'discount_all'");
        $stmt->execute([DB_NAME]);
        
        if ($stmt->fetchColumn() == 0) {
            $pdo->exec("ALTER TABLE `sales`
                ADD COLUMN `discount_all` DECIMAL(10,2) DEFAULT 0.00 AFTER `grand_total`,
                ADD COLUMN `delivery_charges` DECIMAL(10,2) DEFAULT 0.00 AFTER `discount_all`,
                ADD COLUMN `freight_charges` DECIMAL(10,2) DEFAULT 0.00 AFTER `delivery_charges`;");
        }
        $pdo->exec("ALTER TABLE new_items ADD COLUMN dynamic_data JSON DEFAULT NULL AFTER img;");
    } catch (Throwable $e) {
        // Ignore schema errors to prevent app blocking
    }

    return $pdo;
}

// ✅ Execute a query (Insert, Update, Delete)
function query(string $sql, array $params = []) {
    try {
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    } catch (Exception $e) {
        die("Query Failed: " . $e->getMessage());
    }
}

// ✅ Fetch ALL rows (For Lists)
function all_rows(string $sql, array $params = []) {
    return query($sql, $params)->fetchAll();
}

// ✅ Fetch ONE row (For Edit, View, Login)
// This fixes the 'row()' error by using the correct db() connection
function row(string $sql, array $params = []) {
    return query($sql, $params)->fetch();
}

// Alias 'one' to 'row' in case you used 'one()' elsewhere
function one(string $sql, array $params = []) {
    return row($sql, $params);
}

// ✅ Get Last Inserted ID
function last_id() {
    return db()->lastInsertId();
}
?>