package com.cube.examples;

import com.cube.sql.SQLParser;
import com.cube.sql.SQLExecutor;
import com.cube.cql.QueryExecutor;
import com.cube.storage.LSMStorageEngine;

import java.io.IOException;
import java.util.*;

/**
 * SQL Examples - Demonstrates all SQL features
 */
public class SQLExamples {
    
    public static void main(String[] args) throws IOException {
        System.out.println("═══════════════════════════════════════════════════════════");
        System.out.println("  Cube Database - SQL Examples");
        System.out.println("═══════════════════════════════════════════════════════════");
        System.out.println();
        
        // Initialize storage and executor
        LSMStorageEngine storage = new LSMStorageEngine("/tmp/cube-sql-examples");
        QueryExecutor queryExecutor = new QueryExecutor(storage);
        SQLExecutor sqlExecutor = new SQLExecutor(queryExecutor, storage);
        
        try {
            example1_BasicCRUD(sqlExecutor);
            example2_UserManagement(sqlExecutor);
            example3_ProductCatalog(sqlExecutor);
            example4_AdvancedQueries(sqlExecutor);
            example5_BatchOperations(sqlExecutor);
            
            System.out.println("\n═══════════════════════════════════════════════════════════");
            System.out.println("  All SQL examples completed successfully! ✓");
            System.out.println("═══════════════════════════════════════════════════════════\n");
            
        } finally {
            storage.close();
        }
    }
    
    /**
     * Example 1: Basic CRUD Operations
     */
    private static void example1_BasicCRUD(SQLExecutor executor) {
        System.out.println("Example 1: Basic CRUD Operations");
        System.out.println("─────────────────────────────────────────────────────────────");
        
        // CREATE TABLE
        System.out.println("\n1. CREATE TABLE");
        String createSQL = "CREATE TABLE demo.users (id TEXT PRIMARY KEY, name TEXT, email TEXT)";
        System.out.println("SQL: " + createSQL);
        SQLExecutor.SQLResult result = executor.execute(createSQL);
        System.out.println("✓ " + result.getMessage());
        
        // INSERT
        System.out.println("\n2. INSERT");
        String insertSQL = "INSERT INTO demo.users (id, name, email) VALUES ('u1', 'Alice', 'alice@example.com')";
        System.out.println("SQL: " + insertSQL);
        result = executor.execute(insertSQL);
        System.out.println("✓ " + result.getMessage());
        
        // SELECT
        System.out.println("\n3. SELECT");
        String selectSQL = "SELECT * FROM demo.users WHERE id = 'u1'";
        System.out.println("SQL: " + selectSQL);
        result = executor.execute(selectSQL);
        if (result.isSuccess() && !result.getRows().isEmpty()) {
            System.out.println("✓ Found: " + result.getRows().get(0));
        }
        
        // UPDATE
        System.out.println("\n4. UPDATE");
        String updateSQL = "UPDATE demo.users SET name = 'Alice Johnson' WHERE id = 'u1'";
        System.out.println("SQL: " + updateSQL);
        result = executor.execute(updateSQL);
        System.out.println("✓ " + result.getMessage());
        
        // DELETE
        System.out.println("\n5. DELETE");
        String deleteSQL = "DELETE FROM demo.users WHERE id = 'u1'";
        System.out.println("SQL: " + deleteSQL);
        result = executor.execute(deleteSQL);
        System.out.println("✓ " + result.getMessage());
        
        System.out.println();
    }
    
    /**
     * Example 2: User Management System
     */
    private static void example2_UserManagement(SQLExecutor executor) {
        System.out.println("Example 2: User Management System");
        System.out.println("─────────────────────────────────────────────────────────────");
        
        // Create users table
        executor.execute("CREATE TABLE app.users (id TEXT PRIMARY KEY, username TEXT, email TEXT, status TEXT, created_at TEXT)");
        
        // Add users
        System.out.println("\nAdding users...");
        executor.execute("INSERT INTO app.users (id, username, email, status, created_at) VALUES ('u001', 'alice', 'alice@example.com', 'active', '2024-01-15')");
        executor.execute("INSERT INTO app.users (id, username, email, status, created_at) VALUES ('u002', 'bob', 'bob@example.com', 'active', '2024-01-16')");
        executor.execute("INSERT INTO app.users (id, username, email, status, created_at) VALUES ('u003', 'charlie', 'charlie@example.com', 'inactive', '2024-01-17')");
        System.out.println("✓ 3 users added");
        
        // Query user
        System.out.println("\nQuerying user u001...");
        SQLExecutor.SQLResult result = executor.execute("SELECT * FROM app.users WHERE id = 'u001'");
        if (!result.getRows().isEmpty()) {
            Map<String, String> user = result.getRows().get(0);
            System.out.println("  Username: " + user.get("username"));
            System.out.println("  Email: " + user.get("email"));
            System.out.println("  Status: " + user.get("status"));
        }
        
        // Update user status
        System.out.println("\nUpdating user status...");
        executor.execute("UPDATE app.users SET status = 'suspended' WHERE id = 'u002'");
        System.out.println("✓ User u002 suspended");
        
        System.out.println();
    }
    
