-- ============================================================
--  NexSMS Bulk SMS Platform — MySQL Schema
--  UUID v4 primary keys (CHAR(36)) on all tables
--  Compatible with MySQL 8.0+ and MariaDB 10.5+
-- ============================================================

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') 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,
  `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') NOT NULL DEFAULT 'processing',
  `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 COMMENT 'NULL = send immediately; future datetime = scheduled delivery',
  `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,
  `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;

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

SET FOREIGN_KEY_CHECKS = 1;