"""All ORM models for Sightline. Kept in one module to keep relationships coherent

and avoid circular imports in a scaffold of this size."""

from __future__ import annotations

import uuid

from datetime import datetime

from sqlalchemy import (

    BigInteger, Boolean, CheckConstraint, Date, DateTime, Float, ForeignKey,

    Index, Integer, JSON, LargeBinary, Numeric, String, Text, UniqueConstraint, func,

)

from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base import Base, TimestampMixin

def _uuid() -> str:

    return str(uuid.uuid4())

class Role(Base, TimestampMixin):

    __tablename__ = "roles"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    name: Mapped[str] = mapped_column(String, unique=True, nullable=False)

    permissions: Mapped[dict] = mapped_column(JSON, default=dict)

class User(Base, TimestampMixin):

    __tablename__ = "users"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    email: Mapped[str] = mapped_column(String, unique=True, nullable=False, index=True)

    hashed_password: Mapped[str] = mapped_column(String, nullable=False)

    full_name: Mapped[str | None] = mapped_column(String)

    role: Mapped[str] = mapped_column(String, default="analyst", nullable=False)

    is_active: Mapped[bool] = mapped_column(Boolean, default=True)

class ApiKey(Base, TimestampMixin):

    __tablename__ = "api_keys"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    user_id: Mapped[str] = mapped_column(ForeignKey("users.id"))

    name: Mapped[str] = mapped_column(String)

    key_hash: Mapped[str] = mapped_column(String, index=True)

    scopes: Mapped[dict] = mapped_column(JSON, default=dict)

    last_used_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

class AuditLog(Base):

    __tablename__ = "audit_logs"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    actor_user_id: Mapped[str | None] = mapped_column(String, index=True)

    action: Mapped[str] = mapped_column(String, nullable=False)

    entity_type: Mapped[str | None] = mapped_column(String)

    entity_id: Mapped[str | None] = mapped_column(String)

    ip: Mapped[str | None] = mapped_column(String)

    meta: Mapped[dict] = mapped_column("metadata", JSON, default=dict)

    created_at: Mapped[datetime] = mapped_column(

        DateTime(timezone=True), server_default=func.now(), index=True

    )

    __table_args__ = (Index("ix_audit_entity", "entity_type", "entity_id"),)

class Source(Base, TimestampMixin):

    __tablename__ = "sources"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    name: Mapped[str] = mapped_column(String, nullable=False)

    system_type: Mapped[str | None] = mapped_column(String)

    organization: Mapped[str | None] = mapped_column(String)

    jurisdiction: Mapped[str | None] = mapped_column(String)

    trust_level: Mapped[float] = mapped_column(Float, default=0.8)

class Import(Base, TimestampMixin):

    __tablename__ = "imports"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    source_id: Mapped[str | None] = mapped_column(ForeignKey("sources.id"))

    filename: Mapped[str] = mapped_column(String)

    content_type: Mapped[str | None] = mapped_column(String)

    sha256: Mapped[str] = mapped_column(String, unique=True, index=True)

    raw_blob_ref: Mapped[str | None] = mapped_column(String)

    row_count: Mapped[int] = mapped_column(Integer, default=0)

    status: Mapped[str] = mapped_column(String, default="received")

    error: Mapped[str | None] = mapped_column(Text)

    created_by: Mapped[str | None] = mapped_column(String)

class Location(Base, TimestampMixin):

    __tablename__ = "locations"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    label: Mapped[str | None] = mapped_column(String)

    address: Mapped[str | None] = mapped_column(String)

    lat: Mapped[float | None] = mapped_column(Float)

    lon: Mapped[float | None] = mapped_column(Float)

    jurisdiction: Mapped[str | None] = mapped_column(String)

    __table_args__ = (Index("ix_locations_latlon", "lat", "lon"),)

class CameraGroup(Base, TimestampMixin):

    __tablename__ = "camera_groups"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    name: Mapped[str] = mapped_column(String, nullable=False)

    organization: Mapped[str | None] = mapped_column(String)

    description: Mapped[str | None] = mapped_column(Text)

