-- nata.onl / Natapulse
-- Migration pack v5 (additivo, non distruttivo)
-- Target: PostgreSQL
--
-- Obiettivo:
-- 1) introdurre IAM / tenancy per RBAC e workspace;
-- 2) allineare DB a Models, Audit, Job Events, Decision Log e Natapulse multi-workspace;
-- 3) mantenere compatibilità con lo schema minimo v2 usando solo CREATE/ALTER additive.
--
-- Nota:
-- Questo file evita DROP distruttivi. La rimozione di colonne legacy va fatta solo
-- dopo backfill, dual-write e cutover validati.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS iam;
CREATE SCHEMA IF NOT EXISTS ops;
CREATE SCHEMA IF NOT EXISTS memory;
CREATE SCHEMA IF NOT EXISTS pulse;

CREATE OR REPLACE FUNCTION public.set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$;

-- ------------------------------------------------------------------
-- IAM / RBAC
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS iam.users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_key text NOT NULL UNIQUE,
    email text UNIQUE,
    display_name text NOT NULL,
    auth_subject text UNIQUE,
    status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('invited', 'active', 'suspended', 'archived')),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS iam.roles (
    role_key text PRIMARY KEY,
    name text NOT NULL,
    scope text NOT NULL,
    can_text text,
    cannot_text text,
    is_workspace_scoped boolean NOT NULL DEFAULT false,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS iam.permissions (
    permission_key text PRIMARY KEY,
    domain text NOT NULL,
    description text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS iam.role_permissions (
    role_key text NOT NULL REFERENCES iam.roles(role_key) ON DELETE CASCADE,
    permission_key text NOT NULL REFERENCES iam.permissions(permission_key) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (role_key, permission_key)
);

CREATE TABLE IF NOT EXISTS iam.user_role_assignments (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
    role_key text NOT NULL REFERENCES iam.roles(role_key) ON DELETE CASCADE,
    scope_type text NOT NULL DEFAULT 'global'
        CHECK (scope_type IN ('global', 'workspace')),
    scope_ref text NOT NULL DEFAULT '',
    assigned_by text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, role_key, scope_type, scope_ref)
);

INSERT INTO iam.roles (
    role_key, name, scope, can_text, cannot_text, is_workspace_scoped
) VALUES
    ('owner', 'Owner', 'Controllo totale di dominio tecnico e prodotto.', 'Tutte le viste, tutte le azioni sensibili, pubblicazione policy, review rischi, configurazione modelli, gestione workspace.', 'Nessun limite funzionale; resta soggetto a audit trail e doppia conferma per azioni ad alto impatto.', false),
    ('ops_admin', 'Ops Admin', 'Gestione operativa del control plane privato.', 'Usare dashboard tecnica, agenti, job, monitor, log, rischio, memoria, policy in lettura e azioni operative consentite.', 'Non amministra workspace cliente, billing o token cliente; su Natapulse è osservatore interno.', false),
    ('analyst_internal', 'Analyst Internal', 'Lettura interna e uso analitico del prodotto.', 'Leggere overview/memory/policy/audit e tutte le schermate Natapulse; può generare report e usare chat come analista.', 'Nessuna azione distruttiva su agenti, job, policy, modelli o membership.', false),
    ('client_admin', 'Client Admin', 'Amministratore workspace Natapulse.', 'Gestire watchlist, alert, membri workspace, token applicativi, preferenze e report del proprio workspace.', 'Nessun accesso a dashboard tecnica, log interni, policy ops o rischio operativo completo.', true),
    ('client_user', 'Client User', 'Consumo prodotto con azioni limitate.', 'Leggere feed/report/eventi, usare chat, creare o gestire watchlist/alert nel perimetro consentito dal workspace.', 'Non gestisce membri, token workspace o qualunque superficie del control plane.', true)
ON CONFLICT (role_key) DO UPDATE SET
    name = EXCLUDED.name,
    scope = EXCLUDED.scope,
    can_text = EXCLUDED.can_text,
    cannot_text = EXCLUDED.cannot_text,
    is_workspace_scoped = EXCLUDED.is_workspace_scoped;

INSERT INTO iam.permissions (
    permission_key, domain, description
) VALUES
    ('ops.overview.read', 'ops', 'Lettura KPI, incidenti, queue depth e salute complessiva.'),
    ('ops.agents.read', 'ops', 'Lettura stato agenti, run, heartbeat e dettaglio.'),
    ('ops.agents.operate', 'ops', 'Pausa, resume e retry policy-safe sugli agenti.'),
    ('ops.jobs.read', 'ops', 'Lettura code, backlog, payload essenziale e cronologia job.'),
    ('ops.jobs.operate', 'ops', 'Retry, cancel, reprioritize e unblock dei job.'),
    ('ops.memory.read', 'memory', 'Ricerca e retrieve bundle su memoria raw/curated/decision.'),
    ('ops.logs.read', 'ops', 'Lettura stream e query log tecnici.'),
    ('ops.policy.read', 'ops', 'Lettura policy attive, versioni e diff.'),
    ('ops.policy.publish', 'ops', 'Creazione versione policy, attivazione/archiviazione.'),
    ('ops.risk.read', 'ops', 'Lettura registro rischi e trend.'),
    ('ops.risk.review', 'ops', 'Cambio score/stato/owner e review formale rischio.'),
    ('ops.monitor.read', 'ops', 'Lettura host, servizi, metriche e processi.'),
    ('ops.models.read', 'ops', 'Lettura stato modelli, fallback e saturazione.'),
    ('ops.models.configure', 'ops', 'Cambio configurazione modelli e routing operativo.'),
    ('ops.audit.read', 'ops', 'Lettura audit trail, decision log e rollback history.'),
    ('pulse.live.read', 'pulse', 'Accesso al feed Live Pulse e cluster eventi.'),
    ('pulse.events.read', 'pulse', 'Accesso al dettaglio evento e fonti correlate.'),
    ('pulse.reports.read', 'pulse', 'Accesso ai report generati e archiviati.'),
    ('pulse.reports.generate', 'pulse', 'Generazione report catch-up, event, theme, ticker/coin.'),
    ('pulse.social.read', 'pulse', 'Accesso a Social Radar.'),
    ('pulse.filings.read', 'pulse', 'Accesso a Filings Desk.'),
    ('pulse.market.read', 'pulse', 'Accesso a Market Desk.'),
    ('pulse.onchain.read', 'pulse', 'Accesso a On-chain Desk.'),
    ('pulse.watchlists.read', 'pulse', 'Lettura watchlist e feed associato.'),
    ('pulse.watchlists.manage', 'pulse', 'Creazione/modifica watchlist e relativi item.'),
    ('pulse.wallets.read', 'pulse', 'Accesso a Wallet Intelligence e wallet profile.'),
    ('pulse.chat.ask', 'pulse', 'Invio query e uso del canale chat/analyst.'),
    ('pulse.alerts.read', 'pulse', 'Lettura regole alert e consegne.'),
    ('pulse.alerts.manage', 'pulse', 'Creazione/modifica/pausa alert.'),
    ('pulse.settings.read', 'pulse', 'Lettura preferenze profilo/workspace.'),
    ('pulse.settings.manage_self', 'pulse', 'Aggiornamento preferenze utente/workspace consentite.'),
    ('pulse.workspace.members.manage', 'pulse', 'Gestione membri e ruoli del workspace.'),
    ('pulse.workspace.tokens.manage', 'pulse', 'Rotazione token applicativi/API del workspace.')
ON CONFLICT (permission_key) DO NOTHING;

INSERT INTO iam.role_permissions (
    role_key, permission_key
) VALUES
    ('owner', 'ops.overview.read'),
    ('owner', 'ops.agents.read'),
    ('owner', 'ops.agents.operate'),
    ('owner', 'ops.jobs.read'),
    ('owner', 'ops.jobs.operate'),
    ('owner', 'ops.memory.read'),
    ('owner', 'ops.logs.read'),
    ('owner', 'ops.policy.read'),
    ('owner', 'ops.policy.publish'),
    ('owner', 'ops.risk.read'),
    ('owner', 'ops.risk.review'),
    ('owner', 'ops.monitor.read'),
    ('owner', 'ops.models.read'),
    ('owner', 'ops.models.configure'),
    ('owner', 'ops.audit.read'),
    ('owner', 'pulse.live.read'),
    ('owner', 'pulse.events.read'),
    ('owner', 'pulse.reports.read'),
    ('owner', 'pulse.reports.generate'),
    ('owner', 'pulse.social.read'),
    ('owner', 'pulse.filings.read'),
    ('owner', 'pulse.market.read'),
    ('owner', 'pulse.onchain.read'),
    ('owner', 'pulse.watchlists.read'),
    ('owner', 'pulse.watchlists.manage'),
    ('owner', 'pulse.wallets.read'),
    ('owner', 'pulse.chat.ask'),
    ('owner', 'pulse.alerts.read'),
    ('owner', 'pulse.alerts.manage'),
    ('owner', 'pulse.settings.read'),
    ('owner', 'pulse.settings.manage_self'),
    ('owner', 'pulse.workspace.members.manage'),
    ('owner', 'pulse.workspace.tokens.manage'),
    ('ops_admin', 'ops.overview.read'),
    ('ops_admin', 'ops.agents.read'),
    ('ops_admin', 'ops.agents.operate'),
    ('ops_admin', 'ops.jobs.read'),
    ('ops_admin', 'ops.jobs.operate'),
    ('ops_admin', 'ops.memory.read'),
    ('ops_admin', 'ops.logs.read'),
    ('ops_admin', 'ops.policy.read'),
    ('ops_admin', 'ops.policy.publish'),
    ('ops_admin', 'ops.risk.read'),
    ('ops_admin', 'ops.risk.review'),
    ('ops_admin', 'ops.monitor.read'),
    ('ops_admin', 'ops.models.read'),
    ('ops_admin', 'ops.models.configure'),
    ('ops_admin', 'ops.audit.read'),
    ('ops_admin', 'pulse.live.read'),
    ('ops_admin', 'pulse.events.read'),
    ('ops_admin', 'pulse.reports.read'),
    ('ops_admin', 'pulse.reports.generate'),
    ('ops_admin', 'pulse.social.read'),
    ('ops_admin', 'pulse.filings.read'),
    ('ops_admin', 'pulse.market.read'),
    ('ops_admin', 'pulse.onchain.read'),
    ('ops_admin', 'pulse.watchlists.read'),
    ('ops_admin', 'pulse.wallets.read'),
    ('ops_admin', 'pulse.chat.ask'),
    ('ops_admin', 'pulse.alerts.read'),
    ('ops_admin', 'pulse.settings.read'),
    ('analyst_internal', 'ops.overview.read'),
    ('analyst_internal', 'ops.agents.read'),
    ('analyst_internal', 'ops.jobs.read'),
    ('analyst_internal', 'ops.memory.read'),
    ('analyst_internal', 'ops.policy.read'),
    ('analyst_internal', 'ops.risk.read'),
    ('analyst_internal', 'ops.monitor.read'),
    ('analyst_internal', 'ops.models.read'),
    ('analyst_internal', 'ops.audit.read'),
    ('analyst_internal', 'pulse.live.read'),
    ('analyst_internal', 'pulse.events.read'),
    ('analyst_internal', 'pulse.reports.read'),
    ('analyst_internal', 'pulse.reports.generate'),
    ('analyst_internal', 'pulse.social.read'),
    ('analyst_internal', 'pulse.filings.read'),
    ('analyst_internal', 'pulse.market.read'),
    ('analyst_internal', 'pulse.onchain.read'),
    ('analyst_internal', 'pulse.watchlists.read'),
    ('analyst_internal', 'pulse.wallets.read'),
    ('analyst_internal', 'pulse.chat.ask'),
    ('analyst_internal', 'pulse.alerts.read'),
    ('analyst_internal', 'pulse.settings.read'),
    ('client_admin', 'pulse.live.read'),
    ('client_admin', 'pulse.events.read'),
    ('client_admin', 'pulse.reports.read'),
    ('client_admin', 'pulse.reports.generate'),
    ('client_admin', 'pulse.social.read'),
    ('client_admin', 'pulse.filings.read'),
    ('client_admin', 'pulse.market.read'),
    ('client_admin', 'pulse.onchain.read'),
    ('client_admin', 'pulse.watchlists.read'),
    ('client_admin', 'pulse.watchlists.manage'),
    ('client_admin', 'pulse.wallets.read'),
    ('client_admin', 'pulse.chat.ask'),
    ('client_admin', 'pulse.alerts.read'),
    ('client_admin', 'pulse.alerts.manage'),
    ('client_admin', 'pulse.settings.read'),
    ('client_admin', 'pulse.settings.manage_self'),
    ('client_admin', 'pulse.workspace.members.manage'),
    ('client_admin', 'pulse.workspace.tokens.manage'),
    ('client_user', 'pulse.live.read'),
    ('client_user', 'pulse.events.read'),
    ('client_user', 'pulse.reports.read'),
    ('client_user', 'pulse.reports.generate'),
    ('client_user', 'pulse.social.read'),
    ('client_user', 'pulse.filings.read'),
    ('client_user', 'pulse.market.read'),
    ('client_user', 'pulse.onchain.read'),
    ('client_user', 'pulse.watchlists.read'),
    ('client_user', 'pulse.watchlists.manage'),
    ('client_user', 'pulse.wallets.read'),
    ('client_user', 'pulse.chat.ask'),
    ('client_user', 'pulse.alerts.read'),
    ('client_user', 'pulse.alerts.manage'),
    ('client_user', 'pulse.settings.read'),
    ('client_user', 'pulse.settings.manage_self')
ON CONFLICT (role_key, permission_key) DO NOTHING;

-- ------------------------------------------------------------------
-- WORKSPACES / TENANCY
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS pulse.workspaces (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_key text NOT NULL UNIQUE,
    name text NOT NULL,
    plan_tier text NOT NULL DEFAULT 'internal'
        CHECK (plan_tier IN ('internal', 'trial', 'standard', 'premium', 'enterprise')),
    status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'trialing', 'suspended', 'archived')),
    owner_user_id uuid REFERENCES iam.users(id) ON DELETE SET NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pulse.workspace_memberships (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid NOT NULL REFERENCES pulse.workspaces(id) ON DELETE CASCADE,
    user_id uuid NOT NULL REFERENCES iam.users(id) ON DELETE CASCADE,
    role_key text NOT NULL REFERENCES iam.roles(role_key) ON DELETE RESTRICT,
    membership_status text NOT NULL DEFAULT 'active'
        CHECK (membership_status IN ('invited', 'active', 'suspended', 'removed')),
    invited_by text,
    joined_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, user_id)
);

