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

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


-- ============================================================
-- TABLE: users
-- ============================================================
CREATE TABLE `users` (
  `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username`         VARCHAR(50)  NOT NULL UNIQUE,
  `email`            VARCHAR(150) NOT NULL UNIQUE,
  `password`         VARCHAR(255) NOT NULL,
  `full_name`        VARCHAR(100) DEFAULT NULL,
  `avatar`           VARCHAR(255) DEFAULT NULL,
  `role`             ENUM('user','advertiser','admin') NOT NULL DEFAULT 'user',
  `referral_code`    VARCHAR(20)  NOT NULL UNIQUE,
  `referred_by`      INT UNSIGNED DEFAULT NULL,
  `wallet_balance`   DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_earned`     DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `total_withdrawn`  DECIMAL(12,4) NOT NULL DEFAULT 0.0000,
  `status`           ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
  `email_verified`   TINYINT(1) NOT NULL DEFAULT 0,
  `remember_token`   VARCHAR(100) DEFAULT NULL,
  `last_login`       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_referral_code` (`referral_code`),
  KEY `idx_referred_by` (`referred_by`),
  KEY `idx_role` (`role`),
  FOREIGN KEY (`referred_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: bots
-- ============================================================
CREATE TABLE `bots` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         INT UNSIGNED NOT NULL,
  `bot_token`       VARCHAR(255) NOT NULL UNIQUE,
  `bot_username`    VARCHAR(100) DEFAULT NULL,
  `bot_name`        VARCHAR(150) DEFAULT NULL,
  `bot_id`          BIGINT DEFAULT NULL,
  `status`          ENUM('active','inactive','invalid') NOT NULL DEFAULT 'inactive',
  `total_chats`     INT NOT NULL DEFAULT 0,
  `ads_delivered`   INT NOT NULL DEFAULT 0,
  `daily_limit`     INT NOT NULL DEFAULT 50,
  `daily_count`     INT NOT NULL DEFAULT 0,
  `last_reset`      DATE DEFAULT NULL,
  `verified_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_user_id` (`user_id`),
  KEY `idx_status` (`status`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

-- ============================================================
-- TABLE: settings
-- ============================================================
CREATE TABLE `settings` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key`          VARCHAR(100) NOT NULL UNIQUE,
  `value`        TEXT DEFAULT NULL,
  `description`  VARCHAR(255) DEFAULT NULL,
  `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- DEFAULT SETTINGS
-- ============================================================
INSERT INTO `settings` (`key`, `value`, `description`) VALUES
('platform_commission', '30', 'Platform commission percentage (%)'),
('min_withdrawal', '100', 'Minimum withdrawal amount (BDT)'),
('cpm_rate', '0.50', 'Default CPM rate in USD'),
('referral_commission', '10', 'Referral commission percentage'),
('ad_frequency_default', '1', 'Default ad frequency cap per chat per day'),
('platform_name', 'AdsNeo', 'Platform name'),
('platform_url', 'https://adsneo.click', 'Platform URL'),
('maintenance_mode', '0', 'Maintenance mode on/off');

-- ============================================================
-- DEFAULT ADMIN USER (password: Admin@123)
-- ============================================================
INSERT INTO `users` (`username`, `email`, `password`, `full_name`, `role`, `referral_code`, `status`, `email_verified`) VALUES
('admin', 'admin@adsneo.click', '$2y$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LeVg4d9jQ7sZEtFTe', 'AdsNeo Admin', 'admin', 'ADMIN001', 'active', 1);