Parameters and Types

class Parameter:
    """Base class for SQL parameters."""
    
class QmarkParameter(Parameter):
    def __init__(self, key):
        """
        Question mark style parameter: ?
        
        Parameters:
        - key: Parameter identifier
        """

class NamedParameter(Parameter):
    def __init__(self, key):
        """
        Named style parameter: :name
        
        Parameters:
        - key: Parameter name
        """

class NumericParameter(Parameter):
    def __init__(self, key):
        """
        Numeric style parameter: :1, :2, etc.
        
        Parameters:
        - key: Parameter number
        """

class FormatParameter(Parameter):
    def __init__(self, key):
        """
        ANSI C printf style parameter: %s, %d, etc.
        
        Parameters:
        - key: Parameter format specifier
        """

class PyformatParameter(Parameter):
    def __init__(self, key):
        """
        Python format style parameter: %(name)s
        
        Parameters:
        - key: Parameter name
        """

from pypika import Query, Table
from pypika.terms import QmarkParameter, NamedParameter, PyformatParameter

users = Table('users')
orders = Table('orders')

# Question mark parameters (SQLite, MySQL)
qmark_query = (Query.from_(users)
               .select('*')
               .where(users.age > QmarkParameter('age'))
               .where(users.status == QmarkParameter('status')))

print(qmark_query.get_sql())
# SELECT * FROM "users" WHERE "age">? AND "status"=?

# Named parameters (PostgreSQL, Oracle)
named_query = (Query.from_(users)
               .select('*')
               .where(users.age > NamedParameter('min_age'))
               .where(users.status == NamedParameter('user_status')))

print(named_query.get_sql())
# SELECT * FROM "users" WHERE "age">:min_age AND "status"=:user_status

# Python format parameters
pyformat_query = (Query.from_(users)
                  .select('*')
                  .where(users.age > PyformatParameter('min_age'))
                  .where(users.status == PyformatParameter('status')))

print(pyformat_query.get_sql())
# SELECT * FROM "users" WHERE "age">%(min_age)s AND "status"=%(status)s

# INSERT with parameters
insert_query = (Query.into(users)
                .columns(users.name, users.email, users.age)
                .insert(
                    NamedParameter('name'),
                    NamedParameter('email'), 
                    NamedParameter('age')
                ))

# UPDATE with parameters
update_query = (Query.update(users)
                .set(users.email, NamedParameter('new_email'))
                .set(users.updated_at, NamedParameter('timestamp'))
                .where(users.id == NamedParameter('user_id')))

class JSON:
    def get_json_value(self, key_or_index) -> BasicCriterion:
        """
        JSON -> operator: extract JSON value.
        
        Parameters:
        - key_or_index: Object key or array index
        """
        
    def get_text_value(self, key_or_index) -> BasicCriterion:
        """
        JSON ->> operator: extract JSON value as text.
        
        Parameters:
        - key_or_index: Object key or array index
        """
        
    def get_path_json_value(self, path_json) -> BasicCriterion:
        """
        JSON #> operator: extract JSON value at path.
        
        Parameters:
        - path_json: JSON path array
        """
        
    def get_path_text_value(self, path_json) -> BasicCriterion:
        """
        JSON #>> operator: extract JSON value at path as text.
        
        Parameters:
        - path_json: JSON path array
        """
        
    def has_key(self, key) -> BasicCriterion:
        """
        JSON ? operator: check if key exists.
        
        Parameters:
        - key: Key to check for existence
        """
        
    def contains(self, other) -> BasicCriterion:
        """
        JSON @> operator: check if contains value.
        
        Parameters:
        - other: JSON value to check containment
        """
        
    def contained_by(self, other) -> BasicCriterion:
        """
        JSON <@ operator: check if contained by value.
        
        Parameters:
        - other: JSON value to check containment by
        """
        
    def has_keys(self, keys) -> BasicCriterion:
        """
        JSON ?& operator: check if has all keys.
        
        Parameters:
        - keys: Array of keys to check
        """
        
    def has_any_keys(self, keys) -> BasicCriterion:
        """
        JSON ?| operator: check if has any keys.
        
        Parameters:
        - keys: Array of keys to check
        """

