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

import subprocess
import sys
import threading
import uuid
from pathlib import Path, PurePosixPath
from urllib.parse import quote, unquote
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,
    build_instagram_media_path,
    derive_stdown_root,
    IMAGE_EXTENSIONS,
    VIDEO_EXTENSIONS,
    safe_media_component,
)


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.stdown_root = derive_stdown_root(self.st_root).resolve()
        self.instagram_root = (self.stdown_root / "instagram").resolve()
        self._file_selector: Callable[[Path], None] = self._default_file_selector
        initialize_database(self.db_path, self.st_root)

    @staticmethod
    def _default_file_selector(path: Path) -> None:
        if sys.platform == "win32":
            subprocess.Popen(
                ["explorer.exe", f"/select,{path}"],
                stdout=subprocess.DEVNULL,
                stderr=subprocess.DEVNULL,
            )
            return
        folder = path.parent
        command = ["open", str(folder)] if sys.platform == "darwin" else ["xdg-open", str(folder)]
        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.297",
                "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()

    @staticmethod
    def _virtual_component(value: object) -> str:
        return quote(str(value or ""), safe="")

    def _standard_media_files(self, account_name: object, shortcode: object) -> list[dict[str, Any]]:
        account = safe_media_component(account_name, "instagram")
        code = safe_media_component(shortcode, "post")
        files: list[dict[str, Any]] = []
        for asset_kind, extensions in (("IMAGE", IMAGE_EXTENSIONS), ("VIDEO", VIDEO_EXTENSIONS)):
            for extension in extensions:
                path = build_instagram_media_path(self.st_root, account, code, extension).resolve()
                if not path.is_file() or path.stat().st_size <= 0:
                    continue
                relative_path = PurePosixPath("instagram", account, path.name).as_posix()
                files.append({
                    "asset_kind": asset_kind,
                    "file_name": path.name,
                    "extension": path.suffix.lower(),
                    "folder_name": account,
                    "relative_path": relative_path,
                    "absolute_path": str(path),
                    "virtual_url": "/_StDown/" + "/".join(
                        self._virtual_component(part) for part in PurePosixPath(relative_path).parts
                    ),
                })
        return files

    def resolve_stdown_virtual_path(self, virtual_path: object) -> Path:
        decoded = unquote(str(virtual_path or "").split("?", 1)[0]).replace("\\", "/")
        raw_parts = [part for part in decoded.strip("/").split("/") if part]
        if len(raw_parts) != 4 or raw_parts[0] != "_StDown" or raw_parts[1].lower() != "instagram":
            raise ValueError("invalid Instagram virtual path")
        account, filename = raw_parts[2], raw_parts[3]
        if account != safe_media_component(account, "") or not account:
            raise ValueError("invalid Instagram account path")
        file_path = Path(filename)
        if file_path.name != filename or not file_path.stem:
            raise ValueError("invalid Instagram media filename")
        if file_path.stem != safe_media_component(file_path.stem, ""):
            raise ValueError("invalid Instagram shortcode filename")
        if file_path.suffix.lower() not in set(IMAGE_EXTENSIONS) | set(VIDEO_EXTENSIONS):
            raise ValueError("unsupported Instagram media extension")
        target = (self.instagram_root / account / filename).resolve()
        if not self._is_relative_to(target, self.instagram_root):
            raise ValueError("Instagram media path escaped _StDown root")
        if not target.is_file():
            raise FileNotFoundError(str(target))
        return target

    def open_local_media_folder(self, virtual_path: object) -> dict[str, Any]:
        path = self.resolve_stdown_virtual_path(virtual_path)
        self._file_selector(path)
        return {
            "opened": True,
            "platform": "instagram",
            "folder_name": path.parent.name,
            "file_name": path.name,
            "absolute_path": str(path),
            "virtual_url": str(virtual_path),
        }

    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:
            conn = connect_database(self.db_path)
            try:
                account = conn.execute(
                    "SELECT username FROM social_accounts WHERE id=? AND deleted_at IS NULL",
                    (int(account_id),),
                ).fetchone()
            finally:
                conn.close()
            if not account:
                raise ValueError(f"account_id not found: {account_id}")
            username = str(account["username"] or "")
            result = list_account_posts(
                self.db_path,
                account_id=account_id,
                page=page,
                page_size=page_size,
                sort_by=sort_by,
                include_media_assets=False,
            )
            for item in result.get("items") or []:
                for legacy_key in (
                    "media_assets", "folder_name", "file_name", "relative_path", "has_jpg", "has_mp4"
                ):
                    item.pop(legacy_key, None)
                media_files = self._standard_media_files(username, item.get("shortcode"))
                item["account_name"] = username
                item["media_files"] = media_files
                item["has_image"] = int(any(file["asset_kind"] == "IMAGE" for file in media_files))
                item["has_video"] = int(any(file["asset_kind"] == "VIDEO" for file in media_files))
            return result

    def post(self, post_id: int) -> dict[str, Any] | None:
        with self._db_lock:
            item = get_post_detail(self.db_path, post_id, include_media_assets=False)
            if not item:
                return None
            item.pop("media_assets", None)
            username = str(item.get("username") or "")
            item["account_name"] = username
            item["media_files"] = self._standard_media_files(username, item.get("shortcode"))
            return item

    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 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 p.id, p.shortcode, a.username
                    FROM posts p
                    JOIN social_accounts a ON a.id=p.account_id
                    WHERE p.id=? AND p.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 media_file in self._standard_media_files(post["username"], post["shortcode"]):
                    unique_paths.setdefault(Path(media_file["absolute_path"]).resolve(), {})
                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)