"""SQL queries and params for getting account revenues."""
SQL_GET_VENDOR_REVENUE = """
SELECT fs.accountingperiodid, SUM(fs.fx_net_receipt) AS revenue,
COUNT(fs.fx_net_receipt) AS num_transactions
FROM fact_sales AS fs
WHERE fs.labelid = {account_id}
AND fs.accountingperiodid IN {periods}
GROUP BY fs.accountingperiodid
ORDER BY fs.accountingperiodid
"""

SQL_GET_GROSS_VENDOR_REVENUE = """
SELECT fs.accountingperiodid, SUM(fs.fx_net_receipt) AS revenue,
COUNT(fs.fx_net_receipt) AS num_transactions,
SUM(fs.fx_adjusted_gross) as gross_revenue
FROM fact_sales AS fs
WHERE fs.labelid = {account_id}
AND fs.accountingperiodid IN {periods}
GROUP BY fs.accountingperiodid
ORDER BY fs.accountingperiodid
"""

SQL_GET_SUBACCOUNT_REVENUE = """
SELECT fs.accountingperiodid,
SUM(fs.fx_net_receipt) * ds.commissionoverride as revenue,
COUNT(fs.fx_net_receipt) AS num_transactions
FROM fact_sales AS fs
LEFT JOIN dim_subaccount AS ds ON ds.subaccountid = fs.subaccountid
WHERE fs.subaccountid = {account_id}
AND fs.accountingperiodid IN {periods}
GROUP BY fs.accountingperiodid, ds.commissionoverride
ORDER BY fs.accountingperiodid
"""

SQL_GET_SUBACCOUNT_NET_GROSS_REVENUE = """
SELECT
    fs.accountingperiodid,
CASE
    WHEN ds.subaccount_split_type = 'Net' THEN SUM(fs.fx_net_receipt)
    ELSE SUM(fs.fx_gross)
END * ds.commissionoverride as revenue,
CASE
    WHEN ds.subaccount_split_type = 'Net' THEN COUNT(fs.fx_net_receipt)
    ELSE COUNT(fs.fx_gross)
END as num_transactions
FROM fact_sales AS fs
LEFT JOIN dim_subaccount AS ds ON ds.subaccountid = fs.subaccountid
WHERE fs.subaccountid = {account_id}
AND fs.accountingperiodid in {periods}
GROUP BY fs.accountingperiodid, ds.commissionoverride, ds.subaccount_split_type
ORDER BY fs.accountingperiodid;
"""

SNOWFLAKE_REVENUE_PARAMS = {
    'account_id': ':account_id',
    'periods': '(:periods)'
}

SQL_GET_AVERAGE_MONTHLY_NET_REVENUE = """
SELECT IFF(COUNT(*) < 3, 0, AVG(net_revenue_in_period))
    AS average_monthly_net_revenue
FROM (
  SELECT
      fs.{account_type}id,
      CAST(dp.year AS INTEGER) AS year,
      CAST(dp.month AS INTEGER) AS month,
      SUM(fs.actual_net) AS net_revenue_in_period
  FROM facts.prod.fact_sales fs
  JOIN facts.prod.dim_period dp ON dp.periodid = fs.accountingperiodid
  WHERE
      fs.{account_type}id = :account_id
  GROUP BY
      fs.{account_type}id,
      year,
      month
  ORDER BY
      year DESC,
      month DESC
  LIMIT 12
)
"""

SQL_GET_BUDGET_CAPS_BASED_ON_REVENUE = """
SELECT v.vendor_id,
    iff (vrf_enabled.vendor_id IS NOT NULL, 0,
        iff (vrf_w.vendor_id IS NULL, 2500,
            iff (month_rev.month_count < 3
                OR month_rev.month_count IS NULL, 500,
                 month_rev.avg_revenue))) AS budget,
    iff (vrf_enabled.vendor_id IS NOT NULL, 'black_list',
        iff (vrf_w.vendor_id IS NULL, 'white_list',
            iff (month_rev.month_count < 3
                OR month_rev.month_count IS NULL, 'new',
                 'general'))) AS budget_type
    FROM ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS.vendor v
    LEFT OUTER JOIN
    ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS. \
      vendor_restricted_features vrf_enabled
        ON vrf_enabled.vendor_id = v.vendor_id and vrf_enabled.feature_id = 21
    LEFT OUTER JOIN
    ORCHARD_APP_REPORTING_V2.ART_RELATIONS_PROD_ART_RELATIONS. \
      vendor_restricted_features vrf_w
        ON vrf_w.vendor_id = v.vendor_id and vrf_w.feature_id = 22
    LEFT OUTER JOIN
        (
           SELECT labelid,
             SUM(net_revenue)/count(periodid) AS avg_revenue,
             COUNT(periodid) AS month_count
           FROM
           (
             SELECT
                fs.labelid,
                SUM(fs.net_receipt) AS net_revenue,
                dp.periodid
            FROM facts.prod.fact_sales fs
            JOIN facts.prod.dim_period dp ON dp.periodid=fs.accountingperiodid
            WHERE dp.periodid > (
            SELECT MAX(accountingperiodid) FROM facts.prod.fact_sales) - 12
            GROUP BY fs.labelid, dp.periodid
           ) AS monthly_revenues
            group by monthly_revenues.labelid
         ) AS month_rev ON month_rev.labelid = v.vendor_id
        where vrf_enabled.vendor_id IS NULL and v.status like 'signed%'
"""

SQL_GET_FIRST_SALE_ACCOUNTING_PERIOD = """
SELECT min(fs.accountingperiodid) AS first_sale_accounting_period_id
    FROM fact_sales AS fs
    WHERE fs.{account_type}id = :account_id
"""