เทมเพลตข้อมูลวงจรการพัฒนาซอฟต์แวร์

#!/bin/bash

# === CONFIGURATION ===
# The target GitHub repository in "owner/repo" format.
REPO="[owner/repository]"

# The start date for fetching issues and PRs, in YYYY-MM-DD format.
# The script will retrieve items updated on or after this date.
SINCE_DATE="[2023-01-01]"

# === SCRIPT BODY ===

# Print the header for the CSV output.
# The columns are: Case ID, Activity, Timestamp, End Timestamp, User/Assignee, Repository, Case Type, Priority
echo "DevelopmentItemId,ActivityName,EventTimestamp,EndTimestamp,Assignee,RepositoryName,DevelopmentItemType,Priority"

# Fetch the list of all issues and pull requests updated since the SINCE_DATE.
# The 'paste' command combines number and URL on the same line for easier processing.
gh issue list -R "$REPO" -S "updated:>$SINCE_DATE" --limit 5000 --json number,url -q '.[] | "\(.number) \(.url)"' | while read -r number url; do

  # Determine if the item is an Issue or a Pull Request based on its URL.
  if [[ $url == *"/pull/"* ]]; then
    ITEM_TYPE="Pull Request"
    # For PRs, fetch a comprehensive set of data including reviews, commits, and timeline events.
    DATA=$(gh pr view "$number" -R "$REPO" --json author,assignees,createdAt,closedAt,mergedAt,labels,number,repository,reviews,timelineItems,commits)
  else
    ITEM_TYPE="Issue"
    # For Issues, fetch the standard data and timeline events.
    DATA=$(gh issue view "$number" -R "$REPO" --json author,assignees,createdAt,closedAt,labels,number,repository,timelineItems)
  fi

  # Extract common attributes to use in each event row.
  ITEM_ID=$(echo "$DATA" | jq -r .number)
  REPO_NAME=$(echo "$DATA" | jq -r .repository.nameWithOwner)
  # Attempt to find a label that indicates priority.
  PRIORITY=$(echo "$DATA" | jq -r '(.labels[]?.name | select(test("(?i)priority|prio|p-"))? // "")')

  # --- Activity Extraction --- #

  # 1. Issue/PR Created
  ACTIVITY_NAME="Issue Created"
  if [[ "$ITEM_TYPE" == "Pull Request" ]]; then ACTIVITY_NAME="Pull Request Opened"; fi
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg activity "$ACTIVITY_NAME" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '[.number, $activity, .createdAt, "", .author.login, $repo, $type, $prio] | @csv'

  # 2. Branch Created (approximated by the first push of the PR's branch)
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '(.timelineItems[]? | select(.__typename == "HeadRefForcePushedEvent") | .createdAt) as $ts | select($ts != null) | first | [$id, "Branch Created", ., "", "", $repo, $type, $prio] | @csv'

  # 4. Code Pushed to PR
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.commits[]? | select(.commit.author.user.login != null) | [$id, "Code Pushed to PR", .commit.committedDate, "", .commit.author.user.login, $repo, $type, $prio] | @csv'

  # 5. Review Requested
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.timelineItems[]? | select(.__typename == "ReviewRequestedEvent") | [$id, "Review Requested", .createdAt, "", .actor.login, $repo, $type, $prio] | @csv'

  # 6. Changes Requested in Review
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.reviews[]? | select(.state == "CHANGES_REQUESTED") | [$id, "Changes Requested in Review", .submittedAt, "", .author.login, $repo, $type, $prio] | @csv'

  # 7. Pull Request Approved
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.reviews[]? | select(.state == "APPROVED") | [$id, "Pull Request Approved", .submittedAt, "", .author.login, $repo, $type, $prio] | @csv'

  # 8 & 9. CI Checks Passed & Failed (one event per check on each commit)
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.commits[]? | .commit.statusCheckRollup.contexts[]? | select(.state != "PENDING") | [$id, (if .state == "SUCCESS" then "CI Checks Passed" else "CI Checks Failed" end), .completedAt, "", .creator.login, $repo, $type, $prio] | @csv'
  
  # 10. Pull Request Merged
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    'select(.mergedAt != null) | [$id, "Pull Request Merged", .mergedAt, "", .author.login, $repo, $type, $prio] | @csv'

  # 11. Deployment Succeeded
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.timelineItems[]? | select(.__typename == "DeployedEvent") | [$id, "Deployment Succeeded", .createdAt, "", .actor.login, $repo, $type, $prio] | @csv'

  # 12. Issue Closed
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    'select(.closedAt != null) | [$id, "Issue Closed", .closedAt, "", .author.login, $repo, $type, $prio] | @csv'

  # 13. Issue Reopened
  echo "$DATA" | jq -r --arg id "$ITEM_ID" --arg repo "$REPO_NAME" --arg type "$ITEM_TYPE" --arg prio "$PRIORITY" \
    '.timelineItems[]? | select(.__typename == "ReopenedEvent") | [$id, "Issue Reopened", .createdAt, "", .actor.login, $repo, $type, $prio] | @csv'

