from dataclasses import dataclass
from contextlib  import contextmanager
from typing import Optional, Any
import sqlite3
import datetime

@dataclass(frozen=True)
class nodes:
    ID       = "id"
    NAME     = "name"
    PATH     = "path"
    UUID     = "uuid"

@dataclass(frozen=True)
class connections:
    ID       = "id"
    SRC_ID   = "src_id"
    DST_ID   = "dst_id"
    SRC_ATTR = "src_attr"
    DST_ATTR = "dst_attr"

@dataclass(frozen=True)
class system_requirement:
    ID       = "id"
    TARGET   = "target"

@dataclass(frozen=True)
class sessions:
    ID         = "id"
    SCENE_PATH = "scene_path"
    SAVED_AT   = "saved_at"

class SQLmanager:
    def __init__(self, DB_PATH: str = ":memory:"):
        self.DB_PATH = DB_PATH

        self.conn:   sqlite3.Connection = sqlite3.connect(self.DB_PATH)
        self.cursor: sqlite3.Cursor     = self.conn.cursor()

        self.init_db()

        saved_at = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        self.cursor.execute(
            f"""
            INSERT INTO {sessions.__name__}(
                {sessions.SCENE_PATH},
                {sessions.SAVED_AT}
            )VALUES (?, ?)""",
            (self.DB_PATH, saved_at)
        )
        self.conn.commit()

        session_id = self.cursor.lastrowid
        print(f"[DB] セッションID: {session_id}  シーン: {self.DB_PATH}")

    def close(self) -> None:
        """接続を明示的にクローズする。with 文または明示呼び出しで使用を推奨"""
        if(hasattr(self, "conn") and self.conn):
            self.conn.close()
            self.conn = None

    def __enter__(self) -> "SQLmanager":
        return self

    def __exit__(self, exc_type, exc_val, exc_tb) -> None:
        self.close()

    @contextmanager
    def _connect(self):
        """
        ファイルDBの場合のみ一時接続を提供。
        :memory: の場合は永続接続(self.conn)をそのまま yield する
        """
        if(self.DB_PATH == ":memory:"):
            yield self.conn
        else:
            conn = sqlite3.connect(self.DB_PATH)
            conn.row_factory = sqlite3.Row
            try:
                yield conn
            except Exception:
                conn.rollback()
                raise
            finally:
                conn.close()

    def init_db(self) -> None:
        """テーブルが存在しない場合に全テーブルを作成する。"""
        ddl_list = [
            f"""
            CREATE TABLE IF NOT EXISTS {nodes.__name__} (
                {nodes.ID}   INTEGER PRIMARY KEY AUTOINCREMENT,
                {nodes.NAME} TEXT,
                {nodes.PATH} TEXT,
                {nodes.UUID} TEXT
            )
            """,
            f"""
            CREATE TABLE IF NOT EXISTS {connections.__name__} (
                {connections.ID}       INTEGER PRIMARY KEY AUTOINCREMENT,
                {connections.SRC_ID}   INTEGER NOT NULL,
                {connections.DST_ID}   INTEGER NOT NULL,
                {connections.SRC_ATTR} TEXT,
                {connections.DST_ATTR} TEXT,
                FOREIGN KEY ({connections.SRC_ID}) REFERENCES {nodes.__name__}({nodes.ID}),
                FOREIGN KEY ({connections.DST_ID}) REFERENCES {nodes.__name__}({nodes.ID})
            )
            """,
            f"""
            CREATE TABLE IF NOT EXISTS {system_requirement.__name__} (
                {system_requirement.ID}     INTEGER PRIMARY KEY AUTOINCREMENT,
                {system_requirement.TARGET} INTEGER NOT NULL,
                FOREIGN KEY ({system_requirement.TARGET}) REFERENCES {nodes.__name__}({nodes.ID})
            )
            """,
            f"""
            CREATE TABLE IF NOT EXISTS {sessions.__name__} (
                {sessions.ID}         INTEGER PRIMARY KEY AUTOINCREMENT,
                {sessions.SCENE_PATH} TEXT,
                {sessions.SAVED_AT}   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """,
        ]
        with self._connect() as conn:
            for ddl in ddl_list:
                conn.execute(ddl)
            conn.commit()


