"""init schema

Revision ID: 0001
Revises:
Create Date: 2026-04-16

"""
from alembic import op
import sqlalchemy as sa


revision = "0001"
down_revision = None
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("CREATE EXTENSION IF NOT EXISTS pgcrypto")
    op.execute("CREATE EXTENSION IF NOT EXISTS citext")
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")

    op.execute("""
        CREATE TABLE users (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          email CITEXT UNIQUE NOT NULL,
          hashed_password TEXT NOT NULL,
          role TEXT NOT NULL CHECK (role IN ('admin','user')),
          is_active BOOLEAN NOT NULL DEFAULT TRUE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)

    op.execute("""
        CREATE TABLE user_sessions (
          token TEXT PRIMARY KEY,
          user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          expires_at TIMESTAMPTZ NOT NULL
        )
    """)
    op.execute("CREATE INDEX ix_sessions_user ON user_sessions(user_id)")

    op.execute("""
        CREATE TABLE companies (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          nif CHAR(9) UNIQUE NOT NULL,
          legal_name TEXT NOT NULL,
          trade_name TEXT,
          cae TEXT,
          address TEXT,
          status TEXT,
          ptdata_payload JSONB,
          ptdata_fetched_at TIMESTAMPTZ,
          monitored BOOLEAN NOT NULL DEFAULT TRUE,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_companies_name_trgm ON companies USING gin (legal_name gin_trgm_ops)")

    op.execute("""
        CREATE TABLE processes (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          source TEXT NOT NULL CHECK (source IN ('distribuicao','cire')),
          process_number TEXT NOT NULL,
          tribunal TEXT NOT NULL,
          juizo TEXT,
          species TEXT,
          role_in_process TEXT,
          date_filed DATE NOT NULL,
          dedup_hash CHAR(64) NOT NULL UNIQUE,
          raw JSONB NOT NULL,
          raw_html TEXT,
          first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_processes_company ON processes(company_id)")
    op.execute("CREATE INDEX ix_processes_date ON processes(date_filed DESC)")
    op.execute("CREATE INDEX ix_processes_source ON processes(source)")

    op.execute("""
        CREATE TABLE process_events (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          process_id UUID NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
          event_type TEXT NOT NULL,
          payload JSONB,
          occurred_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_events_process ON process_events(process_id, occurred_at DESC)")

    op.execute("""
        CREATE TABLE scraping_logs (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          source TEXT NOT NULL,
          started_at TIMESTAMPTZ NOT NULL,
          finished_at TIMESTAMPTZ,
          status TEXT NOT NULL CHECK (status IN ('running','ok','partial','error')),
          rows_seen INT DEFAULT 0,
          rows_new INT DEFAULT 0,
          error TEXT,
          params JSONB
        )
    """)
    op.execute("CREATE INDEX ix_logs_started ON scraping_logs(started_at DESC)")


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS scraping_logs CASCADE")
    op.execute("DROP TABLE IF EXISTS process_events CASCADE")
    op.execute("DROP TABLE IF EXISTS processes CASCADE")
    op.execute("DROP TABLE IF EXISTS companies CASCADE")
    op.execute("DROP TABLE IF EXISTS user_sessions CASCADE")
    op.execute("DROP TABLE IF EXISTS users CASCADE")