done

import os
import requests
import pandas as pd
import time
import json

# --- Configuration Settings ---
# Replace with your actual GitHub Personal Access Token
GITHUB_TOKEN = "[Your GitHub Personal Access Token]"
# Replace with the repository owner (organization or user)
REPO_OWNER = "[Your Repository Owner]"
# Replace with the repository name
REPO_NAME = "[Your Repository Name]"
# Set the start date for fetching issues (ISO 8601 format)
SINCE_DATE = "2023-01-01T00:00:00Z"
# Number of items to fetch per page (max 100)
PAGE_SIZE = 50

# --- GraphQL Query ---
# This query fetches issues and their associated events, including pull requests and timelines.
GRAPHQL_QUERY = """
query GetRepoData($owner: String!, $name: String!, $since: DateTime, $cursor: String, $pageSize: Int!) {
  repository(owner: $owner, name: $name) {
    name
    issues(first: $pageSize, after: $cursor, filterBy: {since: $since}, orderBy: {field: CREATED_AT, direction: ASC}) {
      pageInfo {
        endCursor
        hasNextPage
      }
      nodes {
        id
        number
        title
        createdAt
        closedAt
        assignees(first: 1) {
          nodes {
            login
          }
        }
        labels(first: 5) {
          nodes {
            name
          }
        }
        timelineItems(first: 100, itemTypes: [CLOSED_EVENT, REOPENED_EVENT]) {
          nodes {
            __typename
            ... on ClosedEvent {
              createdAt
            }
            ... on ReopenedEvent {
              createdAt
            }
          }
        }
        closingPullRequests(first: 10) {
          nodes {
            number
            title
            createdAt
            mergedAt
            mergedBy {
              login
            }
            author {
              login
            }
            headRefName
            timelineItems(first: 100) {
              nodes {
                __typename
                ... on ReviewRequestedEvent {
                  createdAt
                  requestedReviewer {
                    ... on User {
                      login
                    }
                    ... on Team {
                      name
                    }
                  }
                }
                ... on PullRequestReview {
                  author {
                    login
                  }
                  submittedAt
                  state
                }
                ... on MergedEvent {
                  createdAt
                  actor {
                    login
                  }
                }
              }
            }
            commits(first: 100) {
              nodes {
                commit {
                  oid
                  message
                  pushedDate
                  committedDate
                  checkSuites(first: 20) {
                    nodes {
                      conclusion
                      createdAt
                      updatedAt
                      checkRuns(first: 20) {
                        nodes {
                          name
                          conclusion
                          completedAt
                        }
                      }
                    }
                  }
                }
              }
            }
            deployments(first: 20) {
              nodes {
                state
                environment
                createdAt
                latestStatus {
                  state
                  createdAt
                }
              }
            }
          }
        }
      }
    }
  }
  rateLimit {
    limit
    cost
    remaining
    resetAt
  }
}
"""

def run_query(query, variables):
    """Function to execute a GraphQL query."""
    headers = {"Authorization": f"bearer {GITHUB_TOKEN}"}
    request = requests.post('https://api.github.com/graphql', json={'query': query, 'variables': variables}, headers=headers)
    if request.status_code == 200:
        response = request.json()
        if 'errors' in response:
            raise Exception(f"Query failed with errors: {response['errors']}")
        return response
    else:
        raise Exception(f"Query failed to run by returning code of {request.status_code}. {request.text}")

