-- Nubox Database Schema
-- Run with: wrangler d1 execute nubox-db --file=schema.sql

-- Users table
CREATE TABLE IF NOT EXISTS users (
  phone TEXT PRIMARY KEY,
  telegram_id TEXT UNIQUE,
  display_name TEXT,
  created_at INTEGER DEFAULT (unixepoch()),
  last_seen_at INTEGER,
  settings TEXT DEFAULT '{}'
);

CREATE INDEX IF NOT EXISTS idx_users_telegram ON users(telegram_id);

-- Contacts (consent system)
CREATE TABLE IF NOT EXISTS contacts (
  owner_phone TEXT NOT NULL,
  contact_phone TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending', -- 'trusted', 'pending', 'blocked'
  created_at INTEGER DEFAULT (unixepoch()),
  updated_at INTEGER,
  PRIMARY KEY (owner_phone, contact_phone),
  FOREIGN KEY (owner_phone) REFERENCES users(phone)
);

CREATE INDEX IF NOT EXISTS idx_contacts_owner ON contacts(owner_phone, status);
CREATE INDEX IF NOT EXISTS idx_contacts_contact ON contacts(contact_phone);

-- Messages
CREATE TABLE IF NOT EXISTS messages (
  id TEXT PRIMARY KEY,
  from_phone TEXT NOT NULL,
  to_phone TEXT NOT NULL,
  subject TEXT,
  body TEXT NOT NULL,
  body_encrypted INTEGER DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'request', -- 'request', 'inbox', 'archived', 'trash', 'spam'
  is_read INTEGER DEFAULT 0,
  has_attachment INTEGER DEFAULT 0,
  created_at INTEGER DEFAULT (unixepoch()),
  read_at INTEGER,
  expires_at INTEGER,
  FOREIGN KEY (from_phone) REFERENCES users(phone),
  FOREIGN KEY (to_phone) REFERENCES users(phone)
);

CREATE INDEX IF NOT EXISTS idx_messages_to ON messages(to_phone, status, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_from ON messages(from_phone, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_messages_created ON messages(created_at DESC);

-- Attachments metadata
CREATE TABLE IF NOT EXISTS attachments (
  id TEXT PRIMARY KEY,
  message_id TEXT NOT NULL,
  filename TEXT NOT NULL,
  size INTEGER NOT NULL,
  mime_type TEXT,
  r2_key TEXT NOT NULL,
  created_at INTEGER DEFAULT (unixepoch()),
  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_attachments_message ON attachments(message_id);

-- Spam reports for reputation scoring
CREATE TABLE IF NOT EXISTS spam_reports (
  reporter_phone TEXT NOT NULL,
  reported_phone TEXT NOT NULL,
  reason TEXT,
  created_at INTEGER DEFAULT (unixepoch()),
  PRIMARY KEY (reporter_phone, reported_phone)
);

CREATE INDEX IF NOT EXISTS idx_spam_reported ON spam_reports(reported_phone);

-- OTP codes (temporary, auto-cleanup)
CREATE TABLE IF NOT EXISTS otp_codes (
  phone TEXT PRIMARY KEY,
  code TEXT NOT NULL,
  attempts INTEGER DEFAULT 0,
  created_at INTEGER DEFAULT (unixepoch()),
  expires_at INTEGER NOT NULL
);

-- Phone reputation cache
CREATE TABLE IF NOT EXISTS phone_reputation (
  phone TEXT PRIMARY KEY,
  spam_score INTEGER DEFAULT 0,
  total_reports INTEGER DEFAULT 0,
  total_messages_sent INTEGER DEFAULT 0,
  updated_at INTEGER DEFAULT (unixepoch())
);
