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

export async function createContactMessageRepliesTable() {
  try {
    const tableQuery = `
        CREATE TABLE IF NOT EXISTS contact_message_replies (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- References
        message_id UUID NOT NULL,
        replied_by UUID NOT NULL,
        
        -- Reply Content
        reply_subject VARCHAR(200) NOT NULL,
        reply_message TEXT NOT NULL,
        
        -- Email Tracking
        email_sent BOOLEAN DEFAULT true,
        email_sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        email_message_id VARCHAR(200),
        email_status VARCHAR(20) DEFAULT 'sent' 
          CHECK (email_status IN ('sent', 'delivered', 'failed', 'bounced')),
        email_error TEXT,
        
        -- Internal Notes
        internal_notes TEXT,
        
        -- Status
        is_internal BOOLEAN DEFAULT false,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Foreign Keys
        FOREIGN KEY (message_id) REFERENCES contact_messages(id) ON DELETE CASCADE,
        FOREIGN KEY (replied_by) REFERENCES accounts(id) ON DELETE CASCADE,
        
        -- Constraints
        CONSTRAINT reply_not_empty CHECK (reply_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);
  }
}
