<?php

declare(strict_types=1);

namespace CliqThemes\StoreLocator\Schema;

defined('ABSPATH') || exit;

// Schema installation necessarily operates directly on plugin-owned tables.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

use CliqThemes\ORM\Schema\SchemaManager;
use CliqThemes\ORM\Schema\TableBlueprint;

/**
 * Owns the store-locator database schema.
 *
 * {@see defineSchema()} is the single definition of every table, and dbDelta
 * makes it safe to run repeatedly: it diffs the live table against the
 * definition and adds what is missing. A site records the schema signature it
 * installed ({@see schemaSignature()}) so that diff runs when the expected
 * schema changes instead of on every page load.
 */
final class SchemaInstaller
{
    /**
     * Prefix every table this plugin owns carries, on top of WordPress's own.
     * Generic names like `stores` or `categories` are not ours to claim: on a
     * real site they are as likely to belong to another plugin.
     */
    public const TABLE_PREFIX = 'cliq_sl_';

    /** Installed-schema version. Bump when a table's COLUMNS change. */
    public const DB_VERSION = '1.0.0';

    /** Option that records the installed schema signature. */
    public const DB_VERSION_OPTION = 'cliq_sl_schema_version';

    /**
     * Every table the plugin owns, in dependency order. Reversed for drops.
     *
     * @var string[]
     */
    /**
     * The tables this plugin itself owns. A feature adds its own through
     * cliq_sl_owned_tables, so uninstall removes exactly what was installed.
     *
     * @var string[]
     */
    private const TABLES = [
        'cliq_sl_stores',
        'cliq_sl_store_locations',
        'cliq_sl_store_hours',
        'cliq_sl_categories',
        'cliq_sl_store_category',
        'cliq_sl_brands',
        'cliq_sl_brand_store',
        'cliq_sl_store_meta',
        'cliq_sl_countries',
        'cliq_sl_custom_fields',
        'cliq_sl_custom_field_category',
    ];

    /**
     * Activation entry point: ensure the full schema exists, then seed required
     * reference data. Demo stores are created only from onboarding.
     */
    public static function install(): void
    {
        self::defineSchema(new SchemaManager());
        update_option(self::DB_VERSION_OPTION, self::schemaSignature(), false);

        DemoSeeder::seedAuxiliary();
    }

    /**
     * Identity of the schema this build expects: its version plus a digest of
     * the table list.
     *
     * The version alone is not enough. Plugin updates replace files without
     * running the activation hook, and the set of tables can differ between
     * builds that share a version — so a site can end up running code whose
     * tables were never created, and every query against them fails while the
     * recorded version still looks current. Folding the table list in means any
     * change to it is detected on the next load.
     */
    private static function schemaSignature(): string
    {
        return self::DB_VERSION . ':' . substr(md5(implode(',', self::TABLES)), 0, 12);
    }

    /**
     * Boot check, gated on {@see schemaSignature()}. A no-op once the recorded
     * signature matches, so it is cheap to call on every load. Otherwise it runs
     * the idempotent dbDelta pass, creating any missing table or column, and
     * records the new signature.
     */
    public static function ensure(): void
    {
        $signature = self::schemaSignature();
        if ((string) get_option(self::DB_VERSION_OPTION, '') === $signature) {
            return;
        }

        self::defineSchema(new SchemaManager());

        update_option(self::DB_VERSION_OPTION, $signature, false);
    }

    /**
     * Fully-qualified name of a table this plugin owns.
     *
     * Helpers that take a table name and prefix it are the one place a bare
     * name can slip through: the resulting query is valid SQL against a table
     * we do not own, so it returns nothing instead of failing, and the symptom
     * surfaces far from the cause. Route those names through here and a bare
     * one is a hard error at the first call.
     *
     * @throws \InvalidArgumentException When the name lacks the plugin prefix.
     */
    public static function qualify(string $table): string
    {
        if (! str_starts_with($table, self::TABLE_PREFIX)) {
            throw new \InvalidArgumentException(esc_html(
                "Store-locator table name is missing the '" . self::TABLE_PREFIX . "' prefix: " . $table
            ));
        }

        global $wpdb;

        return $wpdb->prefix . $table;
    }

