#!/usr/bin/env python3
"""Aggregate DevOps tenant region values without exposing tenant-level data."""

from __future__ import annotations

import json
import subprocess
import sys
from pathlib import Path


VENDORED_PYMYSQL = (
    Path(__file__).resolve().parents[1]
    / "aws-lambda-apac-copy"
    / "emea-source"
    / "unpacked"
    / "EMEA-Tenants-Scoring-Task"
)
sys.path.insert(0, str(VENDORED_PYMYSQL))
import pymysql  # noqa: E402


PROFILE = "552185537300_DBaaS-DevUser-Role"
REGION = "us-west-2"
SOURCE_FUNCTION = "EMEA-Tenants-Monitor-Fanout-Dispatcher-Task"


def lambda_environment() -> dict[str, str]:
    completed = subprocess.run(
        [
            "aws",
            "lambda",
            "get-function-configuration",
            "--profile",
            PROFILE,
            "--region",
            REGION,
            "--function-name",
            SOURCE_FUNCTION,
            "--query",
            "Environment.Variables",
            "--output",
            "json",
        ],
        check=True,
        capture_output=True,
        text=True,
    )
    return json.loads(completed.stdout)


def connect(environment: dict[str, str]):
    return pymysql.connect(
        host=environment["DEVOPS_DB_HOST"],
        port=int(environment.get("DEVOPS_DB_PORT", "3306")),
        user=environment["DEVOPS_DB_USER"],
        password=environment["DEVOPS_DB_PASSWORD"],
        database=environment["DEVOPS_DB_NAME"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
        connect_timeout=10,
        read_timeout=60,
        write_timeout=60,
    )


QUERIES = {
    "specified_large_tenants": """
        SELECT CAST(id AS CHAR) AS tenant_id,
               country,
               country_region,
               sales_force_region,
               status,
               kind,
               plan
        FROM tenants
        WHERE id IN (
          1372813089209300444,
          1372813089209260919,
          1372813089209245725,
          1372813089209302377,
          1372813089209301246
        )
        ORDER BY id
    """,
    "sales_force_region": """
        SELECT COALESCE(NULLIF(TRIM(sales_force_region), ''), '<empty>') AS value,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE LOWER(kind) = 'external'
        GROUP BY value
        ORDER BY tenant_count DESC, value
    """,
    "country_region": """
        SELECT COALESCE(NULLIF(TRIM(country_region), ''), '<empty>') AS value,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE LOWER(kind) = 'external'
        GROUP BY value
        ORDER BY tenant_count DESC, value
    """,
    "region_cross_tab": """
        SELECT COALESCE(NULLIF(TRIM(sales_force_region), ''), '<empty>') AS sales_force_region,
               COALESCE(NULLIF(TRIM(country_region), ''), '<empty>') AS country_region,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE LOWER(kind) = 'external'
        GROUP BY sales_force_region, country_region
        ORDER BY sales_force_region, tenant_count DESC, country_region
    """,
    "apac_countries": """
        SELECT COALESCE(NULLIF(TRIM(country), ''), '<empty>') AS country,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE LOWER(kind) = 'external' AND sales_force_region = 'APAC'
        GROUP BY country
        ORDER BY tenant_count DESC, country
    """,
    "japan_countries": """
        SELECT COALESCE(NULLIF(TRIM(country), ''), '<empty>') AS country,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE LOWER(kind) = 'external' AND sales_force_region = 'JAPAN'
        GROUP BY country
        ORDER BY tenant_count DESC, country
    """,
    "apac_filter_counts": """
        SELECT
          SUM(CASE WHEN sales_force_region = 'APAC'
                        AND country NOT IN ('JP')
                   THEN 1 ELSE 0 END) AS salesforce_before_plan,
          SUM(CASE WHEN sales_force_region = 'APAC'
                        AND country NOT IN ('JP')
                        AND plan NOT IN ('free_trial', 'new_user')
                   THEN 1 ELSE 0 END) AS salesforce_after_plan,
          SUM(CASE WHEN sales_force_region = 'APAC'
                        AND country = 'CN'
                        AND plan NOT IN ('free_trial', 'new_user')
                   THEN 1 ELSE 0 END) AS salesforce_cn_after_plan,
          SUM(CASE WHEN (
                          sales_force_region = 'APAC'
                          OR (
                            (sales_force_region IS NULL
                             OR TRIM(sales_force_region) = ''
                             OR UPPER(TRIM(sales_force_region)) = 'NULL')
                            AND country_region = 'APAC'
                          )
                        )
                        AND country NOT IN ('JP')
                   THEN 1 ELSE 0 END) AS fallback_before_plan,
          SUM(CASE WHEN (
                          sales_force_region = 'APAC'
                          OR (
                            (sales_force_region IS NULL
                             OR TRIM(sales_force_region) = ''
                             OR UPPER(TRIM(sales_force_region)) = 'NULL')
                            AND country_region = 'APAC'
                          )
                        )
                        AND country NOT IN ('JP')
                        AND plan NOT IN ('free_trial', 'new_user')
                   THEN 1 ELSE 0 END) AS fallback_after_plan,
          SUM(CASE WHEN country_region = 'APAC'
                        AND country NOT IN ('JP')
                        AND plan NOT IN ('free_trial', 'new_user')
                   THEN 1 ELSE 0 END) AS country_region_after_plan
        FROM tenants
        WHERE status = 'active' AND LOWER(kind) = 'external'
    """,
    "apac_plan_breakdown": """
        SELECT COALESCE(NULLIF(TRIM(plan), ''), '<empty>') AS plan,
               COUNT(*) AS tenant_count
        FROM tenants
        WHERE status = 'active'
          AND LOWER(kind) = 'external'
          AND sales_force_region = 'APAC'
          AND country NOT IN ('JP')
        GROUP BY plan
        ORDER BY tenant_count DESC, plan
    """,
}


def main() -> None:
    connection = connect(lambda_environment())
    try:
        with connection.cursor() as cursor:
            for name, query in QUERIES.items():
                cursor.execute(query)
                print(
                    name,
                    json.dumps(cursor.fetchall(), ensure_ascii=False, default=str),
                )
    finally:
        connection.close()


if __name__ == "__main__":
    main()