-- Minimal connector project seed.
-- One operator owns one Slack connection with one completed synchronization run.

CREATE TABLE users (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE provider_connections (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider text NOT NULL,
  external_account_id text NOT NULL,
  status text NOT NULL CHECK (status IN ('active', 'degraded', 'disconnected')),
  last_verified_at timestamptz,
  UNIQUE (provider, external_account_id)
);

CREATE TABLE sync_runs (
  id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  connection_id bigint NOT NULL REFERENCES provider_connections(id) ON DELETE CASCADE,
  status text NOT NULL CHECK (status IN ('queued', 'running', 'completed', 'failed')),
  records_read integer NOT NULL DEFAULT 0,
  records_written integer NOT NULL DEFAULT 0,
  started_at timestamptz NOT NULL,
  completed_at timestamptz
);

CREATE INDEX idx_provider_connections_user_id ON provider_connections(user_id);
CREATE INDEX idx_sync_runs_connection_id ON sync_runs(connection_id);

INSERT INTO users (email, name) VALUES
  ('maya.chen@example.test', 'Maya Chen');

INSERT INTO provider_connections (
  user_id,
  provider,
  external_account_id,
  status,
  last_verified_at
) VALUES
  (1, 'slack', 'T02NORTHSTAR', 'active', '2027-01-15T11:55:00.000Z');

INSERT INTO sync_runs (
  connection_id,
  status,
  records_read,
  records_written,
  started_at,
  completed_at
) VALUES
  (1, 'completed', 128, 128, '2027-01-15T11:50:00.000Z', '2027-01-15T11:50:08.000Z');

INSERT INTO supabase_migrations.schema_migrations (version, name, statements) VALUES
  (
    '20270115090000',
    'create_connector_baseline',
    'CREATE TABLE users (...); CREATE TABLE provider_connections (...); CREATE TABLE sync_runs (...);'
  );
