-- sources
CREATE TABLE IF NOT EXISTS sources (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  provider_type VARCHAR(50) NOT NULL,
  status VARCHAR(20) NOT NULL,
  config JSONB NOT NULL,
  auth_method VARCHAR(50),
  last_health_check TIMESTAMPTZ,
  last_scan_at TIMESTAMPTZ,
  next_scan_at TIMESTAMPTZ,
  schedule_cron VARCHAR(100),
  created_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- source_secrets
CREATE TABLE IF NOT EXISTS source_secrets (
  id UUID PRIMARY KEY,
  source_id UUID REFERENCES sources(id) ON DELETE CASCADE,
  secret_type VARCHAR(50),
  vault_reference VARCHAR(255) NOT NULL,
  expires_at TIMESTAMPTZ,
  rotated_at TIMESTAMPTZ
);

-- ingests
CREATE TABLE IF NOT EXISTS ingests (
  id UUID PRIMARY KEY,
  source_id UUID REFERENCES sources(id),
  type VARCHAR(20) NOT NULL,
  status VARCHAR(20),
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  files_processed INT DEFAULT 0,
  bytes_processed BIGINT DEFAULT 0,
  errors_count INT DEFAULT 0,
  checkpoint JSONB,
  created_by UUID
);

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

DROP TRIGGER IF EXISTS trg_sources_updated ON sources;
CREATE TRIGGER trg_sources_updated BEFORE UPDATE ON sources
FOR EACH ROW EXECUTE FUNCTION set_updated_at();


