-- Claw CRM Schema
-- Run: PGPASSWORD=eventheodds_dev_password psql -h 127.0.0.1 -p 5433 -U eventheodds -d eventheodds_sports -f schema.sql

-- Unified contacts across all bots
CREATE TABLE IF NOT EXISTS crm_contacts (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  telegram_id   BIGINT UNIQUE NOT NULL,
  username      VARCHAR(100),
  display_name  VARCHAR(200),
  first_seen    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_active   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  plan          VARCHAR(30) DEFAULT 'free',
  role          VARCHAR(30) DEFAULT 'user',
  notes         TEXT,
  sc_user_id    UUID REFERENCES sc_users(id),
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_contacts_telegram ON crm_contacts(telegram_id);
CREATE INDEX IF NOT EXISTS idx_crm_contacts_last_active ON crm_contacts(last_active DESC);

-- Synced session metadata
CREATE TABLE IF NOT EXISTS crm_sessions (
  id              UUID PRIMARY KEY,
  agent           VARCHAR(50) NOT NULL,
  contact_id      UUID REFERENCES crm_contacts(id),
  chat_type       VARCHAR(20) NOT NULL,
  channel         VARCHAR(30) NOT NULL DEFAULT 'telegram',
  group_id        BIGINT,
  group_name      VARCHAR(200),
  started_at      TIMESTAMPTZ NOT NULL,
  last_activity   TIMESTAMPTZ NOT NULL,
  message_count   INT NOT NULL DEFAULT 0,
  total_tokens    BIGINT NOT NULL DEFAULT 0,
  input_tokens    BIGINT NOT NULL DEFAULT 0,
  output_tokens   BIGINT NOT NULL DEFAULT 0,
  model           VARCHAR(100),
  source_file     TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_sessions_agent ON crm_sessions(agent);
CREATE INDEX IF NOT EXISTS idx_crm_sessions_contact ON crm_sessions(contact_id);
CREATE INDEX IF NOT EXISTS idx_crm_sessions_last_activity ON crm_sessions(last_activity DESC);

-- Synced messages
CREATE TABLE IF NOT EXISTS crm_messages (
  id            VARCHAR(100) PRIMARY KEY,
  session_id    UUID NOT NULL REFERENCES crm_sessions(id),
  contact_id    UUID REFERENCES crm_contacts(id),
  role          VARCHAR(20) NOT NULL,
  content       TEXT,
  content_type  VARCHAR(30) DEFAULT 'text',
  tool_name     VARCHAR(100),
  tokens        INT,
  timestamp     TIMESTAMPTZ NOT NULL,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_crm_messages_session ON crm_messages(session_id, timestamp);
CREATE INDEX IF NOT EXISTS idx_crm_messages_contact ON crm_messages(contact_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_crm_messages_timestamp ON crm_messages(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_crm_messages_content_search ON crm_messages USING gin(to_tsvector('english', coalesce(content, '')));

-- Sync state tracking
CREATE TABLE IF NOT EXISTS crm_sync_state (
  id            SERIAL PRIMARY KEY,
  source_file   TEXT UNIQUE NOT NULL,
  agent         VARCHAR(50) NOT NULL,
  last_line     INT NOT NULL DEFAULT 0,
  last_byte     BIGINT NOT NULL DEFAULT 0,
  last_synced   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  status        VARCHAR(20) DEFAULT 'active'
);

-- Agent registry (all PM2 processes / workers)
CREATE TABLE IF NOT EXISTS crm_agents (
  id            SERIAL PRIMARY KEY,
  pm2_name      VARCHAR(100) UNIQUE NOT NULL,
  display_name  VARCHAR(200) NOT NULL,
  project       VARCHAR(100) NOT NULL,
  category      VARCHAR(50) NOT NULL,
  description   TEXT,
  cwd           TEXT,
  env_file      TEXT,
  config_file   TEXT,
  color         VARCHAR(20) DEFAULT 'slate',
  created_at    TIMESTAMPTZ DEFAULT NOW()
);

-- Seed all PM2 agents
INSERT INTO crm_agents (pm2_name, display_name, project, category, description, cwd, env_file, config_file, color)
VALUES
  ('eventheodds', 'EventheOdds App', 'eventheodds', 'app', 'Main Next.js application', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('eventheodds-rag', 'RAG Service', 'eventheodds', 'worker', 'Retrieval-augmented generation service', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('eventheodds-mcp', 'MCP Server', 'eventheodds', 'gateway', 'Model Context Protocol server', '/var/www/html/eventheodds/mcp-server', '/var/www/html/eventheodds/mcp-server/.env', NULL, 'blue'),
  ('sgo-props-normalize', 'SGO Props Normalizer', 'eventheodds', 'worker', 'Normalizes SGO props data pipeline', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('backtest-worker', 'Backtest Worker', 'eventheodds', 'worker', 'Runs prop backtesting jobs', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-content-worker', 'SEO Content Worker', 'eventheodds', 'worker', 'Generates SEO content for EventheOdds', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-llm-worker', 'SEO LLM Worker', 'eventheodds', 'worker', 'LLM-powered SEO optimization', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-backlink-worker', 'SEO Backlink Worker', 'eventheodds', 'worker', 'Backlink monitoring and outreach', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-competitor-worker', 'SEO Competitor Worker', 'eventheodds', 'worker', 'Competitor analysis and tracking', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-performance-worker', 'SEO Performance Worker', 'eventheodds', 'worker', 'SEO performance metrics tracking', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-daily-games-worker', 'SEO Daily Games Worker', 'eventheodds', 'worker', 'Daily games content generation', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-odds-alerts-worker', 'SEO Odds Alerts Worker', 'eventheodds', 'worker', 'Odds movement alerts for SEO', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('seo-social-worker', 'SEO Social Worker', 'eventheodds', 'worker', 'Social media content distribution', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('compliance-worker', 'Compliance Worker', 'eventheodds', 'worker', 'Regulatory compliance checks', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('ai-lawyer-rag', 'AI Lawyer RAG', 'eventheodds', 'worker', 'Legal AI retrieval-augmented generation', '/var/www/html/eventheodds', '/var/www/html/eventheodds/.env', NULL, 'blue'),
  ('sportsclaw-guru', 'SportsClaw Guru', 'sportsclaw-guru', 'app', 'SportsClaw blog frontend (Next.js)', '/var/www/html/sportsclaw-guru', '/var/www/html/sportsclaw-guru/.env.local', NULL, 'emerald'),
  ('sportsclaw-api', 'SportsClaw API', 'sportsclaw-guru', 'app', 'SportsClaw blog backend (Express)', '/var/www/html/sportsclaw-guru/backend', '/var/www/html/sportsclaw-guru/backend/.env', NULL, 'emerald'),
  ('sportsclaw-seo-worker', 'SportsClaw SEO Worker', 'sportsclaw-guru', 'worker', 'Generates SEO blog posts via Grok', '/var/www/html/sportsclaw-guru', '/var/www/html/sportsclaw-guru/backend/.env', NULL, 'emerald'),
  ('openclaw-gateway', 'OpenClaw Gateway', 'openclaw', 'gateway', 'OpenClaw Telegram bot gateway', '/home/administrator', NULL, NULL, 'purple'),
  ('data-cleaner', 'Data Cleaner', 'openclaw', 'worker', 'Sports data cleaning and integrity agent (14 tasks)', '/home/administrator/.openclaw/agents/sportsclaw/workspace/agents/data_cleaner', NULL, '/home/administrator/.openclaw/agents/sportsclaw/workspace/agents/data_cleaner/config.py', 'purple'),
  ('langgraph-seo', 'LangGraph SEO', 'langgraph', 'worker', 'LangGraph-based SEO pipeline service', '/var/www/html/langgraph-service', NULL, NULL, 'amber'),
  ('meltmybill', 'MeltMyBill', 'meltmybill', 'app', 'Solar bill reduction app', '/var/www/html/meltmybillsolar', '/var/www/html/meltmybillsolar/.env', NULL, 'rose'),
  ('claw-crm', 'Claw CRM', 'claw-crm', 'app', 'CRM dashboard frontend (Next.js)', '/var/www/html/claw-crm', '/var/www/html/claw-crm/.env.local', NULL, 'cyan'),
  ('claw-crm-api', 'Claw CRM API', 'claw-crm', 'app', 'CRM dashboard backend (Express)', '/var/www/html/claw-crm/backend', '/var/www/html/claw-crm/backend/.env', NULL, 'cyan'),
  ('claw-crm-sync', 'Claw CRM Sync', 'claw-crm', 'sync', 'JSONL conversation sync worker', '/var/www/html/claw-crm/backend', '/var/www/html/claw-crm/backend/.env', NULL, 'cyan')
ON CONFLICT (pm2_name) DO NOTHING;