def get_all_issues():
    """Function to paginate through all issues."""
    all_issues = []
    has_next_page = True
    cursor = None
    page_count = 1

    while has_next_page:
        print(f"Fetching page {page_count}...")
        variables = {
            "owner": REPO_OWNER,
            "name": REPO_NAME,
            "since": SINCE_DATE,
            "pageSize": PAGE_SIZE,
            "cursor": cursor
        }
        
        try:
            result = run_query(GRAPHQL_QUERY, variables)
            data = result['data']['repository']['issues']
            all_issues.extend(data['nodes'])
            page_info = data['pageInfo']
            has_next_page = page_info['hasNextPage']
            cursor = page_info['endCursor']
            page_count += 1

            # Handle rate limiting
            rate_limit_info = result['data']['rateLimit']
            if rate_limit_info['remaining'] < 100:
                print(f"Rate limit approaching. Remaining: {rate_limit_info['remaining']}. Pausing...")
                time.sleep(60) 

        except Exception as e:
            print(f"An error occurred: {e}")
            break

    return all_issues

def get_priority_from_labels(labels):
    """Helper to determine priority from labels."""
    for label in labels:
        if 'p0' in label.lower() or 'high' in label.lower():
            return 'High'
        if 'p1' in label.lower() or 'medium' in label.lower():
            return 'Medium'
        if 'p2' in label.lower() or 'low' in label.lower():
            return 'Low'
    return None

def get_type_from_labels(labels):
    """Helper to determine type from labels.""""    for label in labels:
        if 'bug' in label.lower():
            return 'Bug'
        if 'feature' in label.lower() or 'enhancement' in label.lower():
            return 'Feature'
        if 'task' in label.lower() or 'chore' in label.lower():
            return 'Task'
    return 'Issue'

