-- Website New Post Monitor Database Schema


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

-- 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 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}');
