-- =====================================================================
-- StickerHub - Schema de Base de Dados (MySQL / MariaDB - cPanel ready)
-- =====================================================================
-- Importa este ficheiro via phpMyAdmin no cPanel (Aba "Importar")
-- =====================================================================


-- ---------------------------------------------------------------------
-- Tabela: sticker_packs
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sticker_packs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `identifier` VARCHAR(128) NOT NULL COMMENT 'Slug único, igual ao usado no WhatsApp content provider',
    `name` VARCHAR(128) NOT NULL,
    `publisher` VARCHAR(128) NOT NULL,
    `publisher_email` VARCHAR(191) DEFAULT NULL,
    `publisher_website` VARCHAR(255) DEFAULT NULL,
    `privacy_policy_website` VARCHAR(255) DEFAULT NULL,
    `license_agreement_website` VARCHAR(255) DEFAULT NULL,
    `tray_image_file` VARCHAR(191) NOT NULL COMMENT 'Nome do ficheiro do ícone (guardado em storage/packs/{identifier}/)',
    `image_data_version` VARCHAR(32) NOT NULL DEFAULT '1',
    `avoid_cache` TINYINT(1) NOT NULL DEFAULT 0,
    `animated_sticker_pack` TINYINT(1) NOT NULL DEFAULT 0,
    `android_play_store_link` VARCHAR(255) DEFAULT NULL,
    `ios_app_store_link` VARCHAR(255) DEFAULT NULL,
    `owner_user_id` INT UNSIGNED DEFAULT NULL COMMENT 'Utilizador que submeteu o pacote (NULL = pacote oficial/seed)',
    `status` ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    `rejection_reason` TEXT DEFAULT NULL,
    `downloads_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `likes_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `reports_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `reviewed_at` DATETIME DEFAULT NULL,
    `reviewed_by` INT UNSIGNED DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_identifier` (`identifier`),
    KEY `idx_status` (`status`),
    KEY `idx_owner` (`owner_user_id`),
    CONSTRAINT `fk_pack_owner` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabela: stickers
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stickers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `pack_id` INT UNSIGNED NOT NULL,
    `image_file` VARCHAR(191) NOT NULL COMMENT 'Nome do ficheiro, guardado em storage/packs/{identifier}/',
    `emojis` VARCHAR(64) DEFAULT NULL COMMENT 'Emojis separados por vírgula, ex: 😀,😂',
    `accessibility_text` VARCHAR(255) DEFAULT NULL,
    `file_size` INT UNSIGNED NOT NULL DEFAULT 0,
    `sort_order` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    KEY `idx_pack` (`pack_id`),
    CONSTRAINT `fk_sticker_pack` FOREIGN KEY (`pack_id`) REFERENCES `sticker_packs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabela: pack_likes  (sistema social: curtidas)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pack_likes` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `pack_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_like` (`pack_id`,`user_id`),
    CONSTRAINT `fk_like_pack` FOREIGN KEY (`pack_id`) REFERENCES `sticker_packs` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_like_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabela: pack_reports  (denúncias para moderação)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pack_reports` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `pack_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `reason` VARCHAR(255) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_report_pack` (`pack_id`),
    CONSTRAINT `fk_report_pack` FOREIGN KEY (`pack_id`) REFERENCES `sticker_packs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Tabela: app_config (chave/valor para configs gerais, ex: versão mínima)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `app_config` (
    `config_key` VARCHAR(64) NOT NULL,
    `config_value` TEXT,
    PRIMARY KEY (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

