-- Core MVP schema (SQLite)

CREATE TABLE IF NOT EXISTS auth_users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  full_name TEXT NOT NULL,
  role TEXT NOT NULL,
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_auth_users_user_id ON auth_users (user_id);
CREATE INDEX IF NOT EXISTS ix_auth_users_username ON auth_users (username);
CREATE INDEX IF NOT EXISTS ix_auth_users_role ON auth_users (role);

CREATE TABLE IF NOT EXISTS customers (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL,
  phones_json TEXT NOT NULL DEFAULT '[]',
  preferred_phone TEXT,
  tags_json TEXT NOT NULL DEFAULT '[]',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_customers_customer_id ON customers (customer_id);
CREATE INDEX IF NOT EXISTS ix_customers_display_name ON customers (display_name);

CREATE TABLE IF NOT EXISTS interactions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  interaction_id TEXT NOT NULL UNIQUE,
  channel TEXT NOT NULL,
  subject TEXT NOT NULL,
  customer_id TEXT,
  queue_id TEXT,
  priority INTEGER NOT NULL DEFAULT 3,
  status TEXT NOT NULL,
  assigned_to TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_interactions_interaction_id ON interactions (interaction_id);
CREATE INDEX IF NOT EXISTS ix_interactions_status ON interactions (status);
CREATE INDEX IF NOT EXISTS ix_interactions_queue_id ON interactions (queue_id);
CREATE INDEX IF NOT EXISTS ix_interactions_assigned_to ON interactions (assigned_to);

CREATE TABLE IF NOT EXISTS interaction_timelines (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  interaction_id TEXT NOT NULL,
  timestamp TEXT NOT NULL,
  action TEXT NOT NULL,
  metadata_json TEXT NOT NULL DEFAULT '{}'
);
CREATE INDEX IF NOT EXISTS ix_interaction_timelines_interaction_id ON interaction_timelines (interaction_id);
CREATE INDEX IF NOT EXISTS ix_interaction_timelines_timestamp ON interaction_timelines (timestamp);

CREATE TABLE IF NOT EXISTS queues (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  queue_id TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  rules_json TEXT NOT NULL DEFAULT '[]',
  created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS ix_queues_queue_id ON queues (queue_id);
CREATE INDEX IF NOT EXISTS ix_queues_name ON queues (name);

CREATE TABLE IF NOT EXISTS routing_counters (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  channel TEXT NOT NULL UNIQUE,
  counter INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS ix_routing_counters_channel ON routing_counters (channel);