// database/contactTable.ts
import database from "../config/db";
import { QueryResult } from "pg";

export async function createContactTable() {
  try {
    const tableQuery = `
      CREATE TABLE IF NOT EXISTS contact_settings (
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Company Information
        company_name VARCHAR(200) NOT NULL DEFAULT 'Toolffy',
        company_email VARCHAR(100) NOT NULL DEFAULT 'support@toolffy.com',
        company_phone VARCHAR(20),
        company_address TEXT,
        company_location_city VARCHAR(100),
        company_location_country VARCHAR(100),
        company_location_coordinates JSONB,
        
        -- Contact Information
        support_email VARCHAR(100),
        info_email VARCHAR(100),
        sales_email VARCHAR(100),
        phone_numbers JSONB DEFAULT '[]'::JSONB,
        alternative_contacts JSONB DEFAULT '[]'::JSONB,
        
        -- Working Hours
        working_hours JSONB DEFAULT '{
          "monday": {"open": "09:00", "close": "18:00", "is_open": true},
          "tuesday": {"open": "09:00", "close": "18:00", "is_open": true},
          "wednesday": {"open": "09:00", "close": "18:00", "is_open": true},
          "thursday": {"open": "09:00", "close": "18:00", "is_open": true},
          "friday": {"open": "09:00", "close": "18:00", "is_open": true},
          "saturday": {"open": "10:00", "close": "16:00", "is_open": true},
          "sunday": {"open": null, "close": null, "is_open": false}
        }'::JSONB,
        
        -- Social Media Links
        social_media JSONB DEFAULT '{
          "facebook": null,
          "twitter": null,
          "linkedin": null,
          "instagram": null,
          "youtube": null,
          "github": null,
          "discord": null
        }'::JSONB,
        
        -- Contact Form Settings
        contact_form_enabled BOOLEAN DEFAULT true,
        contact_subjects JSONB DEFAULT '[
          {"value": "general", "label": "General Inquiry"},
          {"value": "support", "label": "Technical Support"},
          {"value": "business", "label": "Business Partnership"},
          {"value": "feedback", "label": "Feedback"},
          {"value": "other", "label": "Other"}
        ]'::JSONB,
        
        -- Quick Info & Features
        quick_info JSONB DEFAULT '[
          {"icon": "check-circle", "text": "Response within 24 hours"},
          {"icon": "users", "text": "Professional support team"},
          {"icon": "clock", "text": "Available 6 days a week"},
          {"icon": "shield", "text": "Secure and confidential"}
        ]'::JSONB,
        
        -- Why Contact Reasons
        contact_reasons JSONB DEFAULT '[
          {"icon": "message-square", "title": "General Inquiry", "description": "Have a question? We''re here to help!"},
          {"icon": "briefcase", "title": "Business Partnership", "description": "Interested in partnering with us?"},
          {"icon": "users", "title": "Support", "description": "Need technical assistance?"},
          {"icon": "star", "title": "Feedback", "description": "Share your thoughts with us"}
        ]'::JSONB,
        
        -- Map Settings
        map_enabled BOOLEAN DEFAULT true,
        map_embed_url TEXT,
        map_location_name VARCHAR(200),
        map_coordinates JSONB DEFAULT '{"lat": 23.8103, "lng": 90.4125}',
        
        -- SEO Settings
        contact_page_title VARCHAR(200) DEFAULT 'Contact Us',
        contact_page_description TEXT,
        contact_page_meta_keywords TEXT[] DEFAULT '{}',
        
        -- Response Settings
        auto_response_enabled BOOLEAN DEFAULT true,
        auto_response_subject VARCHAR(200) DEFAULT 'Thank you for contacting us',
        auto_response_message TEXT,
        
        -- Status
        is_active BOOLEAN DEFAULT true,
        maintenance_mode BOOLEAN DEFAULT false,
        maintenance_message TEXT,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
        -- Constraints
        CONSTRAINT valid_emails CHECK (
          (company_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$') AND
          (support_email IS NULL OR support_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$') AND
          (info_email IS NULL OR info_email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$')
        ),
        CONSTRAINT valid_phone CHECK (
          company_phone IS NULL OR company_phone ~ '^[0-9+\\-\\s()]{10,20}$'
        )
      );

      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 <> '')
      );

      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);

    // Create indexes for better performance
    const indexes = [
      // contact_settings indexes
      `CREATE INDEX IF NOT EXISTS idx_contact_settings_active ON contact_settings(is_active) WHERE is_active = true;`,
      `CREATE INDEX IF NOT EXISTS idx_contact_settings_company_email ON contact_settings(company_email);`,
      
      // contact_messages indexes
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_email ON contact_messages(email);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_status ON contact_messages(status);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_priority ON contact_messages(priority);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_created_at ON contact_messages(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_category ON contact_messages(category);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_is_read ON contact_messages(is_read) WHERE is_read = false;`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_is_replied ON contact_messages(is_replied) WHERE is_replied = false;`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_assigned_to ON contact_messages(assigned_to);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_spam ON contact_messages(is_spam) WHERE is_spam = true;`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_needs_follow_up ON contact_messages(needs_follow_up) WHERE needs_follow_up = true;`,
      
      // Composite indexes for common queries
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_status_priority ON contact_messages(status, priority);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_email_status ON contact_messages(email, status);`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_created_status ON contact_messages(created_at DESC, status);`,
      
      // Full-text search indexes
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_search_name ON contact_messages USING gin(to_tsvector('english', name));`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_search_message ON contact_messages USING gin(to_tsvector('english', message));`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_search_subject ON contact_messages USING gin(to_tsvector('english', subject));`,
      `CREATE INDEX IF NOT EXISTS idx_contact_messages_search_tags ON contact_messages USING gin(tags);`,
      
      // contact_message_replies indexes
      `CREATE INDEX IF NOT EXISTS idx_message_replies_message_id ON contact_message_replies(message_id);`,
      `CREATE INDEX IF NOT EXISTS idx_message_replies_replied_by ON contact_message_replies(replied_by);`,
      `CREATE INDEX IF NOT EXISTS idx_message_replies_created_at ON contact_message_replies(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_message_replies_email_status ON contact_message_replies(email_status);`,
    ];

    for (const indexQuery of indexes) {
      try {
        await database.query(indexQuery);
      } catch (err) {
        const errorMessage = err instanceof Error ? err.message : String(err);
        if (!(err instanceof Error) || !errorMessage.includes("already exists")) {
          console.error("Index creation error:", errorMessage);
        }
      }
    }

    // Create functions for contact settings
    const functions = `
      -- Function to get contact page data
      CREATE OR REPLACE FUNCTION get_contact_page_data()
      RETURNS JSON AS $$
      DECLARE
        settings_record RECORD;
        result JSON;
      BEGIN
        SELECT * INTO settings_record 
        FROM contact_settings 
        WHERE is_active = true 
        LIMIT 1;
        
        IF NOT FOUND THEN
          -- Return default values if no settings exist
          result := json_build_object(
            'company_name', 'Toolffy',
            'company_email', 'support@toolffy.com',
            'company_phone', '+880 1234-567890',
            'company_address', 'Dhaka, Bangladesh',
            'working_hours', '{
              "monday": {"open": "09:00", "close": "18:00", "is_open": true},
              "tuesday": {"open": "09:00", "close": "18:00", "is_open": true},
              "wednesday": {"open": "09:00", "close": "18:00", "is_open": true},
              "thursday": {"open": "09:00", "close": "18:00", "is_open": true},
              "friday": {"open": "09:00", "close": "18:00", "is_open": true},
              "saturday": {"open": "10:00", "close": "16:00", "is_open": true},
              "sunday": {"open": null, "close": null, "is_open": false}
            }'::JSONB,
            'social_media', '{
              "facebook": null,
              "twitter": null,
              "linkedin": null,
              "instagram": null,
              "youtube": null,
              "github": null,
              "discord": null
            }'::JSONB,
            'contact_form_enabled', true,
            'contact_subjects', '[
              {"value": "general", "label": "General Inquiry"},
              {"value": "support", "label": "Technical Support"},
              {"value": "business", "label": "Business Partnership"},
              {"value": "feedback", "label": "Feedback"},
              {"value": "other", "label": "Other"}
            ]'::JSONB,
            'quick_info', '[
              {"icon": "check-circle", "text": "Response within 24 hours"},
              {"icon": "users", "text": "Professional support team"},
              {"icon": "clock", "text": "Available 6 days a week"},
              {"icon": "shield", "text": "Secure and confidential"}
            ]'::JSONB,
            'contact_reasons', '[
              {"icon": "message-square", "title": "General Inquiry", "description": "Have a question? We''re here to help!"},
              {"icon": "briefcase", "title": "Business Partnership", "description": "Interested in partnering with us?"},
              {"icon": "users", "title": "Support", "description": "Need technical assistance?"},
              {"icon": "star", "title": "Feedback", "description": "Share your thoughts with us"}
            ]'::JSONB,
            'map_enabled', true,
            'map_coordinates', '{"lat": 23.8103, "lng": 90.4125}',
            'auto_response_enabled', true,
            'is_active', true
          );
        ELSE
          result := row_to_json(settings_record);
        END IF;
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to calculate message statistics
      CREATE OR REPLACE FUNCTION get_contact_message_stats()
      RETURNS TABLE (
        total_messages INTEGER,
        unread_messages INTEGER,
        replied_messages INTEGER,
        spam_messages INTEGER,
        today_messages INTEGER,
        urgent_messages INTEGER
      ) AS $$
      BEGIN
        RETURN QUERY
        SELECT 
          COUNT(*)::INTEGER as total_messages,
          COUNT(CASE WHEN status = 'unread' THEN 1 END)::INTEGER as unread_messages,
          COUNT(CASE WHEN is_replied = true THEN 1 END)::INTEGER as replied_messages,
          COUNT(CASE WHEN is_spam = true THEN 1 END)::INTEGER as spam_messages,
          COUNT(CASE WHEN DATE(created_at) = CURRENT_DATE THEN 1 END)::INTEGER as today_messages,
          COUNT(CASE WHEN priority = 'urgent' THEN 1 END)::INTEGER as urgent_messages
        FROM contact_messages
        WHERE is_spam = false;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to update message status
      CREATE OR REPLACE FUNCTION update_message_status(
        message_uuid UUID,
        new_status VARCHAR,
        user_uuid UUID DEFAULT NULL
      )
      RETURNS JSON AS $$
      DECLARE
        result JSON;
      BEGIN
        UPDATE contact_messages
        SET 
          status = new_status,
          updated_at = CURRENT_TIMESTAMP,
          read_at = CASE 
            WHEN new_status = 'read' AND is_read = false THEN CURRENT_TIMESTAMP
            ELSE read_at
          END,
          read_by = CASE 
            WHEN new_status = 'read' AND is_read = false THEN user_uuid
            ELSE read_by
          END,
          is_read = CASE 
            WHEN new_status = 'read' THEN true
            WHEN new_status = 'unread' THEN false
            ELSE is_read
          END
        WHERE id = message_uuid
        RETURNING id, name, email, status, is_read INTO result;
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;

      -- Function to check if email is spam
      CREATE OR REPLACE FUNCTION check_spam_message(
        p_email VARCHAR,
        p_name VARCHAR,
        p_message TEXT,
        p_ip_address VARCHAR
      )
      RETURNS DECIMAL(3,2) AS $$
      DECLARE
        spam_score DECIMAL(3,2) := 0.0;
        similar_messages INTEGER;
        recent_messages INTEGER;
        suspicious_keywords TEXT[] := ARRAY[
          'viagra', 'casino', 'lottery', 'click here', 'buy now',
          'urgent', 'investment', 'money', 'free', 'winner',
          'credit card', 'password', 'account', 'verify', 'login'
        ];
        keyword TEXT;
      BEGIN
        -- Check for multiple recent messages from same IP (10 points)
        SELECT COUNT(*) INTO recent_messages
        FROM contact_messages
        WHERE ip_address = p_ip_address 
          AND created_at > CURRENT_TIMESTAMP - INTERVAL '1 hour';
        
        IF recent_messages >= 5 THEN
          spam_score := spam_score + 0.3;
        END IF;

        -- Check for similar messages from same email (10 points)
        SELECT COUNT(*) INTO similar_messages
        FROM contact_messages
        WHERE email = p_email 
          AND created_at > CURRENT_TIMESTAMP - INTERVAL '24 hours';
        
        IF similar_messages >= 3 THEN
          spam_score := spam_score + 0.3;
        END IF;

        -- Check for suspicious keywords (40 points)
        FOREACH keyword IN ARRAY suspicious_keywords LOOP
          IF LOWER(p_message) LIKE '%' || keyword || '%' THEN
            spam_score := spam_score + 0.1;
          END IF;
        END LOOP;

        -- Check for excessive caps (10 points)
        IF (LENGTH(p_message) - LENGTH(REPLACE(p_message, ' ', ''))) > 0 THEN
          IF UPPER(p_message) = p_message THEN
            spam_score := spam_score + 0.1;
          END IF;
        END IF;

        -- Check for suspicious email patterns (10 points)
        IF p_email ~* '.*[0-9]{5,}@.*' THEN
          spam_score := spam_score + 0.1;
        END IF;

        -- Cap at 1.0
        IF spam_score > 1.0 THEN
          spam_score := 1.0;
        END IF;

        RETURN spam_score;
      END;
      $$ LANGUAGE plpgsql;

      -- Trigger function for automatic updated_at
      CREATE OR REPLACE FUNCTION update_contact_tables_updated_at()
      RETURNS TRIGGER AS $$
      BEGIN
        NEW.updated_at = CURRENT_TIMESTAMP;
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(functions);

    // Create triggers for automatic updated_at
    const triggers = `
      DROP TRIGGER IF EXISTS set_contact_settings_updated_at ON contact_settings;
      DROP TRIGGER IF EXISTS set_contact_messages_updated_at ON contact_messages;
      DROP TRIGGER IF EXISTS set_message_replies_updated_at ON contact_message_replies;

      CREATE TRIGGER set_contact_settings_updated_at
      BEFORE UPDATE ON contact_settings
      FOR EACH ROW
      EXECUTE FUNCTION update_contact_tables_updated_at();

      CREATE TRIGGER set_contact_messages_updated_at
      BEFORE UPDATE ON contact_messages
      FOR EACH ROW
      EXECUTE FUNCTION update_contact_tables_updated_at();

      CREATE TRIGGER set_message_replies_updated_at
      BEFORE UPDATE ON contact_message_replies
      FOR EACH ROW
      EXECUTE FUNCTION update_contact_tables_updated_at();
    `;

    await database.query(triggers);

    // Insert default contact settings if not exists
    const insertDefaultSettings = `
      INSERT INTO contact_settings (
        company_name, 
        company_email, 
        company_phone, 
        company_address,
        company_location_city,
        company_location_country,
        contact_page_title,
        contact_page_description
      )
      SELECT 
        'Toolffy',
        'support@toolffy.com',
        '+880 1234-567890',
        'Dhaka, Bangladesh',
        'Dhaka',
        'Bangladesh',
        'Contact Us',
        'Have questions? We''d love to hear from you. Send us a message and we''ll respond as soon as possible.'
      WHERE NOT EXISTS (SELECT 1 FROM contact_settings LIMIT 1);
    `;

    await database.query(insertDefaultSettings);

    console.log("✅ Contact tables created successfully");
  } catch (error) {
    console.error("❌ Failed to create contact tables:", error);
    console.error("Error details:", error);
    process.exit(1);
  }
}