SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    nickname VARCHAR(120) NULL,
    role ENUM('guest', 'user', 'creator', 'moderator', 'admin') NOT NULL DEFAULT 'user',
    status ENUM('active', 'pending', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
    language CHAR(2) NOT NULL DEFAULT 'tr',
    email_verified_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    display_name VARCHAR(120) NOT NULL,
    nickname VARCHAR(80) NOT NULL UNIQUE,
    bio TEXT NULL,
    avatar_path VARCHAR(255) NULL,
    cover_path VARCHAR(255) NULL,
    country VARCHAR(80) NULL,
    website_url VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    display_name VARCHAR(120) NOT NULL,
    about TEXT NULL,
    avatar VARCHAR(255) NULL,
    cover_image VARCHAR(255) NULL,
    instagram VARCHAR(190) NULL,
    youtube VARCHAR(190) NULL,
    website VARCHAR(255) NULL,
    privacy ENUM('public', 'members', 'private') NOT NULL DEFAULT 'public',
    message_privacy ENUM('everyone', 'members', 'friends', 'none') NOT NULL DEFAULT 'members',
    billing_first_name VARCHAR(120) NULL,
    billing_last_name VARCHAR(120) NULL,
    billing_email VARCHAR(190) NULL,
    billing_phone VARCHAR(40) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS password_resets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    email VARCHAR(190) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at TIMESTAMP NULL,
    used_at TIMESTAMP NULL,
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_password_resets_token_hash (token_hash),
    INDEX idx_password_resets_user_used (user_id, used_at),
    CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id BIGINT UNSIGNED NULL,
    type ENUM('mod', 'music') NOT NULL DEFAULT 'mod',
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    description TEXT NULL,
    icon VARCHAR(255) NULL,
    banner VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mods (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    creator_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    summary VARCHAR(255) NULL,
    description MEDIUMTEXT NULL,
    tags VARCHAR(500) NULL,
    external_link VARCHAR(500) NULL,
    external_link_status ENUM('unchecked', 'safe', 'suspicious', 'blocked', 'unavailable') NOT NULL DEFAULT 'unchecked',
    external_link_risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    cover_path VARCHAR(255) NULL,
    cover_image VARCHAR(255) NULL,
    privacy ENUM('public', 'free', 'members', 'private', 'premium', 'subscriber') NOT NULL DEFAULT 'public',
    package_id BIGINT UNSIGNED NULL,
    file_url VARCHAR(500) NULL,
    version VARCHAR(40) NULL,
    game_version VARCHAR(80) NULL,
    access_type ENUM('free', 'subscriber', 'paid') NOT NULL DEFAULT 'free',
    status ENUM('draft', 'pending', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'draft',
    download_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    like_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    dislike_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    featured TINYINT(1) NOT NULL DEFAULT 0,
    rating DECIMAL(3,1) NOT NULL DEFAULT 0.0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_mods_creator_status (creator_id, status),
    INDEX idx_mods_category_status (category_id, status),
    INDEX idx_mods_home (status, featured, rating, created_at),
    CONSTRAINT fk_mods_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_mods_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_mods_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mod_images (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_mod_images_mod_path (mod_id, image_path),
    CONSTRAINT fk_mod_images_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mod_videos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    video_url VARCHAR(500) NOT NULL,
    provider VARCHAR(80) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_mod_videos_mod_url (mod_id, video_url),
    CONSTRAINT fk_mod_videos_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mod_views (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mod_views_mod_created (mod_id, created_at),
    CONSTRAINT fk_mod_views_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE,
    CONSTRAINT fk_mod_views_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mod_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction ENUM('like', 'dislike') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_mod_reactions_user (mod_id, user_id),
    CONSTRAINT fk_mod_reactions_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE,
    CONSTRAINT fk_mod_reactions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mod_download_clicks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(64) NULL,
    external_url VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mod_downloads_mod_created (mod_id, created_at),
    CONSTRAINT fk_mod_download_clicks_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE,
    CONSTRAINT fk_mod_download_clicks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    artist_name VARCHAR(160) NOT NULL,
    description MEDIUMTEXT NULL,
    cover_path VARCHAR(255) NULL,
    file_path VARCHAR(255) NULL,
    duration_seconds INT UNSIGNED NULL,
    access_type ENUM('free', 'subscriber', 'paid') NOT NULL DEFAULT 'free',
    status ENUM('draft', 'pending', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'draft',
    play_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_music_creator_status (creator_id, status),
    CONSTRAINT fk_music_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_music_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS packages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(160) NOT NULL UNIQUE,
    description TEXT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    billing_period ENUM('monthly', 'yearly', 'one_time') NOT NULL DEFAULT 'monthly',
    benefits LONGTEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_packages_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    creator_id BIGINT UNSIGNED NOT NULL,
    package_id BIGINT UNSIGNED NOT NULL,
    status ENUM('active', 'past_due', 'cancelled', 'expired') NOT NULL DEFAULT 'active',
    starts_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at TIMESTAMP NULL,
    renews_at TIMESTAMP NULL,
    cancelled_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_subscriptions_user_status (user_id, status),
    CONSTRAINT fk_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscriptions_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_subscriptions_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(80) NOT NULL UNIQUE,
    user_id BIGINT UNSIGNED NOT NULL,
    creator_id BIGINT UNSIGNED NULL,
    package_id BIGINT UNSIGNED NULL,
    subscription_id BIGINT UNSIGNED NULL,
    type ENUM('subscription', 'mod_purchase', 'music_purchase', 'donation') NOT NULL DEFAULT 'subscription',
    status ENUM('pending', 'paid', 'failed', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending',
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    platform_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    creator_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_orders_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_orders_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL,
    CONSTRAINT fk_orders_subscription FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(80) NOT NULL,
    provider_payment_id VARCHAR(190) NULL,
    status ENUM('pending', 'authorized', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
    amount DECIMAL(12,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    request_payload LONGTEXT NULL,
    response_payload LONGTEXT NULL,
    paid_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_payments_provider_status (provider, status),
    CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS creator_balances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT UNSIGNED NOT NULL UNIQUE,
    pending_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    available_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    withdrawn_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_creator_balances_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS creator_ledgers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    type ENUM('earning', 'fee', 'release', 'withdrawal', 'refund') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    description VARCHAR(255) NULL,
    available_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_creator_ledgers_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_creator_ledgers_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    label VARCHAR(120) NOT NULL,
    account_holder VARCHAR(160) NOT NULL,
    iban VARCHAR(34) NOT NULL,
    bank_name VARCHAR(120) NULL,
    is_site_account TINYINT(1) NOT NULL DEFAULT 0,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_bank_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS withdrawal_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_id BIGINT UNSIGNED NOT NULL,
    bank_account_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    status ENUM('pending', 'approved', 'paid', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending',
    admin_note TEXT NULL,
    requested_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    CONSTRAINT fk_withdrawals_creator FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_withdrawals_bank FOREIGN KEY (bank_account_id) REFERENCES bank_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    type VARCHAR(80) NOT NULL,
    title VARCHAR(190) NOT NULL,
    body TEXT NULL,
    data LONGTEXT NULL,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(120) NOT NULL UNIQUE,
    setting_value LONGTEXT NULL,
    type VARCHAR(40) NOT NULL DEFAULT 'string',
    is_public TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lang CHAR(2) NOT NULL,
    translation_key VARCHAR(190) NOT NULL,
    translation_value TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_translations_lang_key (lang, translation_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    key_name VARCHAR(190) NOT NULL UNIQUE,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_rate_limits_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    target_type VARCHAR(80) NULL,
    target_id BIGINT UNSIGNED NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    details LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_admin_logs_admin FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS friendships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    requester_id BIGINT UNSIGNED NOT NULL,
    receiver_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending', 'accepted', 'rejected', 'removed') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_friendship_pair (requester_id, receiver_id),
    CONSTRAINT fk_friendships_requester FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_friendships_receiver FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS blocked_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocker_id BIGINT UNSIGNED NOT NULL,
    blocked_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_blocked_users_pair (blocker_id, blocked_id),
    CONSTRAINT fk_blocked_users_blocker FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_blocked_users_blocked FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type ENUM('private', 'group') NOT NULL DEFAULT 'private',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    last_read_at TIMESTAMP NULL,
    archived_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_conversation_participant (conversation_id, user_id),
    INDEX idx_conversation_participants_user (user_id, archived_at),
    CONSTRAINT fk_conversation_participants_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_conversation_participants_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender_id BIGINT UNSIGNED NOT NULL,
    message_type ENUM('text', 'emoji', 'gif', 'image', 'link') NOT NULL DEFAULT 'text',
    body TEXT NULL,
    attachment_path VARCHAR(255) NULL,
    gif_url VARCHAR(500) NULL,
    link_url VARCHAR(500) NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('sent', 'pending_review', 'blocked', 'deleted') NOT NULL DEFAULT 'sent',
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_messages_conversation_created (conversation_id, created_at),
    CONSTRAINT fk_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_messages_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    reporter_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NOT NULL,
    status ENUM('open', 'reviewed', 'dismissed', 'actioned') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_message_reports_status (status, created_at),
    CONSTRAINT fk_message_reports_message FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_message_reports_reporter FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content_type ENUM('mod', 'music', 'community') NOT NULL DEFAULT 'mod',
    content_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
    user_id BIGINT UNSIGNED NOT NULL,
    commentable_type ENUM('mod', 'music') NULL,
    commentable_id BIGINT UNSIGNED NULL,
    parent_id BIGINT UNSIGNED NULL,
    content TEXT NOT NULL,
    body TEXT NULL,
    status ENUM('published', 'pending_review', 'blocked', 'visible', 'hidden', 'pending', 'deleted') NOT NULL DEFAULT 'published',
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_comments_content (content_type, content_id, status),
    INDEX idx_comments_target (commentable_type, commentable_id, status),
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS comment_likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_comment_likes_user (comment_id, user_id),
    CONSTRAINT fk_comment_likes_comment FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_likes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(190) NOT NULL,
    content MEDIUMTEXT NOT NULL,
    language CHAR(2) NOT NULL DEFAULT 'tr',
    status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft',
    show_in_header TINYINT(1) NOT NULL DEFAULT 0,
    show_in_footer TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    seo_title VARCHAR(190) NULL,
    seo_description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_pages_slug_lang (slug, language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookie_consents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    visitor_id VARCHAR(120) NULL,
    consent_token VARCHAR(120) NOT NULL UNIQUE,
    necessary TINYINT(1) NOT NULL DEFAULT 1,
    analytics TINYINT(1) NOT NULL DEFAULT 0,
    personalization TINYINT(1) NOT NULL DEFAULT 0,
    marketing TINYINT(1) NOT NULL DEFAULT 0,
    preferences TINYINT(1) NOT NULL DEFAULT 0,
    consent_status ENUM('accepted', 'rejected', 'necessary') NOT NULL DEFAULT 'necessary',
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_cookie_consents_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS security_scan_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    scan_type ENUM('link', 'text', 'file') NOT NULL,
    subject VARCHAR(500) NULL,
    result_status ENUM('safe', 'suspicious', 'blocked', 'needs_review') NOT NULL,
    result_reason VARCHAR(190) NULL,
    result_payload LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_security_scan_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bolum 2 idempotent schema tamamlamalari.
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS nickname VARCHAR(120) NULL AFTER password_hash;

ALTER TABLE users
    MODIFY COLUMN role ENUM('guest', 'user', 'creator', 'moderator', 'admin') NOT NULL DEFAULT 'user';

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS email_verified_at TIMESTAMP NULL AFTER status;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS language CHAR(2) NOT NULL DEFAULT 'tr' AFTER email_verified_at;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER is_public;

ALTER TABLE admin_logs
    ADD COLUMN IF NOT EXISTS target_type VARCHAR(80) NULL AFTER action,
    ADD COLUMN IF NOT EXISTS target_id BIGINT UNSIGNED NULL AFTER target_type;

ALTER TABLE cookie_consents
    ADD COLUMN IF NOT EXISTS visitor_id VARCHAR(120) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS preferences TINYINT(1) NOT NULL DEFAULT 0 AFTER marketing,
    ADD COLUMN IF NOT EXISTS consent_status ENUM('accepted', 'rejected', 'necessary') NOT NULL DEFAULT 'necessary' AFTER preferences,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

-- Bolum 3 idempotent ana site, kategori ve mod schema tamamlamalari.
ALTER TABLE categories
    ADD COLUMN IF NOT EXISTS description TEXT NULL AFTER slug,
    ADD COLUMN IF NOT EXISTS icon VARCHAR(255) NULL AFTER description,
    ADD COLUMN IF NOT EXISTS banner VARCHAR(255) NULL AFTER icon;

ALTER TABLE mods
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS external_link VARCHAR(500) NULL AFTER description,
    ADD COLUMN IF NOT EXISTS external_link_status ENUM('unchecked', 'safe', 'suspicious', 'blocked', 'unavailable') NOT NULL DEFAULT 'unchecked' AFTER external_link,
    ADD COLUMN IF NOT EXISTS external_link_risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER external_link_status,
    ADD COLUMN IF NOT EXISTS cover_image VARCHAR(255) NULL AFTER cover_path,
    ADD COLUMN IF NOT EXISTS privacy ENUM('public', 'free', 'members', 'private', 'premium', 'subscriber') NOT NULL DEFAULT 'public' AFTER cover_image,
    ADD COLUMN IF NOT EXISTS package_id BIGINT UNSIGNED NULL AFTER privacy,
    ADD COLUMN IF NOT EXISTS like_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER view_count,
    ADD COLUMN IF NOT EXISTS dislike_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER like_count,
    ADD COLUMN IF NOT EXISTS featured TINYINT(1) NOT NULL DEFAULT 0 AFTER dislike_count,
    ADD COLUMN IF NOT EXISTS rating DECIMAL(3,1) NOT NULL DEFAULT 0.0 AFTER featured;

ALTER TABLE mods
    MODIFY COLUMN privacy ENUM('public', 'free', 'members', 'private', 'premium', 'subscriber') NOT NULL DEFAULT 'public';

-- Bolum 5 mod detay, galeri, yorum ve harici indirme schema tamamlamalari.
ALTER TABLE mod_images
    ADD COLUMN IF NOT EXISTS mod_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS image_path VARCHAR(255) NOT NULL AFTER mod_id,
    ADD COLUMN IF NOT EXISTS sort_order INT NOT NULL DEFAULT 0 AFTER image_path,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER sort_order;

ALTER TABLE mod_videos
    ADD COLUMN IF NOT EXISTS mod_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS video_url VARCHAR(500) NOT NULL AFTER mod_id,
    ADD COLUMN IF NOT EXISTS provider VARCHAR(80) NULL AFTER video_url,
    ADD COLUMN IF NOT EXISTS sort_order INT NOT NULL DEFAULT 0 AFTER provider,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER sort_order;

ALTER TABLE mod_views
    ADD COLUMN IF NOT EXISTS mod_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER mod_id,
    ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER ip_address;

ALTER TABLE mod_reactions
    ADD COLUMN IF NOT EXISTS mod_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NOT NULL AFTER mod_id,
    ADD COLUMN IF NOT EXISTS reaction ENUM('like', 'dislike') NOT NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER reaction;

ALTER TABLE mod_download_clicks
    ADD COLUMN IF NOT EXISTS mod_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER mod_id,
    ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS external_url VARCHAR(500) NULL AFTER ip_address,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER external_url;

ALTER TABLE comments
    ADD COLUMN IF NOT EXISTS content_type ENUM('mod', 'music') NOT NULL DEFAULT 'mod' AFTER id,
    ADD COLUMN IF NOT EXISTS content_id BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER content_type,
    ADD COLUMN IF NOT EXISTS parent_id BIGINT UNSIGNED NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS content TEXT NULL AFTER parent_id,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE comments
    MODIFY COLUMN status ENUM('published', 'pending_review', 'blocked', 'visible', 'hidden', 'pending', 'deleted') NOT NULL DEFAULT 'published';

UPDATE comments
SET content_type = commentable_type
WHERE commentable_type IS NOT NULL;

UPDATE comments
SET content_id = commentable_id
WHERE commentable_id IS NOT NULL AND content_id = 0;

UPDATE comments
SET content = body
WHERE body IS NOT NULL AND (content IS NULL OR content = '');

CREATE TABLE IF NOT EXISTS comment_likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comment_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_comment_likes_user (comment_id, user_id),
    CONSTRAINT fk_comment_likes_comment FOREIGN KEY (comment_id) REFERENCES comments(id) ON DELETE CASCADE,
    CONSTRAINT fk_comment_likes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE mod_images
    ADD UNIQUE KEY IF NOT EXISTS uniq_mod_images_mod_path (mod_id, image_path);

ALTER TABLE mod_videos
    ADD UNIQUE KEY IF NOT EXISTS uniq_mod_videos_mod_url (mod_id, video_url);

ALTER TABLE mod_reactions
    ADD UNIQUE KEY IF NOT EXISTS uniq_mod_reactions_user (mod_id, user_id);

ALTER TABLE comment_likes
    ADD UNIQUE KEY IF NOT EXISTS uniq_comment_likes_user (comment_id, user_id);



-- Bolum 20: Topluluk gonderileri, anketler, reaksiyonlar, yorumlar ve story/durum sistemi.
CREATE TABLE IF NOT EXISTS community_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    post_type ENUM('text', 'image', 'poll') NOT NULL DEFAULT 'text',
    content TEXT NULL,
    image_path VARCHAR(255) NULL,
    poll_question VARCHAR(255) NULL,
    status ENUM('published', 'pending_review', 'blocked', 'deleted') NOT NULL DEFAULT 'published',
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_community_posts_user_status (user_id, status, created_at),
    INDEX idx_community_posts_status_created (status, created_at),
    CONSTRAINT fk_community_posts_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_poll_options (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    option_text VARCHAR(180) NOT NULL,
    vote_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_community_poll_options_post_sort (post_id, sort_order),
    CONSTRAINT fk_community_poll_options_post_b20 FOREIGN KEY (post_id) REFERENCES community_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_poll_votes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    option_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_community_poll_votes_user (post_id, user_id),
    INDEX idx_community_poll_votes_option (option_id),
    CONSTRAINT fk_community_poll_votes_post_b20 FOREIGN KEY (post_id) REFERENCES community_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_community_poll_votes_option_b20 FOREIGN KEY (option_id) REFERENCES community_poll_options(id) ON DELETE CASCADE,
    CONSTRAINT fk_community_poll_votes_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction ENUM('like', 'dislike') NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_community_reactions_user (post_id, user_id),
    INDEX idx_community_reactions_post_reaction (post_id, reaction),
    CONSTRAINT fk_community_reactions_post_b20 FOREIGN KEY (post_id) REFERENCES community_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_community_reactions_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    parent_id BIGINT UNSIGNED NULL,
    content TEXT NOT NULL,
    status ENUM('published', 'pending_review', 'blocked', 'visible', 'deleted') NOT NULL DEFAULT 'published',
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_community_comments_post_status (post_id, status, created_at),
    INDEX idx_community_comments_parent (parent_id),
    CONSTRAINT fk_community_comments_post_b20 FOREIGN KEY (post_id) REFERENCES community_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_community_comments_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_community_comments_parent_b20 FOREIGN KEY (parent_id) REFERENCES community_comments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    story_type ENUM('text', 'image') NOT NULL DEFAULT 'text',
    content TEXT NULL,
    media_path VARCHAR(255) NULL,
    link_url VARCHAR(500) NULL,
    status ENUM('active', 'expired', 'deleted') NOT NULL DEFAULT 'active',
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_stories_user_status (user_id, status, expires_at),
    INDEX idx_stories_status_expires (status, expires_at, created_at),
    CONSTRAINT fk_stories_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS story_views (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    story_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    viewed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_story_views_user (story_id, user_id),
    INDEX idx_story_views_user (user_id, viewed_at),
    CONSTRAINT fk_story_views_story_b20 FOREIGN KEY (story_id) REFERENCES stories(id) ON DELETE CASCADE,
    CONSTRAINT fk_story_views_user_b20 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE community_posts
    ADD COLUMN IF NOT EXISTS post_type ENUM('text', 'image', 'poll') NOT NULL DEFAULT 'text' AFTER user_id,
    ADD COLUMN IF NOT EXISTS content TEXT NULL AFTER post_type,
    ADD COLUMN IF NOT EXISTS image_path VARCHAR(255) NULL AFTER content,
    ADD COLUMN IF NOT EXISTS poll_question VARCHAR(255) NULL AFTER image_path,
    ADD COLUMN IF NOT EXISTS status ENUM('published', 'pending_review', 'blocked', 'deleted') NOT NULL DEFAULT 'published' AFTER poll_question,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL AFTER updated_at,
    ADD INDEX IF NOT EXISTS idx_community_posts_user_status (user_id, status, created_at),
    ADD INDEX IF NOT EXISTS idx_community_posts_status_created (status, created_at);

ALTER TABLE community_poll_options
    ADD INDEX IF NOT EXISTS idx_community_poll_options_post_sort (post_id, sort_order);

ALTER TABLE community_poll_votes
    ADD UNIQUE KEY IF NOT EXISTS uniq_community_poll_votes_user (post_id, user_id),
    ADD INDEX IF NOT EXISTS idx_community_poll_votes_option (option_id);

ALTER TABLE community_reactions
    ADD UNIQUE KEY IF NOT EXISTS uniq_community_reactions_user (post_id, user_id),
    ADD INDEX IF NOT EXISTS idx_community_reactions_post_reaction (post_id, reaction);

ALTER TABLE community_comments
    ADD COLUMN IF NOT EXISTS parent_id BIGINT UNSIGNED NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL AFTER updated_at,
    ADD INDEX IF NOT EXISTS idx_community_comments_post_status (post_id, status, created_at),
    ADD INDEX IF NOT EXISTS idx_community_comments_parent (parent_id);

ALTER TABLE stories
    ADD COLUMN IF NOT EXISTS story_type ENUM('text', 'image') NOT NULL DEFAULT 'text' AFTER user_id,
    ADD COLUMN IF NOT EXISTS content TEXT NULL AFTER story_type,
    ADD COLUMN IF NOT EXISTS media_path VARCHAR(255) NULL AFTER content,
    ADD COLUMN IF NOT EXISTS link_url VARCHAR(500) NULL AFTER media_path,
    ADD COLUMN IF NOT EXISTS status ENUM('active', 'expired', 'deleted') NOT NULL DEFAULT 'active' AFTER link_url,
    ADD COLUMN IF NOT EXISTS expires_at TIMESTAMP NULL AFTER status,
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL AFTER created_at,
    ADD INDEX IF NOT EXISTS idx_stories_user_status (user_id, status, expires_at),
    ADD INDEX IF NOT EXISTS idx_stories_status_expires (status, expires_at, created_at);

ALTER TABLE story_views
    ADD UNIQUE KEY IF NOT EXISTS uniq_story_views_user (story_id, user_id),
    ADD INDEX IF NOT EXISTS idx_story_views_user (user_id, viewed_at);

ALTER TABLE comments
    MODIFY COLUMN content_type ENUM('mod', 'music', 'community', 'community_post') NOT NULL DEFAULT 'mod',
    MODIFY COLUMN commentable_type ENUM('mod', 'music', 'community', 'community_post') NULL;

-- Bolum 18: ZPanel merkezi yonetim paneli, medya/logo ve settings tamamlamalari.
ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS group_name VARCHAR(80) NULL AFTER type,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD INDEX IF NOT EXISTS idx_settings_group (group_name, setting_key);

CREATE TABLE IF NOT EXISTS media_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(190) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
    file_type VARCHAR(60) NOT NULL DEFAULT 'image',
    context VARCHAR(80) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_media_files_context (context, created_at),
    INDEX idx_media_files_active (is_active, created_at),
    CONSTRAINT fk_media_files_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE media_files
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS file_path VARCHAR(500) NOT NULL DEFAULT '' AFTER user_id,
    ADD COLUMN IF NOT EXISTS file_name VARCHAR(190) NOT NULL DEFAULT '' AFTER file_path,
    ADD COLUMN IF NOT EXISTS mime_type VARCHAR(120) NOT NULL DEFAULT '' AFTER file_name,
    ADD COLUMN IF NOT EXISTS file_size BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER mime_type,
    ADD COLUMN IF NOT EXISTS file_type VARCHAR(60) NOT NULL DEFAULT 'image' AFTER file_size,
    ADD COLUMN IF NOT EXISTS context VARCHAR(80) NULL AFTER file_type,
    ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER context,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER is_active,
    ADD INDEX IF NOT EXISTS idx_media_files_context (context, created_at),
    ADD INDEX IF NOT EXISTS idx_media_files_active (is_active, created_at);

-- Bolum 17: UX/fonksiyon toparlama, Artist Connect, telif review ve icerik cevirileri.
ALTER TABLE users
    ADD COLUMN IF NOT EXISTS deletion_requested_at TIMESTAMP NULL AFTER last_login_at,
    ADD COLUMN IF NOT EXISTS disabled_at TIMESTAMP NULL AFTER deletion_requested_at;

CREATE TABLE IF NOT EXISTS artists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    artist_name VARCHAR(160) NOT NULL,
    slug VARCHAR(180) NOT NULL UNIQUE,
    bio TEXT NULL,
    avatar VARCHAR(255) NULL,
    cover_image VARCHAR(255) NULL,
    verified TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    rejection_reason TEXT NULL,
    spotify_url VARCHAR(500) NULL,
    youtube_url VARCHAR(500) NULL,
    apple_music_url VARCHAR(500) NULL,
    instagram_url VARCHAR(500) NULL,
    rights_confirmed_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_artists_user_status_seed (user_id, status),
    INDEX idx_artists_status_seed (status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_tracks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    artist_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    artist_name VARCHAR(160) NOT NULL,
    description MEDIUMTEXT NULL,
    cover_image VARCHAR(255) NULL,
    audio_file VARCHAR(255) NULL,
    external_link VARCHAR(500) NULL,
    youtube_url VARCHAR(500) NULL,
    spotify_url VARCHAR(500) NULL,
    apple_music_url VARCHAR(500) NULL,
    privacy ENUM('public', 'free', 'members', 'private', 'premium', 'subscriber') NOT NULL DEFAULT 'public',
    package_id BIGINT UNSIGNED NULL,
    status ENUM('draft', 'pending', 'pending_review', 'published', 'rejected', 'blocked', 'archived') NOT NULL DEFAULT 'published',
    play_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    like_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    featured TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_music_tracks_status_seed (status, featured, created_at),
    INDEX idx_music_tracks_artist_status_seed (artist_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_copyright_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    file_hash CHAR(64) NULL,
    title VARCHAR(180) NULL,
    artist_name VARCHAR(160) NULL,
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    reasons LONGTEXT NULL,
    provider VARCHAR(80) NOT NULL DEFAULT 'local_hash_keyword',
    status ENUM('clean', 'review', 'blocked', 'duplicate', 'pending') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_music_copyright_status_seed (status, created_at),
    INDEX idx_music_copyright_hash_seed (file_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE artists
    ADD COLUMN IF NOT EXISTS status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending' AFTER verified,
    ADD COLUMN IF NOT EXISTS rejection_reason TEXT NULL AFTER status,
    ADD COLUMN IF NOT EXISTS instagram_url VARCHAR(500) NULL AFTER apple_music_url,
    ADD COLUMN IF NOT EXISTS rights_confirmed_at TIMESTAMP NULL AFTER instagram_url,
    ADD INDEX IF NOT EXISTS idx_artists_status (status, updated_at);

UPDATE artists
SET status = CASE WHEN verified = 1 THEN 'approved' ELSE COALESCE(status, 'pending') END
WHERE status IS NULL OR status = 'pending';

ALTER TABLE music_tracks
    ADD COLUMN IF NOT EXISTS artist_id BIGINT UNSIGNED NULL AFTER user_id,
    MODIFY COLUMN status ENUM('draft', 'pending', 'pending_review', 'published', 'rejected', 'blocked', 'archived') NOT NULL DEFAULT 'published',
    ADD INDEX IF NOT EXISTS idx_music_tracks_artist_status_b17 (artist_id, status);

ALTER TABLE music_copyright_checks
    ADD COLUMN IF NOT EXISTS file_hash CHAR(64) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS title VARCHAR(180) NULL AFTER file_hash,
    ADD COLUMN IF NOT EXISTS artist_name VARCHAR(160) NULL AFTER title,
    ADD COLUMN IF NOT EXISTS provider VARCHAR(80) NOT NULL DEFAULT 'local_hash_keyword' AFTER reasons,
    ADD INDEX IF NOT EXISTS idx_music_copyright_status_b17 (status, created_at),
    ADD INDEX IF NOT EXISTS idx_music_copyright_hash_b17 (file_hash);

CREATE TABLE IF NOT EXISTS content_translations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    content_type VARCHAR(40) NOT NULL,
    content_id BIGINT UNSIGNED NOT NULL,
    language CHAR(2) NOT NULL,
    title VARCHAR(190) NULL,
    description TEXT NULL,
    body MEDIUMTEXT NULL,
    status ENUM('draft', 'published', 'needs_review') NOT NULL DEFAULT 'draft',
    translated_by BIGINT UNSIGNED NULL,
    provider VARCHAR(80) NOT NULL DEFAULT 'local_dictionary',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_content_translation (content_type, content_id, language),
    INDEX idx_content_translation_lookup (content_type, content_id, language, status),
    INDEX idx_content_translation_status (language, status, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE pages
    ADD COLUMN IF NOT EXISTS show_in_menu TINYINT(1) NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS sort_order INT NOT NULL DEFAULT 0 AFTER show_in_footer,
    ADD INDEX IF NOT EXISTS idx_pages_public_b17 (language, status, show_in_menu, show_in_footer, sort_order);

-- Bolum 13 Kullanici profil sayfasi, creator paketleri ve profil aksiyonlari.
ALTER TABLE user_profiles
    ADD COLUMN IF NOT EXISTS nickname VARCHAR(80) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS display_name VARCHAR(120) NOT NULL DEFAULT '' AFTER nickname,
    ADD COLUMN IF NOT EXISTS about TEXT NULL AFTER display_name,
    ADD COLUMN IF NOT EXISTS avatar VARCHAR(255) NULL AFTER about,
    ADD COLUMN IF NOT EXISTS cover_image VARCHAR(255) NULL AFTER avatar,
    ADD COLUMN IF NOT EXISTS instagram VARCHAR(190) NULL AFTER cover_image,
    ADD COLUMN IF NOT EXISTS youtube VARCHAR(190) NULL AFTER instagram,
    ADD COLUMN IF NOT EXISTS website VARCHAR(255) NULL AFTER youtube,
    ADD COLUMN IF NOT EXISTS privacy ENUM('public', 'logged_in', 'private') NOT NULL DEFAULT 'public' AFTER website,
    ADD COLUMN IF NOT EXISTS message_privacy ENUM('everyone', 'friends', 'none') NOT NULL DEFAULT 'everyone' AFTER privacy;

ALTER TABLE user_profiles
    MODIFY COLUMN privacy ENUM('public', 'members', 'logged_in', 'private') NOT NULL DEFAULT 'public',
    MODIFY COLUMN message_privacy ENUM('everyone', 'members', 'friends', 'none') NOT NULL DEFAULT 'everyone';

UPDATE user_profiles
SET privacy = 'logged_in'
WHERE privacy = 'members';

UPDATE user_profiles
SET message_privacy = 'everyone'
WHERE message_privacy = 'members';

ALTER TABLE user_profiles
    MODIFY COLUMN privacy ENUM('public', 'logged_in', 'private') NOT NULL DEFAULT 'public',
    MODIFY COLUMN message_privacy ENUM('everyone', 'friends', 'none') NOT NULL DEFAULT 'everyone',
    ADD INDEX IF NOT EXISTS idx_user_profiles_nickname (nickname),
    ADD INDEX IF NOT EXISTS idx_user_profiles_privacy (privacy);

ALTER TABLE users
    ADD INDEX IF NOT EXISTS idx_users_nickname (nickname);

CREATE TABLE IF NOT EXISTS user_follows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    follower_id BIGINT UNSIGNED NOT NULL,
    followed_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_follows_pair (follower_id, followed_id),
    INDEX idx_user_follows_followed (followed_id, created_at),
    CONSTRAINT fk_user_follows_follower FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_follows_followed FOREIGN KEY (followed_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bolum 12 ZPanel gelismis admin paneli, sayfa yonetimi ve admin loglari tamamlamalari.
CREATE TABLE IF NOT EXISTS pages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(190) NOT NULL,
    title VARCHAR(180) NOT NULL,
    content MEDIUMTEXT NOT NULL,
    language CHAR(2) NOT NULL DEFAULT 'tr',
    status ENUM('draft', 'published', 'hidden', 'trashed') NOT NULL DEFAULT 'draft',
    show_in_menu TINYINT(1) NOT NULL DEFAULT 0,
    show_in_header TINYINT(1) NOT NULL DEFAULT 0,
    show_in_footer TINYINT(1) NOT NULL DEFAULT 1,
    seo_title VARCHAR(190) NULL,
    seo_description VARCHAR(255) NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    UNIQUE KEY uniq_pages_slug_lang_b12 (slug, language)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE pages
    ADD COLUMN IF NOT EXISTS slug VARCHAR(190) NOT NULL DEFAULT '' AFTER id,
    ADD COLUMN IF NOT EXISTS title VARCHAR(180) NOT NULL DEFAULT '' AFTER slug,
    ADD COLUMN IF NOT EXISTS content MEDIUMTEXT NULL AFTER title,
    ADD COLUMN IF NOT EXISTS language CHAR(2) NOT NULL DEFAULT 'tr' AFTER content,
    ADD COLUMN IF NOT EXISTS status ENUM('draft', 'published', 'hidden', 'trashed') NOT NULL DEFAULT 'draft' AFTER language,
    ADD COLUMN IF NOT EXISTS show_in_menu TINYINT(1) NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS show_in_header TINYINT(1) NOT NULL DEFAULT 0 AFTER show_in_menu,
    ADD COLUMN IF NOT EXISTS show_in_footer TINYINT(1) NOT NULL DEFAULT 1 AFTER show_in_header,
    ADD COLUMN IF NOT EXISTS seo_title VARCHAR(190) NULL AFTER show_in_footer,
    ADD COLUMN IF NOT EXISTS seo_description VARCHAR(255) NULL AFTER seo_title,
    ADD COLUMN IF NOT EXISTS created_by BIGINT UNSIGNED NULL AFTER seo_description,
    ADD COLUMN IF NOT EXISTS updated_by BIGINT UNSIGNED NULL AFTER created_by,
    ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL AFTER updated_at;

ALTER TABLE pages
    MODIFY COLUMN status ENUM('draft', 'published', 'archived', 'hidden', 'trashed') NOT NULL DEFAULT 'draft';

UPDATE pages SET status = 'hidden' WHERE status = 'archived';

ALTER TABLE pages
    MODIFY COLUMN status ENUM('draft', 'published', 'hidden', 'trashed') NOT NULL DEFAULT 'draft',
    ADD UNIQUE KEY IF NOT EXISTS uniq_pages_slug_lang (slug, language),
    ADD INDEX IF NOT EXISTS idx_pages_public (language, status, show_in_footer, show_in_menu);

CREATE TABLE IF NOT EXISTS admin_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    target_type VARCHAR(80) NULL,
    target_id BIGINT UNSIGNED NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    details LONGTEXT NULL,
    ip_address VARCHAR(64) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin_logs_action_created_b12 (action, created_at),
    INDEX idx_admin_logs_target_b12 (target_type, target_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE admin_logs
    ADD COLUMN IF NOT EXISTS admin_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS action VARCHAR(120) NOT NULL DEFAULT 'admin.action' AFTER admin_id,
    ADD COLUMN IF NOT EXISTS target_type VARCHAR(80) NULL AFTER action,
    ADD COLUMN IF NOT EXISTS target_id BIGINT UNSIGNED NULL AFTER target_type,
    ADD COLUMN IF NOT EXISTS entity_type VARCHAR(80) NULL AFTER target_id,
    ADD COLUMN IF NOT EXISTS entity_id BIGINT UNSIGNED NULL AFTER entity_type,
    ADD COLUMN IF NOT EXISTS details LONGTEXT NULL AFTER entity_id,
    ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) NULL AFTER details,
    ADD COLUMN IF NOT EXISTS user_agent VARCHAR(255) NULL AFTER ip_address,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER user_agent,
    ADD INDEX IF NOT EXISTS idx_admin_logs_action_created (action, created_at),
    ADD INDEX IF NOT EXISTS idx_admin_logs_target (target_type, target_id);

ALTER TABLE notifications
    ADD COLUMN IF NOT EXISTS target_type VARCHAR(40) NULL AFTER type,
    ADD COLUMN IF NOT EXISTS target_role VARCHAR(40) NULL AFTER target_type,
    ADD COLUMN IF NOT EXISTS url VARCHAR(500) NULL AFTER body,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE settings
    ADD INDEX IF NOT EXISTS idx_settings_public (is_public, setting_key);

ALTER TABLE message_reports
    ADD COLUMN IF NOT EXISTS admin_note TEXT NULL AFTER status,
    ADD COLUMN IF NOT EXISTS resolved_by BIGINT UNSIGNED NULL AFTER admin_note,
    ADD COLUMN IF NOT EXISTS resolved_at TIMESTAMP NULL AFTER resolved_by;

-- Bolum 10: Sanal POS provider altyapisi, checkout ve test odeme akisi.
-- Kart numarasi, CVV, son kullanma tarihi ve kart uzerindeki isim icin kalici kolon eklenmez.

CREATE TABLE IF NOT EXISTS payment_providers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_key VARCHAR(80) NOT NULL,
    provider_name VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 0,
    is_test_mode TINYINT(1) NOT NULL DEFAULT 1,
    config_json LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_payment_providers_key (provider_key),
    INDEX idx_payment_providers_active (is_active, provider_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_callbacks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(80) NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    provider_reference VARCHAR(190) NULL,
    payload LONGTEXT NULL,
    signature_valid TINYINT(1) NOT NULL DEFAULT 0,
    ip_address VARCHAR(64) NULL,
    status VARCHAR(80) NOT NULL DEFAULT 'received',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_payment_callbacks_provider_created (provider, created_at),
    INDEX idx_payment_callbacks_order (order_id),
    CONSTRAINT fk_payment_callbacks_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS period_type ENUM('monthly', '6_month', '12_month') NOT NULL DEFAULT 'monthly' AFTER type,
    ADD COLUMN IF NOT EXISTS gross_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER period_type,
    ADD COLUMN IF NOT EXISTS discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER gross_amount,
    ADD COLUMN IF NOT EXISTS provider_fee_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000 AFTER discount_amount,
    ADD COLUMN IF NOT EXISTS provider_fee_fixed DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER provider_fee_rate,
    ADD COLUMN IF NOT EXISTS provider_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER provider_fee_fixed,
    ADD COLUMN IF NOT EXISTS commission_rate DECIMAL(8,4) NOT NULL DEFAULT 0.1000 AFTER provider_fee_amount,
    ADD COLUMN IF NOT EXISTS commission_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER commission_rate,
    ADD COLUMN IF NOT EXISTS tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0.2000 AFTER commission_amount,
    ADD COLUMN IF NOT EXISTS tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER tax_rate,
    ADD COLUMN IF NOT EXISTS creator_net_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER tax_amount,
    ADD COLUMN IF NOT EXISTS platform_net_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER creator_net_amount,
    ADD COLUMN IF NOT EXISTS payment_provider VARCHAR(80) NULL AFTER currency,
    ADD COLUMN IF NOT EXISTS provider_reference VARCHAR(190) NULL AFTER payment_provider,
    ADD INDEX IF NOT EXISTS idx_orders_user_status (user_id, status),
    ADD INDEX IF NOT EXISTS idx_orders_order_no (order_no),
    ADD INDEX IF NOT EXISTS idx_orders_provider (payment_provider, provider_reference);

ALTER TABLE orders
    MODIFY COLUMN status ENUM('pending', 'pending_payment', 'paid', 'completed', 'failed', 'cancelled', 'refunded', 'partially_refunded') NOT NULL DEFAULT 'pending_payment';

UPDATE orders
SET status = 'pending_payment'
WHERE status = 'pending';

UPDATE orders
SET gross_amount = total
WHERE gross_amount = 0.00 AND total > 0.00;

UPDATE orders
SET commission_amount = platform_fee,
    platform_net_amount = platform_fee,
    creator_net_amount = creator_amount
WHERE platform_fee > 0.00 OR creator_amount > 0.00;

ALTER TABLE orders
    MODIFY COLUMN status ENUM('pending_payment', 'paid', 'completed', 'failed', 'cancelled', 'refunded', 'partially_refunded') NOT NULL DEFAULT 'pending_payment';

ALTER TABLE payments
    ADD COLUMN IF NOT EXISTS provider_reference VARCHAR(190) NULL AFTER provider,
    ADD COLUMN IF NOT EXISTS payment_session_id VARCHAR(190) NULL AFTER provider_reference,
    ADD COLUMN IF NOT EXISTS card_type VARCHAR(40) NULL AFTER currency,
    ADD COLUMN IF NOT EXISTS card_brand VARCHAR(40) NULL AFTER card_type,
    ADD COLUMN IF NOT EXISTS installment_count TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER card_brand,
    ADD COLUMN IF NOT EXISTS is_3d_secure TINYINT(1) NOT NULL DEFAULT 0 AFTER installment_count,
    ADD COLUMN IF NOT EXISTS raw_request LONGTEXT NULL AFTER is_3d_secure,
    ADD COLUMN IF NOT EXISTS raw_response LONGTEXT NULL AFTER raw_request,
    ADD COLUMN IF NOT EXISTS failed_at TIMESTAMP NULL AFTER paid_at,
    ADD INDEX IF NOT EXISTS idx_payments_order_status (order_id, status),
    ADD INDEX IF NOT EXISTS idx_payments_reference (provider, provider_reference);

ALTER TABLE payments
    MODIFY COLUMN status ENUM('pending', 'authorized', 'paid', 'failed', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending';

UPDATE payments
SET status = 'pending'
WHERE status = 'authorized';

UPDATE payments
SET provider_reference = provider_payment_id
WHERE provider_reference IS NULL AND provider_payment_id IS NOT NULL;

ALTER TABLE payments
    MODIFY COLUMN status ENUM('pending', 'paid', 'failed', 'cancelled', 'refunded') NOT NULL DEFAULT 'pending';

-- Bolum 11: Creator kazanc, bakiye, para cekme ve finans yonetimi.
-- Eski creator_id/bank_accounts tablolari korunur; yeni alanlar geriye uyumlu eklenir.

CREATE TABLE IF NOT EXISTS user_bank_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    account_holder VARCHAR(160) NOT NULL,
    bank_name VARCHAR(120) NOT NULL,
    iban VARCHAR(34) NOT NULL,
    identity_or_tax_no VARCHAR(40) NULL,
    company_name VARCHAR(160) NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_bank_account_iban (user_id, iban),
    INDEX idx_user_bank_accounts_user (user_id, is_default),
    CONSTRAINT fk_user_bank_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_bank_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    bank_name VARCHAR(120) NOT NULL,
    account_holder VARCHAR(160) NOT NULL,
    iban VARCHAR(34) NOT NULL,
    branch_code VARCHAR(40) NULL,
    account_no VARCHAR(80) NULL,
    currency CHAR(3) NOT NULL DEFAULT 'TRY',
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_site_bank_account_iban (iban),
    INDEX idx_site_bank_accounts_active (is_active, is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE user_bank_accounts
    ADD UNIQUE KEY IF NOT EXISTS uniq_user_bank_account_iban (user_id, iban);

ALTER TABLE site_bank_accounts
    ADD UNIQUE KEY IF NOT EXISTS uniq_site_bank_account_iban (iban);

ALTER TABLE creator_balances
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER creator_id,
    ADD COLUMN IF NOT EXISTS rejected_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER withdrawn_balance,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER currency,
    ADD INDEX IF NOT EXISTS idx_creator_balances_user (user_id);

UPDATE creator_balances SET user_id = creator_id WHERE user_id IS NULL;

ALTER TABLE creator_ledgers
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER creator_id,
    ADD COLUMN IF NOT EXISTS withdrawal_id BIGINT UNSIGNED NULL AFTER order_id,
    ADD COLUMN IF NOT EXISTS balance_before DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER amount,
    ADD COLUMN IF NOT EXISTS balance_after DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER balance_before,
    ADD INDEX IF NOT EXISTS idx_creator_ledgers_user_created (user_id, created_at),
    ADD INDEX IF NOT EXISTS idx_creator_ledgers_withdrawal (withdrawal_id);

UPDATE creator_ledgers SET user_id = creator_id WHERE user_id IS NULL;

ALTER TABLE creator_ledgers
    MODIFY COLUMN type ENUM(
        'earning',
        'fee',
        'release',
        'withdrawal',
        'refund',
        'sale_income',
        'commission_deducted',
        'tax_deducted',
        'provider_fee_deducted',
        'withdrawal_requested',
        'withdrawal_approved',
        'withdrawal_sent',
        'withdrawal_rejected',
        'refund_deducted',
        'manual_adjustment'
    ) NOT NULL DEFAULT 'sale_income';

ALTER TABLE withdrawal_requests
    MODIFY COLUMN bank_account_id BIGINT UNSIGNED NULL,
    MODIFY COLUMN creator_id BIGINT UNSIGNED NOT NULL,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER creator_id,
    ADD COLUMN IF NOT EXISTS user_bank_account_id BIGINT UNSIGNED NULL AFTER bank_account_id,
    ADD COLUMN IF NOT EXISTS site_bank_account_id BIGINT UNSIGNED NULL AFTER user_bank_account_id,
    ADD COLUMN IF NOT EXISTS admin_id BIGINT UNSIGNED NULL AFTER status,
    ADD COLUMN IF NOT EXISTS user_note TEXT NULL AFTER admin_note,
    ADD COLUMN IF NOT EXISTS transaction_reference VARCHAR(190) NULL AFTER user_note,
    ADD COLUMN IF NOT EXISTS receipt_file VARCHAR(255) NULL AFTER transaction_reference,
    ADD COLUMN IF NOT EXISTS approved_at TIMESTAMP NULL AFTER requested_at,
    ADD COLUMN IF NOT EXISTS processing_at TIMESTAMP NULL AFTER approved_at,
    ADD COLUMN IF NOT EXISTS sent_at TIMESTAMP NULL AFTER processing_at,
    ADD COLUMN IF NOT EXISTS rejected_at TIMESTAMP NULL AFTER sent_at,
    ADD COLUMN IF NOT EXISTS cancelled_at TIMESTAMP NULL AFTER rejected_at,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER processed_at,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD INDEX IF NOT EXISTS idx_withdrawals_user_status (user_id, status),
    ADD INDEX IF NOT EXISTS idx_withdrawals_admin_status (status, requested_at);

UPDATE withdrawal_requests SET user_id = creator_id WHERE user_id IS NULL;
UPDATE withdrawal_requests SET user_bank_account_id = bank_account_id WHERE user_bank_account_id IS NULL;

ALTER TABLE withdrawal_requests
    MODIFY COLUMN status ENUM('pending', 'approved', 'processing', 'sent', 'paid', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending';

UPDATE withdrawal_requests SET status = 'sent', sent_at = COALESCE(processed_at, requested_at) WHERE status = 'paid';

ALTER TABLE withdrawal_requests
    MODIFY COLUMN status ENUM('pending', 'approved', 'processing', 'sent', 'paid', 'rejected', 'cancelled') NOT NULL DEFAULT 'pending';

ALTER TABLE orders
    ADD COLUMN IF NOT EXISTS provider_fee_rate DECIMAL(8,4) NOT NULL DEFAULT 0.0000 AFTER discount_amount,
    ADD COLUMN IF NOT EXISTS provider_fee_fixed DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER provider_fee_rate,
    ADD COLUMN IF NOT EXISTS provider_fee_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER provider_fee_fixed,
    ADD COLUMN IF NOT EXISTS commission_rate DECIMAL(8,4) NOT NULL DEFAULT 0.1000 AFTER provider_fee_amount,
    ADD COLUMN IF NOT EXISTS commission_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER commission_rate,
    ADD COLUMN IF NOT EXISTS tax_rate DECIMAL(8,4) NOT NULL DEFAULT 0.2000 AFTER commission_amount,
    ADD COLUMN IF NOT EXISTS tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER tax_rate,
    ADD COLUMN IF NOT EXISTS creator_net_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER tax_amount,
    ADD COLUMN IF NOT EXISTS platform_net_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER creator_net_amount;

-- Bolum 9: Patreon benzeri paket / abonelik / premium erisim altyapisi.
-- Eski Bolum 0-8 semasinda bulunan creator_id/user_id/price/is_active alanlari korunur;
-- yeni MVP alanlari idempotent sekilde eklenir ve geriye uyumlu tutulur.

ALTER TABLE packages
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER description,
    ADD COLUMN IF NOT EXISTS price_6_month DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER price_monthly,
    ADD COLUMN IF NOT EXISTS price_12_month DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER price_6_month,
    ADD COLUMN IF NOT EXISTS status ENUM('active', 'inactive') NOT NULL DEFAULT 'active' AFTER price_12_month;

UPDATE packages
SET user_id = creator_id
WHERE user_id IS NULL AND creator_id IS NOT NULL;

UPDATE packages
SET price_monthly = price
WHERE price_monthly = 0.00 AND price > 0.00;

UPDATE packages
SET price_6_month = ROUND(price_monthly * 6, 2)
WHERE price_6_month = 0.00 AND price_monthly > 0.00;

UPDATE packages
SET price_12_month = ROUND(price_monthly * 12, 2)
WHERE price_12_month = 0.00 AND price_monthly > 0.00;

UPDATE packages
SET status = CASE WHEN is_active = 1 THEN 'active' ELSE 'inactive' END;

ALTER TABLE packages
    MODIFY COLUMN status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    ADD INDEX IF NOT EXISTS idx_packages_user_status (user_id, status),
    ADD INDEX IF NOT EXISTS idx_packages_creator_status (creator_id, status);

ALTER TABLE subscriptions
    ADD COLUMN IF NOT EXISTS subscriber_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS order_id BIGINT UNSIGNED NULL AFTER package_id;

UPDATE subscriptions
SET subscriber_id = user_id
WHERE subscriber_id IS NULL AND user_id IS NOT NULL;

ALTER TABLE subscriptions
    MODIFY COLUMN status ENUM('active', 'past_due', 'cancelled', 'expired', 'payment_failed', 'pending') NOT NULL DEFAULT 'pending';

UPDATE subscriptions
SET status = 'payment_failed'
WHERE status = 'past_due';

ALTER TABLE subscriptions
    MODIFY COLUMN status ENUM('active', 'expired', 'cancelled', 'payment_failed', 'pending') NOT NULL DEFAULT 'pending',
    ADD INDEX IF NOT EXISTS idx_subscriptions_subscriber_status (subscriber_id, status),
    ADD INDEX IF NOT EXISTS idx_subscriptions_creator_package_status (creator_id, package_id, status),
    ADD INDEX IF NOT EXISTS idx_subscriptions_order (order_id);

ALTER TABLE mods
    ADD COLUMN IF NOT EXISTS privacy ENUM('public', 'free', 'members', 'private', 'premium', 'subscriber') NOT NULL DEFAULT 'public' AFTER access_type,
    ADD COLUMN IF NOT EXISTS package_id BIGINT UNSIGNED NULL AFTER privacy;

ALTER TABLE music_tracks
    ADD COLUMN IF NOT EXISTS privacy ENUM('public', 'free', 'premium', 'subscriber', 'private') NOT NULL DEFAULT 'public' AFTER apple_music_url,
    ADD COLUMN IF NOT EXISTS package_id BIGINT UNSIGNED NULL AFTER privacy;

-- Bolum 4 Zalorox Shield guvenlik ve moderasyon altyapisi.
CREATE TABLE IF NOT EXISTS domain_whitelist (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(190) NOT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_domain_whitelist_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS domain_blacklist (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    domain VARCHAR(190) NOT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_domain_blacklist_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS forbidden_words (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    word VARCHAR(120) NOT NULL,
    severity TINYINT UNSIGNED NOT NULL DEFAULT 60,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_forbidden_words_word (word)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE security_scan_logs
    ADD COLUMN IF NOT EXISTS target_type VARCHAR(80) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS target_id BIGINT UNSIGNED NULL AFTER target_type,
    ADD COLUMN IF NOT EXISTS input_value VARCHAR(500) NULL AFTER scan_type,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER input_value,
    ADD COLUMN IF NOT EXISTS status ENUM('safe', 'review', 'blocked') NOT NULL DEFAULT 'safe' AFTER risk_score,
    ADD COLUMN IF NOT EXISTS reasons LONGTEXT NULL AFTER status,
    ADD COLUMN IF NOT EXISTS action_taken ENUM('allow', 'pending_review', 'blocked') NOT NULL DEFAULT 'allow' AFTER reasons;

ALTER TABLE security_scan_logs
    MODIFY COLUMN scan_type VARCHAR(40) NOT NULL;

ALTER TABLE domain_whitelist
    ADD COLUMN IF NOT EXISTS description VARCHAR(255) NULL AFTER domain,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER description;

ALTER TABLE domain_blacklist
    ADD COLUMN IF NOT EXISTS description VARCHAR(255) NULL AFTER domain,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER description;

ALTER TABLE forbidden_words
    ADD COLUMN IF NOT EXISTS severity TINYINT UNSIGNED NOT NULL DEFAULT 60 AFTER word,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER severity;

ALTER TABLE domain_whitelist
    ADD UNIQUE KEY IF NOT EXISTS uniq_domain_whitelist_domain (domain);

ALTER TABLE domain_blacklist
    ADD UNIQUE KEY IF NOT EXISTS uniq_domain_blacklist_domain (domain);

ALTER TABLE forbidden_words
    ADD UNIQUE KEY IF NOT EXISTS uniq_forbidden_words_word (word);

-- Bolum 6 Music subdomain / muzik sitesi schema tamamlamalari.
CREATE TABLE IF NOT EXISTS music_genres (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(160) NOT NULL,
    description TEXT NULL,
    icon VARCHAR(255) NULL,
    banner VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_music_genres_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_tracks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    category_id BIGINT UNSIGNED NULL,
    title VARCHAR(180) NOT NULL,
    slug VARCHAR(220) NOT NULL,
    artist_name VARCHAR(160) NOT NULL,
    description MEDIUMTEXT NULL,
    tags VARCHAR(500) NULL,
    cover_image VARCHAR(255) NULL,
    audio_file VARCHAR(255) NULL,
    external_link VARCHAR(500) NULL,
    youtube_url VARCHAR(500) NULL,
    spotify_url VARCHAR(500) NULL,
    apple_music_url VARCHAR(500) NULL,
    privacy ENUM('public', 'free', 'premium', 'subscriber', 'private') NOT NULL DEFAULT 'public',
    package_id BIGINT UNSIGNED NULL,
    status ENUM('draft', 'pending', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'published',
    play_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    view_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    like_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
    featured TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_music_tracks_slug (slug),
    INDEX idx_music_tracks_status_created (status, created_at),
    INDEX idx_music_tracks_status_play (status, play_count),
    INDEX idx_music_tracks_user_status (user_id, status),
    CONSTRAINT fk_music_tracks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_music_tracks_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_music_tracks_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_track_genres (
    track_id BIGINT UNSIGNED NOT NULL,
    genre_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (track_id, genre_id),
    CONSTRAINT fk_music_track_genres_track FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
    CONSTRAINT fk_music_track_genres_genre FOREIGN KEY (genre_id) REFERENCES music_genres(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_plays (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_music_plays_track_created (track_id, created_at),
    INDEX idx_music_plays_user_created (user_id, created_at),
    INDEX idx_music_plays_ip_created (ip_address, created_at),
    CONSTRAINT fk_music_plays_track FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
    CONSTRAINT fk_music_plays_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS music_likes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_music_likes_user (track_id, user_id),
    CONSTRAINT fk_music_likes_track FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE CASCADE,
    CONSTRAINT fk_music_likes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE music_genres
    ADD COLUMN IF NOT EXISTS name VARCHAR(120) NOT NULL DEFAULT '' AFTER id,
    ADD COLUMN IF NOT EXISTS slug VARCHAR(160) NOT NULL DEFAULT '' AFTER name,
    ADD COLUMN IF NOT EXISTS description TEXT NULL AFTER slug,
    ADD COLUMN IF NOT EXISTS icon VARCHAR(255) NULL AFTER description,
    ADD COLUMN IF NOT EXISTS banner VARCHAR(255) NULL AFTER icon,
    ADD COLUMN IF NOT EXISTS sort_order INT NOT NULL DEFAULT 0 AFTER banner,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER sort_order,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE music_tracks
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER id,
    ADD COLUMN IF NOT EXISTS category_id BIGINT UNSIGNED NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS title VARCHAR(180) NOT NULL DEFAULT '' AFTER category_id,
    ADD COLUMN IF NOT EXISTS slug VARCHAR(220) NOT NULL DEFAULT '' AFTER title,
    ADD COLUMN IF NOT EXISTS artist_name VARCHAR(160) NOT NULL DEFAULT 'Zalorox Music' AFTER slug,
    ADD COLUMN IF NOT EXISTS description MEDIUMTEXT NULL AFTER artist_name,
    ADD COLUMN IF NOT EXISTS cover_image VARCHAR(255) NULL AFTER description,
    ADD COLUMN IF NOT EXISTS audio_file VARCHAR(255) NULL AFTER cover_image,
    ADD COLUMN IF NOT EXISTS external_link VARCHAR(500) NULL AFTER audio_file,
    ADD COLUMN IF NOT EXISTS youtube_url VARCHAR(500) NULL AFTER external_link,
    ADD COLUMN IF NOT EXISTS spotify_url VARCHAR(500) NULL AFTER youtube_url,
    ADD COLUMN IF NOT EXISTS apple_music_url VARCHAR(500) NULL AFTER spotify_url,
    ADD COLUMN IF NOT EXISTS privacy ENUM('public', 'free', 'premium', 'subscriber', 'private') NOT NULL DEFAULT 'public' AFTER apple_music_url,
    ADD COLUMN IF NOT EXISTS package_id BIGINT UNSIGNED NULL AFTER privacy,
    ADD COLUMN IF NOT EXISTS status ENUM('draft', 'pending', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'published' AFTER package_id,
    ADD COLUMN IF NOT EXISTS play_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS view_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER play_count,
    ADD COLUMN IF NOT EXISTS like_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER view_count,
    ADD COLUMN IF NOT EXISTS featured TINYINT(1) NOT NULL DEFAULT 0 AFTER like_count,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER featured,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE music_tracks
    MODIFY COLUMN privacy ENUM('public', 'free', 'premium', 'subscriber', 'private') NOT NULL DEFAULT 'public',
    MODIFY COLUMN status ENUM('draft', 'pending', 'published', 'rejected', 'archived') NOT NULL DEFAULT 'published';

ALTER TABLE music_track_genres
    ADD COLUMN IF NOT EXISTS track_id BIGINT UNSIGNED NOT NULL,
    ADD COLUMN IF NOT EXISTS genre_id BIGINT UNSIGNED NOT NULL;

ALTER TABLE music_plays
    ADD COLUMN IF NOT EXISTS track_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NULL AFTER track_id,
    ADD COLUMN IF NOT EXISTS ip_address VARCHAR(64) NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER ip_address;

ALTER TABLE music_likes
    ADD COLUMN IF NOT EXISTS track_id BIGINT UNSIGNED NOT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS user_id BIGINT UNSIGNED NOT NULL AFTER track_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER user_id;

ALTER TABLE music_genres
    ADD UNIQUE KEY IF NOT EXISTS uniq_music_genres_slug (slug);

ALTER TABLE music_tracks
    ADD UNIQUE KEY IF NOT EXISTS uniq_music_tracks_slug (slug),
    ADD INDEX IF NOT EXISTS idx_music_tracks_status_created (status, created_at),
    ADD INDEX IF NOT EXISTS idx_music_tracks_status_play (status, play_count),
    ADD INDEX IF NOT EXISTS idx_music_tracks_user_status (user_id, status);

-- Studio upload/edit modernizasyonu: lokal Zalorox AI etiketleri.
ALTER TABLE mods
    ADD COLUMN IF NOT EXISTS tags VARCHAR(500) NULL AFTER description;

ALTER TABLE music_tracks
    ADD COLUMN IF NOT EXISTS tags VARCHAR(500) NULL AFTER description;

ALTER TABLE music_likes
    ADD UNIQUE KEY IF NOT EXISTS uniq_music_likes_user (track_id, user_id);

-- Bolum 16 Profil ayarlari, 3D mod galerisi, site premium muzik, artist ve telif kontrol altyapisi.
CREATE TABLE IF NOT EXISTS mod_models (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mod_id BIGINT UNSIGNED NOT NULL,
    model_path VARCHAR(500) NOT NULL,
    model_type VARCHAR(40) NOT NULL DEFAULT 'glb',
    thumbnail_path VARCHAR(255) NULL,
    title VARCHAR(160) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_mod_models_mod_path (mod_id, model_path),
    INDEX idx_mod_models_mod_sort (mod_id, sort_order),
    CONSTRAINT fk_mod_models_mod FOREIGN KEY (mod_id) REFERENCES mods(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_plans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    slug VARCHAR(160) NOT NULL,
    description TEXT NULL,
    price_monthly DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    price_6_month DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    price_12_month DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    features_json LONGTEXT NULL,
    status ENUM('active', 'inactive', 'archived') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_site_plans_slug (slug),
    INDEX idx_site_plans_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    site_plan_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NULL,
    starts_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at TIMESTAMP NULL,
    status ENUM('active', 'past_due', 'cancelled', 'expired') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_site_subscriptions_user_plan (user_id, site_plan_id),
    INDEX idx_site_subscriptions_user_status (user_id, status),
    INDEX idx_site_subscriptions_plan_status (site_plan_id, status),
    CONSTRAINT fk_site_subscriptions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_site_subscriptions_plan FOREIGN KEY (site_plan_id) REFERENCES site_plans(id) ON DELETE CASCADE,
    CONSTRAINT fk_site_subscriptions_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS artists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    artist_name VARCHAR(160) NOT NULL,
    slug VARCHAR(180) NOT NULL,
    bio TEXT NULL,
    avatar VARCHAR(255) NULL,
    cover_image VARCHAR(255) NULL,
    verified TINYINT(1) NOT NULL DEFAULT 0,
    spotify_url VARCHAR(500) NULL,
    youtube_url VARCHAR(500) NULL,
    apple_music_url VARCHAR(500) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_artists_slug (slug),
    INDEX idx_artists_user (user_id),
    CONSTRAINT fk_artists_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE music_tracks
    ADD COLUMN IF NOT EXISTS artist_id BIGINT UNSIGNED NULL AFTER user_id,
    MODIFY COLUMN status ENUM('draft', 'pending', 'pending_review', 'published', 'rejected', 'blocked', 'archived') NOT NULL DEFAULT 'published',
    ADD INDEX IF NOT EXISTS idx_music_tracks_artist_status (artist_id, status);

CREATE TABLE IF NOT EXISTS music_copyright_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    track_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    file_hash CHAR(64) NULL,
    title VARCHAR(180) NULL,
    artist_name VARCHAR(160) NULL,
    status ENUM('clean', 'review', 'blocked', 'duplicate', 'pending') NOT NULL DEFAULT 'pending',
    risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    reasons LONGTEXT NULL,
    provider VARCHAR(80) NOT NULL DEFAULT 'local_hash_keyword',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_music_copyright_track_provider (track_id, provider),
    INDEX idx_music_copyright_hash (file_hash),
    INDEX idx_music_copyright_status (status, created_at),
    CONSTRAINT fk_music_copyright_track FOREIGN KEY (track_id) REFERENCES music_tracks(id) ON DELETE SET NULL,
    CONSTRAINT fk_music_copyright_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bolum 8 Auth hata duzeltme, arkadaslik, mesajlasma ve gelismis yorum schema tamamlamalari.
ALTER TABLE friendships
    ADD COLUMN IF NOT EXISTS addressee_id BIGINT UNSIGNED NULL AFTER requester_id,
    ADD COLUMN IF NOT EXISTS receiver_id BIGINT UNSIGNED NULL AFTER requester_id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

UPDATE friendships
SET receiver_id = addressee_id
WHERE receiver_id IS NULL AND addressee_id IS NOT NULL;

DELETE FROM friendships
WHERE receiver_id IS NULL OR requester_id = receiver_id;

ALTER TABLE friendships
    MODIFY COLUMN receiver_id BIGINT UNSIGNED NOT NULL,
    MODIFY COLUMN status ENUM('pending', 'accepted', 'blocked', 'rejected', 'removed') NOT NULL DEFAULT 'pending';

UPDATE friendships
SET status = 'removed'
WHERE status = 'blocked';

ALTER TABLE friendships
    MODIFY COLUMN status ENUM('pending', 'accepted', 'rejected', 'removed') NOT NULL DEFAULT 'pending';

ALTER TABLE friendships
    ADD UNIQUE KEY IF NOT EXISTS uniq_friendships_requester_receiver (requester_id, receiver_id),
    ADD INDEX IF NOT EXISTS idx_friendships_receiver_status (receiver_id, status),
    ADD INDEX IF NOT EXISTS idx_friendships_requester_status (requester_id, status);

CREATE TABLE IF NOT EXISTS blocked_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocker_id BIGINT UNSIGNED NOT NULL,
    blocked_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_blocked_users_pair (blocker_id, blocked_id),
    CONSTRAINT fk_blocked_users_blocker_b8 FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_blocked_users_blocked_b8 FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE blocked_users
    ADD UNIQUE KEY IF NOT EXISTS uniq_blocked_users_pair (blocker_id, blocked_id),
    ADD INDEX IF NOT EXISTS idx_blocked_users_blocked (blocked_id);

ALTER TABLE conversations
    ADD COLUMN IF NOT EXISTS type VARCHAR(20) NOT NULL DEFAULT 'private' AFTER id,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

UPDATE conversations
SET type = 'private'
WHERE type NOT IN ('private', 'group');

ALTER TABLE conversations
    MODIFY COLUMN type ENUM('private', 'group') NOT NULL DEFAULT 'private',
    ADD INDEX IF NOT EXISTS idx_conversations_updated (updated_at);

CREATE TABLE IF NOT EXISTS conversation_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    last_read_at TIMESTAMP NULL,
    archived_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_conversation_participant (conversation_id, user_id),
    INDEX idx_conversation_participants_user (user_id, archived_at),
    CONSTRAINT fk_conversation_participants_conversation_b8 FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_conversation_participants_user_b8 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE conversation_participants
    ADD COLUMN IF NOT EXISTS last_read_at TIMESTAMP NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS archived_at TIMESTAMP NULL AFTER last_read_at,
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER archived_at,
    ADD UNIQUE KEY IF NOT EXISTS uniq_conversation_participant (conversation_id, user_id),
    ADD INDEX IF NOT EXISTS idx_conversation_participants_user (user_id, archived_at);

INSERT IGNORE INTO conversation_participants (conversation_id, user_id, created_at)
SELECT DISTINCT conversation_id, sender_id, NOW()
FROM messages
WHERE conversation_id IS NOT NULL AND sender_id IS NOT NULL;

ALTER TABLE messages
    ADD COLUMN IF NOT EXISTS message_type ENUM('text', 'emoji', 'gif', 'image', 'link') NOT NULL DEFAULT 'text' AFTER sender_id,
    ADD COLUMN IF NOT EXISTS attachment_path VARCHAR(255) NULL AFTER body,
    ADD COLUMN IF NOT EXISTS gif_url VARCHAR(500) NULL AFTER attachment_path,
    ADD COLUMN IF NOT EXISTS link_url VARCHAR(500) NULL AFTER gif_url,
    ADD COLUMN IF NOT EXISTS is_deleted TINYINT(1) NOT NULL DEFAULT 0 AFTER link_url,
    ADD COLUMN IF NOT EXISTS status ENUM('sent', 'pending_review', 'blocked', 'deleted') NOT NULL DEFAULT 'sent' AFTER is_deleted,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;

ALTER TABLE messages
    MODIFY COLUMN message_type ENUM('text', 'emoji', 'gif', 'image', 'link') NOT NULL DEFAULT 'text',
    MODIFY COLUMN status ENUM('sent', 'pending_review', 'blocked', 'deleted') NOT NULL DEFAULT 'sent',
    ADD INDEX IF NOT EXISTS idx_messages_conversation_created (conversation_id, created_at),
    ADD INDEX IF NOT EXISTS idx_messages_sender_created (sender_id, created_at);

CREATE TABLE IF NOT EXISTS message_reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT UNSIGNED NOT NULL,
    reporter_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NOT NULL,
    status ENUM('open', 'reviewed', 'dismissed', 'actioned') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_message_reports_status (status, created_at),
    CONSTRAINT fk_message_reports_message_b8 FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_message_reports_reporter_b8 FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE comments
    MODIFY COLUMN content_type ENUM('mod', 'music', 'community') NOT NULL DEFAULT 'mod',
    ADD COLUMN IF NOT EXISTS parent_id BIGINT UNSIGNED NULL AFTER user_id,
    ADD COLUMN IF NOT EXISTS content TEXT NULL AFTER parent_id,
    ADD COLUMN IF NOT EXISTS status ENUM('published', 'pending_review', 'blocked', 'visible', 'hidden', 'pending', 'deleted') NOT NULL DEFAULT 'published' AFTER content,
    ADD COLUMN IF NOT EXISTS risk_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
    ADD INDEX IF NOT EXISTS idx_comments_parent (parent_id);

UPDATE comments
SET content = body
WHERE (content IS NULL OR content = '') AND body IS NOT NULL;

ALTER TABLE comment_likes
    ADD UNIQUE KEY IF NOT EXISTS uniq_comment_likes_user (comment_id, user_id);

-- Bolum 20 final enum guard: sonraki bloklar comments enumunu daraltmissa community_post destegini geri ekle.
ALTER TABLE comments
    MODIFY COLUMN content_type ENUM('mod', 'music', 'community', 'community_post') NOT NULL DEFAULT 'mod',
    MODIFY COLUMN commentable_type ENUM('mod', 'music', 'community', 'community_post') NULL;
