View Operations

/**
 * CREATE VIEW statement for Hive dialect
 * Creates views with Hive-specific properties and syntax support
 */
public class SqlCreateHiveView extends SqlCreateView {
    /**
     * Creates a new Hive view creation statement
     * @param pos Parser position information
     * @param viewName Name of the view to create
     * @param fieldList List of view field names (optional)
     * @param query SELECT query that defines the view
     * @param ifNotExists Whether to use IF NOT EXISTS clause
     * @param comment View comment
     * @param properties View properties (TBLPROPERTIES)
     */
    public SqlCreateHiveView(SqlParserPos pos, SqlIdentifier viewName, SqlNodeList fieldList,
                            SqlNode query, boolean ifNotExists, SqlCharStringLiteral comment,
                            SqlNodeList properties);
}

// Basic view creation
String basicViewSql = """
    CREATE VIEW sales_summary AS
    SELECT 
        year,
        month,
        SUM(amount) as total_sales,
        COUNT(*) as transaction_count
    FROM sales_data
    GROUP BY year, month
    """;

// View with explicit field list
String viewWithFieldsSql = """
    CREATE VIEW IF NOT EXISTS monthly_sales (
        sales_year,
        sales_month, 
        total_amount,
        num_transactions
    ) AS
    SELECT 
        year,
        month,
        SUM(amount),
        COUNT(*)
    FROM sales_data
    GROUP BY year, month
    """;

// View with comment and properties
String viewWithPropertiesSql = """
    CREATE VIEW customer_analytics
    COMMENT 'Customer analytics aggregated view'
    TBLPROPERTIES (
        'owner' = 'analytics_team',
        'refresh_frequency' = 'daily',
        'data_source' = 'customer_data'
    )
    AS
    SELECT 
        customer_id,
        COUNT(*) as total_transactions,
        SUM(amount) as total_spent,
        AVG(amount) as avg_transaction,
        MAX(transaction_date) as last_transaction
    FROM sales_data
    GROUP BY customer_id
    """;

// Programmatic view creation
SqlIdentifier viewName = new SqlIdentifier("product_summary", SqlParserPos.ZERO);

// Define field list
SqlNodeList fieldList = new SqlNodeList(SqlParserPos.ZERO);
fieldList.add(new SqlIdentifier("product_name", SqlParserPos.ZERO));
fieldList.add(new SqlIdentifier("total_sales", SqlParserPos.ZERO));
fieldList.add(new SqlIdentifier("avg_price", SqlParserPos.ZERO));

// Define properties
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);
properties.add(new SqlTableOption("owner", "product_team", SqlParserPos.ZERO));
properties.add(new SqlTableOption("update_frequency", "hourly", SqlParserPos.ZERO));

// Create query (would be parsed separately)
String queryString = """
    SELECT 
        product_name, 
        SUM(amount) as total_sales, 
        AVG(amount) as avg_price 
    FROM sales_data 
    GROUP BY product_name
    """;
SqlNode query = SqlParser.create(queryString).parseQuery();

SqlCreateHiveView createView = new SqlCreateHiveView(
    SqlParserPos.ZERO,
    viewName,
    fieldList,
    query,
    true, // IF NOT EXISTS
    SqlLiteral.createCharString("Product sales summary view", SqlParserPos.ZERO),
    properties
);

/**
 * ALTER VIEW SET TBLPROPERTIES statement  
 * Changes the properties of an existing view
 */
public class SqlAlterHiveViewProperties extends SqlAlterViewProperties {
    /**
     * Creates view properties alteration statement
     * @param pos Parser position information
     * @param tableName Name of view to alter (views use table name parameter)
     * @param propertyList New properties to set
     */
    public SqlAlterHiveViewProperties(SqlParserPos pos, SqlIdentifier tableName, 
                                     SqlNodeList propertyList);
}

// Change view properties
String alterViewPropsSql = """
    ALTER VIEW sales_summary SET TBLPROPERTIES (
        'last_updated' = '2023-12-01',
        'owner' = 'new_analytics_team',
        'retention_period' = '90'
    )
    """;

