-- ☒ Regex can't handle nested structures
CREATE TABLE complex (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    data jsonb DEFAULT '{"nested": {"deep": {"value": true}}}'::jsonb,
    constraint check_complex CHECK (
        (data->>'type')::text IN ('user', 'admin') AND
        (data->'permissions'->'level')::int >= 1
    )
);

-- ☒ Regex breaks on complex string literals
CREATE TABLE messages (
    content text DEFAULT 'Hello, this contains "quotes" and ''apostrophes'' and \n newlines'
);

-- ☒ Regex can't understand context-dependent keywords
CREATE TABLE user_roles (
    role text CHECK (role IN ('user', 'admin', 'read-only')) -- 'read-only' isn't a keyword
);

-- Traditional tools see: CREATE TABLE, ALTER TABLE, DROP TABLE
-- CAPYSQUASH sees: Object lifecycle, dependencies, constraints

-- Phase 1: Initial creation
CREATE TABLE users (id uuid, email text);

-- Phase 2: Evolution
ALTER TABLE users ADD COLUMN created_at timestamptz;
ALTER TABLE users ADD COLUMN updated_at timestamptz;
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Phase 3: Optimization
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

// This is what PostgreSQL itself uses to parse SQL
// CAPYSQUASH uses the exact same technology

type RawStmt struct {
   Stmt   Node
   StmtLocation int
   StmtLen      int
}

type CreateStmt struct {
   Relation        *RangeVar
   TableElts       []Node
   InhRelations    []Node
   Partbound       Node
   Opts            []Node
   Oncommit        OnCommitAction
   Options         []Node
}

-- Input SQL
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL UNIQUE,
    created_at timestamptz DEFAULT now()
);

-- AST Structure (simplified)
CreateStmt {
    Relation: RangeVar { relname: "users" },
    TableElts: [
        ColumnDef {
            colname: "id",
            typeName: SystemTypeName { names: ["uuid"] },
            constraints: [
                Constraint { contype: CONSTR_PRIMARY },
                Constraint {
                    contype: CONSTR_DEFAULT,
                    raw_expr: FuncCall { funcname: ["gen_random_uuid"] }
                }
            ]
        },
        ColumnDef {
            colname: "email",
            typeName: SystemTypeName { names: ["text"] },
            constraints: [
                Constraint { contype: CONSTR_NOTNULL },
                Constraint { contype: CONSTR_UNIQUE }
            ]
        },
        ColumnDef {
            colname: "created_at",
            typeName: SystemTypeName { names: ["timestamptz"] },
            constraints: [
                Constraint {
                    contype: CONSTR_DEFAULT,
                    raw_expr: FuncCall { funcname: ["now"] }
                }
            ]
        }
    ]
}

-- Arrays with complex elements
CREATE TABLE posts (
    tags text[] DEFAULT ARRAY['postgresql', 'optimization'],
    metadata jsonb DEFAULT '{"views": 0, "likes": []}'::jsonb,
    ranges numrange[] DEFAULT '{[1,10), [20,30)}'
);

-- Custom types and domains
CREATE DOMAIN email_address AS text
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE users (
    contact_info email_address,
    preferences user_preferences_type
);

-- Enum types
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');
CREATE TABLE users (status user_status DEFAULT 'active');

-- Multi-column constraints
CREATE TABLE orders (
    user_id uuid,
    product_id uuid,
    quantity int,
    PRIMARY KEY (user_id, product_id),
    CONSTRAINT valid_quantity CHECK (quantity > 0 AND quantity <= 1000),
    CONSTRAINT user_product_unique UNIQUE (user_id, product_id)
);

-- Foreign key constraints with actions
CREATE TABLE order_items (
    order_id uuid REFERENCES orders(id) ON DELETE CASCADE,
    product_id uuid REFERENCES products(id) ON DELETE RESTRICT,
    quantity int NOT NULL CHECK (quantity > 0)
);

-- Complex check constraints
CREATE TABLE employees (
    salary numeric,
    department text,
    CONSTRAINT salary_check CHECK (
        CASE
            WHEN department = 'engineering' THEN salary >= 80000
            WHEN department = 'sales' THEN salary >= 60000
            ELSE salary >= 40000
        END
    )
);

-- PL/pgSQL functions with complex logic
CREATE OR REPLACE FUNCTION calculate_user_metrics(user_id_param uuid)
RETURNS TABLE (
    total_orders bigint,
    total_spent numeric,
    favorite_category text
) AS $$
DECLARE
    user_orders RECORD;
    category_counts RECORD;
BEGIN
    -- Complex query with CTEs and window functions
    WITH user_order_data AS (
        SELECT
            o.id,
            o.total_amount,
            p.category,
            ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY o.created_at DESC) as rn
        FROM orders o
        JOIN products p ON o.product_id = p.id
        WHERE o.user_id = user_id_param
    ),
    category_stats AS (
        SELECT
            category,
            COUNT(*) as order_count,
            SUM(total_amount) as category_total
        FROM user_order_data
        GROUP BY category
    )
    SELECT
        COUNT(*) as total_orders,
        COALESCE(SUM(total_amount), 0) as total_spent,
        (SELECT category FROM category_stats ORDER BY order_count DESC LIMIT 1) as favorite_category
    INTO total_orders, total_spent, favorite_category
    FROM user_order_data;

    RETURN NEXT;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- These migrations have complex dependencies
-- Migration 1: Base types
CREATE TYPE user_status AS ENUM ('active', 'inactive');