CREATE TABLE IF NOT EXISTS pulse.workspace_tokens (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid NOT NULL REFERENCES pulse.workspaces(id) ON DELETE CASCADE,
    token_name text NOT NULL,
    token_prefix text NOT NULL,
    token_hash text NOT NULL,
    status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'rotated', 'revoked', 'archived')),
    last_used_at timestamptz,
    rotated_at timestamptz,
    created_by text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, token_name)
);

-- ------------------------------------------------------------------
-- OPS DELTA V5
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS ops.models (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    model_key text NOT NULL UNIQUE,
    name text NOT NULL,
    provider_type text NOT NULL
        CHECK (provider_type IN ('local', 'cloud', 'hybrid')),
    host_id uuid REFERENCES ops.hosts(id) ON DELETE SET NULL,
    routing_class text NOT NULL
        CHECK (routing_class IN ('reasoning', 'coding', 'fast', 'writing', 'quant', 'fallback')),
    status text NOT NULL DEFAULT 'available'
        CHECK (status IN ('available', 'warming', 'degraded', 'paused', 'offline')),
    concurrency_limit integer NOT NULL DEFAULT 1 CHECK (concurrency_limit >= 1),
    context_window integer,
    keep_alive_seconds integer,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS ops.model_config_versions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    model_id uuid NOT NULL REFERENCES ops.models(id) ON DELETE CASCADE,
    version_no integer NOT NULL,
    config_json jsonb NOT NULL DEFAULT '{}'::jsonb,
    change_note text,
    created_by text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (model_id, version_no)
);

