<?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);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS guesthouse (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(190) NOT NULL DEFAULT '',
                subtitle VARCHAR(300) NOT NULL DEFAULT '',
                description MEDIUMTEXT NOT NULL,
                image VARCHAR(500) NOT NULL DEFAULT '',
                gallery TEXT NOT NULL,
                active TINYINT(1) NOT NULL DEFAULT 1
              ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS guesthouse_units (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(190) NOT NULL DEFAULT '',
                subtitle VARCHAR(300) NOT NULL DEFAULT '',
                description MEDIUMTEXT NOT NULL,
                image VARCHAR(500) NOT NULL DEFAULT '',
                gallery TEXT NOT NULL,
                amenities TEXT NOT NULL,
                price_per_night DECIMAL(8,2) NOT NULL DEFAULT 0,
                max_guests INT NOT NULL DEFAULT 1,
                bedrooms INT NOT NULL DEFAULT 1,
                bathrooms INT NOT NULL DEFAULT 1,
                min_nights INT NOT NULL DEFAULT 1,
                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 stay_blackouts (
                id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                date VARCHAR(10) NOT NULL,
                unit_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);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS guesthouse (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL DEFAULT '',
                subtitle TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                image TEXT NOT NULL DEFAULT '',
                gallery TEXT NOT NULL DEFAULT '[]',
                active INTEGER NOT NULL DEFAULT 1
              )
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS guesthouse_units (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL DEFAULT '',
                subtitle TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                image TEXT NOT NULL DEFAULT '',
                gallery TEXT NOT NULL DEFAULT '[]',
                amenities TEXT NOT NULL DEFAULT '[]',
                price_per_night REAL NOT NULL DEFAULT 0,
                max_guests INTEGER NOT NULL DEFAULT 1,
                bedrooms INTEGER NOT NULL DEFAULT 1,
                bathrooms INTEGER NOT NULL DEFAULT 1,
                min_nights INTEGER NOT NULL DEFAULT 1,
                active INTEGER NOT NULL DEFAULT 1,
                sort_order INTEGER NOT NULL DEFAULT 0
              )
              SQL);
            DB::execute(<<<'SQL'
              CREATE TABLE IF NOT EXISTS stay_blackouts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                unit_id INTEGER REFERENCES guesthouse_units(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',
                // Guest-house stays: which unit, nightly check-in/out range + party size.
                'unit_id'      => $mysql ? 'INT UNSIGNED DEFAULT NULL' : 'INTEGER',
                'check_in'     => $mysql ? "VARCHAR(10) DEFAULT ''" : "TEXT DEFAULT ''",
                'check_out'    => $mysql ? "VARCHAR(10) DEFAULT ''" : "TEXT DEFAULT ''",
                'guests'       => $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");
                }
            }
        }

        // stay_blackouts gains unit_id on installs created before multi-unit
        // support (NULL there means "every unit", so nothing already stored
        // needs to change meaning).
        $blackoutCols = self::columns('stay_blackouts', $mysql);
        if ($blackoutCols !== [] && !in_array('unit_id', $blackoutCols, true)) {
            DB::execute('ALTER TABLE stay_blackouts ADD COLUMN unit_id ' . ($mysql ? 'INT UNSIGNED DEFAULT NULL' : 'INTEGER'));
        }

        // 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']);
        }

        // Older installs stored one listing's specs directly on the `guesthouse`
        // row. Move that into a first unit so nothing already saved (photos,
        // pricing, description) is lost when multi-unit support arrives —
        // detected by the legacy price_per_night column still being present.
        if (DB::selectOne('SELECT id FROM guesthouse_units LIMIT 1') === null) {
            $legacyCols = self::columns('guesthouse', $mysql);
            $old = in_array('price_per_night', $legacyCols, true)
                ? DB::selectOne('SELECT * FROM guesthouse LIMIT 1')
                : null;
            DB::insert('guesthouse_units', $old !== null ? [
                'name'            => $old['title'] !== '' ? $old['title'] : 'Main Unit',
                'subtitle'        => $old['subtitle'],
                'description'     => $old['description'],
                'image'           => $old['image'],
                'gallery'         => $old['gallery'],
                'amenities'       => $old['amenities'],
                'price_per_night' => $old['price_per_night'],
                'max_guests'      => $old['max_guests'],
                'bedrooms'        => $old['bedrooms'],
                'bathrooms'       => $old['bathrooms'],
                'min_nights'      => $old['min_nights'],
                'active'          => $old['active'],
                'sort_order'      => 0,
            ] : self::defaultGuesthouseUnit());
        }

        // Seed the guest-house page intro row so the page and CMS editor have
        // content to show before the owner customises it.
        if (DB::selectOne('SELECT id FROM guesthouse LIMIT 1') === null) {
            DB::insert('guesthouse', self::defaultGuesthouse());
        }
    }

    /** Default guest-house page intro — one row, edited in the CMS. */
    public static function defaultGuesthouse(): array
    {
        return [
            'title'       => 'Our Guest House',
            'subtitle'    => 'Your home away from home in the heart of Antigua',
            'description' => '<p>A handful of self-contained units on the same quiet property, a few '
                . 'minutes from St. John\'s and our spa. Pick the one that fits your stay and book its '
                . 'dates directly below.</p>',
            'image'       => '',
            'gallery'     => json_encode([], JSON_UNESCAPED_SLASHES),
            'active'      => 1,
        ];
    }

    /** Default single unit, seeded only when no legacy listing exists to migrate. */
    public static function defaultGuesthouseUnit(): array
    {
        return [
            'name'            => 'Main Unit',
            'subtitle'        => 'Your home away from home in the heart of Antigua',
            'description'     => '<p>Spacious two-bedroom apartment ideal for families or groups. '
                . 'Includes a full kitchen, living and dining areas, two bathrooms, and a private '
                . 'terrace perfect for morning yoga.</p>',
            'image'           => '',
            'gallery'         => json_encode([], JSON_UNESCAPED_SLASHES),
            'amenities'       => json_encode([
                'Full kitchen', 'Two bathrooms', 'Private terrace', 'Living & dining areas',
                'Air conditioning', 'Fast Wi-Fi', 'Walk to St. John\'s',
            ], JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE),
            'price_per_night' => 150,
            'max_guests'      => 4,
            'bedrooms'        => 2,
            'bathrooms'       => 2,
            'min_nights'      => 2,
            'active'          => 1,
            'sort_order'      => 0,
        ];
    }

    /** @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,
        )));
    }
}
