from __future__ import annotations

import argparse
import hashlib
import json
import sqlite3
import uuid
from pathlib import Path, PurePosixPath
from typing import Any, Mapping

SCHEMA_VERSION = 2
SCHEMA_FILES = (
    Path(__file__).with_name("schema_sqlite_v1.sql"),
    Path(__file__).with_name("schema_sqlite_v2.sql"),
)
DEFAULT_DB_RELATIVE = Path("Shared") / "data" / "sort_local.sqlite3"
DEFAULT_STORAGE_CODE = "STDOWN_INSTAGRAM"
SOURCE_MODES = frozenset(
    {
        "MANUAL_SINGLE",
        "BULK_ALL",
        "BULK_FILTERED",
        "TOP_SORTED",
        "TRANSCRIPT_REQUEST",
    }
)


def derive_stdown_root(st_root: str | Path) -> Path:
    """Return the fixed sibling _StDown directory for the supplied _St root."""
    root = Path(st_root).expanduser().resolve()
    if root.name != "_St":
        raise ValueError(f"st_root must point to the _St directory: {root}")
    return root.parent / "_StDown"


def default_database_path(st_root: str | Path) -> Path:
    return Path(st_root).expanduser().resolve() / DEFAULT_DB_RELATIVE


def connect_database(db_path: str | Path) -> sqlite3.Connection:
    path = Path(db_path).expanduser().resolve()
    path.parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path, timeout=10.0)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA busy_timeout = 10000")
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    return conn


def _apply_schema(conn: sqlite3.Connection) -> None:
    for schema_file in SCHEMA_FILES:
        conn.executescript(schema_file.read_text(encoding="utf-8"))
    migrations = (
        (1, "initial_storage_foundation"),
        (2, "unified_media_saved_flow"),
    )
    conn.executemany(
        "INSERT OR IGNORE INTO schema_migrations(version, name) VALUES (?, ?)",
        migrations,
    )


def initialize_database(
    db_path: str | Path,
    st_root: str | Path,
    *,
    create_storage_dirs: bool = True,
) -> dict[str, Any]:
    stdown_root = derive_stdown_root(st_root)
    instagram_root = stdown_root / "instagram"
    if create_storage_dirs:
        instagram_root.mkdir(parents=True, exist_ok=True)

    conn = connect_database(db_path)
    try:
        _apply_schema(conn)
        conn.execute(
            """
            INSERT INTO storage_roots(root_code, root_path, is_default)
            VALUES (?, ?, 1)
            ON CONFLICT(root_code) DO UPDATE SET
                root_path = excluded.root_path,
                is_default = 1,
                updated_at = CURRENT_TIMESTAMP
            """,
            (DEFAULT_STORAGE_CODE, str(instagram_root)),
        )
        conn.execute("INSERT OR IGNORE INTO collector_profile(id) VALUES (1)")
        conn.execute("INSERT OR IGNORE INTO device_profile(id) VALUES (1)")
        conn.commit()

        table_count = conn.execute(
            "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
        ).fetchone()[0]
        journal_mode = conn.execute("PRAGMA journal_mode").fetchone()[0]
        foreign_keys = conn.execute("PRAGMA foreign_keys").fetchone()[0]
        user_version = conn.execute("PRAGMA user_version").fetchone()[0]
        return {
            "db_path": str(Path(db_path).expanduser().resolve()),
            "storage_root": str(instagram_root),
            "schema_version": int(user_version),
            "table_count": int(table_count),
            "journal_mode": str(journal_mode).lower(),
            "foreign_keys": int(foreign_keys),
        }
    finally:
        conn.close()


def normalize_source_mode(source_mode: str) -> str:
    normalized = str(source_mode or "").strip().upper()
    if normalized not in SOURCE_MODES:
        allowed = ", ".join(sorted(SOURCE_MODES))
        raise ValueError(f"unsupported source_mode={source_mode!r}; allowed={allowed}")
    return normalized


def normalize_relative_path(relative_path: str | Path) -> str:
    raw = str(relative_path or "").replace("\\", "/").strip()
    if not raw:
        raise ValueError("relative_path is required")
    path = PurePosixPath(raw)
    if path.is_absolute() or ".." in path.parts:
        raise ValueError(f"relative_path must stay below the configured storage root: {raw}")
    return path.as_posix()


