# FILE: _St/Analyze8780/analyze8780_repository.py | ROLE: Analyze8780 SQLite 조회·전환·로컬 미디어 저장소
from __future__ import annotations

import os
import subprocess
import sys
import threading
import uuid
from pathlib import Path, PurePosixPath
from typing import Any, Callable

ST_ROOT = Path(__file__).resolve().parents[1]
if str(ST_ROOT) not in sys.path:
    sys.path.insert(0, str(ST_ROOT))

from Shared.storage.sort_storage import (
    connect_database,
    create_database_file,
    default_database_path,
    get_post_detail,
    get_post_stats,
    initialize_database,
    list_account_posts,
    list_accounts,
    list_collection_accounts,
    list_database_files,
    list_deleted_accounts,
    open_database_file,
    replace_collection_accounts,
    save_database_as,
    soft_delete_account,
    upsert_observation,
)


class AnalyzeRepository:
    def __init__(self, st_root: str | Path, db_path: str | Path | None = None) -> None:
        self.st_root = Path(st_root).expanduser().resolve()
        self._explicit_db_path = Path(db_path).expanduser().resolve() if db_path else None
        self._db_lock = threading.RLock()
        self.db_path = self._explicit_db_path or default_database_path(self.st_root)
        self._path_launcher: Callable[[Path], None] = self._default_path_launcher
        initialize_database(self.db_path, self.st_root)

    @staticmethod
    def _default_path_launcher(path: Path) -> None:
        if sys.platform == "win32":
            os.startfile(str(path))  # type: ignore[attr-defined]
            return
        command = ["open", str(path)] if sys.platform == "darwin" else ["xdg-open", str(path)]
        subprocess.Popen(command, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)

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

    @classmethod
    def _resolve_asset_path(cls, asset: dict[str, Any]) -> Path:
        root_text = str(asset.get("root_path") or "").strip()
        relative_text = str(asset.get("relative_path") or "").replace("\\", "/").strip()
        if not root_text or not relative_text:
            raise ValueError(f"media asset path is incomplete: asset_id={asset.get('id')}")
        relative = PurePosixPath(relative_text)
        if relative.is_absolute() or ".." in relative.parts:
            raise ValueError(f"media asset path escaped storage root: asset_id={asset.get('id')}")
        root = Path(root_text).expanduser().resolve()
        target = root.joinpath(*relative.parts).resolve()
        if not cls._is_relative_to(target, root):
            raise ValueError(f"media asset path escaped storage root: asset_id={asset.get('id')}")
        return target

    def _post_asset_rows(self, conn: Any, post_id: int) -> list[dict[str, Any]]:
        rows = conn.execute(
            """
            SELECT ma.id, ma.post_id, ma.asset_kind, ma.media_index, ma.storage_root_id,
                   ma.folder_name, ma.file_name, ma.relative_path, ma.local_status,
                   sr.root_path
            FROM media_assets ma
            LEFT JOIN storage_roots sr ON sr.id=ma.storage_root_id
            WHERE ma.post_id=? AND ma.local_status='AVAILABLE'
            ORDER BY ma.media_index, ma.id
            """,
            (int(post_id),),
        ).fetchall()
        return [dict(row) for row in rows]

    def health(self) -> dict[str, Any]:
        with self._db_lock:
            return {
                "ok": True,
                "service": "Analyze8780",
                "version": "2.3.8.289",
                "db_path": str(self.db_path),
                "db_filename": self.db_path.name,
            }

    def accounts(
        self,
        *,
        page: int = 1,
        page_size: int = 50,
        include_deleted: bool = False,
        search_pattern: str = "",
    ) -> dict[str, Any]:
        with self._db_lock:
            return list_accounts(
                self.db_path,
                page=page,
                page_size=page_size,
                include_deleted=include_deleted,
                search_pattern=search_pattern,
            )

    def account_table_check(self, search_pattern: str = "") -> dict[str, Any]:
        pattern = str(search_pattern or "")
        with self._db_lock:
            conn = connect_database(self.db_path)
            try:
                table_name = "social_accounts"
                exists = bool(conn.execute(
                    "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table_name,)
                ).fetchone())
                if not exists:
                    raise ValueError(f"required table not found: {table_name}")
                quick_rows = conn.execute("PRAGMA quick_check").fetchall()
                quick_check = [str(row[0]) for row in quick_rows]
                quick_ok = quick_check == ["ok"]
                total_all = int(conn.execute(
                    "SELECT COUNT(*) FROM social_accounts WHERE active=1 AND deleted_at IS NULL"
                ).fetchone()[0])
                if pattern:
                    total = int(conn.execute(
                        """
                        SELECT COUNT(*) FROM social_accounts
                        WHERE active=1 AND deleted_at IS NULL
                          AND username LIKE ? COLLATE NOCASE
                        """,
                        (pattern,),
                    ).fetchone()[0])
                else:
                    total = total_all
                return {
                    "ok": quick_ok,
                    "table_name": table_name,
                    "table_exists": exists,
                    "quick_check": quick_check,
                    "search_pattern": pattern,
                    "total": total,
                    "total_all": total_all,
                    "db_filename": self.db_path.name,
                }
            finally:
                conn.close()

    def account_posts(self, account_id: int, *, page: int = 1, page_size: int = 50, sort_by: str = "views") -> dict[str, Any]:
        with self._db_lock:
            return list_account_posts(
                self.db_path,
                account_id=account_id,
                page=page,
                page_size=page_size,
                sort_by=sort_by,
            )

    def post(self, post_id: int) -> dict[str, Any] | None:
        with self._db_lock:
            return get_post_detail(self.db_path, post_id)

    def post_stats(self, post_id: int, *, limit: int = 144) -> list[dict[str, Any]]:
        with self._db_lock:
            return get_post_stats(self.db_path, post_id, limit=limit)

    def media_asset(self, post_id: int, asset_id: int) -> dict[str, Any]:
        with self._db_lock:
            conn = connect_database(self.db_path)
            try:
                row = conn.execute(
                    """
                    SELECT ma.id, ma.post_id, ma.asset_kind, ma.media_index, ma.storage_root_id,
                           ma.folder_name, ma.file_name, ma.relative_path, ma.local_status,
                           sr.root_path
                    FROM media_assets ma
                    LEFT JOIN storage_roots sr ON sr.id=ma.storage_root_id
                    JOIN posts p ON p.id=ma.post_id
                    WHERE ma.id=? AND ma.post_id=? AND ma.local_status='AVAILABLE'
                      AND p.deleted_at IS NULL
                    """,
                    (int(asset_id), int(post_id)),
                ).fetchone()
                if not row:
                    raise ValueError(f"media asset not found: post_id={post_id}, asset_id={asset_id}")
                asset = dict(row)
                path = self._resolve_asset_path(asset)
                if not path.is_file():
                    raise FileNotFoundError(str(path))
                return {**asset, "path": path}
            finally:
                conn.close()

    def open_post_folder(self, post_id: int) -> dict[str, Any]:
        with self._db_lock:
            conn = connect_database(self.db_path)
            try:
                post = conn.execute(
                    "SELECT id, shortcode FROM posts WHERE id=? AND deleted_at IS NULL", (int(post_id),)
                ).fetchone()
                if not post:
                    raise ValueError(f"post_id not found: {post_id}")
                assets = self._post_asset_rows(conn, post_id)
            finally:
                conn.close()
            for asset in assets:
                path = self._resolve_asset_path(asset)
                folder = path.parent
                if folder.is_dir():
                    self._path_launcher(folder)
                    return {
                        "opened": True,
                        "post_id": int(post_id),
                        "shortcode": str(post["shortcode"]),
                        "folder_name": folder.name,
                    }
            raise FileNotFoundError(f"saved folder not found for post_id={post_id}")

    def save_observation(self, payload: dict[str, Any]) -> dict[str, Any]:
        with self._db_lock:
            return upsert_observation(
                self.db_path,
                username=str(payload.get("username") or payload.get("account") or ""),
                platform_account_id=str(payload.get("platform_account_id") or ""),
                display_name=str(payload.get("display_name") or ""),
                profile_url=str(payload.get("profile_url") or ""),
                shortcode=str(payload.get("shortcode") or payload.get("code") or ""),
                post_url=str(payload.get("post_url") or ""),
                caption=str(payload.get("caption") or ""),
                published_at=payload.get("published_at"),
                thumbnail_url=str(payload.get("thumbnail_url") or ""),
                video_url=str(payload.get("video_url") or ""),
                sort_by=str(payload.get("sort_by") or payload.get("sort_field") or ""),
                sort_value=int(payload.get("sort_value") or 0),
                source_kind=str(payload.get("source_kind") or "LOCAL"),
                views=int(payload.get("views") or payload.get("play_count") or 0),
                views_available=int(payload.get("views_available") or 0),
                likes=int(payload.get("likes") or payload.get("like_count") or 0),
                comments=int(payload.get("comments") or payload.get("comment_count") or 0),
                reposts=int(payload.get("reposts") or payload.get("media_repost_count") or 0),
                observed_at=payload.get("observed_at") or payload.get("collected_at") or payload.get("captured_at"),
            )

    def save_observations(self, payload: dict[str, Any]) -> dict[str, Any]:
        items = payload.get("items") if isinstance(payload, dict) else []
        if not isinstance(items, list):
            raise ValueError("items must be an array")
        if len(items) > 500:
            raise ValueError("items limit is 500")
        with self._db_lock:
            saved = []
            skipped = []
            failed = []
            for index, item in enumerate(items):
                if not isinstance(item, dict):
                    failed.append({"index": index, "error": "item must be an object"})
                    continue
                try:
                    result = self.save_observation(item)
                    if result.get("skipped_unchanged"):
                        skipped.append(result)
                    else:
                        saved.append(result)
                except Exception as exc:
                    failed.append({"index": index, "shortcode": str(item.get("shortcode") or item.get("code") or ""), "error": str(exc)})
            return {
                "ok": not failed,
                "saved_count": len(saved),
                "skipped_count": len(skipped),
                "failed_count": len(failed),
                "items": saved,
                "skipped_items": skipped,
                "failures": failed,
            }

    def database_files(self) -> dict[str, Any]:
        with self._db_lock:
            info = list_database_files(self.st_root)
            return {"ok": True, **info, "active_filename": self.db_path.name, "active_path": str(self.db_path)}

    def open_database(self, payload: dict[str, Any]) -> dict[str, Any]:
        if self._explicit_db_path is not None:
            raise ValueError("runtime database switching is disabled when --db is used")
        filename = str(payload.get("filename") or "") if isinstance(payload, dict) else ""
        with self._db_lock:
            result = open_database_file(self.st_root, filename)
            self.db_path = Path(result["db_path"]).resolve()
            return {"ok": True, "action": "open", **result, **self.collection_accounts()}

    def new_database(self, payload: dict[str, Any]) -> dict[str, Any]:
        if self._explicit_db_path is not None:
            raise ValueError("runtime database switching is disabled when --db is used")
        filename = str(payload.get("filename") or "") if isinstance(payload, dict) else ""
        with self._db_lock:
            result = create_database_file(self.st_root, filename)
            self.db_path = Path(result["db_path"]).resolve()
            return {"ok": True, "action": "new", **result, **self.collection_accounts()}

    def save_database_as(self, payload: dict[str, Any]) -> dict[str, Any]:
        if self._explicit_db_path is not None:
            raise ValueError("runtime database switching is disabled when --db is used")
        filename = str(payload.get("filename") or "") if isinstance(payload, dict) else ""
        with self._db_lock:
            result = save_database_as(self.st_root, self.db_path, filename)
            self.db_path = Path(result["db_path"]).resolve()
            return {"ok": True, "action": "save_as", **result, **self.collection_accounts()}

    def collection_accounts(self) -> dict[str, Any]:
        with self._db_lock:
            items = list_collection_accounts(self.db_path, include_deleted=True)
            return {"ok": True, "items": items, "total": len(items), "db_filename": self.db_path.name, "db_path": str(self.db_path)}

    def replace_collection_accounts(self, payload: dict[str, Any]) -> dict[str, Any]:
        items = payload.get("items") if isinstance(payload, dict) else []
        if not isinstance(items, list):
            raise ValueError("items must be an array")
        with self._db_lock:
            result = replace_collection_accounts(self.db_path, items)
            return {"ok": True, **result, "db_filename": self.db_path.name, "db_path": str(self.db_path)}

    def delete_account(self, account_id: int) -> dict[str, Any]:
        with self._db_lock:
            return soft_delete_account(self.db_path, account_id)

    def delete_post(self, post_id: int) -> dict[str, Any]:
        staged: list[tuple[Path, Path]] = []
        missing_files: list[str] = []
        with self._db_lock:
            conn = connect_database(self.db_path)
            committed = False
            try:
                conn.execute("BEGIN IMMEDIATE")
                post = conn.execute(
                    "SELECT id, shortcode FROM posts WHERE id=? AND deleted_at IS NULL", (int(post_id),)
                ).fetchone()
                if not post:
                    raise ValueError(f"post_id not found: {post_id}")
                assets = self._post_asset_rows(conn, post_id)
                unique_paths: dict[Path, dict[str, Any]] = {}
                for asset in assets:
                    path = self._resolve_asset_path(asset)
                    shared = int(conn.execute(
                        """
                        SELECT COUNT(*) FROM media_assets
                        WHERE post_id<>? AND local_status='AVAILABLE'
                          AND storage_root_id IS ? AND relative_path=?
                        """,
                        (int(post_id), asset.get("storage_root_id"), asset.get("relative_path")),
                    ).fetchone()[0])
                    if shared:
                        raise ValueError(f"shared media reference detected: {path.name}")
                    unique_paths[path] = asset
                for path in unique_paths:
                    if not path.exists():
                        missing_files.append(path.name)
                        continue
                    if not path.is_file():
                        raise ValueError(f"media path is not a file: {path}")
                    staged_path = path.with_name(f".{path.name}.analyze8780-delete-{uuid.uuid4().hex}")
                    path.replace(staged_path)
                    staged.append((path, staged_path))
                conn.execute("DELETE FROM deletion_events WHERE entity_type='POST' AND entity_local_id=?", (int(post_id),))
                conn.execute("DELETE FROM sync_queue WHERE entity_type='POST' AND entity_local_id=?", (int(post_id),))
                deleted = conn.execute("DELETE FROM posts WHERE id=?", (int(post_id),)).rowcount
                if deleted != 1:
                    raise RuntimeError(f"post delete failed: {post_id}")
                conn.commit()
                committed = True
            except Exception:
                if not committed:
                    conn.rollback()
                    for original, staged_path in reversed(staged):
                        if staged_path.exists() and not original.exists():
                            staged_path.replace(original)
                raise
            finally:
                conn.close()
        cleanup_failures: list[str] = []
        for _, staged_path in staged:
            try:
                staged_path.unlink()
            except Exception as exc:
                cleanup_failures.append(f"{staged_path.name}: {exc}")
        if cleanup_failures:
            raise RuntimeError("post DB delete completed but media cleanup failed: " + "; ".join(cleanup_failures))
        return {
            "post_id": int(post_id),
            "shortcode": str(post["shortcode"]),
            "deleted": True,
            "deleted_file_count": len(staged),
            "missing_files": missing_files,
        }

    def deleted_accounts(self) -> list[dict[str, Any]]:
        with self._db_lock:
            return list_deleted_accounts(self.db_path)
