-- Reference PostgreSQL DDL for the qbit-assistant tables (snake_case, as mapped by a
-- snake_case table customizer). Adjust to your conventions; the QBit only needs the
-- QQQ field names to map onto columns.

CREATE TABLE assistant_conversation
(
   id SERIAL PRIMARY KEY,
   user_id VARCHAR(255),
   title TEXT,
   max_classification VARCHAR(16),
   status VARCHAR(16),
   pinned BOOLEAN,
   last_message_at TIMESTAMP WITH TIME ZONE,
   unread_count INTEGER,
   context_json TEXT,
   total_tokens_in BIGINT,
   total_tokens_out BIGINT,
   total_cost_micros BIGINT,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_conversation_user_id ON assistant_conversation (user_id);

CREATE TABLE assistant_message
(
   id SERIAL PRIMARY KEY,
   conversation_id INTEGER,
   user_id VARCHAR(255),
   parent_message_id INTEGER,
   role VARCHAR(16),
   status VARCHAR(16),
   turn_id INTEGER,
   seq INTEGER,
   model VARCHAR(200),
   parts_json TEXT,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_message_user_id ON assistant_message (user_id);
CREATE INDEX assistant_message_conversation_id ON assistant_message (conversation_id);

CREATE TABLE assistant_turn
(
   id SERIAL PRIMARY KEY,
   conversation_id INTEGER,
   user_id VARCHAR(255),
   user_message_id INTEGER,
   assistant_message_id INTEGER,
   status VARCHAR(16),
   classification VARCHAR(16),
   classification_tables VARCHAR(500),
   stop_reason TEXT,
   error_code VARCHAR(32),
   cancel_reason VARCHAR(64),
   model VARCHAR(200),
   started_at TIMESTAMP WITH TIME ZONE,
   ended_at TIMESTAMP WITH TIME ZONE,
   tool_call_count INTEGER,
   model_call_count INTEGER,
   input_tokens BIGINT,
   output_tokens BIGINT,
   cache_read_tokens BIGINT,
   cache_write_tokens BIGINT,
   cost_micros BIGINT,
   provider_request_ids TEXT,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_turn_user_id ON assistant_turn (user_id);
CREATE INDEX assistant_turn_conversation_id ON assistant_turn (conversation_id);

CREATE TABLE assistant_turn_event
(
   id SERIAL PRIMARY KEY,
   turn_id INTEGER,
   user_id VARCHAR(255),
   seq INTEGER,
   event_type VARCHAR(32),
   data_json TEXT,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_turn_event_user_id ON assistant_turn_event (user_id);
CREATE INDEX assistant_turn_event_turn ON assistant_turn_event (turn_id, seq);

CREATE TABLE assistant_tool_call
(
   id SERIAL PRIMARY KEY,
   turn_id INTEGER,
   conversation_id INTEGER,
   user_id VARCHAR(255),
   tool_call_id TEXT,
   tool_name VARCHAR(64),
   input_json TEXT,
   outcome VARCHAR(16),
   permission_check VARCHAR(255),
   table_name VARCHAR(128),
   process_name VARCHAR(128),
   widget_name VARCHAR(128),
   row_count INTEGER,
   duration_ms BIGINT,
   result_summary TEXT,
   result_json TEXT,
   redactions_json TEXT,
   ui_api_operation VARCHAR(255),
   http_status INTEGER,
   audit_href TEXT,
   pending_action_id INTEGER,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_tool_call_user_id ON assistant_tool_call (user_id);
CREATE INDEX assistant_tool_call_conversation_id ON assistant_tool_call (conversation_id);
CREATE INDEX assistant_tool_call_turn ON assistant_tool_call (turn_id);

CREATE TABLE assistant_feedback
(
   id SERIAL PRIMARY KEY,
   message_id INTEGER,
   conversation_id INTEGER,
   user_id VARCHAR(255),
   rating INTEGER,
   reason TEXT,
   comment TEXT,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_feedback_user_id ON assistant_feedback (user_id);
CREATE INDEX assistant_feedback_conversation_id ON assistant_feedback (conversation_id);

CREATE TABLE assistant_policy
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(64),
   kill_switch BOOLEAN,
   kill_switch_reason VARCHAR(255),
   main_model VARCHAR(64),
   main_effort VARCHAR(16),
   user_daily_tokens_soft BIGINT,
   user_daily_tokens_hard BIGINT,
   user_daily_cost_micros_soft BIGINT,
   user_daily_cost_micros_hard BIGINT,
   instance_monthly_tokens_soft BIGINT,
   instance_monthly_tokens_hard BIGINT,
   instance_monthly_cost_micros_soft BIGINT,
   instance_monthly_cost_micros_hard BIGINT,
   writes_require_confirmation BOOLEAN,
   allowed_tools VARCHAR(1000),
   max_rows_per_call INTEGER,
   redact_pii BOOLEAN,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);

CREATE TABLE assistant_usage_daily
(
   id SERIAL PRIMARY KEY,
   scope VARCHAR(16),
   user_id VARCHAR(255),
   usage_date DATE,
   turn_count INTEGER,
   input_tokens BIGINT,
   output_tokens BIGINT,
   cache_read_tokens BIGINT,
   cache_write_tokens BIGINT,
   cost_micros BIGINT,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_usage_daily_user_id ON assistant_usage_daily (user_id);
CREATE INDEX assistant_usage_daily_date ON assistant_usage_daily (usage_date, scope);

-- 0.1.0-RC.4: provider settings, model prices and per-call usage.
-- api_key_status is filled when a row is read (never written); the API key itself
-- is never stored: api_key_env_name holds only the NAME of an environment variable.

CREATE TABLE assistant_provider
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(100) NOT NULL UNIQUE,
   provider_type VARCHAR(50) NOT NULL,
   kind VARCHAR(30),
   location VARCHAR(10),
   hipaa_eligible BOOLEAN DEFAULT FALSE,
   region VARCHAR(50),
   cloud_project VARCHAR(100),
   api_version VARCHAR(30),
   connect_timeout_seconds INTEGER DEFAULT 10 CHECK (connect_timeout_seconds BETWEEN 1 AND 600),
   request_timeout_seconds INTEGER DEFAULT 120 CHECK (request_timeout_seconds BETWEEN 1 AND 600),
   last_discovery_at TIMESTAMP WITH TIME ZONE,
   last_discovery_status VARCHAR(16),
   last_discovery_error VARCHAR(500),
   model VARCHAR(200),
   enabled BOOLEAN,
   is_default BOOLEAN,
   base_url VARCHAR(255),
   tls_ca_bundle VARCHAR(100),
   api_key_env_name VARCHAR(100),
   api_key_status VARCHAR(200),
   monthly_cost_cap_usd NUMERIC(12, 2),
   max_output_tokens_per_request INTEGER,
   max_concurrent_requests INTEGER,
   last_test_at TIMESTAMP WITH TIME ZONE,
   last_test_status VARCHAR(16),
   last_test_latency_ms INTEGER,
   last_test_model VARCHAR(200),
   last_test_error VARCHAR(500),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);

CREATE TABLE assistant_model
(
   id SERIAL PRIMARY KEY,
   provider_id INTEGER NOT NULL REFERENCES assistant_provider(id),
   model_id VARCHAR(200) NOT NULL,
   display_name VARCHAR(200),
   enabled BOOLEAN,
   supports_tools BOOLEAN,
   supports_vision BOOLEAN,
   supports_streaming BOOLEAN,
   supports_reasoning_effort BOOLEAN,
   context_window_tokens INTEGER,
   max_output_tokens INTEGER,
   input_usd_per_m_tok NUMERIC(12, 4),
   output_usd_per_m_tok NUMERIC(12, 4),
   source VARCHAR(16),
   discovered_at TIMESTAMP WITH TIME ZONE,
   last_test_at TIMESTAMP WITH TIME ZONE,
   last_test_status VARCHAR(16),
   last_test_latency_ms INTEGER,
   last_test_error VARCHAR(500),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE,
   UNIQUE (provider_id, model_id)
);

CREATE TABLE assistant_model_price
(
   id SERIAL PRIMARY KEY,
   provider_type VARCHAR(50) NOT NULL,
   model VARCHAR(100) NOT NULL,
   input_usd_per_m_tok NUMERIC(12, 4) NOT NULL,
   output_usd_per_m_tok NUMERIC(12, 4) NOT NULL,
   cache_read_usd_per_m_tok NUMERIC(12, 4),
   cache_write5m_usd_per_m_tok NUMERIC(12, 4),
   cache_write1h_usd_per_m_tok NUMERIC(12, 4),
   notes VARCHAR(255),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE,
   UNIQUE (provider_type, model)
);

CREATE TABLE assistant_usage
(
   id SERIAL PRIMARY KEY,
   user_id VARCHAR(255),
   conversation_id INTEGER,
   turn_id INTEGER,
   provider_id INTEGER,
   provider_type VARCHAR(50),
   model VARCHAR(200),
   purpose VARCHAR(16),
   status VARCHAR(16),
   error_kind VARCHAR(32),
   http_status INTEGER,
   latency_ms INTEGER,
   input_tokens BIGINT,
   output_tokens BIGINT,
   cache_read_tokens BIGINT,
   cache_write_tokens BIGINT,
   cost_micros BIGINT,
   request_id TEXT,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_usage_create_date ON assistant_usage (create_date);
CREATE INDEX assistant_usage_provider ON assistant_usage (provider_id, create_date);
CREATE INDEX assistant_usage_conversation ON assistant_usage (conversation_id);
CREATE INDEX assistant_usage_user_id ON assistant_usage (user_id);

-- Anthropic list prices, USD per million tokens (cached 2026-10-06). Cache writes:
-- 1.25x input (5 minute) and 2x input (1 hour). Re-check before budgeting.
INSERT INTO assistant_model_price (provider_type, model, input_usd_per_m_tok, output_usd_per_m_tok, cache_read_usd_per_m_tok, cache_write5m_usd_per_m_tok, cache_write1h_usd_per_m_tok, notes, create_date, modify_date)
VALUES
   ('anthropic', 'claude-sonnet-5-5', 2.00, 10.00, 0.20, 2.50, 4.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-opus-5-5', 4.00, 20.00, 0.20, 5.00, 8.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-haiku-5-5', 0.10, 0.50, 0.01, 0.125, 0.20, 'Anthropic list price, 2026-10 (prompts up to 100K tokens)', NOW(), NOW()),
   ('anthropic', 'claude-fable-5-1', 10.00, 50.00, 0.25, 12.50, 20.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-opus-5', 5.00, 25.00, 0.50, 6.25, 10.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-sonnet-5', 2.00, 10.00, 0.20, 2.50, 4.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-opus-4-8', 5.00, 25.00, 0.50, 6.25, 10.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-sonnet-4-6', 3.00, 15.00, 0.30, 3.75, 6.00, 'Anthropic list price, 2026-10', NOW(), NOW()),
   ('anthropic', 'claude-haiku-4-5', 1.00, 5.00, 0.10, 1.25, 2.00, 'Anthropic list price, 2026-10', NOW(), NOW());

-- 0.1.0-RC.6: the assistant's data access per table / process, and writes waiting
-- for the user's confirmation. Existing databases: add the tool-call and policy
-- columns above with ALTER TABLE ... ADD COLUMN IF NOT EXISTS.
CREATE TABLE assistant_data_access
(
   id SERIAL PRIMARY KEY,
   object_type VARCHAR(16) NOT NULL,
   object_name VARCHAR(128) NOT NULL,
   mode VARCHAR(16) NOT NULL,
   notes VARCHAR(500),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE,
   CONSTRAINT assistant_data_access_object UNIQUE (object_type, object_name)
);

CREATE TABLE assistant_pending_action
(
   id SERIAL PRIMARY KEY,
   conversation_id INTEGER,
   turn_id INTEGER,
   user_id VARCHAR(255),
   tool_call_id TEXT,
   tool_name VARCHAR(64),
   ui_api_operation VARCHAR(255),
   table_name VARCHAR(128),
   record_id VARCHAR(128),
   params_json TEXT,
   summary TEXT,
   status VARCHAR(16),
   http_status INTEGER,
   result_summary TEXT,
   expires_at TIMESTAMP WITH TIME ZONE,
   decided_at TIMESTAMP WITH TIME ZONE,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_pending_action_user_id ON assistant_pending_action (user_id);
CREATE INDEX assistant_pending_action_conversation_id ON assistant_pending_action (conversation_id);

-- 0.1.0-RC.6: profiles (persona) and skills. Text the model reads; never tools or access.
CREATE TABLE assistant_profile
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(64) NOT NULL UNIQUE,
   display_name VARCHAR(64) NOT NULL,
   icon VARCHAR(64),
   personality TEXT,
   greeting VARCHAR(500),
   starter_prompts TEXT,
   is_default BOOLEAN,
   enabled BOOLEAN,
   app_name VARCHAR(128),
   permission_name VARCHAR(128),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);

CREATE TABLE assistant_skill
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(64) NOT NULL UNIQUE,
   description VARCHAR(500),
   instructions TEXT NOT NULL,
   enabled BOOLEAN,
   trigger_keywords VARCHAR(500),
   app_names VARCHAR(500),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);

CREATE TABLE assistant_profile_skill
(
   id SERIAL PRIMARY KEY,
   profile_id INTEGER NOT NULL,
   skill_id INTEGER NOT NULL,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE,
   CONSTRAINT assistant_profile_skill_pair UNIQUE (profile_id, skill_id)
);

CREATE TABLE assistant_data_audit
(
   id SERIAL PRIMARY KEY,
   user_id VARCHAR(255),
   conversation_id INTEGER,
   turn_id INTEGER,
   provider_id INTEGER,
   provider_name VARCHAR(200),
   model VARCHAR(200),
   hipaa_eligible BOOLEAN,
   classification VARCHAR(16),
   tables VARCHAR(500),
   decision VARCHAR(16),
   reason TEXT,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX assistant_data_audit_user_id ON assistant_data_audit (user_id);
CREATE INDEX assistant_data_audit_conversation_id ON assistant_data_audit (conversation_id);
CREATE INDEX assistant_data_audit_turn_id ON assistant_data_audit (turn_id);

-- P-d: data-driven budgets (UTC periods, USD or whole-token allowances).
CREATE TABLE assistant_budget
(
   id SERIAL PRIMARY KEY,
   name VARCHAR(200) NOT NULL,
   enabled BOOLEAN DEFAULT TRUE,
   scope_type VARCHAR(16) NOT NULL,
   scope_value VARCHAR(200),
   period VARCHAR(16) NOT NULL,
   metric VARCHAR(16) NOT NULL,
   limit_value NUMERIC NOT NULL CHECK (limit_value > 0),
   warn_percent INTEGER DEFAULT 80 CHECK (warn_percent >= 0 AND warn_percent < 100),
   at_limit_action VARCHAR(16) DEFAULT 'DOWNGRADE',
   downgrade_model_id INTEGER REFERENCES assistant_model(id),
   hard_ceiling_percent INTEGER DEFAULT 120 CHECK (hard_ceiling_percent >= 100),
   notes VARCHAR(2000),
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE
);
CREATE TABLE assistant_budget_override
(
   id SERIAL PRIMARY KEY,
   budget_id INTEGER NOT NULL REFERENCES assistant_budget(id),
   scope_value VARCHAR(200),
   extra_value NUMERIC,
   unlimited_until TIMESTAMP WITH TIME ZONE,
   reason TEXT NOT NULL,
   granted_by VARCHAR(200),
   expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
   create_date TIMESTAMP WITH TIME ZONE,
   modify_date TIMESTAMP WITH TIME ZONE,
   CHECK ((extra_value > 0 AND unlimited_until IS NULL) OR (extra_value IS NULL AND unlimited_until IS NOT NULL))
);
CREATE INDEX assistant_budget_override_budget_id ON assistant_budget_override(budget_id);
CREATE TABLE assistant_budget_event
(
   id SERIAL PRIMARY KEY,
   budget_id INTEGER NOT NULL REFERENCES assistant_budget(id),
   scope_key VARCHAR(400) NOT NULL,
   period_start TIMESTAMP WITH TIME ZONE NOT NULL,
   state VARCHAR(16) NOT NULL,
   used_value NUMERIC,
   limit_value NUMERIC,
   user_id VARCHAR(200),
   create_date TIMESTAMP WITH TIME ZONE,
   UNIQUE (budget_id, scope_key, period_start, state)
);
CREATE INDEX assistant_usage_budget_period ON assistant_usage(create_date, user_id, provider_id, model);

-- Private projection: standard admin permissions, AI NONE, no owner lock.
-- No conversation identifiers, text, request ids or credentials are copied.
CREATE TABLE assistant_budget_usage
(
   id SERIAL PRIMARY KEY,
   usage_id INTEGER NOT NULL UNIQUE,
   user_id VARCHAR(255),
   provider_id INTEGER,
   model VARCHAR(200),
   occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
   cost_micros BIGINT,
   input_tokens BIGINT,
   output_tokens BIGINT,
   cache_read_tokens BIGINT,
   cache_write_tokens BIGINT
);
CREATE INDEX assistant_budget_usage_period ON assistant_budget_usage(occurred_at, provider_id, model);
-- Run once before enabling P-d, with writes paused, to include existing usage.
INSERT INTO assistant_budget_usage
   (usage_id, user_id, provider_id, model, occurred_at, cost_micros, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens)
SELECT id, user_id, provider_id, model, create_date, cost_micros, input_tokens, output_tokens, cache_read_tokens, cache_write_tokens
FROM assistant_usage
ON CONFLICT (usage_id) DO NOTHING;


-- P-e: profile routing, bounded fallback chains, provider health and turn outcome.
ALTER TABLE assistant_profile ADD COLUMN routing_mode VARCHAR(16) DEFAULT 'OFF'
   CHECK (routing_mode IN ('OFF', 'HEURISTIC', 'CLASSIFIER'));
ALTER TABLE assistant_profile ADD COLUMN simple_model_id INTEGER REFERENCES assistant_model(id);
ALTER TABLE assistant_profile ADD COLUMN complex_model_id INTEGER REFERENCES assistant_model(id);
ALTER TABLE assistant_profile ADD COLUMN classifier_model_id INTEGER REFERENCES assistant_model(id);
ALTER TABLE assistant_model ADD COLUMN fallback_model_id INTEGER REFERENCES assistant_model(id);
ALTER TABLE assistant_provider ADD COLUMN health_state VARCHAR(8) CHECK (health_state IN ('UP', 'DOWN'));
ALTER TABLE assistant_provider ADD COLUMN health_checked_at TIMESTAMP WITH TIME ZONE;
ALTER TABLE assistant_provider ADD COLUMN down_until TIMESTAMP WITH TIME ZONE;
ALTER TABLE assistant_turn ADD COLUMN route_decision VARCHAR(16);
ALTER TABLE assistant_turn ADD COLUMN route_source VARCHAR(16);
ALTER TABLE assistant_turn ADD COLUMN fallback_count INTEGER DEFAULT 0;
ALTER TABLE assistant_turn ADD COLUMN provider_id INTEGER REFERENCES assistant_provider(id);


-- P-f: embedding registry and admin-only knowledge-source configuration.
ALTER TABLE assistant_model ADD COLUMN model_kind VARCHAR(16) DEFAULT 'CHAT'
   CHECK (model_kind IN ('CHAT', 'EMBEDDING'));
ALTER TABLE assistant_model ADD COLUMN embedding_dimensions INTEGER
   CHECK (embedding_dimensions > 0);

CREATE TABLE assistant_rag_source (
   id SERIAL PRIMARY KEY,
   name VARCHAR(200) NOT NULL,
   enabled BOOLEAN DEFAULT TRUE,
   table_name VARCHAR(200) NOT NULL,
   field_names VARCHAR(500) NOT NULL,
   title_field_name VARCHAR(200),
   filter_json TEXT,
   embedding_model_id INTEGER NOT NULL REFERENCES assistant_model(id),
   chunk_chars INTEGER DEFAULT 1500 CHECK (chunk_chars BETWEEN 100 AND 16000),
   chunk_overlap_chars INTEGER DEFAULT 200 CHECK (chunk_overlap_chars >= 0 AND chunk_overlap_chars < chunk_chars),
   last_indexed_at TIMESTAMPTZ,
   last_index_status VARCHAR(200),
   last_index_error VARCHAR(500),
   indexed_record_count INTEGER,
   indexed_chunk_count INTEGER,
   create_date TIMESTAMPTZ,
   modify_date TIMESTAMPTZ
);

-- Optional pgvector part: reference only. Run the extension step as a DBA.
-- PgVectorStore.ensureSchema() performs the remainder for the host's schema/N.
-- Use a distinct schema per QQQ instance; match N to the embedding model.
-- CREATE EXTENSION IF NOT EXISTS vector;
-- CREATE SCHEMA IF NOT EXISTS assistant_rag;
-- CREATE TABLE IF NOT EXISTS assistant_rag.assistant_rag_chunk (
--    id BIGSERIAL PRIMARY KEY,
--    source_id INT NOT NULL,
--    table_name TEXT NOT NULL,
--    record_pk TEXT NOT NULL,
--    field_name TEXT NOT NULL,
--    chunk_index INT NOT NULL,
--    content_hash CHAR(64) NOT NULL,
--    embedding vector(1024) NOT NULL,
--    indexed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
--    UNIQUE(source_id, table_name, record_pk, field_name, chunk_index)
-- );
-- CREATE INDEX IF NOT EXISTS assistant_rag_chunk_cosine
--    ON assistant_rag.assistant_rag_chunk USING hnsw (embedding vector_cosine_ops);

-- P-g: designated-user scheduled assistant tasks. Install before enabling jobs.
CREATE TABLE assistant_scheduled_task (
   id SERIAL PRIMARY KEY,
   name VARCHAR(200) NOT NULL,
   enabled BOOLEAN NOT NULL DEFAULT FALSE,
   prompt TEXT NOT NULL CHECK (char_length(prompt) BETWEEN 1 AND 4000),
   run_as_user_id VARCHAR(200) NOT NULL,
   credential_env_name VARCHAR(200) NOT NULL,
   credential_status VARCHAR(200), -- computed; always NULL in stored rows
   profile_id INTEGER REFERENCES assistant_profile(id),
   cron_expression VARCHAR(200) NOT NULL,
   timezone VARCHAR(100) NOT NULL DEFAULT 'UTC',
   next_run_at TIMESTAMPTZ,
   output_mode VARCHAR(20) NOT NULL DEFAULT 'RECORD' CHECK (output_mode IN ('RECORD', 'NOTIFICATION', 'BOTH')),
   write_mode_requested BOOLEAN NOT NULL DEFAULT FALSE,
   write_mode_approved_by VARCHAR(200),
   write_mode_approved_at TIMESTAMPTZ,
   write_mode_approval_hash VARCHAR(64),
   max_cost_usd_per_run NUMERIC(18,6) CHECK (max_cost_usd_per_run >= 0),
   last_run_at TIMESTAMPTZ,
   last_run_status VARCHAR(20),
   last_run_error VARCHAR(500),
   create_date TIMESTAMPTZ,
   modify_date TIMESTAMPTZ
);
CREATE INDEX assistant_scheduled_task_due ON assistant_scheduled_task(next_run_at) WHERE enabled;

ALTER TABLE assistant_conversation ADD COLUMN scheduled_task_id INTEGER REFERENCES assistant_scheduled_task(id);
ALTER TABLE assistant_conversation ADD COLUMN channel VARCHAR(64) NOT NULL DEFAULT 'panel';
ALTER TABLE assistant_tool_call ADD COLUMN client_channel VARCHAR(64);

CREATE TABLE assistant_scheduled_run (
   id SERIAL PRIMARY KEY,
   task_id INTEGER NOT NULL REFERENCES assistant_scheduled_task(id),
   user_id VARCHAR(200) NOT NULL,
   scheduled_for TIMESTAMPTZ NOT NULL,
   started_at TIMESTAMPTZ,
   ended_at TIMESTAMPTZ,
   status VARCHAR(20) NOT NULL CHECK (status IN ('RUNNING', 'OK', 'FAILED', 'SKIPPED', 'BLOCKED')),
   conversation_id INTEGER REFERENCES assistant_conversation(id) ON DELETE SET NULL,
   turn_id INTEGER REFERENCES assistant_turn(id) ON DELETE SET NULL,
   summary TEXT,
   cost_micros BIGINT,
   error TEXT,
   create_date TIMESTAMPTZ,
   modify_date TIMESTAMPTZ,
   UNIQUE (task_id, scheduled_for)
);
CREATE INDEX assistant_scheduled_run_owner ON assistant_scheduled_run(user_id, started_at);

-- X1/Y1: encoded audit filters can exceed the old 500-character record links.
ALTER TABLE assistant_tool_call ALTER COLUMN audit_href TYPE TEXT;


-- Durable turns: apply this additive block before deploying the new runtime.
-- Like earlier additions, fresh installs run the CREATEs followed by ALTERs.
ALTER TABLE assistant_turn ADD COLUMN heartbeat_at TIMESTAMP;
ALTER TABLE assistant_turn ADD COLUMN lease_owner VARCHAR(100);
ALTER TABLE assistant_turn ADD COLUMN cancel_requested_at TIMESTAMP;
ALTER TABLE assistant_turn ADD COLUMN phase VARCHAR(64);
ALTER TABLE assistant_turn ADD COLUMN concurrency_mode VARCHAR(16);
CREATE INDEX assistant_turn_user_status ON assistant_turn (user_id, status);

-- Fence concurrent orphan reapers at the database boundary. A turn ends once.
-- Check for pre-existing duplicate message.end rows before applying this index.
CREATE UNIQUE INDEX assistant_turn_event_terminal ON assistant_turn_event (turn_id)
   WHERE event_type = 'message.end';

-- Index audit (2026-10-14): lookup, retention and FK paths with no leading index.
-- ScheduledTurn.cost reads usage by turn on every scheduled run.
CREATE INDEX IF NOT EXISTS assistant_usage_turn ON assistant_usage (turn_id);
-- Retention: deleteTurnEventsBefore, clearToolResultsBefore and listConversationsInactiveSince filter by date.
CREATE INDEX IF NOT EXISTS assistant_turn_event_create_date ON assistant_turn_event (create_date);
CREATE INDEX IF NOT EXISTS assistant_tool_call_create_date ON assistant_tool_call (create_date);
CREATE INDEX IF NOT EXISTS assistant_conversation_modify_date ON assistant_conversation (modify_date);
-- ON DELETE SET NULL children: deleting a conversation or turn would scan scheduled runs without these.
CREATE INDEX IF NOT EXISTS assistant_scheduled_run_conversation ON assistant_scheduled_run (conversation_id);
CREATE INDEX IF NOT EXISTS assistant_scheduled_run_turn ON assistant_scheduled_run (turn_id);

-- Foreign keys (2026-10-14). Run on new and existing databases; every step is guarded
-- and re-runnable. The rules follow how the qbit deletes:
-- * AssistantStore.deleteConversation (also the retention purge) deletes turn events,
--   tool calls, feedback, messages, turns, data audit and pending actions, then the
--   conversation. The conversation's own detail rows CASCADE, so an admin delete of a
--   conversation cleans up the same way.
-- * The message <-> turn cycle (turn.user_message_id / assistant_message_id and
--   message.turn_id) and the other optional pointers are SET NULL. Rows are inserted
--   parent first, so nothing is deferred.
-- * assistant_usage keeps its cost rows when a conversation goes (SET NULL), and the
--   data audit trail is never cascaded into (SET NULL).
-- * Provider references from usage, budget accounting and the data audit are NO ACTION
--   (like assistant_turn.provider_id): disable a provider with history, do not delete
--   it. A provider's model rows are its own (CASCADE).
-- * assistant_budget_usage.usage_id has no foreign key on purpose: the projection
--   outlives its usage row so budgets keep counting the spend.
-- Rows that already point at a missing parent are recorded in qqq_fk_quarantine first,
-- then nulled (nullable columns) or deleted with the full row recorded (NOT NULL).
CREATE INDEX IF NOT EXISTS assistant_message_parent_message ON assistant_message (parent_message_id);
CREATE INDEX IF NOT EXISTS assistant_message_turn ON assistant_message (turn_id);
CREATE INDEX IF NOT EXISTS assistant_turn_user_message ON assistant_turn (user_message_id);
CREATE INDEX IF NOT EXISTS assistant_turn_assistant_message ON assistant_turn (assistant_message_id);
CREATE INDEX IF NOT EXISTS assistant_turn_provider ON assistant_turn (provider_id);
CREATE INDEX IF NOT EXISTS assistant_tool_call_pending_action ON assistant_tool_call (pending_action_id);
CREATE INDEX IF NOT EXISTS assistant_feedback_message ON assistant_feedback (message_id);
CREATE INDEX IF NOT EXISTS assistant_pending_action_turn ON assistant_pending_action (turn_id);
CREATE INDEX IF NOT EXISTS assistant_data_audit_provider ON assistant_data_audit (provider_id);
CREATE INDEX IF NOT EXISTS assistant_budget_usage_provider ON assistant_budget_usage (provider_id);
CREATE INDEX IF NOT EXISTS assistant_profile_skill_skill ON assistant_profile_skill (skill_id);
CREATE INDEX IF NOT EXISTS assistant_budget_downgrade_model ON assistant_budget (downgrade_model_id);
CREATE INDEX IF NOT EXISTS assistant_conversation_scheduled_task ON assistant_conversation (scheduled_task_id);
CREATE INDEX IF NOT EXISTS assistant_model_fallback_model ON assistant_model (fallback_model_id);
CREATE INDEX IF NOT EXISTS assistant_profile_simple_model ON assistant_profile (simple_model_id);
CREATE INDEX IF NOT EXISTS assistant_profile_complex_model ON assistant_profile (complex_model_id);
CREATE INDEX IF NOT EXISTS assistant_profile_classifier_model ON assistant_profile (classifier_model_id);
CREATE INDEX IF NOT EXISTS assistant_rag_source_embedding_model ON assistant_rag_source (embedding_model_id);
CREATE INDEX IF NOT EXISTS assistant_scheduled_task_profile ON assistant_scheduled_task (profile_id);

CREATE TABLE IF NOT EXISTS qqq_fk_quarantine (id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, table_name VARCHAR(100) NOT NULL, column_name VARCHAR(100) NOT NULL, row_id VARCHAR(100), missing_parent_id VARCHAR(100), action VARCHAR(20) NOT NULL, row_data TEXT, changeset VARCHAR(200), quarantined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

DO $$
DECLARE
   r   RECORD;
   old TEXT;
BEGIN
   FOR r IN SELECT * FROM (VALUES
      ('assistant_message',        'conversation_id',      'assistant_conversation',   'CASCADE',   TRUE),
      ('assistant_message',        'parent_message_id',    'assistant_message',        'SET NULL',  TRUE),
      ('assistant_message',        'turn_id',              'assistant_turn',           'SET NULL',  TRUE),
      ('assistant_turn',           'conversation_id',      'assistant_conversation',   'CASCADE',   TRUE),
      ('assistant_turn',           'user_message_id',      'assistant_message',        'SET NULL',  TRUE),
      ('assistant_turn',           'assistant_message_id', 'assistant_message',        'SET NULL',  TRUE),
      ('assistant_turn_event',     'turn_id',              'assistant_turn',           'CASCADE',   TRUE),
      ('assistant_tool_call',      'conversation_id',      'assistant_conversation',   'CASCADE',   TRUE),
      ('assistant_tool_call',      'turn_id',              'assistant_turn',           'CASCADE',   TRUE),
      ('assistant_tool_call',      'pending_action_id',    'assistant_pending_action', 'SET NULL',  TRUE),
      ('assistant_feedback',       'conversation_id',      'assistant_conversation',   'CASCADE',   TRUE),
      ('assistant_feedback',       'message_id',           'assistant_message',        'CASCADE',   TRUE),
      ('assistant_pending_action', 'conversation_id',      'assistant_conversation',   'CASCADE',   TRUE),
      ('assistant_pending_action', 'turn_id',              'assistant_turn',           'SET NULL',  TRUE),
      ('assistant_data_audit',     'conversation_id',      'assistant_conversation',   'SET NULL',  TRUE),
      ('assistant_data_audit',     'turn_id',              'assistant_turn',           'SET NULL',  TRUE),
      ('assistant_usage',          'conversation_id',      'assistant_conversation',   'SET NULL',  TRUE),
      ('assistant_usage',          'turn_id',              'assistant_turn',           'SET NULL',  TRUE),
      ('assistant_usage',          'provider_id',          'assistant_provider',       'NO ACTION', TRUE),
      ('assistant_data_audit',     'provider_id',          'assistant_provider',       'NO ACTION', TRUE),
      ('assistant_budget_usage',   'provider_id',          'assistant_provider',       'NO ACTION', TRUE),
      ('assistant_profile_skill',  'profile_id',           'assistant_profile',        'CASCADE',   FALSE),
      ('assistant_profile_skill',  'skill_id',             'assistant_skill',          'CASCADE',   FALSE)
   ) AS v(child, col, parent, rule, nullable)
   LOOP
      IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema = current_schema() AND LOWER(constraint_name) = 'fk_' || r.child || '_' || r.col) THEN
         IF r.nullable THEN
            EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, changeset) '
               || 'SELECT %L, %L, c.id::text, c.%I::text, ''NULLED'', ''qbit-assistant 2026-10-14 foreign keys'' FROM %I c '
               || 'WHERE c.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = c.%I)', r.child, r.col, r.col, r.child, r.col, r.parent, r.col);
            EXECUTE format('UPDATE %I c SET %I = NULL WHERE c.%I IS NOT NULL AND NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = c.%I)', r.child, r.col, r.col, r.parent, r.col);
         ELSE
            EXECUTE format('INSERT INTO qqq_fk_quarantine (table_name, column_name, row_id, missing_parent_id, action, row_data, changeset) '
               || 'SELECT %L, %L, c.id::text, c.%I::text, ''DELETED'', row_to_json(c)::text, ''qbit-assistant 2026-10-14 foreign keys'' FROM %I c '
               || 'WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = c.%I)', r.child, r.col, r.col, r.child, r.parent, r.col);
            EXECUTE format('DELETE FROM %I c WHERE NOT EXISTS (SELECT 1 FROM %I p WHERE p.id = c.%I)', r.child, r.parent, r.col);
         END IF;
         EXECUTE format('ALTER TABLE %I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (id) ON DELETE %s', r.child, 'fk_' || r.child || '_' || r.col, r.col, r.parent, r.rule);
      END IF;
   END LOOP;

   -- assistant_model.provider_id: the inline NO ACTION key becomes CASCADE, named as on kof22.
   IF NOT EXISTS (SELECT 1 FROM information_schema.table_constraints WHERE constraint_schema = current_schema() AND LOWER(constraint_name) = 'fk_assistant_model_provider') THEN
      FOR old IN SELECT conname FROM pg_constraint
         WHERE contype = 'f' AND conrelid = 'assistant_model'::regclass AND confrelid = 'assistant_provider'::regclass
      LOOP
         EXECUTE format('ALTER TABLE assistant_model DROP CONSTRAINT %I', old);
      END LOOP;
      ALTER TABLE assistant_model ADD CONSTRAINT fk_assistant_model_provider FOREIGN KEY (provider_id) REFERENCES assistant_provider (id) ON DELETE CASCADE;
   END IF;
END $$;

-- Transcript redaction: tokens can expand beyond the original payload bounds.
-- Apply to existing databases as well; SET DATA TYPE is also valid in H2.
ALTER TABLE assistant_turn ALTER COLUMN stop_reason SET DATA TYPE TEXT;
ALTER TABLE assistant_tool_call ALTER COLUMN tool_call_id SET DATA TYPE TEXT;
ALTER TABLE assistant_feedback ALTER COLUMN reason SET DATA TYPE TEXT;
ALTER TABLE assistant_usage ALTER COLUMN request_id SET DATA TYPE TEXT;
ALTER TABLE assistant_pending_action ALTER COLUMN tool_call_id SET DATA TYPE TEXT;
ALTER TABLE assistant_data_audit ALTER COLUMN reason SET DATA TYPE TEXT;
ALTER TABLE assistant_budget_override ALTER COLUMN reason SET DATA TYPE TEXT;
ALTER TABLE assistant_scheduled_run ALTER COLUMN summary SET DATA TYPE TEXT;
ALTER TABLE assistant_scheduled_run ALTER COLUMN error SET DATA TYPE TEXT;

-- Fail soft (UI API extension assistant 1.5.0): errors the panel reports through
-- POST /qqq/v1/assistant/client-errors. Owner-locked like the other user rows; the
-- message and stacks are scrubbed and bounded before they are stored. The ids are
-- kept only when they are the reporting user's own rows, and SET NULL when those go.
-- Retention deletes rows older than toolResultRetentionDays. Safe to re-run.
CREATE TABLE IF NOT EXISTS assistant_client_error
(
   id SERIAL PRIMARY KEY,
   user_id VARCHAR(255),
   scope VARCHAR(64),
   message TEXT,
   error_name VARCHAR(100),
   stack TEXT,
   component_stack TEXT,
   request_id VARCHAR(128),
   route VARCHAR(300),
   conversation_id INTEGER CONSTRAINT fk_assistant_client_error_conversation_id REFERENCES assistant_conversation (id) ON DELETE SET NULL,
   message_id INTEGER CONSTRAINT fk_assistant_client_error_message_id REFERENCES assistant_message (id) ON DELETE SET NULL,
   turn_id INTEGER CONSTRAINT fk_assistant_client_error_turn_id REFERENCES assistant_turn (id) ON DELETE SET NULL,
   part_type VARCHAR(64),
   render_type VARCHAR(64),
   app_version VARCHAR(64),
   occurred_at TIMESTAMP WITH TIME ZONE,
   create_date TIMESTAMP WITH TIME ZONE
);
CREATE INDEX IF NOT EXISTS assistant_client_error_user_id ON assistant_client_error (user_id);
CREATE INDEX IF NOT EXISTS assistant_client_error_create_date ON assistant_client_error (create_date);
CREATE INDEX IF NOT EXISTS assistant_client_error_conversation ON assistant_client_error (conversation_id);
CREATE INDEX IF NOT EXISTS assistant_client_error_message ON assistant_client_error (message_id);
CREATE INDEX IF NOT EXISTS assistant_client_error_turn ON assistant_client_error (turn_id);

-- Provider setup state: SETTING_UP marks a provider an admin is still building. The
-- assistant never routes to it and its provider and model health checks do not run.
-- Existing rows (and NULL) behave as ACTIVE, exactly as before. Safe to re-run.
ALTER TABLE assistant_provider ADD COLUMN IF NOT EXISTS setup_state VARCHAR(16) DEFAULT 'ACTIVE'
   CONSTRAINT ck_assistant_provider_setup_state CHECK (setup_state IN ('ACTIVE', 'SETTING_UP'));

-- Pending actions (UI API extension assistant 1.9.0): what a card shows of the record as it
-- was, what kind of change is proposed, and the single decision of an action.
-- action_kind: record_create, record_update, record_delete, or the proposing tool's name.
-- before_json: the values the card showed before the change, after the exposure rules of
-- get_record (encrypted in HIPAA mode, like params_json). record_version: a digest of the
-- record as the proposing user could read it, never its values; a confirmed update or delete
-- runs only while the record still has that version.
-- assistant_pending_action_claim: one row per decided action. Its unique key is what lets
-- exactly one request confirm or cancel an action, on one server or several; do not drop it.
-- Safe to re-run.
ALTER TABLE assistant_pending_action ADD COLUMN IF NOT EXISTS action_kind VARCHAR(64);
ALTER TABLE assistant_pending_action ADD COLUMN IF NOT EXISTS before_json TEXT;
ALTER TABLE assistant_pending_action ADD COLUMN IF NOT EXISTS record_version VARCHAR(64);
CREATE INDEX IF NOT EXISTS assistant_pending_action_user_status ON assistant_pending_action (user_id, status);
CREATE INDEX IF NOT EXISTS assistant_pending_action_create_date ON assistant_pending_action (create_date);

CREATE TABLE IF NOT EXISTS assistant_pending_action_claim
(
   id SERIAL PRIMARY KEY,
   pending_action_id INTEGER NOT NULL CONSTRAINT fk_assistant_pending_action_claim_pending_action_id REFERENCES assistant_pending_action (id) ON DELETE CASCADE,
   user_id VARCHAR(255),
   decision VARCHAR(16),
   create_date TIMESTAMP WITH TIME ZONE,
   CONSTRAINT assistant_pending_action_claim_action UNIQUE (pending_action_id)
);
CREATE INDEX IF NOT EXISTS assistant_pending_action_claim_user_id ON assistant_pending_action_claim (user_id);