def main():
    """Main function to orchestrate extraction and processing."""
    print("Starting GitHub data extraction...")
    issues_data = get_all_issues()
    print(f"Fetched data for {len(issues_data)} issues.")

    event_log = []

    for issue in issues_data:
        if not issue: continue

        case_id = f"ISSUE-{issue['number']}"
        repo_name = f"{REPO_OWNER}/{REPO_NAME}"
        labels = [l['name'] for l in issue['labels']['nodes']]
        priority = get_priority_from_labels(labels)
        dev_item_type = get_type_from_labels(labels)
        assignee_node = issue['assignees']['nodes']
        assignee = assignee_node[0]['login'] if assignee_node else None

        # 1. Issue Created
        event_log.append({
            'DevelopmentItemId': case_id,
            'ActivityName': 'Issue Created',
            'EventTimestamp': issue['createdAt'],
            'EndTimestamp': None,
            'Assignee': assignee,
            'RepositoryName': repo_name,
            'DevelopmentItemType': dev_item_type,
            'Priority': priority
        })

        # 2. Issue Closed / Reopened Events
        for event in issue.get('timelineItems', {}).get('nodes', []):
            if event['__typename'] == 'ClosedEvent' and event.get('createdAt'):
                event_log.append({
                    'DevelopmentItemId': case_id, 'ActivityName': 'Issue Closed', 'EventTimestamp': event['createdAt'],
                    'EndTimestamp': None, 'Assignee': None, 'RepositoryName': repo_name, 
                    'DevelopmentItemType': dev_item_type, 'Priority': priority
                })
            elif event['__typename'] == 'ReopenedEvent' and event.get('createdAt'):
                event_log.append({
                    'DevelopmentItemId': case_id, 'ActivityName': 'Issue Reopened', 'EventTimestamp': event['createdAt'],
                    'EndTimestamp': None, 'Assignee': None, 'RepositoryName': repo_name, 
                    'DevelopmentItemType': dev_item_type, 'Priority': priority
                })
        
        # Process linked Pull Requests
        for pr in issue.get('closingPullRequests', {}).get('nodes', []):
            pr_author = pr.get('author', {}).get('login') if pr.get('author') else None

            # 3. Pull Request Opened
            event_log.append({
                'DevelopmentItemId': case_id, 'ActivityName': 'Pull Request Opened', 'EventTimestamp': pr['createdAt'],
                'EndTimestamp': None, 'Assignee': pr_author, 'RepositoryName': repo_name,
                'DevelopmentItemType': dev_item_type, 'Priority': priority
            })

            # Process PR Commits for Branch Created, Code Pushed, and CI Checks
            commits = sorted(pr.get('commits', {}).get('nodes', []), key=lambda c: c['commit']['committedDate'])
            if commits:
                # 4. Branch Created (inferred from first commit)
                first_commit_date = commits[0]['commit']['committedDate']
                event_log.append({
                    'DevelopmentItemId': case_id, 'ActivityName': 'Branch Created', 'EventTimestamp': first_commit_date,
                    'EndTimestamp': None, 'Assignee': pr_author, 'RepositoryName': repo_name,
                    'DevelopmentItemType': dev_item_type, 'Priority': priority
                })

            for commit_node in commits:
                commit = commit_node['commit']
                # 5. Code Pushed to PR
                if commit.get('pushedDate'):
                    event_log.append({
                        'DevelopmentItemId': case_id, 'ActivityName': 'Code Pushed to PR', 'EventTimestamp': commit['pushedDate'],
                        'EndTimestamp': None, 'Assignee': pr_author, 'RepositoryName': repo_name,
                        'DevelopmentItemType': dev_item_type, 'Priority': priority
                    })
                
                # 6. CI Checks Passed/Failed
                for check_suite in commit.get('checkSuites', {}).get('nodes', []):
                    for check_run in check_suite.get('checkRuns', {}).get('nodes', []):
                        if check_run.get('completedAt') and check_run.get('conclusion'):
                            status = check_run['conclusion']
                            if status == 'SUCCESS':
                                activity_name = 'CI Checks Passed'
                            elif status in ['FAILURE', 'TIMED_OUT', 'ACTION_REQUIRED']:
                                activity_name = 'CI Checks Failed'
                            else:
                                continue # Skip neutral/in-progress statuses
                            
                            event_log.append({
                                'DevelopmentItemId': case_id, 'ActivityName': activity_name, 'EventTimestamp': check_run['completedAt'],
                                'EndTimestamp': None, 'Assignee': None, 'RepositoryName': repo_name,
                                'DevelopmentItemType': dev_item_type, 'Priority': priority
                            })

            # Process PR Timeline events
            for event in pr.get('timelineItems', {}).get('nodes', []):
                typename = event.get('__typename')
                # 7. Review Requested
                if typename == 'ReviewRequestedEvent':
                    reviewer = event.get('requestedReviewer', {}).get('login') or event.get('requestedReviewer', {}).get('name')
                    event_log.append({
                        'DevelopmentItemId': case_id, 'ActivityName': 'Review Requested', 'EventTimestamp': event['createdAt'],
                        'EndTimestamp': None, 'Assignee': reviewer, 'RepositoryName': repo_name,
                        'DevelopmentItemType': dev_item_type, 'Priority': priority
                    })
                # 8. Changes Requested / Approved
                elif typename == 'PullRequestReview' and event.get('submittedAt'):
                    review_state = event.get('state')
                    activity_name = None
                    if review_state == 'CHANGES_REQUESTED':
                        activity_name = 'Changes Requested in Review'
                    elif review_state == 'APPROVED':
                        activity_name = 'Pull Request Approved'
                    if activity_name:
                        reviewer = event.get('author', {}).get('login')
                        event_log.append({
                            'DevelopmentItemId': case_id, 'ActivityName': activity_name, 'EventTimestamp': event['submittedAt'],
                            'EndTimestamp': None, 'Assignee': reviewer, 'RepositoryName': repo_name,
                            'DevelopmentItemType': dev_item_type, 'Priority': priority
                        })

            # 9. Pull Request Merged
            if pr.get('mergedAt'):
                merger = pr.get('mergedBy', {}).get('login')
                event_log.append({
                    'DevelopmentItemId': case_id, 'ActivityName': 'Pull Request Merged', 'EventTimestamp': pr['mergedAt'],
                    'EndTimestamp': None, 'Assignee': merger, 'RepositoryName': repo_name,
                    'DevelopmentItemType': dev_item_type, 'Priority': priority
                })

            # 10. Deployment Succeeded
            for deployment in pr.get('deployments', {}).get('nodes', []):
                if deployment.get('latestStatus', {}).get('state') == 'SUCCESS':
                    event_log.append({
                        'DevelopmentItemId': case_id, 'ActivityName': 'Deployment Succeeded', 'EventTimestamp': deployment['latestStatus']['createdAt'],
                        'EndTimestamp': None, 'Assignee': None, 'RepositoryName': repo_name,
                        'DevelopmentItemType': dev_item_type, 'Priority': priority
                    })

    if not event_log:
        print("No events were generated. The repository might be empty or no issues were found in the specified date range.")
        return

    # Create DataFrame and save to CSV
    df = pd.DataFrame(event_log)
    df = df.sort_values(by=['DevelopmentItemId', 'EventTimestamp']).reset_index(drop=True)
    df.to_csv('github_event_log.csv', index=False)
    print("Extraction complete. Event log saved to github_event_log.csv")

if __name__ == "__main__":
    main()

import os
import requests
import pandas as pd
from datetime import datetime, timedelta
import time

