-- Auto-generated migration for sqlite backend
-- Generated by Orion migration_gen

CREATE TABLE IF NOT EXISTS "workflows" ( "workflow_id" text NOT NULL, "version" integer NOT NULL, "name" text NOT NULL, "description" text, "priority" integer NOT NULL DEFAULT 0, "status" text NOT NULL DEFAULT 'draft', "rollout_percentage" integer NOT NULL DEFAULT 100, "condition_json" text NOT NULL DEFAULT 'true', "tasks_json" text NOT NULL DEFAULT '[]', "tags" text NOT NULL DEFAULT '[]', "continue_on_error" boolean NOT NULL DEFAULT FALSE, "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("workflow_id", "version") );

CREATE TABLE IF NOT EXISTS "channels" ( "channel_id" text NOT NULL, "version" integer NOT NULL, "name" text NOT NULL, "description" text, "channel_type" text NOT NULL, "protocol" text NOT NULL, "methods" text, "route_pattern" text, "topic" text, "consumer_group" text, "transport_config_json" text NOT NULL DEFAULT '{}', "workflow_id" text, "config_json" text NOT NULL DEFAULT '{}', "status" text NOT NULL DEFAULT 'draft', "priority" integer NOT NULL DEFAULT 0, "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("channel_id", "version") );

CREATE TABLE IF NOT EXISTS "connectors" ( "id" text NOT NULL PRIMARY KEY, "name" text NOT NULL UNIQUE, "connector_type" text NOT NULL, "config_json" text NOT NULL DEFAULT '{}', "enabled" boolean NOT NULL DEFAULT TRUE, "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE IF NOT EXISTS "traces" ( "id" text NOT NULL PRIMARY KEY, "channel" text NOT NULL DEFAULT 'default', "channel_id" text, "mode" text NOT NULL DEFAULT 'sync', "status" text NOT NULL DEFAULT 'pending', "input_json" text, "result_json" text, "error_message" text, "duration_ms" double, "started_at" timestamp_text, "completed_at" timestamp_text, "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP, "updated_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP );

CREATE TABLE IF NOT EXISTS "trace_dlq" (
    "id" text NOT NULL PRIMARY KEY,
    "trace_id" text NOT NULL,
    "channel" text NOT NULL,
    "payload_json" text NOT NULL,
    "metadata_json" text NOT NULL DEFAULT '{}',
    "error_message" text NOT NULL,
    "retry_count" integer NOT NULL DEFAULT 0,
    "max_retries" integer NOT NULL DEFAULT 5,
    "next_retry_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX "idx_workflows_status" ON "workflows" ("status");
CREATE INDEX "idx_workflows_workflow_id" ON "workflows" ("workflow_id");
CREATE INDEX "idx_workflows_priority_name" ON "workflows" ("priority" DESC, "name" ASC);
CREATE INDEX "idx_channels_status" ON "channels" ("status");
CREATE INDEX "idx_channels_name" ON "channels" ("name");
CREATE INDEX "idx_channels_type_status" ON "channels" ("channel_type", "status");
CREATE INDEX "idx_channels_channel_id" ON "channels" ("channel_id");
CREATE INDEX "idx_channels_route" ON "channels" ("route_pattern");
CREATE INDEX "idx_channels_topic" ON "channels" ("topic");
CREATE INDEX "idx_channels_workflow" ON "channels" ("workflow_id");
CREATE INDEX "idx_traces_status" ON "traces" ("status");
CREATE INDEX "idx_traces_status_channel" ON "traces" ("status", "channel");
CREATE INDEX "idx_traces_channel" ON "traces" ("channel");
CREATE INDEX "idx_traces_mode" ON "traces" ("mode");
CREATE INDEX "idx_traces_created_at" ON "traces" ("created_at");
CREATE INDEX "idx_traces_channel_id" ON "traces" ("channel_id");

CREATE INDEX idx_channels_route_partial ON channels(route_pattern) WHERE route_pattern IS NOT NULL;
CREATE INDEX idx_channels_topic_partial ON channels(topic) WHERE topic IS NOT NULL;
CREATE INDEX idx_channels_workflow_partial ON channels(workflow_id) WHERE workflow_id IS NOT NULL;
CREATE INDEX idx_traces_channel_id_partial ON traces(channel_id) WHERE channel_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS "audit_logs" (
    "id" text NOT NULL PRIMARY KEY,
    "principal" text NOT NULL,
    "action" text NOT NULL,
    "resource_type" text NOT NULL,
    "resource_id" text NOT NULL,
    "details" text,
    "created_at" timestamp_text NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX "idx_trace_dlq_next_retry" ON "trace_dlq" ("next_retry_at") WHERE "retry_count" < "max_retries";
CREATE INDEX "idx_trace_dlq_channel" ON "trace_dlq" ("channel");
CREATE INDEX "idx_audit_logs_created_at" ON "audit_logs" ("created_at");
CREATE INDEX "idx_audit_logs_resource" ON "audit_logs" ("resource_type", "resource_id");

-- Latest version per workflow_id
CREATE VIEW current_workflows AS
SELECT w.*
FROM workflows w
INNER JOIN (
  SELECT workflow_id, MAX(version) AS max_version
  FROM workflows
  GROUP BY workflow_id
) latest ON w.workflow_id = latest.workflow_id AND w.version = latest.max_version;

-- Latest version per channel_id
CREATE VIEW current_channels AS
SELECT c.*
FROM channels c
INNER JOIN (
  SELECT channel_id, MAX(version) AS max_version
  FROM channels
  GROUP BY channel_id
) latest ON c.channel_id = latest.channel_id AND c.version = latest.max_version;

-- Auto-update updated_at
CREATE TRIGGER trg_workflows_updated_at AFTER UPDATE ON workflows
BEGIN
  UPDATE workflows SET updated_at = datetime('now')
  WHERE workflow_id = NEW.workflow_id AND version = NEW.version;
END;

CREATE TRIGGER trg_channels_updated_at AFTER UPDATE ON channels
BEGIN
  UPDATE channels SET updated_at = datetime('now')
  WHERE channel_id = NEW.channel_id AND version = NEW.version;
END;

CREATE TRIGGER trg_connectors_updated_at AFTER UPDATE ON connectors
BEGIN
  UPDATE connectors SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER trg_traces_updated_at AFTER UPDATE ON traces
BEGIN
  UPDATE traces SET updated_at = datetime('now') WHERE id = NEW.id;
END;

CREATE TRIGGER trg_trace_dlq_updated_at AFTER UPDATE ON trace_dlq
BEGIN
  UPDATE trace_dlq SET updated_at = datetime('now') WHERE id = NEW.id;
END;

-- Only one draft per workflow_id
CREATE TRIGGER trg_workflows_single_draft
BEFORE INSERT ON workflows
WHEN NEW.status = 'draft'
BEGIN
  SELECT RAISE(ABORT, 'Only one draft version allowed per workflow')
  WHERE EXISTS (
    SELECT 1 FROM workflows
    WHERE workflow_id = NEW.workflow_id AND status = 'draft'
  );
END;

-- Only one draft per channel_id
CREATE TRIGGER trg_channels_single_draft
BEFORE INSERT ON channels
WHEN NEW.status = 'draft'
BEGIN
  SELECT RAISE(ABORT, 'Only one draft version allowed per channel')
  WHERE EXISTS (
    SELECT 1 FROM channels
    WHERE channel_id = NEW.channel_id AND status = 'draft'
  );
END;

-- Prevent content changes on active workflows
CREATE TRIGGER trg_workflows_active_immutable
BEFORE UPDATE ON workflows
WHEN OLD.status = 'active'
  AND NEW.status = 'active'
  AND (OLD.name != NEW.name
    OR OLD.description IS NOT NEW.description
    OR OLD.priority != NEW.priority
    OR OLD.condition_json != NEW.condition_json
    OR OLD.tasks_json != NEW.tasks_json
    OR OLD.tags != NEW.tags
    OR OLD.continue_on_error != NEW.continue_on_error)
BEGIN
  SELECT RAISE(ABORT, 'Cannot modify content of active workflows');
END;

-- Prevent content changes on active channels
CREATE TRIGGER trg_channels_active_immutable
BEFORE UPDATE ON channels
WHEN OLD.status = 'active'
  AND NEW.status = 'active'
  AND (OLD.name != NEW.name
    OR OLD.description IS NOT NEW.description
    OR OLD.channel_type != NEW.channel_type
    OR OLD.protocol != NEW.protocol
    OR OLD.methods IS NOT NEW.methods
    OR OLD.route_pattern IS NOT NEW.route_pattern
    OR OLD.topic IS NOT NEW.topic
    OR OLD.consumer_group IS NOT NEW.consumer_group
    OR OLD.workflow_id IS NOT NEW.workflow_id
    OR OLD.config_json != NEW.config_json)
BEGIN
  SELECT RAISE(ABORT, 'Cannot modify content of active channels');
END;
