-- Migration: refresh_tokens, app_config, report_pack, report_sticker,
-- google_id on users, and fixes for buggy FKs.

-- --------------------------------------------------------
-- 1. users.googleId (stable Google subject ID)
-- --------------------------------------------------------
ALTER TABLE `users`
  ADD COLUMN `googleId` VARCHAR(255) DEFAULT NULL AFTER `loginType`,
  ADD UNIQUE KEY `users_googleId_UQ` (`googleId`);

-- --------------------------------------------------------
-- 2. refresh_tokens
-- --------------------------------------------------------
CREATE TABLE `refresh_tokens` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `token` VARCHAR(512) NOT NULL,
  `expiresAt` DATETIME NOT NULL,
  `revoked` TINYINT(1) NOT NULL DEFAULT 0,
  `userAgent` VARCHAR(255) DEFAULT NULL,
  `ipAddress` VARCHAR(64) DEFAULT NULL,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `refresh_tokens_token_UQ` (`token`),
  KEY `refresh_tokens_user_id_idx` (`user_id`),
  CONSTRAINT `refresh_tokens_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 3. app_config (key-value store for runtime settings)
-- --------------------------------------------------------
CREATE TABLE `app_config` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `key_name` VARCHAR(100) NOT NULL,
  `value` TEXT DEFAULT NULL,
  `description` VARCHAR(255) DEFAULT NULL,
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_config_key_UQ` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `app_config` (`key_name`, `value`, `description`) VALUES
  ('min_android_version',     '1',     'Minimum allowed Android app version'),
  ('min_ios_version',         '1',     'Minimum allowed iOS app version'),
  ('maintenance_mode',        'false', 'If true, app shows maintenance screen'),
  ('force_update',            'false', 'If true, app requires update');

-- --------------------------------------------------------
-- 4. report_pack
-- --------------------------------------------------------
CREATE TABLE `report_pack` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `pack_id` INT NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `report_pack_user_idx` (`user_id`),
  KEY `report_pack_pack_idx` (`pack_id`),
  CONSTRAINT `report_pack_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `report_pack_pack_fk`
    FOREIGN KEY (`pack_id`) REFERENCES `pack` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 5. report_sticker
-- --------------------------------------------------------
CREATE TABLE `report_sticker` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `sticker_id` INT NOT NULL,
  `reason` VARCHAR(255) NOT NULL,
  `status` VARCHAR(30) NOT NULL DEFAULT 'pending',
  `createdAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `report_sticker_user_idx` (`user_id`),
  KEY `report_sticker_sticker_idx` (`sticker_id`),
  CONSTRAINT `report_sticker_user_fk`
    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `report_sticker_sticker_fk`
    FOREIGN KEY (`sticker_id`) REFERENCES `sticker` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------
-- 6. Fix buggy FKs on sticker_likes and sticker_shares
--    (both had user_id pointing at sticker.id instead of users.id)
-- --------------------------------------------------------
ALTER TABLE `sticker_likes` DROP FOREIGN KEY `sl_user_id`;
ALTER TABLE `sticker_likes`
  ADD CONSTRAINT `sl_user_id`
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `sticker_shares` DROP FOREIGN KEY `ss_user_id`;
ALTER TABLE `sticker_shares`
  ADD CONSTRAINT `ss_user_id`
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;

-- --------------------------------------------------------
-- 7. Fix pack_tray_icon cascade: SET NULL + UNIQUE(pack_id) is broken
--    because multiple NULL pack_ids would violate unique after deletes.
-- --------------------------------------------------------
ALTER TABLE `pack_tray_icon` DROP FOREIGN KEY `pack_tray_icon_ibfk_1`;
ALTER TABLE `pack_tray_icon` DROP FOREIGN KEY `pack_tray_icon_ibfk_2`;
ALTER TABLE `pack_tray_icon`
  ADD CONSTRAINT `pack_tray_icon_pack_fk`
  FOREIGN KEY (`pack_id`) REFERENCES `pack` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `pack_tray_icon`
  ADD CONSTRAINT `pack_tray_icon_sticker_fk`
  FOREIGN KEY (`sticker_id`) REFERENCES `sticker` (`id`)
  ON DELETE CASCADE ON UPDATE CASCADE;