-- Profiles with CLI-specific metadata
CREATE TABLE IF NOT EXISTS profiles (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  philosophical_template TEXT DEFAULT 'custom',
  ascii_theme TEXT DEFAULT 'default',
  color_scheme TEXT DEFAULT 'blue',
  output_directory TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Virtues with display preferences
CREATE TABLE IF NOT EXISTS virtues (
  id TEXT PRIMARY KEY,
  profile_id TEXT NOT NULL,
  name TEXT NOT NULL,
  definition TEXT,
  priority_weight REAL DEFAULT 1.0,
  template_source TEXT,
  emoji TEXT DEFAULT '🎯',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE
);

-- Daily entries with CLI interaction metadata
CREATE TABLE IF NOT EXISTS daily_entries (
  id TEXT PRIMARY KEY,
  profile_id TEXT NOT NULL,
  date TEXT NOT NULL,
  virtue_scores TEXT NOT NULL,  -- JSON
  notes TEXT,
  coherence_score REAL,
  completion_time_seconds INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE,
  UNIQUE(profile_id, date)
);

-- Session tracking for streaks and habits
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  profile_id TEXT NOT NULL,
  session_type TEXT NOT NULL CHECK(session_type IN ('daily', 'analytics', 'insights', 'monthly')),
  duration_seconds INTEGER,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE
);

-- Monthly deep-dive assessments
CREATE TABLE IF NOT EXISTS monthly_deep_dives (
  id TEXT PRIMARY KEY,
  profile_id TEXT NOT NULL,
  month TEXT NOT NULL, -- YYYY-MM format
  analysis_data TEXT NOT NULL, -- JSON
  intervention_plan TEXT, -- JSON
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (profile_id) REFERENCES profiles (id) ON DELETE CASCADE,
  UNIQUE(profile_id, month)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_daily_entries_profile_date ON daily_entries(profile_id, date);
CREATE INDEX IF NOT EXISTS idx_sessions_profile_created ON sessions(profile_id, created_at);
CREATE INDEX IF NOT EXISTS idx_virtues_profile ON virtues(profile_id);