-- ============================================================
--  NexSMS Bulk SMS Platform — COMPLETE MySQL Database
--  Single file: all tables + v2 columns + seed data
--  Compatible with MySQL 8.0+ and MariaDB 10.5+
--
--  HOW TO IMPORT:
--    Option A (phpMyAdmin): Create a new database, then import this file.
--    Option B (CLI):
--      mysql -u root -p
--      CREATE DATABASE nexsms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--      USE nexsms;
--      SOURCE /path/to/nexsms_mysql.sql;
--
--  DEFAULT ADMIN LOGIN:
--    Email:    admin@bulksms.com
--    Password: Admin@123456
--
--  After import, update sms-php/.env:
--    database.default.hostname = localhost
--    database.default.database = nexsms
--    database.default.username = your_db_user
--    database.default.password = your_db_pass
--    database.default.DBDriver = MySQLi
--    database.default.port     = 3306
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ── Users ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`                CHAR(36)       NOT NULL,
  `name`              VARCHAR(120)   NOT NULL,
  `email`             VARCHAR(180)   NOT NULL,
  `password_hash`     VARCHAR(255)   NOT NULL,
  `role`              ENUM('admin','reseller','customer') NOT NULL DEFAULT 'customer',
  `status`            ENUM('active','suspended','pending','inactive') NOT NULL DEFAULT 'active',
  `phone`             VARCHAR(30)    DEFAULT NULL,
  `company`           VARCHAR(120)   DEFAULT NULL,
  `reseller_id`       CHAR(36)       DEFAULT NULL,
  `sms_balance`       DECIMAL(18,4)  NOT NULL DEFAULT 0,
  `wallet_balance`    DECIMAL(18,4)  NOT NULL DEFAULT 0,
  `sms_buying_price`  DECIMAL(10,6)  NOT NULL DEFAULT 0.010000,
  `sms_selling_price` DECIMAL(10,6)  NOT NULL DEFAULT 0.050000,
  `api_key`           VARCHAR(64)    DEFAULT NULL,
  `theme`             ENUM('light','dark') NOT NULL DEFAULT 'light',
  `referral_code`     VARCHAR(16)    DEFAULT NULL,
  `referred_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_users_email`   (`email`),
  UNIQUE KEY `uq_users_api_key` (`api_key`),
  KEY `idx_users_role`      (`role`),
  KEY `idx_users_reseller`  (`reseller_id`),
  KEY `idx_users_status`    (`status`),
  KEY `idx_users_created`   (`created_at`),
  CONSTRAINT `fk_users_reseller` FOREIGN KEY (`reseller_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contact Groups ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contact_groups` (
  `id`          CHAR(36)     NOT NULL,
  `user_id`     CHAR(36)     NOT NULL,
  `name`        VARCHAR(100) NOT NULL,
  `description` TEXT         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_cg_user`    (`user_id`),
  KEY `idx_cg_created` (`created_at`),
  CONSTRAINT `fk_cg_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contacts ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `contacts` (
  `id`         CHAR(36)     NOT NULL,
  `user_id`    CHAR(36)     NOT NULL,
  `group_id`   CHAR(36)     DEFAULT NULL,
  `phone`      VARCHAR(30)  NOT NULL,
  `name`       VARCHAR(120) DEFAULT NULL,
  `email`      VARCHAR(180) DEFAULT NULL,
  `status`     ENUM('active','unsubscribed','invalid') NOT NULL DEFAULT 'active',
  `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_contacts_user`    (`user_id`),
  KEY `idx_contacts_phone`   (`phone`),
  KEY `idx_contacts_group`   (`group_id`),
  KEY `idx_contacts_status`  (`status`),
  KEY `idx_contacts_created` (`created_at`),
  CONSTRAINT `fk_contacts_user`  FOREIGN KEY (`user_id`)  REFERENCES `users`          (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_contacts_group` FOREIGN KEY (`group_id`) REFERENCES `contact_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Contact Group Members (many-to-many) ──────────────────────────
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;

-- ── SMS Logs (one row per campaign) ───────────────────────────────
CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id`               CHAR(36)      NOT NULL,
  `user_id`          CHAR(36)      NOT NULL,
  `sender_id`        VARCHAR(20)   NOT NULL,
  `message`          TEXT          NOT NULL,
  `recipients_count` INT UNSIGNED  NOT NULL DEFAULT 0,
  `delivered_count`  INT UNSIGNED  NOT NULL DEFAULT 0,
  `failed_count`     INT UNSIGNED  NOT NULL DEFAULT 0,
  `total_cost`       DECIMAL(18,6) NOT NULL DEFAULT 0,
  `price_per_sms`    DECIMAL(10,6) NOT NULL DEFAULT 0,
  `status`           ENUM('processing','completed','failed','scheduled','cancelled','pending') NOT NULL DEFAULT 'processing',
  `send_method`      ENUM('system','api') NOT NULL DEFAULT 'system',
  `scheduled_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_sms_logs_user`    (`user_id`),
  KEY `idx_sms_logs_status`  (`status`),
  KEY `idx_sms_logs_created` (`created_at`),
  CONSTRAINT `fk_sms_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SMS Details (one row per recipient) ───────────────────────────
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;

-- ── SMS Queue ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sms_queue` (
  `id`              CHAR(36)     NOT NULL,
  `log_id`          CHAR(36)     NOT NULL,
  `user_id`         CHAR(36)     NOT NULL,
  `phone`           VARCHAR(30)  NOT NULL,
  `message`         TEXT         NOT NULL,
  `sender_id`       VARCHAR(20)  NOT NULL,
  `status`          ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
  `attempts`        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `last_attempt_at` DATETIME     DEFAULT NULL,
  `error_message`   TEXT         DEFAULT NULL,
  `scheduled_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_queue_status`    (`status`),
  KEY `idx_queue_log`       (`log_id`),
  KEY `idx_queue_user`      (`user_id`),
  KEY `idx_queue_created`   (`created_at`),
  KEY `idx_queue_scheduled` (`scheduled_at`),
  CONSTRAINT `fk_queue_log`  FOREIGN KEY (`log_id`)  REFERENCES `sms_logs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_queue_user` FOREIGN KEY (`user_id`) REFERENCES `users`    (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Transactions ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`             CHAR(36)      NOT NULL,
  `user_id`        CHAR(36)      NOT NULL,
  `type`           ENUM('deposit','transfer_in','transfer_out','sms_charge','topup','refund') NOT NULL,
  `sms_units`      DECIMAL(18,4) NOT NULL DEFAULT 0,
  `amount`         DECIMAL(18,6) NOT NULL DEFAULT 0,
  `price_per_sms`  DECIMAL(10,6) DEFAULT NULL,
  `profit`         DECIMAL(18,6) DEFAULT NULL,
  `balance_before` DECIMAL(18,4) NOT NULL DEFAULT 0,
  `balance_after`  DECIMAL(18,4) NOT NULL DEFAULT 0,
  `reference_id`   CHAR(36)      DEFAULT NULL,
  `note`           VARCHAR(255)  DEFAULT NULL,
  `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tx_user`    (`user_id`),
  KEY `idx_tx_type`    (`type`),
  KEY `idx_tx_created` (`created_at`),
  KEY `idx_tx_ref`     (`reference_id`),
  CONSTRAINT `fk_tx_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Activity Logs ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id`          CHAR(36)    NOT NULL,
  `user_id`     CHAR(36)    NOT NULL,
  `action`      VARCHAR(80) NOT NULL,
  `description` TEXT        DEFAULT NULL,
  `ip_address`  VARCHAR(45) DEFAULT NULL,
  `created_at`  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_act_user`    (`user_id`),
  KEY `idx_act_action`  (`action`),
  KEY `idx_act_created` (`created_at`),
  CONSTRAINT `fk_act_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── API Keys ──────────────────────────────────────────────────────
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,
  `expires_at` DATETIME    DEFAULT NULL,
  `created_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  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;

-- ── Settings ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `settings` (
  `key`        VARCHAR(60) NOT NULL,
  `value`      TEXT        NOT NULL,
  `updated_at` DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── Pricing Tiers ─────────────────────────────────────────────────
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;

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

-- ── Sender IDs ────────────────────────────────────────────────────
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;

-- ── Sender ID Assignments ─────────────────────────────────────────
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;

-- ── Sender ID Requests ────────────────────────────────────────────
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 ────────────────────────────────────────────────
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 ─────────────────────────────────────────────────
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;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  SEED DATA — Default settings + admin account
-- ============================================================

-- Default settings
INSERT INTO `settings` (`key`, `value`) VALUES
  ('base_sms_cost',                  '0.0100'),
  ('system_name',                    'NexSMS'),
  ('system_logo',                    ''),
  ('max_sms_per_job',                '1000'),
  ('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')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);

-- Default admin user
-- Password: Admin@123456  (bcrypt cost=12)
INSERT INTO `users`
  (`id`, `name`, `email`, `password_hash`, `role`, `status`,
   `sms_balance`, `wallet_balance`, `sms_buying_price`, `sms_selling_price`,
   `theme`, `created_at`, `updated_at`)
VALUES (
  '00000000-0000-4000-8000-000000000001',
  'Admin',
  'admin@bulksms.com',
  '$2y$12$JxhrDwZktcE4QoLVKXarveCM3XKPPoDDVtKaaliGZvlUL1CpwlLSy',
  'admin',
  'active',
  999999, 999999,
  0.0100, 0.0500,
  'light',
  NOW(), NOW()
)
ON DUPLICATE KEY UPDATE `updated_at` = NOW();