class NodeRepository:
    """
    nodes テーブルに対する CRUD 操作をまとめたクラス。

    SQLmanager インスタンスを受け取り、その接続(_connect)を介して
    SQL を発行する。SQLmanager 自体のテーブル定義やセッション管理には
    一切手を加えない。
    """

    def __init__(self, db: SQLmanager):
        self.db = db

    # ------------------------------------------------------------------
    # CREATE
    # ------------------------------------------------------------------
    def create(self, name: str, path: Optional[str] = None,
               node_uuid: Optional[str] = None) -> int:
        """
        ノードを1件追加する。

        Args:
            name: ノード名
            path: ノードのパス（任意）
            node_uuid: UUID（任意）

        Returns:
            追加されたノードの id（rowid）
        """
        sql = f"""
            INSERT INTO {nodes.__name__} (
                {nodes.NAME},
                {nodes.PATH},
                {nodes.UUID}
            ) VALUES (?, ?, ?)
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (name, path, node_uuid))
            conn.commit()
            return cur.lastrowid

    # ------------------------------------------------------------------
    # READ
    # ------------------------------------------------------------------
    def get_by_id(self, node_id: int) -> Optional[dict[str, Any]]:
        """id を指定して1件取得する。存在しなければ None"""
        sql = f"""
            SELECT {nodes.ID}, {nodes.NAME}, {nodes.PATH}, {nodes.UUID}
            FROM {nodes.__name__}
            WHERE {nodes.ID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (node_id,))
            row = cur.fetchone()
            return self._row_to_dict(row)

    def get_by_uuid(self, node_uuid: str) -> Optional[dict[str, Any]]:
        """uuid を指定して1件取得する。存在しなければ None。"""
        sql = f"""
            SELECT {nodes.ID}, {nodes.NAME}, {nodes.PATH}, {nodes.UUID}
            FROM {nodes.__name__}
            WHERE {nodes.UUID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (node_uuid,))
            row = cur.fetchone()
            return self._row_to_dict(row)

    def list_all(self) -> list[dict[str, Any]]:
        """全ノードを取得する。"""
        sql = f"""
            SELECT {nodes.ID}, {nodes.NAME}, {nodes.PATH}, {nodes.UUID}
            FROM {nodes.__name__}
            ORDER BY {nodes.ID}
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql)
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    def find_by_name(self, name: str) -> list[dict[str, Any]]:
        """name で部分一致検索する。"""
        sql = f"""
            SELECT {nodes.ID}, {nodes.NAME}, {nodes.PATH}, {nodes.UUID}
            FROM {nodes.__name__}
            WHERE {nodes.NAME} LIKE ?
            ORDER BY {nodes.ID}
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (f"%{name}%",))
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    # ------------------------------------------------------------------
    # UPDATE
    # ------------------------------------------------------------------
    def update(self, node_id: int, name: Optional[str] = None,
               path: Optional[str] = None,
               node_uuid: Optional[str] = None) -> bool:
        """
        指定 id のノードを更新する。値が None の項目は更新しない。

        Returns:
            1件以上更新されたら True、対象が無ければ False
        """
        fields = []
        params: list[Any] = []

        if(name is not None):
            fields.append(f"{nodes.NAME} = ?")
            params.append(name)
        if(path is not None):
            fields.append(f"{nodes.PATH} = ?")
            params.append(path)
        if(node_uuid is not None):
            fields.append(f"{nodes.UUID} = ?")
            params.append(node_uuid)

        if(not fields):
            return False

        sql = f"""
            UPDATE {nodes.__name__}
            SET {", ".join(fields)}
            WHERE {nodes.ID} = ?
        """
        params.append(node_id)

        with self.db._connect() as conn:
            cur = conn.execute(sql, params)
            conn.commit()
            return cur.rowcount > 0

    # ------------------------------------------------------------------
    # DELETE
    # ------------------------------------------------------------------
    def delete(self, node_id: int) -> bool:
        """
        指定 id のノードを削除する。

        Returns:
            削除できたら True、対象が無ければ False
        """
        sql = f"""
            DELETE FROM {nodes.__name__}
            WHERE {nodes.ID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (node_id,))
            conn.commit()
            return cur.rowcount > 0

    def delete_all(self) -> int:
        """全ノードを削除する。削除件数を返す。"""
        sql = f"DELETE FROM {nodes.__name__}"
        with self.db._connect() as conn:
            cur = conn.execute(sql)
            conn.commit()
            return cur.rowcount

    # ------------------------------------------------------------------
    # internal helper
    # ------------------------------------------------------------------
    @staticmethod
    def _row_to_dict(row) -> Optional[dict[str, Any]]:
        """
        sqlite3.Row / tuple のどちらが来ても dict に変換する。
        (:memory: 接続では sqlite3.Row でなく tuple が返るため両対応)
        """
        if(row is None):
            return None
        if(isinstance(row, sqlite3.Row)):
            return dict(row)
        keys = [nodes.ID, nodes.NAME, nodes.PATH, nodes.UUID]
        return dict(zip(keys, row))


