# [Step 1] Set up the environment
import lseg.data as ld
from lseg.data.content import search

import datetime
import pandas as pd
ld.open_session()

# [Step 2] Using search function to retrieve the data
df = ld.discovery.search(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    top = 10000,
    filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))",
    select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn,RCSRiskOrganisationCountry",
)
display(df)

# [Step 2] Using search function to retrieve the data
df = ld.discovery.search(
    view = ld.discovery.Views.GOV_CORP_INSTRUMENTS,
    top = 10000,
    filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))",
    select = "RIC,RCSTRBC2012Leaf,IssueDate,EOMAmountOutstanding,PricingRedemDate,IssuerLegalName,PricingClosingYield, CurrentYield, FaceIssuedTotal,EOMPriceChange,RCSBondGradeLeaf,EOMPriceReturn",
)
display(df)

response = search.Definition(
    view = search.Views.GOV_CORP_INSTRUMENTS,
    filter = "((DbType eq 'GOVT' or DbType eq 'CORP' or DbType eq 'AGNC' or DbType eq 'OMUN' or DbType eq 'OTHR') and IsActive eq true and ((RCSRiskOrganisationCountry xeq 'G:53' or RCSRiskOrganisationCountry xeq 'G:3H' or RCSRiskOrganisationCountry xeq 'G:7U' or RCSRiskOrganisationCountry xeq 'G:3I') and RCSCurrency in ('C:6') and RCSCountryGenealogy ne 'M:DH\G:K\G:S\G:6\G:53'))",
    top = 0,
    navigators = "RCSRiskOrganisationCountry"
).get_data()
response.data.raw["Navigators"]["RCSRiskOrganisationCountry"]["Buckets"]

df = pd.concat([df1, df2], ignore_index=True)
df

# [Step 3] Visualize the Kungfu bonds
# 3.1 ) TRBC Pie
import plotly.express as px
rt = df.groupby("RCSTRBC2012Leaf",as_index=False).agg('count')
rt = rt.sort_values('RIC', ascending = False).head(10)
fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='TRBC Pie',color_discrete_sequence=px.colors.sequential.RdBu)
fig.show()

# 3.2 ) Bond Grade Pie 
df['RCSBondGradeLeaf'].fillna("No Grade", inplace=True)
grad = df.groupby("RCSBondGradeLeaf",as_index=False).agg('count')
fig = px.pie(grad, values='RIC', names='RCSBondGradeLeaf', title='Bond Grade Pie',color_discrete_sequence=px.colors.sequential.RdBu)
fig.show()

# 3.3 ) Issued amount last 12 months 
from datetime import date
from dateutil.relativedelta import relativedelta
decb = date.today() + relativedelta(months=-12)
start = datetime.datetime(decb.year, decb.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:10]
end = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:10]

cols = ['RIC','IssueDate','FaceIssuedTotal']
c = df[cols].copy()
c.loc[:,"IssueDate"] = pd.to_datetime(c["IssueDate"])
c = c.set_index("IssueDate").sort_index(ascending=False)
c_month = c.resample('BM').sum()
c_monthb = c_month[start:end]
c_monthb.index.name = None

import plotly.express as px
fig = px.bar(c_monthb, y='FaceIssuedTotal', title = 'Issued amount last 12 months', color_discrete_sequence=px.colors.sequential.RdBu)
fig.show()

# 3.4 ) Redem amount in 10 years
yearsf = date.today() + relativedelta(years=+10)
end = datetime.datetime(yearsf.year, yearsf.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:4]
start = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:4]
cols = ['RIC','PricingRedemDate','FaceIssuedTotal']
d = df[cols].copy()
d.loc[:,"PricingRedemDate"] = pd.to_datetime(d["PricingRedemDate"], errors = 'coerce')
d = d.set_index("PricingRedemDate").sort_index(ascending=True)
d_month = d.resample('BY').sum()
d_monthf = d_month[start:end]
d_monthf.index.name = None

import plotly.express as px
fig = px.bar(d_monthf, y='FaceIssuedTotal',title = 'Redem amount in 10 years', color_discrete_sequence=px.colors.sequential.RdBu)
fig.show()

# 3.5 ) Newly issued bonds last 3 months
monb3 = date.today() + relativedelta(months=-3)
start = datetime.datetime(monb3.year, monb3.month, 1).strftime("%Y-%m-%d %H:%M:%S")[0:10]
end = date.today().strftime("%Y-%m-%d %H:%M:%S")[0:10]
print(start, end)
cols = ['RIC','IssueDate','FaceIssuedTotal','RCSTRBC2012Leaf']
f = df[cols].copy()
f.loc[:,"IssueDate"] = pd.to_datetime(f["IssueDate"])
f = f.set_index("IssueDate").sort_index(ascending=True)
f_3m = f[start:end]
rt = f_3m.groupby("RCSTRBC2012Leaf",as_index=False).agg('count')
rt = rt.sort_values('RIC', ascending = False).head(10)
fig = px.pie(rt, values='RIC', names='RCSTRBC2012Leaf', title='newly issued bonds last 3 months',color_discrete_sequence=px.colors.sequential.RdBu)
fig.show()

# 3.6 ) Price top 10
cols = ['RIC','IssuerLegalName','EOMPriceReturn','EOMPriceChange']
price = df[cols].copy()

price_top10 = price.sort_values('EOMPriceReturn', ascending = False).head(10)
price_top10

# 3.7.1 ) Yield top 10
yield_top10 = y.sort_values('CurrentYield', ascending = False).head(10)
yield_top10

# 3.7.2 ) Yield last 10
yield_last10 = y.sort_values('CurrentYield', ascending = True).head(10)
yield_last10