CREATE TABLE IF NOT EXISTS users (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    role VARCHAR(50) DEFAULT 'user',
    status VARCHAR(50) DEFAULT 'active',
    phone VARCHAR(50),
    country VARCHAR(100),
    onboarding_completed_at TIMESTAMP NULL,
    onboarding_data JSON,
    current_plan_id CHAR(36),
    current_subscription_id CHAR(36),
    email_verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS plans (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255),
    slug VARCHAR(100) UNIQUE,
    description TEXT,
    price_monthly DECIMAL(10, 2),
    price_yearly DECIMAL(10, 2),
    currency VARCHAR(10) DEFAULT 'USD',
    status VARCHAR(50) DEFAULT 'active',
    sort_order INT DEFAULT 0,
    is_default BOOLEAN DEFAULT FALSE,
    features JSON,
    limits JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS subscriptions (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36),
    plan_id CHAR(36),
    status VARCHAR(50) DEFAULT 'active',
    activated_at TIMESTAMP NULL,
    ends_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS personal_access_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tokenable_type VARCHAR(255),
    tokenable_id CHAR(36),
    name VARCHAR(255),
    token VARCHAR(64) UNIQUE,
    abilities TEXT,
    last_used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS content_queue (
    id CHAR(36) PRIMARY KEY,
    topic TEXT,
    language VARCHAR(100),
    style VARCHAR(100),
    mode VARCHAR(50),
    status VARCHAR(50),
    youtube_format VARCHAR(50),
    duration_preset VARCHAR(100),
    upload_to_youtube BOOLEAN,
    youtube_account_id VARCHAR(255),
    channel_id CHAR(36),
    idea_id CHAR(36),
    batch_id CHAR(36),
    template_id VARCHAR(100),
    music_id VARCHAR(100),
    watermark BOOLEAN,
    intro_card BOOLEAN,
    ken_burns BOOLEAN,
    silence_trim BOOLEAN,
    avoid_repeat_clips BOOLEAN,
    preferred_hook TEXT,
    custom_text TEXT,
    series JSON,
    upload_meta JSON,
    seo JSON,
    news_headlines JSON,
    clips JSON,
    clip_paths JSON,
    audio_path TEXT,
    voice_path TEXT,
    background_image_path TEXT,
    audio_dur DECIMAL(10, 2),
    qa_attempts INT DEFAULT 0,
    pinned_comment_draft TEXT,
    error TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS generated_scripts (
    id CHAR(36) PRIMARY KEY,
    queue_id CHAR(36),
    title TEXT,
    hook TEXT,
    script_lines JSON,
    visual_queries JSON,
    seo_keywords JSON,
    pinned_comment TEXT,
    raw_json JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (queue_id) REFERENCES content_queue(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS generated_videos (
    id CHAR(36) PRIMARY KEY,
    queue_id CHAR(36),
    script_id CHAR(36),
    video_path TEXT,
    audio_path TEXT,
    thumbnail_path TEXT,
    sentence_timings JSON,
    duration_sec DECIMAL(10, 2),
    width INT,
    height INT,
    status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (queue_id) REFERENCES content_queue(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS upload_history (
    id CHAR(36) PRIMARY KEY,
    queue_id CHAR(36),
    youtube_account_id VARCHAR(255),
    youtube_video_id VARCHAR(255),
    url TEXT,
    title TEXT,
    format VARCHAR(50),
    privacy_status VARCHAR(50),
    publish_at TIMESTAMP NULL,
    status VARCHAR(50),
    response JSON,
    error TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (queue_id) REFERENCES content_queue(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS payment_methods (
    id CHAR(36) PRIMARY KEY,
    name VARCHAR(255),
    slug VARCHAR(100) UNIQUE,
    type VARCHAR(50),
    account_title VARCHAR(255),
    account_number VARCHAR(255),
    phone_number VARCHAR(255),
    bank_name VARCHAR(255),
    iban VARCHAR(255),
    wallet_address VARCHAR(255),
    network VARCHAR(100),
    instructions TEXT,
    status VARCHAR(50) DEFAULT 'active',
    sort_order INT DEFAULT 0,
    provider VARCHAR(50) DEFAULT 'manual',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channels (
    id CHAR(36) PRIMARY KEY,
    user_id CHAR(36),
    name VARCHAR(255),
    slug VARCHAR(255),
    language VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channel_interests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id CHAR(36),
    type VARCHAR(50),
    value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS channel_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel_id CHAR(36) UNIQUE,
    studio_defaults JSON,
    auto JSON,
    performance_signal JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS idea_bank (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    title TEXT,
    description TEXT,
    category VARCHAR(100),
    content_pillar VARCHAR(100),
    priority INT DEFAULT 3,
    status VARCHAR(50) DEFAULT 'inbox',
    source VARCHAR(50),
    trend_score INT,
    opportunity_score INT,
    revenue_score INT,
    channel_match_score INT,
    notes TEXT,
    suggested_hook TEXT,
    suggested_cta TEXT,
    tags JSON,
    preferred_publish_date DATE,
    opportunity_id CHAR(36),
    queue_id CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_manager_conversations (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    user_id CHAR(36),
    title VARCHAR(255),
    last_message_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_manager_messages (
    id CHAR(36) PRIMARY KEY,
    conversation_id CHAR(36),
    channel_id CHAR(36),
    role VARCHAR(50),
    content TEXT,
    context_used JSON,
    meta JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES ai_manager_conversations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ai_manager_actions (
    id CHAR(36) PRIMARY KEY,
    conversation_id CHAR(36),
    message_id CHAR(36),
    channel_id CHAR(36),
    user_id CHAR(36),
    action VARCHAR(100),
    parameters JSON,
    status VARCHAR(50) DEFAULT 'pending',
    result TEXT,
    error TEXT,
    affected_queue_id CHAR(36),
    affected_idea_id CHAR(36),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES ai_manager_conversations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS content_performance (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    queue_id CHAR(36),
    title TEXT,
    topic TEXT,
    views INT DEFAULT 0,
    likes INT DEFAULT 0,
    outcome VARCHAR(50),
    hook TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS content_opportunities (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    topic TEXT,
    explanation TEXT,
    suggested_angle TEXT,
    competition_level VARCHAR(50),
    content_pillar VARCHAR(100),
    opportunity_score INT,
    trend_score INT,
    audience_match INT,
    suggested_cta TEXT,
    keywords JSON,
    status VARCHAR(50) DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS channel_competitors (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    name VARCHAR(255),
    niche VARCHAR(255),
    subscriber_count INT,
    average_views INT,
    common_topics JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS channel_health_scores (
    id CHAR(36) PRIMARY KEY,
    channel_id CHAR(36),
    overall INT,
    content_consistency INT,
    topic_strategy INT,
    hook_quality INT,
    seo INT,
    growth INT,
    scored_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (channel_id) REFERENCES channels(id) ON DELETE CASCADE
);