from pathlib import Path
import unittest


class BaselineSchemaTests(unittest.TestCase):
    def test_baseline_schema_defines_seven_tables(self):
        migration_dir = Path(__file__).resolve().parents[1] / "supabase" / "migrations"
        # Pin to the authoritative baseline file by name. Later additive
        # migrations (e.g. auth/RBAC) sort after it, so migrations[-1] would
        # otherwise point at the wrong file and break this assertion.
        baseline = migration_dir / "20260616_baseline_schema.sql"
        self.assertTrue(baseline.exists(), "Expected the baseline schema migration to exist")

        sql = baseline.read_text(encoding="utf-8").lower()

        expected_tables = [
            "profiles",
            "submissions",
            "reviews",
            "feedback",
            "pipeline_steps",
            "provider_calls",
            "evidence_items",
        ]
        for table in expected_tables:
            self.assertIn(f"create table if not exists public.{table}", sql)
            self.assertIn(f"alter table public.{table} enable row level security", sql)

        # The usage rollup is a view, not a table.
        self.assertIn("create or replace view public.submission_usage_summary", sql)
        self.assertNotIn("create table if not exists public.submission_usage_summary", sql)

        # Tables we deliberately dropped during consolidation.
        for table in ("artifacts", "events", "pipeline_runs"):
            self.assertIn(f"drop table if exists public.{table}", sql)
            self.assertNotIn(f"create table if not exists public.{table}", sql)

        # Identifier cleanup: single user-facing key, no vestigial submission_key.
        self.assertIn("rename column secret_key to access_key", sql)
        self.assertIn("drop column if exists submission_key", sql)
        self.assertNotIn("submission_key text", sql)

        # New columns required by the redesign.
        self.assertIn("parsed_markdown_text", sql)       # markdown text queryable in DB
        self.assertIn("output_json", sql)                # canonical per-step output
        # output_summary was dropped (redundant with output_json — see
        # 20260623_drop_unused_columns.sql); the baseline must no longer create it.
        self.assertNotIn("output_summary", sql)

        # All nine pipeline step names remain in the pipeline_steps CHECK.
        for step_name in [
            "pdf_to_markdown",
            "extract_paper_title",
            "extract_contributions",
            "extract_research_topic",
            "generate_search_queries",
            "search_related_papers",
            "fetch_paper_metadata",
            "summarize_related_research",
            "generate_review",
        ]:
            self.assertIn(step_name, sql)

        self.assertNotIn("conversation", sql)


class AuthRbacMigrationTests(unittest.TestCase):
    def _sql(self):
        path = (
            Path(__file__).resolve().parents[1]
            / "supabase" / "migrations" / "20260620_auth_rbac.sql"
        )
        self.assertTrue(path.exists(), "Expected the auth/RBAC migration to exist")
        return path.read_text(encoding="utf-8").lower()

    def test_adds_rbac_and_auth_link_columns(self):
        sql = self._sql()
        self.assertIn("add column if not exists role text", sql)
        self.assertIn("add column if not exists auth_user_id uuid", sql)
        self.assertIn("add column if not exists is_active boolean", sql)
        self.assertIn("references auth.users(id)", sql)

    def test_creates_trigger_helpers_and_views(self):
        sql = self._sql()
        self.assertIn("function public.handle_new_user", sql)
        self.assertIn("on auth.users", sql)
        self.assertIn("function public.is_admin", sql)
        self.assertIn("function public.current_profile_id", sql)
        self.assertIn("create or replace view public.user_usage_summary", sql)
        self.assertIn("create or replace view public.admin_overview", sql)
        self.assertIn("create or replace view public.admin_cost_by_day", sql)

    def test_is_additive_only_on_profiles(self):
        # Must never drop/rename a profiles column — that would orphan the
        # anonymous submissions FK (submissions.profile_id -> profiles.id).
        sql = self._sql()
        self.assertNotIn("drop column", sql)
        self.assertNotIn("alter table public.profiles rename column", sql)