"""
FreeExile Social Repository: SQLite Persistence Layer for Friends & Social Engine.
Enforces normalized player friendships, bidirectional blocklist, and indexing.
"""

from __future__ import annotations
import os
import sqlite3
import time
from typing import Any, Dict, List, Optional
from server.social.social_types import FriendshipStatus


class SocialRepository:
    """SQLite repository managing player friendships, pending requests, and block lists."""

    def __init__(self, db_path: str = "data/social_store.db") -> None:
        self._db_path = db_path
        self._persistent_conn: Optional[sqlite3.Connection] = None
        self._ensure_storage_dir()
        self._init_db()

    def _ensure_storage_dir(self) -> None:
        """Creates target directory for SQLite database if not in-memory."""
        if self._db_path != ":memory:":
            dirname = os.path.dirname(self._db_path)
            if dirname:
                os.makedirs(dirname, exist_ok=True)

    def _get_connection(self) -> sqlite3.Connection:
        """Returns SQLite connection with row factory and WAL journal mode."""
        if self._db_path == ":memory:":
            if self._persistent_conn is None:
                self._persistent_conn = sqlite3.connect(":memory:")
                self._persistent_conn.row_factory = sqlite3.Row
                self._persistent_conn.execute("PRAGMA foreign_keys = ON;")
            return self._persistent_conn

        conn = sqlite3.connect(self._db_path)
        conn.row_factory = sqlite3.Row
        conn.execute("PRAGMA foreign_keys = ON;")
        conn.execute("PRAGMA journal_mode = WAL;")
        return conn

    def _init_db(self) -> None:
        """Initializes tables and indices if not present."""
        conn = self._get_connection()
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS player_friendships (
                user_id TEXT NOT NULL,
                friend_id TEXT NOT NULL,
                status TEXT NOT NULL,
                friend_note TEXT NOT NULL DEFAULT '',
                created_at REAL NOT NULL,
                updated_at REAL NOT NULL,
                PRIMARY KEY (user_id, friend_id)
            );
            CREATE INDEX IF NOT EXISTS idx_friendships_user_status ON player_friendships(user_id, status);
            CREATE INDEX IF NOT EXISTS idx_friendships_friend_status ON player_friendships(friend_id, status);
            """
        )
        conn.commit()

    def is_blocked(self, user_id: str, friend_id: str) -> bool:
        """Checks mutual block: True if either user has blocked the other."""
        if not user_id or not friend_id:
            return False
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT 1 FROM player_friendships WHERE (user_id = ? AND friend_id = ? AND status = ?) "
            "OR (user_id = ? AND friend_id = ? AND status = ?) LIMIT 1",
            (user_id, friend_id, FriendshipStatus.BLOCKED.value, friend_id, user_id, FriendshipStatus.BLOCKED.value),
        )
        return cur.fetchone() is not None

    def is_friend(self, user_id: str, friend_id: str) -> bool:
        """Returns True if both users mutually accepted friendship and neither is blocked."""
        if not user_id or not friend_id or user_id == friend_id or self.is_blocked(user_id, friend_id):
            return False
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT 1 FROM player_friendships WHERE user_id = ? AND friend_id = ? AND status = ? LIMIT 1",
            (user_id, friend_id, FriendshipStatus.ACCEPTED.value),
        )
        return cur.fetchone() is not None

    def add_friend_request(self, user_id: str, friend_id: str) -> bool:
        """Sends an outgoing friend request from user_id to friend_id."""
        if not user_id or not friend_id or user_id == friend_id:
            return False
        if self.is_blocked(user_id, friend_id) or self.is_friend(user_id, friend_id):
            return False

        now = time.time()
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute("SELECT status FROM player_friendships WHERE user_id = ? AND friend_id = ?", (user_id, friend_id))
        existing = cur.fetchone()
        if existing and existing["status"] in (FriendshipStatus.PENDING_SENT.value, FriendshipStatus.ACCEPTED.value):
            return False

        cur.execute(
            "INSERT OR REPLACE INTO player_friendships (user_id, friend_id, status, friend_note, created_at, updated_at) "
            "VALUES (?, ?, ?, COALESCE((SELECT friend_note FROM player_friendships WHERE user_id = ? AND friend_id = ?), ''), ?, ?)",
            (user_id, friend_id, FriendshipStatus.PENDING_SENT.value, user_id, friend_id, now, now),
        )
        cur.execute(
            "INSERT OR REPLACE INTO player_friendships (user_id, friend_id, status, friend_note, created_at, updated_at) "
            "VALUES (?, ?, ?, COALESCE((SELECT friend_note FROM player_friendships WHERE user_id = ? AND friend_id = ?), ''), ?, ?)",
            (friend_id, user_id, FriendshipStatus.PENDING_RECEIVED.value, friend_id, user_id, now, now),
        )
        conn.commit()
        return True

    def accept_friend_request(self, user_id: str, friend_id: str) -> bool:
        """Accepts a pending incoming request for user_id from friend_id."""
        if not user_id or not friend_id or self.is_blocked(user_id, friend_id):
            return False
        now = time.time()
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute("SELECT status FROM player_friendships WHERE user_id = ? AND friend_id = ?", (user_id, friend_id))
        row = cur.fetchone()
        if not row or row["status"] != FriendshipStatus.PENDING_RECEIVED.value:
            return False

        cur.execute(
            "UPDATE player_friendships SET status = ?, updated_at = ? WHERE user_id = ? AND friend_id = ?",
            (FriendshipStatus.ACCEPTED.value, now, user_id, friend_id),
        )
        cur.execute(
            "UPDATE player_friendships SET status = ?, updated_at = ? WHERE user_id = ? AND friend_id = ?",
            (FriendshipStatus.ACCEPTED.value, now, friend_id, user_id),
        )
        conn.commit()
        return True

    def decline_friend_request(self, user_id: str, friend_id: str) -> bool:
        """Declines an incoming friend request from friend_id."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute("SELECT status FROM player_friendships WHERE user_id = ? AND friend_id = ?", (user_id, friend_id))
        row = cur.fetchone()
        if not row or row["status"] != FriendshipStatus.PENDING_RECEIVED.value:
            return False

        cur.execute(
            "DELETE FROM player_friendships WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)",
            (user_id, friend_id, friend_id, user_id),
        )
        conn.commit()
        return True

    def remove_friend(self, user_id: str, friend_id: str) -> bool:
        """Removes an accepted friend relationship between user_id and friend_id."""
        if not self.is_friend(user_id, friend_id):
            return False
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "DELETE FROM player_friendships WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)",
            (user_id, friend_id, friend_id, user_id),
        )
        conn.commit()
        return True

    def block_player(self, user_id: str, target_id: str) -> bool:
        """Blocks target_id by user_id and wipes any existing non-blocked relationship."""
        if not user_id or not target_id or user_id == target_id:
            return False
        now = time.time()
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute("SELECT status FROM player_friendships WHERE user_id = ? AND friend_id = ?", (target_id, user_id))
        target_rel = cur.fetchone()
        if target_rel and target_rel["status"] != FriendshipStatus.BLOCKED.value:
            cur.execute("DELETE FROM player_friendships WHERE user_id = ? AND friend_id = ?", (target_id, user_id))

        cur.execute(
            "INSERT OR REPLACE INTO player_friendships (user_id, friend_id, status, friend_note, created_at, updated_at) "
            "VALUES (?, ?, ?, '', ?, ?)",
            (user_id, target_id, FriendshipStatus.BLOCKED.value, now, now),
        )
        conn.commit()
        return True

    def unblock_player(self, user_id: str, target_id: str) -> bool:
        """Removes target_id from user_id's block list."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT 1 FROM player_friendships WHERE user_id = ? AND friend_id = ? AND status = ?",
            (user_id, target_id, FriendshipStatus.BLOCKED.value),
        )
        if not cur.fetchone():
            return False
        cur.execute(
            "DELETE FROM player_friendships WHERE user_id = ? AND friend_id = ? AND status = ?",
            (user_id, target_id, FriendshipStatus.BLOCKED.value),
        )
        conn.commit()
        return True

    def set_friend_note(self, user_id: str, friend_id: str, note: str) -> bool:
        """Sets a personal note for an accepted friend (capped at 64 chars)."""
        if not self.is_friend(user_id, friend_id):
            return False
        clean_note = (note or "")[:64]
        now = time.time()
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "UPDATE player_friendships SET friend_note = ?, updated_at = ? WHERE user_id = ? AND friend_id = ? AND status = ?",
            (clean_note, now, user_id, friend_id, FriendshipStatus.ACCEPTED.value),
        )
        conn.commit()
        return cur.rowcount > 0

    def get_friend_note(self, user_id: str, friend_id: str) -> str:
        """Retrieves stored note for friend_id from user_id's perspective."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT friend_note FROM player_friendships WHERE user_id = ? AND friend_id = ? AND status = ?",
            (user_id, friend_id, FriendshipStatus.ACCEPTED.value),
        )
        row = cur.fetchone()
        return str(row["friend_note"]) if row and row["friend_note"] else ""

    def get_friends(self, user_id: str) -> List[Dict[str, Any]]:
        """Returns all accepted friends with metadata, excluding blocked users."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT friend_id, friend_note, created_at, updated_at FROM player_friendships "
            "WHERE user_id = ? AND status = ? ORDER BY created_at ASC",
            (user_id, FriendshipStatus.ACCEPTED.value),
        )
        rows = cur.fetchall()
        friends: List[Dict[str, Any]] = []
        for row in rows:
            fid = str(row["friend_id"])
            if not self.is_blocked(user_id, fid):
                friends.append({
                    "friend_id": fid,
                    "friend_note": str(row["friend_note"]),
                    "created_at": float(row["created_at"]),
                    "updated_at": float(row["updated_at"]),
                })
        return friends

    def get_blocked_list(self, user_id: str) -> List[str]:
        """Returns list of player IDs blocked by user_id."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT friend_id FROM player_friendships WHERE user_id = ? AND status = ? ORDER BY created_at ASC",
            (user_id, FriendshipStatus.BLOCKED.value),
        )
        return [str(row["friend_id"]) for row in cur.fetchall()]

    def get_pending_requests(self, user_id: str) -> List[str]:
        """Returns player IDs that sent pending friend requests to user_id."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT friend_id FROM player_friendships WHERE user_id = ? AND status = ? ORDER BY created_at ASC",
            (user_id, FriendshipStatus.PENDING_RECEIVED.value),
        )
        results = []
        for row in cur.fetchall():
            sender_id = str(row["friend_id"])
            if not self.is_blocked(user_id, sender_id):
                results.append(sender_id)
        return results

    def get_sent_requests(self, user_id: str) -> List[str]:
        """Returns player IDs to whom user_id sent friend requests."""
        conn = self._get_connection()
        cur = conn.cursor()
        cur.execute(
            "SELECT friend_id FROM player_friendships WHERE user_id = ? AND status = ? ORDER BY created_at ASC",
            (user_id, FriendshipStatus.PENDING_SENT.value),
        )
        return [str(row["friend_id"]) for row in cur.fetchall()]