// Programmatic view properties change
SqlIdentifier viewName = new SqlIdentifier("customer_analytics", SqlParserPos.ZERO);
SqlNodeList newProperties = new SqlNodeList(SqlParserPos.ZERO);

newProperties.add(new SqlTableOption("refresh_frequency", "twice_daily", SqlParserPos.ZERO));
newProperties.add(new SqlTableOption("quality_score", "95", SqlParserPos.ZERO));
newProperties.add(new SqlTableOption("data_lineage", "sales_data,customer_profile", SqlParserPos.ZERO));

SqlAlterHiveViewProperties alterViewProps = new SqlAlterHiveViewProperties(
    SqlParserPos.ZERO,
    viewName,
    newProperties
);

// View with complex query
String complexViewSql = """
    CREATE VIEW customer_segments
    COMMENT 'Customer segmentation based on purchase behavior'
    TBLPROPERTIES (
        'business_logic' = 'customer_segmentation_v2',
        'depends_on' = 'sales_data,customer_profile'
    )
    AS
    SELECT 
        c.customer_id,
        c.customer_name,
        c.registration_date,
        s.total_spent,
        s.transaction_count,
        s.avg_transaction_amount,
        CASE 
            WHEN s.total_spent > 10000 THEN 'Premium'
            WHEN s.total_spent > 5000 THEN 'Gold' 
            WHEN s.total_spent > 1000 THEN 'Silver'
            ELSE 'Bronze'
        END as customer_segment,
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE 
                    WHEN s.total_spent > 10000 THEN 'Premium'
                    WHEN s.total_spent > 5000 THEN 'Gold'
                    WHEN s.total_spent > 1000 THEN 'Silver'
                    ELSE 'Bronze'
                END 
            ORDER BY s.total_spent DESC
        ) as segment_rank
    FROM customer_profile c
    JOIN (
        SELECT 
            customer_id,
            SUM(amount) as total_spent,
            COUNT(*) as transaction_count,
            AVG(amount) as avg_transaction_amount
        FROM sales_data
        WHERE transaction_date >= DATE_SUB(CURRENT_DATE, 365)
        GROUP BY customer_id
    ) s ON c.customer_id = s.customer_id
    """;

// View over partitioned table
String partitionedViewSql = """
    CREATE VIEW recent_sales
    COMMENT 'Sales data for the last 3 months'
    AS
    SELECT 
        id,
        customer_id,
        product_name,
        amount,
        transaction_date,
        year,
        month
    FROM sales_data
    WHERE year = YEAR(CURRENT_DATE)
      AND month >= MONTH(CURRENT_DATE) - 2
    """;

// View with dependency tracking
String dependencyTrackingViewSql = """
    CREATE VIEW sales_kpis
    COMMENT 'Key performance indicators for sales'
    TBLPROPERTIES (
        'depends_on' = 'sales_data,customer_profile,product_catalog',
        'created_by' = 'analytics_pipeline',
        'lineage_level' = '2',
        'refresh_dependencies' = 'sales_data:daily,customer_profile:weekly'
    )
    AS
    SELECT 
        DATE(transaction_date) as sales_date,
        COUNT(DISTINCT customer_id) as unique_customers,
        COUNT(*) as total_transactions,
        SUM(amount) as total_revenue,
        AVG(amount) as avg_transaction_value,
        COUNT(DISTINCT product_name) as unique_products_sold
    FROM sales_data s
    JOIN customer_profile c ON s.customer_id = c.customer_id
    JOIN product_catalog p ON s.product_name = p.product_name
    GROUP BY DATE(transaction_date)
    """;

public class HiveViewManager {
    private TableEnvironment tableEnv;
    private SqlParser parser;
    
    public HiveViewManager(TableEnvironment tableEnv) {
        this.tableEnv = tableEnv;
        this.parser = SqlParser.create("", 
            SqlParser.config().withParserFactory(FlinkHiveSqlParserImpl.FACTORY));
    }
    