class Camera(Base, TimestampMixin):

    __tablename__ = "cameras"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    source_id: Mapped[str | None] = mapped_column(ForeignKey("sources.id"))

    external_camera_id: Mapped[str] = mapped_column(String, index=True)

    name: Mapped[str | None] = mapped_column(String)

    direction_facing: Mapped[str | None] = mapped_column(String)

    operator: Mapped[str | None] = mapped_column(String, index=True)

    manufacturer: Mapped[str | None] = mapped_column(String)

    zone: Mapped[str | None] = mapped_column(String)

    state: Mapped[str | None] = mapped_column(String, index=True)

    osm_id: Mapped[str | None] = mapped_column(String)

    image_url: Mapped[str | None] = mapped_column(String)

    has_live_feed: Mapped[bool] = mapped_column(Boolean, default=False, index=True)

    live_source: Mapped[str | None] = mapped_column(String)

    area: Mapped[str | None] = mapped_column(String)

    location_id: Mapped[str | None] = mapped_column(ForeignKey("locations.id"))

    camera_group_id: Mapped[str | None] = mapped_column(ForeignKey("camera_groups.id"))

    meta: Mapped[dict] = mapped_column("metadata", JSON, default=dict)

    location: Mapped[Location | None] = relationship()

    __table_args__ = (UniqueConstraint("source_id", "external_camera_id", name="uq_camera_source_extid"),)

class LicensePlate(Base, TimestampMixin):

    __tablename__ = "license_plates"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    plate_raw: Mapped[str] = mapped_column(String)

    plate_norm: Mapped[str] = mapped_column(String, index=True, nullable=False)

    state: Mapped[str | None] = mapped_column(String)

    plate_type: Mapped[str | None] = mapped_column(String)

    __table_args__ = (UniqueConstraint("plate_norm", "state", name="uq_plate_norm_state"),)

class Vehicle(Base, TimestampMixin):

    __tablename__ = "vehicles"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    primary_plate_id: Mapped[str | None] = mapped_column(ForeignKey("license_plates.id"))

    make: Mapped[str | None] = mapped_column(String)

    model: Mapped[str | None] = mapped_column(String)

    color: Mapped[str | None] = mapped_column(String)

    body_type: Mapped[str | None] = mapped_column(String)

    year: Mapped[int | None] = mapped_column(Integer)

    notes: Mapped[str | None] = mapped_column(Text)

    possible_duplicate_of: Mapped[str | None] = mapped_column(ForeignKey("vehicles.id"))

class PlateRead(Base):

    __tablename__ = "plate_reads"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    import_id: Mapped[str | None] = mapped_column(ForeignKey("imports.id"))

    source_id: Mapped[str | None] = mapped_column(ForeignKey("sources.id"))

    camera_id: Mapped[str | None] = mapped_column(ForeignKey("cameras.id"), index=True)

    plate_id: Mapped[str | None] = mapped_column(ForeignKey("license_plates.id"))

    plate_norm: Mapped[str] = mapped_column(String, index=True, nullable=False)

    read_ts: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True, nullable=False)

    direction: Mapped[str | None] = mapped_column(String)

    confidence: Mapped[float | None] = mapped_column(Numeric(4, 3))

    lane: Mapped[str | None] = mapped_column(String)

    lat: Mapped[float | None] = mapped_column(Float)

    lon: Mapped[float | None] = mapped_column(Float)

    hotlist_hit: Mapped[bool] = mapped_column(Boolean, default=False)

    incident_ref: Mapped[str | None] = mapped_column(String)

    burst_count: Mapped[int] = mapped_column(Integer, default=1)

    raw_read: Mapped[dict] = mapped_column(JSON, default=dict)

    dedup_hash: Mapped[bytes] = mapped_column(LargeBinary, unique=True)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    __table_args__ = (Index("ix_reads_ts_camera", "read_ts", "camera_id"),)

class Sighting(Base):

    __tablename__ = "sightings"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    plate_read_id: Mapped[int | None] = mapped_column(ForeignKey("plate_reads.id"))

    vehicle_id: Mapped[str | None] = mapped_column(ForeignKey("vehicles.id"), index=True)

    camera_id: Mapped[str | None] = mapped_column(ForeignKey("cameras.id"))

    location_id: Mapped[str | None] = mapped_column(ForeignKey("locations.id"))

    investigation_id: Mapped[str | None] = mapped_column(ForeignKey("investigations.id"), index=True)

    read_ts: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)

    confidence: Mapped[float | None] = mapped_column(Numeric(4, 3))

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    __table_args__ = (Index("ix_sightings_vehicle_ts", "vehicle_id", "read_ts"),)

