-- ============================================================
-- AdsNeo - Telegram Ad Monetization Network
-- Database Schema
-- ============================================================


-- ============================================================
-- TABLE: chats (groups/channels connected to bots)
-- ============================================================
CREATE TABLE `chats` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bot_id`          INT UNSIGNED NOT NULL,
  `user_id`         INT UNSIGNED NOT NULL,
  `chat_id`         BIGINT NOT NULL,
  `chat_type`       ENUM('group','supergroup','channel') NOT NULL,
  `chat_title`      VARCHAR(255) DEFAULT NULL,
  `chat_username`   VARCHAR(100) DEFAULT NULL,
  `member_count`    INT DEFAULT NULL,
  `status`          ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `ads_delivered`   INT NOT NULL DEFAULT 0,
  `daily_count`     INT NOT NULL DEFAULT 0,
  `last_ad_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 `unique_bot_chat` (`bot_id`, `chat_id`),
  KEY `idx_bot_id` (`bot_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`bot_id`) REFERENCES `bots`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: ads (campaigns)
-- ============================================================
CREATE TABLE `ads` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         INT UNSIGNED NOT NULL,
  `title`           VARCHAR(200) NOT NULL,
  `caption`         TEXT NOT NULL,
  `image_path`      VARCHAR(255) DEFAULT NULL,
  `button_text`     VARCHAR(100) DEFAULT NULL,
  `button_url`      VARCHAR(500) DEFAULT NULL,
  `target_type`     ENUM('all','group','channel','bot') NOT NULL DEFAULT 'all',
  `budget`          DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `budget_spent`    DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `cpm_rate`        DECIMAL(8,4) NOT NULL DEFAULT 0.5000,
  `impressions`     INT NOT NULL DEFAULT 0,
  `clicks`          INT NOT NULL DEFAULT 0,
  `delivery_count`  INT NOT NULL DEFAULT 0,
  `status`          ENUM('pending','active','paused','completed','rejected') NOT NULL DEFAULT 'pending',
  `start_date`      DATETIME DEFAULT NULL,
  `end_date`        DATETIME DEFAULT NULL,
  `frequency_cap`   INT NOT NULL DEFAULT 1,
  `daily_limit`     INT NOT NULL DEFAULT 100,
  `daily_count`     INT NOT NULL DEFAULT 0,
  `last_reset`      DATE DEFAULT NULL,
  `admin_note`      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_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_start_date` (`start_date`),
  KEY `idx_end_date` (`end_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: ad_logs
-- ============================================================
CREATE TABLE `ad_logs` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ad_id`       INT UNSIGNED NOT NULL,
  `bot_id`      INT UNSIGNED NOT NULL,
  `chat_id`     BIGINT NOT NULL,
  `user_id`     INT UNSIGNED NOT NULL,
  `message_id`  BIGINT DEFAULT NULL,
  `status`      ENUM('sent','failed','clicked') NOT NULL DEFAULT 'sent',
  `earning`     DECIMAL(10,6) NOT NULL DEFAULT 0.000000,
  `error_msg`   TEXT DEFAULT NULL,
  `sent_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ad_id` (`ad_id`),
  KEY `idx_bot_id` (`bot_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_chat_id` (`chat_id`),
  KEY `idx_sent_at` (`sent_at`),
  FOREIGN KEY (`ad_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`bot_id`) REFERENCES `bots`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: ad_chat_log (deduplication tracking)
-- ============================================================
CREATE TABLE `ad_chat_log` (
  `id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ad_id`     INT UNSIGNED NOT NULL,
  `chat_id`   BIGINT NOT NULL,
  `count`     INT NOT NULL DEFAULT 1,
  `last_sent` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_ad_chat` (`ad_id`, `chat_id`),
  KEY `idx_ad_id` (`ad_id`),
  FOREIGN KEY (`ad_id`) REFERENCES `ads`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: transactions
-- ============================================================
CREATE TABLE `transactions` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`     INT UNSIGNED NOT NULL,
  `type`        ENUM('earning','referral','withdrawal','refund','deposit','commission') NOT NULL,
  `amount`      DECIMAL(12,4) NOT NULL,
  `balance`     DECIMAL(12,4) NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `reference`   VARCHAR(100) DEFAULT NULL,
  `status`      ENUM('pending','completed','failed') NOT NULL DEFAULT 'completed',
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_created_at` (`created_at`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: withdrawals
-- ============================================================
CREATE TABLE `withdrawals` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         INT UNSIGNED NOT NULL,
  `amount`          DECIMAL(12,4) NOT NULL,
  `method`          ENUM('bkash','nagad','bank') NOT NULL,
  `account_number`  VARCHAR(50) NOT NULL,
  `account_name`    VARCHAR(100) DEFAULT NULL,
  `bank_name`       VARCHAR(100) DEFAULT NULL,
  `status`          ENUM('pending','approved','rejected','processing') NOT NULL DEFAULT 'pending',
  `admin_note`      TEXT DEFAULT NULL,
  `processed_by`    INT UNSIGNED DEFAULT NULL,
  `processed_at`    DATETIME DEFAULT NULL,
  `transaction_ref` VARCHAR(100) 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_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: notifications
-- ============================================================
CREATE TABLE `notifications` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`    INT UNSIGNED NOT NULL,
  `title`      VARCHAR(200) NOT NULL,
  `message`    TEXT NOT NULL,
  `type`       ENUM('info','success','warning','error') NOT NULL DEFAULT 'info',
  `is_read`    TINYINT(1) NOT NULL DEFAULT 0,
  `link`       VARCHAR(255) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_is_read` (`is_read`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: rate_limits
-- ============================================================
CREATE TABLE `rate_limits` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip_address`  VARCHAR(45) NOT NULL,
  `endpoint`    VARCHAR(100) NOT NULL,
  `hit_count`   INT NOT NULL DEFAULT 1,
  `window_start` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ip_endpoint` (`ip_address`, `endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