    /**
     * Example 3: Product Catalog
     */
    private static void example3_ProductCatalog(SQLExecutor executor) {
        System.out.println("Example 3: Product Catalog");
        System.out.println("─────────────────────────────────────────────────────────────");
        
        // Create products table
        executor.execute("CREATE TABLE shop.products (sku TEXT PRIMARY KEY, name TEXT, price TEXT, category TEXT, stock TEXT)");
        
        // Add products
        System.out.println("\nAdding products...");
        executor.execute("INSERT INTO shop.products (sku, name, price, category, stock) VALUES ('LAPTOP-001', 'MacBook Pro 16', '2499.99', 'Electronics', '10')");
        executor.execute("INSERT INTO shop.products (sku, name, price, category, stock) VALUES ('MOUSE-001', 'Wireless Mouse', '29.99', 'Accessories', '100')");
        executor.execute("INSERT INTO shop.products (sku, name, price, category, stock) VALUES ('KEYBOARD-001', 'Mechanical Keyboard', '149.99', 'Accessories', '50')");
        System.out.println("✓ 3 products added");
        
        // Query product
        System.out.println("\nQuerying laptop...");
        SQLExecutor.SQLResult result = executor.execute("SELECT * FROM shop.products WHERE sku = 'LAPTOP-001'");
        if (!result.getRows().isEmpty()) {
            Map<String, String> product = result.getRows().get(0);
            System.out.println("  Product: " + product.get("name"));
            System.out.println("  Price: $" + product.get("price"));
            System.out.println("  Stock: " + product.get("stock") + " units");
        }
        
        // Update price
        System.out.println("\nApplying discount...");
        executor.execute("UPDATE shop.products SET price = '2299.99' WHERE sku = 'LAPTOP-001'");
        System.out.println("✓ Price updated");
        
        // Update stock
        System.out.println("\nUpdating stock after sale...");
        executor.execute("UPDATE shop.products SET stock = '9' WHERE sku = 'LAPTOP-001'");
        System.out.println("✓ Stock updated");
        
        System.out.println();
    }
    
    /**
     * Example 4: Advanced Queries
     */
    private static void example4_AdvancedQueries(SQLExecutor executor) {
        System.out.println("Example 4: Advanced Queries");
        System.out.println("─────────────────────────────────────────────────────────────");
        
        // Create table
        executor.execute("CREATE TABLE data.records (id TEXT PRIMARY KEY, type TEXT, value TEXT, status TEXT)");
        
        // Insert test data
        executor.execute("INSERT INTO data.records (id, type, value, status) VALUES ('r1', 'A', '100', 'active')");
        executor.execute("INSERT INTO data.records (id, type, value, status) VALUES ('r2', 'B', '200', 'active')");
        executor.execute("INSERT INTO data.records (id, type, value, status) VALUES ('r3', 'A', '300', 'inactive')");
        
        // Select specific columns
        System.out.println("\nSelecting specific columns...");
        SQLExecutor.SQLResult result = executor.execute("SELECT id, type, value FROM data.records WHERE id = 'r1'");
        if (!result.getRows().isEmpty()) {
            Map<String, String> row = result.getRows().get(0);
            System.out.println("  ID: " + row.get("id"));
            System.out.println("  Type: " + row.get("type"));
            System.out.println("  Value: " + row.get("value"));
        }
        
        // Update multiple columns
        System.out.println("\nUpdating multiple columns...");
        executor.execute("UPDATE data.records SET type = 'C', value = '150', status = 'pending' WHERE id = 'r1'");
        System.out.println("✓ Multiple columns updated");
        
        // Verify
        result = executor.execute("SELECT * FROM data.records WHERE id = 'r1'");
        if (!result.getRows().isEmpty()) {
            System.out.println("  New values: " + result.getRows().get(0));
        }
        
        System.out.println();
    }
    
    /**
     * Example 5: Batch Operations
     */
    private static void example5_BatchOperations(SQLExecutor executor) {
        System.out.println("Example 5: Batch Operations");
        System.out.println("─────────────────────────────────────────────────────────────");
        
        System.out.println("\nExecuting multiple operations...");
        
        // Create table
        executor.execute("CREATE TABLE batch.data (id TEXT PRIMARY KEY, value TEXT)");
        
        // Batch inserts
        int count = 0;
        for (int i = 1; i <= 5; i++) {
            String sql = String.format("INSERT INTO batch.data (id, value) VALUES ('item-%d', 'value-%d')", i, i * 100);
            SQLExecutor.SQLResult result = executor.execute(sql);
            if (result.isSuccess()) {
                count++;
            }
        }
        System.out.println("✓ Inserted " + count + " records");
        
        // Batch updates
        count = 0;
        for (int i = 1; i <= 3; i++) {
            String sql = String.format("UPDATE batch.data SET value = 'updated-%d' WHERE id = 'item-%d'", i, i);
            SQLExecutor.SQLResult result = executor.execute(sql);
            if (result.isSuccess()) {
                count++;
            }
        }
        System.out.println("✓ Updated " + count + " records");
        
        // Query results
        System.out.println("\nFinal data:");
        for (int i = 1; i <= 5; i++) {
            SQLExecutor.SQLResult result = executor.execute("SELECT * FROM batch.data WHERE id = 'item-" + i + "'");
            if (!result.getRows().isEmpty()) {
                Map<String, String> row = result.getRows().get(0);
                System.out.println("  " + row.get("id") + " = " + row.get("value"));
            }
        }
        
        System.out.println();
    }
}