<?php

declare(strict_types=1);

namespace App\Core;

use App\Core\Database as DB;

/**
 * Idempotent schema self-heal for the room-booking feature, so databases
 * deployed before it existed gain the new tables and columns without a manual
 * migration step. Mirrors PaymentController::ensureTable() and the settings
 * self-heal in AdminController::settings(). Runs at most once per request.
 */
final class Schema
{
    private static bool $done = false;

    public static function heal(): void
    {
        if (self::$done) {
            return;
        }
        self::$done = true;

        $mysql = (App::config('db')['driver'] ?? 'sqlite') === 'mysql';

        if ($mysql) {
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS rooms (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                slug VARCHAR(120) NOT NULL UNIQUE,
                name VARCHAR(190) NOT NULL,
                description TEXT,
                image VARCHAR(500) NOT NULL DEFAULT '',
                active TINYINT(1) NOT NULL DEFAULT 1,
                sort_order INT NOT NULL DEFAULT 0
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS availability_rules (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                weekday TINYINT NOT NULL,
                start_time VARCHAR(5) NOT NULL,
                end_time VARCHAR(5) NOT NULL,
                active TINYINT(1) NOT NULL DEFAULT 1
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS blackout_dates (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                date VARCHAR(10) NOT NULL,
                room_id INT UNSIGNED DEFAULT NULL,
                reason VARCHAR(190) DEFAULT ''
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
              SQL);
        } else {
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS rooms (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                description TEXT DEFAULT '',
                image TEXT NOT NULL DEFAULT '',
                active INTEGER NOT NULL DEFAULT 1,
                sort_order INTEGER NOT NULL DEFAULT 0
              )
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS availability_rules (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                weekday INTEGER NOT NULL,
                start_time TEXT NOT NULL,
                end_time TEXT NOT NULL,
                active INTEGER NOT NULL DEFAULT 1
              )
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS blackout_dates (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                room_id INTEGER REFERENCES rooms(id) ON DELETE CASCADE,
                reason TEXT DEFAULT ''
              )
              SQL);
        }

        // bookings gains room/slot columns on installs created before them.
        // Empty list = the table doesn't exist yet; PaymentController::ensureTable()
        // will create it (with these columns) on the first booking.
        $cols = self::columns('bookings', $mysql);
        if ($cols !== []) {
            $add = [
                'room_id'      => $mysql ? 'INT UNSIGNED DEFAULT NULL' : 'INTEGER',
                'slot_date'    => $mysql ? "VARCHAR(10) DEFAULT ''" : "TEXT DEFAULT ''",
                'slot_time'    => $mysql ? "VARCHAR(5) DEFAULT ''" : "TEXT DEFAULT ''",
                'slot_minutes' => $mysql ? 'INT NOT NULL DEFAULT 0' : 'INTEGER DEFAULT 0',
            ];
            foreach ($add as $col => $type) {
                if (!in_array($col, $cols, true)) {
                    DB::execute("ALTER TABLE bookings ADD COLUMN $col $type");
                }
            }
        }

        // pages gains hero image + intro-line columns for the About-style page
        // template. ('subtitle', not 'lead' — LEAD is a reserved word in MySQL 8.)
        $pageCols = self::columns('pages', $mysql);
        if ($pageCols !== []) {
            $addPage = [
                'image'        => $mysql ? "VARCHAR(500) NOT NULL DEFAULT ''" : "TEXT NOT NULL DEFAULT ''",
                'image_layout' => $mysql ? "VARCHAR(20) NOT NULL DEFAULT ''" : "TEXT NOT NULL DEFAULT ''",
                'image_size'   => $mysql ? "VARCHAR(20) NOT NULL DEFAULT ''" : "TEXT NOT NULL DEFAULT ''",
                'subtitle'     => $mysql ? "VARCHAR(300) NOT NULL DEFAULT ''" : "TEXT NOT NULL DEFAULT ''",
            ];
            foreach ($addPage as $col => $type) {
                if (!in_array($col, $pageCols, true)) {
                    DB::execute("ALTER TABLE pages ADD COLUMN $col $type");
                }
            }
        }

        // Slot length setting for installs created before it existed.
        if (DB::selectOne('SELECT id FROM settings WHERE name = ?', ['slot_minutes']) === null) {
            DB::insert('settings', ['name' => 'slot_minutes', 'value' => '60']);
        }
    }

    /** @return list<string> Column names of a table, or [] if it doesn't exist. */
    private static function columns(string $table, bool $mysql): array
    {
        $rows = $mysql
            ? DB::select(
                'SELECT COLUMN_NAME AS name FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?',
                [$table],
            )
            : DB::select("PRAGMA table_info('$table')");
        return array_values(array_filter(array_map(
            static fn ($r): string => (string) ($r['name'] ?? ''),
            $rows,
        )));
    }
}
