-- VITA FORMATION CRM Schema v2
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
  id TEXT PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  password_hash TEXT NOT NULL,
  full_name TEXT NOT NULL,
  email TEXT,
  role TEXT NOT NULL DEFAULT 'counsellor' CHECK(role IN ('admin', 'counsellor', 'viewer')),
  phone TEXT,
  avatar TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS languages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  code TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS sources (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  is_active INTEGER NOT NULL DEFAULT 1,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS statuses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  color TEXT NOT NULL DEFAULT '#6b7280',
  bg_color TEXT NOT NULL DEFAULT '#f3f4f6',
  is_active INTEGER NOT NULL DEFAULT 1,
  is_final INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER DEFAULT 0
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS courses (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  language_id INTEGER REFERENCES languages(id),
  level TEXT,
  teacher TEXT,
  schedule TEXT,
  seats INTEGER DEFAULT 20,
  seats_taken INTEGER DEFAULT 0,
  start_date TEXT,
  end_date TEXT,
  duration TEXT,
  price REAL DEFAULT 0,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS opportunities (
  id TEXT PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  phone TEXT NOT NULL,
  whatsapp TEXT,
  gender TEXT,
  age INTEGER,
  city TEXT,
  email TEXT,
  language_id INTEGER REFERENCES languages(id),
  level TEXT DEFAULT 'Unknown',
  preferred_schedule TEXT,
  preferred_start_date TEXT,
  source_id INTEGER REFERENCES sources(id),
  status_id INTEGER NOT NULL REFERENCES statuses(id),
  priority TEXT NOT NULL DEFAULT 'Normal' CHECK(priority IN ('Low', 'Normal', 'High', 'Urgent')),
  assigned_to TEXT REFERENCES users(id),
  created_by TEXT REFERENCES users(id),
  notes TEXT,
  tags TEXT DEFAULT '[]',
  lead_score INTEGER NOT NULL DEFAULT 0,
  placement_status TEXT DEFAULT 'none' CHECK(placement_status IN ('none', 'pending', 'completed')),
  placement_level TEXT,
  recommended_course_id TEXT REFERENCES courses(id),
  expected_revenue REAL DEFAULT 0,
  call_attempts INTEGER NOT NULL DEFAULT 0,
  is_favorite INTEGER NOT NULL DEFAULT 0,
  is_pinned INTEGER NOT NULL DEFAULT 0,
  last_contact_at TEXT,
  next_follow_up_at TEXT,
  deleted_at TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS notes (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES users(id),
  content TEXT NOT NULL,
  is_quick INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS history (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES users(id),
  action_type TEXT NOT NULL,
  field_name TEXT,
  old_value TEXT,
  new_value TEXT,
  details TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS calls (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES users(id),
  result TEXT NOT NULL,
  notes TEXT,
  duration_seconds INTEGER,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS followups (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES users(id),
  scheduled_at TEXT NOT NULL,
  reason TEXT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending', 'completed', 'missed', 'cancelled', 'rescheduled', 'skipped')),
  attempt_number INTEGER DEFAULT 1,
  completed_at TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS notifications (
  id TEXT PRIMARY KEY,
  user_id TEXT REFERENCES users(id),
  opportunity_id TEXT REFERENCES opportunities(id) ON DELETE SET NULL,
  title TEXT NOT NULL,
  message TEXT,
  type TEXT NOT NULL DEFAULT 'info',
  is_read INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS students (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT UNIQUE REFERENCES opportunities(id),
  student_number TEXT UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  phone TEXT NOT NULL,
  whatsapp TEXT,
  email TEXT,
  gender TEXT,
  age INTEGER,
  city TEXT,
  language_id INTEGER REFERENCES languages(id),
  level TEXT,
  course_id TEXT REFERENCES courses(id),
  teacher TEXT,
  class_name TEXT,
  registration_date TEXT NOT NULL DEFAULT (datetime('now')),
  assigned_to TEXT REFERENCES users(id),
  notes TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS payments (
  id TEXT PRIMARY KEY,
  student_id TEXT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
  opportunity_id TEXT REFERENCES opportunities(id),
  amount REAL NOT NULL DEFAULT 0,
  paid_amount REAL NOT NULL DEFAULT 0,
  remaining REAL NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'Waiting' CHECK(status IN ('Waiting', 'Deposit Paid', 'Paid', 'Refunded')),
  invoice_number TEXT,
  method TEXT,
  notes TEXT,
  paid_at TEXT,
  created_by TEXT REFERENCES users(id),
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS attachments (
  id TEXT PRIMARY KEY,
  opportunity_id TEXT NOT NULL REFERENCES opportunities(id) ON DELETE CASCADE,
  user_id TEXT REFERENCES users(id),
  filename TEXT NOT NULL,
  original_name TEXT NOT NULL,
  mime_type TEXT,
  size INTEGER DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS saved_filters (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  filters_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS deleted_items (
  id TEXT PRIMARY KEY,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  data TEXT NOT NULL,
  deleted_by TEXT REFERENCES users(id),
  deleted_at TEXT NOT NULL DEFAULT (datetime('now')),
  expires_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_opp_phone ON opportunities(phone);
CREATE INDEX IF NOT EXISTS idx_opp_status ON opportunities(status_id);
CREATE INDEX IF NOT EXISTS idx_opp_assigned ON opportunities(assigned_to);
CREATE INDEX IF NOT EXISTS idx_opp_created ON opportunities(created_at);
CREATE INDEX IF NOT EXISTS idx_opp_followup ON opportunities(next_follow_up_at);
CREATE INDEX IF NOT EXISTS idx_opp_deleted ON opportunities(deleted_at);
CREATE INDEX IF NOT EXISTS idx_opp_score ON opportunities(lead_score);
CREATE INDEX IF NOT EXISTS idx_followups_scheduled ON followups(scheduled_at);
CREATE INDEX IF NOT EXISTS idx_followups_status ON followups(status);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read);
CREATE INDEX IF NOT EXISTS idx_history_opp ON history(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_notes_opp ON notes(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_calls_opp ON calls(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_students_opp ON students(opportunity_id);
CREATE INDEX IF NOT EXISTS idx_payments_student ON payments(student_id);