from pypika import PostgreSQLQuery, Table, JSON

# PostgreSQL table with JSON columns
users = Table('users')
events = Table('events')

# JSON field operations
profile = users.profile  # Assuming JSON/JSONB column
metadata = events.metadata

# Extract JSON values
query = (PostgreSQLQuery.from_(users)
         .select(
             users.id,
             profile.get_text_value('name').as_('profile_name'),
             profile.get_json_value('preferences').as_('user_prefs'),
             profile.get_text_value('email').as_('profile_email')
         ))

# Path-based JSON extraction
query = (PostgreSQLQuery.from_(users)
         .select(
             users.id,
             profile.get_path_text_value(['address', 'street']).as_('street'),
             profile.get_path_text_value(['address', 'city']).as_('city'),
             profile.get_path_json_value(['contact', 'phones']).as_('phone_numbers')
         ))

# JSON key existence checks
query = (PostgreSQLQuery.from_(users)
         .select(users.id, users.name)
         .where(profile.has_key('email'))
         .where(profile.has_key('phone')))

# Multiple key checks
query = (PostgreSQLQuery.from_(users)
         .select('*')
         .where(profile.has_keys(['name', 'email', 'address']))
         .where(profile.has_any_keys(['twitter', 'facebook', 'linkedin'])))

# JSON containment operations
query = (PostgreSQLQuery.from_(users)
         .select('*')
         .where(profile.contains({'subscription': 'premium'}))
         .where(profile.contains({'preferences': {'newsletter': True}})))

# Complex JSON queries
query = (PostgreSQLQuery.from_(events)
         .select(
             events.id,
             events.event_type,
             metadata.get_text_value('source').as_('event_source'),
             metadata.get_path_text_value(['device', 'type']).as_('device_type'),
             metadata.get_path_json_value(['location', 'coordinates']).as_('coordinates')
         )
         .where(metadata.has_key('user_id'))
         .where(metadata.get_text_value('status') == 'completed'))

# JSON aggregations
from pypika.functions import Count
query = (PostgreSQLQuery.from_(users)
         .select(
             profile.get_text_value('country').as_('country'),
             Count('*').as_('user_count')
         )
         .where(profile.has_key('country'))
         .groupby(profile.get_text_value('country')))

class Interval:
    def __init__(self, years=0, months=0, days=0, hours=0, minutes=0, 
                 seconds=0, microseconds=0, quarters=0, weeks=0, dialect=None):
        """
        Create time interval for date arithmetic.
        
        Parameters:
        - years: Number of years
        - months: Number of months  
        - days: Number of days
        - hours: Number of hours
        - minutes: Number of minutes
        - seconds: Number of seconds
        - microseconds: Number of microseconds
        - quarters: Number of quarters
        - weeks: Number of weeks
        - dialect: Database dialect for formatting
        """

from pypika import Query, Table, Interval
from pypika.functions import Now, CurDate

users = Table('users')
subscriptions = Table('subscriptions')
events = Table('events')

# Basic interval operations
one_day = Interval(days=1)
one_week = Interval(weeks=1)
one_month = Interval(months=1)
one_year = Interval(years=1)
thirty_minutes = Interval(minutes=30)

# Date arithmetic with intervals
query = (Query.from_(users)
         .select(
             users.name,
             users.created_at,
             (users.created_at + one_year).as_('anniversary_date'),
             (Now() - users.created_at).as_('account_age'),
             (users.created_at + Interval(days=30)).as_('trial_end_date')
         ))

# Filter by time ranges
recent_users = (Query.from_(users)
                .select('*')
                .where(users.created_at > (Now() - Interval(days=30))))