def create_download_batch(
    db_path: str | Path,
    *,
    source_mode: str,
    batch_key: str | None = None,
    account_id: int | None = None,
    sort_by: str = "",
    filter_days: int | None = None,
    requested_count: int = 0,
    metadata: Mapping[str, Any] | None = None,
) -> int:
    mode = normalize_source_mode(source_mode)
    key = str(batch_key or uuid.uuid4())
    conn = connect_database(db_path)
    try:
        cursor = conn.execute(
            """
            INSERT INTO download_batches(
                batch_key, source_mode, account_id, sort_by, filter_days,
                requested_count, metadata_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                key,
                mode,
                account_id,
                str(sort_by or ""),
                filter_days,
                max(0, int(requested_count)),
                json.dumps(dict(metadata or {}), ensure_ascii=False, sort_keys=True),
            ),
        )
        conn.commit()
        return int(cursor.lastrowid)
    finally:
        conn.close()


def create_download_job(
    db_path: str | Path,
    *,
    post_id: int,
    source_mode: str,
    asset_kind: str,
    media_index: int = 0,
    batch_id: int | None = None,
    rank_no: int | None = None,
    source_url: str = "",
    expected_file_name: str = "",
) -> int:
    mode = normalize_source_mode(source_mode)
    kind = str(asset_kind or "").strip().upper()
    if not kind:
        raise ValueError("asset_kind is required")
    conn = connect_database(db_path)
    try:
        cursor = conn.execute(
            """
            INSERT INTO download_jobs(
                batch_id, post_id, source_mode, asset_kind, media_index,
                rank_no, source_url, expected_file_name
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                batch_id,
                int(post_id),
                mode,
                kind,
                max(0, int(media_index)),
                rank_no,
                str(source_url or ""),
                str(expected_file_name or ""),
            ),
        )
        conn.commit()
        return int(cursor.lastrowid)
    finally:
        conn.close()


