-- Automated evidence collection: integrations + run history + evidence source tracking

CREATE TABLE IF NOT EXISTS integrations (
  id           CHAR(36)                                          NOT NULL DEFAULT (UUID()),
  tenant_id    CHAR(36)                                          NOT NULL,
  type         VARCHAR(50)                                       NOT NULL,  -- github | okta | jira | aws | gcp | azure
  name         VARCHAR(255)                                      NOT NULL,
  credentials  TEXT                                              NOT NULL,  -- AES-256-GCM encrypted JSON
  config       JSON,                                                        -- non-sensitive config (org, domain, project…)
  status       ENUM('active','inactive','error')                 NOT NULL DEFAULT 'active',
  last_sync_at DATETIME,
  last_error   TEXT,
  created_at   DATETIME                                          NOT NULL DEFAULT NOW(),
  updated_at   DATETIME                                          NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  PRIMARY KEY (id),
  INDEX idx_integrations_tenant (tenant_id, type),
  CONSTRAINT fk_integrations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_runs (
  id               CHAR(36)                               NOT NULL DEFAULT (UUID()),
  integration_id   CHAR(36)                               NOT NULL,
  tenant_id        CHAR(36)                               NOT NULL,
  status           ENUM('running','completed','failed')   NOT NULL DEFAULT 'running',
  records_collected INT UNSIGNED                          NOT NULL DEFAULT 0,
  error_log        TEXT,
  started_at       DATETIME                               NOT NULL DEFAULT NOW(),
  completed_at     DATETIME,
  PRIMARY KEY (id),
  INDEX idx_ir_integration (integration_id, started_at),
  CONSTRAINT fk_ir_integration FOREIGN KEY (integration_id) REFERENCES integrations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE evidence
  ADD COLUMN source                ENUM('manual','integration') NOT NULL DEFAULT 'manual'   AFTER tags,
  ADD COLUMN source_integration_id CHAR(36)                     NULL                        AFTER source,
  ADD COLUMN source_ref            VARCHAR(500)                 NULL                        AFTER source_integration_id,
  ADD CONSTRAINT fk_evidence_integration
    FOREIGN KEY (source_integration_id) REFERENCES integrations(id) ON DELETE SET NULL;