class Person(Base, TimestampMixin):

    __tablename__ = "persons"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    full_name: Mapped[str] = mapped_column(String, index=True, nullable=False)

    aliases: Mapped[list | None] = mapped_column(JSON, default=list)

    dob: Mapped[str | None] = mapped_column(String)

    sex: Mapped[str | None] = mapped_column(String)

    height_cm: Mapped[int | None] = mapped_column(Integer)

    build: Mapped[str | None] = mapped_column(String)

    hair: Mapped[str | None] = mapped_column(String)

    eyes: Mapped[str | None] = mapped_column(String)

    distinguishing_marks: Mapped[str | None] = mapped_column(Text)

    associated_plate: Mapped[str | None] = mapped_column(String, index=True)

    associated_plates: Mapped[list | None] = mapped_column(JSON, default=list)

    threat_level: Mapped[str] = mapped_column(String, default="none")

    wanted: Mapped[bool] = mapped_column(Boolean, default=False)

    consented: Mapped[bool] = mapped_column(Boolean, default=False, index=True)

    notes: Mapped[str | None] = mapped_column(Text)

class AppSetting(Base):

    """Mutable runtime settings (singleton-ish key/value). Used for the profile-engine scope

    switch so an operator can lock the dossier builder to consented subjects only."""

    __tablename__ = "app_settings"

    key: Mapped[str] = mapped_column(String, primary_key=True)

    value: Mapped[dict] = mapped_column(JSON, default=dict)

class Detection(Base):

    """A person/occupant observation at a camera, carrying soft-biometric and clothing

    descriptors - supports search by name, outfit, or article of clothing. (In a real deployment

    these come from video analytics; in the POC they are synthetic but realistically structured.)"""

    __tablename__ = "detections"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    person_id: Mapped[str | None] = mapped_column(ForeignKey("persons.id"), index=True)

    camera_id: Mapped[str | None] = mapped_column(ForeignKey("cameras.id"), index=True)

    sighting_id: Mapped[int | None] = mapped_column(ForeignKey("sightings.id"))

    read_ts: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)

    plate_norm: Mapped[str | None] = mapped_column(String, index=True)

    occupant_role: Mapped[str | None] = mapped_column(String)

    clothing_top: Mapped[str | None] = mapped_column(String, index=True)

    clothing_bottom: Mapped[str | None] = mapped_column(String)

    articles: Mapped[list | None] = mapped_column(JSON, default=list)

    description: Mapped[str | None] = mapped_column(Text)

    confidence: Mapped[float | None] = mapped_column(Numeric(4, 3))

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

class Investigation(Base, TimestampMixin):

    __tablename__ = "investigations"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    title: Mapped[str] = mapped_column(String, nullable=False)

    status: Mapped[str] = mapped_column(String, default="open")

    priority: Mapped[str] = mapped_column(String, default="normal")

    summary: Mapped[str | None] = mapped_column(Text)

    lead_user_id: Mapped[str | None] = mapped_column(String)

    organization: Mapped[str | None] = mapped_column(String)

class Target(Base, TimestampMixin):

    __tablename__ = "targets"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str] = mapped_column(ForeignKey("investigations.id"))

    kind: Mapped[str] = mapped_column(String)

    label: Mapped[str] = mapped_column(String)

    plate_id: Mapped[str | None] = mapped_column(ForeignKey("license_plates.id"))

    vehicle_id: Mapped[str | None] = mapped_column(ForeignKey("vehicles.id"))

    osint_entity_id: Mapped[str | None] = mapped_column(ForeignKey("osint_entities.id"))

    notes: Mapped[str | None] = mapped_column(Text)

class Incident(Base, TimestampMixin):

    __tablename__ = "incidents"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    external_ref: Mapped[str | None] = mapped_column(String)

    title: Mapped[str] = mapped_column(String)

    kind: Mapped[str | None] = mapped_column(String)

    occurred_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    location_id: Mapped[str | None] = mapped_column(ForeignKey("locations.id"))

    description: Mapped[str | None] = mapped_column(Text)

class Note(Base, TimestampMixin):

    __tablename__ = "notes"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str] = mapped_column(ForeignKey("investigations.id"))

    author_id: Mapped[str | None] = mapped_column(String)

    body: Mapped[str] = mapped_column(Text)

    pinned: Mapped[bool] = mapped_column(Boolean, default=False)

