import { contentFts5MigrationSql } from '@platform-modules/content/search-sqlite';

/**
 * Complete SQLite-compatible DDL for mod-cms 1-click D1 deploy.
 * Host applies each statement once at provision time (no semicolon splitting).
 */
export function getD1SchemaStatements(): string[] {
  return [
    `CREATE TABLE IF NOT EXISTS languages (
  code text PRIMARY KEY,
  name_native text,
  name_english text,
  direction text,
  search_config text,
  is_active integer NOT NULL,
  is_default integer NOT NULL DEFAULT 0,
  sort_order integer NOT NULL DEFAULT 0,
  created_at text NOT NULL DEFAULT (datetime('now')),
  updated_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS languages_one_default_uq ON languages (is_default) WHERE is_default = 1`,
    `CREATE INDEX IF NOT EXISTS languages_active_sort_idx ON languages (is_active, sort_order)`,

    `CREATE TABLE IF NOT EXISTS translation_value (
  id text PRIMARY KEY,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  field_key text NOT NULL,
  locale text NOT NULL REFERENCES languages(code),
  value text NOT NULL,
  source_hash text NOT NULL DEFAULT '',
  manual_override integer NOT NULL DEFAULT 0,
  model_id text,
  status text NOT NULL DEFAULT 'OK',
  translated_at text,
  created_at text NOT NULL DEFAULT (datetime('now')),
  updated_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS tv_entity_field_locale_uq ON translation_value (entity_type, entity_id, field_key, locale)`,
    `CREATE INDEX IF NOT EXISTS tv_entity_locale_idx ON translation_value (entity_type, entity_id, locale)`,
    `CREATE INDEX IF NOT EXISTS tv_locale_status_idx ON translation_value (locale, status)`,
    `CREATE INDEX IF NOT EXISTS tv_entity_field_locale_val_idx ON translation_value (entity_type, field_key, locale, value)`,

    `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'))
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS content_entries_type_slug_uq ON content_entries (type, slug)`,
    `CREATE INDEX IF NOT EXISTS content_entries_type_status_pub_idx ON content_entries (type, status, published_at DESC)`,
    `CREATE INDEX IF NOT EXISTS content_entries_type_status_vis_pub_idx ON content_entries (type, status, visibility, published_at DESC)`,

    `CREATE TABLE IF NOT EXISTS content_terms (
  id text PRIMARY KEY,
  taxonomy text NOT NULL,
  slug text NOT NULL,
  name text NOT NULL,
  parent_id text REFERENCES content_terms(id) ON DELETE RESTRICT,
  depth integer NOT NULL DEFAULT 0,
  created_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS content_terms_sibling_slug_uq
  ON content_terms (taxonomy, COALESCE(parent_id, '00000000-0000-0000-0000-000000000000'), slug)`,
    `CREATE INDEX IF NOT EXISTS content_terms_taxonomy_parent_idx ON content_terms (taxonomy, parent_id)`,

    `CREATE TABLE IF NOT EXISTS content_entry_terms (
  entry_id text NOT NULL REFERENCES content_entries(id) ON DELETE CASCADE,
  term_id text NOT NULL REFERENCES content_terms(id) ON DELETE CASCADE,
  PRIMARY KEY (entry_id, term_id)
)`,
    `CREATE INDEX IF NOT EXISTS content_entry_terms_term_idx ON content_entry_terms (term_id)`,

    `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'))
)`,
    `CREATE INDEX IF NOT EXISTS content_revisions_entry_seq_idx ON content_revisions (entry_id, seq DESC)`,

    `CREATE TABLE IF NOT EXISTS site_settings (
  key text PRIMARY KEY,
  value text NOT NULL,
  updated_at text NOT NULL DEFAULT (datetime('now'))
)`,

    `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
)`,
    `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 form_submissions (
  id text PRIMARY KEY,
  form_id text NOT NULL,
  data text NOT NULL,
  meta text,
  spam integer NOT NULL DEFAULT 0,
  created_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE INDEX IF NOT EXISTS form_submissions_form_created_idx ON form_submissions (form_id, created_at DESC)`,

    `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
)`,
    `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`,

    `CREATE TABLE IF NOT EXISTS audit_log (
  id text PRIMARY KEY,
  actor_id text,
  actor_type text,
  actor_label text,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  metadata text,
  ip text,
  tenant_id text,
  created_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE INDEX IF NOT EXISTS audit_log_tenant_created_idx ON audit_log (tenant_id, created_at DESC)`,
    `CREATE INDEX IF NOT EXISTS audit_log_tenant_entity_idx ON audit_log (tenant_id, entity_type, entity_id)`,
    `CREATE INDEX IF NOT EXISTS audit_log_tenant_actor_idx ON audit_log (tenant_id, actor_id)`,

    `CREATE TABLE IF NOT EXISTS auth_users (
  id text PRIMARY KEY,
  email text NOT NULL UNIQUE,
  password_hash text,
  session_version integer NOT NULL DEFAULT 0,
  roles text NOT NULL DEFAULT '["user"]',
  status text NOT NULL DEFAULT 'active',
  created_at text NOT NULL
)`,
    `CREATE TABLE IF NOT EXISTS user_sessions (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  refresh_token_hash text NOT NULL,
  previous_refresh_token_hash text,
  last_refreshed_at text,
  expires_at text NOT NULL,
  status text NOT NULL DEFAULT 'active'
)`,
    `CREATE TABLE IF NOT EXISTS service_tokens (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  token_hash text NOT NULL UNIQUE,
  label text NOT NULL,
  expires_at text,
  status text NOT NULL DEFAULT 'active',
  created_at text NOT NULL,
  last_used_at text
)`,

    `CREATE TABLE IF NOT EXISTS media_assets (
  key text PRIMARY KEY,
  url text NOT NULL,
  content_type text NOT NULL,
  size integer NOT NULL,
  width integer,
  height integer,
  uploader_id text NOT NULL,
  created_at text NOT NULL DEFAULT (datetime('now'))
)`,

    `CREATE TABLE IF NOT EXISTS field_values (
  id text PRIMARY KEY,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  group_id text NOT NULL,
  field_key text NOT NULL,
  ordinal integer NOT NULL DEFAULT 0,
  value_text text,
  value_num real,
  value_bool integer,
  value_date text,
  ref_type text,
  ref_id text,
  ref_meta text,
  created_at text NOT NULL DEFAULT (datetime('now')),
  updated_at text NOT NULL DEFAULT (datetime('now')),
  CONSTRAINT field_values_one_lane CHECK (
    (CASE WHEN value_text IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_num IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_bool IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN value_date IS NOT NULL THEN 1 ELSE 0 END
   + CASE WHEN ref_id IS NOT NULL THEN 1 ELSE 0 END) = 1
  )
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS field_values_entity_group_field_ord_uq
  ON field_values (entity_type, entity_id, group_id, field_key, ordinal)`,
    `CREATE INDEX IF NOT EXISTS field_values_entity_idx ON field_values (entity_type, entity_id)`,
    `CREATE INDEX IF NOT EXISTS field_values_facet_text_idx ON field_values (entity_type, field_key, value_text)`,
    `CREATE INDEX IF NOT EXISTS field_values_facet_num_idx ON field_values (entity_type, field_key, value_num)`,
    `CREATE INDEX IF NOT EXISTS field_values_facet_date_idx ON field_values (entity_type, field_key, value_date)`,
    `CREATE INDEX IF NOT EXISTS field_values_ref_idx ON field_values (entity_type, ref_type, ref_id)`,

    `CREATE TABLE IF NOT EXISTS field_groups (
  id text PRIMARY KEY,
  entity_type text NOT NULL,
  sub_type text,
  key text NOT NULL,
  label text NOT NULL,
  fields text NOT NULL,
  position integer NOT NULL DEFAULT 0,
  created_at text NOT NULL DEFAULT (datetime('now')),
  updated_at text NOT NULL DEFAULT (datetime('now'))
)`,
    `CREATE UNIQUE INDEX IF NOT EXISTS field_groups_entity_key_uq ON field_groups (entity_type, key)`,

    `CREATE TABLE IF NOT EXISTS menus (
  id text PRIMARY KEY,
  key text NOT NULL UNIQUE,
  label text NOT NULL,
  created_at_ms integer NOT NULL,
  updated_at_ms integer NOT NULL
)`,
    `CREATE TABLE IF NOT EXISTS menu_items (
  id text PRIMARY KEY,
  menu_id text NOT NULL REFERENCES menus(id) ON DELETE CASCADE,
  parent_id text REFERENCES menu_items(id) ON DELETE CASCADE,
  position integer NOT NULL,
  depth integer NOT NULL,
  label text NOT NULL,
  target_kind text NOT NULL,
  url text,
  target_entity_type text,
  target_entity_id text,
  open_in_new integer NOT NULL DEFAULT 0,
  created_at_ms integer NOT NULL,
  updated_at_ms integer NOT NULL
)`,
    `CREATE INDEX IF NOT EXISTS menu_items_menu_parent_position_idx
  ON menu_items (menu_id, parent_id, position)`,

    ...contentFts5MigrationSql
      .split('\n\n')
      .map((s) => s.trim())
      .filter(Boolean),
  ];
}