from __future__ import annotations

import re
from pathlib import Path

ROOT = Path(__file__).resolve().parents[3]
MIGRATION_PATH = ROOT / "backend/migrations/versions/0003_document_ingestion.py"
MIGRATION = MIGRATION_PATH.read_text(encoding="utf-8")
NORMALIZED = " ".join(MIGRATION.lower().split())


def _table(name: str) -> str:
    pattern = re.compile(rf"(?ms)create table rag\.{re.escape(name)} \((.*?)^        \);?")
    match = pattern.search(MIGRATION.lower())
    assert match is not None, f"missing table rag.{name}"
    return " ".join(match.group(1).split())


def test_revision_is_additive_after_model_profiles() -> None:
    assert 'revision: str = "0003_document_ingestion"' in MIGRATION
    assert 'down_revision: str | none = "0002_model_profiles"' in MIGRATION.lower()
    assert "alter table rag.documents" not in NORMALIZED
    assert "alter table rag.chunks" not in NORMALIZED
    assert "drop table if exists rag.documents" not in NORMALIZED
    assert "drop table if exists rag.background_jobs" not in NORMALIZED


def test_uploads_bind_actor_scope_idempotency_content_and_completion() -> None:
    table = _table("document_uploads")

    for column in (
        "actor_subject text not null",
        "knowledge_base_id uuid not null",
        "access_scope_id uuid not null",
        "idempotency_key_hash char(64) not null",
        "request_fingerprint char(64) not null",
        "expected_size bigint not null",
        "expected_sha256 char(64) not null",
        "storage_key uuid not null",
        "status text not null default 'created'",
        "document_id uuid",
        "parse_job_id uuid",
    ):
        assert column in table
    assert "foreign key (knowledge_base_id, access_scope_id)" in table
    assert "references rag.access_scopes (knowledge_base_id, id)" in table
    assert "unique (actor_subject, idempotency_key_hash)" in table
    assert "unique (storage_key)" in table
    assert "status in ('created', 'stored', 'completed')" in table
    assert "actual_size = expected_size" in table
    assert "actual_sha256 = expected_sha256" in table
    assert "status = 'completed'" in table
    assert "document_id is not null" in table
    assert "parse_job_id is not null" in table
    assert "completed_at is not null" in table
    assert "original_filename !~ '[/\\\\\\\\]'" in table


def test_upload_audit_is_append_only_metadata_without_sensitive_fields() -> None:
    table = _table("document_upload_events")

    assert "upload_id uuid not null" in table
    assert "actor_subject text not null" in table
    assert "trace_id uuid not null" in table
    assert "event_type in ('created', 'stored', 'completed')" in table
    assert "jsonb_typeof(metadata) = 'object'" in table
    assert "metadata_has_no_credentials" in table
    for forbidden in (
        "api[_-]?key",
        "secret",
        "password",
        "token",
        "authorization",
        "credential",
        "storage[_-]?key",
        "path",
    ):
        assert forbidden in table


def test_pages_and_blocks_preserve_version_source_anchors() -> None:
    pages = _table("document_pages")
    blocks = _table("document_blocks")

    assert "foreign key (document_version_id)" in pages
    assert "references rag.document_versions (id) on delete cascade" in pages
    assert "unique (document_version_id, ordinal)" in pages
    assert "page_number is null or page_number > 0" in pages
    assert "text_sha256 ~ '^[0-9a-f]{64}$'" in pages
    assert "line_start > 0 and line_end >= line_start" in pages

    assert "foreign key (document_version_id, page_id)" in blocks
    assert "references rag.document_pages (document_version_id, id)" in blocks
    assert "block_kind in ('heading', 'paragraph', 'table_row')" in blocks
    assert "jsonb_typeof(section_path) = 'array'" in blocks
    assert "anchor_id ~ '^[0-9a-f]{64}$'" in blocks
    assert "normalized_text_sha256 ~ '^[0-9a-f]{64}$'" in blocks
    assert "char_end > char_start" in blocks
    assert "line_end >= line_start" in blocks
    assert "unique (document_version_id, anchor_id)" in blocks


def test_downgrade_drops_only_new_dependents_in_safe_order() -> None:
    block = NORMALIZED.index("drop table if exists rag.document_blocks")
    page = NORMALIZED.index("drop table if exists rag.document_pages")
    event = NORMALIZED.index("drop table if exists rag.document_upload_events")
    upload = NORMALIZED.index("drop table if exists rag.document_uploads")

    assert block < page < event < upload
    assert "drop table if exists rag.documents" not in NORMALIZED
    assert "drop table if exists rag.document_versions" not in NORMALIZED