<?php

declare(strict_types=1);

namespace PpPhp\Core\Calendar;

use PpPhp\Core\DB;

/**
 * CalendarSchema — self-provisioning storage for the Calendar / event app.
 *
 * The Calendar is a standalone application with its OWN tables (it never touches
 * the project's business tables). Those tables are created on demand with
 * CREATE TABLE IF NOT EXISTS the first time the app is used — the same self-heal
 * pattern the audit / 2FA features use — so no migration step is required.
 *
 * All tables use the `pp_` prefix so the System Tables registry classifies them
 * as platform tables (never shown as project data).
 */
final class CalendarSchema
{
    /** Once we've confirmed the tables exist this request, don't probe again. */
    private static bool $ready = false;

    public const CALENDARS = 'pp_calendars';
    public const EVENTS     = 'pp_events';
    public const SHARES     = 'pp_event_shares';
    public const ATTENDEES  = 'pp_event_attendees';
    public const REMINDERS  = 'pp_event_reminders';

    /**
     * Ensure every calendar table exists. Cheap on the hot path: after the first
     * successful check in a request it is a no-op; otherwise it probes for the
     * events table and, if missing, creates the whole set.
     */
    public static function ensure(): void
    {
        if (self::$ready) {
            return;
        }
        try {
            $exists = DB::FetchOne(
                "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? LIMIT 1",
                [self::EVENTS]
            );
            if (!$exists) {
                self::create();
            }
            self::ensureColumns();
            self::$ready = true;
        } catch (\Throwable $e) {
            // A restricted account may not read information_schema — fall back to
            // issuing the idempotent CREATE statements directly.
            self::create();
            self::$ready = true;
        }
    }

    /** Idempotent DDL for all calendar tables. */
    public static function create(): void
    {
        // Calendars — containers that group events and carry a colour + default
        // visibility. owner_user_id NULL marks a shared/system calendar.
        DB::Exec("CREATE TABLE IF NOT EXISTS `" . self::CALENDARS . "` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(120) NOT NULL,
            `color` VARCHAR(20) NOT NULL DEFAULT '#2563eb',
            `owner_user_id` INT NULL,
            `visibility` VARCHAR(16) NOT NULL DEFAULT 'private',
            `is_personal` TINYINT NOT NULL DEFAULT 1,
            `is_default` TINYINT NOT NULL DEFAULT 0,
            `created_at` DATETIME NULL,
            `updated_at` DATETIME NULL,
            KEY `idx_cal_owner` (`owner_user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Events — the heart of the app. visibility overrides the calendar's when
        // set; rrule holds an iCal recurrence; parent_event_id links a modified
        // occurrence back to its series.
        DB::Exec("CREATE TABLE IF NOT EXISTS `" . self::EVENTS . "` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `calendar_id` INT NOT NULL,
            `title` VARCHAR(255) NOT NULL,
            `description` TEXT NULL,
            `location` VARCHAR(255) NULL,
            `starts_at` DATETIME NOT NULL,
            `ends_at` DATETIME NULL,
            `all_day` TINYINT NOT NULL DEFAULT 0,
            `color` VARCHAR(20) NULL,
            `icon` VARCHAR(40) NULL,
            `image` VARCHAR(500) NULL,
            `owner_user_id` INT NOT NULL,
            `visibility` VARCHAR(16) NOT NULL DEFAULT 'inherit',
            `rrule` VARCHAR(255) NULL,
            `rrule_until` DATETIME NULL,
            `parent_event_id` INT NULL,
            `status` VARCHAR(16) NOT NULL DEFAULT 'confirmed',
            `created_at` DATETIME NULL,
            `updated_at` DATETIME NULL,
            KEY `idx_ev_cal` (`calendar_id`),
            KEY `idx_ev_owner` (`owner_user_id`),
            KEY `idx_ev_start` (`starts_at`),
            KEY `idx_ev_parent` (`parent_event_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Shares — grant view/edit on an event OR a whole calendar to everyone,
        // a group, or a specific user.
        DB::Exec("CREATE TABLE IF NOT EXISTS `" . self::SHARES . "` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `event_id` INT NULL,
            `calendar_id` INT NULL,
            `principal_type` VARCHAR(12) NOT NULL DEFAULT 'user',
            `principal_id` INT NULL,
            `permission` VARCHAR(8) NOT NULL DEFAULT 'view',
            `created_at` DATETIME NULL,
            KEY `idx_sh_event` (`event_id`),
            KEY `idx_sh_cal` (`calendar_id`),
            KEY `idx_sh_principal` (`principal_type`, `principal_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Attendees — meeting invitees + their RSVP.
        DB::Exec("CREATE TABLE IF NOT EXISTS `" . self::ATTENDEES . "` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `event_id` INT NOT NULL,
            `user_id` INT NOT NULL,
            `response` VARCHAR(12) NOT NULL DEFAULT 'pending',
            `is_organizer` TINYINT NOT NULL DEFAULT 0,
            `created_at` DATETIME NULL,
            KEY `idx_at_event` (`event_id`),
            KEY `idx_at_user` (`user_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Reminders — popup / email, N minutes before; sent_at tracks dispatch.
        DB::Exec("CREATE TABLE IF NOT EXISTS `" . self::REMINDERS . "` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `event_id` INT NOT NULL,
            `minutes_before` INT NOT NULL DEFAULT 15,
            `method` VARCHAR(10) NOT NULL DEFAULT 'popup',
            `sent_at` DATETIME NULL,
            KEY `idx_rm_event` (`event_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    /**
     * Self-heal newer columns onto an existing pp_events table (icon + image),
     * so upgrading a project that already had the calendar never needs a migration.
     */
    private static function ensureColumns(): void
    {
        $cols = ['icon' => "VARCHAR(40) NULL", 'image' => "VARCHAR(500) NULL"];
        foreach ($cols as $col => $def) {
            try {
                $has = DB::FetchOne(
                    "SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1",
                    [self::EVENTS, $col]
                );
                if (!$has) {
                    DB::Exec("ALTER TABLE `" . self::EVENTS . "` ADD COLUMN `$col` $def");
                }
            } catch (\Throwable $e) {
                // best-effort; a restricted account simply won't get the new columns
            }
        }
    }

    /**
     * The id of a user's default personal calendar, creating it on first use so
     * every user always has somewhere to put an event.
     */
    public static function defaultCalendarFor(int $userId): int
    {
        self::ensure();
        $row = DB::FetchAll(
            "SELECT id FROM `" . self::CALENDARS . "` WHERE owner_user_id = ? AND is_default = 1 LIMIT 1",
            [$userId]
        )[0] ?? null;
        if ($row) {
            return (int) $row['id'];
        }
        $now = date('Y-m-d H:i:s');
        DB::Insert(self::CALENDARS, [
            'name'          => 'My Calendar',
            'color'         => '#2563eb',
            'owner_user_id' => $userId,
            'visibility'    => 'private',
            'is_personal'   => 1,
            'is_default'    => 1,
            'created_at'    => $now,
            'updated_at'    => $now,
        ]);
        return (int) DB::FetchOne(
            "SELECT id FROM `" . self::CALENDARS . "` WHERE owner_user_id = ? AND is_default = 1 ORDER BY id DESC LIMIT 1",
            [$userId]
        );
    }

    /** Test-only: drop every calendar table (used by the backbone test cleanup). */
    public static function dropAll(): void
    {
        foreach ([self::REMINDERS, self::ATTENDEES, self::SHARES, self::EVENTS, self::CALENDARS] as $t) {
            DB::Exec("DROP TABLE IF EXISTS `$t`");
        }
        self::$ready = false;
    }
}
