CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150) UNIQUE,
    password VARCHAR(255),
    role ENUM('admin', 'editor', 'viewer') DEFAULT 'editor',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE social_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    platform ENUM('twitter', 'telegram'),
    name VARCHAR(150),
    external_id VARCHAR(255) NULL,
    api_key TEXT NULL,
    api_secret TEXT NULL,
    access_token TEXT NULL,
    access_token_secret TEXT NULL,
    bot_token TEXT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE twitter_sources (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    social_account_id INT UNSIGNED NOT NULL,
    source_type ENUM('user','list','hashtag') DEFAULT 'user',
    identifier VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    last_tweet_id VARCHAR(50) NULL,
    include_retweets TINYINT(1) DEFAULT 0,
    include_replies TINYINT(1) DEFAULT 0,
    status ENUM('active','paused') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE telegram_targets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    social_account_id INT UNSIGNED NOT NULL,
    target_type ENUM('channel','group','user') DEFAULT 'channel',
    chat_id VARCHAR(100) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    status ENUM('active','paused') DEFAULT 'active',
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE publishing_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    twitter_source_id INT UNSIGNED NOT NULL,
    telegram_target_id INT UNSIGNED NOT NULL,
    enabled TINYINT(1) DEFAULT 1,
    post_with_media TINYINT(1) DEFAULT 1,
    text_template TEXT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

CREATE TABLE posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    twitter_source_id INT UNSIGNED NOT NULL,
    tweet_id VARCHAR(50) NOT NULL,
    tweet_text TEXT,
    tweet_url VARCHAR(255),
    media_json JSON NULL,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    UNIQUE KEY uniq_tweet (twitter_source_id, tweet_id)
);

CREATE TABLE post_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    publishing_rule_id INT UNSIGNED NOT NULL,
    telegram_message_id VARCHAR(100) NULL,
    status ENUM('success','failed') DEFAULT 'success',
    error_message TEXT NULL,
    sent_text TEXT NULL,
    target_chat_id VARCHAR(150) NULL,
    created_at TIMESTAMP NULL
);
