<?php

declare(strict_types=1);

namespace ProCalendarWP\Infrastructure;

final class Schema
{
    public const VERSION = '8';

    public static function install(): void
    {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();
        $occurrences = $wpdb->prefix . 'pcwp_occurrences';
        $entities = $wpdb->prefix . 'pcwp_event_entities';
        $jobs = $wpdb->prefix . 'pcwp_ai_jobs';

        if ((string) get_option('pcwp_schema_version', '') === '1') {
            self::materializeLegacyRows($occurrences, $entities);
        }

        dbDelta("CREATE TABLE {$occurrences} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id bigint(20) unsigned NOT NULL,
            series_id bigint(20) unsigned DEFAULT NULL,
            schedule_type varchar(16) NOT NULL DEFAULT 'continuous',
            schedule_revision varchar(32) NOT NULL DEFAULT '',
            start_utc datetime NOT NULL,
            end_utc datetime NOT NULL,
            start_local datetime NOT NULL,
            end_local datetime NOT NULL,
            timezone varchar(64) NOT NULL,
            all_day tinyint(1) NOT NULL DEFAULT 0,
            event_status varchar(32) NOT NULL DEFAULT 'scheduled',
            event_mode varchar(16) NOT NULL DEFAULT 'in-person',
            featured tinyint(1) NOT NULL DEFAULT 0,
            cost_decimal decimal(18,4) DEFAULT NULL,
            latitude decimal(10,7) DEFAULT NULL,
            longitude decimal(10,7) DEFAULT NULL,
            fingerprint char(64) NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY event_id (event_id),
            UNIQUE KEY event_fingerprint (event_id,fingerprint),
            KEY event_start (event_id,start_utc),
            KEY series_start (series_id,start_utc),
            KEY utc_range (start_utc,end_utc),
            KEY start_event (start_utc,event_id,end_utc),
            KEY local_range (start_local,end_local),
            KEY status_start (event_status,start_utc),
            KEY mode_start (event_mode,start_utc),
            KEY featured_start (featured,start_utc),
            KEY cost_start (cost_decimal,start_utc),
            KEY geo (latitude,longitude),
            KEY timezone (timezone)
        ) {$charset};");

        $intervals = $wpdb->prefix . 'pcwp_daily_intervals';
        dbDelta("CREATE TABLE {$intervals} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id bigint(20) unsigned NOT NULL,
            revision varchar(32) NOT NULL,
            slot_id varchar(64) NOT NULL,
            start_local datetime NOT NULL,
            end_local datetime NOT NULL,
            start_utc datetime NOT NULL,
            end_utc datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY event_slot (event_id,revision,slot_id),
            KEY event_range (event_id,revision,start_utc,end_utc),
            KEY utc_range (start_utc,end_utc)
        ) {$charset};");

        dbDelta("CREATE TABLE {$entities} (
            event_id bigint(20) unsigned NOT NULL,
            entity_id bigint(20) unsigned NOT NULL,
            entity_type varchar(16) NOT NULL,
            sort_order smallint(5) unsigned NOT NULL DEFAULT 0,
            PRIMARY KEY  (event_id,entity_id,entity_type),
            KEY entity_events (entity_id,entity_type,event_id),
            KEY event_type_order (event_id,entity_type,sort_order)
        ) {$charset};");

        dbDelta("CREATE TABLE {$jobs} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            request_uuid char(36) DEFAULT NULL,
            source_type varchar(16) NOT NULL,
            source_reference text NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'queued',
            requested_by bigint(20) unsigned NOT NULL,
            result longtext DEFAULT NULL,
            usage_data text DEFAULT NULL,
            error_message text DEFAULT NULL,
            options longtext DEFAULT NULL,
            artwork_data longtext DEFAULT NULL,
            event_id bigint(20) unsigned DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            expires_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY request_uuid (request_uuid),
            KEY requested_status (requested_by,status),
            KEY status_created (status,created_at),
            KEY expires_at (expires_at)
        ) {$charset};");

        $rsvps = $wpdb->prefix . 'pcwp_rsvps';
        dbDelta("CREATE TABLE {$rsvps} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id bigint(20) unsigned NOT NULL,
            email_hash char(64) DEFAULT NULL,
            name varchar(150) NOT NULL,
            email varchar(254) NOT NULL,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            status varchar(16) NOT NULL DEFAULT 'confirmed',
            token_hash char(64) NOT NULL,
            mail_status varchar(16) NOT NULL DEFAULT 'pending',
            schedule_hash char(64) NOT NULL DEFAULT '',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY event_email (event_id,email_hash),
            KEY event_status (event_id,status),
            KEY email (email)
        ) ENGINE=InnoDB {$charset};");
        Capabilities::install();
        update_option('pcwp_schema_version', self::VERSION, false);
    }