CREATE TABLE IF NOT EXISTS ops.job_events (
    id bigserial PRIMARY KEY,
    job_id uuid NOT NULL REFERENCES ops.jobs(id) ON DELETE CASCADE,
    event_type text NOT NULL,
    status_from text,
    status_to text,
    actor text,
    trace_id text,
    note text,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS ops.audit_entries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    trace_id text,
    actor text,
    role_key text,
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    permission_key text REFERENCES iam.permissions(permission_key) ON DELETE SET NULL,
    action_type text NOT NULL,
    target_type text NOT NULL,
    target_ref text,
    outcome text NOT NULL
        CHECK (outcome IN ('accepted', 'succeeded', 'failed', 'denied', 'rolled_back')),
    reason text,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE IF EXISTS ops.jobs
    ADD COLUMN IF NOT EXISTS trace_id text,
    ADD COLUMN IF NOT EXISTS idempotency_key text,
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS requested_capability text,
    ADD COLUMN IF NOT EXISTS requested_model text,
    ADD COLUMN IF NOT EXISTS dedup_key text,
    ADD COLUMN IF NOT EXISTS cancel_reason text;

ALTER TABLE IF EXISTS ops.agent_runs
    ADD COLUMN IF NOT EXISTS trace_id text,
    ADD COLUMN IF NOT EXISTS review_status text DEFAULT 'not_required'
        CHECK (review_status IN ('not_required', 'pending', 'approved', 'rejected')),
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS decision_ref uuid,
    ADD COLUMN IF NOT EXISTS dedup_key text;

ALTER TABLE IF EXISTS ops.risks
    ADD COLUMN IF NOT EXISTS last_reviewed_at timestamptz,
    ADD COLUMN IF NOT EXISTS last_reviewed_by text,
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL;

-- ------------------------------------------------------------------
-- MEMORY DELTA V5
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS memory.decisions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    decision_key text NOT NULL UNIQUE,
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    author text,
    status text NOT NULL DEFAULT 'draft'
        CHECK (status IN ('draft', 'approved', 'rejected', 'superseded')),
    decision text NOT NULL,
    rationale text,
    alternatives text,
    impact text,
    references_json jsonb NOT NULL DEFAULT '[]'::jsonb,
    tags text[] NOT NULL DEFAULT ARRAY[]::text[]
);