class NodeNotFoundError(Exception):
    """指定されたノードIDが nodes テーブルに存在しない場合に発生。"""
    pass


class ConnectionRepository:
    """
    connections テーブルに対する CRUD 操作をまとめたクラス。

    SQLmanager インスタンスを受け取り、その接続(_connect)を介して
    SQL を発行。src_id / dst_id の存在チェックには NodeRepository を使用。
    """

    def __init__(self, db: SQLmanager):
        self.db = db
        self.node_repo = NodeRepository(db)

    # ------------------------------------------------------------------
    # CREATE
    # ------------------------------------------------------------------
    def create(self, src_id: int, dst_id: int,
               src_attr: Optional[str] = None,
               dst_attr: Optional[str] = None,
               validate_nodes: bool = True) -> int:
        """
        ノード間の接続を1件追加する。

        Args:
            src_id  : 接続元ノードID (nodes.id)
            dst_id  : 接続先ノードID (nodes.id)
            src_attr: 接続元側の属性名（任意。例: "output"）
            dst_attr: 接続先側の属性名（任意。例: "input"）
            validate_nodes: True の場合、src_id / dst_id が
                nodes テーブルに実在するかを事前確認。

        Returns:
            追加された接続の id（rowid）

        Raises:
            NodeNotFoundError: validate_nodes=True で
                src_id または dst_id が nodes に存在しない場合
        """
        if(validate_nodes):
            self._assert_node_exists(src_id, role="src_id")
            self._assert_node_exists(dst_id, role="dst_id")

        sql = f"""
            INSERT INTO {connections.__name__} (
                {connections.SRC_ID},
                {connections.DST_ID},
                {connections.SRC_ATTR},
                {connections.DST_ATTR}
            ) VALUES (?, ?, ?, ?)
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (src_id, dst_id, src_attr, dst_attr))
            conn.commit()
            return cur.lastrowid

    # ------------------------------------------------------------------
    # READ
    # ------------------------------------------------------------------
    def get_by_id(self, connection_id: int) -> Optional[dict[str, Any]]:
        """id を指定して1件取得。存在しなければ None"""
        sql = f"""
            SELECT {connections.ID}, {connections.SRC_ID}, {connections.DST_ID},
                   {connections.SRC_ATTR}, {connections.DST_ATTR}
            FROM {connections.__name__}
            WHERE {connections.ID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (connection_id,))
            row = cur.fetchone()
            return self._row_to_dict(row)

    def list_all(self) -> list[dict[str, Any]]:
        """全接続を取得"""
        sql = f"""
            SELECT {connections.ID}, {connections.SRC_ID}, {connections.DST_ID}, {connections.SRC_ATTR}, {connections.DST_ATTR}
            FROM {connections.__name__}
            ORDER BY {connections.ID}
        """
        with self.db._connect() as conn:
            cur  = conn.execute(sql)
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    def list_by_src(self, src_id: int) -> list[dict[str, Any]]:
        """指定ノードを接続元(src)とする接続を全て取得"""
        sql = f"""
            SELECT {connections.ID}, {connections.SRC_ID}, {connections.DST_ID}, {connections.SRC_ATTR}, {connections.DST_ATTR}
            FROM {connections.__name__}
            WHERE {connections.SRC_ID} = ?
            ORDER BY {connections.ID}
        """
        with self.db._connect() as conn:
            cur  = conn.execute(sql, (src_id,))
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    def list_by_dst(self, dst_id: int) -> list[dict[str, Any]]:
        """指定ノードを接続先(dst)とする接続を全て取得"""
        sql = f"""
            SELECT {connections.ID}, {connections.SRC_ID}, {connections.DST_ID}, {connections.SRC_ATTR}, {connections.DST_ATTR}
            FROM {connections.__name__}
            WHERE {connections.DST_ID} = ?
            ORDER BY {connections.ID}
        """
        with self.db._connect() as conn:
            cur  = conn.execute(sql, (dst_id,))
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    def list_by_node(self, node_id: int) -> list[dict[str, Any]]:
        """指定ノードが src・dst いずれかに含まれる接続を全て取得"""
        sql = f"""
            SELECT {connections.ID}, {connections.SRC_ID}, {connections.DST_ID},{connections.SRC_ATTR}, {connections.DST_ATTR}
            FROM {connections.__name__}
            WHERE {connections.SRC_ID} = ? OR {connections.DST_ID} = ?
            ORDER BY {connections.ID}
        """
        with self.db._connect() as conn:
            cur  = conn.execute(sql, (node_id, node_id))
            rows = cur.fetchall()
            return [self._row_to_dict(r) for r in rows]

    # ------------------------------------------------------------------
    # UPDATE
    # ------------------------------------------------------------------
    def update(self, connection_id: int,
               src_id: Optional[int]   = None,
               dst_id: Optional[int]   = None,
               src_attr: Optional[str] = None,
               dst_attr: Optional[str] = None,
               validate_nodes: bool = True) -> bool:
        """
        指定 id の接続を更新する。値が None の項目は更新しない

        Returns:
            1件以上更新されたら True、対象が無ければ False
        """
        if(validate_nodes):
            if(src_id is not None):
                self._assert_node_exists(src_id, role="src_id")
            if(dst_id is not None):
                self._assert_node_exists(dst_id, role="dst_id")

        fields = []
        params: list[Any] = []

        if(src_id is not None):
            fields.append(f"{connections.SRC_ID} = ?")
            params.append(src_id)
        if(dst_id is not None):
            fields.append(f"{connections.DST_ID} = ?")
            params.append(dst_id)
        if(src_attr is not None):
            fields.append(f"{connections.SRC_ATTR} = ?")
            params.append(src_attr)
        if(dst_attr is not None):
            fields.append(f"{connections.DST_ATTR} = ?")
            params.append(dst_attr)

        if(not fields):
            return False

        sql = f"""
            UPDATE {connections.__name__}
            SET {", ".join(fields)}
            WHERE {connections.ID} = ?
        """
        params.append(connection_id)

        with self.db._connect() as conn:
            cur = conn.execute(sql, params)
            conn.commit()
            return cur.rowcount > 0

    # ------------------------------------------------------------------
    # DELETE
    # ------------------------------------------------------------------
    def delete(self, connection_id: int) -> bool:
        """
        指定 id の接続を削除

        Returns:
            削除できたら True、対象が無ければ False
        """
        sql = f"""
            DELETE FROM {connections.__name__}
            WHERE {connections.ID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (connection_id,))
            conn.commit()
            return cur.rowcount > 0

    def delete_by_node(self, node_id: int) -> int:
        """
        指定ノードが src・dst いずれかに含まれる接続を全て削除する
        ノード削除前のクリーンアップ用途を想定

        Returns:
            削除件数
        """
        sql = f"""
            DELETE FROM {connections.__name__}
            WHERE {connections.SRC_ID} = ? OR {connections.DST_ID} = ?
        """
        with self.db._connect() as conn:
            cur = conn.execute(sql, (node_id, node_id))
            conn.commit()
            return cur.rowcount

    def delete_all(self) -> int:
        """全接続を削除する。削除件数を返す。"""
        sql = f"DELETE FROM {connections.__name__}"
        with self.db._connect() as conn:
            cur = conn.execute(sql)
            conn.commit()
            return cur.rowcount

    # ------------------------------------------------------------------
    # internal helper
    # ------------------------------------------------------------------
    def _assert_node_exists(self, node_id: int, role: str) -> None:
        """node_id が nodes テーブルに存在しなければ NodeNotFoundError を投げる。"""
        if(self.node_repo.get_by_id(node_id) is None):
            raise NodeNotFoundError(
                f"{role}={node_id} は nodes テーブルに存在しません。"
            )

    @staticmethod
    def _row_to_dict(row) -> Optional[dict[str, Any]]:
        """
        sqlite3.Row / tuple のどちらが来ても dict に変換する。
        (:memory: 接続では sqlite3.Row でなく tuple が返るため両対応)
        """
        if(row is None):
            return None
        if(isinstance(row, sqlite3.Row)):
            return dict(row)
        keys = [connections.ID, connections.SRC_ID, connections.DST_ID,
                connections.SRC_ATTR, connections.DST_ATTR]
        return dict(zip(keys, row))


if(__name__ == "__main__"):
    with SQLmanager() as db:
        node_repo = NodeRepository(db)
        conn_repo = ConnectionRepository(db)

        print("======================================")
        print("NodeRepository")
        print("======================================")
        cube_id   = node_repo.create(name="Cube",   path="/Scene/Cube")
        sphere_id = node_repo.create(name="Sphere", path="/Scene/Sphere")
        print("レコード作成\t{} {}".format(cube_id, sphere_id))
        print("全件\t{}".format(node_repo.list_all()))

        print("\n======================================")
        print("ConnectionRepository")
        print("======================================")
        print("全件\t{}".format(conn_repo.list_all()))
        print("\n---- READ ---")
        edge_id = conn_repo.create(
            src_id=cube_id, dst_id=sphere_id,
            src_attr="output", dst_attr="input"
        )
        print("接続作成 >> {}".format(edge_id))

        print("\n---- READ ---")
        print("全件\n\t{}".format(conn_repo.list_all()))
        print("ID指定\n\t{}".format(conn_repo.get_by_id(edge_id)))
        print("Cubeを起点とする接続\n\t{}".format(conn_repo.list_by_src(cube_id)))
        print("Sphereに関連する接続\n\t{}".format(conn_repo.list_by_node(sphere_id)))

        print("\n---- UPDATE ---")
        conn_repo.update(edge_id, dst_attr="input_color")
        print("更新後:\n\t{}".format(conn_repo.get_by_id(edge_id)))

        try:
            conn_repo.create(src_id=cube_id, dst_id=9999)
        except Exception as e:
            print("想定済みの例外:", e)

        print("\n---- DELETE ---")
        conn_repo.delete(edge_id)
        print("削除後の全件:\n\t{}".format(conn_repo.list_all()))