class Tag(Base, TimestampMixin):

    __tablename__ = "tags"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    name: Mapped[str] = mapped_column(String, unique=True)

    color: Mapped[str | None] = mapped_column(String)

class Tagging(Base):

    __tablename__ = "taggings"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    tag_id: Mapped[str] = mapped_column(ForeignKey("tags.id"))

    entity_type: Mapped[str] = mapped_column(String)

    entity_id: Mapped[str] = mapped_column(String)

    __table_args__ = (Index("ix_taggings_entity", "entity_type", "entity_id"),)

class EvidenceFile(Base, TimestampMixin):

    __tablename__ = "evidence_files"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str] = mapped_column(ForeignKey("investigations.id"))

    filename: Mapped[str] = mapped_column(String)

    content_type: Mapped[str | None] = mapped_column(String)

    size_bytes: Mapped[int] = mapped_column(Integer, default=0)

    sha256: Mapped[str] = mapped_column(String, index=True)

    blob_ref: Mapped[str | None] = mapped_column(String)

    source_id: Mapped[str | None] = mapped_column(ForeignKey("sources.id"))

    description: Mapped[str | None] = mapped_column(Text)

    uploaded_by: Mapped[str | None] = mapped_column(String)

class Watchlist(Base, TimestampMixin):

    __tablename__ = "watchlists"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    name: Mapped[str] = mapped_column(String, nullable=False)

    organization: Mapped[str | None] = mapped_column(String)

    kind: Mapped[str] = mapped_column(String, default="plate")

    active: Mapped[bool] = mapped_column(Boolean, default=True)

    created_by: Mapped[str | None] = mapped_column(String)

class WatchlistEntry(Base, TimestampMixin):

    __tablename__ = "watchlist_entries"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    watchlist_id: Mapped[str] = mapped_column(ForeignKey("watchlists.id"))

    plate_norm: Mapped[str | None] = mapped_column(String, index=True)

    partial_plate: Mapped[str | None] = mapped_column(String)

    vehicle_desc: Mapped[dict | None] = mapped_column(JSON)

    reason: Mapped[str | None] = mapped_column(Text)

    expires_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

class WatchlistHit(Base):

    __tablename__ = "watchlist_hits"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    watchlist_entry_id: Mapped[str] = mapped_column(ForeignKey("watchlist_entries.id"))

    plate_read_id: Mapped[int | None] = mapped_column(ForeignKey("plate_reads.id"))

    sighting_id: Mapped[int | None] = mapped_column(ForeignKey("sightings.id"))

    matched_on: Mapped[str] = mapped_column(String)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)

class AlertRule(Base, TimestampMixin):

    __tablename__ = "alert_rules"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    watchlist_id: Mapped[str | None] = mapped_column(ForeignKey("watchlists.id"))

    condition: Mapped[dict] = mapped_column(JSON, default=dict)

    channel: Mapped[str] = mapped_column(String, default="in_app")

    throttle_seconds: Mapped[int] = mapped_column(Integer, default=0)

    active: Mapped[bool] = mapped_column(Boolean, default=True)

class Alert(Base):

    __tablename__ = "alerts"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    alert_rule_id: Mapped[str | None] = mapped_column(ForeignKey("alert_rules.id"))

    watchlist_hit_id: Mapped[int | None] = mapped_column(ForeignKey("watchlist_hits.id"))

    status: Mapped[str] = mapped_column(String, default="new")

    payload: Mapped[dict] = mapped_column(JSON, default=dict)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now(), index=True)

class OsintEntity(Base, TimestampMixin):

    __tablename__ = "osint_entities"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    kind: Mapped[str] = mapped_column(String)

    value: Mapped[str] = mapped_column(String)

    attributes: Mapped[dict] = mapped_column(JSON, default=dict)

    first_seen: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    last_seen: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    __table_args__ = (UniqueConstraint("kind", "value", name="uq_osint_kind_value"),)

class EntityRelationship(Base):

    __tablename__ = "entity_relationships"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    src_type: Mapped[str] = mapped_column(String)

    src_id: Mapped[str] = mapped_column(String)

    dst_type: Mapped[str] = mapped_column(String)

    dst_id: Mapped[str] = mapped_column(String)

    relation: Mapped[str] = mapped_column(String)

    weight: Mapped[float] = mapped_column(Float, default=1.0)

    confidence: Mapped[float | None] = mapped_column(Float)

    evidence_ref: Mapped[str | None] = mapped_column(String)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    __table_args__ = (

        Index("ix_rel_src", "src_type", "src_id"),

        Index("ix_rel_dst", "dst_type", "dst_id"),

    )

