SQL Tables and Querying

class Table(Model, Queryable, AuditMixin):
    """
    SQL table model for data visualization and querying.
    
    Attributes:
        id (int): Primary key
        table_name (str): Name of the database table
        main_datetime_column_id (int): Foreign key to main datetime column
        main_datetime_column (TableColumn): Primary date/time column for time-based queries
        default_endpoint (str): Default visualization endpoint
        database_id (int): Foreign key to Database
        database (Database): Reference to parent database
    """
    
    def query(self, groupby, metrics, granularity, from_dttm, to_dttm,
             limit_spec=None, filter=None, is_timeseries=True,
             timeseries_limit=15, row_limit=None):
        """
        Execute SQL query with aggregations and filters.
        
        Args:
            groupby (list): List of columns to group by
            metrics (list): List of metrics to calculate  
            granularity (str): Time granularity for grouping
            from_dttm (datetime): Start datetime for time-based queries
            to_dttm (datetime): End datetime for time-based queries
            limit_spec (dict, optional): Limit specification
            filter (list, optional): List of filter conditions
            is_timeseries (bool): Whether query is time-based (default True)
            timeseries_limit (int): Limit for timeseries results (default 15)
            row_limit (int, optional): Maximum number of rows to return
            
        Returns:
            QueryResult: Named tuple with df, query, and duration
        """
    
    def fetch_metadata(self):
        """
        Synchronize table structure from the database.
        
        Discovers columns, data types, and other metadata
        from the actual database table structure.
        """
    
    @property
    def name(self):
        """Get the table name."""
        return self.table_name
    
    @property
    def table_link(self):
        """Get HTML link to the table view."""
        url = "/panoramix/table/{}/".format(self.id)
        return '<a href="{url}">{self.table_name}</a>'.format(**locals())
    
    @property
    def metrics_combo(self):
        """Get list of metric name/verbose name tuples for forms."""
        return sorted([
            (m.metric_name, m.verbose_name)
            for m in self.metrics
        ], key=lambda x: x[1])
    
    def __repr__(self):
        """String representation of the table."""
        return self.table_name

class SqlMetric(Model, AuditMixin):
    """
    SQL-based metric definition for tables.
    
    Attributes:
        id (int): Primary key
        metric_name (str): Unique metric identifier
        verbose_name (str): Human-readable metric name
        metric_type (str): Type of metric ('count', 'sum', 'avg', etc.)
        expression (str): SQL expression for the metric
        description (str): Metric description
        table_id (int): Foreign key to Table
        table (Table): Reference to parent table
    """

class TableColumn(Model, AuditMixin):
    """
    SQL table column metadata.
    
    Attributes:
        id (int): Primary key
        column_name (str): Column name in the database
        is_dttm (bool): Whether column contains datetime data
        is_active (bool): Whether column is active for queries
        type (str): Column data type
        groupby (bool): Whether column can be used for grouping
        filterable (bool): Whether column can be filtered
        description (str): Column description
        table_id (int): Foreign key to Table
        table (Table): Reference to parent table
    """
    
    def __repr__(self):
        """String representation of the table column."""
        return self.column_name

from panoramix.models import Database, Table

# Set up database and table
db = Database.query.filter_by(database_name='sales').first()
table = Table.query.filter_by(table_name='orders', database=db).first()

# Simple aggregation query
result = table.query(
    groupby=['product_category'],
    metrics=['sum__revenue', 'count'],
    row_limit=10
)

print(result.df)
print(f"Query took {result.duration} seconds")

# Time series query with granularity
result = table.query(
    groupby=['product_category'],
    metrics=['sum__revenue'],
    granularity='day',
    since='7 days ago',
    until='now',
    main_datetime_column='order_date'
)

# Access results as pandas DataFrame
daily_sales = result.df

# Query with WHERE and HAVING clauses
result = table.query(
    groupby=['region', 'product_category'],
    metrics=['sum__revenue', 'avg__order_value'],
    where="order_status = 'completed'",
    having="sum__revenue > 10000",
    order_desc=True,
    limit_metric='sum__revenue',
    row_limit=20
)

# Sync table structure from database
table.fetch_metadata()

# Access column information
for column in table.columns:
    print(f"Column: {column.column_name}, Type: {column.type}")
    
# Access available metrics
for metric in table.metrics:
    print(f"Metric: {metric.metric_name}, Expression: {metric.expression}")

class Table:
    @property
    def table_link(self):
        """HTML link to table visualization view"""
    
    @property
    def metrics_combo(self):
        """List of available metrics as form choices"""
    
    @property
    def column_names(self):
        """List of all column names"""
        
    @property
    def groupby_column_names(self):
        """List of columns available for grouping"""
        
    @property
    def filterable_column_names(self):
        """List of columns available for filtering"""