old_users = (Query.from_(users)
             .select('*')
             .where(users.created_at < (Now() - Interval(years=2))))

# Subscription management
query = (Query.from_(subscriptions)
         .select(
             subscriptions.user_id,
             subscriptions.start_date,
             (subscriptions.start_date + Interval(months=12)).as_('renewal_date'),
             subscriptions.trial_start,
             (subscriptions.trial_start + Interval(days=14)).as_('trial_end')
         ))

# Complex interval calculations
quarterly_intervals = Interval(quarters=1)
semi_annual = Interval(months=6)
bi_weekly = Interval(weeks=2)

query = (Query.from_(events)
         .select(
             events.event_date,
             (events.event_date + quarterly_intervals).as_('next_quarter'),
             (events.event_date - semi_annual).as_('six_months_ago'),
             (events.event_date + bi_weekly).as_('two_weeks_later')
         ))

# Time-based grouping and analysis
from pypika.functions import Extract, DatePart
query = (Query.from_(users)
         .select(
             Extract(DatePart.year, users.created_at).as_('signup_year'),
             Extract(DatePart.month, users.created_at).as_('signup_month'),
             Count('*').as_('signups')
         )
         .where(users.created_at > (Now() - Interval(years=2)))
         .groupby(
             Extract(DatePart.year, users.created_at),
             Extract(DatePart.month, users.created_at)
         ))

# Interval comparisons
active_sessions = (Query.from_(events)
                   .select('*')
                   .where(events.session_end - events.session_start > Interval(minutes=5)))

# Database-specific interval usage
from pypika import PostgreSQLQuery
pg_query = (PostgreSQLQuery.from_(users)
            .select(
                users.name,
                (users.created_at + Interval(months=1, dialect='postgresql')).as_('plus_month')
            ))

class Array:
    def __init__(self, *items):
        """
        Create SQL array literal.
        
        Parameters:
        - items: Array elements
        """
        
    def contains(self, item) -> BasicCriterion:
        """Check if array contains item."""
        
    def overlap(self, other_array) -> BasicCriterion:
        """Check if arrays overlap."""
        
    def length(self) -> Function:
        """Get array length."""

from pypika import PostgreSQLQuery, Table, Array

# PostgreSQL arrays
users = Table('users')
posts = Table('posts')

# Array operations
tags_array = Array('python', 'sql', 'database')
colors_array = Array('red', 'blue', 'green')

# Array containment
query = (PostgreSQLQuery.from_(posts)
         .select('*')
         .where(posts.tags.contains('python')))

# Array overlap
query = (PostgreSQLQuery.from_(posts)
         .select('*')
         .where(posts.tags.overlap(tags_array)))

# Array functions
from pypika.functions import ArrayLength, ArrayAppend
query = (PostgreSQLQuery.from_(posts)
         .select(
             posts.title,
             posts.tags,
             ArrayLength(posts.tags).as_('tag_count')
         ))

# Complex array queries
query = (PostgreSQLQuery.from_(users)
         .select(
             users.name,
             users.skills,
             ArrayLength(users.skills).as_('skill_count')
         )
         .where(users.skills.overlap(Array('python', 'java', 'javascript')))
         .where(ArrayLength(users.skills) >= 3))

class Tuple:
    def __init__(self, *items):
        """
        Create SQL tuple literal.
        
        Parameters:
        - items: Tuple elements
        """
        
    def isin(self, *values) -> BasicCriterion:
        """Check if tuple is in list of values."""

from pypika import Query, Table, Tuple

users = Table('users')
coordinates = Table('coordinates')

# Multi-column comparisons
name_tuple = Tuple(users.first_name, users.last_name)
valid_names = Tuple(
    Tuple('John', 'Doe'),
    Tuple('Jane', 'Smith'),
    Tuple('Bob', 'Johnson')
)

query = (Query.from_(users)
         .select('*')
         .where(name_tuple.isin(valid_names)))

