-- noteflow_init.sql
-- Creates schema + tables + placeholder data for local dev.

-- Extensions (safe to run repeatedly)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;
CREATE EXTENSION IF NOT EXISTS vector;

-- Schema
CREATE SCHEMA IF NOT EXISTS noteflow;
SET search_path TO noteflow, public;

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

--------------------------------------------------------------------------------
-- Identity / tenancy (future-ready)
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noteflow.workspaces (
  id          uuid PRIMARY KEY,
  slug        text UNIQUE,
  name        text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS noteflow.users (
  id           uuid PRIMARY KEY,
  email        citext UNIQUE,
  display_name text NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  metadata     jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS noteflow.workspace_memberships (
  workspace_id uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  user_id      uuid NOT NULL REFERENCES noteflow.users(id) ON DELETE CASCADE,
  role         text NOT NULL DEFAULT 'owner',
  created_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (workspace_id, user_id)
);

CREATE TRIGGER trg_workspaces_updated_at
BEFORE UPDATE ON noteflow.workspaces
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON noteflow.users
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

--------------------------------------------------------------------------------
-- Core domain (matches current project shape)
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noteflow.meetings (
  id            uuid PRIMARY KEY,
  -- Forward-looking fields: safe defaults for current code
  workspace_id  uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid
                REFERENCES noteflow.workspaces(id) ON DELETE RESTRICT,
  created_by_id uuid NULL DEFAULT '00000000-0000-0000-0000-000000000001'::uuid
                REFERENCES noteflow.users(id) ON DELETE SET NULL,

  title         varchar(255) NOT NULL,
  state         integer NOT NULL DEFAULT 1, -- 1..5 (Created..Error)
  created_at    timestamptz NOT NULL DEFAULT now(),
  started_at    timestamptz NULL,
  ended_at      timestamptz NULL,

  metadata      jsonb NOT NULL DEFAULT '{}'::jsonb,
  wrapped_dek   bytea NULL,
  asset_path    text NULL,

  deleted_at    timestamptz NULL
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'meetings_state_chk'
  ) THEN
    ALTER TABLE noteflow.meetings
      ADD CONSTRAINT meetings_state_chk
      CHECK (state BETWEEN 1 AND 5);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_meetings_workspace_created_at
  ON noteflow.meetings(workspace_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_meetings_state
  ON noteflow.meetings(state);

CREATE TABLE IF NOT EXISTS noteflow.segments (
  id                  bigserial PRIMARY KEY,
  meeting_id           uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  segment_id           integer NOT NULL, -- stable ordering within meeting
  text                 text NOT NULL,
  start_time           double precision NOT NULL,
  end_time             double precision NOT NULL,

  language             varchar(10) NOT NULL DEFAULT 'en',
  language_confidence  double precision NOT NULL DEFAULT 0,
  avg_logprob          double precision NOT NULL DEFAULT 0,
  no_speech_prob       double precision NOT NULL DEFAULT 0,

  embedding            vector(1536) NULL,

  speaker_id           varchar(50) NULL,
  speaker_confidence   double precision NOT NULL DEFAULT 0.0,

  created_at           timestamptz NOT NULL DEFAULT now()
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'segments_unique_per_meeting'
  ) THEN
    ALTER TABLE noteflow.segments
      ADD CONSTRAINT segments_unique_per_meeting
      UNIQUE (meeting_id, segment_id);
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_segments_meeting_id
  ON noteflow.segments(meeting_id);

CREATE INDEX IF NOT EXISTS idx_segments_meeting_time
  ON noteflow.segments(meeting_id, start_time);

-- Vector index (ivfflat is broadly supported; you can switch to hnsw later)
CREATE INDEX IF NOT EXISTS idx_segments_embedding_ivfflat
  ON noteflow.segments USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

CREATE TABLE IF NOT EXISTS noteflow.word_timings (
  id         bigserial PRIMARY KEY,
  segment_pk bigint NOT NULL REFERENCES noteflow.segments(id) ON DELETE CASCADE,
  word_index integer NOT NULL,
  word       varchar(255) NOT NULL,
  start_time double precision NOT NULL,
  end_time   double precision NOT NULL,
  probability double precision NOT NULL,
  UNIQUE (segment_pk, word_index)
);

CREATE INDEX IF NOT EXISTS idx_word_timings_segment_pk
  ON noteflow.word_timings(segment_pk);

CREATE TABLE IF NOT EXISTS noteflow.summaries (
  id                bigserial PRIMARY KEY,
  meeting_id         uuid NOT NULL UNIQUE REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  executive_summary  text NOT NULL DEFAULT '',
  generated_at       timestamptz NOT NULL DEFAULT now(),

  provider_name      text NOT NULL DEFAULT '',
  model_name         text NOT NULL DEFAULT '',
  tokens_used        integer NULL,
  latency_ms         double precision NULL,

  verification       jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS noteflow.key_points (
  id          bigserial PRIMARY KEY,
  summary_id  bigint NOT NULL REFERENCES noteflow.summaries(id) ON DELETE CASCADE,
  position    integer NOT NULL,
  text        text NOT NULL,
  segment_ids integer[] NOT NULL DEFAULT '{}'::integer[],
  start_time  double precision NOT NULL DEFAULT 0,
  end_time    double precision NOT NULL DEFAULT 0,
  UNIQUE (summary_id, position)
);

CREATE TABLE IF NOT EXISTS noteflow.action_items (
  id          bigserial PRIMARY KEY,
  summary_id  bigint NOT NULL REFERENCES noteflow.summaries(id) ON DELETE CASCADE,
  position    integer NOT NULL,
  text        text NOT NULL,
  segment_ids integer[] NOT NULL DEFAULT '{}'::integer[],
  start_time  double precision NOT NULL DEFAULT 0,
  end_time    double precision NOT NULL DEFAULT 0,

  assignee    text NOT NULL DEFAULT '',
  due_date    timestamptz NULL,
  priority    integer NOT NULL DEFAULT 0,
  UNIQUE (summary_id, position)
);

CREATE TABLE IF NOT EXISTS noteflow.annotations (
  id              bigserial PRIMARY KEY,
  annotation_id   uuid NOT NULL DEFAULT gen_random_uuid(),
  meeting_id      uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  annotation_type varchar(50) NOT NULL,
  text            text NOT NULL,
  start_time      double precision NOT NULL DEFAULT 0,
  end_time        double precision NOT NULL DEFAULT 0,
  segment_ids     integer[] NOT NULL DEFAULT '{}'::integer[],
  created_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (annotation_id)
);

CREATE INDEX IF NOT EXISTS idx_annotations_meeting_id
  ON noteflow.annotations(meeting_id);

CREATE TABLE IF NOT EXISTS noteflow.diarization_jobs (
  id              varchar(36) PRIMARY KEY,
  meeting_id      uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  status          integer NOT NULL DEFAULT 0,
  segments_updated integer NOT NULL DEFAULT 0,
  speaker_ids     text[] NOT NULL DEFAULT '{}'::text[],
  error_message   text NOT NULL DEFAULT '',
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE TRIGGER trg_diarization_jobs_updated_at
BEFORE UPDATE ON noteflow.diarization_jobs
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.streaming_diarization_turns (
  id          bigserial PRIMARY KEY,
  meeting_id  uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  speaker     varchar(50) NOT NULL,
  start_time  double precision NOT NULL,
  end_time    double precision NOT NULL,
  confidence  double precision NOT NULL DEFAULT 0.0,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_streaming_turns_meeting_time
  ON noteflow.streaming_diarization_turns(meeting_id, start_time);

--------------------------------------------------------------------------------
-- Named Entity Recognition (Sprint 4)
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noteflow.named_entities (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  meeting_id      uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  text            text NOT NULL,
  normalized_text text NOT NULL,
  category        varchar(50) NOT NULL,
  segment_ids     integer[] NOT NULL DEFAULT '{}'::integer[],
  confidence      double precision NOT NULL DEFAULT 0.0,
  is_pinned       boolean NOT NULL DEFAULT false,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_named_entities_meeting_text UNIQUE (meeting_id, normalized_text)
);

CREATE TRIGGER trg_named_entities_updated_at
BEFORE UPDATE ON noteflow.named_entities
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE INDEX IF NOT EXISTS idx_named_entities_meeting_id
  ON noteflow.named_entities(meeting_id);

CREATE INDEX IF NOT EXISTS idx_named_entities_category
  ON noteflow.named_entities(category);

--------------------------------------------------------------------------------
-- Webhook System (Sprint 6)
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noteflow.webhook_configs (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id  uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  name          varchar(255) NOT NULL DEFAULT 'Webhook',
  url           text NOT NULL,
  events        text[] NOT NULL DEFAULT '{}'::text[],
  secret        text NULL,
  enabled       boolean NOT NULL DEFAULT true,
  timeout_ms    integer NOT NULL DEFAULT 10000,
  max_retries   integer NOT NULL DEFAULT 3,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT chk_webhook_url_format CHECK (url ~ '^https?://')
);

CREATE TRIGGER trg_webhook_configs_updated_at
BEFORE UPDATE ON noteflow.webhook_configs
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE INDEX IF NOT EXISTS idx_webhook_configs_workspace_id
  ON noteflow.webhook_configs(workspace_id);

CREATE TABLE IF NOT EXISTS noteflow.webhook_deliveries (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  webhook_id      uuid NOT NULL REFERENCES noteflow.webhook_configs(id) ON DELETE CASCADE,
  event_type      text NOT NULL,
  payload         jsonb NOT NULL DEFAULT '{}'::jsonb,
  status_code     integer NULL,
  response_body   text NULL,
  error_message   text NULL,
  attempt_count   integer NOT NULL DEFAULT 1,
  duration_ms     integer NULL,
  delivered_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_webhook_id
  ON noteflow.webhook_deliveries(webhook_id, delivered_at DESC);

CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_event_type
  ON noteflow.webhook_deliveries(event_type, delivered_at DESC);

-- Existing style KV preferences (compat with current repo pattern) [oai_citation:11‡repomix-output.md](sediment://file_000000004f2c722fbba5e8a81215dabf)
CREATE TABLE IF NOT EXISTS noteflow.user_preferences (
  key        varchar(64) PRIMARY KEY,
  value      jsonb NOT NULL DEFAULT '{}'::jsonb,
  updated_at timestamptz NOT NULL DEFAULT now()
);

--------------------------------------------------------------------------------
-- Future-facing but safe additions: people, tags, tasks, integrations, settings
--------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS noteflow.persons (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  display_name text NOT NULL,
  email       citext NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb,
  UNIQUE (workspace_id, email)
);

CREATE TRIGGER trg_persons_updated_at
BEFORE UPDATE ON noteflow.persons
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.meeting_speakers (
  meeting_id uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  speaker_id varchar(50) NOT NULL,
  display_name text NULL,
  person_id uuid NULL REFERENCES noteflow.persons(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (meeting_id, speaker_id)
);

CREATE TABLE IF NOT EXISTS noteflow.tags (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  name        text NOT NULL,
  color       text NOT NULL DEFAULT '#888888',
  created_at  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (workspace_id, name)
);

CREATE TABLE IF NOT EXISTS noteflow.meeting_tags (
  meeting_id uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  tag_id     uuid NOT NULL REFERENCES noteflow.tags(id) ON DELETE CASCADE,
  PRIMARY KEY (meeting_id, tag_id)
);

CREATE TABLE IF NOT EXISTS noteflow.tasks (
  id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id      uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  meeting_id        uuid NULL REFERENCES noteflow.meetings(id) ON DELETE SET NULL,
  action_item_id    bigint NULL REFERENCES noteflow.action_items(id) ON DELETE SET NULL,
  text              text NOT NULL,
  status            text NOT NULL DEFAULT 'open',
  assignee_person_id uuid NULL REFERENCES noteflow.persons(id) ON DELETE SET NULL,
  due_date          timestamptz NULL,
  priority          integer NOT NULL DEFAULT 0,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),
  completed_at      timestamptz NULL,
  metadata          jsonb NOT NULL DEFAULT '{}'::jsonb
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'tasks_status_chk'
  ) THEN
    ALTER TABLE noteflow.tasks
      ADD CONSTRAINT tasks_status_chk
      CHECK (status IN ('open','done','dismissed'));
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_tasks_workspace_status
  ON noteflow.tasks(workspace_id, status);

CREATE TRIGGER trg_tasks_updated_at
BEFORE UPDATE ON noteflow.tasks
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.integrations (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id uuid NOT NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  name        text NOT NULL,
  type        text NOT NULL,
  status      text NOT NULL DEFAULT 'disconnected',
  config       jsonb NOT NULL DEFAULT '{}'::jsonb,
  last_sync   timestamptz NULL,
  error_message text NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'integrations_type_chk'
  ) THEN
    ALTER TABLE noteflow.integrations
      ADD CONSTRAINT integrations_type_chk
      CHECK (type IN ('auth','email','calendar','pkm','custom'));
  END IF;
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'integrations_status_chk'
  ) THEN
    ALTER TABLE noteflow.integrations
      ADD CONSTRAINT integrations_status_chk
      CHECK (status IN ('disconnected','connected','error'));
  END IF;
END $$;

CREATE TRIGGER trg_integrations_updated_at
BEFORE UPDATE ON noteflow.integrations
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.integration_secrets (
  integration_id uuid NOT NULL REFERENCES noteflow.integrations(id) ON DELETE CASCADE,
  secret_key     text NOT NULL,
  secret_value   bytea NOT NULL,
  created_at     timestamptz NOT NULL DEFAULT now(),
  updated_at     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (integration_id, secret_key)
);

CREATE TRIGGER trg_integration_secrets_updated_at
BEFORE UPDATE ON noteflow.integration_secrets
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.integration_sync_runs (
  id             uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id uuid NOT NULL REFERENCES noteflow.integrations(id) ON DELETE CASCADE,
  status         text NOT NULL,
  started_at     timestamptz NOT NULL DEFAULT now(),
  ended_at       timestamptz NULL,
  duration_ms    integer NULL,
  error_message  text NULL,
  stats          jsonb NOT NULL DEFAULT '{}'::jsonb
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'integration_sync_runs_status_chk'
  ) THEN
    ALTER TABLE noteflow.integration_sync_runs
      ADD CONSTRAINT integration_sync_runs_status_chk
      CHECK (status IN ('running','success','error'));
  END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_sync_runs_integration_started
  ON noteflow.integration_sync_runs(integration_id, started_at DESC);

CREATE TABLE IF NOT EXISTS noteflow.calendar_events (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id uuid NOT NULL REFERENCES noteflow.integrations(id) ON DELETE CASCADE,
  external_id   text NOT NULL,
  calendar_id   text NOT NULL,
  calendar_name text NOT NULL,
  title         text NOT NULL,
  description   text NULL,
  start_time    timestamptz NOT NULL,
  end_time      timestamptz NOT NULL,
  location      text NULL,
  attendees     text[] NULL,
  is_all_day    boolean NOT NULL DEFAULT false,
  meeting_link  text NULL,
  raw           jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (integration_id, external_id)
);

CREATE TRIGGER trg_calendar_events_updated_at
BEFORE UPDATE ON noteflow.calendar_events
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

CREATE TABLE IF NOT EXISTS noteflow.meeting_calendar_links (
  meeting_id uuid NOT NULL REFERENCES noteflow.meetings(id) ON DELETE CASCADE,
  calendar_event_id uuid NOT NULL REFERENCES noteflow.calendar_events(id) ON DELETE CASCADE,
  PRIMARY KEY (meeting_id, calendar_event_id)
);

CREATE TABLE IF NOT EXISTS noteflow.external_refs (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  integration_id uuid NOT NULL REFERENCES noteflow.integrations(id) ON DELETE CASCADE,
  entity_type   text NOT NULL,
  entity_id     text NOT NULL,
  external_id   text NOT NULL,
  external_url  text NULL,
  created_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (integration_id, entity_type, entity_id)
);

CREATE TABLE IF NOT EXISTS noteflow.settings (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  scope        text NOT NULL, -- system | workspace | user
  workspace_id uuid NULL REFERENCES noteflow.workspaces(id) ON DELETE CASCADE,
  user_id      uuid NULL REFERENCES noteflow.users(id) ON DELETE CASCADE,
  key          text NOT NULL,
  value        jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (scope, workspace_id, user_id, key)
);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'settings_scope_chk'
  ) THEN
    ALTER TABLE noteflow.settings
      ADD CONSTRAINT settings_scope_chk
      CHECK (scope IN ('system','workspace','user'));
  END IF;
END $$;

CREATE TRIGGER trg_settings_updated_at
BEFORE UPDATE ON noteflow.settings
FOR EACH ROW EXECUTE FUNCTION noteflow.set_updated_at();

--------------------------------------------------------------------------------
-- Seed data (safe to re-run)
--------------------------------------------------------------------------------
-- Deterministic IDs for local dev
-- workspace/user share the same UUID to simplify defaults
INSERT INTO noteflow.workspaces (id, slug, name, metadata)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  'default',
  'Default Workspace',
  '{"seed":true}'::jsonb
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.users (id, email, display_name, metadata)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  'local@noteflow.local',
  'Local User',
  '{"seed":true}'::jsonb
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.workspace_memberships (workspace_id, user_id, role)
VALUES (
  '00000000-0000-0000-0000-000000000001',
  '00000000-0000-0000-0000-000000000001',
  'owner'
)
ON CONFLICT DO NOTHING;

-- Sample meeting
INSERT INTO noteflow.meetings (
  id, title, state, created_at, started_at, ended_at, metadata, asset_path
) VALUES (
  '11111111-1111-1111-1111-111111111111',
  'Seed Meeting: Project Kickoff',
  4,
  now() - interval '2 days',
  now() - interval '2 days' + interval '5 minutes',
  now() - interval '2 days' + interval '47 minutes',
  '{"source":"seed","topic":"kickoff"}'::jsonb,
  '11111111-1111-1111-1111-111111111111'
)
ON CONFLICT (id) DO NOTHING;

-- Sample segments
INSERT INTO noteflow.segments (
  id, meeting_id, segment_id, text, start_time, end_time, language, speaker_id, speaker_confidence
) VALUES
  (1, '11111111-1111-1111-1111-111111111111', 0, 'Welcome everyone. Today we will align on goals and deliverables.', 0.0, 6.2, 'en', 'SPEAKER_00', 0.92),
  (2, '11111111-1111-1111-1111-111111111111', 1, 'We should prioritize the database schema first, then build the UI around it.', 6.2, 12.4, 'en', 'SPEAKER_01', 0.88),
  (3, '11111111-1111-1111-1111-111111111111', 2, 'Action item: draft an initial schema and seed script for local development.', 12.4, 18.0, 'en', 'SPEAKER_00', 0.90)
ON CONFLICT (id) DO NOTHING;

-- Word timings (a few illustrative words)
INSERT INTO noteflow.word_timings (segment_pk, word_index, word, start_time, end_time, probability)
VALUES
  (1, 0, 'Welcome', 0.00, 0.40, 0.98),
  (1, 1, 'everyone.', 0.41, 0.80, 0.97),
  (2, 0, 'We', 6.20, 6.30, 0.99),
  (2, 1, 'should', 6.31, 6.55, 0.99),
  (3, 0, 'Action', 12.40, 12.62, 0.97),
  (3, 1, 'item:', 12.63, 12.82, 0.95)
ON CONFLICT DO NOTHING;

-- Summary + points + items
INSERT INTO noteflow.summaries (
  id, meeting_id, executive_summary, provider_name, model_name, tokens_used, latency_ms, verification
) VALUES (
  1,
  '11111111-1111-1111-1111-111111111111',
  'Aligned on building a scalable schema first; UI will follow. Identified a concrete next action to draft schema + seeds.',
  'local',
  'mock',
  123,
  42.0,
  '{"seed":true}'::jsonb
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.key_points (id, summary_id, position, text, segment_ids, start_time, end_time)
VALUES
  (1, 1, 0, 'Schema-first development to accelerate UI work.', ARRAY[1], 6.2, 12.4)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.action_items (id, summary_id, position, text, segment_ids, start_time, end_time, assignee, priority)
VALUES
  (1, 1, 0, 'Draft initial database schema + seed script.', ARRAY[2], 12.4, 18.0, 'Local User', 2)
ON CONFLICT (id) DO NOTHING;

-- Task derived from action item (future task workflow)
INSERT INTO noteflow.tasks (id, workspace_id, meeting_id, action_item_id, text, status, priority)
VALUES (
  '22222222-2222-2222-2222-222222222222',
  '00000000-0000-0000-0000-000000000001',
  '11111111-1111-1111-1111-111111111111',
  1,
  'Draft initial database schema + seed script.',
  'open',
  2
)
ON CONFLICT (id) DO NOTHING;

-- Annotation
INSERT INTO noteflow.annotations (id, meeting_id, annotation_type, text, start_time, end_time, segment_ids)
VALUES
  (1, '11111111-1111-1111-1111-111111111111', 'ANNOTATION_TYPE_NOTE', 'Remember to keep schema modular and future-proof.', 6.0, 10.0, ARRAY[1])
ON CONFLICT (id) DO NOTHING;

-- Speaker/person mapping
INSERT INTO noteflow.persons (id, workspace_id, display_name, email)
VALUES
  ('33333333-3333-3333-3333-333333333333', '00000000-0000-0000-0000-000000000001', 'Alex Example', 'alex@example.com')
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.meeting_speakers (meeting_id, speaker_id, display_name, person_id)
VALUES
  ('11111111-1111-1111-1111-111111111111', 'SPEAKER_00', 'Alex', '33333333-3333-3333-3333-333333333333'),
  ('11111111-1111-1111-1111-111111111111', 'SPEAKER_01', 'Jordan', NULL)
ON CONFLICT DO NOTHING;

-- Tags
INSERT INTO noteflow.tags (id, workspace_id, name, color)
VALUES
  ('44444444-4444-4444-4444-444444444444', '00000000-0000-0000-0000-000000000001', 'seed', '#00AEEF')
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.meeting_tags (meeting_id, tag_id)
VALUES
  ('11111111-1111-1111-1111-111111111111', '44444444-4444-4444-4444-444444444444')
ON CONFLICT DO NOTHING;

-- Mock integration + a calendar event (shape matches your client-side config model) [oai_citation:12‡repomix-output.md](sediment://file_000000004f2c722fbba5e8a81215dabf)
INSERT INTO noteflow.integrations (id, workspace_id, name, type, status, config, last_sync)
VALUES (
  '55555555-5555-5555-5555-555555555555',
  '00000000-0000-0000-0000-000000000001',
  'Mock Calendar',
  'calendar',
  'connected',
  '{"sync_interval_minutes":60,"calendar_ids":["primary"],"webhook_url":"https://example.invalid/webhook"}'::jsonb,
  now() - interval '1 day'
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.calendar_events (
  id, integration_id, external_id, calendar_id, calendar_name, title, start_time, end_time, attendees, meeting_link
) VALUES (
  '66666666-6666-6666-6666-666666666666',
  '55555555-5555-5555-5555-555555555555',
  'evt_seed_001',
  'primary',
  'Primary',
  'Seed Meeting: Project Kickoff',
  now() - interval '2 days' + interval '5 minutes',
  now() - interval '2 days' + interval '47 minutes',
  ARRAY['alex@example.com'],
  'https://meet.example.invalid/seed'
)
ON CONFLICT (id) DO NOTHING;

INSERT INTO noteflow.meeting_calendar_links (meeting_id, calendar_event_id)
VALUES ('11111111-1111-1111-1111-111111111111', '66666666-6666-6666-6666-666666666666')
ON CONFLICT DO NOTHING;

-- Preferences KV used by server-side logic (stored as {"value": ...}) [oai_citation:13‡repomix-output.md](sediment://file_000000004f2c722fbba5e8a81215dabf)
INSERT INTO noteflow.user_preferences (key, value)
VALUES
  ('cloud_consent_granted', '{"value": false}'::jsonb),
  ('schema_seeded', '{"value": true}'::jsonb)
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value, updated_at = now();

-- Keep sequences sane if you re-run
SELECT setval('noteflow.segments_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.segments));
SELECT setval('noteflow.summaries_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.summaries));
SELECT setval('noteflow.key_points_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.key_points));
SELECT setval('noteflow.action_items_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.action_items));
SELECT setval('noteflow.annotations_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.annotations));
SELECT setval('noteflow.word_timings_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.word_timings));
SELECT setval('noteflow.streaming_diarization_turns_id_seq', (SELECT COALESCE(MAX(id), 1) FROM noteflow.streaming_diarization_turns));