-- ============================================
-- DATABASE: telegram_id_bot
-- @author Muhamad Irfan (Fullstack Developer)
-- ============================================

CREATE DATABASE IF NOT EXISTS telegram_id_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE telegram_id_bot;

-- Table nyimpen semua user yang pernah /start bot
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(100) DEFAULT NULL,
    first_name VARCHAR(150) DEFAULT NULL,
    last_name VARCHAR(150) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Table log broadcast (biar ada history pesan yang udah dikirim)
CREATE TABLE IF NOT EXISTS broadcast_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    total_sent INT DEFAULT 0,
    total_failed INT DEFAULT 0,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;