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

export async function createFollowsTable() {
  try {
    const query = `
      CREATE TABLE IF NOT EXISTS follows (
        -- Primary Key
        id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
        
        -- Relationship
        follower_id UUID NOT NULL,
        following_id UUID NOT NULL,
        
        -- Follow Type: 'user_to_user', 'user_to_company', 'company_to_user', 'company_to_company'
        follow_type VARCHAR(20) NOT NULL 
          CHECK (follow_type IN ('user_to_user', 'user_to_company', 'company_to_user', 'company_to_company')),
        
        -- Status
        status VARCHAR(20) DEFAULT 'pending' 
          CHECK (status IN ('pending', 'accepted', 'rejected', 'blocked')),
        
        -- Settings
        receive_notifications BOOLEAN DEFAULT true,
        notifications_settings JSONB DEFAULT '{
          "new_posts": true,
          "product_updates": true,
          "milestones": true,
          "achievements": true
        }'::JSONB,
        
        -- Metadata
        is_mutual BOOLEAN DEFAULT false,
        follow_request_message TEXT,
        
        -- Timestamps
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        accepted_at TIMESTAMP,
        
        -- Constraints
        CONSTRAINT fk_follower FOREIGN KEY (follower_id) 
          REFERENCES accounts(id) ON DELETE CASCADE,
        CONSTRAINT fk_following FOREIGN KEY (following_id) 
          REFERENCES accounts(id) ON DELETE CASCADE,
        CONSTRAINT unique_followership UNIQUE(follower_id, following_id),
        CONSTRAINT cannot_follow_self CHECK (follower_id != following_id)
      );

      -- Indexes for better performance
      CREATE INDEX IF NOT EXISTS idx_follows_follower_id ON follows(follower_id);
      CREATE INDEX IF NOT EXISTS idx_follows_following_id ON follows(following_id);
      CREATE INDEX IF NOT EXISTS idx_follows_follower_following ON follows(follower_id, following_id);
      CREATE INDEX IF NOT EXISTS idx_follows_status ON follows(status) WHERE status = 'accepted';
      CREATE INDEX IF NOT EXISTS idx_follows_follow_type ON follows(follow_type);
      CREATE INDEX IF NOT EXISTS idx_follows_created_at ON follows(created_at DESC);
      
      -- Index for mutual follows
      CREATE INDEX IF NOT EXISTS idx_follows_is_mutual ON follows(is_mutual) WHERE is_mutual = true;
    `;

    await database.query(query);

    const validateFollowTypeFunction = `
        CREATE OR REPLACE FUNCTION validate_follow_type()
        RETURNS TRIGGER AS $$
        DECLARE
          follower_type VARCHAR;
          following_type VARCHAR;
        BEGIN
          SELECT account_type INTO follower_type FROM accounts WHERE id = NEW.follower_id;
          SELECT account_type INTO following_type FROM accounts WHERE id = NEW.following_id;

          IF follower_type IS NULL OR following_type IS NULL THEN
            RAISE EXCEPTION 'Invalid follower or following account';
          END IF;

          IF NOT (
            (NEW.follow_type = 'user_to_user' AND follower_type = 'user' AND following_type = 'user') OR
            (NEW.follow_type = 'user_to_company' AND follower_type = 'user' AND following_type = 'company') OR
            (NEW.follow_type = 'company_to_user' AND follower_type = 'company' AND following_type = 'user') OR
            (NEW.follow_type = 'company_to_company' AND follower_type = 'company' AND following_type = 'company')
          ) THEN
            RAISE EXCEPTION
              'Invalid follow_type % for follower % and following %',
              NEW.follow_type, follower_type, following_type;
          END IF;

          RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
    `;

    await database.query(validateFollowTypeFunction);

    await database.query(`
      DROP TRIGGER IF EXISTS trigger_validate_follow_type ON follows;
    `);

    await database.query(`
      CREATE TRIGGER trigger_validate_follow_type
      BEFORE INSERT OR UPDATE ON follows
      FOR EACH ROW
      EXECUTE FUNCTION validate_follow_type();
    `);

    // Function to update mutual follow status
    const updateMutualFollowFunction = `
      CREATE OR REPLACE FUNCTION update_mutual_follow_status()
      RETURNS TRIGGER AS $$
      BEGIN
        -- Check if reverse follow exists and update mutual status
        IF NEW.status = 'accepted' THEN
          -- Check if reverse follow exists
          IF EXISTS (
            SELECT 1 FROM follows 
            WHERE follower_id = NEW.following_id 
            AND following_id = NEW.follower_id 
            AND status = 'accepted'
          ) THEN
            -- Update both follows to mutual
            UPDATE follows SET is_mutual = true, updated_at = CURRENT_TIMESTAMP
            WHERE (follower_id = NEW.follower_id AND following_id = NEW.following_id)
               OR (follower_id = NEW.following_id AND following_id = NEW.follower_id);
          END IF;
        END IF;
        
        -- If follow is removed, update mutual status
        IF OLD.status = 'accepted' AND (NEW.status != 'accepted' OR NEW IS NULL) THEN
          UPDATE follows SET is_mutual = false, updated_at = CURRENT_TIMESTAMP
          WHERE (follower_id = OLD.follower_id AND following_id = OLD.following_id)
             OR (follower_id = OLD.following_id AND following_id = OLD.follower_id);
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(updateMutualFollowFunction);

    // Create trigger for mutual follows
    await database.query(`
      DROP TRIGGER IF EXISTS trigger_update_mutual_follow ON follows;
    `);

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

    // Function to get follow statistics
    const followStatsFunction = `
      CREATE OR REPLACE FUNCTION get_follow_stats(account_id UUID)
      RETURNS JSON AS $$
      DECLARE
        follower_count INTEGER;
        following_count INTEGER;
        mutual_count INTEGER;
        account_type VARCHAR;
        stats JSON;
      BEGIN
        -- Get account type
        SELECT account_type INTO account_type FROM accounts WHERE id = account_id;
        
        -- Get follower count (people/companies following this account)
        SELECT COUNT(*) INTO follower_count 
        FROM follows 
        WHERE following_id = account_id AND status = 'accepted';
        
        -- Get following count (people/companies this account follows)
        SELECT COUNT(*) INTO following_count 
        FROM follows 
        WHERE follower_id = account_id AND status = 'accepted';
        
        -- Get mutual follows count
        SELECT COUNT(*) INTO mutual_count 
        FROM follows 
        WHERE follower_id = account_id AND status = 'accepted' AND is_mutual = true;
        
        stats := json_build_object(
          'follower_count', follower_count,
          'following_count', following_count,
          'mutual_count', mutual_count,
          'account_type', account_type,
          'follow_stats_updated', CURRENT_TIMESTAMP
        );
        
        RETURN stats;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(followStatsFunction);

    // Function to check follow relationship
    const checkFollowRelationshipFunction = `
      CREATE OR REPLACE FUNCTION check_follow_relationship(
        follower_id UUID,
        following_id UUID
      )
      RETURNS JSON AS $$
      DECLARE
        relationship_record RECORD;
        result JSON;
      BEGIN
        SELECT * INTO relationship_record 
        FROM follows 
        WHERE follower_id = $1 AND following_id = $2;
        
        IF FOUND THEN
          result := json_build_object(
            'is_following', true,
            'status', relationship_record.status,
            'is_mutual', relationship_record.is_mutual,
            'created_at', relationship_record.created_at,
            'follow_id', relationship_record.id
          );
        ELSE
          result := json_build_object(
            'is_following', false
          );
        END IF;
        
        RETURN result;
      END;
      $$ LANGUAGE plpgsql;
    `;

    await database.query(checkFollowRelationshipFunction);

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

// CONSTRAINT valid_follow_type CHECK (
//           (follow_type = 'user_to_user' AND
//             (SELECT account_type FROM accounts WHERE id = follower_id) = 'user' AND
//             (SELECT account_type FROM accounts WHERE id = following_id) = 'user') OR
//           (follow_type = 'user_to_company' AND
//             (SELECT account_type FROM accounts WHERE id = follower_id) = 'user' AND
//             (SELECT account_type FROM accounts WHERE id = following_id) = 'company') OR
//           (follow_type = 'company_to_user' AND
//             (SELECT account_type FROM accounts WHERE id = follower_id) = 'company' AND
//             (SELECT account_type FROM accounts WHERE id = following_id) = 'user') OR
//           (follow_type = 'company_to_company' AND
//             (SELECT account_type FROM accounts WHERE id = follower_id) = 'company' AND
//             (SELECT account_type FROM accounts WHERE id = following_id) = 'company')
//         )
