#!/usr/bin/env python3
"""
Reserved Word Detector
Identifies SQL reserved words in table and column names.
"""

import sqlite3
import json
import os

def detect_reserved_words(db_path="database.sqlite"):
    """Check for reserved words in database schema."""

    os.makedirs("tool_output", exist_ok=True)

    # Comprehensive list of SQL reserved words
    SQL_RESERVED = {
        # SQLite specific
        'ABORT', 'ACTION', 'ADD', 'AFTER', 'ALL', 'ALTER', 'ANALYZE', 'AND', 'AS', 'ASC',
        'ATTACH', 'AUTOINCREMENT', 'BEFORE', 'BEGIN', 'BETWEEN', 'BY', 'CASCADE', 'CASE',
        'CAST', 'CHECK', 'COLLATE', 'COLUMN', 'COMMIT', 'CONFLICT', 'CONSTRAINT', 'CREATE',
        'CROSS', 'CURRENT', 'CURRENT_DATE', 'CURRENT_TIME', 'CURRENT_TIMESTAMP', 'DATABASE',
        'DEFAULT', 'DEFERRABLE', 'DEFERRED', 'DELETE', 'DESC', 'DETACH', 'DISTINCT', 'DO',
        'DROP', 'EACH', 'ELSE', 'END', 'ESCAPE', 'EXCEPT', 'EXCLUSIVE', 'EXISTS', 'EXPLAIN',
        'FAIL', 'FOR', 'FOREIGN', 'FROM', 'FULL', 'GLOB', 'GROUP', 'HAVING', 'IF', 'IGNORE',
        'IMMEDIATE', 'IN', 'INDEX', 'INDEXED', 'INITIALLY', 'INNER', 'INSERT', 'INSTEAD',
        'INTERSECT', 'INTO', 'IS', 'ISNULL', 'JOIN', 'KEY', 'LEFT', 'LIKE', 'LIMIT', 'MATCH',
        'NATURAL', 'NO', 'NOT', 'NOTHING', 'NOTNULL', 'NULL', 'OF', 'OFFSET', 'ON', 'OR',
        'ORDER', 'OUTER', 'PLAN', 'PRAGMA', 'PRIMARY', 'QUERY', 'RAISE', 'RECURSIVE',
        'REFERENCES', 'REGEXP', 'REINDEX', 'RELEASE', 'RENAME', 'REPLACE', 'RESTRICT',
        'RIGHT', 'ROLLBACK', 'ROW', 'SAVEPOINT', 'SELECT', 'SET', 'TABLE', 'TEMP', 'TEMPORARY',
        'THEN', 'TO', 'TRANSACTION', 'TRIGGER', 'UNION', 'UNIQUE', 'UPDATE', 'USING', 'VACUUM',
        'VALUES', 'VIEW', 'VIRTUAL', 'WHEN', 'WHERE', 'WITH', 'WITHOUT',
        # Common functions that might be column names
        'AVG', 'COUNT', 'MAX', 'MIN', 'SUM', 'DATE', 'TIME', 'DATETIME', 'YEAR', 'MONTH', 'DAY'
    }

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()

    reserved_info = {
        "tables_requiring_quotes": [],
        "columns_requiring_quotes": {},
        "critical_warnings": [],
        "quoting_examples": []
    }

    try:
        # Get all tables
        cursor.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
        tables = cursor.fetchall()

        for table_name, in tables:
            if table_name.startswith("sqlite_"):
                continue

            table_needs_quotes = False

            # Check if table name is reserved
            if table_name.upper() in SQL_RESERVED:
                reserved_info["tables_requiring_quotes"].append(table_name)
                table_needs_quotes = True

                # Special handling for critical reserved words
                if table_name.lower() == 'transaction':
                    reserved_info["critical_warnings"].insert(0, {
                        "severity": "CRITICAL",
                        "table": table_name,
                        "message": f"Table '{table_name}' is a SQL reserved word - ALWAYS use backticks",
                        "example": f"FROM `{table_name}` or JOIN `{table_name}`"
                    })
                else:
                    reserved_info["critical_warnings"].append({
                        "severity": "HIGH",
                        "table": table_name,
                        "message": f"Table '{table_name}' is a reserved word",
                        "example": f"Use `{table_name}` with backticks"
                    })

            # Check for spaces or special characters
            if ' ' in table_name or '-' in table_name or '.' in table_name:
                if table_name not in reserved_info["tables_requiring_quotes"]:
                    reserved_info["tables_requiring_quotes"].append(table_name)
                    table_needs_quotes = True

            # Get columns for this table
            cursor.execute(f"PRAGMA table_info(`{table_name}`)")
            columns = cursor.fetchall()

            columns_needing_quotes = []

            for col in columns:
                col_name = col[1]

                # Check if column name is reserved
                if col_name.upper() in SQL_RESERVED:
                    columns_needing_quotes.append(col_name)

                # Check for spaces or special characters
                if ' ' in col_name or '-' in col_name or '.' in col_name:
                    if col_name not in columns_needing_quotes:
                        columns_needing_quotes.append(col_name)

            if columns_needing_quotes:
                reserved_info["columns_requiring_quotes"][table_name] = columns_needing_quotes

            # Generate quoting examples
            if table_needs_quotes or columns_needing_quotes:
                example = {
                    "table": table_name,
                    "needs_table_quotes": table_needs_quotes,
                    "columns_needing_quotes": columns_needing_quotes[:3],  # Limit for readability
                    "sql_examples": []
                }

                if table_needs_quotes:
                    example["sql_examples"].append(f"SELECT * FROM `{table_name}`")
                    example["sql_examples"].append(f"JOIN `{table_name}` T2 ON ...")

                if columns_needing_quotes:
                    col = columns_needing_quotes[0]
                    example["sql_examples"].append(f"SELECT `{col}` FROM ...")
                    example["sql_examples"].append(f"WHERE `{col}` = 'value'")

                reserved_info["quoting_examples"].append(example)

        # Summary
        reserved_info["summary"] = {
            "tables_requiring_quotes": len(reserved_info["tables_requiring_quotes"]),
            "total_columns_requiring_quotes": sum(len(cols) for cols in reserved_info["columns_requiring_quotes"].values()),
            "critical_issues": len([w for w in reserved_info["critical_warnings"] if w["severity"] == "CRITICAL"])
        }

    except Exception as e:
        reserved_info["error"] = str(e)
    finally:
        conn.close()

    # Write to output file
    output_path = "tool_output/reserved_words.json"
    with open(output_path, 'w') as f:
        json.dump(reserved_info, f, indent=2)

    print(f"Reserved word check complete - results in {output_path}")
    if reserved_info["critical_warnings"]:
        print(f"⚠️  Found {len(reserved_info['critical_warnings'])} reserved word issues")
    if reserved_info["tables_requiring_quotes"]:
        print(f"Tables requiring backticks: {', '.join(reserved_info['tables_requiring_quotes'][:5])}")

if __name__ == "__main__":
    detect_reserved_words()