"""U-001 tests: all 15 tables build, and the constraints named in the Unit's
Done criterion (unique / foreign key / enum) are actually enforced.

Run: python3 -m unittest src.test_schema -v
"""

import sqlite3
import unittest

from src import schema


class SchemaCreationTest(unittest.TestCase):
    def setUp(self):
        self.conn = schema.connect(":memory:")
        schema.create_schema(self.conn)

    def tearDown(self):
        self.conn.close()

    def test_all_15_tables_created(self):
        rows = self.conn.execute(
            "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
        ).fetchall()
        actual = {row[0] for row in rows}
        self.assertEqual(actual, set(schema.TABLE_NAMES))
        self.assertEqual(len(schema.TABLE_NAMES), 15)

    def test_schema_is_idempotent_to_reopen_on_disk(self):
        # not required by the Done criterion, but a schema that only works once
        # against :memory: would be a trap for every later Unit that opens a
        # real db file -- cheap to check now.
        import os
        import tempfile

        fd, path = tempfile.mkstemp(suffix=".sqlite3")
        os.close(fd)
        os.remove(path)
        try:
            conn = schema.connect(path)
            schema.create_schema(conn)
            conn.close()
            conn2 = schema.connect(path)
            rows = conn2.execute(
                "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%'"
            ).fetchall()
            conn2.close()
            self.assertEqual({r[0] for r in rows}, set(schema.TABLE_NAMES))
        finally:
            if os.path.exists(path):
                os.remove(path)


class ConstraintTestBase(unittest.TestCase):
    def setUp(self):
        self.conn = schema.connect(":memory:")
        schema.create_schema(self.conn)

    def tearDown(self):
        self.conn.close()

    def insert_project(self, project_id="proj-1", target_folder_path="/tmp/proj-1"):
        self.conn.execute(
            "INSERT INTO project (project_id, name, target_folder_path, has_git_repo, status) "
            "VALUES (?, ?, ?, 1, 'active')",
            (project_id, "Project One", target_folder_path),
        )
        self.conn.commit()
        return project_id

    def insert_task(self, task_id="task-1", project_id="proj-1", status="queued"):
        self.conn.execute(
            "INSERT INTO task (task_id, project_id, goal_text, task_type, status, assigned_org_node) "
            "VALUES (?, ?, 'implement snake game', 'goal', ?, 'planner')",
            (task_id, project_id, status),
        )
        self.conn.commit()
        return task_id

    def insert_skill(self, skill_id="skill-1"):
        self.conn.execute(
            "INSERT INTO skill (skill_id, owner_org_node, file_path, current_git_commit_sha, source, status) "
            "VALUES (?, 'coder', 'skills/skill-1/', 'abc123', 'new', 'active')",
            (skill_id,),
        )
        self.conn.commit()
        return skill_id

    def insert_work_graph_run(self, run_id="run-1", task_id="task-1", org_node="coder", status="planning"):
        self.conn.execute(
            "INSERT INTO work_graph_run (run_id, task_id, org_node, graph_manifest, status) "
            "VALUES (?, ?, ?, '{}', ?)",
            (run_id, task_id, org_node, status),
        )
        self.conn.commit()
        return run_id


