<?php

/**
 * This script will create the SQLite database and the necessary 'certificates' table.
 * Run it once by visiting http://localhost/environment_bd/setup_database.php in your browser.
 */

header('Content-Type: text/plain');

$db_path = __DIR__ . '/doe_data.sqlite';

try {
    // 1. Connect to the SQLite database. If the file doesn't exist, it will be created.
    $pdo = new PDO('sqlite:' . $db_path);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Successfully connected to the database file at: " . $db_path . "\n";

    // 2. SQL to create the 'certificates' table.
    $create_table_sql = "
    CREATE TABLE IF NOT EXISTS certificates (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_no INTEGER NOT NULL UNIQUE,
        sarok_no TEXT,
        project_name TEXT NOT NULL,
        address TEXT NOT NULL,
        category TEXT NOT NULL,
        certificate_type TEXT NOT NULL,
        issue_date TEXT NOT NULL,
        expiry_date TEXT NOT NULL,
        detail_link TEXT NOT NULL
    );";

    $pdo->exec($create_table_sql);
    echo "Table 'certificates' created successfully (or already exists).\n";

    // 3. SQL to insert sample data. We use 'INSERT OR IGNORE' to prevent errors on re-running.
    $insert_data_sql = "
    INSERT OR IGNORE INTO certificates (file_no, project_name, address, category, certificate_type, issue_date, expiry_date, detail_link)
    VALUES (
        324926,
        'The Park City Health Care',
        'Total Tower Bhaban (4th Floor), Firoz Shah Housing Estate, Akbarshah, Chattogram',
        'Orange',
        'Renew',
        '26-Jul-2026',
        '07-Jul-2027',
        '324926.pdf'
    );";

    $pdo->exec($insert_data_sql);
    echo "Sample data for file_no 324926 inserted successfully (or already exists).\n";

    echo "\nDatabase setup is complete! You can now use the verification page.";

} catch (PDOException $e) {
    die("Database setup failed: " . $e->getMessage());
}