    /**
     * Every table base-name this plugin owns, for teardown. Single source of
     * truth for {@see uninstall.php} so a new table can never be forgotten on
     * uninstall.
     *
     * @return string[]
     */
    public static function allTableNames(): array
    {
        /**
         * Filter the tables this installation owns.
         *
         * @param string[] $tables
         */
        $tables = (array) apply_filters('cliq_sl_owned_tables', self::TABLES);

        return array_values(array_unique(array_filter(array_map('strval', $tables))));
    }

    /**
     * DEV RESET: drop every plugin table, recreate the whole schema and reseed.
     * Wipes all store-locator data. Foreign-key checks are disabled around the
     * drops so inter-table constraints can't block the wipe.
     */
    public static function fresh(): void
    {
        global $wpdb;

        $wpdb->query('SET FOREIGN_KEY_CHECKS = 0');
        foreach (array_reverse(self::TABLES) as $table) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange -- Explicit development reset for plugin-owned tables.
            $wpdb->query($wpdb->prepare('DROP TABLE IF EXISTS %i', $wpdb->prefix . $table));
        }
        $wpdb->query('SET FOREIGN_KEY_CHECKS = 1');

        self::defineSchema(new SchemaManager());
        update_option(self::DB_VERSION_OPTION, self::schemaSignature(), false);