class ProjectUniqueConstraintTest(ConstraintTestBase):
    def test_duplicate_target_folder_path_rejected(self):
        self.insert_project("proj-1", "/tmp/shared-path")
        with self.assertRaises(sqlite3.IntegrityError):
            self.conn.execute(
                "INSERT INTO project (project_id, name, target_folder_path, has_git_repo, status) "
                "VALUES ('proj-2', 'Project Two', '/tmp/shared-path', 1, 'active')"
            )

    def test_has_git_repo_boolean_check_rejects_out_of_range_value(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.conn.execute(
                "INSERT INTO project (project_id, name, target_folder_path, has_git_repo, status) "
                "VALUES ('proj-1', 'P', '/tmp/p', 2, 'active')"
            )


class WorkNodeExecutionEnumConstraintTest(ConstraintTestBase):
    def setUp(self):
        super().setUp()
        self.insert_project()
        self.insert_task()
        self.insert_skill()
        self.insert_work_graph_run()

    def _insert_node_exec(self, status):
        self.conn.execute(
            "INSERT INTO work_node_execution "
            "(node_exec_id, run_id, node_id, subagent_type, skill_ref, "
            " input_schema_ref, output_schema_ref, input_payload, status) "
            "VALUES ('node-1', 'run-1', 'n1', 'coder', 'skill-1', 'in.json', 'out.json', '{}', ?)",
            (status,),
        )

    def test_valid_status_accepted(self):
        self._insert_node_exec("waiting_for_quota")
        self.conn.commit()
        row = self.conn.execute(
            "SELECT status FROM work_node_execution WHERE node_exec_id = 'node-1'"
        ).fetchone()
        self.assertEqual(row[0], "waiting_for_quota")

    def test_invalid_status_rejected(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self._insert_node_exec("not_a_real_status")


class ForeignKeyEnforcementTest(ConstraintTestBase):
    def test_task_rejects_unknown_project_id(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self.insert_task(task_id="task-1", project_id="does-not-exist")

    def test_work_node_execution_rejects_unknown_skill_ref(self):
        self.insert_project()
        self.insert_task()
        self.insert_work_graph_run()
        with self.assertRaises(sqlite3.IntegrityError):
            self.conn.execute(
                "INSERT INTO work_node_execution "
                "(node_exec_id, run_id, node_id, subagent_type, skill_ref, "
                " input_schema_ref, output_schema_ref, input_payload, status) "
                "VALUES ('node-1', 'run-1', 'n1', 'coder', 'no-such-skill', 'in.json', 'out.json', '{}', 'pending')"
            )


class WorkGraphRunActiveUniqueIndexTest(ConstraintTestBase):
    """01-M 1.3: only one active (planning/running/merging/verifying) run per
    (task_id, org_node) at a time -- terminal-status runs don't count."""

    def setUp(self):
        super().setUp()
        self.insert_project()
        self.insert_task()

    def test_second_active_run_for_same_task_and_org_node_rejected(self):
        self.insert_work_graph_run("run-1", "task-1", "coder", status="running")
        with self.assertRaises(sqlite3.IntegrityError):
            self.insert_work_graph_run("run-2", "task-1", "coder", status="planning")

    def test_second_run_allowed_once_first_is_terminal(self):
        self.insert_work_graph_run("run-1", "task-1", "coder", status="done")
        self.insert_work_graph_run("run-2", "task-1", "coder", status="planning")
        count = self.conn.execute(
            "SELECT COUNT(*) FROM work_graph_run WHERE task_id = 'task-1' AND org_node = 'coder'"
        ).fetchone()[0]
        self.assertEqual(count, 2)


class ArtifactUniqueConstraintTest(ConstraintTestBase):
    def setUp(self):
        super().setUp()
        self.insert_project()
        self.insert_task()
        self.insert_work_graph_run()

    def _insert_artifact(self, artifact_id):
        self.conn.execute(
            "INSERT INTO artifact (artifact_id, artifact_type, task_id, produced_by_run_id, "
            "file_path, git_commit_sha) VALUES (?, 'sdd', 'task-1', 'run-1', 'sdd.md', 'sha-1')",
            (artifact_id,),
        )

    def test_duplicate_task_type_commit_rejected(self):
        self._insert_artifact("artifact-1")
        self.conn.commit()
        with self.assertRaises(sqlite3.IntegrityError):
            self._insert_artifact("artifact-2")


class ExecutionLogFileUniqueConstraintTest(ConstraintTestBase):
    def setUp(self):
        super().setUp()
        self.insert_project()
        self.insert_task()
        self.insert_skill()
        self.insert_work_graph_run()
        self.conn.execute(
            "INSERT INTO work_node_execution "
            "(node_exec_id, run_id, node_id, subagent_type, skill_ref, "
            " input_schema_ref, output_schema_ref, input_payload, status) "
            "VALUES ('node-1', 'run-1', 'n1', 'coder', 'skill-1', 'in.json', 'out.json', '{}', 'running')"
        )
        self.conn.commit()

    def test_second_log_file_for_same_node_exec_rejected(self):
        self.conn.execute(
            "INSERT INTO execution_log_file (log_file_id, node_exec_id, project_id, file_path, format, status) "
            "VALUES ('log-1', 'node-1', 'proj-1', '/logs/node-1.jsonl', 'jsonl', 'active')"
        )
        self.conn.commit()
        with self.assertRaises(sqlite3.IntegrityError):
            self.conn.execute(
                "INSERT INTO execution_log_file (log_file_id, node_exec_id, project_id, file_path, format, status) "
                "VALUES ('log-2', 'node-1', 'proj-1', '/logs/node-1-again.jsonl', 'jsonl', 'active')"
            )


class BugRecordRootCauseCategoryTest(ConstraintTestBase):
    """core_engine is PH5-scoped (U-034); U-001 deliberately does not accept it yet
    (confirmed with user, 2026-07-30) -- this test locks that decision in so a
    future edit can't silently widen the enum back out ahead of U-034."""

    def _insert_bug(self, root_cause_category):
        self.conn.execute(
            "INSERT INTO bug_record (bug_id, source, description, root_cause_category, status) "
            "VALUES ('bug-1', 'validator', 'wall collision misjudged', ?, 'open')",
            (root_cause_category,),
        )

    def test_pre_ph5_categories_accepted(self):
        for category in ("planning", "coder", "skill_defect"):
            with self.subTest(category=category):
                conn = schema.connect(":memory:")
                schema.create_schema(conn)
                conn.execute(
                    "INSERT INTO bug_record (bug_id, source, description, root_cause_category, status) "
                    "VALUES ('bug-1', 'validator', 'x', ?, 'open')",
                    (category,),
                )
                conn.commit()
                conn.close()

    def test_core_engine_rejected_before_u034(self):
        with self.assertRaises(sqlite3.IntegrityError):
            self._insert_bug("core_engine")


if __name__ == "__main__":
    unittest.main()