-- Phase 1b: slack_workspace, slack_connection, slack_channel, slack_user_group, slack_template,
-- slack_message and slack_message_attempt. Apply before producing the qbit.
-- Host tenant (security) columns, configured through SlackQBitConfig.securityFields, must be
-- added by the host to slack_workspace and slack_template (children inherit them by join).
-- bot_token and user_token hold AES-256-GCM ciphertext (v1:keyId:base64), never plaintext.

CREATE TABLE slack_workspace (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   team_id VARCHAR(500) NOT NULL,
   team_name VARCHAR(500),
   enterprise_id VARCHAR(500),
   app_id VARCHAR(500),
   tenant_key VARCHAR(500),
   installed_by_user_id VARCHAR(500),
   installed_at TIMESTAMP,
   bot_user_id VARCHAR(500),
   bot_scopes TEXT,
   bot_token VARCHAR(2000),
   bot_token_fingerprint VARCHAR(500),
   status VARCHAR(500),
   readiness_status VARCHAR(500),
   readiness_checked_at TIMESTAMP,
   readiness_summary VARCHAR(500),
   health VARCHAR(500),
   consecutive_failures INTEGER,
   rate_per_minute INTEGER,
   UNIQUE (team_id, tenant_key)
);

CREATE TABLE slack_connection (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   user_id VARCHAR(500) NOT NULL,
   workspace_id INTEGER NOT NULL,
   slack_user_id VARCHAR(500),
   slack_user_name VARCHAR(500),
   user_token VARCHAR(2000),
   user_token_fingerprint VARCHAR(500),
   user_scopes TEXT,
   status VARCHAR(500),
   connected_at TIMESTAMP,
   last_verified_at TIMESTAMP,
   revoked_at TIMESTAMP,
   last_error VARCHAR(500),
   UNIQUE (user_id, workspace_id)
);

CREATE TABLE slack_channel (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500) NOT NULL,
   label VARCHAR(500),
   workspace_id INTEGER NOT NULL,
   slack_channel_id VARCHAR(500),
   slack_channel_name VARCHAR(500),
   is_private BOOLEAN,
   bot_is_member BOOLEAN,
   is_archived BOOLEAN,
   enabled BOOLEAN,
   description TEXT,
   checked_at TIMESTAMP,
   UNIQUE (workspace_id, name)
);

CREATE TABLE slack_user_group (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500) NOT NULL,
   label VARCHAR(500),
   workspace_id INTEGER NOT NULL,
   slack_usergroup_id VARCHAR(500),
   handle VARCHAR(500),
   channel_id VARCHAR(500),
   enabled BOOLEAN,
   UNIQUE (workspace_id, name)
);

CREATE TABLE slack_template (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   name VARCHAR(500) NOT NULL,
   label VARCHAR(500),
   description TEXT,
   category VARCHAR(500),
   enabled BOOLEAN,
   tenant_key VARCHAR(500),
   text TEXT,
   blocks_template TEXT,
   variables_schema TEXT,
   UNIQUE (name)
);

CREATE TABLE slack_message (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   owner_user_id VARCHAR(500) NOT NULL,
   workspace_id INTEGER NOT NULL,
   connection_id INTEGER,
   post_as VARCHAR(500),
   operation VARCHAR(500),
   target_type VARCHAR(500),
   target_ref VARCHAR(500),
   slack_channel_id VARCHAR(500),
   channel_label VARCHAR(500),
   status VARCHAR(500),
   category VARCHAR(500),
   template_name VARCHAR(500),
   text TEXT,
   blocks_json TEXT,
   variables_snapshot TEXT,
   idempotency_key VARCHAR(500),
   parent_message_id INTEGER,
   thread_ts VARCHAR(500),
   target_message_id INTEGER,
   attachment_storage_ref VARCHAR(500),
   attachment_storage_table VARCHAR(500),
   file_reconcile_required BOOLEAN DEFAULT FALSE,
   attachment_name VARCHAR(500),
   attachment_bytes BIGINT,
   slack_file_id VARCHAR(500),
   slack_ts VARCHAR(500),
   permalink TEXT,
   attempt_count INTEGER,
   next_attempt_date TIMESTAMP,
   lease_expires_date TIMESTAMP,
   last_error_code VARCHAR(500),
   last_error VARCHAR(500),
   sent_date TIMESTAMP,
   dead_letter_date TIMESTAMP,
   linked_table_name VARCHAR(500),
   linked_record_id VARCHAR(500),
   sender_name VARCHAR(500),
   delivery_log_id VARCHAR(500),
   UNIQUE (idempotency_key)
);

CREATE TABLE slack_message_attempt (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   message_id INTEGER NOT NULL,
   attempt_number INTEGER NOT NULL,
   outcome VARCHAR(500),
   error_code VARCHAR(500),
   http_status INTEGER,
   retry_after_seconds INTEGER,
   posted_as VARCHAR(500),
   claimed_by VARCHAR(500),
   start_date TIMESTAMP,
   end_date TIMESTAMP,
   error VARCHAR(500),
   UNIQUE (message_id, attempt_number)
);

CREATE INDEX slack_message_due ON slack_message (status, next_attempt_date);
CREATE INDEX slack_message_owner ON slack_message (owner_user_id, status);
CREATE INDEX slack_message_workspace ON slack_message (workspace_id);
CREATE INDEX slack_message_parent ON slack_message (parent_message_id);
CREATE INDEX slack_connection_workspace ON slack_connection (workspace_id);
CREATE INDEX slack_channel_workspace ON slack_channel (workspace_id);
CREATE INDEX slack_user_group_workspace ON slack_user_group (workspace_id);

CREATE TABLE slack_interaction (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   create_date TIMESTAMP,
   modify_date TIMESTAMP,
   workspace_id INTEGER,
   message_id INTEGER,
   team_id VARCHAR(500),
   slack_user_id VARCHAR(500),
   user_id VARCHAR(500),
   action_name VARCHAR(500),
   delivery_key VARCHAR(500),
   outcome VARCHAR(500),
   error VARCHAR(500),
   completed_date TIMESTAMP,
   UNIQUE (delivery_key)
);
CREATE INDEX slack_interaction_message ON slack_interaction (message_id);
CREATE INDEX slack_interaction_workspace ON slack_interaction (workspace_id);