import { database } from "../config/db";

export async function createContactMessagesTable() {
  try {
    const tableQuery = `
        CREATE TABLE IF NOT EXISTS contact_messages (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Message Information
        name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL,
        phone VARCHAR(20),
        subject VARCHAR(100) NOT NULL,
        message TEXT NOT NULL,
        
        -- Message Metadata
        ip_address VARCHAR(45),
        user_agent TEXT,
        referrer_url VARCHAR(500),
        
        -- Status & Processing
        status VARCHAR(20) DEFAULT 'unread' 
          CHECK (status IN ('unread', 'read', 'replied', 'archived', 'spam')),
        priority VARCHAR(20) DEFAULT 'normal' 
          CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
        assigned_to UUID,
        
        -- Categories & Tags
        category VARCHAR(50) DEFAULT 'general',
        tags TEXT[] DEFAULT '{}',
        
        -- Response Tracking
        is_replied BOOLEAN DEFAULT false,
        replied_at TIMESTAMP,
        reply_message TEXT,
        reply_sent_by UUID,
        
        -- Follow-up
        needs_follow_up BOOLEAN DEFAULT false,
        follow_up_date DATE,
        follow_up_notes TEXT,
        
        -- Read Tracking
        is_read BOOLEAN DEFAULT false,
        read_at TIMESTAMP,
        read_by UUID,
        
        -- Spam Detection
        spam_score DECIMAL(3,2) DEFAULT 0.00 CHECK (spam_score >= 0 AND spam_score <= 1),
        is_spam BOOLEAN DEFAULT false,
        spam_reason TEXT,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Foreign Keys
        FOREIGN KEY (assigned_to) REFERENCES accounts(id) ON DELETE SET NULL,
        FOREIGN KEY (reply_sent_by) REFERENCES accounts(id) ON DELETE SET NULL,
        FOREIGN KEY (read_by) REFERENCES accounts(id) ON DELETE SET NULL,
        
        -- Constraints
        CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
        CONSTRAINT valid_phone_message CHECK (phone IS NULL OR phone ~ '^[0-9+\-\s()]{10,20}$'),
        CONSTRAINT message_not_empty CHECK (message <> '')
      );`;

    await database.query(tableQuery);

    const indexes = [""];

    for (const indexQuery of indexes) {
      await database.query(indexQuery);
    }
  } catch (error) {
    console.error("❌ Failed To Create Product Benefit Table.", error);
    process.exit(1);
  }
}