-- Migration 2: User table with type dependency
CREATE TABLE users (
    id uuid PRIMARY KEY,
    status user_status DEFAULT 'active'
);

-- Migration 3: Function with table dependency
CREATE FUNCTION get_active_users()
RETURNS TABLE (id uuid, status user_status) AS $$
    SELECT id, status FROM users WHERE status = 'active';
$$ LANGUAGE sql;

-- Migration 4: View with function dependency
CREATE VIEW active_user_summary AS
    SELECT COUNT(*) as active_count
    FROM get_active_users();

-- Migration 5: Index with view dependency (PostgreSQL 14+)
CREATE INDEX idx_active_user_count ON users ((SELECT COUNT(*) FROM active_user_summary));

user_status (type)
    ↓
users (table)
    ↓
get_active_users (function)
    ↓
active_user_summary (view)
    ↓
idx_active_user_count (index)

-- Circular dependency example
CREATE TABLE users (
    id uuid PRIMARY KEY,
    manager_id uuid REFERENCES users(id) -- Self-reference
);

CREATE TABLE departments (
    id uuid PRIMARY KEY,
    head_user_id uuid REFERENCES users(id)
);

-- Function that references both tables
CREATE FUNCTION get_department_head(dept_id uuid)
RETURNS uuid AS $$
    SELECT head_user_id FROM departments WHERE id = dept_id;
$$ LANGUAGE sql;

-- CAPYSQUASH detects the cycle and handles it correctly

-- Before: Multiple migrations
-- Migration 1
CREATE TABLE users (id uuid, email text);

-- Migration 2
ALTER TABLE users ADD COLUMN created_at timestamptz DEFAULT now();

-- Migration 3
ALTER TABLE users ADD COLUMN updated_at timestamptz DEFAULT now();

-- Migration 4
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- Migration 5
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- After: Semantically equivalent single migration
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    email text NOT NULL UNIQUE,
    created_at timestamptz DEFAULT now(),
    updated_at timestamptz DEFAULT now()
);

-- Complex constraints preserved exactly
CREATE TABLE products (
    id uuid PRIMARY KEY,
    name text NOT NULL,
    price numeric CHECK (price > 0),
    discount_price numeric CHECK (discount_price >= 0 AND discount_price < price),
    CONSTRAINT valid_pricing CHECK (
        (discount_price IS NULL) OR (discount_price < price)
    ),
    CONSTRAINT name_length CHECK (length(name) >= 2)
);

-- Indexes with expressions preserved
CREATE INDEX idx_products_search ON products (
    lower(name),
    CASE WHEN discount_price IS NOT NULL THEN discount_price ELSE price END
);

-- Partial indexes preserved
CREATE INDEX idx_active_products ON products (id) WHERE price > 0 AND name IS NOT NULL;

-- Pattern: Repeated ALTER TABLE operations
-- Before:
ALTER TABLE users ADD COLUMN first_name text;
ALTER TABLE users ADD COLUMN last_name text;
ALTER TABLE users ADD COLUMN email text;
ALTER TABLE users ADD COLUMN phone text;

-- After: Optimized single CREATE TABLE
CREATE TABLE users (
    id uuid PRIMARY KEY,
    first_name text,
    last_name text,
    email text,
    phone text
);

-- Before: Unused objects
CREATE TABLE temp_users (id uuid, email text); -- Never referenced
CREATE INDEX idx_temp_users_email ON temp_users(email); -- Orphaned index
CREATE FUNCTION unused_function() RETURNS int AS $$ SELECT 1; $$ LANGUAGE sql; -- Never called

-- After: Clean, optimized schema
-- Unused objects safely removed

-- PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE TABLE locations (
    id uuid PRIMARY KEY,
    point geometry(Point, 4326),
    area geometry(Polygon, 4326)
);
CREATE INDEX idx_locations_point ON locations USING GIST (point);

-- TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;
CREATE TABLE metrics (
    time timestamptz NOT NULL,
    value double precision,
    device_id text
);
SELECT create_hypertable('metrics', 'time');

-- UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE users (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

-- PostgreSQL 12+ generated columns
CREATE TABLE users (
    id uuid PRIMARY KEY,
    first_name text,
    last_name text,
    full_name text GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED
);

-- PostgreSQL 14+ multirange types
CREATE TABLE bookings (
    id uuid PRIMARY KEY,
    stay_dates daterange DEFAULT daterange(current_date, current_date + 7)
);

-- PostgreSQL 15+ security labels
CREATE TABLE sensitive_data (
    id uuid PRIMARY KEY,
    data text,
    SECURITY LABEL FOR 'row_level' ON sensitive_data IS 'classified';
);

-- Complex expressions in defaults
CREATE TABLE orders (
    id uuid PRIMARY KEY,
    total_amount numeric,
    tax_amount numeric GENERATED ALWAYS AS (total_amount * 0.08) STORED,
    final_amount numeric GENERATED ALWAYS AS (total_amount + (total_amount * 0.08)) STORED
);

-- Subqueries in check constraints
CREATE TABLE employees (
    id uuid PRIMARY KEY,
    salary numeric,
    department_id uuid,
    CONSTRAINT salary_within_range CHECK (
        salary BETWEEN (
            SELECT min_salary
            FROM departments
            WHERE id = department_id
        ) AND (
            SELECT max_salary
            FROM departments
            WHERE id = department_id
        )
    )
);