-- ====================================================================
-- Redirects + Sitemap URLs modules (2026-09-26)
--   1. `redirects` and `sitemap_urls` — the columns the user supplied, kept
--      as-is (INT AUTO_INCREMENT ids, so rows exported from an older site
--      import without changes), plus:
--        - `deleted_at` for soft delete / restore, same as every admin module;
--        - UNIQUE on the path (one rule / one entry per path);
--        - indexes for the list filters.
--   2. `redirects:*` and `sitemap_urls:*` permissions (the standard 11
--      actions), granted to the super_admin role.
-- Safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `redirects` (
  `id`               INT(11)      NOT NULL AUTO_INCREMENT,
  `source_path`      VARCHAR(500) NOT NULL COMMENT 'Path on this site, e.g. /old-page',
  `destination_path` VARCHAR(500) NOT NULL COMMENT 'Path on this site or a full http(s) URL',
  `redirect_type`    INT(11)      DEFAULT 301 COMMENT '301, 302, 307 or 308',
  `is_active`        TINYINT(1)   DEFAULT 1,
  `deleted_at`       TIMESTAMP    NULL DEFAULT NULL,
  `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_redirects_source` (`source_path`),
  INDEX `idx_redirects_active` (`is_active`),
  INDEX `idx_redirects_deleted` (`deleted_at`),
  INDEX `idx_redirects_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sitemap_urls` (
  `id`            INT(11)      NOT NULL AUTO_INCREMENT,
  `url_path`      VARCHAR(255) NOT NULL COMMENT 'Path on this site, e.g. /about',
  `priority`      DECIMAL(2,1) DEFAULT 0.5 COMMENT '0.0 – 1.0',
  `last_modified` DATE         DEFAULT NULL,
  `is_active`     TINYINT(1)   DEFAULT 1,
  `deleted_at`    TIMESTAMP    NULL DEFAULT NULL,
  `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sitemap_urls_path` (`url_path`),
  INDEX `idx_sitemap_urls_active` (`is_active`),
  INDEX `idx_sitemap_urls_deleted` (`deleted_at`),
  INDEX `idx_sitemap_urls_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If the tables already existed from an import without soft delete, add it.
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'redirects' AND COLUMN_NAME = 'deleted_at');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `redirects` ADD COLUMN `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `is_active`, ADD INDEX `idx_redirects_deleted` (`deleted_at`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sitemap_urls' AND COLUMN_NAME = 'deleted_at');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `sitemap_urls` ADD COLUMN `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `is_active`, ADD INDEX `idx_sitemap_urls_deleted` (`deleted_at`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Permissions — same name/action conventions as authors / product_tags.
INSERT IGNORE INTO `permissions` (`id`, `name`, `module`, `action`) VALUES
  (UUID(), 'redirects:read',                'redirects', 'read'),
  (UUID(), 'redirects:create',              'redirects', 'create'),
  (UUID(), 'redirects:update',              'redirects', 'update'),
  (UUID(), 'redirects:delete',              'redirects', 'delete'),
  (UUID(), 'redirects:bulk-delete',         'redirects', 'bulkdelete'),
  (UUID(), 'redirects:activate-deactivate', 'redirects', 'active_deactive'),
  (UUID(), 'redirects:view-deleted',        'redirects', 'view_deleted'),
  (UUID(), 'redirects:permanent-delete',    'redirects', 'delete_permanent'),
  (UUID(), 'redirects:restore',             'redirects', 'restore'),
  (UUID(), 'redirects:bulk-restore',        'redirects', 'restore_permanent'),
  (UUID(), 'redirects:view-timeline',       'redirects', 'view_timeline'),

  (UUID(), 'sitemap_urls:read',                'sitemap_urls', 'read'),
  (UUID(), 'sitemap_urls:create',              'sitemap_urls', 'create'),
  (UUID(), 'sitemap_urls:update',              'sitemap_urls', 'update'),
  (UUID(), 'sitemap_urls:delete',              'sitemap_urls', 'delete'),
  (UUID(), 'sitemap_urls:bulk-delete',         'sitemap_urls', 'bulkdelete'),
  (UUID(), 'sitemap_urls:activate-deactivate', 'sitemap_urls', 'active_deactive'),
  (UUID(), 'sitemap_urls:view-deleted',        'sitemap_urls', 'view_deleted'),
  (UUID(), 'sitemap_urls:permanent-delete',    'sitemap_urls', 'delete_permanent'),
  (UUID(), 'sitemap_urls:restore',             'sitemap_urls', 'restore'),
  (UUID(), 'sitemap_urls:bulk-restore',        'sitemap_urls', 'restore_permanent'),
  (UUID(), 'sitemap_urls:view-timeline',       'sitemap_urls', 'view_timeline');

INSERT IGNORE INTO `role_permissions` (`id`, `role_id`, `permission_id`)
SELECT UUID(), r.id, p.id
FROM `roles` r
JOIN `permissions` p ON p.module IN ('redirects', 'sitemap_urls')
WHERE r.name = 'super_admin';
