-- =====================================================================
--  Domain Lifecycle Toolkit — schema (MySQL 5.7+ / 8.x, InnoDB, utf8mb4)
--  Safe to run on shared / cPanel MySQL. No stored procs, no triggers.
-- =====================================================================

-- ---------------------------------------------------------------------
--  Hook execution telemetry (written by PdoHookLogger)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `hook_logs` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event`        VARCHAR(64)     NOT NULL,
    `hook_file`    VARCHAR(191)    NOT NULL,
    `success`      TINYINT(1)      NOT NULL DEFAULT 0,
    `skipped`      TINYINT(1)      NOT NULL DEFAULT 0,
    `duration_ms`  DECIMAL(12,3)   NOT NULL DEFAULT 0,
    `exception`    TEXT            NULL,
    `trace`        MEDIUMTEXT      NULL,
    `return_value` MEDIUMTEXT      NULL,
    `created_at`   DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_hook_logs_event`   (`event`),
    KEY `idx_hook_logs_created` (`created_at`),
    KEY `idx_hook_logs_success` (`success`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  Per-hook enable/disable (read by PdoHookState, written by Admin Panel).
--  A hook is ENABLED unless a row here sets enabled = 0.
--  hook_file = '' disables every file for that event.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `hook_state` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event`      VARCHAR(64)  NOT NULL,
    `hook_file`  VARCHAR(191) NOT NULL DEFAULT '',
    `enabled`    TINYINT(1)   NOT NULL DEFAULT 1,
    `updated_at` DATETIME     NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_hook_state` (`event`, `hook_file`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
--  OPTIONAL: domains table mirroring DropCatch\Lifecycle\Domain.
--  Provided for a complete standalone drop-in. Skip it if you already
--  have your own domains table — just map the columns in Domain::fromArray().
--  `statuses`, `optional_data`, `rdap` are stored as JSON.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `domains` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain`          VARCHAR(255)    NOT NULL,
    `tld`             VARCHAR(64)     NOT NULL DEFAULT '',
    `status`          VARCHAR(32)     NOT NULL DEFAULT 'UNKNOWN',
    `previous_status` VARCHAR(32)     NULL,
    `statuses`        JSON            NULL,
    `crm_id`          VARCHAR(191)    NULL,
    `notes`           TEXT            NULL,
    `optional_data`   JSON            NULL,
    `whois`           MEDIUMTEXT      NULL,
    `rdap`            JSON            NULL,
    `expiry_at`       INT UNSIGNED    NULL,
    `available_at`    INT UNSIGNED    NULL,
    `checked_at`      INT UNSIGNED    NULL,
    `created_at`      DATETIME        NOT NULL,
    `updated_at`      DATETIME        NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_domains_domain` (`domain`),
    KEY `idx_domains_status`     (`status`),
    KEY `idx_domains_checked_at` (`checked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;