class EnrichmentJob(Base):

    __tablename__ = "enrichment_jobs"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str | None] = mapped_column(ForeignKey("investigations.id"))

    enricher: Mapped[str] = mapped_column(String)

    target: Mapped[str] = mapped_column(String)

    status: Mapped[str] = mapped_column(String, default="pending")

    result: Mapped[dict | None] = mapped_column(JSON)

    confidence: Mapped[float | None] = mapped_column(Float)

    raw_response_ref: Mapped[str | None] = mapped_column(String)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

class SensorAsset(Base):

    """A real, publicly-sourced fixed asset: ALPR/traffic/surveillance camera, weather station,

    radar site, seismic station, stream gauge, air-quality monitor, etc. Sourced from public

    APIs (OSM/DeFlock, NWS, USGS, ...) - every row is real and carries its provenance."""

    __tablename__ = "sensor_assets"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    kind: Mapped[str] = mapped_column(String, index=True)

    category: Mapped[str] = mapped_column(String, index=True)

    source: Mapped[str] = mapped_column(String, index=True)

    external_id: Mapped[str | None] = mapped_column(String, index=True)

    name: Mapped[str | None] = mapped_column(String)

    operator: Mapped[str | None] = mapped_column(String, index=True)

    lat: Mapped[float | None] = mapped_column(Float)

    lon: Mapped[float | None] = mapped_column(Float)

    state: Mapped[str | None] = mapped_column(String, index=True)

    attributes: Mapped[dict] = mapped_column(JSON, default=dict)

    last_seen: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

    __table_args__ = (Index("ix_assets_kind_state", "kind", "state"),

                      Index("ix_assets_latlon", "lat", "lon"))

class LiveEntity(Base):

    """Latest snapshot of a real moving public entity (aircraft via ADS-B, satellite via TLE,

    vessel via AIS). Upserted by (source, identifier) as the live feed polls."""

    __tablename__ = "live_entities"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    kind: Mapped[str] = mapped_column(String, index=True)

    source: Mapped[str] = mapped_column(String, index=True)

    identifier: Mapped[str] = mapped_column(String, index=True)

    label: Mapped[str | None] = mapped_column(String)

    lat: Mapped[float | None] = mapped_column(Float)

    lon: Mapped[float | None] = mapped_column(Float)

    altitude_m: Mapped[float | None] = mapped_column(Float)

    heading: Mapped[float | None] = mapped_column(Float)

    velocity: Mapped[float | None] = mapped_column(Float)

    origin_country: Mapped[str | None] = mapped_column(String, index=True)

    attributes: Mapped[dict] = mapped_column(JSON, default=dict)

    observed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)

    __table_args__ = (UniqueConstraint("source", "identifier", name="uq_entity_source_ident"),)

class EntityPosition(Base):

    """Append-only position history for moving entities - builds the track/trail for any entity."""

    __tablename__ = "entity_positions"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)

    identifier: Mapped[str] = mapped_column(String, index=True)

    kind: Mapped[str] = mapped_column(String)

    lat: Mapped[float] = mapped_column(Float)

    lon: Mapped[float] = mapped_column(Float)

    altitude_m: Mapped[float | None] = mapped_column(Float)

    observed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)

    __table_args__ = (Index("ix_pos_ident_ts", "identifier", "observed_at"),)

class Report(Base):

    __tablename__ = "reports"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str] = mapped_column(ForeignKey("investigations.id"))

    title: Mapped[str] = mapped_column(String)

    format: Mapped[str] = mapped_column(String, default="markdown")

    sections: Mapped[dict] = mapped_column(JSON, default=dict)

    rendered_ref: Mapped[str | None] = mapped_column(String)

    created_by: Mapped[str | None] = mapped_column(String)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

class Export(Base):

    __tablename__ = "exports"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)

    investigation_id: Mapped[str | None] = mapped_column(ForeignKey("investigations.id"))

    kind: Mapped[str] = mapped_column(String)

    format: Mapped[str] = mapped_column(String)

    filters: Mapped[dict] = mapped_column(JSON, default=dict)

    file_ref: Mapped[str | None] = mapped_column(String)

    created_by: Mapped[str | None] = mapped_column(String)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())