-- Communication tracking tables

CREATE TABLE IF NOT EXISTS communications (
  id SERIAL PRIMARY KEY,
  lead_id INTEGER REFERENCES leads(id) ON DELETE CASCADE,
  
  communication_type VARCHAR(50) NOT NULL CHECK (communication_type IN ('whatsapp', 'sms', 'email', 'call')),
  direction VARCHAR(20) CHECK (direction IN ('inbound', 'outbound')),
  
  recipient VARCHAR(255),
  subject VARCHAR(500),
  message TEXT NOT NULL,
  
  status VARCHAR(50) DEFAULT 'sent' CHECK (status IN ('pending', 'sent', 'delivered', 'failed', 'read')),
  external_id VARCHAR(255),
  
  sent_by INTEGER REFERENCES users(id),
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  delivered_at TIMESTAMP,
  read_at TIMESTAMP,
  
  metadata JSONB
);

CREATE INDEX IF NOT EXISTS idx_communications_lead ON communications(lead_id);
CREATE INDEX IF NOT EXISTS idx_communications_type ON communications(communication_type);
CREATE INDEX IF NOT EXISTS idx_communications_status ON communications(status);
