-- ================================================
-- MESSAGES HUB 0.1% - DATABASE MIGRATION
-- ================================================
-- Created: Jan 17, 2026
-- Feature: Messages Hub Upgrade

-- 1. RESPONSE TIME TRACKING
-- Track how long it takes to respond to inbound messages
ALTER TABLE communications ADD COLUMN IF NOT EXISTS response_time_seconds INTEGER;
CREATE INDEX IF NOT EXISTS idx_comms_response_time ON communications(response_time_seconds) WHERE response_time_seconds IS NOT NULL;

-- 2. MESSAGE FLAGS
-- Allow flagging important messages for follow-up
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged BOOLEAN DEFAULT FALSE;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged_at TIMESTAMPTZ;
ALTER TABLE communications ADD COLUMN IF NOT EXISTS flagged_reason VARCHAR(100);

-- 3. EXTRACTED ENTITIES
-- Store detected phone numbers, emails, dates from messages
CREATE TABLE IF NOT EXISTS extracted_entities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  communication_id UUID REFERENCES communications(id) ON DELETE CASCADE,
  lead_id UUID REFERENCES leads(id) ON DELETE CASCADE,
  entity_type VARCHAR(50) NOT NULL,  -- 'phone', 'email', 'date', 'address'
  entity_value TEXT NOT NULL,
  saved_to_lead BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_extracted_entities_lead ON extracted_entities(lead_id);
CREATE INDEX IF NOT EXISTS idx_extracted_entities_comm ON extracted_entities(communication_id);
