# Import required packages
import psycopg2
import pandas as pd 
import sqlalchemy as salc
import json
import os
import seaborn as sns
from matplotlib import pyplot as plt
from datetime import datetime, timezone
sns.set(rc={'figure.figsize':(15,5)})

with open("config_temp.json") as config_file:
    config = json.load(config_file)

database_connection_string = 'postgresql+psycopg2://{}:{}@{}:{}/{}'.format(config['user'], config['password'], config['host'], config['port'], config['database'])

dbschema='augur_data'
engine = salc.create_engine(
    database_connection_string,
    connect_args={'options': '-csearch_path={}'.format(dbschema)})

repolist = pd.DataFrame()
repo_query = salc.sql.text(f"""
             SET SCHEMA 'augur_data';
             SELECT a.rg_name,
                a.repo_group_id,
                b.repo_name,
                b.repo_id,
                b.forked_from,
                b.repo_archived,
                b.repo_git
            FROM
                repo_groups a,
                repo b
            WHERE
                a.repo_group_id = b.repo_group_id
            ORDER BY
                rg_name,
                repo_name;
    """)
repolist = pd.read_sql(repo_query, con=engine)
display(repolist)
repolist.dtypes

# Make Operate First repo lists to filter for following sql queries
repo_id_name = repolist[repolist['rg_name']=="operate-first"][['repo_id', 'repo_git']]
repo_id_list = repo_id_name['repo_id'].tolist()
print("Number of repositories in Operate First Organization:", len(repo_id_list))

repolist_columns = ['repo_id', 'repo_name']
repolist = repolist[repolist_columns]
repolist.head()

# Identify the list of internal contributors
internal_contributors = ['Aakanksha Duggal','Alice Saparov','Anand Sanmukhani','Bill Burns','Christoph Görn','Francesco Murdaca','Fridolín Pokorný','Gagan Kumar','Harshad Reddy Nalla','Heidi Picher Dempsey','Hema Veeradhi',
 'Humair','Isabel Zimmerman','Karanraj Chauhan','Karsten Wade','Lars Kellogg-Stedman','LeihaoChen','Marcel Hild','Margaret Haley','Michael Clifford','Michal Drla','Oindrilla Chatterjee','Operate First','Shrey','Surbhi Kanthed','Surya Prakash Pathak','Thoth Bot','Tom Coufal']

contributors = pd.DataFrame()

repo_query = salc.sql.text(f"""
             SET SCHEMA 'augur_data';
             SELECT * FROM contributors
    """)

contributors = pd.read_sql(repo_query, con=engine)

# Retain only relevant columns of interest
contributors_columns = ['cntrb_id', 'cntrb_full_name', 'gh_user_id']
contributors = contributors[contributors_columns]
# Add another column to identify whether a user is an internal or external contributor
contributors['cntrb_internal'] = contributors['cntrb_full_name'].apply(lambda x: x in internal_contributors)
#contributors = contributors.dropna()
contributors

contributors['cntrb_internal'].value_counts()

cntrb_groups = contributors['gh_user_id'].groupby(contributors.cntrb_internal).agg('count')

labels = ["External","Internal"]
cntrb_groups.plot.pie(figsize=(15,8),autopct='%1.1f%%',startangle=90,pctdistance=0.9,labels=labels)
plt.title("Internal vs External Contributors")
plt.tight_layout()
plt.show()

START_DATE = '2020-01-01'
END_DATE = '2021-12-31'

issues = pd.DataFrame()

repo_query = salc.sql.text(f"""
             SET SCHEMA 'augur_data';
             SELECT * FROM issues
             WHERE repo_id IN {tuple(repo_id_list)}
    """)

issues = pd.read_sql(repo_query, con=engine)
issues = issues[(issues['created_at'] >= START_DATE) & (issues['created_at']<=END_DATE)]

# Retain only relvant columns of interest
issues_columns = ['issue_id', 'repo_id', 'gh_user_id', 'created_at', 'closed_at']
issues = issues[issues_columns]

# Join repository details
issues_repo = issues.set_index('repo_id').join(repolist.set_index('repo_id')).reset_index()

# Join contributor details
issues_repo_cont = issues_repo.set_index('gh_user_id').join(contributors.set_index('gh_user_id')).reset_index()

issues_repo_cont = issues_repo_cont[issues_repo_cont['repo_name'] != "alerts"]

issues_repo_cont

issues_repo_cont[['repo_name', 'created_at', 'issue_id']].groupby(['repo_name', 'created_at']).count().plot()
plt.xticks(rotation=90)
plt.xlabel("Repository,Day")
plt.ylabel("# Issues")
plt.title("# Issues Created Over Time per Repository")
plt.show()

issues_repo_time = issues_repo_cont['repo_name'].groupby(issues_repo_cont.created_at.dt.to_period("M")).agg('count')

ax = issues_repo_time.plot()
ax.set_xlim(pd.Timestamp('2020-08-01'), pd.Timestamp('2022-01-31'))
plt.title("Number of Issues Created Over Time")
plt.xlabel("Month")
plt.ylabel("Count")
plt.show()