def record_media_saved(
    db_path: str | Path,
    *,
    post_id: int,
    source_mode: str,
    asset_kind: str,
    relative_path: str | Path,
    file_name: str,
    file_size: int,
    media_index: int = 0,
    rank_no: int | None = None,
    md5: str = "",
    sha256: str = "",
    source_url: str = "",
    storage_root_id: int | None = None,
    download_job_id: int | None = None,
    event_uuid: str | None = None,
    completed_at: str | None = None,
    payload: Mapping[str, Any] | None = None,
) -> dict[str, Any]:
    """Record the common MEDIA_SAVED result for every download entry path.

    The server sync payload intentionally excludes the local relative path. The
    local event retains only a path relative to the configured storage root.
    """
    mode = normalize_source_mode(source_mode)
    kind = str(asset_kind or "").strip().upper()
    if not kind:
        raise ValueError("asset_kind is required")
    safe_relative_path = normalize_relative_path(relative_path)
    safe_file_name = str(file_name or "").strip()
    if not safe_file_name:
        raise ValueError("file_name is required")
    size = max(0, int(file_size))
    index = max(0, int(media_index))
    event_key = str(event_uuid or uuid.uuid4())
    completed_value = completed_at or None

    conn = connect_database(db_path)
    try:
        conn.execute("BEGIN IMMEDIATE")
        existing = conn.execute(
            "SELECT id, asset_id FROM media_saved_events WHERE event_uuid = ?",
            (event_key,),
        ).fetchone()
        if existing:
            conn.commit()
            return {
                "event_id": int(existing["id"]),
                "asset_id": int(existing["asset_id"]),
                "event_uuid": event_key,
                "source_mode": mode,
                "idempotent": True,
            }

        job_row = None
        if download_job_id is not None:
            job_row = conn.execute(
                """
                SELECT id, batch_id, post_id, source_mode, asset_kind, media_index
                FROM download_jobs WHERE id = ?
                """,
                (int(download_job_id),),
            ).fetchone()
            if not job_row:
                raise ValueError(f"download_job_id not found: {download_job_id}")
            expected = (int(post_id), mode, kind, index)
            actual = (
                int(job_row["post_id"]),
                str(job_row["source_mode"]),
                str(job_row["asset_kind"]),
                int(job_row["media_index"]),
            )
            if actual != expected:
                raise ValueError(
                    f"download job mismatch: expected={expected!r} actual={actual!r}"
                )

        post = conn.execute(
            "SELECT id, account_id, shortcode FROM posts WHERE id = ?",
            (int(post_id),),
        ).fetchone()
        if not post:
            raise ValueError(f"post_id not found: {post_id}")

        if storage_root_id is None:
            default_root = conn.execute(
                "SELECT id FROM storage_roots WHERE is_default = 1 ORDER BY id LIMIT 1"
            ).fetchone()
            storage_root_id = int(default_root["id"]) if default_root else None

        conn.execute(
            """
            INSERT INTO media_assets(
                post_id, asset_kind, media_index, source_url, storage_root_id,
                relative_path, file_name, file_size, md5, sha256,
                local_status, verified_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'AVAILABLE', COALESCE(?, CURRENT_TIMESTAMP))
            ON CONFLICT(post_id, asset_kind, media_index) DO UPDATE SET
                source_url = CASE WHEN excluded.source_url <> '' THEN excluded.source_url ELSE media_assets.source_url END,
                storage_root_id = COALESCE(excluded.storage_root_id, media_assets.storage_root_id),
                relative_path = excluded.relative_path,
                file_name = excluded.file_name,
                file_size = excluded.file_size,
                md5 = excluded.md5,
                sha256 = excluded.sha256,
                local_status = 'AVAILABLE',
                verified_at = excluded.verified_at,
                updated_at = CURRENT_TIMESTAMP
            """,
            (
                int(post_id),
                kind,
                index,
                str(source_url or ""),
                storage_root_id,
                safe_relative_path,
                safe_file_name,
                size,
                str(md5 or ""),
                str(sha256 or ""),
                completed_value,
            ),
        )
        asset = conn.execute(
            "SELECT id FROM media_assets WHERE post_id = ? AND asset_kind = ? AND media_index = ?",
            (int(post_id), kind, index),
        ).fetchone()
        asset_id = int(asset["id"])

        local_payload = dict(payload or {})
        event_cursor = conn.execute(
            """
            INSERT INTO media_saved_events(
                event_uuid, download_job_id, post_id, asset_id, account_id,
                shortcode, source_mode, asset_kind, media_index, rank_no,
                relative_path, file_name, file_size, md5, sha256,
                completed_at, payload_json
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP), ?)
            """,
            (
                event_key,
                download_job_id,
                int(post_id),
                asset_id,
                post["account_id"],
                str(post["shortcode"]),
                mode,
                kind,
                index,
                rank_no,
                safe_relative_path,
                safe_file_name,
                size,
                str(md5 or ""),
                str(sha256 or ""),
                completed_value,
                json.dumps(local_payload, ensure_ascii=False, sort_keys=True),
            ),
        )
        event_id = int(event_cursor.lastrowid)

        if download_job_id is not None:
            conn.execute(
                """
                UPDATE download_jobs
                SET job_status = 'COMPLETE', completed_at = COALESCE(?, CURRENT_TIMESTAMP),
                    last_error = '', updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (completed_value, int(download_job_id)),
            )
            batch_id = job_row["batch_id"] if job_row else None
            if batch_id is not None:
                batch_progress = conn.execute(
                    """
                    SELECT b.requested_count,
                           COUNT(j.id) AS total_jobs,
                           SUM(CASE WHEN j.job_status <> 'COMPLETE' THEN 1 ELSE 0 END) AS remaining_jobs
                    FROM download_batches b
                    LEFT JOIN download_jobs j ON j.batch_id = b.id
                    WHERE b.id = ?
                    GROUP BY b.id, b.requested_count
                    """,
                    (int(batch_id),),
                ).fetchone()
                requested = int(batch_progress["requested_count"] or 0) if batch_progress else 0
                total_jobs = int(batch_progress["total_jobs"] or 0) if batch_progress else 0
                remaining = int(batch_progress["remaining_jobs"] or 0) if batch_progress else 0
                if remaining == 0 and (requested <= 0 or total_jobs >= requested):
                    conn.execute(
                        """
                        UPDATE download_batches
                        SET batch_status = 'COMPLETE',
                            completed_at = COALESCE(?, CURRENT_TIMESTAMP),
                            updated_at = CURRENT_TIMESTAMP
                        WHERE id = ?
                        """,
                        (completed_value, int(batch_id)),
                    )
                else:
                    conn.execute(
                        """
                        UPDATE download_batches
                        SET batch_status = 'RUNNING', completed_at = NULL,
                            updated_at = CURRENT_TIMESTAMP
                        WHERE id = ?
                        """,
                        (int(batch_id),),
                    )

        conn.execute(
            "UPDATE posts SET media_state = 'MEDIA_AVAILABLE', updated_at = CURRENT_TIMESTAMP WHERE id = ?",
            (int(post_id),),
        )
        conn.execute(
            """
            INSERT INTO p2p_inventory(asset_id, available, updated_at)
            VALUES (?, 1, CURRENT_TIMESTAMP)
            ON CONFLICT(asset_id) DO UPDATE SET available = 1, updated_at = CURRENT_TIMESTAMP
            """,
            (asset_id,),
        )

        server_payload = {
            "client_event_id": event_key,
            "post_id": int(post_id),
            "shortcode": str(post["shortcode"]),
            "source_mode": mode,
            "asset_kind": kind,
            "media_index": index,
            "rank_no": rank_no,
            "file_name": safe_file_name,
            "file_size": size,
            "md5": str(md5 or ""),
            "sha256": str(sha256 or ""),
            "completed_at": completed_value,
        }
        conn.execute(
            """
            INSERT INTO sync_queue(entity_type, entity_local_id, operation, payload_json)
            VALUES ('MEDIA_SAVED', ?, 'UPSERT', ?)
            """,
            (event_id, json.dumps(server_payload, ensure_ascii=False, sort_keys=True)),
        )
        conn.commit()
        return {
            "event_id": event_id,
            "asset_id": asset_id,
            "event_uuid": event_key,
            "source_mode": mode,
            "idempotent": False,
        }
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

def ensure_storage_root(
    db_path: str | Path,
    *,
    root_path: str | Path,
    root_code: str | None = None,
    is_default: bool = False,
) -> int:
    resolved = Path(root_path).expanduser().resolve()
    code = str(root_code or "").strip()
    if not code:
        digest = hashlib.sha256(str(resolved).encode("utf-8")).hexdigest()[:12].upper()
        code = f"LOCAL_DOWNLOAD_{digest}"
    conn = connect_database(db_path)
    try:
        if is_default:
            conn.execute("UPDATE storage_roots SET is_default = 0 WHERE is_default = 1")
        conn.execute(
            """
            INSERT INTO storage_roots(root_code, root_path, is_default)
            VALUES (?, ?, ?)
            ON CONFLICT(root_code) DO UPDATE SET
                root_path = excluded.root_path,
                is_default = excluded.is_default,
                updated_at = CURRENT_TIMESTAMP
            """,
            (code, str(resolved), 1 if is_default else 0),
        )
        row = conn.execute("SELECT id FROM storage_roots WHERE root_code = ?", (code,)).fetchone()
        conn.commit()
        return int(row["id"])
    finally:
        conn.close()


def _is_relative_to(path: Path, root: Path) -> bool:
    try:
        path.relative_to(root)
        return True
    except ValueError:
        return False


def resolve_download_storage(
    db_path: str | Path,
    *,
    file_path: str | Path,
    account_path: str = "",
    account_folder_merge: bool = False,
) -> dict[str, Any]:
    file = Path(file_path).expanduser().resolve()
    if not file.is_file():
        raise FileNotFoundError(str(file))

    conn = connect_database(db_path)
    try:
        default = conn.execute(
            "SELECT id, root_path FROM storage_roots WHERE is_default = 1 ORDER BY id LIMIT 1"
        ).fetchone()
    finally:
        conn.close()

    if default:
        default_root = Path(str(default["root_path"])).expanduser().resolve()
        if _is_relative_to(file, default_root):
            return {
                "storage_root_id": int(default["id"]),
                "storage_root_path": str(default_root),
                "relative_path": file.relative_to(default_root).as_posix(),
                "storage_layout": "STDOWN_DEFAULT",
            }

    parent = file.parent
    root = parent
    account_parts = [part for part in str(account_path or "").replace("\\", "/").split("/") if part]
    if not account_folder_merge and account_parts:
        probe = parent
        matched = True
        for expected in reversed(account_parts):
            if probe.name.casefold() != expected.casefold():
                matched = False
                break
            probe = probe.parent
        if matched:
            root = probe

    root_id = ensure_storage_root(db_path, root_path=root)
    return {
        "storage_root_id": root_id,
        "storage_root_path": str(root),
        "relative_path": file.relative_to(root).as_posix(),
        "storage_layout": "LOCAL_DOWNLOAD_ROOT",
    }


def upsert_download_post(
    db_path: str | Path,
    *,
    shortcode: str,
    owner_username: str,
    post_url: str = "",
    caption: str = "",
    thumbnail_url: str = "",
    video_url: str = "",
    published_at: str | None = None,
    stats: Mapping[str, Any] | None = None,
) -> dict[str, int]:
    code = str(shortcode or "").strip()
    owner = str(owner_username or "").strip().lstrip("@")
    if not code:
        raise ValueError("shortcode is required")
    if not owner:
        raise ValueError("owner_username is required")
    url = str(post_url or "").strip() or f"https://www.instagram.com/p/{code}/"
    conn = connect_database(db_path)
    try:
        conn.execute("BEGIN IMMEDIATE")
        conn.execute(
            """
            INSERT INTO social_accounts(platform, username, profile_url)
            VALUES ('instagram', ?, ?)
            ON CONFLICT(platform, username) DO UPDATE SET
                profile_url = CASE WHEN excluded.profile_url <> '' THEN excluded.profile_url ELSE social_accounts.profile_url END,
                updated_at = CURRENT_TIMESTAMP
            """,
            (owner, f"https://www.instagram.com/{owner}/"),
        )
        account = conn.execute(
            "SELECT id FROM social_accounts WHERE platform = 'instagram' AND username = ?",
            (owner,),
        ).fetchone()
        account_id = int(account["id"])
        conn.execute(
            """
            INSERT INTO posts(
                account_id, platform, shortcode, post_url, caption, published_at,
                thumbnail_url, video_url, source_url_observed_at
            ) VALUES (?, 'instagram', ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
            ON CONFLICT(platform, shortcode) DO UPDATE SET
                account_id = excluded.account_id,
                post_url = CASE WHEN excluded.post_url <> '' THEN excluded.post_url ELSE posts.post_url END,
                caption = CASE WHEN excluded.caption <> '' THEN excluded.caption ELSE posts.caption END,
                published_at = COALESCE(excluded.published_at, posts.published_at),
                thumbnail_url = CASE WHEN excluded.thumbnail_url <> '' THEN excluded.thumbnail_url ELSE posts.thumbnail_url END,
                video_url = CASE WHEN excluded.video_url <> '' THEN excluded.video_url ELSE posts.video_url END,
                source_url_observed_at = CURRENT_TIMESTAMP,
                last_observed_at = CURRENT_TIMESTAMP,
                updated_at = CURRENT_TIMESTAMP
            """,
            (
                account_id,
                code,
                url,
                str(caption or ""),
                published_at,
                str(thumbnail_url or ""),
                str(video_url or ""),
            ),
        )
        post = conn.execute(
            "SELECT id FROM posts WHERE platform = 'instagram' AND shortcode = ?",
            (code,),
        ).fetchone()
        post_id = int(post["id"])

        values = dict(stats or {})
        if values:
            collected_at = str(values.get("captured_at") or values.get("collected_at") or "").strip() or None
            metric_values = (
                int(values.get("views") or 0),
                int(values.get("likes") or 0),
                int(values.get("comments") or 0),
                int(values.get("reposts") or 0),
            )
            conn.execute(
                """
                INSERT INTO post_stat_latest(post_id, views, likes, comments, reposts, collected_at)
                VALUES (?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP))
                ON CONFLICT(post_id) DO UPDATE SET
                    views = excluded.views,
                    likes = excluded.likes,
                    comments = excluded.comments,
                    reposts = excluded.reposts,
                    collected_at = excluded.collected_at
                """,
                (post_id, *metric_values, collected_at),
            )
            conn.execute(
                """
                INSERT OR IGNORE INTO post_stat_history(
                    post_id, views, likes, comments, reposts, collected_at
                ) VALUES (?, ?, ?, ?, ?, COALESCE(?, CURRENT_TIMESTAMP))
                """,
                (post_id, *metric_values, collected_at),
            )
        conn.commit()
        return {"account_id": account_id, "post_id": post_id}
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def ensure_download_batch(
    db_path: str | Path,
    *,
    batch_key: str,
    source_mode: str,
    account_id: int | None = None,
    sort_by: str = "",
    filter_days: int | None = None,
    requested_count: int = 0,
    metadata: Mapping[str, Any] | None = None,
) -> int:
    key = str(batch_key or "").strip()
    if not key:
        raise ValueError("batch_key is required")
    mode = normalize_source_mode(source_mode)
    conn = connect_database(db_path)
    try:
        conn.execute(
            """
            INSERT INTO download_batches(
                batch_key, source_mode, account_id, sort_by, filter_days,
                requested_count, batch_status, metadata_json
            ) VALUES (?, ?, ?, ?, ?, ?, 'RUNNING', ?)
            ON CONFLICT(batch_key) DO UPDATE SET
                account_id = COALESCE(excluded.account_id, download_batches.account_id),
                sort_by = CASE WHEN excluded.sort_by <> '' THEN excluded.sort_by ELSE download_batches.sort_by END,
                filter_days = COALESCE(excluded.filter_days, download_batches.filter_days),
                requested_count = MAX(download_batches.requested_count, excluded.requested_count),
                batch_status = CASE WHEN download_batches.batch_status = 'COMPLETE' THEN 'COMPLETE' ELSE 'RUNNING' END,
                metadata_json = excluded.metadata_json,
                updated_at = CURRENT_TIMESTAMP
            """,
            (
                key,
                mode,
                account_id,
                str(sort_by or ""),
                filter_days,
                max(0, int(requested_count)),
                json.dumps(dict(metadata or {}), ensure_ascii=False, sort_keys=True),
            ),
        )
        row = conn.execute("SELECT id FROM download_batches WHERE batch_key = ?", (key,)).fetchone()
        conn.commit()
        return int(row["id"])
    finally:
        conn.close()


def ensure_download_job(
    db_path: str | Path,
    *,
    batch_id: int,
    post_id: int,
    source_mode: str,
    asset_kind: str,
    media_index: int = 0,
    rank_no: int | None = None,
    source_url: str = "",
    expected_file_name: str = "",
) -> int:
    mode = normalize_source_mode(source_mode)
    kind = str(asset_kind or "").strip().upper()
    if not kind:
        raise ValueError("asset_kind is required")
    conn = connect_database(db_path)
    try:
        conn.execute(
            """
            INSERT INTO download_jobs(
                batch_id, post_id, source_mode, asset_kind, media_index,
                rank_no, source_url, expected_file_name, job_status
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'DOWNLOADING')
            ON CONFLICT(batch_id, post_id, asset_kind, media_index) DO UPDATE SET
                rank_no = COALESCE(excluded.rank_no, download_jobs.rank_no),
                source_url = CASE WHEN excluded.source_url <> '' THEN excluded.source_url ELSE download_jobs.source_url END,
                expected_file_name = CASE WHEN excluded.expected_file_name <> '' THEN excluded.expected_file_name ELSE download_jobs.expected_file_name END,
                job_status = CASE WHEN download_jobs.job_status = 'COMPLETE' THEN 'COMPLETE' ELSE 'DOWNLOADING' END,
                last_error = '',
                updated_at = CURRENT_TIMESTAMP
            """,
            (
                int(batch_id),
                int(post_id),
                mode,
                kind,
                max(0, int(media_index)),
                rank_no,
                str(source_url or ""),
                str(expected_file_name or ""),
            ),
        )
        row = conn.execute(
            """
            SELECT id FROM download_jobs
            WHERE batch_id = ? AND post_id = ? AND asset_kind = ? AND media_index = ?
            """,
            (int(batch_id), int(post_id), kind, max(0, int(media_index))),
        ).fetchone()
        conn.commit()
        return int(row["id"])
    finally:
        conn.close()


def record_downloaded_file(
    *,
    st_root: str | Path,
    file_path: str | Path,
    source_mode: str,
    shortcode: str,
    owner_username: str,
    asset_kind: str,
    media_index: int = 0,
    rank_no: int | None = None,
    source_url: str = "",
    post_url: str = "",
    caption: str = "",
    thumbnail_url: str = "",
    video_url: str = "",
    published_at: str | None = None,
    md5: str = "",
    sha256: str = "",
    file_size: int = 0,
    account_path: str = "",
    account_folder_merge: bool = False,
    batch_key: str = "",
    requested_count: int = 0,
    sort_by: str = "",
    filter_days: int | None = None,
    event_uuid: str = "",
    stats: Mapping[str, Any] | None = None,
    payload: Mapping[str, Any] | None = None,
) -> dict[str, Any]:
    root = Path(st_root).expanduser().resolve()
    db_path = default_database_path(root)
    initialize_database(db_path, root)
    mode = normalize_source_mode(source_mode)
    event_key = str(event_uuid or uuid.uuid4())
    context = upsert_download_post(
        db_path,
        shortcode=shortcode,
        owner_username=owner_username,
        post_url=post_url,
        caption=caption,
        thumbnail_url=thumbnail_url,
        video_url=video_url,
        published_at=published_at,
        stats=stats,
    )
    storage = resolve_download_storage(
        db_path,
        file_path=file_path,
        account_path=account_path,
        account_folder_merge=account_folder_merge,
    )
    effective_batch_key = str(batch_key or f"single-{event_key}")
    batch_id = ensure_download_batch(
        db_path,
        batch_key=effective_batch_key,
        source_mode=mode,
        account_id=context["account_id"],
        sort_by=sort_by,
        filter_days=filter_days,
        requested_count=max(1, int(requested_count or 0)),
        metadata={
            "account_path": str(account_path or ""),
            "storage_layout": storage["storage_layout"],
        },
    )
    job_id = ensure_download_job(
        db_path,
        batch_id=batch_id,
        post_id=context["post_id"],
        source_mode=mode,
        asset_kind=asset_kind,
        media_index=media_index,
        rank_no=rank_no,
        source_url=source_url,
        expected_file_name=Path(file_path).name,
    )
    local_payload = dict(payload or {})
    local_payload.update(
        {
            "batch_key": effective_batch_key,
            "storage_layout": storage["storage_layout"],
        }
    )
    try:
        result = record_media_saved(
            db_path,
            post_id=context["post_id"],
            source_mode=mode,
            asset_kind=asset_kind,
            relative_path=storage["relative_path"],
            file_name=Path(file_path).name,
            file_size=file_size,
            media_index=media_index,
            rank_no=rank_no,
            md5=md5,
            sha256=sha256,
            source_url=source_url,
            storage_root_id=storage["storage_root_id"],
            download_job_id=job_id,
            event_uuid=event_key,
            payload=local_payload,
        )
    except Exception as exc:
        conn = connect_database(db_path)
        try:
            conn.execute(
                """
                UPDATE download_jobs
                SET job_status = 'FAILED', last_error = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (str(exc), job_id),
            )
            conn.commit()
        finally:
            conn.close()
        raise
    return {
        **result,
        "db_path": str(db_path),
        "account_id": context["account_id"],
        "post_id": context["post_id"],
        "batch_id": batch_id,
        "job_id": job_id,
        "relative_path": storage["relative_path"],
        "storage_root_id": storage["storage_root_id"],
        "storage_layout": storage["storage_layout"],
    }


def main() -> int:
    parser = argparse.ArgumentParser(description="Initialize sort local SQLite storage.")
    parser.add_argument("--st-root", required=True, help="Absolute path to the _St directory")
    parser.add_argument("--db", help="SQLite path. Defaults to _St/Shared/data/sort_local.sqlite3")
    parser.add_argument("--no-create-storage-dirs", action="store_true")
    args = parser.parse_args()

    st_root = Path(args.st_root)
    db_path = Path(args.db) if args.db else default_database_path(st_root)
    try:
        result = initialize_database(
            db_path,
            st_root,
            create_storage_dirs=not args.no_create_storage_dirs,
        )
    except (OSError, sqlite3.Error, ValueError) as exc:
        print(json.dumps({"overall": "FAIL", "error": str(exc)}, ensure_ascii=False))
        return 1

    print(json.dumps({"overall": "OK", **result}, ensure_ascii=False, indent=2))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())
