import pyodbc
from .config import Config

# ── Connection ────────────────────────────────────────────────────

def get_db(db_name: str = None):
    """Analytics server se connect karo (DataAnalytics DB + admin/user tables)."""
    conn_str = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={Config.SQL_SERVER};"
        f"DATABASE={db_name or Config.SQL_DATABASE};"
        f"UID={Config.SQL_USERNAME};"
        f"PWD={Config.SQL_PASSWORD}"
    )
    return pyodbc.connect(conn_str, autocommit=False)


def get_client_db(db_name: str):
    """
    Production/Client server se connect karo.
    Yeh function tab use hota hai jab client ka apna alag DB access karna ho
    (db_analytics.py aur db_dashboard.py mein use hota hai).
    
    CLIENT_SQL_SERVER .env mein set karo — analytics server se alag hoga.
    Agar CLIENT_SQL_SERVER set nahi hai to fallback analytics server pe.
    """
    server   = Config.CLIENT_SQL_SERVER   or Config.SQL_SERVER
    username = Config.CLIENT_SQL_USERNAME or Config.SQL_USERNAME
    password = Config.CLIENT_SQL_PASSWORD or Config.SQL_PASSWORD

    conn_str = (
        f"DRIVER={{ODBC Driver 17 for SQL Server}};"
        f"SERVER={server};"
        f"DATABASE={db_name};"
        f"UID={username};"
        f"PWD={password}"
    )
    return pyodbc.connect(conn_str, autocommit=False)

# ── USERS ─────────────────────────────────────────────────────────

def get_user_by_username(username: str):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT u.id, u.username, u.password_hash, u.full_name,
                   u.email, u.role_id, u.ssrs_folder, u.is_active, u.client_db
            FROM Reportusers u
            WHERE u.username = ? AND u.is_active = 1
            """, username
        )
        row = cur.fetchone()
    if not row:
        return None
    return {
        "id": row[0], "username": row[1], "password_hash": row[2],
        "full_name": row[3], "email": row[4], "role_id": row[5],
        "ssrs_folder": row[6], "is_active": bool(row[7]),
        "client_db": row[8] 
    }

def get_user_by_id(user_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT id, username, full_name, email, role_id,
                   ssrs_folder, is_active, last_login, created_at
            FROM Reportusers WHERE id = ?
            """, user_id
        )
        row = cur.fetchone()
    if not row:
        return None
    return {
        "id": row[0], "username": row[1], "full_name": row[2],
        "email": row[3], "role_id": row[4], "ssrs_folder": row[5],
        "is_active": bool(row[6]), "last_login": row[7], "created_at": row[8]
    }

def get_all_clients():
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT u.id, u.username, u.full_name, u.email,
                   u.ssrs_folder, u.is_active, u.last_login, u.created_at,
                   COUNT(a.id) AS report_count
            FROM Reportusers u
            LEFT JOIN ReportClientAccess a
                   ON a.client_id = u.id AND a.is_active = 1
            WHERE u.role_id = 3
            GROUP BY u.id, u.username, u.full_name, u.email,
                      u.ssrs_folder, u.is_active, u.last_login, u.created_at
            ORDER BY u.full_name
            """
        )
        return [
            {
                "id": r[0], "username": r[1], "full_name": r[2],
                "email": r[3], "ssrs_folder": r[4],
                "is_active": bool(r[5]), "last_login": r[6],
                "created_at": r[7], "report_count": r[8]
            }
            for r in cur.fetchall()
        ]

def get_all_users_by_role(role_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT id, username, full_name, email, is_active, last_login, created_at
            FROM Reportusers WHERE role_id = ? ORDER BY full_name
            """, role_id
        )
        return [
            {"id": r[0], "username": r[1], "full_name": r[2],
             "email": r[3], "is_active": bool(r[4]),
             "last_login": r[5], "created_at": r[6]}
            for r in cur.fetchall()
        ]

