<?php

/**
 * JCOGS Image Pro - Shared License Runtime Migration
 * ==================================================
 * Ensures shared CP license governance runtime table exists and is up to date.
 *
 * @category   ExpressionEngine Add-on
 * @package    JCOGS Image Pro
 * @author     JCOGS Design <contact@jcogs.net>
 * @copyright  Copyright (c) 2021 - 2026 JCOGS Design
 * @license    https://jcogs.net/add-ons/license/jcogs_img_pro
 * @version    2.0.6
 * @link       https://jcogs.net/
 * @since      2.0.0
 */

use ExpressionEngine\Service\Migration\Migration;

class CreateJcogsProLicenseRuntimeTable extends Migration
{
    private const TABLE = 'jcogs_pro_license_runtime';

    public function up()
    {
        ee()->load->dbforge();

        if (!ee()->db->table_exists(self::TABLE)) {
            $this->createTable();
        } else {
            $this->ensureColumns();
            $this->ensureIndexes();
        }

        $this->seedCurrentAddonRow();
    }

    public function down()
    {
        // Shared table across multiple add-ons: intentionally no-op.
    }

    private function createTable(): void
    {
        ee()->dbforge->add_field([
            'id' => [
                'type' => 'BIGINT',
                'constraint' => 20,
                'unsigned' => true,
                'auto_increment' => true,
                'null' => false,
            ],
            'site_id' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => false,
            ],
            'addon_short_name' => [
                'type' => 'VARCHAR',
                'constraint' => 64,
                'null' => false,
            ],
            'first_seen_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => false,
            ],
            'first_restricted_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => true,
            ],
            'last_known_status' => [
                'type' => 'VARCHAR',
                'constraint' => 32,
                'null' => false,
                'default' => 'na',
            ],
            'status_checked_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => false,
            ],
            'grace_ends_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => true,
            ],
            'created_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => false,
            ],
            'updated_at' => [
                'type' => 'INT',
                'constraint' => 10,
                'unsigned' => true,
                'null' => false,
            ],
        ]);

        ee()->dbforge->add_key('id', true);
        ee()->dbforge->create_table(self::TABLE, true);

        $this->ensureIndexes();
    }

    private function ensureColumns(): void
    {
        $this->addColumnIfMissing('site_id', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => false, 'default' => 1,
        ]);
        $this->addColumnIfMissing('addon_short_name', [
            'type' => 'VARCHAR', 'constraint' => 64, 'null' => false, 'default' => 'jcogs_img_pro',
        ]);
        $this->addColumnIfMissing('first_seen_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => false, 'default' => time(),
        ]);
        $this->addColumnIfMissing('first_restricted_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => true,
        ]);
        $this->addColumnIfMissing('last_known_status', [
            'type' => 'VARCHAR', 'constraint' => 32, 'null' => false, 'default' => 'na',
        ]);
        $this->addColumnIfMissing('status_checked_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => false, 'default' => time(),
        ]);
        $this->addColumnIfMissing('grace_ends_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => true,
        ]);
        $this->addColumnIfMissing('created_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => false, 'default' => time(),
        ]);
        $this->addColumnIfMissing('updated_at', [
            'type' => 'INT', 'constraint' => 10, 'unsigned' => true, 'null' => false, 'default' => time(),
        ]);
    }

    private function addColumnIfMissing(string $column, array $definition): void
    {
        if (ee()->db->field_exists($column, self::TABLE)) {
            return;
        }

        ee()->dbforge->add_column(self::TABLE, [$column => $definition]);
    }

    private function ensureIndexes(): void
    {
        $this->addIndexIfMissing('uq_site_addon', 'ALTER TABLE `' . ee()->db->dbprefix . self::TABLE . '` ADD UNIQUE KEY `uq_site_addon` (`site_id`, `addon_short_name`)');
        $this->addIndexIfMissing('idx_status', 'ALTER TABLE `' . ee()->db->dbprefix . self::TABLE . '` ADD KEY `idx_status` (`last_known_status`)');
        $this->addIndexIfMissing('idx_grace', 'ALTER TABLE `' . ee()->db->dbprefix . self::TABLE . '` ADD KEY `idx_grace` (`grace_ends_at`)');
    }

    private function addIndexIfMissing(string $indexName, string $sql): void
    {
        $table = ee()->db->dbprefix . self::TABLE;
        $existing = ee()->db->query('SHOW INDEX FROM `' . $table . '` WHERE Key_name = ' . ee()->db->escape($indexName));

        if ($existing->num_rows() > 0) {
            return;
        }

        ee()->db->query($sql);
    }

    private function seedCurrentAddonRow(): void
    {
        $siteId = (int) (ee()->config->item('site_id') ?: 1);
        $addonShortName = 'jcogs_img_pro';
        $now = time();

        $exists = ee()->db->select('id')
            ->from(self::TABLE)
            ->where('site_id', $siteId)
            ->where('addon_short_name', $addonShortName)
            ->limit(1)
            ->get()
            ->row('id');

        if ($exists) {
            return;
        }

        ee()->db->insert(self::TABLE, [
            'site_id' => $siteId,
            'addon_short_name' => $addonShortName,
            'first_seen_at' => $now,
            'first_restricted_at' => null,
            'last_known_status' => 'na',
            'status_checked_at' => $now,
            'grace_ends_at' => null,
            'created_at' => $now,
            'updated_at' => $now,
        ]);
    }
}
