-- ============================================================
--  NexSMS v2 — Additive MySQL / MariaDB Migration (consolidated)
--  Includes all v2 + v2.1 changes in a single file.
--  Safe to run multiple times.
--  Run after schema.sql / initial setup.
--  Compatible with MySQL 8.0+ and MariaDB 10.5+
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Extend existing tables ────────────────────────────────────

-- users: theme, referral_code, referred_by
ALTER TABLE `users`
    MODIFY COLUMN `status` ENUM('active','suspended','pending','inactive') NOT NULL DEFAULT 'active';

ALTER TABLE `users`
    ADD COLUMN IF NOT EXISTS `theme`         ENUM('light','dark') NOT NULL DEFAULT 'light',
    ADD COLUMN IF NOT EXISTS `referral_code` VARCHAR(16)           DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `referred_by`   CHAR(36)              DEFAULT NULL;

-- sms_logs: add send_method, extend status enum
ALTER TABLE `sms_logs`
    MODIFY COLUMN `status` ENUM('processing','completed','failed','scheduled','cancelled','pending') NOT NULL DEFAULT 'processing';

ALTER TABLE `sms_logs`
    ADD COLUMN IF NOT EXISTS `send_method` ENUM('system','api') NOT NULL DEFAULT 'system';

-- transactions: extend type enum
ALTER TABLE `transactions`
    MODIFY COLUMN `type` ENUM('deposit','transfer_in','transfer_out','sms_charge','topup','refund') NOT NULL;

-- ── Missing tables from base schema ─────────────────────────

CREATE TABLE IF NOT EXISTS `contact_group_members` (
  `id`         CHAR(36) NOT NULL,
  `group_id`   CHAR(36) NOT NULL,
  `contact_id` CHAR(36) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cgm_pair` (`group_id`, `contact_id`),
  KEY `idx_cgm_contact` (`contact_id`),
  CONSTRAINT `fk_cgm_group`   FOREIGN KEY (`group_id`)   REFERENCES `contact_groups` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cgm_contact` FOREIGN KEY (`contact_id`) REFERENCES `contacts`       (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sms_details` (
  `id`          CHAR(36)     NOT NULL,
  `log_id`      CHAR(36)     NOT NULL,
  `user_id`     CHAR(36)     NOT NULL,
  `phone`       VARCHAR(30)  NOT NULL,
  `status`      ENUM('pending','sent','delivered','failed') NOT NULL DEFAULT 'pending',
  `gateway_ref` VARCHAR(120) DEFAULT NULL,
  `error_msg`   TEXT         DEFAULT NULL,
  `sent_at`     DATETIME     DEFAULT NULL,
  `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_sms_det_log`  (`log_id`),
  KEY `idx_sms_det_user` (`user_id`),
  KEY `idx_sms_det_phone` (`phone`),
  KEY `idx_sms_det_status` (`status`),
  CONSTRAINT `fk_sms_det_log`  FOREIGN KEY (`log_id`)  REFERENCES `sms_logs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sms_det_user` FOREIGN KEY (`user_id`) REFERENCES `users`    (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `api_keys` (
  `id`         CHAR(36)                  NOT NULL,
  `user_id`    CHAR(36)                  NOT NULL,
  `name`       VARCHAR(80)               NOT NULL,
  `key_hash`   VARCHAR(64)               NOT NULL,
  `key_prefix` VARCHAR(8)                NOT NULL,
  `status`     ENUM('active','revoked')  NOT NULL DEFAULT 'active',
  `last_used`  DATETIME     DEFAULT NULL,
  `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME     DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_api_key_hash` (`key_hash`),
  KEY `idx_ak_user`   (`user_id`),
  KEY `idx_ak_status` (`status`),
  CONSTRAINT `fk_ak_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── New Feature Tables ────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pricing_tiers` (
  `id`            CHAR(36)      NOT NULL,
  `scope`         VARCHAR(10)   NOT NULL DEFAULT 'global',
  `user_id`       CHAR(36)      DEFAULT NULL,
  `min_qty`       INT UNSIGNED  NOT NULL DEFAULT 1,
  `max_qty`       INT UNSIGNED  DEFAULT NULL,
  `price_per_sms` DECIMAL(10,6) NOT NULL,
  `created_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pricing_scope`   (`scope`),
  KEY `idx_pricing_user_id` (`user_id`),
  CONSTRAINT `fk_pricing_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id`            CHAR(36)                              NOT NULL,
  `user_id`       CHAR(36)                              NOT NULL,
  `sms_units`     INT UNSIGNED                          NOT NULL DEFAULT 0,
  `amount`        DECIMAL(18,6)                         NOT NULL DEFAULT 0,
  `price_per_sms` DECIMAL(10,6)                         NOT NULL DEFAULT 0,
  `status`        ENUM('pending','paid','cancelled')    NOT NULL DEFAULT 'pending',
  `note`          TEXT                                  DEFAULT NULL,
  `paid_at`       DATETIME                              DEFAULT NULL,
  `paid_by`       CHAR(36)                              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`),
  KEY `idx_orders_user`   (`user_id`),
  KEY `idx_orders_status` (`status`),
  CONSTRAINT `fk_orders_user`    FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_orders_paid_by` FOREIGN KEY (`paid_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sender_ids` (
  `id`          CHAR(36)                  NOT NULL,
  `name`        VARCHAR(20)               NOT NULL,
  `description` TEXT                      DEFAULT NULL,
  `status`      ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_by`  CHAR(36)                  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_sender_name` (`name`),
  KEY `idx_sid_status` (`status`),
  CONSTRAINT `fk_sid_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sender_id_assignments` (
  `id`            CHAR(36) NOT NULL,
  `sender_id_id`  CHAR(36) NOT NULL,
  `user_id`       CHAR(36) NOT NULL,
  `assigned_by`   CHAR(36) DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_sida_pair` (`sender_id_id`, `user_id`),
  KEY `idx_sida_user`      (`user_id`),
  KEY `idx_sida_sender_id` (`sender_id_id`),
  CONSTRAINT `fk_sida_sid`  FOREIGN KEY (`sender_id_id`) REFERENCES `sender_ids` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sida_user` FOREIGN KEY (`user_id`)      REFERENCES `users`      (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sida_by`   FOREIGN KEY (`assigned_by`)  REFERENCES `users`      (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `sender_id_requests` (
  `id`            CHAR(36)                              NOT NULL,
  `user_id`       CHAR(36)                              NOT NULL,
  `sender_name`   VARCHAR(20)                           NOT NULL,
  `reason`        TEXT                                  DEFAULT NULL,
  `document_path` TEXT                                  DEFAULT NULL,
  `status`        ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `reviewed_by`   CHAR(36)                              DEFAULT NULL,
  `review_note`   TEXT                                  DEFAULT NULL,
  `reviewed_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`),
  KEY `idx_sidr_user`   (`user_id`),
  KEY `idx_sidr_status` (`status`),
  CONSTRAINT `fk_sidr_user`   FOREIGN KEY (`user_id`)     REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sidr_rev`    FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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 settings rows ────────────────────────────────────

INSERT IGNORE INTO `settings` (`key`, `value`) VALUES
    ('system_name',                     'NexSMS'),
    ('system_logo',                     ''),
    ('account_creation_fee_reseller',   '0'),
    ('account_creation_fee_customer',   '0'),
    ('gateway_username',                ''),
    ('gateway_password',                ''),
    ('referral_bonus_sms',              '10'),
    ('referee_bonus_sms',               '5'),
    ('referral_enabled',                'false');

SET FOREIGN_KEY_CHECKS = 1;