    /**
     * Creates a view with standard properties
     */
    public void createStandardView(String viewName, String query, String owner, String description) {
        String createViewSql = String.format("""
            CREATE VIEW IF NOT EXISTS %s
            COMMENT '%s'
            TBLPROPERTIES (
                'owner' = '%s',
                'created_date' = '%s',
                'view_type' = 'analytical'
            )
            AS %s
            """, viewName, description, owner, LocalDate.now().toString(), query);
        
        try {
            tableEnv.executeSql(createViewSql);
            System.out.println("Created view: " + viewName);
        } catch (Exception e) {
            System.err.println("Failed to create view " + viewName + ": " + e.getMessage());
        }
    }
    
    /**
     * Updates view properties for maintenance
     */
    public void updateViewMaintenance(String viewName, String lastRefresh, String dataQuality) {
        String updatePropsSql = String.format("""
            ALTER VIEW %s SET TBLPROPERTIES (
                'last_refresh' = '%s',
                'data_quality_score' = '%s',
                'last_validated' = '%s'
            )
            """, viewName, lastRefresh, dataQuality, LocalDateTime.now().toString());
        
        try {
            tableEnv.executeSql(updatePropsSql);
            System.out.println("Updated maintenance properties for view: " + viewName);
        } catch (Exception e) {
            System.err.println("Failed to update view properties: " + e.getMessage());
        }
    }
    
    /**
     * Creates a materialized view pattern (using table + refresh logic)
     */
    public void createMaterializedViewPattern(String viewName, String tableName, String query) {
        // Create underlying table
        String createTableSql = String.format("""
            CREATE TABLE IF NOT EXISTS %s_materialized
            STORED AS PARQUET
            TBLPROPERTIES (
                'materialized_view' = 'true',
                'source_view' = '%s'
            )
            AS %s
            """, viewName, viewName, query);
        
        // Create view over materialized table
        String createViewSql = String.format("""
            CREATE VIEW IF NOT EXISTS %s
            COMMENT 'Materialized view backed by %s_materialized table'
            TBLPROPERTIES (
                'view_type' = 'materialized',
                'backing_table' = '%s_materialized'
            )
            AS SELECT * FROM %s_materialized
            """, viewName, viewName, viewName, viewName);
        
        try {
            tableEnv.executeSql(createTableSql);
            tableEnv.executeSql(createViewSql);
            System.out.println("Created materialized view pattern: " + viewName);
        } catch (Exception e) {
            System.err.println("Failed to create materialized view: " + e.getMessage());
        }
    }
}

// Usage
HiveViewManager viewManager = new HiveViewManager(tableEnv);

// Create standard analytical view
String salesAnalysisQuery = """
    SELECT 
        year, month,
        SUM(amount) as total_sales,
        COUNT(*) as transaction_count,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM sales_data 
    GROUP BY year, month
    """;

viewManager.createStandardView(
    "monthly_sales_analysis", 
    salesAnalysisQuery, 
    "analytics_team",
    "Monthly sales aggregation for reporting"
);

// Update view maintenance properties
viewManager.updateViewMaintenance("monthly_sales_analysis", "2023-12-01", "98.5");

// Create materialized view for performance
String expensiveQuery = """
    SELECT 
        customer_id,
        COUNT(*) as lifetime_transactions,
        SUM(amount) as lifetime_value,
        MIN(transaction_date) as first_purchase,
        MAX(transaction_date) as last_purchase,
        DATEDIFF(MAX(transaction_date), MIN(transaction_date)) as customer_lifespan_days
    FROM sales_data
    GROUP BY customer_id
    """;

viewManager.createMaterializedViewPattern("customer_lifetime_value", "clv_table", expensiveQuery);

// Security view with row-level filtering
String securityViewSql = """
    CREATE VIEW secure_sales_data
    COMMENT 'Sales data with row-level security'
    TBLPROPERTIES (
        'security_enabled' = 'true',
        'access_control' = 'row_level'
    )
    AS
    SELECT 
        id,
        customer_id,
        product_name,
        amount,
        transaction_date,
        year,
        month
    FROM sales_data
    WHERE year >= YEAR(CURRENT_DATE) - 1  -- Only recent data
      AND amount < 10000  -- Hide large transactions
    """;