-- D1 parity migration: preserve package-level monotonic seq contracts with SQLite-native allocation.
-- Safe on both an already-installed database and an empty database.
PRAGMA defer_foreign_keys = ON;

-- content_revisions references content_entries. D1 validates that parent table while applying this
-- migration, so bootstrap the canonical D1 content_entries shape when the database is brand-new.
-- On an installed site this is a no-op; applyD1Schema creates its indexes separately.
CREATE TABLE IF NOT EXISTS content_entries (
  id text PRIMARY KEY,
  slug text NOT NULL,
  type text NOT NULL,
  title text NOT NULL,
  body text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'draft',
  visibility text NOT NULL DEFAULT 'public',
  published_at text,
  author text NOT NULL,
  created_at text NOT NULL DEFAULT (datetime('now')),
  updated_at text NOT NULL DEFAULT (datetime('now'))
);

-- Bootstrap the legacy shape when this is an empty database so the same rebuild path is deterministic.
CREATE TABLE IF NOT EXISTS content_revisions (
  id text PRIMARY KEY,
  entry_id text NOT NULL REFERENCES content_entries(id) ON DELETE CASCADE,
  seq integer NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  slug text NOT NULL,
  type text NOT NULL,
  term_ids text NOT NULL DEFAULT '[]',
  editor text NOT NULL,
  created_at text NOT NULL DEFAULT (datetime('now'))
);
ALTER TABLE content_revisions RENAME TO content_revisions_seq_legacy;
CREATE TABLE content_revisions (
  seq integer PRIMARY KEY AUTOINCREMENT,
  id text NOT NULL UNIQUE,
  entry_id text NOT NULL REFERENCES content_entries(id) ON DELETE CASCADE,
  title text NOT NULL,
  body text NOT NULL,
  slug text NOT NULL,
  type text NOT NULL,
  term_ids text NOT NULL DEFAULT '[]',
  editor text NOT NULL,
  created_at text NOT NULL DEFAULT (datetime('now'))
);
INSERT INTO content_revisions (seq, id, entry_id, title, body, slug, type, term_ids, editor, created_at)
SELECT seq, id, entry_id, title, body, slug, type, term_ids, editor, created_at
FROM content_revisions_seq_legacy
ORDER BY seq;
DROP TABLE content_revisions_seq_legacy;
CREATE INDEX IF NOT EXISTS content_revisions_entry_seq_idx ON content_revisions (entry_id, seq DESC);

CREATE TABLE IF NOT EXISTS comments (
  seq integer NOT NULL,
  id text PRIMARY KEY,
  target_type text NOT NULL,
  target_id text NOT NULL,
  parent_id text REFERENCES comments(id),
  depth integer NOT NULL DEFAULT 0,
  author_kind text NOT NULL,
  author_user_id text,
  author_name text,
  author_email text,
  author_url text,
  body text NOT NULL DEFAULT '',
  body_html text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'pending',
  created_at_ms integer NOT NULL,
  created_at text NOT NULL,
  edited_at text
);
ALTER TABLE comments RENAME TO comments_seq_legacy;
CREATE TABLE comments (
  seq integer PRIMARY KEY AUTOINCREMENT,
  id text NOT NULL UNIQUE,
  target_type text NOT NULL,
  target_id text NOT NULL,
  parent_id text REFERENCES comments(id),
  depth integer NOT NULL DEFAULT 0,
  author_kind text NOT NULL,
  author_user_id text,
  author_name text,
  author_email text,
  author_url text,
  body text NOT NULL DEFAULT '',
  body_html text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'pending',
  created_at_ms integer NOT NULL,
  created_at text NOT NULL,
  edited_at text
);
INSERT INTO comments (seq, id, target_type, target_id, parent_id, depth, author_kind, author_user_id, author_name, author_email, author_url, body, body_html, status, created_at_ms, created_at, edited_at)
SELECT seq, id, target_type, target_id, parent_id, depth, author_kind, author_user_id, author_name, author_email, author_url, body, body_html, status, created_at_ms, created_at, edited_at
FROM comments_seq_legacy
ORDER BY seq;
DROP TABLE comments_seq_legacy;
CREATE INDEX IF NOT EXISTS comments_target_keyset_idx ON comments (target_type, target_id, created_at_ms, seq);
CREATE INDEX IF NOT EXISTS comments_parent_id_idx ON comments (parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS comments_status_idx ON comments (status);
CREATE INDEX IF NOT EXISTS comments_moderation_idx ON comments (status, created_at_ms, seq);

CREATE TABLE IF NOT EXISTS notifications (
  seq integer NOT NULL,
  id text PRIMARY KEY,
  user_id text NOT NULL,
  title text NOT NULL,
  body text,
  href text,
  kind text,
  created_at_ms integer NOT NULL,
  created_at text NOT NULL,
  read_at text
);
ALTER TABLE notifications RENAME TO notifications_seq_legacy;
CREATE TABLE notifications (
  seq integer PRIMARY KEY AUTOINCREMENT,
  id text NOT NULL UNIQUE,
  user_id text NOT NULL,
  title text NOT NULL,
  body text,
  href text,
  kind text,
  created_at_ms integer NOT NULL,
  created_at text NOT NULL,
  read_at text
);
INSERT INTO notifications (seq, id, user_id, title, body, href, kind, created_at_ms, created_at, read_at)
SELECT seq, id, user_id, title, body, href, kind, created_at_ms, created_at, read_at
FROM notifications_seq_legacy
ORDER BY seq;
DROP TABLE notifications_seq_legacy;
CREATE INDEX IF NOT EXISTS notifications_user_keyset_idx ON notifications (user_id, created_at_ms, seq);
CREATE INDEX IF NOT EXISTS notifications_user_unread_idx ON notifications (user_id) WHERE read_at IS NULL;

PRAGMA defer_foreign_keys = OFF;
