-- Migration 003: Redirects, Media Assets, and System Settings
-- Run on MariaDB 10.6+ / MySQL 8.0+

CREATE TABLE IF NOT EXISTS `redirects` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `source_path` VARCHAR(255) NOT NULL,
    `target_path` VARCHAR(255) NOT NULL,
    `status_code` SMALLINT NOT NULL DEFAULT 301,
    `hits` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_redirects_source` (`source_path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `media` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` CHAR(36) NOT NULL,
    `uploaded_by` CHAR(36) NOT NULL,
    `filename_disk` VARCHAR(150) NOT NULL,
    `original_name` VARCHAR(255) NOT NULL,
    `mime_type` VARCHAR(50) NOT NULL,
    `file_size_bytes` INT UNSIGNED NOT NULL,
    `width` INT UNSIGNED NOT NULL,
    `height` INT UNSIGNED NOT NULL,
    `alt_text` VARCHAR(255) NOT NULL,
    `variants_json` TEXT DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_media_uuid` (`uuid`),
    KEY `idx_media_disk` (`filename_disk`),
    CONSTRAINT `fk_media_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `settings` (
    `key_name` VARCHAR(80) NOT NULL,
    `value_text` LONGTEXT DEFAULT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
