-- Website New Post Monitor Database Schema

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS check_logs;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS websites;
DROP TABLE IF EXISTS telegram_settings;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS admins;
SET FOREIGN_KEY_CHECKS = 1;

-- Admins table
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Websites table
CREATE TABLE websites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    url VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    check_interval INT DEFAULT 1, -- in minutes
    selector_container VARCHAR(255) DEFAULT NULL,
    selector_title VARCHAR(255) DEFAULT NULL,
    selector_url VARCHAR(255) DEFAULT NULL,
    selector_image VARCHAR(255) DEFAULT NULL,
    last_check_at DATETIME DEFAULT NULL,
    last_detected_post VARCHAR(255) DEFAULT NULL,
    failed_checks INT DEFAULT 0,
    last_error TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Posts table
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    website_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    post_url TEXT NOT NULL,
    image_url TEXT DEFAULT NULL,
    content_hash VARCHAR(64) NOT NULL,
    detected_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    notification_sent TINYINT(1) DEFAULT 0,
    notification_sent_at DATETIME DEFAULT NULL,
    FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Telegram Settings table
CREATE TABLE telegram_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bot_token VARCHAR(255) DEFAULT '',
    chat_id VARCHAR(100) DEFAULT '',
    enabled TINYINT(1) DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings table (for message template and general settings)
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT DEFAULT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Check Logs table
CREATE TABLE check_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    website_id INT DEFAULT NULL,
    website_name VARCHAR(150) DEFAULT NULL,
    check_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    posts_found INT DEFAULT 0,
    new_posts_found INT DEFAULT 0,
    telegram_status VARCHAR(50) DEFAULT 'N/A',
    error_message TEXT DEFAULT NULL,
    FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default Admin (username: admin, password: password123)
INSERT INTO admins (username, password) VALUES ('admin', '$2y$10$3Byv/B987EzTVcZuwOw2SOAwlEQvFMrmHullfXlWVv4uM4PAL6WT6');

-- Default Telegram Settings
INSERT INTO telegram_settings (bot_token, chat_id, enabled) VALUES ('', '', 0);

-- Default Message Template
INSERT INTO settings (setting_key, setting_value) VALUES ('telegram_message_template', '🔥 <b>New Post Detected!</b>

📌 <b>{title}</b>

🌐 {site_name}

🔗 {url}

⏰ {date}');