issues_repo_gb = issues_repo_cont.groupby('repo_name')
issues_repo = pd.DataFrame()
issues_repo['internal'] = issues_repo_gb.apply(lambda x: x['cntrb_internal'].sum())
issues_repo['external'] = issues_repo_gb.apply(lambda x: len(x['cntrb_internal']) - x['cntrb_internal'].sum())

issues_repo.sort_values(by='internal', ascending=False).plot(kind='bar', stacked='True')
plt.title("Number of Issues Created (Grouped by Repository)")
plt.xlabel("Repository")
plt.ylabel("Count")
plt.show()

issues_repo_gb.gh_user_id.agg('count').plot.pie(figsize=(35,20),autopct='%1.1f%%')
plt.title("Issues Created Across Repositories")
plt.show()

issue_cntrb = issues_repo_cont['issue_id'].groupby(issues_repo_cont.cntrb_internal).agg('count')

labels=["External","Internal"]
issue_cntrb.plot.pie(figsize=(15,8),autopct='%1.1f%%',startangle=90,pctdistance=0.9,labels=labels)
plt.title("Issues Opened by External vs Internal Contributors")
plt.tight_layout()
plt.show()

pr = pd.DataFrame()

repo_query = salc.sql.text(f"""
             SET SCHEMA 'augur_data';
             SELECT * FROM pull_requests
             WHERE repo_id IN {tuple(repo_id_list)}
    """)

pr = pd.read_sql(repo_query, con=engine)
pr = pr[(pr['pr_created_at'] >= START_DATE) & (pr['pr_created_at'] <= END_DATE)]

pr_meta = pd.DataFrame()

repo_query = salc.sql.text(f"""
             SET SCHEMA 'augur_data';
             SELECT * FROM pull_request_meta
    """)

pr_meta = pd.read_sql(repo_query, con=engine)

# Retain relevant columns
pr_columns = ['pull_request_id', 'pr_created_at', 'pr_closed_at', 'pr_merged_at', 'repo_id']
pr = pr[pr_columns]

pr_meta_columns = ['pull_request_id', 'cntrb_id']
pr_meta = pr_meta[pr_meta_columns]

# Join repository details
pr_repo = pr.set_index('repo_id').join(repolist.set_index('repo_id')).reset_index()

# Join contributor id
pr_repo_cid = pr_repo.set_index('pull_request_id').join(pr_meta.set_index('pull_request_id')).reset_index()

# Join contributor details
pr_repo_cid_cont = pr_repo_cid.set_index('cntrb_id').join(contributors.set_index('cntrb_id')).reset_index()

pr_repo_cid_cont

pr_repo_cid_cont[['repo_name', 'pr_created_at', 'pull_request_id']].groupby(['repo_name', 'pr_created_at']).count().plot()
plt.xticks(rotation=90)
plt.xlabel("Repository,Day")
plt.ylabel("# PRs")
plt.title("# PRs Created Over Time per Repository")
plt.show()

prs_repo_time = pr_repo_cid_cont['repo_name'].groupby(pr_repo_cid_cont.pr_created_at.dt.to_period("M")).agg('count')

ax = prs_repo_time.plot()
ax.set_xlim(pd.Timestamp('2020-08-01'), pd.Timestamp('2022-01-31'))
plt.title("Number of PRs Created Over Time")
plt.xlabel("Month")
plt.ylabel("Count")
plt.show()

pr_repo_cid_cont

prs_repo_gb = pr_repo_cid_cont.groupby('repo_name')
prs_repo = pd.DataFrame()
prs_repo['internal'] = prs_repo_gb.apply(lambda x: x['cntrb_internal'].sum())
prs_repo['external'] = prs_repo_gb.apply(lambda x: len(x['cntrb_internal']) - x['cntrb_internal'].sum())

prs_repo.sort_values(by='internal', ascending=False).plot(kind='bar', stacked='True')
plt.title("Number of PRs Created (Grouped by Repository)")
plt.xlabel("Repository")
plt.ylabel("Count")
plt.show()

prs_repo_gb.pull_request_id.agg('count').plot.pie(figsize=(35,20),autopct='%1.1f%%',startangle=90,pctdistance=0.9)
plt.title("PRs Opened across Repositories")
plt.tight_layout()
plt.show()

pr_cntrb = pr_repo_cid_cont['pull_request_id'].groupby(pr_repo_cid_cont.cntrb_internal).agg('count')

labels = ["External","Internal"]
pr_cntrb.plot.pie(figsize=(15,8),autopct='%1.1f%%',startangle=90,pctdistance=0.9,labels=labels)
plt.title("PRs Opened by External vs Internal Contributors")
plt.tight_layout()
plt.show()

irc = issues_repo_cont[['issue_id', 'cntrb_internal']].rename(columns={'issue_id':'Contribution'})
prc = pr_repo_cid_cont[['pull_request_id', 'cntrb_internal']].rename(columns={'pull_request_id':'Contribution'})
iprc = pd.concat([irc, prc])
iprc_groups = iprc.groupby('cntrb_internal').agg('count')['Contribution']

labels = ["External","Internal"]
iprc_groups.plot.pie(figsize=(15,8),autopct='%1.1f%%',startangle=90,pctdistance=0.9,labels=labels)
plt.title("Internal vs External Contributions (PRs + issues)")
plt.tight_layout()
plt.show()