def add_user(username, password_hash, full_name, email,
             role_id, ssrs_folder, created_by, client_db=None) -> int:
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            INSERT INTO Reportusers
                (username, password_hash, full_name, email,
                 role_id, ssrs_folder, created_by, client_db)
            OUTPUT INSERTED.id
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            username, password_hash, full_name, email,
            role_id, ssrs_folder or None, created_by, client_db or None
        )
        new_id = cur.fetchone()[0]
        conn.commit()
    return new_id

def toggle_user_active(user_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            "UPDATE Reportusers SET is_active = CASE WHEN is_active=1 THEN 0 ELSE 1 END WHERE id=?",
            user_id
        )
        conn.commit()

def reset_user_password(user_id: int, password_hash: str):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            "UPDATE Reportusers SET password_hash=? WHERE id=?",
            password_hash, user_id
        )
        conn.commit()

def update_last_login(user_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            "UPDATE Reportusers SET last_login=GETDATE() WHERE id=?", user_id
        )
        conn.commit()

def delete_user(user_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute("UPDATE Reportusers SET is_active=0 WHERE id=?", user_id)
        conn.commit()

# ── REPORT CLIENT ACCESS ──────────────────────────────────────────

def get_client_categories(client_id: int) -> dict:
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT report_id, report_path, report_name, category,
                   COALESCE(display_name, report_name) AS display_name
            FROM ReportClientAccess
            WHERE client_id = ? AND is_active = 1
            ORDER BY category, COALESCE(display_name, report_name)
            """, client_id
        )
        rows = cur.fetchall()

    categories: dict = {}
    for row in rows:
        cat = row[3] or "General"
        categories.setdefault(cat, []).append({
            "id": row[0], "path": row[1], "name": row[4]  # use display_name as 'name'
        })
    return categories

def get_client_access_list(client_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            """
            SELECT a.id, a.report_path, a.report_name, a.category,
                   a.is_active, a.granted_at,
                   u.full_name AS granted_by_name,
                   COALESCE(a.display_name, a.report_name) AS display_name
            FROM ReportClientAccess a
            LEFT JOIN Reportusers u ON u.id = a.granted_by
            WHERE a.client_id = ?
            ORDER BY a.category, COALESCE(a.display_name, a.report_name)
            """, client_id
        )
        return [
            {
                "id": r[0], "report_path": r[1], "report_name": r[2],
                "category": r[3], "is_active": bool(r[4]),
                "granted_at": r[5], "granted_by": r[6],
                "display_name": r[7]
            }
            for r in cur.fetchall()
        ]

def grant_report_access(client_id, report_id, report_path,
                         report_name, category, granted_by,
                         display_name: str = None) -> bool:
    try:
        with get_db() as conn:
            cur = conn.cursor()
            cur.execute(
                "SELECT id, is_active FROM ReportClientAccess WHERE client_id=? AND report_path=?",
                client_id, report_path
            )
            existing = cur.fetchone()
            if existing:
                if existing[1]: 
                    return False    
                cur.execute(
                    """
                    UPDATE ReportClientAccess
                    SET is_active=1, category=?, granted_by=?, granted_at=GETDATE(),
                        display_name=COALESCE(?, display_name)
                    WHERE id=?
                    """, category, granted_by, display_name, existing[0]
                )
            else:
                cur.execute(
                    """
                    INSERT INTO ReportClientAccess
                        (client_id, report_id, report_path, report_name,
                         category, granted_by, display_name)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                    """,
                    client_id, report_id, report_path, report_name,
                    category, granted_by, display_name or None
                )
            conn.commit()
        return True
    except Exception as e:
        print(f"Grant error: {e}")
        return False

def batch_grant_report_access(client_id: int, reports: list, category: str, granted_by: int) -> dict:
    """Grant access for multiple reports in a single DB transaction."""
    ok = 0
    skip = 0
    fail = 0
    try:
        with get_db() as conn:
            cur = conn.cursor()
            for r in reports:
                try:
                    report_path = r.get("report_path", "")
                    cur.execute(
                        "SELECT id, is_active FROM ReportClientAccess WHERE client_id=? AND report_path=?",
                        client_id, report_path
                    )
                    existing = cur.fetchone()
                    if existing:
                        if existing[1]:
                            skip += 1
                            continue
                        cur.execute(
                            """UPDATE ReportClientAccess
                               SET is_active=1, category=?, granted_by=?, granted_at=GETDATE()
                               WHERE id=?""",
                            category, granted_by, existing[0]
                        )
                    else:
                        cur.execute(
                            """INSERT INTO ReportClientAccess
                               (client_id, report_id, report_path, report_name, category, granted_by)
                               VALUES (?, ?, ?, ?, ?, ?)""",
                            client_id, r.get("report_id", ""), report_path,
                            r.get("report_name", ""), category, granted_by
                        )
                    ok += 1
                except Exception as e:
                    print(f"Batch grant row error: {e}")
                    fail += 1
            conn.commit()
    except Exception as e:
        print(f"Batch grant transaction error: {e}")
        fail += len(reports)
    return {"ok": ok, "skip": skip, "fail": fail}

def revoke_report_access(access_id: int):
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute(
            "UPDATE ReportClientAccess SET is_active=0 WHERE id=?", access_id
        )
        conn.commit()

# ── AUDIT LOG ─────────────────────────────────────────────────────

def log_action(user_id: int, action: str,
               report_name: str = "", ip: str = "", extra: str = ""):
    try:
        with get_db() as conn:
            cur = conn.cursor()
            cur.execute(
                """
                INSERT INTO Reportaudit_log
                    (user_id, report_name, action, ip_address, extra_info)
                VALUES (?, ?, ?, ?, ?)
                """,
                user_id, report_name or None, action,
                ip or None, extra or None
            )
            conn.commit()
    except Exception as e:
        print(f"Audit log error: {e}")

def get_audit_log(limit=200, user_id=None):
    with get_db() as conn:
        cur = conn.cursor()
        query = f"""
            SELECT TOP {limit}
                   a.accessed_at, u.full_name, u.username,
                   r.role_name, a.report_name, a.action, a.ip_address, a.extra_info
            FROM Reportaudit_log a
            JOIN Reportusers u ON u.id = a.user_id
            JOIN Reportroles r ON r.id = u.role_id
            {"WHERE a.user_id = ?" if user_id else ""}
            ORDER  BY a.accessed_at DESC
        """
        cur.execute(query, user_id) if user_id else cur.execute(query)
        return [
            {
                "time": r[0], "full_name": r[1], "username": r[2],
                "role": r[3], "report": r[4], "action": r[5],
                "ip": r[6], "extra": r[7]
            }
            for r in cur.fetchall()
        ]

# ── ENTITY SEARCH  ─────────────────────

ENTITY_PARAM_MAP = {
    "projectid": ("Project",  "ProjectId",  "ProjectCode",  "ProjectName"),
    "clientid": ("Client",  "ClientId",   "ClientCode",   "ClientName"),
    "supplierid": ("Supplier", "SupplierId", "SupplierCode", "SupplierName"),
}


def search_entity(entity_type: str, query: str,
                  limit: int = 10, db_name: str = None):
    mapping = None
    for param_name, info in ENTITY_PARAM_MAP.items():
        if info[0].lower() == entity_type.lower():
            mapping = info
            break
    if not mapping:
        return []

    table, id_col, code_col, name_col = mapping
    q = f"%{query.strip()}%"

    try:
        with get_db(db_name) as conn:
            cur = conn.cursor()
            cur.execute(
                f"""
                SELECT TOP {limit}
                       {id_col}, {code_col}, {name_col}
                FROM {table}
                WHERE ({code_col} LIKE ? OR {name_col} LIKE ?)
                ORDER BY {name_col}
                """, q, q
            )
            return [
                {"id": str(r[0]), "code": str(r[1] or ""),
                 "name": str(r[2] or ""), "display": f"{r[1]} — {r[2]}"}
                for r in cur.fetchall()
            ]
    except Exception as e:
        print(f"search_entity error ({entity_type}): {e}")
        return []

def resolve_entity_id(entity_type: str, code_or_name: str,
                      db_name: str = None):
    results = search_entity(entity_type, code_or_name,
                            limit=5, db_name=db_name)
    if not results:
        return None
    for r in results:
        if r["code"].lower() == code_or_name.lower():
            return r["id"]
    return results[0]["id"]

def get_dashboard_stats() -> dict:
    with get_db() as conn:
        cur = conn.cursor()

        cur.execute("SELECT COUNT(*) FROM Reportusers WHERE role_id=3 AND is_active=1")
        active_clients = cur.fetchone()[0]

        cur.execute("SELECT COUNT(*) FROM Reportusers WHERE role_id=3")
        total_clients = cur.fetchone()[0]

        cur.execute("SELECT COUNT(*) FROM ReportClientAccess WHERE is_active=1")
        total_grants = cur.fetchone()[0]

        cur.execute(
            """SELECT COUNT(*) FROM Reportaudit_log
               WHERE action LIKE 'DOWNLOAD%'
               AND CAST(accessed_at AS DATE) = CAST(GETDATE() AS DATE)"""
        )
        today_downloads = cur.fetchone()[0]

        cur.execute(
            """SELECT TOP 5 u.full_name, a.action, a.report_name, a.accessed_at
               FROM Reportaudit_log a
               JOIN Reportusers u ON u.id = a.user_id
               ORDER BY a.accessed_at DESC"""
        )
        recent_activity = [
            {"name": r[0], "action": r[1], "report": r[2], "time": r[3]}
            for r in cur.fetchall()
        ]

    return {
        "active_clients": active_clients,
        "total_clients": total_clients,
        "total_grants": total_grants,
        "today_downloads": today_downloads,
        "recent_activity": recent_activity
    }

# ── REPORT DISPLAY NAME / CATEGORY MANAGEMENT ────────────────────
# Sab kuch sirf ReportClientAccess se — koi alag master table nahi.
# Distinct report_path rows se unique reports milte hain.

def get_all_unique_reports():
    """
    ReportClientAccess se distinct reports (by path) return karo.
    Backup reports (naam ke end mein date ho) skip hote hain.
    Har report ke liye ek hi record — display_name aur category
    jo admin ne set ki hai.
    """
    import re
    backup_re = re.compile(
        r'_\d{1,2}[A-Za-z]{3}\d{2,4}$'   # _27Apr23, _17Oct23
        r'|_\d{8}$'                         # _20230427
        r'|\d{4}-\d{2}-\d{2}$'             # 2023-04-27
        r'|_\d{1,2}[A-Za-z]{3}$'           # _29Apr
    )
    with get_db() as conn:
        cur = conn.cursor()
        cur.execute("""
            SELECT
                report_path,
                MAX(report_name)                         AS report_name,
                MAX(COALESCE(display_name, report_name)) AS display_name,
                MAX(category)                            AS category,
                MAX(CAST(is_active AS INT))              AS is_active
            FROM ReportClientAccess
            GROUP BY report_path
            ORDER BY
                MAX(category),
                MAX(COALESCE(display_name, report_name))
            """)
        rows = cur.fetchall()

    result = []
    for r in rows:
        path = r[0] or ""
        name = r[1] or ""
        if backup_re.search(name):
            continue
        result.append({
            "report_path":  path,
            "report_name":  name,
            "display_name": r[2] or name,
            "category":     r[3] or "",
            "is_active":    bool(r[4]),
        })
    return result

def update_report_meta(report_path: str, display_name: str,
                        category: str, is_active: bool = None) -> bool:
    """
    Update display_name, category (and optionally is_active) for all rows
    with the given report_path in ReportClientAccess.
    """
    try:
        with get_db() as conn:
            cur = conn.cursor()
            if is_active is not None:
                cur.execute(
                    """
                    UPDATE ReportClientAccess
                    SET display_name = ?, category = ?, is_active = ?
                    WHERE report_path = ?
                    """,
                    display_name, category, 1 if is_active else 0, report_path
                )
            else:
                cur.execute(
                    """
                    UPDATE ReportClientAccess
                    SET display_name = ?, category = ?
                    WHERE report_path = ?
                    """,
                    display_name, category, report_path
                )
            conn.commit()
        return True
    except Exception as e:
        print(f"update_report_meta error: {e}")
        return False

def auto_grant_all_reports_for_client(client_id: int, granted_by: int,
                                       ssrs_folder: str = None) -> int:
    """
    Naye client ke liye reports auto-grant karo.

    Strategy (order mein try karta hai):
    1. Agar ssrs_folder diya hai → SSRS se directly reports lo (backup filter ke saath)
    2. Fallback → ReportClientAccess mein jo bhi unique active reports hain unse copy karo

    Returns: number of grants made.
    """
    import re
    from .ssrs import get_folder_reports

    backup_re = re.compile(
        r'_\d{1,2}[A-Za-z]{3}\d{2,4}$'
        r'|_\d{8}$'
        r'|\d{4}-\d{2}-\d{2}$'
        r'|_\d{1,2}[A-Za-z]{3}$'
        r'|_\d{2}[A-Za-z]{3}\d{2}$'
    )

    reports = []

    # ── Strategy 1: SSRS folder se directly ──────────────────────
    if ssrs_folder:
        try:
            ssrs_reports = get_folder_reports("/" + ssrs_folder.strip("/"))
            for r in ssrs_reports:
                name = r.get("name", "")
                path = r.get("path", "")
                if not name or not path:
                    continue
                if backup_re.search(name):
                    continue
                # Existing display_name/category agar kisi aur client ke liye set ho
                existing = _get_existing_meta(path)
                reports.append({
                    "report_id":    r.get("id", ""),
                    "report_path":  path,
                    "report_name":  name,
                    "display_name": existing["display_name"] if existing else name,
                    "category":     existing["category"]     if existing else "General",
                })
        except Exception as e:
            print(f"auto_grant SSRS error: {e}")

    # ── Strategy 2: Fallback — existing ReportClientAccess se ────
    if not reports:
        reports = [
            {
                "report_id":    "",
                "report_path":  r["report_path"],
                "report_name":  r["report_name"],
                "display_name": r["display_name"],
                "category":     r["category"] or "General",
            }
            for r in get_all_unique_reports()
        ]

    count = 0
    for r in reports:
        ok = grant_report_access(
            client_id=client_id,
            report_id=r["report_id"],
            report_path=r["report_path"],
            report_name=r["report_name"],
            category=r["category"],
            granted_by=granted_by,
            display_name=r["display_name"]
        )
        if ok:
            count += 1
    return count


def _get_existing_meta(report_path: str):
    """ReportClientAccess mein agar yeh path pehle se kisi client ke liye hai
    to uska display_name aur category return karo (admin ne set ki hui values)."""
    try:
        with get_db() as conn:
            cur = conn.cursor()
            cur.execute(
                """
                SELECT TOP 1
                    COALESCE(display_name, report_name) AS display_name,
                    category
                FROM ReportClientAccess
                WHERE report_path = ?
                ORDER BY
                    CASE WHEN display_name IS NOT NULL AND display_name != report_name
                         THEN 0 ELSE 1 END,
                    granted_at DESC
                """, report_path
            )
            row = cur.fetchone()
        if row:
            return {"display_name": row[0] or "", "category": row[1] or "General"}
        return None
    except Exception as e:
        print(f"_get_existing_meta error: {e}")
        return None