CREATE TABLE IF NOT EXISTS memory.digests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    digest_type text NOT NULL
        CHECK (digest_type IN ('daily', 'weekly', 'incident', 'release')),
    period_start timestamptz NOT NULL,
    period_end timestamptz NOT NULL,
    summary text NOT NULL,
    highlights jsonb NOT NULL DEFAULT '[]'::jsonb,
    blockers jsonb NOT NULL DEFAULT '[]'::jsonb,
    next_actions jsonb NOT NULL DEFAULT '[]'::jsonb,
    references_json jsonb NOT NULL DEFAULT '[]'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS memory.retrieve_sessions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    requester_type text NOT NULL
        CHECK (requester_type IN ('user', 'agent', 'system')),
    requester_ref text,
    mode text NOT NULL
        CHECK (mode IN ('structured', 'semantic', 'hybrid', 'bundle')),
    query_text text NOT NULL,
    top_k integer NOT NULL DEFAULT 5 CHECK (top_k BETWEEN 1 AND 20),
    result_count integer NOT NULL DEFAULT 0,
    trace_id text,
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE IF EXISTS memory.memory_items
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS visibility_scope text NOT NULL DEFAULT 'internal'
        CHECK (visibility_scope IN ('internal', 'workspace', 'client', 'public')),
    ADD COLUMN IF NOT EXISTS confidence numeric(5,2),
    ADD COLUMN IF NOT EXISTS retention_class text NOT NULL DEFAULT 'standard'
        CHECK (retention_class IN ('short', 'standard', 'long', 'permanent')),
    ADD COLUMN IF NOT EXISTS source_url text,
    ADD COLUMN IF NOT EXISTS checksum text;

