<?php
function get_db_connection() {
    $db_path = __DIR__ . '/customers.db';
    try {
        $pdo = new PDO('sqlite:' . $db_path);
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        return $pdo;
    } catch (PDOException $e) {
        die("Database connection failed: " . $e->getMessage());
    }
}

function initialize_database() {
    $pdo = get_db_connection();
    
    // Enable foreign key constraints
    $pdo->exec('PRAGMA foreign_keys = ON;');

    $pdo->exec("
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            name_kana TEXT,
            address TEXT,
            phone TEXT,
            email TEXT,
            created_at TEXT DEFAULT (datetime('now', 'localtime')),
            updated_at TEXT DEFAULT (datetime('now', 'localtime'))
        )
    ");

    // Trigger to update updated_at timestamp
    $pdo->exec("
        CREATE TRIGGER IF NOT EXISTS update_customers_updated_at
        AFTER UPDATE ON customers
        FOR EACH ROW
        BEGIN
            UPDATE customers SET updated_at = datetime('now', 'localtime') WHERE id = OLD.id;
        END;
    ");
}

// Initialize the database and table when this file is loaded.
initialize_database();