    private static function materializeLegacyRows(string $occurrences, string $entities): void
    {
        global $wpdb;
        if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $occurrences)) !== $occurrences) {
            return;
        }
        $duplicateIds = $wpdb->get_col("SELECT event_id FROM {$occurrences} GROUP BY event_id HAVING COUNT(*) > 1"); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        foreach ($duplicateIds as $sourceIdValue) {
            $sourceId = (int) $sourceIdValue;
            $source = get_post($sourceId);
            if (! $source instanceof \WP_Post) {
                continue;
            }
            $rows = $wpdb->get_results($wpdb->prepare("SELECT * FROM {$occurrences} WHERE event_id=%d ORDER BY start_utc ASC,id ASC", $sourceId)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            foreach (is_array($rows) ? $rows : [] as $index => $row) {
                if ($index === 0) {
                    self::syncLegacyRowMeta($sourceId, $row, $index);
                    continue;
                }
                $contentPost = ! empty($row->override_post_id) ? get_post((int) $row->override_post_id) : null;
                if (! $contentPost instanceof \WP_Post) {
                    $contentPost = $source;
                }
                $newId = wp_insert_post([
                    'post_type' => 'pcwp_event',
                    'post_status' => $source->post_status,
                    'post_title' => $contentPost->post_title,
                    'post_content' => $contentPost->post_content,
                    'post_excerpt' => $contentPost->post_excerpt,
                    'post_author' => (int) $source->post_author,
                    'post_name' => sanitize_title($source->post_name . '-' . str_replace([' ', ':'], ['-', ''], (string) $row->start_local)),
                ], true);
                if (is_wp_error($newId)) {
                    continue;
                }
                $newId = (int) $newId;
                $wpdb->query($wpdb->prepare(
                    "INSERT INTO {$wpdb->postmeta} (post_id,meta_key,meta_value) SELECT %d,meta_key,meta_value FROM {$wpdb->postmeta} WHERE post_id=%d AND meta_key LIKE %s",
                    $newId,
                    $sourceId,
                    '\\_pcwp\\_%',
                ));
                $wpdb->query($wpdb->prepare(
                    "INSERT IGNORE INTO {$wpdb->term_relationships} (object_id,term_taxonomy_id,term_order) SELECT %d,term_taxonomy_id,term_order FROM {$wpdb->term_relationships} WHERE object_id=%d",
                    $newId,
                    $sourceId,
                ));
                if ($wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $entities)) === $entities) {
                    $wpdb->query($wpdb->prepare("INSERT IGNORE INTO {$entities} (event_id,entity_id,entity_type,sort_order) SELECT %d,entity_id,entity_type,sort_order FROM {$entities} WHERE event_id=%d", $newId, $sourceId)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                }
                self::syncLegacyRowMeta($newId, $row, $index);
                $wpdb->update($occurrences, [
                    'event_id' => $newId,
                    'fingerprint' => hash('sha256', $newId . '|base'),
                ], ['id' => (int) $row->id], ['%d', '%s'], ['%d']);
            }
        }
    }

    private static function syncLegacyRowMeta(int $eventId, object $row, int $sequence): void
    {
        update_post_meta($eventId, '_pcwp_start', str_replace(' ', 'T', (string) $row->start_local));
        update_post_meta($eventId, '_pcwp_end', str_replace(' ', 'T', (string) $row->end_local));
        update_post_meta($eventId, '_pcwp_timezone', (string) $row->timezone);
        update_post_meta($eventId, '_pcwp_all_day', (int) $row->all_day);
        update_post_meta($eventId, '_pcwp_status', (string) $row->event_status);
        if (! empty($row->series_id)) {
            update_post_meta($eventId, '_pcwp_series_id', (int) $row->series_id);
            update_post_meta($eventId, '_pcwp_series_sequence', $sequence);
        }
    }
}