#!/usr/bin/env python3
"""
Reserved Word Detector Tool
Identifies SQL reserved words used as table/column names.
"""

import sqlite3
import json
import os

# Common SQL reserved words that require backticks
RESERVED_WORDS = {
    'add', 'all', 'alter', 'and', 'as', 'asc', 'between', 'by', 'case', 'check',
    'column', 'create', 'cross', 'current', 'database', 'default', 'delete', 'desc',
    'distinct', 'drop', 'else', 'end', 'escape', 'except', 'exists', 'for', 'foreign',
    'from', 'full', 'group', 'having', 'in', 'index', 'inner', 'insert', 'intersect',
    'into', 'is', 'join', 'key', 'left', 'like', 'limit', 'match', 'natural', 'not',
    'null', 'on', 'or', 'order', 'outer', 'primary', 'references', 'right', 'select',
    'set', 'table', 'then', 'to', 'transaction', 'union', 'unique', 'update', 'using',
    'values', 'when', 'where', 'with', 'first', 'last', 'offset', 'plan', 'pragma',
    'query', 'raise', 'recursive', 'regexp', 'release', 'rename', 'replace', 'restrict',
    'rollback', 'row', 'savepoint', 'temp', 'temporary', 'trigger', 'vacuum', 'view',
    'virtual', 'window', 'rank', 'user', 'current_date', 'current_time', 'current_timestamp',
    'date', 'time', 'timestamp', 'year', 'month', 'day', 'hour', 'minute', 'second'
}

def detect_reserved_words(db_path="database.sqlite"):
    """Detect reserved words used in table and column names."""

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

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

    reserved_usage = {
        "tables_needing_backticks": [],
        "columns_needing_backticks": {},
        "all_reserved_items": [],
        "critical_warnings": []
    }

    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

            # Check if table name is reserved
            if table_name.lower() in RESERVED_WORDS:
                reserved_usage["tables_needing_backticks"].append(table_name)
                reserved_usage["all_reserved_items"].append(f"`{table_name}` (table)")

                if table_name.lower() == 'transaction':
                    reserved_usage["critical_warnings"].append(
                        f"⚠️ Table '{table_name}' is a reserved word - ALWAYS use backticks: `{table_name}`"
                    )

            # Check column names
            cursor.execute(f"PRAGMA table_info(`{table_name}`)")
            columns = cursor.fetchall()

            reserved_cols = []
            for col in columns:
                col_name = col[1]
                if col_name.lower() in RESERVED_WORDS:
                    reserved_cols.append(col_name)
                    reserved_usage["all_reserved_items"].append(f"`{table_name}`.`{col_name}` (column)")

                    if col_name.lower() in ['order', 'group', 'rank', 'user']:
                        reserved_usage["critical_warnings"].append(
                            f"⚠️ Column '{col_name}' in table '{table_name}' is a reserved word - use `{col_name}`"
                        )

            if reserved_cols:
                reserved_usage["columns_needing_backticks"][table_name] = reserved_cols

    except Exception as e:
        reserved_usage["error"] = str(e)

    finally:
        conn.close()

    # Add usage instructions
    reserved_usage["usage_instructions"] = {
        "tables": f"Always use backticks for these tables: {', '.join([f'`{t}`' for t in reserved_usage['tables_needing_backticks']])}",
        "columns": "Use backticks for reserved column names in queries",
        "example": "SELECT * FROM `transaction` WHERE `order` = 123"
    }

    # Write output
    with open("tool_output/reserved_words.json", "w") as f:
        json.dump(reserved_usage, f, indent=2)

    print(f"Reserved word detection complete: {len(reserved_usage['all_reserved_items'])} reserved items found")
    if reserved_usage["critical_warnings"]:
        print("CRITICAL WARNINGS:")
        for warning in reserved_usage["critical_warnings"]:
            print(f"  {warning}")
    print("Results saved to tool_output/reserved_words.json")

if __name__ == "__main__":
    detect_reserved_words()