-- ============================================================
--  NexSMS v2.1 — MySQL/MariaDB Patch Migration
--  Fixes: users.theme default ('dark' → 'light')
--  Adds:  referral_codes, referral_uses tables
--  Safe to run multiple times (guarded).
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- Fix users.theme default to 'light' (also update existing 'dark' defaults)
ALTER TABLE `users`
    MODIFY COLUMN `theme` ENUM('light','dark') NOT NULL DEFAULT 'light';

-- Backfill existing rows that were defaulted to 'dark' — optional
UPDATE `users` SET `theme` = 'light' WHERE `theme` = 'dark';

-- referral_codes: per-user referral codes and bonus configuration
CREATE TABLE IF NOT EXISTS `referral_codes` (
  `id`            CHAR(36)    NOT NULL,
  `user_id`       CHAR(36)    NOT NULL,
  `code`          VARCHAR(16) NOT NULL,
  `bonus_sms`     INT         NOT NULL DEFAULT 0,
  `referee_bonus` INT         NOT NULL DEFAULT 0,
  `used_count`    INT         NOT NULL DEFAULT 0,
  `is_active`     TINYINT(1)  NOT NULL DEFAULT 1,
  `expires_at`    DATETIME    DEFAULT NULL,
  `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 `uq_referral_code` (`code`),
  KEY `idx_referral_user`   (`user_id`),
  KEY `idx_referral_active` (`is_active`),
  CONSTRAINT `fk_referral_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- referral_uses: record each time a referral code was used
CREATE TABLE IF NOT EXISTS `referral_uses` (
  `id`              CHAR(36) NOT NULL,
  `code_id`         CHAR(36) NOT NULL,
  `referee_user_id` CHAR(36) NOT NULL,
  `bonus_granted`   INT      NOT NULL DEFAULT 0,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_referral_use` (`code_id`, `referee_user_id`),
  KEY `idx_referral_use_code` (`code_id`),
  CONSTRAINT `fk_referral_use_code`    FOREIGN KEY (`code_id`)         REFERENCES `referral_codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_referral_use_referee` FOREIGN KEY (`referee_user_id`) REFERENCES `users`          (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default referral settings
INSERT IGNORE INTO `settings` (`key`, `value`) VALUES
  ('referral_bonus_sms',  '10'),
  ('referee_bonus_sms',   '5'),
  ('referral_enabled',    'false');

SET FOREIGN_KEY_CHECKS = 1;