# Coordinate comparisons
location_tuple = Tuple(coordinates.latitude, coordinates.longitude)
valid_locations = Tuple(
    Tuple(40.7128, -74.0060),  # New York
    Tuple(34.0522, -118.2437), # Los Angeles
    Tuple(41.8781, -87.6298)   # Chicago
)

query = (Query.from_(coordinates)
         .select('*')
         .where(location_tuple.isin(valid_locations)))

# Complex tuple operations
user_status_tuple = Tuple(users.status, users.role, users.department)
admin_combinations = Tuple(
    Tuple('active', 'admin', 'IT'),
    Tuple('active', 'admin', 'Security'),
    Tuple('pending', 'admin', 'IT')
)

query = (Query.from_(users)
         .select('*')
         .where(user_status_tuple.isin(admin_combinations)))

class NullValue:
    """SQL NULL value representation."""

class SystemTimeValue:
    """SQL SYSTEM_TIME value for temporal tables."""

class Not:
    def __init__(self, term):
        """
        NOT operator wrapper.
        
        Parameters:
        - term: Expression to negate
        """

class Bracket:
    def __init__(self, term):
        """
        Parentheses wrapper for expressions.
        
        Parameters:
        - term: Expression to wrap in parentheses
        """

class Index:
    def __init__(self, name: str):
        """
        Database index reference.
        
        Parameters:
        - name: Index name
        """

from pypika import Query, Table, NULL, SYSTEM_TIME, Not, Bracket, Index

users = Table('users')
orders = Table('orders')

# NULL operations
query = (Query.from_(users)
         .select('*')
         .where(users.phone != NULL)
         .where(users.deleted_at == NULL))

# NOT operations
not_admin = Not(users.role == 'admin')
query = Query.from_(users).select('*').where(not_admin)

# Complex conditions with parentheses
complex_condition = Bracket(
    (users.age >= 18) & (users.status == 'active')
) | Bracket(
    (users.role == 'admin') & (users.verified == True)
)

query = Query.from_(users).select('*').where(complex_condition)

# Index hints (MySQL-specific)
user_email_idx = Index('idx_user_email')
# Usage would depend on dialect-specific implementation

# Temporal table operations
historical_users = users.for_(SYSTEM_TIME.between('2023-01-01', '2023-12-31'))
query = Query.from_(historical_users).select('*')

# Combining special values
query = (Query.from_(users)
         .select('*')
         .where(
             Not(users.email == NULL) &
             Bracket(
                 (users.created_at > '2023-01-01') |
                 (users.status == 'premium')
             )
         ))

class ValueWrapper:
    def __init__(self, value):
        """
        Wrap Python values for SQL usage.
        
        Parameters:
        - value: Python value to wrap
        """
        
class LiteralValue:
    def __init__(self, value):
        """
        Create literal SQL value.
        
        Parameters:
        - value: Literal value
        """

from pypika import Query, Table, ValueWrapper, LiteralValue

users = Table('users')

# Value wrapping for safe SQL generation
python_list = [1, 2, 3, 4, 5]
wrapped_list = ValueWrapper(python_list)

query = Query.from_(users).select('*').where(users.id.isin(wrapped_list))

# Literal values
current_timestamp = LiteralValue('CURRENT_TIMESTAMP')
query = (Query.into(users)
         .columns(users.name, users.email, users.created_at)
         .insert('John Doe', 'john@example.com', current_timestamp))

# Safe parameter handling
def build_user_query(min_age=None, status=None, roles=None):
    query = Query.from_(users).select('*')
    
    if min_age is not None:
        query = query.where(users.age >= ValueWrapper(min_age))
    
    if status is not None:
        query = query.where(users.status == ValueWrapper(status))
    
    if roles is not None:
        query = query.where(users.role.isin(ValueWrapper(roles)))
    
    return query

# Usage
user_query = build_user_query(min_age=18, status='active', roles=['admin', 'user'])