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

export async function createNotificationsTable() {
  try {
    const query = `
      CREATE TABLE IF NOT EXISTS notifications (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Notification Target
        recipient_id UUID NOT NULL,
        sender_id UUID,
        
        -- Notification Content
        type VARCHAR(50) NOT NULL 
          CHECK (type IN (
            'follow_request',
            'follow_accepted',
            'follow_rejected',
            'new_message',
            'new_comment',
            'post_like',
            'post_share',
            'company_update',
            'product_launch',
            'achievement_unlocked',
            'system_alert',
            'security_alert'
          )),
        
        title VARCHAR(200) NOT NULL,
        message TEXT NOT NULL,
        metadata JSONB DEFAULT '{}'::JSONB,
        
        -- Status
        is_read BOOLEAN DEFAULT false,
        is_archived BOOLEAN DEFAULT false,
        priority VARCHAR(10) DEFAULT 'normal' 
          CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
        
        -- Delivery
        delivery_method VARCHAR(20) DEFAULT 'in_app' 
          CHECK (delivery_method IN ('in_app', 'email', 'push', 'sms', 'all')),
        delivery_status VARCHAR(20) DEFAULT 'pending' 
          CHECK (delivery_status IN ('pending', 'sent', 'delivered', 'failed', 'read')),
        
        -- Links & Actions
        action_url VARCHAR(500),
        action_label VARCHAR(50),
        action_data JSONB DEFAULT '{}'::JSONB,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        read_at TIMESTAMP,
        delivered_at TIMESTAMP,
        scheduled_for TIMESTAMP,
        expires_at TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_recipient FOREIGN KEY (recipient_id) 
          REFERENCES accounts(id) ON DELETE CASCADE,
        CONSTRAINT fk_sender FOREIGN KEY (sender_id) 
          REFERENCES accounts(id) ON DELETE SET NULL
      );`;

    await database.query(query);

    // Create indexes
    const indexes = [
      `CREATE INDEX IF NOT EXISTS idx_notifications_recipient_id ON notifications(recipient_id);`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_is_read ON notifications(is_read) WHERE is_read = false;`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_created_at ON notifications(created_at DESC);`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_type ON notifications(type);`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_priority ON notifications(priority) WHERE priority IN ('high', 'urgent');`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_unread_count ON notifications(recipient_id, is_read) WHERE is_read = false;`,
      `CREATE INDEX IF NOT EXISTS idx_notifications_scheduled ON notifications(scheduled_for) WHERE scheduled_for IS NOT NULL;`,
    ];

    for (const indexQuery of indexes) {
      try {
        await database.query(indexQuery);
      } catch (err) {
        if (
          !(err instanceof Error) ||
          !err.message.includes("already exists")
        ) {
          throw err;
        }
      }
    }

    // Function to get unread notification count
    const unreadCountFunction = `
      CREATE OR REPLACE FUNCTION get_unread_notification_count(account_id UUID)
      RETURNS INTEGER AS $$
      DECLARE
        unread_count INTEGER;
      BEGIN
        SELECT COUNT(*) INTO unread_count 
        FROM notifications 
        WHERE recipient_id = account_id 
        AND is_read = false 
        AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP)
        AND (scheduled_for IS NULL OR scheduled_for <= CURRENT_TIMESTAMP);
        
        RETURN unread_count;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(unreadCountFunction);

    // Function to create follow notification
    const followNotificationFunction = `
      CREATE OR REPLACE FUNCTION create_follow_notification(
        p_follower_id UUID,
        p_following_id UUID,
        p_notification_type VARCHAR
      )
      RETURNS UUID AS $$
      DECLARE
        follower_username VARCHAR;
        follower_display_name VARCHAR;
        follower_account_type VARCHAR;
        following_account_type VARCHAR;
        notification_title VARCHAR;
        notification_message TEXT;
        notification_id UUID;
      BEGIN
        -- Get follower details
        SELECT username, display_name, account_type 
        INTO follower_username, follower_display_name, follower_account_type
        FROM accounts WHERE id = p_follower_id;
        
        -- Get following account type
        SELECT account_type INTO following_account_type
        FROM accounts WHERE id = p_following_id;
        
        -- Determine notification content based on type
        CASE p_notification_type
          WHEN 'follow_request' THEN
            IF follower_account_type = 'user' AND following_account_type = 'user' THEN
              notification_title := 'New Follow Request';
              notification_message := follower_display_name || ' wants to follow you';
            ELSIF follower_account_type = 'user' AND following_account_type = 'company' THEN
              notification_title := 'New Company Follower';
              notification_message := follower_display_name || ' wants to follow your company';
            ELSIF follower_account_type = 'company' AND following_account_type = 'user' THEN
              notification_title := 'Company Follow Request';
              notification_message := follower_display_name || ' wants to follow you';
            ELSE
              notification_title := 'Company Follow Request';
              notification_message := follower_display_name || ' wants to follow your company';
            END IF;
            
          WHEN 'follow_accepted' THEN
            notification_title := 'Follow Request Accepted';
            notification_message := 'Your follow request has been accepted';
            
          WHEN 'follow_rejected' THEN
            notification_title := 'Follow Request Declined';
            notification_message := 'Your follow request was not accepted';
            
          ELSE
            notification_title := 'Follow Update';
            notification_message := 'There is an update to your follow request';
        END CASE;
        
        -- Create notification
        INSERT INTO notifications (
          recipient_id,
          sender_id,
          type,
          title,
          message,
          metadata,
          action_url,
          action_label,
          priority
        ) VALUES (
          CASE p_notification_type 
            WHEN 'follow_request' THEN p_following_id
            ELSE p_follower_id
          END,
          CASE p_notification_type 
            WHEN 'follow_request' THEN p_follower_id
            ELSE p_following_id
          END,
          p_notification_type,
          notification_title,
          notification_message,
          json_build_object(
            'follower_id', p_follower_id,
            'following_id', p_following_id,
            'follower_username', follower_username,
            'follower_display_name', follower_display_name,
            'follower_type', follower_account_type,
            'following_type', following_account_type
          ),
          '/profile/' || follower_username,
          'View Profile',
          'normal'
        ) RETURNING id INTO notification_id;
        
        RETURN notification_id;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(followNotificationFunction);

    // Trigger for follow notifications
    const followNotificationTriggerFunction = `
      CREATE OR REPLACE FUNCTION trigger_follow_notification()
      RETURNS TRIGGER AS $$
      BEGIN
        -- Create notification for follow request
        IF NEW.status = 'pending' THEN
          PERFORM create_follow_notification(
            NEW.follower_id,
            NEW.following_id,
            'follow_request'
          );
        END IF;
        
        -- Create notification for accepted follow
        IF NEW.status = 'accepted' AND OLD.status = 'pending' THEN
          PERFORM create_follow_notification(
            NEW.follower_id,
            NEW.following_id,
            'follow_accepted'
          );
        END IF;
        
        -- Create notification for rejected follow
        IF NEW.status = 'rejected' AND OLD.status = 'pending' THEN
          PERFORM create_follow_notification(
            NEW.follower_id,
            NEW.following_id,
            'follow_rejected'
          );
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(followNotificationTriggerFunction);

    // Create trigger for follow notifications
    await database.query(`
      DROP TRIGGER IF EXISTS trigger_create_follow_notification ON follows;
    `);

    await database.query(`
      CREATE TRIGGER trigger_create_follow_notification
      AFTER INSERT OR UPDATE OF status ON follows
      FOR EACH ROW
      EXECUTE FUNCTION trigger_follow_notification();
    `);

    console.log("✅ Notifications table created successfully");
  } catch (err) {
    console.error("❌ Failed To Create Notifications Table.", err);
    process.exit(1);
  }
}
