<?php
function db_connect() {
    $host = '127.0.0.1';
    $db   = 'inventory_management';
    $user = 'inventory_user';
    $pass = 'password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$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, $user, $pass, $options);
    } catch (PDOException $e) {
         throw new PDOException($e->getMessage(), (int)$e->getCode());
    }
    return $pdo;
}

function create_schema($pdo) {
    $queries = [
        'CREATE TABLE IF NOT EXISTS `inventory_items` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `acquisition_date` DATE NOT NULL,
            `item_code` VARCHAR(255) UNIQUE NOT NULL,
            `company_origin` VARCHAR(255) NOT NULL,
            `category` VARCHAR(255) NOT NULL,
            `sub_category` VARCHAR(255) NOT NULL,
            `po_number` VARCHAR(255) NOT NULL,
            `item_name` VARCHAR(255) NOT NULL,
            `acquisition_price` DECIMAL(10, 2) NOT NULL,
            `item_user_name` VARCHAR(255) NOT NULL,
            `user_division` VARCHAR(255) NOT NULL,
            `user_department` VARCHAR(255) NOT NULL,
            `location_city` VARCHAR(255) NOT NULL,
            `location_building` VARCHAR(255) NOT NULL,
            `location_area` VARCHAR(255) NOT NULL,
            `current_condition` VARCHAR(255) NOT NULL,
            `condition_at_acquisition` VARCHAR(255) NOT NULL,
            `item_information` TEXT,
            `asset_status` VARCHAR(255) NOT NULL,
            `transfer_status` VARCHAR(255) NOT NULL,
            `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );'
    ];

    foreach ($queries as $query) {
        $pdo->exec($query);
    }
}

try {
    $pdo = db_connect();
    create_schema($pdo);
} catch (PDOException $e) {
    // In a real app, you'd log this error
    error_log($e->getMessage());
    // You could also show a generic error page
    // die("Database setup failed. Please check the logs.");
}
?>