        DemoSeeder::seedAuxiliary();
    }

    /**
     * Single source of truth: create every table. dbDelta makes this safe to
     * run repeatedly (it diffs and adds missing columns in place).
     */
    private static function defineSchema(SchemaManager $schema): void
    {
        self::createStoresTable($schema);
        self::createStoreLocationsTable($schema);
        self::createStoreHoursTable($schema);
        self::createCategoriesTable($schema);
        self::createStoreCategoryTable($schema);
        self::createBrandsTable($schema);
        self::createBrandStoreTable($schema);
        self::createStoreMetaTable($schema);
        self::createCountriesTable($schema);
        self::createCustomFieldsTable($schema);
        self::createCustomFieldCategoryTable($schema);

        /**
         * Create the tables a feature of your own needs.
         *
         * @param SchemaManager $schema
         */
        do_action('cliq_sl_install_schema', $schema);
    }

    private static function createStoresTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_stores', static function (TableBlueprint $table): void {
            $table->id();
            $table->string('name');
            $table->string('slug');
            $table->longText('description', ['nullable' => true]);
            $table->string('status', 20, ['default' => 'active']);
            $table->boolean('featured', ['default' => 0]);
            $table->integer('ordering', 11, ['default' => 0]);
            // Multilingual (TRID model): lang='' + source_id=NULL is the canonical
            // "original"; translations share `trid` and point back via source_id.
            $table->string('lang', 10, ['default' => '']);
            $table->bigInteger('trid', 20, ['unsigned' => true, 'nullable' => true]);
            $table->bigInteger('source_id', 20, ['unsigned' => true, 'nullable' => true]);
            $table->string('phone', 30, ['nullable' => true]);
            $table->string('email', 120, ['nullable' => true]);
            $table->string('website', 255, ['nullable' => true]);
            // Average rating (0–5) + optional review count shown by the Rating
            // element. Authored per store — empty hides the element (no fake data).
            $table->decimal('rating', 3, 2, ['nullable' => true]);
            $table->integer('rating_count', 11, ['nullable' => true]);
            $table->string('city', 120, ['nullable' => true]);
            $table->string('country', 2, ['nullable' => true]);
            $table->bigInteger('logo_id', 20, ['unsigned' => true, 'nullable' => true]);
            $table->bigInteger('marker_id', 20, ['unsigned' => true, 'nullable' => true]);
            // HTML marker (a Design, purpose='marker') — parallel to marker_id
            // above; which one is used depends on the map widget's markerType.
            $table->bigInteger('marker_design_id', 20, ['unsigned' => true, 'nullable' => true]);
            // 'own' = use this store's marker_id/marker_design_id; 'category' =
            // inherit from the store's first category that has one.
            $table->string('marker_source', 20, ['default' => 'own']);
            $table->bigInteger('owner_id', 20, ['unsigned' => true, 'nullable' => true]);
            // Visibility window (scheduled publish / expire).
            $table->timestamps();
            $table->softDeletes();

            // Slug is unique per language so a translation can keep the slug.
            $table->unique(['slug', 'lang']);
            $table->index(['country']);
            $table->index(['status']);
            $table->index(['featured']);
            $table->index(['trid']);
        });
    }

    private static function createStoreLocationsTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_store_locations', static function (TableBlueprint $table): void {
            $table->id();
            $table->bigInteger('store_id', 20, ['unsigned' => true]);
            $table->string('label', 120, ['nullable' => true]);
            $table->string('address_line1', 255, ['nullable' => true]);
            $table->string('address_line2', 255, ['nullable' => true]);
            $table->string('city', 120, ['nullable' => true]);
            $table->string('state', 120, ['nullable' => true]);
            $table->string('postal_code', 30, ['nullable' => true]);
            $table->string('country', 2, ['nullable' => true]);
            $table->decimal('latitude', 10, 7, ['default' => 0]);
            $table->decimal('longitude', 10, 7, ['default' => 0]);
            $table->boolean('is_primary', ['default' => 0]);
            // Set by GeocodingService::fillMissingCoordinates() when a geocode
            // attempt fails, so the manual "Fill missing coordinates" batch
            // (Tools screen) can prioritise never-attempted rows over ones that
            // are known to keep failing, instead of getting stuck reprocessing
            // the same unresolvable addresses forever. Cleared implicitly on
            // success (the row's lat/lng change, so it drops out of the
            // "missing coordinates" WHERE clause entirely).
            $table->timestamp('geocode_failed_at', ['nullable' => true]);
            $table->timestamps();

            $table->index(['store_id']);
            // Radius search bounding-box pre-filters on these decimal columns.
            // (A generated POINT + SPATIAL index was dropped: the search never
            // used ST_Distance_Sphere, so it was write-time overhead only.)
            $table->index(['latitude', 'longitude']);
        });
    }

    private static function createStoreHoursTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_store_hours', static function (TableBlueprint $table): void {
            $table->id();
            $table->bigInteger('location_id', 20, ['unsigned' => true]);
            $table->integer('day_of_week', 11); // 0 (Sun) - 6 (Sat)
            $table->string('open_time', 5, ['nullable' => true]); // HH:MM
            $table->string('close_time', 5, ['nullable' => true]);
            $table->boolean('is_closed', ['default' => 0]);

            $table->index(['location_id']);
        });
    }

    private static function createCategoriesTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_categories', static function (TableBlueprint $table): void {
            $table->id();
            $table->string('name');
            $table->string('slug');
            $table->bigInteger('parent_id', 20, ['unsigned' => true, 'default' => 0]);
            $table->longText('icon', ['nullable' => true]); // inline SVG data-URI from the icon picker
            $table->string('color', 20, ['nullable' => true]);
            $table->bigInteger('marker_id', 20, ['unsigned' => true, 'nullable' => true]); // map marker stores can inherit
            $table->bigInteger('marker_design_id', 20, ['unsigned' => true, 'nullable' => true]); // HTML marker stores can inherit
            $table->integer('ordering', 11, ['default' => 0]);
            $table->boolean('is_active', ['default' => 1]);
            // Multilingual (TRID model) — see stores table.
            $table->string('lang', 10, ['default' => '']);
            $table->bigInteger('trid', 20, ['unsigned' => true, 'nullable' => true]);
            $table->bigInteger('source_id', 20, ['unsigned' => true, 'nullable' => true]);
            $table->timestamps();

            $table->unique(['slug', 'lang']);
            $table->index(['parent_id']);
            $table->index(['trid']);
        });
    }

    private static function createStoreCategoryTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_store_category', static function (TableBlueprint $table): void {
            $table->bigInteger('store_id', 20, ['unsigned' => true]);
            $table->bigInteger('category_id', 20, ['unsigned' => true]);

            $table->primary(['store_id', 'category_id']);
            $table->index(['category_id']);
        });
    }

    private static function createBrandsTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_brands', static function (TableBlueprint $table): void {
            $table->id();
            $table->string('name');
            $table->string('slug');
            $table->string('logo', 255, ['nullable' => true]);
            $table->text('description', ['nullable' => true]);
            $table->string('website', 255, ['nullable' => true]);
            $table->longText('meta', ['nullable' => true]); // custom fields JSON
            // Multilingual (TRID model) — see stores table.
            $table->string('lang', 10, ['default' => '']);
            $table->bigInteger('trid', 20, ['unsigned' => true, 'nullable' => true]);
            $table->bigInteger('source_id', 20, ['unsigned' => true, 'nullable' => true]);
            $table->timestamps();

            $table->unique(['slug', 'lang']);
            $table->index(['trid']);
        });
    }

    private static function createBrandStoreTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_brand_store', static function (TableBlueprint $table): void {
            $table->bigInteger('store_id', 20, ['unsigned' => true]);
            $table->bigInteger('brand_id', 20, ['unsigned' => true]);

            $table->primary(['store_id', 'brand_id']);
            $table->index(['brand_id']);
        });
    }


    private static function createStoreMetaTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_store_meta', static function (TableBlueprint $table): void {
            $table->id();
            $table->bigInteger('store_id', 20, ['unsigned' => true]);
            $table->string('meta_key');
            $table->longText('meta_value', ['nullable' => true]);

            $table->index(['store_id']);
            // Deliberately NOT unique on (store_id, meta_key). Several rows may
            // share a key: that is how a multi-value custom field (checkboxes)
            // stores its selections, and it is what lets a facet match one value
            // in SQL instead of unpicking a serialised blob. wp_postmeta has no
            // such constraint either, for the same reason.
            //
            // Writes are still race-safe: both writers delete the key's rows and
            // re-insert inside a transaction, which is what update_post_meta()
            // does. The guarantee moved from the index to the transaction.
            $table->index(['store_id', 'meta_key']);
            // A facet filters by key first, then compares values within it.
            // meta_value is longtext so it cannot be indexed directly; narrowing
            // on the key is what wp_postmeta relies on too.
            $table->index(['meta_key']);
        });
    }



    private static function createCountriesTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_countries', static function (TableBlueprint $table): void {
            $table->id();
            $table->string('name', 120);
            $table->string('iso2', 2);
            $table->string('iso3', 3, ['nullable' => true]);

            $table->index(['iso2']);
        });
    }

    private static function createCustomFieldsTable(SchemaManager $schema): void
    {
        $schema->createTable('cliq_sl_custom_fields', static function (TableBlueprint $table): void {
            $table->id();
            $table->string('field_key', 191);
            $table->string('label');
            $table->string('type', 40, ['default' => 'text']);
            // Which entity the field applies to: store | brand.
            $table->string('entity', 20, ['default' => 'store']);
            $table->string('section', 120, ['nullable' => true]);
            // JSON config: options, placeholder, help, default, required, sub-fields (repeater), min/max, etc.
            $table->longText('config', ['nullable' => true]);
            $table->boolean('is_repeatable', ['default' => 0]);
            $table->integer('ordering', 11, ['default' => 0]);
            $table->boolean('is_active', ['default' => 1]);
            $table->timestamps();

            $table->unique(['field_key']);
            $table->index(['is_active']);
        });
    }

    private static function createCustomFieldCategoryTable(SchemaManager $schema): void
    {
        // Links a custom field to one or more categories. A field with no rows
        // here applies to ALL stores; otherwise only to stores in those categories.
        $schema->createTable('cliq_sl_custom_field_category', static function (TableBlueprint $table): void {
            $table->bigInteger('field_id', 20, ['unsigned' => true]);
            $table->bigInteger('category_id', 20, ['unsigned' => true]);

            $table->primary(['field_id', 'category_id']);
            $table->index(['category_id']);
        });
    }
}
