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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Tabela: users
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id` VARCHAR(191) NOT NULL COMMENT 'ID anónimo do dispositivo (ANDROID_ID) ou UUID gerado',
    `username` VARCHAR(64) DEFAULT NULL,
    `email` VARCHAR(191) DEFAULT NULL,
    `password_hash` VARCHAR(255) DEFAULT NULL,
    `role` ENUM('user','admin') NOT NULL DEFAULT 'user',
    `status` ENUM('active','banned') NOT NULL DEFAULT 'active',
    `api_token` VARCHAR(64) NOT NULL COMMENT 'Token usado nas chamadas autenticadas da API',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_device_id` (`device_id`),
    UNIQUE KEY `uniq_email` (`email`),
    UNIQUE KEY `uniq_api_token` (`api_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 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;

-- ---------------------------------------------------------------------
-- Utilizador admin de exemplo (ALTERA a password depois de importar!)
-- Password inicial: admin123  (hash bcrypt gerado abaixo)
-- ---------------------------------------------------------------------
INSERT INTO `users` (`device_id`, `username`, `email`, `password_hash`, `role`, `api_token`)
VALUES (
    'admin-seed-device',
    'admin',
    'admin@example.com',
    '$2b$10$veNG1N8KukA6D2qrxtn2COQCTjOe1sbS5NDfbhBpLUG6HK1YDzimS',
    'admin',
    '6095670c08a4a32373da385b2eff67c4ae3b728d'
)
ON DUPLICATE KEY UPDATE `username` = `username`;

SET FOREIGN_KEY_CHECKS = 1;