-- ============================================
-- APPROVEBOT - Database Schema
-- ============================================

SET NAMES utf8mb4;
SET time_zone = '+06:00';

-- Users (self-registered regular users)
CREATE TABLE IF NOT EXISTS `users` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `telegram_id`     BIGINT NOT NULL UNIQUE,
  `username`        VARCHAR(100) DEFAULT NULL,
  `first_name`      VARCHAR(100) DEFAULT NULL,
  `is_premium`      TINYINT(1) DEFAULT 0,
  `broadcast_limit` INT DEFAULT 3,
  `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Channels (connected by regular users)
CREATE TABLE IF NOT EXISTS `channels` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`         INT NOT NULL,
  `channel_id`      BIGINT NOT NULL,
  `channel_title`   VARCHAR(200) DEFAULT NULL,
  `channel_username`VARCHAR(100) DEFAULT NULL,
  `log_channel_id`  BIGINT DEFAULT NULL,
  `welcome_message` TEXT DEFAULT NULL,
  `is_active`       TINYINT(1) DEFAULT 1,
  `created_at`      DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  UNIQUE KEY `unique_channel` (`channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Approved Members
CREATE TABLE IF NOT EXISTS `approved_members` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `channel_id`    BIGINT NOT NULL,
  `telegram_id`   BIGINT NOT NULL,
  `username`      VARCHAR(100) DEFAULT NULL,
  `first_name`    VARCHAR(100) DEFAULT NULL,
  `approved_at`   DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_member` (`channel_id`, `telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Broadcast History
CREATE TABLE IF NOT EXISTS `broadcasts` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `sent_by`       BIGINT NOT NULL,
  `channel_id`    BIGINT DEFAULT NULL COMMENT 'NULL = super admin all channels',
  `message`       TEXT NOT NULL,
  `total_sent`    INT DEFAULT 0,
  `total_failed`  INT DEFAULT 0,
  `created_at`    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Broadcast usage today tracking
CREATE TABLE IF NOT EXISTS `broadcast_usage` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT NOT NULL,
  `used_date`   DATE NOT NULL,
  `count`       INT DEFAULT 0,
  UNIQUE KEY `unique_usage` (`user_id`, `used_date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bot Logs
CREATE TABLE IF NOT EXISTS `logs` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `channel_id`  BIGINT DEFAULT NULL,
  `telegram_id` BIGINT DEFAULT NULL,
  `action`      VARCHAR(50) DEFAULT NULL,
  `detail`      TEXT DEFAULT NULL,
  `created_at`  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Daily Report tracking
CREATE TABLE IF NOT EXISTS `daily_stats` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `channel_id`  BIGINT NOT NULL,
  `stat_date`   DATE NOT NULL,
  `approved`    INT DEFAULT 0,
  UNIQUE KEY `unique_stat` (`channel_id`, `stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User conversation states (for bot inline menu flow)
CREATE TABLE IF NOT EXISTS `user_states` (
  `telegram_id` BIGINT NOT NULL PRIMARY KEY,
  `state`       VARCHAR(50) DEFAULT NULL,
  `data`        TEXT DEFAULT NULL,
  `updated_at`  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;