-- ============================================================================
-- Esquema de base de datos.
-- ----------------------------------------------------------------------------
-- Se usa SQLite (better-sqlite3) para la v1. El SQL se escribió a propósito
-- en un subconjunto compatible con PostgreSQL (tipos simples, sin funciones
-- específicas de SQLite salvo AUTOINCREMENT-like vía TEXT PK) para que migrar
-- sea, en el peor caso, cambiar el driver en database/connection.ts + ajustar
-- unos pocos tipos (INTEGER de booleanos -> BOOLEAN, TEXT de fechas -> TIMESTAMP).
-- Ninguna consulta de negocio (services/*) usa sintaxis exclusiva de SQLite.
-- ============================================================================

CREATE TABLE IF NOT EXISTS imports (
  id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  file_kind TEXT NOT NULL,
  file_name TEXT NOT NULL,
  imported_at TEXT NOT NULL,
  app_user TEXT,
  record_count INTEGER NOT NULL DEFAULT 0,
  duplicate_count INTEGER NOT NULL DEFAULT 0,
  error_count INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'processing',
  processing_time_ms INTEGER,
  warnings_json TEXT
);

CREATE TABLE IF NOT EXISTS calls (
  unique_id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  agent TEXT,
  agent_id TEXT,
  campaign TEXT,
  phone TEXT,
  call_date TEXT,
  call_start TEXT,
  call_end TEXT,
  direction TEXT,
  talk_time INTEGER NOT NULL DEFAULT 0,
  hold_time INTEGER NOT NULL DEFAULT 0,
  wrap_up_time INTEGER NOT NULL DEFAULT 0,
  wait_time INTEGER NOT NULL DEFAULT 0,
  ring_time INTEGER NOT NULL DEFAULT 0,
  call_result TEXT,
  lead_status TEXT,
  is_effective_contact INTEGER NOT NULL DEFAULT 0,
  is_sale INTEGER NOT NULL DEFAULT 0,
  tipificacion TEXT,
  nivel1 TEXT,
  nivel2 TEXT,
  nivel3 TEXT,
  nivel4 TEXT,
  import_id TEXT NOT NULL REFERENCES imports(id)
);

CREATE INDEX IF NOT EXISTS idx_calls_provider ON calls(provider);
CREATE INDEX IF NOT EXISTS idx_calls_campaign ON calls(campaign);
CREATE INDEX IF NOT EXISTS idx_calls_agent ON calls(agent);
CREATE INDEX IF NOT EXISTS idx_calls_date ON calls(call_date);
CREATE INDEX IF NOT EXISTS idx_calls_import ON calls(import_id);
CREATE INDEX IF NOT EXISTS idx_calls_tipificacion ON calls(tipificacion);

CREATE TABLE IF NOT EXISTS sessions (
  unique_id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  agent TEXT,
  agent_id TEXT,
  date TEXT,
  login_time TEXT,
  logout_time TEXT,
  connected_time INTEGER NOT NULL DEFAULT 0,
  break_time INTEGER NOT NULL DEFAULT 0,
  available_time INTEGER NOT NULL DEFAULT 0,
  unavailable_time INTEGER NOT NULL DEFAULT 0,
  break_breakdown_json TEXT,
  import_id TEXT NOT NULL REFERENCES imports(id)
);

CREATE INDEX IF NOT EXISTS idx_sessions_provider ON sessions(provider);
CREATE INDEX IF NOT EXISTS idx_sessions_agent ON sessions(agent);
CREATE INDEX IF NOT EXISTS idx_sessions_date ON sessions(date);
CREATE INDEX IF NOT EXISTS idx_sessions_import ON sessions(import_id);

CREATE TABLE IF NOT EXISTS performance_summary (
  unique_id TEXT PRIMARY KEY,
  provider TEXT NOT NULL,
  agent TEXT NOT NULL,
  total_login_time INTEGER NOT NULL DEFAULT 0,
  total_break_time INTEGER NOT NULL DEFAULT 0,
  total_calls INTEGER NOT NULL DEFAULT 0,
  inbound_calls INTEGER NOT NULL DEFAULT 0,
  outbound_calls INTEGER NOT NULL DEFAULT 0,
  total_talk_time INTEGER NOT NULL DEFAULT 0,
  total_hold_time INTEGER NOT NULL DEFAULT 0,
  total_wrap_time INTEGER NOT NULL DEFAULT 0,
  total_wait_time INTEGER NOT NULL DEFAULT 0,
  per_campaign_json TEXT,
  import_id TEXT NOT NULL REFERENCES imports(id)
);

CREATE INDEX IF NOT EXISTS idx_performance_import ON performance_summary(import_id);