PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS patients (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  file_no TEXT UNIQUE,
  full_name TEXT NOT NULL,
  national_id TEXT,
  birth_date TEXT,
  phone TEXT,
  notes TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS visits (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  patient_id INTEGER NOT NULL,
  doctor_name TEXT,
  visit_datetime TEXT NOT NULL,
  chief_complaint TEXT,
  status TEXT NOT NULL DEFAULT 'open',
  source_type TEXT,
  source_label TEXT,
  started_at TEXT,
  ended_at TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS visit_media (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  visit_id INTEGER NOT NULL,
  patient_id INTEGER NOT NULL,
  media_type TEXT NOT NULL,
  file_path TEXT NOT NULL,
  file_name TEXT,
  thumb_path TEXT,
  mime_type TEXT,
  width INTEGER,
  height INTEGER,
  file_size INTEGER,
  captured_at TEXT NOT NULL,
  is_frozen_frame INTEGER NOT NULL DEFAULT 1,
  selected_for_report INTEGER NOT NULL DEFAULT 0,
  note TEXT,
  sort_order INTEGER DEFAULT 0,
  created_at TEXT NOT NULL,
  FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE,
  FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS visit_notes (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  visit_id INTEGER NOT NULL,
  note_type TEXT NOT NULL DEFAULT 'general',
  note_text TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS visit_reports (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  visit_id INTEGER NOT NULL UNIQUE,
  doctor_report_text TEXT,
  patient_report_text TEXT,
  structured_json TEXT,
  report_status TEXT NOT NULL DEFAULT 'draft',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS ai_logs (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  visit_id INTEGER,
  target_audience TEXT NOT NULL,
  request_payload TEXT,
  response_text TEXT,
  model_name TEXT,
  status TEXT NOT NULL DEFAULT 'success',
  created_at TEXT NOT NULL,
  FOREIGN KEY (visit_id) REFERENCES visits(id)
);
CREATE TABLE IF NOT EXISTS app_settings (
  key TEXT PRIMARY KEY,
  value TEXT,
  updated_at TEXT NOT NULL
);