class=class="hl-string">"hl-comment"># --- Configuration ---
class=class="hl-string">"hl-comment"># It is recommended to set your token as an environment variable for security.
class=class="hl-string">"hl-comment"># The script will use the environment variable class="hl-string">'GITHUB_TOKEN' if it exists.
GITHUB_TOKEN = os.getenv(class="hl-string">"GITHUB_TOKEN", class="hl-string">"[Your GitHub Personal Access Token]")
class=class="hl-string">"hl-comment"># Replace with the repository owner (user or organization)
REPO_OWNER = class="hl-string">"[Your Repository Owner]"
class=class="hl-string">"hl-comment"># Replace with the repository name
REPO_NAME = class="hl-string">"[Your Repository Name]"
class=class="hl-string">"hl-comment"># Set the start date for fetching issues. Format: YYYY-MM-DD
class=class="hl-string">"hl-comment"># The script will fetch all issues updated on or after this date.
SINCE_DATE = (datetime.now() - timedelta(days=180)).strftime(class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
class=class="hl-string">"hl-comment"># --- End Configuration ---

HEADERS = {
    class="hl-string">"Authorization": fclass="hl-string">"token {GITHUB_TOKEN}",
    class="hl-string">"Accept": class="hl-string">"application/vnd.github.v3+json"
}
BASE_URL = fclass="hl-string">"https://api.github.com/repos/{REPO_OWNER}/{REPO_NAME}"

def get_api_data(url, params=None):
    class="hl-string">"""Makes a request to a given URL and handles common errors and rate limiting."""
    try:
        response = requests.get(url, headers=HEADERS, params=params)
        if response.status_code == 429:
            retry_after = int(response.headers.get(class="hl-string">'Retry-After', 60))
            print(fclass="hl-string">"Rate limit hit. Waiting for {retry_after} seconds.")
            time.sleep(retry_after)
            return get_api_data(url, params) class=class="hl-string">"hl-comment"># Retry the request
        response.raise_for_status()
        return response
    except requests.exceptions.HTTPError as e:
        print(fclass="hl-string">"HTTP Error fetching data from {url}: {e}")
    except Exception as e:
        print(fclass="hl-string">"An error occurred: {e}")
    return None

def get_paginated_data(url, params=None):
    class="hl-string">"""Handles pagination for GitHub API requests and returns a list of all items."""
    data = []
    while url:
        response = get_api_data(url, params)
        if not response:
            break
        data.extend(response.json())
        class=class="hl-string">"hl-comment"># Subsequent requests should not include initial params
        if params: 
            params = None 
        if class="hl-string">'next' in response.links:
            url = response.links[class="hl-string">'next'][class="hl-string">'url']
        else:
            url = None
        time.sleep(0.5) class=class="hl-string">"hl-comment"># Be a good API citizen
    return data

def get_priority_from_labels(labels):
    class="hl-string">"""Extracts priority from a list of labels based on keywords."""
    for label in labels:
        name = label.get(class="hl-string">"name", class="hl-string">"").lower()
        if class="hl-string">"priority: high" in name or class="hl-string">"p1" in name or class="hl-string">"high priority" in name:
            return class="hl-string">"High"
        if class="hl-string">"priority: medium" in name or class="hl-string">"p2" in name or class="hl-string">"medium priority" in name:
            return class="hl-string">"Medium"
        if class="hl-string">"priority: low" in name or class="hl-string">"p3" in name or class="hl-string">"low priority" in name:
            return class="hl-string">"Low"
    return None

def main():
    class="hl-string">"""Main function to extract, process, and save the event log."""
    event_log = []
    print(fclass="hl-string">"Fetching issues for {REPO_OWNER}/{REPO_NAME} updated since {SINCE_DATE}...")
    issues_url = fclass="hl-string">"{BASE_URL}/issues"
    params = {class="hl-string">"state": class="hl-string">"all", class="hl-string">"since": SINCE_DATE, class="hl-string">"direction": class="hl-string">"asc", class="hl-string">"per_page": 100}
    issues = get_paginated_data(issues_url, params)
    print(fclass="hl-string">"Found {len(issues)} issues to process.")

    for i, issue in enumerate(issues):
        print(fclass="hl-string">"Processing issue {i+1}/{len(issues)}: class="hl-commentclass="hl-string">">#{issue['number']}")
        item_id = issue[class="hl-string">"number"]
        item_type = class="hl-string">"Pull Request" if class="hl-string">"pull_request" in issue else class="hl-string">"Issue"
        priority = get_priority_from_labels(issue.get(class="hl-string">"labels", []))

        class=class="hl-string">"hl-comment"># Activity: Issue Created
        event_log.append({
            class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Issue Created", class="hl-string">"EventTimestamp": issue[class="hl-string">"created_at"],
            class="hl-string">"EndTimestamp": issue[class="hl-string">"created_at"], class="hl-string">"Assignee": issue.get(class="hl-string">"user", {}).get(class="hl-string">"login"),
            class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority
        })

        class=class="hl-string">"hl-comment"># --- Get Timeline Events (Closed, Reopened, Merged, Review Requested) ---
        timeline_events = get_paginated_data(issue[class="hl-string">"timeline_url"])
        for event in timeline_events:
            event_type = event.get(class="hl-string">"event")
            actor = event.get(class="hl-string">"actor", {}).get(class="hl-string">"login")
            ts = event.get(class="hl-string">"created_at")
            if event_type == class="hl-string">"closed":
                event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Issue Closed", class="hl-string">"EventTimestamp": ts, class="hl-string">"EndTimestamp": ts, class="hl-string">"Assignee": actor, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})
            elif event_type == class="hl-string">"reopened":
                event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Issue Reopened", class="hl-string">"EventTimestamp": ts, class="hl-string">"EndTimestamp": ts, class="hl-string">"Assignee": actor, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})
            elif event_type == class="hl-string">"merged":
                 event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Pull Request Merged", class="hl-string">"EventTimestamp": ts, class="hl-string">"EndTimestamp": ts, class="hl-string">"Assignee": actor, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})
            elif event_type == class="hl-string">"review_requested":
                reviewer = event.get(class="hl-string">"requested_reviewer", {}).get(class="hl-string">"login")
                event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Review Requested", class="hl-string">"EventTimestamp": ts, class="hl-string">"EndTimestamp": ts, class="hl-string">"Assignee": reviewer, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})

        class=class="hl-string">"hl-comment"># --- Process PR-specific data ---
        if class="hl-string">"pull_request" in issue:
            pr_response = get_api_data(issue[class="hl-string">"pull_request"][class="hl-string">"url"])
            if not pr_response: continue
            pr_data = pr_response.json()

            class=class="hl-string">"hl-comment"># Activity: Pull Request Opened
            event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Pull Request Opened", class="hl-string">"EventTimestamp": pr_data[class="hl-string">"created_at"], class="hl-string">"EndTimestamp": pr_data[class="hl-string">"created_at"], class="hl-string">"Assignee": pr_data.get(class="hl-string">"user", {}).get(class="hl-string">"login"), class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})
            
            class=class="hl-string">"hl-comment"># --- Get Commits (Branch Created, Code Pushed, CI Status) ---
            commits = get_paginated_data(pr_data[class="hl-string">"commits_url"])
            for idx, commit in enumerate(commits):
                commit_ts = commit[class="hl-string">"commit"][class="hl-string">"author"][class="hl-string">"date"]
                committer = commit.get(class="hl-string">"author", {}).get(class="hl-string">"login")
                if idx == 0:
                    event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Branch Created", class="hl-string">"EventTimestamp": commit_ts, class="hl-string">"EndTimestamp": commit_ts, class="hl-string">"Assignee": committer, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})
                event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": class="hl-string">"Code Pushed to PR", class="hl-string">"EventTimestamp": commit_ts, class="hl-string">"EndTimestamp": commit_ts, class="hl-string">"Assignee": committer, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})

                class=class="hl-string">"hl-comment"># Get CI Check Statuses
                check_runs_url = fclass="hl-string">"{BASE_URL}/commits/{commit['sha']}/check-runs"
                check_runs_response = get_api_data(check_runs_url)
                if check_runs_response:
                    check_runs_data = check_runs_response.json()
                    for check in check_runs_data.get(class="hl-string">'check_runs', []):
                        if check.get(class="hl-string">'conclusion') == class="hl-string">'success':
                            activity = class="hl-string">'CI Checks Passed'
                        elif check.get(class="hl-string">'conclusion') in [class="hl-string">'failure', class="hl-string">'cancelled', class="hl-string">'timed_out']:
                            activity = class="hl-string">'CI Checks Failed'
                        else:
                            continue
                        event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": activity, class="hl-string">"EventTimestamp": check[class="hl-string">"completed_at"], class="hl-string">"EndTimestamp": check[class="hl-string">"completed_at"], class="hl-string">"Assignee": None, class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})

            class=class="hl-string">"hl-comment"># --- Get Reviews (Approved, Changes Requested) ---
            reviews = get_paginated_data(fclass="hl-string">"{BASE_URL}/pulls/{item_id}/reviews")
            for review in reviews:
                if review[class="hl-string">"state"] == class="hl-string">"APPROVED":
                    activity = class="hl-string">"Pull Request Approved"
                elif review[class="hl-string">"state"] == class="hl-string">"CHANGES_REQUESTED":
                    activity = class="hl-string">"Changes Requested in Review"
                else:
                    continue
                event_log.append({class="hl-string">"DevelopmentItemId": item_id, class="hl-string">"ActivityName": activity, class="hl-string">"EventTimestamp": review[class="hl-string">"submitted_at"], class="hl-string">"EndTimestamp": review[class="hl-string">"submitted_at"], class="hl-string">"Assignee": review.get(class="hl-string">"user", {}).get(class="hl-string">"login"), class="hl-string">"RepositoryName": REPO_NAME, class="hl-string">"DevelopmentItemType": item_type, class="hl-string">"Priority": priority})

    if not event_log:
        print(class="hl-string">"No events were generated. Exiting.")
        return

    class=class="hl-string">"hl-comment"># --- Final Data Processing ---
    df = pd.DataFrame(event_log)
    df.drop_duplicates(inplace=True)
    df[class="hl-string">"EventTimestamp"] = pd.to_datetime(df[class="hl-string">"EventTimestamp"], errors=class="hl-string">'coerce')
    df.dropna(subset=[class="hl-string">"EventTimestamp"], inplace=True)
    df.sort_values(by=[class="hl-string">"DevelopmentItemId", class="hl-string">"EventTimestamp"], inplace=True)

    final_columns = [class="hl-string">"DevelopmentItemId", class="hl-string">"ActivityName", class="hl-string">"EventTimestamp", class="hl-string">"EndTimestamp", class="hl-string">"Assignee", class="hl-string">"RepositoryName", class="hl-string">"DevelopmentItemType", class="hl-string">"Priority"]
    df_final = pd.DataFrame(columns=final_columns)
    for col in final_columns:
        if col in df.columns:
            df_final[col] = df[col]
        else:
            df_final[col] = None

    output_filename = class="hl-string">"github_event_log.csv"
    df_final.to_csv(output_filename, index=False, date_format=class="hl-string">'%Y-%m-%dT%H:%M:%SZ')
    print(fclass="hl-string">"Successfully created event log: {output_filename}")

if __name__ == class="hl-string">"__main__":
    main()

WITH issue_created AS (
    SELECT
        CAST(i.issue_id AS VARCHAR) AS DevelopmentItemId,
        'Issue Created' AS ActivityName,
        CAST(i.created_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(i.assignee_login AS VARCHAR) AS Assignee,
        CAST(i.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your issues source object] i
    WHERE i.created_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND i.created_at < CAST('[End date and time]' AS TIMESTAMP)
),
branch_created AS (
    SELECT
        CAST(b.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Branch Created' AS ActivityName,
        CAST(b.created_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(b.actor_login AS VARCHAR) AS Assignee,
        CAST(b.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your branches or push events source object] b
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(b.linked_issue_id AS VARCHAR)
    WHERE b.event_type = 'branch_created'
      AND b.created_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND b.created_at < CAST('[End date and time]' AS TIMESTAMP)
      AND b.linked_issue_id IS NOT NULL
),
pull_request_opened AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Pull Request Opened' AS ActivityName,
        CAST(p.created_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(p.author_login AS VARCHAR) AS Assignee,
        CAST(p.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull requests source object] p
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE p.created_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND p.created_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
code_pushed_to_pr AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Code Pushed to PR' AS ActivityName,
        CAST(c.pushed_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(c.author_login AS VARCHAR) AS Assignee,
        CAST(c.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull request commits source object] c
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.pull_request_id AS VARCHAR) = CAST(c.pull_request_id AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE c.pushed_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND c.pushed_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
review_requested AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Review Requested' AS ActivityName,
        CAST(r.requested_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(r.requested_reviewer_login AS VARCHAR) AS Assignee,
        CAST(r.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull request review requests source object] r
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.pull_request_id AS VARCHAR) = CAST(r.pull_request_id AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE r.requested_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND r.requested_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
changes_requested AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Changes Requested in Review' AS ActivityName,
        CAST(r.submitted_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(r.reviewer_login AS VARCHAR) AS Assignee,
        CAST(r.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull request reviews source object] r
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.pull_request_id AS VARCHAR) = CAST(r.pull_request_id AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE r.review_state = 'request_changes'
      AND r.submitted_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND r.submitted_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
pull_request_approved AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Pull Request Approved' AS ActivityName,
        CAST(r.submitted_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(r.reviewer_login AS VARCHAR) AS Assignee,
        CAST(r.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull request reviews source object] r
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.pull_request_id AS VARCHAR) = CAST(r.pull_request_id AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE r.review_state = 'approve'
      AND r.submitted_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND r.submitted_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
ci_checks_passed AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'CI Checks Passed' AS ActivityName,
        CAST(c.completed_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(c.actor_login AS VARCHAR) AS Assignee,
        CAST(c.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your check runs source object] c
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.head_commit_sha AS VARCHAR) = CAST(c.commit_sha AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE c.conclusion = 'success'
      AND c.completed_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND c.completed_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
ci_checks_failed AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'CI Checks Failed' AS ActivityName,
        CAST(c.completed_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(c.actor_login AS VARCHAR) AS Assignee,
        CAST(c.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your check runs source object] c
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.head_commit_sha AS VARCHAR) = CAST(c.commit_sha AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE c.conclusion IN ('failure', 'cancelled', 'timed_out', 'action_required', 'startup_failure')
      AND c.completed_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND c.completed_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
pull_request_merged AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Pull Request Merged' AS ActivityName,
        CAST(p.merged_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(p.merged_by_login AS VARCHAR) AS Assignee,
        CAST(p.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your pull requests source object] p
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE p.merged_at IS NOT NULL
      AND p.merged_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND p.merged_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
deployment_succeeded AS (
    SELECT
        CAST(p.linked_issue_id AS VARCHAR) AS DevelopmentItemId,
        'Deployment Succeeded' AS ActivityName,
        CAST(d.completed_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(d.creator_login AS VARCHAR) AS Assignee,
        CAST(d.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your deployment statuses source object] d
    INNER JOIN [Your pull requests source object] p
      ON CAST(p.merge_commit_sha AS VARCHAR) = CAST(d.commit_sha AS VARCHAR)
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(p.linked_issue_id AS VARCHAR)
    WHERE d.state = 'success'
      AND d.completed_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND d.completed_at < CAST('[End date and time]' AS TIMESTAMP)
      AND p.linked_issue_id IS NOT NULL
),
issue_closed AS (
    SELECT
        CAST(e.issue_id AS VARCHAR) AS DevelopmentItemId,
        'Issue Closed' AS ActivityName,
        CAST(e.created_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(e.actor_login AS VARCHAR) AS Assignee,
        CAST(e.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your issue events source object] e
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(e.issue_id AS VARCHAR)
    WHERE e.event_type = 'closed'
      AND e.created_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND e.created_at < CAST('[End date and time]' AS TIMESTAMP)
),
issue_reopened AS (
    SELECT
        CAST(e.issue_id AS VARCHAR) AS DevelopmentItemId,
        'Issue Reopened' AS ActivityName,
        CAST(e.created_at AS TIMESTAMP) AS EventTimestamp,
        CAST(NULL AS TIMESTAMP) AS EndTimestamp,
        CAST(e.actor_login AS VARCHAR) AS Assignee,
        CAST(e.repository_name AS VARCHAR) AS RepositoryName,
        CAST(i.development_item_type AS VARCHAR) AS DevelopmentItemType,
        CAST(i.priority AS VARCHAR) AS Priority
    FROM [Your issue events source object] e
    LEFT JOIN [Your issues source object] i
      ON CAST(i.issue_id AS VARCHAR) = CAST(e.issue_id AS VARCHAR)
    WHERE e.event_type = 'reopened'
      AND e.created_at >= CAST('[Start date and time]' AS TIMESTAMP)
      AND e.created_at < CAST('[End date and time]' AS TIMESTAMP)
)
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM issue_created
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM branch_created
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM pull_request_opened
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM code_pushed_to_pr
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM review_requested
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM changes_requested
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM pull_request_approved
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM ci_checks_passed
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM ci_checks_failed
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM pull_request_merged
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM deployment_succeeded
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM issue_closed
UNION ALL
SELECT DevelopmentItemId, ActivityName, EventTimestamp, EndTimestamp, Assignee, RepositoryName, DevelopmentItemType, Priority FROM issue_reopened
ORDER BY DevelopmentItemId, EventTimestamp, ActivityName;