-- Core MVP schema (PostgreSQL)

CREATE TABLE IF NOT EXISTS auth_users (
  id BIGSERIAL PRIMARY KEY,
  user_id VARCHAR(64) NOT NULL UNIQUE,
  username VARCHAR(128) NOT NULL UNIQUE,
  password VARCHAR(256) NOT NULL,
  full_name VARCHAR(256) NOT NULL,
  role VARCHAR(32) NOT NULL,
  created_at VARCHAR(64) 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 BIGSERIAL PRIMARY KEY,
  customer_id VARCHAR(64) NOT NULL UNIQUE,
  display_name VARCHAR(256) NOT NULL,
  phones_json TEXT NOT NULL DEFAULT '[]',
  preferred_phone VARCHAR(64),
  tags_json TEXT NOT NULL DEFAULT '[]',
  created_at VARCHAR(64) 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 BIGSERIAL PRIMARY KEY,
  interaction_id VARCHAR(64) NOT NULL UNIQUE,
  channel VARCHAR(32) NOT NULL,
  subject VARCHAR(512) NOT NULL,
  customer_id VARCHAR(64),
  queue_id VARCHAR(64),
  priority INTEGER NOT NULL DEFAULT 3,
  status VARCHAR(32) NOT NULL,
  assigned_to VARCHAR(128),
  created_at VARCHAR(64) NOT NULL,
  updated_at VARCHAR(64) 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 BIGSERIAL PRIMARY KEY,
  interaction_id VARCHAR(64) NOT NULL,
  timestamp VARCHAR(64) NOT NULL,
  action VARCHAR(128) 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 BIGSERIAL PRIMARY KEY,
  queue_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(256) NOT NULL,
  description TEXT NOT NULL DEFAULT '',
  rules_json TEXT NOT NULL DEFAULT '[]',
  created_at VARCHAR(64) 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 BIGSERIAL PRIMARY KEY,
  channel VARCHAR(32) NOT NULL UNIQUE,
  counter INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS ix_routing_counters_channel ON routing_counters (channel);