from __future__ import annotations

import re
from pathlib import Path

SQL_PATH = Path(__file__).resolve().parent / "storage" / "mysql" / "migration_v45_from_yellow_v44.sql"
SQL = SQL_PATH.read_text(encoding="utf-8")
SQL_UPPER = SQL.upper()

REQUIRED_TABLES = {
    "collector_members",
    "collector_devices",
    "collector_groups",
    "collector_group_members",
    "collector_group_join_requests",
    "collector_social_accounts",
    "collector_account_lists",
    "collector_account_group_visibility",
    "collector_collection_profiles",
    "collector_account_rules",
    "collector_posts",
    "collector_post_stat_latest",
    "collector_post_stat_history",
    "collector_asset_sources",
    "collector_asset_inventory",
    "collector_transcripts",
    "collector_hook_segments",
    "collector_categories",
    "collector_post_categories",
    "collector_tags",
    "collector_post_tags",
    "collector_notes",
    "collector_review_templates",
    "collector_review_items",
    "collector_reviews",
    "collector_review_scores",
    "collector_comments",
    "collector_aggregates",
    "collector_aggregate_accounts",
    "collector_tasks",
    "collector_sync_events",
    "collector_point_ledger",
}


def created_tables() -> set[str]:
    return {
        match.group(1)
        for match in re.finditer(
            r"CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+`([^`]+)`",
            SQL,
            re.IGNORECASE,
        )
    }


def test_mysql_migration_is_additive_and_keeps_yellow_v44_tables() -> None:
    assert "DROP TABLE" not in SQL_UPPER
    assert "TRUNCATE TABLE" not in SQL_UPPER
    assert "ALTER TABLE `IG_MEDIA`" not in SQL_UPPER
    assert "ALTER TABLE `IG_MEDIA_USER_DOWNLOAD`" not in SQL_UPPER
    assert "실제 MP4/JPG 파일과 사용자 로컬 절대경로는 서버에 저장하지 않는다" in SQL


def test_mysql_schema_has_required_mvp_and_future_tables() -> None:
    assert REQUIRED_TABLES.issubset(created_tables())


def test_collector_code_is_public_identity_not_token() -> None:
    member_block = SQL.split("CREATE TABLE IF NOT EXISTS `collector_members`", 1)[1].split(
        ") ENGINE=InnoDB", 1
    )[0]
    device_block = SQL.split("CREATE TABLE IF NOT EXISTS `collector_devices`", 1)[1].split(
        ") ENGINE=InnoDB", 1
    )[0]
    assert "`collector_code` varchar(8) NOT NULL" in member_block
    assert "UNIQUE KEY `uq_collector_code`" in member_block
    assert "`token_hash` char(64)" in device_block
    assert "`computer_id_hash` char(64)" in device_block


def test_server_stores_urls_and_device_inventory_without_local_paths() -> None:
    post_block = SQL.split("CREATE TABLE IF NOT EXISTS `collector_posts`", 1)[1].split(
        ") ENGINE=InnoDB", 1
    )[0]
    inventory_block = SQL.split(
        "CREATE TABLE IF NOT EXISTS `collector_asset_inventory`", 1
    )[1].split(") ENGINE=InnoDB", 1)[0]
    assert "`thumbnail_url`" in post_block
    assert "`video_url`" in post_block
    assert "`device_id`" in inventory_block
    assert "LOCAL_PATH" not in SQL_UPPER
    assert "SAVE_FOLDER" not in SQL_UPPER