-- ------------------------------------------------------------------
-- PULSE DELTA V5
-- ------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS pulse.event_clusters (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    cluster_key text NOT NULL UNIQUE,
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    title text NOT NULL,
    theme text,
    severity_score smallint CHECK (severity_score BETWEEN 1 AND 10),
    status text NOT NULL DEFAULT 'open'
        CHECK (status IN ('open', 'tracking', 'closed', 'archived')),
    first_seen_at timestamptz,
    last_seen_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pulse.event_sources (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    market_event_id uuid NOT NULL REFERENCES pulse.market_events(id) ON DELETE CASCADE,
    source_kind text NOT NULL
        CHECK (source_kind IN ('social', 'sec', 'market', 'news', 'onchain', 'internal')),
    source_name text,
    source_ref text,
    source_url text,
    published_at timestamptz,
    credibility_score numeric(5,2),
    snippet text,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pulse.report_requests (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    request_key text NOT NULL UNIQUE,
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    requested_by text,
    report_type text NOT NULL
        CHECK (report_type IN ('catch_up_15m', 'catch_up_1h', 'session', 'event', 'theme', 'ticker', 'coin', 'risk')),
    scope_type text NOT NULL,
    scope_ref text,
    request_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'queued'
        CHECK (status IN ('queued', 'running', 'completed', 'failed', 'cancelled')),
    requested_at timestamptz NOT NULL DEFAULT now(),
    started_at timestamptz,
    completed_at timestamptz,
    error_message text
);

CREATE TABLE IF NOT EXISTS pulse.report_versions (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    report_id uuid NOT NULL REFERENCES pulse.reports(id) ON DELETE CASCADE,
    version_no integer NOT NULL,
    summary text,
    body_md text,
    change_note text,
    created_by text,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (report_id, version_no)
);

CREATE TABLE IF NOT EXISTS pulse.chat_threads (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE CASCADE,
    created_by text,
    title text,
    status text NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'archived')),
    last_message_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pulse.chat_messages (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    thread_id uuid NOT NULL REFERENCES pulse.chat_threads(id) ON DELETE CASCADE,
    role text NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    author_ref text,
    content text NOT NULL,
    citations jsonb NOT NULL DEFAULT '[]'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS pulse.user_preferences (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE CASCADE,
    user_id uuid REFERENCES iam.users(id) ON DELETE CASCADE,
    preference_key text NOT NULL,
    preference_value jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (workspace_id, user_id, preference_key)
);

CREATE TABLE IF NOT EXISTS pulse.alert_deliveries (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    alert_event_id uuid NOT NULL REFERENCES pulse.alert_events(id) ON DELETE CASCADE,
    channel text NOT NULL,
    delivery_status text NOT NULL DEFAULT 'queued'
        CHECK (delivery_status IN ('queued', 'sent', 'failed', 'suppressed')),
    destination_ref text,
    provider_message_ref text,
    delivered_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE IF EXISTS pulse.market_events
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS cluster_id uuid REFERENCES pulse.event_clusters(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS source_url text,
    ADD COLUMN IF NOT EXISTS detection_mode text DEFAULT 'hybrid'
        CHECK (detection_mode IN ('manual', 'rules', 'llm_local', 'llm_cloud', 'hybrid')),
    ADD COLUMN IF NOT EXISTS confidence_band text DEFAULT 'medium'
        CHECK (confidence_band IN ('low', 'medium', 'high')),
    ADD COLUMN IF NOT EXISTS visibility_scope text NOT NULL DEFAULT 'client'
        CHECK (visibility_scope IN ('internal', 'workspace', 'client', 'public')),
    ADD COLUMN IF NOT EXISTS published_at timestamptz;

ALTER TABLE IF EXISTS pulse.reports
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS request_id uuid REFERENCES pulse.report_requests(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS status text DEFAULT 'draft'
        CHECK (status IN ('draft', 'queued', 'running', 'ready', 'published', 'archived', 'failed')),
    ADD COLUMN IF NOT EXISTS version_no integer NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS report_window_start timestamptz,
    ADD COLUMN IF NOT EXISTS report_window_end timestamptz,
    ADD COLUMN IF NOT EXISTS published_by text;

ALTER TABLE IF EXISTS pulse.watchlists
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS visibility_scope text NOT NULL DEFAULT 'workspace'
        CHECK (visibility_scope IN ('internal', 'workspace', 'client', 'public'));

ALTER TABLE IF EXISTS pulse.alert_rules
    ADD COLUMN IF NOT EXISTS workspace_id uuid REFERENCES pulse.workspaces(id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS cooldown_seconds integer NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS last_triggered_at timestamptz;

-- ------------------------------------------------------------------
-- INDEXES
-- ------------------------------------------------------------------

CREATE INDEX IF NOT EXISTS idx_iam_user_role_assignments_scope
    ON iam.user_role_assignments (scope_type, scope_ref, role_key);

CREATE INDEX IF NOT EXISTS idx_pulse_workspace_memberships_workspace_role
    ON pulse.workspace_memberships (workspace_id, role_key);

CREATE INDEX IF NOT EXISTS idx_ops_jobs_workspace_status
    ON ops.jobs (workspace_id, status, created_at DESC);

CREATE UNIQUE INDEX IF NOT EXISTS idx_ops_jobs_idempotency_key
    ON ops.jobs (idempotency_key)
    WHERE idempotency_key IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_ops_job_events_job_time
    ON ops.job_events (job_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_ops_audit_entries_trace_time
    ON ops.audit_entries (trace_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_ops_models_status_route
    ON ops.models (status, routing_class);

CREATE INDEX IF NOT EXISTS idx_memory_items_workspace_visibility_created
    ON memory.memory_items (workspace_id, visibility_scope, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_memory_items_checksum
    ON memory.memory_items (checksum);

CREATE INDEX IF NOT EXISTS idx_memory_decisions_workspace_created
    ON memory.decisions (workspace_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_memory_digests_workspace_period
    ON memory.digests (workspace_id, period_end DESC);

CREATE INDEX IF NOT EXISTS idx_memory_retrieve_sessions_trace_time
    ON memory.retrieve_sessions (trace_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_market_events_workspace_cluster_time
    ON pulse.market_events (workspace_id, cluster_id, occurred_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_event_sources_event
    ON pulse.event_sources (market_event_id, published_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_report_requests_workspace_status
    ON pulse.report_requests (workspace_id, status, requested_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_reports_workspace_status_created
    ON pulse.reports (workspace_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_chat_threads_workspace_last
    ON pulse.chat_threads (workspace_id, last_message_at DESC NULLS LAST);

CREATE INDEX IF NOT EXISTS idx_pulse_chat_messages_thread_time
    ON pulse.chat_messages (thread_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_watchlists_workspace
    ON pulse.watchlists (workspace_id, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_alert_rules_workspace_status
    ON pulse.alert_rules (workspace_id, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_pulse_alert_deliveries_event
    ON pulse.alert_deliveries (alert_event_id, created_at DESC);

-- ------------------------------------------------------------------
-- UPDATED_AT TRIGGERS
-- ------------------------------------------------------------------

DROP TRIGGER IF EXISTS trg_iam_users_updated_at ON iam.users;
CREATE TRIGGER trg_iam_users_updated_at
BEFORE UPDATE ON iam.users
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_iam_roles_updated_at ON iam.roles;
CREATE TRIGGER trg_iam_roles_updated_at
BEFORE UPDATE ON iam.roles
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_workspaces_updated_at ON pulse.workspaces;
CREATE TRIGGER trg_pulse_workspaces_updated_at
BEFORE UPDATE ON pulse.workspaces
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_workspace_memberships_updated_at ON pulse.workspace_memberships;
CREATE TRIGGER trg_pulse_workspace_memberships_updated_at
BEFORE UPDATE ON pulse.workspace_memberships
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_workspace_tokens_updated_at ON pulse.workspace_tokens;
CREATE TRIGGER trg_pulse_workspace_tokens_updated_at
BEFORE UPDATE ON pulse.workspace_tokens
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_ops_models_updated_at ON ops.models;
CREATE TRIGGER trg_ops_models_updated_at
BEFORE UPDATE ON ops.models
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_event_clusters_updated_at ON pulse.event_clusters;
CREATE TRIGGER trg_pulse_event_clusters_updated_at
BEFORE UPDATE ON pulse.event_clusters
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_chat_threads_updated_at ON pulse.chat_threads;
CREATE TRIGGER trg_pulse_chat_threads_updated_at
BEFORE UPDATE ON pulse.chat_threads
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

DROP TRIGGER IF EXISTS trg_pulse_user_preferences_updated_at ON pulse.user_preferences;
CREATE TRIGGER trg_pulse_user_preferences_updated_at
BEFORE UPDATE ON pulse.user_preferences
FOR EACH ROW EXECUTE FUNCTION public.set_updated_at();

-- ------------------------------------------------------------------
-- VIEWS OPERATIVE V5
-- ------------------------------------------------------------------

CREATE OR REPLACE VIEW ops.v_job_live_queue_v5 AS
SELECT
    j.id,
    j.queue_name,
    j.priority,
    j.status,
    j.workspace_id,
    j.trace_id,
    j.requested_capability,
    j.requested_model,
    j.created_at,
    max(e.created_at) AS last_event_at
FROM ops.jobs j
LEFT JOIN ops.job_events e ON e.job_id = j.id
GROUP BY j.id, j.queue_name, j.priority, j.status, j.workspace_id, j.trace_id, j.requested_capability, j.requested_model, j.created_at;

CREATE OR REPLACE VIEW ops.v_model_compute_overview_v5 AS
SELECT
    m.id,
    m.model_key,
    m.name,
    m.provider_type,
    m.routing_class,
    m.status,
    m.concurrency_limit,
    m.context_window,
    h.name AS host_name,
    h.host_key
FROM ops.models m
LEFT JOIN ops.hosts h ON h.id = m.host_id;

CREATE OR REPLACE VIEW pulse.v_live_event_clusters_v5 AS
SELECT
    c.id AS cluster_id,
    c.cluster_key,
    c.title,
    c.theme,
    c.severity_score,
    c.status,
    c.first_seen_at,
    c.last_seen_at,
    count(e.id) AS event_count,
    max(e.occurred_at) AS latest_event_at
FROM pulse.event_clusters c
LEFT JOIN pulse.market_events e ON e.cluster_id = c.id
GROUP BY c.id, c.cluster_key, c.title, c.theme, c.severity_score, c.status, c.first_seen_at, c.last_seen_at;

CREATE OR REPLACE VIEW pulse.v_workspace_report_pipeline_v5 AS
SELECT
    rr.id AS report_request_id,
    rr.request_key,
    rr.workspace_id,
    rr.report_type,
    rr.scope_type,
    rr.scope_ref,
    rr.status AS request_status,
    rr.requested_at,
    r.id AS report_id,
    r.title,
    r.status AS report_status,
    r.version_no,
    r.published_at
FROM pulse.report_requests rr
LEFT JOIN pulse.reports r ON r.request_id = rr.id;

-- Fine migration pack v5
