-- Migration: Rename prompts to trackers and add entry metadata
-- Date: 2026-02-09
-- Description: Rename "prompt" concept to "tracker" and make entries self-contained

-- Drop old prompts table (test data only, safe to drop)
DROP TABLE IF EXISTS prompts;

-- Create new trackers table (renamed from prompts)
CREATE TABLE trackers (
  tracker_id TEXT PRIMARY KEY,
  dossier_id TEXT NOT NULL,
  category TEXT,
  type TEXT,
  question TEXT,
  frequency TEXT,
  time_of_day TEXT,
  next_ask INTEGER,
  expires_at INTEGER,
  input_type TEXT,
  input_config TEXT,
  group_name TEXT,
  trigger_entry TEXT,
  created_by TEXT,
  last_response TEXT,
  last_response_raw TEXT,
  last_response_at INTEGER,
  dismissed INTEGER DEFAULT 0,
  active INTEGER DEFAULT 1,
  created_at INTEGER,
  updated_at INTEGER,
  open INTEGER DEFAULT 0,
  source_input TEXT,
  schedule TEXT
);

CREATE INDEX idx_trackers_dossier ON trackers(dossier_id);
CREATE INDEX idx_trackers_dossier_active ON trackers(dossier_id, active, next_ask);

-- Update entries table to be self-contained
-- Add columns for storing tracker metadata snapshot
ALTER TABLE entries ADD COLUMN tracker_question TEXT DEFAULT '';
ALTER TABLE entries ADD COLUMN tracker_fields TEXT DEFAULT '';  -- JSON: field definitions
ALTER TABLE entries ADD COLUMN tracker_layout TEXT DEFAULT '';  -- layout type

-- Note: search_key is now optional - entries can be standalone or linked to tracker