import dai
import pandas as pd
from datetime import datetime

sd = '2022-07-01'
ed = datetime.now().date().strftime("%Y-%m-%d")

def get_factor_data(alpha_sql):

    sql = f"""

    WITH 
    data_base AS (
        SELECT
            date,
            instrument,
            open,
            close,
            high,
            low,
            amount,
            volume,
            close / m_lag(close, 1) - 1 AS returns,
            amount / volume AS vwap,
            m_nanavg(volume,   5) AS adv5,
            m_nanavg(volume,  10) AS adv10,
            m_nanavg(volume,  15) AS adv15,
            m_nanavg(volume,  20) AS adv20,
            m_nanavg(volume,  30) AS adv30,
            m_nanavg(volume,  40) AS adv40,
            m_nanavg(volume,  50) AS adv50,
            m_nanavg(volume,  60) AS adv60,
            m_nanavg(volume,  81) AS adv81,
            m_nanavg(volume, 120) AS adv120,
            m_nanavg(volume, 150) AS adv150,
            m_nanavg(volume, 180) AS adv180,
            float_market_cap,
            industry_level1_code,
            industry_level2_code,
            industry_level3_code,
        FROM cn_stock_bar1d JOIN cn_stock_industry_component USING (date, instrument) JOIN cn_stock_valuation USING (date, instrument)
        WHERE industry = 'sw2021'
        AND instrument NOT LIKE '%BJ%'

        QUALIFY COLUMNS(*) IS NOT NULL
    )
    ,
    data_factor AS (
        {alpha_sql}
    )

    SELECT 
        *
    FROM data_factor
    QUALIFY COLUMNS(*) IS NOT NULL
    ORDER BY date, instrument
    
    """

    return dai.query(sql, filters={'date':[sd, ed]}).df()

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    IF(((c_pct_rank(log(m_product(c_pct_rank((c_pct_rank(m_corr(vwap, m_sum(adv10, 49.6054), 8.47743))^4)), 14.9655))) < c_pct_rank(m_corr(c_pct_rank(vwap), c_pct_rank(volume), 5.07914)))),1,0) * -1  AS alpha_a101_f0081
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (least(c_pct_rank(m_decay_linear(m_delta(open, 1.46063), 14.8717)), m_rank(m_decay_linear(m_corr(c_indneutralize(volume, industry_level1_code), ((open * 0.634196) + (open * (1 - 0.634196))), 17.4842), 6.92131), 13.4283)) * -1) AS alpha_a101_f0082
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((c_pct_rank(m_lag(((high - low) / (m_sum(close, 5) / 5)), 2)) * c_pct_rank(c_pct_rank(volume))) / (((high - low) / (m_sum(close, 5) / 5)) / (vwap - close))) AS alpha_a101_f0083
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    power(m_rank((vwap - m_max(vwap, 15.3217)), 20.7127), c_pct_rank(m_delta(close, 4.96796)))  AS alpha_a101_f0084
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (c_pct_rank(m_corr(((high * 0.876703) + (close * (1 - 0.876703))), adv30, 9.61331))^c_pct_rank(m_corr(m_rank(((high + low) / 2), 3.70596), m_rank(volume, 10.1595), 7.11408))) AS alpha_a101_f0085
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    IF((m_pct_rank(m_corr(close, m_sum(adv20, 14.7444), 6.00049), 20.4195) < c_pct_rank(((open + close) - (vwap + open)))), 1, 0) * -1 AS alpha_a101_f0086
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    greatest(c_pct_rank(m_decay_linear(m_delta(((close * 0.369701) + (vwap * (1 - 0.369701))), 1.91233), 2.65461)),m_pct_rank(m_decay_linear(abs(m_corr(c_indneutralize(adv81, industry_level2_code), close, 13.4132)), 4.89768), 14.4535)) * -1 AS alpha_a101_f0087
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    least(c_pct_rank(m_decay_linear(((c_pct_rank(open) + c_pct_rank(low)) - (c_pct_rank(high) + c_pct_rank(close))), 8.06882)), m_rank(m_decay_linear(m_corr(m_rank(close, 8.44728), m_rank(adv60, 20.6966), 8.01266), 6.65053), 2.61957)) AS alpha_a101_f0088
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (m_rank(m_decay_linear(m_corr(((low * 0.967285) + (low * (1 - 0.967285))), adv10, 6.94279), 5.51607), 3.79744) - m_rank(m_decay_linear(m_delta(c_indneutralize(vwap, industry_level2_code), 3.48158), 10.1466), 15.3012)) AS alpha_a101_f0089
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((c_pct_rank((close - m_max(close, 4.66719)))^m_rank(m_corr(c_indneutralize(adv40, industry_level3_code), low, 5.38375), 3.21856)) * -1) AS alpha_a101_f0090
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((m_rank(m_decay_linear(m_decay_linear(m_corr(c_indneutralize(close, industry_level2_code), volume, 9.74928), 16.398), 3.83219), 4.8667) - c_pct_rank(m_decay_linear(m_corr(vwap, adv30, 4.01303), 2.6809))) * -1) AS alpha_a101_f0091
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    least(m_rank(m_decay_linear(((((high + low) / 2) + close) < (low + open)), 14.7221), 18.8683), m_rank(m_decay_linear(m_corr(c_pct_rank(low), c_pct_rank(adv30), 7.58555), 6.94024), 6.80584)) AS alpha_a101_f0092
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (m_rank(m_decay_linear(m_corr(c_indneutralize(vwap, industry_level2_code), adv81, 17.4193), 19.848), 7.54455) / c_pct_rank(m_decay_linear(m_delta(((close * 0.524434) + (vwap * (1 - 0.524434))), 2.77377), 16.2664))) AS alpha_a101_f0093
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((c_pct_rank((vwap - m_min(vwap, 11.5783)))^m_rank(m_corr(m_rank(vwap, 19.6462), m_rank(adv60, 4.02992), 18.0926), 2.70756)) * -1) AS alpha_a101_f0094
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    IF((c_pct_rank((open - m_min(open, 12.4105))) < m_rank((c_pct_rank(m_corr(m_sum(((high + low) / 2), 19.1351), m_sum(adv40, 19.1351), 12.8742))^5), 11.7584)), 1, 0)  AS alpha_a101_f0095
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    greatest(m_rank(m_decay_linear(m_corr(c_pct_rank(vwap), c_pct_rank(volume), 3.83878), 4.16783), 8.38151), m_rank(m_decay_linear(m_imax(m_corr(m_rank(close, 7.45404), m_rank(adv60, 4.13242), 3.65459), 12.6556), 14.0365), 13.4143)) * -1 AS alpha_a101_f0096
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((c_pct_rank(m_decay_linear(m_delta(c_indneutralize(((low * 0.721001) + (vwap * (1 - 0.721001))), industry_level2_code), 3.3705), 20.4523)) - m_rank(m_decay_linear(m_rank(m_corr(m_rank(low, 7.87871), m_rank(adv60, 17.255), 4.97547), 18.5925), 15.7152), 6.71659)) * -1)  AS alpha_a101_f0097
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (c_pct_rank(m_decay_linear(m_corr(vwap, m_sum(adv5, 26.4719), 4.58418), 7.18088)) - c_pct_rank(m_decay_linear(m_rank(m_imin(m_corr(c_pct_rank(open), c_pct_rank(adv15), 20.8187), 8.62571), 6.95668), 8.07206))) AS alpha_a101_f0098
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    IF(((c_pct_rank(m_corr(m_sum(((high + low) / 2), 19.8975), m_sum(adv60, 19.8975), 8.8136)) < c_pct_rank(m_corr(low, volume, 6.28259)))), 1, 0) * -1  AS alpha_a101_f0099
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    (0 - (1 * (((1.5 * c_scale(c_indneutralize(c_indneutralize(c_pct_rank((((close - low) - (high - close)) / (high - low)) * volume) * 100, industry_level3_code),industry_level3_code), 1)) - c_scale(c_indneutralize((m_corr(close, c_pct_rank(adv20), 5) - c_pct_rank(m_imin(close, 30))), industry_level3_code),1)) * (volume / adv20)))) AS alpha_a101_f0100
FROM data_base
"""

get_factor_data(alpha_sql)

alpha_sql = f"""
SELECT
    date, 
    instrument, 
    ((close - open) / ((high - low) + 0.001))  AS alpha_a101_f0101
FROM data_base
"""

get_factor_data(alpha_sql)