CREATE TABLE boards (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  icon TEXT NOT NULL DEFAULT '',
  show_description INTEGER NOT NULL DEFAULT 0,
  card_properties TEXT NOT NULL DEFAULT '[]',
  is_template INTEGER NOT NULL DEFAULT 0,
  created_by INTEGER NOT NULL,
  updated_by INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE blocks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  board_id INTEGER NOT NULL,
  parent_id INTEGER NOT NULL DEFAULT 0,
  created_by INTEGER NOT NULL,
  updated_by INTEGER NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT '',
  fields TEXT NOT NULL DEFAULT '{}',
  completed_at INTEGER NOT NULL DEFAULT 0,
  completed_by INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE blocks_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  block_id INTEGER NOT NULL,
  public_id TEXT NOT NULL,
  board_id INTEGER NOT NULL,
  parent_id INTEGER NOT NULL DEFAULT 0,
  created_by INTEGER NOT NULL,
  updated_by INTEGER NOT NULL,
  type TEXT NOT NULL,
  title TEXT NOT NULL DEFAULT '',
  fields TEXT NOT NULL DEFAULT '{}',
  completed_at INTEGER NOT NULL DEFAULT 0,
  completed_by INTEGER,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  insert_at INTEGER NOT NULL
);

CREATE TABLE board_members (
  board_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  role TEXT NOT NULL DEFAULT 'writer',
  created_at INTEGER NOT NULL,
  PRIMARY KEY (board_id, user_id),
  FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE block_comments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  block_id INTEGER NOT NULL,
  content TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE block_properties (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  block_id INTEGER NOT NULL UNIQUE,
  status TEXT NOT NULL DEFAULT '',
  card_type TEXT NOT NULL DEFAULT '',
  priority TEXT NOT NULL DEFAULT '',
  due_date TEXT NOT NULL DEFAULT '',
  assignee_id INTEGER,
  sprint TEXT NOT NULL DEFAULT '',
  description TEXT NOT NULL DEFAULT '',
  FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE board_field_values (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  board_id INTEGER NOT NULL REFERENCES boards(id) ON DELETE CASCADE,
  field TEXT NOT NULL,
  value TEXT NOT NULL,
  label TEXT NOT NULL,
  color TEXT NOT NULL DEFAULT '',
  display_order INTEGER NOT NULL DEFAULT 0,
  start_date TEXT NOT NULL DEFAULT '',
  end_date TEXT NOT NULL DEFAULT '',
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  UNIQUE(board_id, field, value)
);

CREATE TABLE block_checklist_items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  block_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  done INTEGER NOT NULL DEFAULT 0,
  display_order INTEGER NOT NULL DEFAULT 0,
  created_by INTEGER NOT NULL,
  updated_by INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (updated_by) REFERENCES users(id)
);

CREATE TABLE block_attachments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  public_id TEXT NOT NULL UNIQUE,
  block_id INTEGER NOT NULL,
  filename TEXT NOT NULL,
  content_type TEXT NOT NULL DEFAULT 'application/octet-stream',
  size INTEGER NOT NULL DEFAULT 0,
  storage_path TEXT NOT NULL,
  created_by INTEGER NOT NULL,
  created_at INTEGER NOT NULL,
  delete_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE TABLE block_assignees (
  block_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  assigned_at INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (block_id, user_id),
  FOREIGN KEY (block_id) REFERENCES blocks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS auth_provider_group_mappings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  auth_provider_id INTEGER NOT NULL,
  group_id INTEGER NOT NULL,
  claim_value TEXT NOT NULL,
  created_at INTEGER NOT NULL DEFAULT 0,
  updated_at INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (auth_provider_id) REFERENCES auth_providers(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES user_groups(id) ON DELETE CASCADE,
  UNIQUE (auth_provider_id, claim_value, group_id)
);

ALTER TABLE repos ADD COLUMN description TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN avatar_storage_path TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN avatar_content_type TEXT NOT NULL DEFAULT '';
ALTER TABLE users ADD COLUMN avatar_updated_at INTEGER NOT NULL DEFAULT 0;
ALTER TABLE auth_providers ADD COLUMN oidc_end_session_url TEXT NOT NULL DEFAULT '';
ALTER TABLE auth_providers ADD COLUMN group_sync_mode TEXT NOT NULL DEFAULT 'off';
ALTER TABLE auth_providers ADD COLUMN oidc_post_logout_redirect INTEGER NOT NULL DEFAULT 0;
ALTER TABLE sessions ADD COLUMN oidc_id_token TEXT NOT NULL DEFAULT '';

INSERT OR IGNORE INTO block_properties (block_id, status, description)
SELECT bl.id,
  COALESCE(json_extract(bl.fields, '$.status'), ''),
  COALESCE(json_extract(bl.fields, '$.description'), '')
FROM blocks bl
WHERE bl.type = 'card' AND bl.delete_at = 0;

INSERT OR IGNORE INTO block_assignees (block_id, user_id, assigned_at)
SELECT block_id, assignee_id, strftime('%s', 'now')
FROM block_properties
WHERE assignee_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_boards_project ON boards(project_id, delete_at);
CREATE INDEX IF NOT EXISTS idx_blocks_board ON blocks(board_id, delete_at);
CREATE INDEX IF NOT EXISTS idx_blocks_parent ON blocks(parent_id);
CREATE INDEX IF NOT EXISTS idx_blocks_type ON blocks(board_id, type, delete_at);
CREATE INDEX IF NOT EXISTS idx_blocks_history_block ON blocks_history(block_id, insert_at DESC);
CREATE INDEX IF NOT EXISTS idx_board_members_user ON board_members(user_id);
CREATE INDEX IF NOT EXISTS idx_block_comments_block ON block_comments(block_id, delete_at);
CREATE INDEX IF NOT EXISTS idx_block_properties_block ON block_properties(block_id);
CREATE INDEX IF NOT EXISTS idx_board_field_values_board_field ON board_field_values(board_id, field);
CREATE INDEX IF NOT EXISTS idx_block_checklist_items_block ON block_checklist_items(block_id, delete_at, display_order);
CREATE INDEX IF NOT EXISTS idx_block_attachments_block ON block_attachments(block_id, delete_at, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_block_assignees_user ON block_assignees(user_id, block_id);
CREATE INDEX IF NOT EXISTS idx_auth_provider_group_mappings_provider ON auth_provider_group_mappings(auth_provider_id);
CREATE INDEX IF NOT EXISTS idx_auth_provider_group_mappings_claim ON auth_provider_group_mappings(auth_provider_id, claim_value);