
CREATE TABLE IF NOT EXISTS {wp_abj404_redirects} (
    `id` bigint(30) NOT NULL auto_increment,
    `url` varchar(2048) NOT NULL,
    `status` bigint(20) NOT NULL,
    `type` bigint(20) NOT NULL,
    `final_dest` varchar(2048) NOT NULL,
    `code` bigint(20) NOT NULL,
    `disabled` int(10) NOT NULL default 0,
    `timestamp` bigint(30) NOT NULL,
    `engine` varchar(64) DEFAULT NULL,
    `score` decimal(5,2) DEFAULT NULL COMMENT 'Match confidence score (0-100), NULL for manual redirects',
    `start_ts` bigint(20) DEFAULT NULL COMMENT 'Unix timestamp when redirect becomes active (NULL = always)',
    `end_ts` bigint(20) DEFAULT NULL COMMENT 'Unix timestamp when redirect expires (NULL = never)',
    `canonical_url` varchar(2048) DEFAULT NULL COMMENT 'Cached CONCAT(/, TRIM(BOTH / FROM url)) so the captured-page JOIN to logs_hits.requested_url is index-friendly. NULL until backfilled by buildRedirectsCanonicalUrlChunk().',
    `logshits` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Denormalized rolled-up 404 hit count for this redirect URL, copied from the wp_abj404_logs_hits rollup (the materialized COUNT-by-canonical-requested_url aggregate of logsv2) keyed on canonical_url. Maintained by real-time write-back + nightly reconcile; backfilled by backfillRedirectsDenormColumns(). 0 until backfilled. Do NOT re-aggregate raw logsv2 here: read logs_hits (report.md Finding 2).',
    `last_used` bigint(20) DEFAULT NULL COMMENT 'Denormalized last-hit timestamp for this redirect URL, copied from the wp_abj404_logs_hits rollup (its MAX(timestamp)-by-canonical-requested_url aggregate) keyed on canonical_url. NULL until backfilled.',
    `dest_for_view` varchar(2048) DEFAULT NULL COMMENT 'Denormalized resolved destination title/label for admin display + filterText search. NULL is the not-yet-backfilled sentinel; a backfilled row is always non-NULL (empty string at minimum).',
    `dest_sort_key` varchar(191) DEFAULT NULL COMMENT 'Indexable sort key for the admin Destination sort: LEFT(dest_for_view, 191). dest_for_view is varchar(2048) -> only prefix-indexable -> ORDER BY on it always filesorts; this narrow copy is fully indexable so the Destination sort is index-ordered like Hits/Last Used. Maintained alongside dest_for_view (live resolver + backfill/reconcile). NULL until backfilled (sorts first ascending, self-heals).',
    `url_sort_key` varchar(191) DEFAULT NULL COMMENT 'Indexable sort key for the admin URL sort: LEFT(url, 191). url is varchar(2048) -> only prefix-indexable -> ORDER BY url always filesorts (MySQL will not order by a prefix index even when every value is short); this narrow copy is fully indexable so the URL sort is index-ordered on the captured tab too. Maintained alongside url (set on every insert/edit via the Step 3c recompute, plus backfill/reconcile). NULL until backfilled (sorts first ascending, self-heals). url > 191 chars sharing a 191-char prefix tie-break by id.',
    `published_status` tinyint(4) DEFAULT NULL COMMENT 'Denormalized resolved publish state of the destination (1 = published/valid, 0 = broken/unpublished). NULL until backfilled.',
    PRIMARY KEY  (`id`),
    KEY `status` (`status`),
    KEY `type` (`type`),
    KEY `code` (`code`),
    KEY `timestamp` (`timestamp`),
    KEY `disabled` (`disabled`),
    KEY `url` (`url`(190)) USING BTREE,
    KEY `final_dest` (`final_dest`(190)) USING BTREE,
    KEY `idx_url_disabled_status` (`url`(190), `disabled`, `status`),
    KEY `idx_status_disabled` (`status`, `disabled`),
    KEY `idx_canonical_url` (`canonical_url`(190)) USING BTREE,
    KEY `idx_dest_for_view_id` (`dest_for_view`(190), `id`),
    KEY `idx_disabled_logshits_id` (`disabled`, `logshits`, `id`),
    KEY `idx_disabled_last_used_id` (`disabled`, `last_used`, `id`),
    KEY `idx_status_disabled_logshits_id` (`status`, `disabled`, `logshits`, `id`),
    KEY `idx_status_disabled_last_used_id` (`status`, `disabled`, `last_used`, `id`),
    KEY `idx_disabled_dest_sort_id` (`disabled`, `dest_sort_key`, `id`),
    KEY `idx_status_disabled_dest_sort_id` (`status`, `disabled`, `dest_sort_key`, `id`),
    KEY `idx_disabled_url_sort_id` (`disabled`, `url_sort_key`, `id`),
    KEY `idx_status_disabled_url_sort_id` (`status`, `disabled`, `url_sort_key`, `id`),
    KEY `idx_disabled_score_id` (`disabled`, `score`, `id`),
    KEY `idx_status_disabled_score_id` (`status`, `disabled`, `score`, `id`)
) COMMENT='404 Solution Plugin Redirects Table' AUTO_INCREMENT=1

