app/query_builder.py

"""
Visual Query Builder
Build SQL queries without writing SQL
"""

from typing import Dict, List, Optional

class QueryBuilder:
    """Build SQL queries programmatically"""
    
    def build_select(self, database: str, table: str, columns: List[str] = None, 
                     where: Dict = None, order_by: str = None, limit: int = None, 
                     offset: int = None, group_by: List[str] = None, 
                     having: str = None, joins: List[Dict] = None) -> str:
        """Build SELECT query"""
        
        # SELECT clause
        if columns:
            cols = ", ".join(columns)
        else:
            cols = "*"
        
        query = f'SELECT {cols} FROM "{database}"."{table}"'
        
        # JOINs
        if joins:
            for join in joins:
                join_type = join.get('type', 'INNER').upper()
                join_table = join['table']
                on_condition = join['on']
                query += f' {join_type} JOIN "{join_table}" ON {on_condition}'
        
        # WHERE clause
        if where:
            conditions = []
            for key, value in where.items():
                if isinstance(value, dict):
                    # Handle operators
                    op = value.get('op', '=')
                    val = value.get('value')
                    if isinstance(val, str):
                        conditions.append(f'{key} {op} \'{val}\'')
                    else:
                        conditions.append(f'{key} {op} {val}')
                else:
                    if isinstance(value, str):
                        conditions.append(f'{key} = \'{value}\'')
                    else:
                        conditions.append(f'{key} = {value}')
            
            query += ' WHERE ' + ' AND '.join(conditions)
        
        # GROUP BY
        if group_by:
            query += ' GROUP BY ' + ', '.join(group_by)
        
        # HAVING
        if having:
            query += f' HAVING {having}'
        
        # ORDER BY
        if order_by:
            query += f' ORDER BY {order_by}'
        
        # LIMIT
        if limit:
            query += f' LIMIT {limit}'
        
        # OFFSET
        if offset:
            query += f' OFFSET {offset}'
        
        return query
    
    def build_insert(self, database: str, table: str, data: Dict) -> str:
        """Build INSERT query"""
        columns = list(data.keys())
        values = []
        
        for val in data.values():
            if val is None:
                values.append('NULL')
            elif isinstance(val, str):
                values.append(f"'{val}'")
            else:
                values.append(str(val))
        
        cols_str = ', '.join(columns)
        vals_str = ', '.join(values)
        
        return f'INSERT INTO "{database}"."{table}" ({cols_str}) VALUES ({vals_str})'
    
    def build_update(self, database: str, table: str, data: Dict, where: Dict) -> str:
        """Build UPDATE query"""
        set_clauses = []
        
        for key, val in data.items():
            if val is None:
                set_clauses.append(f'{key} = NULL')
            elif isinstance(val, str):
                set_clauses.append(f'{key} = \'{val}\'')
            else:
                set_clauses.append(f'{key} = {val}')
        
        query = f'UPDATE "{database}"."{table}" SET ' + ', '.join(set_clauses)
        
        if where:
            conditions = []
            for key, val in where.items():
                if isinstance(val, str):
                    conditions.append(f'{key} = \'{val}\'')
                else:
                    conditions.append(f'{key} = {val}')
            query += ' WHERE ' + ' AND '.join(conditions)
        
        return query
    
    def build_delete(self, database: str, table: str, where: Dict) -> str:
        """Build DELETE query"""
        query = f'DELETE FROM "{database}"."{table}"'
        
        if where:
            conditions = []
            for key, val in where.items():
                if isinstance(val, str):
                    conditions.append(f'{key} = \'{val}\'')
                else:
                    conditions.append(f'{key} = {val}')
            query += ' WHERE ' + ' AND '.join(conditions)
        
        return query
    
    def build_aggregate(self, database: str, table: str, aggregates: List[Dict], 
                       group_by: List[str] = None, where: Dict = None) -> str:
        """Build aggregate query (COUNT, SUM, AVG, etc.)"""
        
        agg_clauses = []
        for agg in aggregates:
            func = agg['function'].upper()
            column = agg['column']
            alias = agg.get('alias', f'{func.lower()}_{column}')
            agg_clauses.append(f'{func}({column}) AS {alias}')
        
        query = f'SELECT {", ".join(agg_clauses)} FROM "{database}"."{table}"'
        
        if where:
            conditions = []
            for key, val in where.items():
                if isinstance(val, str):
                    conditions.append(f'{key} = \'{val}\'')
                else:
                    conditions.append(f'{key} = {val}')
            query += ' WHERE ' + ' AND '.join(conditions)
        
        if group_by:
            query += ' GROUP BY ' + ', '.join(group_by)
        
        return query

query_builder = QueryBuilder()