<?php

namespace PloverCRM\Modules\Reminders\Database;

use PloverCRM\Common\CrmUserManager;

/**
 * Database Schema for Reminders
 */
class Schema
{
    const TABLE_NAME = 'plover_crm_reminders';
    const SCHEMA_VERSION_OPTION = 'plover_crm_reminders_schema_version';
    const SCHEMA_VERSION = '2';

    public static function getTableName(): string
    {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_NAME;
    }

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

        $table_name = self::getTableName();
        $charset_collate = $wpdb->get_charset_collate();

        if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name) {
            self::maybeMigrateTable($table_name);
            return;
        }

        $sql = "CREATE TABLE $table_name (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            contact_id bigint(20) unsigned NOT NULL,
            crm_user_id bigint(20) unsigned NOT NULL,
            title varchar(255) DEFAULT NULL,
            note text DEFAULT NULL,
            scheduled_at datetime NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'pending',
            completed_at datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            PRIMARY KEY (id),
            KEY contact_id (contact_id),
            KEY crm_user_id (crm_user_id),
            KEY status (status),
            KEY scheduled_at (scheduled_at),
            KEY crm_user_status_scheduled (crm_user_id, status, scheduled_at)
        ) $charset_collate;";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
        update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
    }

    public static function dropTable(): void
    {
        global $wpdb;
        $table_name = self::getTableName();
        $wpdb->query("DROP TABLE IF EXISTS $table_name");
    }

    private static function maybeMigrateTable(string $tableName): void
    {
        if (get_option(self::SCHEMA_VERSION_OPTION) === self::SCHEMA_VERSION) {
            return;
        }

        global $wpdb;

        $columns = $wpdb->get_col("SHOW COLUMNS FROM $tableName", 0);

        if (in_array('crm_user_id', $columns, true)) {
            update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
            return;
        }

        if (!in_array('user_id', $columns, true)) {
            update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
            return;
        }

        $wpdb->query("ALTER TABLE $tableName ADD COLUMN crm_user_id bigint(20) unsigned DEFAULT NULL AFTER contact_id");

        $rows = $wpdb->get_results("SELECT id, user_id FROM $tableName", ARRAY_A);

        foreach ((array) $rows as $row) {
            $crmUserId = CrmUserManager::getPublicIdByWordPressUserId((int) ($row['user_id'] ?? 0));

            if (!$crmUserId) {
                continue;
            }

            $wpdb->update(
                $tableName,
                ['crm_user_id' => $crmUserId],
                ['id' => (int) $row['id']],
                ['%d'],
                ['%d']
            );
        }

        $wpdb->query("DELETE FROM $tableName WHERE crm_user_id IS NULL");
        $wpdb->query("ALTER TABLE $tableName MODIFY crm_user_id bigint(20) unsigned NOT NULL");
        $wpdb->query("ALTER TABLE $tableName DROP COLUMN user_id");
        $wpdb->query("ALTER TABLE $tableName ADD KEY crm_user_id (crm_user_id)");
        $wpdb->query("ALTER TABLE $tableName ADD KEY crm_user_status_scheduled (crm_user_id, status, scheduled_at)");

        update_option(self::SCHEMA_VERSION_OPTION, self::SCHEMA_VERSION, false);
    }
}
