-- Customer service representative view
CREATE VIEW customer_service_view AS
SELECT 
    c.customer_id,
    c.first_name,
    c.last_name,
    c.email,
    c.phone,
    c.registration_date,
    COUNT(o.order_id) as total_orders,
    SUM(o.total_amount) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.status = 'active'
GROUP BY c.customer_id;

-- Analytics team view with aggregated data
CREATE VIEW sales_analytics_view AS
SELECT 
    DATE_TRUNC('month', o.order_date) as month,
    p.category,
    COUNT(*) as order_count,
    SUM(oi.quantity * oi.price) as revenue,
    AVG(oi.quantity * oi.price) as avg_order_value
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'completed'
GROUP BY DATE_TRUNC('month', o.order_date), p.category;

-- Create specific roles for different user types
CREATE ROLE customer_service;
CREATE ROLE analytics_team;
CREATE ROLE admin_users;

-- Grant appropriate permissions
GRANT SELECT ON customer_service_view TO customer_service;
GRANT SELECT ON sales_analytics_view TO analytics_team;
GRANT ALL PRIVILEGES ON ALL TABLES TO admin_users;

-- Row-level security for sensitive data
CREATE POLICY customer_data_access ON customers
    FOR SELECT TO customer_service
    USING (region = current_setting('app.user_region'));

-- Customer management
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    date_of_birth DATE,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Product catalog with hierarchical categories
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    parent_category_id INTEGER REFERENCES categories(category_id),
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT true,
    sort_order INTEGER DEFAULT 0
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
    sku VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    cost DECIMAL(10,2) CHECK (cost >= 0),
    weight DECIMAL(8,3),
    dimensions JSONB, -- {"length": 10, "width": 5, "height": 3}
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Inventory management
CREATE TABLE inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    warehouse_location VARCHAR(100),
    quantity_available INTEGER NOT NULL DEFAULT 0 CHECK (quantity_available >= 0),
    quantity_reserved INTEGER NOT NULL DEFAULT 0 CHECK (quantity_reserved >= 0),
    reorder_level INTEGER DEFAULT 10,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order processing
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' 
        CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled')),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
    shipping_cost DECIMAL(8,2) DEFAULT 0,
    tax_amount DECIMAL(8,2) DEFAULT 0,
    discount_amount DECIMAL(8,2) DEFAULT 0,
    shipping_address JSONB NOT NULL,
    billing_address JSONB NOT NULL,
    payment_method VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    discount_amount DECIMAL(8,2) DEFAULT 0,
    total_price DECIMAL(10,2) GENERATED ALWAYS AS 
        ((quantity * unit_price) - discount_amount) STORED
);

-- Primary performance indexes
CREATE INDEX idx_customers_email ON customers USING HASH (email);
CREATE INDEX idx_customers_registration_date ON customers (registration_date);
CREATE INDEX idx_customers_status_region ON customers (status, region) WHERE status = 'active';

-- Composite indexes for common query patterns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
CREATE INDEX idx_orders_status_date ON orders (status, order_date) WHERE status IN ('pending', 'processing');

-- Partial indexes for specific use cases
CREATE INDEX idx_products_active_category ON products (category_id, price) 
    WHERE is_active = true;

-- Full-text search indexes
CREATE INDEX idx_products_search ON products 
    USING GIN (to_tsvector('english', name || ' ' || COALESCE(description, '')));

-- JSONB indexes for semi-structured data
CREATE INDEX idx_orders_shipping_city ON orders 
    USING GIN ((shipping_address->'city'));

-- Time-based partitioning for orders
CREATE TABLE orders_partitioned (
    LIKE orders INCLUDING ALL
) PARTITION BY RANGE (order_date);

-- Create monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders_partitioned
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Hash partitioning for high-volume transaction data
CREATE TABLE user_activities_partitioned (
    activity_id BIGSERIAL,
    user_id INTEGER NOT NULL,
    activity_type VARCHAR(50),
    activity_data JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY HASH (user_id);

-- Create hash partitions
CREATE TABLE user_activities_p0 PARTITION OF user_activities_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_activities_p1 PARTITION OF user_activities_partitioned
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

-- Normalized core tables for transactional integrity
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    title VARCHAR(255) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE likes (
    like_id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(user_id),
    post_id INTEGER NOT NULL REFERENCES posts(post_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, post_id)
);

-- Denormalized summary table for performance
CREATE TABLE post_statistics (
    post_id INTEGER PRIMARY KEY REFERENCES posts(post_id),
    author_username VARCHAR(50), -- Denormalized from users table
    like_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    view_count INTEGER DEFAULT 0,
    last_activity TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Trigger to maintain denormalized data
CREATE OR REPLACE FUNCTION update_post_statistics()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE post_statistics 
        SET like_count = like_count + 1,
            last_activity = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP
        WHERE post_id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE post_statistics 
        SET like_count = like_count - 1,
            last_activity = CURRENT_TIMESTAMP,
            updated_at = CURRENT_TIMESTAMP
        WHERE post_id = OLD.post_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_post_statistics
    AFTER INSERT OR DELETE ON likes
    FOR EACH ROW EXECUTE FUNCTION update_post_statistics();

-- Type 2 SCD: Historical tracking with valid time periods
CREATE TABLE customer_history (
    history_id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    address JSONB,
    phone VARCHAR(20),
    valid_from TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    valid_to TIMESTAMP DEFAULT '9999-12-31 23:59:59',
    is_current BOOLEAN DEFAULT true,
    created_by VARCHAR(100),
    change_reason VARCHAR(255)
);

-- Ensure only one current record per customer
CREATE UNIQUE INDEX idx_customer_current 
    ON customer_history (customer_id) 
    WHERE is_current = true;

-- Function to handle customer updates with history
CREATE OR REPLACE FUNCTION update_customer_with_history(
    p_customer_id INTEGER,
    p_email VARCHAR(255),
    p_first_name VARCHAR(100),
    p_last_name VARCHAR(100),
    p_address JSONB,
    p_phone VARCHAR(20),
    p_change_reason VARCHAR(255) DEFAULT 'Data update'
) RETURNS VOID AS $$
BEGIN
    -- Close current record
    UPDATE customer_history 
    SET valid_to = CURRENT_TIMESTAMP,
        is_current = false
    WHERE customer_id = p_customer_id AND is_current = true;
    
    -- Insert new current record
    INSERT INTO customer_history (
        customer_id, email, first_name, last_name, 
        address, phone, change_reason
    ) VALUES (
        p_customer_id, p_email, p_first_name, p_last_name,
        p_address, p_phone, p_change_reason
    );
END;
$$ LANGUAGE plpgsql;

-- Event store for capturing all state changes
CREATE TABLE event_store (
    event_id BIGSERIAL PRIMARY KEY,
    aggregate_id UUID NOT NULL,
    aggregate_type VARCHAR(100) NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_version INTEGER NOT NULL,
    event_data JSONB NOT NULL,
    metadata JSONB,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sequence_number BIGINT
);

-- Optimistic concurrency control
CREATE UNIQUE INDEX idx_event_store_aggregate_version 
    ON event_store (aggregate_id, event_version);

-- Event projection for current state
CREATE TABLE customer_projection (
    customer_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    status VARCHAR(20),
    last_event_version INTEGER,
    projected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Snapshot table for performance optimization
CREATE TABLE customer_snapshots (
    snapshot_id BIGSERIAL PRIMARY KEY,
    customer_id UUID NOT NULL,
    snapshot_data JSONB NOT NULL,
    event_version INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create shadow table with new structure
CREATE TABLE users_new (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL, -- New column
    profile_data JSONB, -- New JSONB column
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Copy data in batches to avoid locks
DO $$
DECLARE
    batch_size INTEGER := 10000;
    offset_val INTEGER := 0;
    row_count INTEGER;
BEGIN
    LOOP
        INSERT INTO users_new (user_id, email, username, full_name, created_at)
        SELECT 
            user_id, 
            email, 
            username,
            TRIM(CONCAT(first_name, ' ', last_name)) as full_name,
            created_at
        FROM users
        ORDER BY user_id
        LIMIT batch_size OFFSET offset_val;
        
        GET DIAGNOSTICS row_count = ROW_COUNT;
        EXIT WHEN row_count = 0;
        
        offset_val := offset_val + batch_size;
        COMMIT;
        
        -- Pause to reduce system load
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

-- Create triggers to keep shadow table in sync during migration
CREATE OR REPLACE FUNCTION sync_users_new()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO users_new (user_id, email, username, full_name, created_at)
        VALUES (NEW.user_id, NEW.email, NEW.username, 
                TRIM(CONCAT(NEW.first_name, ' ', NEW.last_name)), NEW.created_at);
    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE users_new 
        SET email = NEW.email,
            username = NEW.username,
            full_name = TRIM(CONCAT(NEW.first_name, ' ', NEW.last_name)),
            updated_at = CURRENT_TIMESTAMP
        WHERE user_id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        DELETE FROM users_new WHERE user_id = OLD.user_id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_sync_users_new
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION sync_users_new();

-- Query pattern analysis
EXPLAIN (ANALYZE, BUFFERS) 
SELECT user_id, email, last_login 
FROM users 
WHERE status = 'active' 
  AND created_at >= '2024-01-01'
ORDER BY last_login DESC
LIMIT 100;

-- Covering index to eliminate table lookups
CREATE INDEX idx_users_covering_active_recent 
ON users (status, created_at, last_login DESC) 
INCLUDE (user_id, email)
WHERE status = 'active';

-- Conditional indexes for specific query patterns
CREATE INDEX idx_orders_high_value_recent 
ON orders (created_at DESC, total_amount) 
WHERE total_amount > 1000 AND status = 'completed';

-- Bad: Separate single-column indexes
-- CREATE INDEX idx_orders_customer ON orders(customer_id);
-- CREATE INDEX idx_orders_date ON orders(order_date);
-- CREATE INDEX idx_orders_status ON orders(status);

-- Good: Composite index matching query patterns
CREATE INDEX idx_orders_customer_date_status 
ON orders (customer_id, order_date DESC, status);

-- Query that can use the composite index efficiently
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01'
  AND status IN ('completed', 'shipped')
ORDER BY order_date DESC;

-- Window functions for analytics without self-joins
SELECT 
    customer_id,
    order_date,
    total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as order_rank,
    SUM(total_amount) OVER (PARTITION BY customer_id) as customer_total,
    LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) as previous_order
FROM orders
WHERE order_date >= '2024-01-01';

-- Efficient pagination with cursor-based approach
-- Instead of OFFSET which gets slower with large offsets
SELECT * FROM products 
WHERE product_id > 12345  -- cursor from last result
ORDER BY product_id 
LIMIT 20;

-- Optimized EXISTS queries instead of IN with large datasets
SELECT c.customer_id, c.email
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id 
      AND o.order_date >= '2024-01-01'
);

-- Common Table Expressions for complex queries
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as revenue
    FROM orders 
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', order_date)
),
sales_growth AS (
    SELECT 
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) as previous_month_revenue,
        ROUND(
            ((revenue - LAG(revenue) OVER (ORDER BY month)) / 
             LAG(revenue) OVER (ORDER BY month)) * 100, 2
        ) as growth_percentage
    FROM monthly_sales
)
SELECT * FROM sales_growth 
WHERE growth_percentage IS NOT NULL
ORDER BY month DESC;

-- High-frequency query materialized view
CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT 
    p.product_id,
    p.name,
    p.category_id,
    COUNT(oi.order_item_id) as total_orders,
    SUM(oi.quantity) as total_quantity_sold,
    SUM(oi.total_price) as total_revenue,
    AVG(oi.unit_price) as avg_selling_price,
    MAX(o.order_date) as last_sold_date
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed' OR o.status IS NULL
GROUP BY p.product_id, p.name, p.category_id;

-- Unique index for faster refreshes
CREATE UNIQUE INDEX idx_product_sales_summary_product_id 
ON product_sales_summary (product_id);

-- Automated refresh strategy
CREATE OR REPLACE FUNCTION refresh_product_sales_summary()
RETURNS VOID AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_summary;
    
    -- Log refresh for monitoring
    INSERT INTO materialized_view_refresh_log (view_name, refreshed_at)
    VALUES ('product_sales_summary', CURRENT_TIMESTAMP);
END;
$$ LANGUAGE plpgsql;

-- Schedule refresh via pg_cron or external scheduler
-- SELECT cron.schedule('refresh-product-sales', '0 */6 * * *', 
--                     'SELECT refresh_product_sales_summary();');

-- User Service Database Schema
CREATE DATABASE user_service;

-- Users aggregate root
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile JSONB,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1 -- Optimistic concurrency control
);

-- Domain events table for eventual consistency
CREATE TABLE user_domain_events (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(user_id),
    event_type VARCHAR(100) NOT NULL,
    event_data JSONB NOT NULL,
    event_version INTEGER NOT NULL,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed BOOLEAN DEFAULT FALSE
);

-- Order Service Database Schema  
CREATE DATABASE order_service;

-- Customer reference (not foreign key to users table)
CREATE TABLE customer_references (
    customer_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    -- Cached data from user service
    last_synced TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customer_references(customer_id),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(12,2) NOT NULL,
    items JSONB NOT NULL, -- Denormalized order items
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version INTEGER DEFAULT 1
);

-- Saga orchestration table
CREATE TABLE saga_instances (
    saga_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    saga_type VARCHAR(100) NOT NULL,
    saga_state VARCHAR(50) NOT NULL DEFAULT 'started',
    saga_data JSONB NOT NULL,
    current_step INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Saga step execution log
CREATE TABLE saga_steps (
    step_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    saga_id UUID NOT NULL REFERENCES saga_instances(saga_id),
    step_name VARCHAR(100) NOT NULL,
    step_type VARCHAR(50) NOT NULL, -- 'command' or 'compensation'
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    request_data JSONB,
    response_data JSONB,
    error_details TEXT,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP
);

-- Example: Order processing saga implementation
CREATE OR REPLACE FUNCTION process_order_saga(
    p_order_data JSONB
) RETURNS UUID AS $$
DECLARE
    v_saga_id UUID;
BEGIN
    -- Create saga instance
    INSERT INTO saga_instances (saga_type, saga_data)
    VALUES ('order_processing', p_order_data)
    RETURNING saga_id INTO v_saga_id;
    
    -- Start first step: Reserve inventory
    INSERT INTO saga_steps (saga_id, step_name, step_type, request_data)
    VALUES (v_saga_id, 'reserve_inventory', 'command', p_order_data);
    
    RETURN v_saga_id;
END;
$$ LANGUAGE plpgsql;

-- Command side: Event store
CREATE TABLE event_store (
    event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    stream_id VARCHAR(255) NOT NULL,
    stream_version INTEGER NOT NULL,
    event_type VARCHAR(100) NOT NULL,
    event_data JSONB NOT NULL,
    metadata JSONB,
    occurred_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Optimistic concurrency control
    CONSTRAINT unique_stream_version UNIQUE (stream_id, stream_version)
);

-- Partitioning for performance
CREATE INDEX idx_event_store_stream_id_version 
ON event_store (stream_id, stream_version);

-- Query side: Read models
CREATE TABLE user_read_model (
    user_id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    full_name VARCHAR(255),
    status VARCHAR(20),
    last_login TIMESTAMP,
    total_orders INTEGER DEFAULT 0,
    total_spent DECIMAL(12,2) DEFAULT 0,
    last_event_version INTEGER,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Projection function to update read models
CREATE OR REPLACE FUNCTION project_user_events()
RETURNS TRIGGER AS $$
BEGIN
    CASE NEW.event_type
        WHEN 'UserRegistered' THEN
            INSERT INTO user_read_model (
                user_id, email, username, full_name, status, last_event_version
            ) VALUES (
                (NEW.event_data->>'user_id')::UUID,
                NEW.event_data->>'email',
                NEW.event_data->>'username',
                NEW.event_data->>'full_name',
                'active',
                NEW.stream_version
            );
            
        WHEN 'UserProfileUpdated' THEN
            UPDATE user_read_model 
            SET full_name = NEW.event_data->>'full_name',
                last_event_version = NEW.stream_version,
                updated_at = CURRENT_TIMESTAMP
            WHERE user_id = (NEW.event_data->>'user_id')::UUID;
            
        WHEN 'OrderCompleted' THEN
            UPDATE user_read_model 
            SET total_orders = total_orders + 1,
                total_spent = total_spent + (NEW.event_data->>'amount')::DECIMAL,
                last_event_version = NEW.stream_version,
                updated_at = CURRENT_TIMESTAMP
            WHERE user_id = (NEW.event_data->>'user_id')::UUID;
    END CASE;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_project_user_events
    AFTER INSERT ON event_store
    FOR EACH ROW 
    WHEN (NEW.stream_id LIKE 'user-%')
    EXECUTE FUNCTION project_user_events();

-- Install pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Secure customer data table with encryption
CREATE TABLE secure_customers (
    customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email_encrypted BYTEA NOT NULL, -- Encrypted email
    phone_encrypted BYTEA, -- Encrypted phone
    ssn_encrypted BYTEA, -- Encrypted SSN
    address_encrypted BYTEA, -- Encrypted address
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- Non-sensitive metadata
    status VARCHAR(20) DEFAULT 'active',
    marketing_consent BOOLEAN DEFAULT FALSE
);

-- Functions for encryption/decryption
CREATE OR REPLACE FUNCTION encrypt_sensitive_data(
    plaintext TEXT,
    encryption_key TEXT
) RETURNS BYTEA AS $$
BEGIN
    RETURN pgp_sym_encrypt(plaintext, encryption_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE FUNCTION decrypt_sensitive_data(
    ciphertext BYTEA,
    encryption_key TEXT
) RETURNS TEXT AS $$
BEGIN
    RETURN pgp_sym_decrypt(ciphertext, encryption_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Secure view for application access
CREATE VIEW customer_secure_view AS
SELECT 
    customer_id,
    decrypt_sensitive_data(email_encrypted, current_setting('app.encryption_key')) as email,
    CASE 
        WHEN has_column_privilege(current_user, 'secure_customers', 'phone_encrypted', 'SELECT')
        THEN decrypt_sensitive_data(phone_encrypted, current_setting('app.encryption_key'))
        ELSE '***-***-****'
    END as phone,
    status,
    created_at
FROM secure_customers;

-- Enable RLS on sensitive tables
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Policy for data isolation by region
CREATE POLICY customer_region_isolation ON customer_data
    FOR ALL TO application_role
    USING (region = current_setting('app.user_region', true));

-- Policy for role-based access
CREATE POLICY customer_role_access ON customer_data
    FOR SELECT TO customer_service_role
    USING (
        status IN ('active', 'suspended') AND
        created_at >= CURRENT_DATE - INTERVAL '2 years'
    );

-- Admin override policy
CREATE POLICY admin_full_access ON customer_data
    FOR ALL TO admin_role
    USING (true);

-- Audit policy - log all access
CREATE POLICY audit_all_access ON customer_data
    FOR SELECT TO audit_role
    USING (
        -- Log the access
        (SELECT audit_log_access(customer_id, current_user, 'customer_data_access')) IS NOT NULL
    );

-- Generic audit log table
CREATE TABLE audit_log (
    audit_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(100) NOT NULL,
    record_id VARCHAR(255) NOT NULL,
    operation VARCHAR(10) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_values JSONB,
    new_values JSONB,
    changed_by VARCHAR(100) NOT NULL,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    client_ip INET,
    application_name VARCHAR(100),
    session_id VARCHAR(255)
);

-- Partition by date for performance
ALTER TABLE audit_log PARTITION BY RANGE (changed_at);

-- Create monthly partitions
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- Generic audit trigger function
CREATE OR REPLACE FUNCTION audit_trigger_function()
RETURNS TRIGGER AS $$
DECLARE
    old_data JSONB;
    new_data JSONB;
BEGIN
    -- Capture old and new values
    IF TG_OP = 'DELETE' THEN
        old_data = to_jsonb(OLD);
        new_data = NULL;
    ELSIF TG_OP = 'INSERT' THEN
        old_data = NULL;
        new_data = to_jsonb(NEW);
    ELSE -- UPDATE
        old_data = to_jsonb(OLD);
        new_data = to_jsonb(NEW);
    END IF;
    
    -- Insert audit record
    INSERT INTO audit_log (
        table_name, record_id, operation, old_values, new_values,
        changed_by, client_ip, application_name, session_id
    ) VALUES (
        TG_TABLE_NAME,
        COALESCE(NEW.id, OLD.id)::TEXT,
        TG_OP,
        old_data,
        new_data,
        current_setting('application_name', true),
        inet_client_addr(),
        current_setting('application_name', true),
        current_setting('app.session_id', true)
    );
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers to sensitive tables
CREATE TRIGGER audit_customers_trigger
    AFTER INSERT OR UPDATE OR DELETE ON customers
    FOR EACH ROW EXECUTE FUNCTION audit_trigger_function();

-- Data subject rights management
CREATE TABLE data_subject_requests (
    request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subject_id VARCHAR(255) NOT NULL, -- Customer ID or email
    request_type VARCHAR(50) NOT NULL CHECK (
        request_type IN ('access', 'portability', 'rectification', 'erasure', 'restriction')
    ),
    request_status VARCHAR(50) DEFAULT 'pending' CHECK (
        request_status IN ('pending', 'in_progress', 'completed', 'rejected')
    ),
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    completion_details JSONB,
    requestor_info JSONB NOT NULL
);

-- Data retention policy enforcement
CREATE TABLE data_retention_policies (
    policy_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name VARCHAR(100) NOT NULL,
    retention_period INTERVAL NOT NULL,
    deletion_criteria JSONB,
    last_enforced TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Automated data purging function
CREATE OR REPLACE FUNCTION enforce_data_retention()
RETURNS VOID AS $$
DECLARE
    policy RECORD;
    deletion_count INTEGER;
BEGIN
    FOR policy IN 
        SELECT * FROM data_retention_policies WHERE is_active = TRUE
    LOOP
        EXECUTE format(
            'DELETE FROM %I WHERE created_at < NOW() - %L',
            policy.table_name,
            policy.retention_period
        );
        
        GET DIAGNOSTICS deletion_count = ROW_COUNT;
        
        -- Log retention enforcement
        INSERT INTO audit_log (
            table_name, record_id, operation, new_values, changed_by
        ) VALUES (
            policy.table_name,
            'RETENTION_POLICY',
            'DELETE',
            jsonb_build_object(
                'deleted_count', deletion_count,
                'policy_id', policy.policy_id,
                'retention_period', policy.retention_period
            ),
            'SYSTEM_RETENTION_JOB'
        );
        
        UPDATE data_retention_policies 
        SET last_enforced = CURRENT_TIMESTAMP
        WHERE policy_id = policy.policy_id;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Query performance tracking
CREATE TABLE query_performance_log (
    log_id BIGSERIAL PRIMARY KEY,
    query_hash VARCHAR(64) NOT NULL, -- MD5 hash of normalized query
    query_text TEXT,
    execution_time_ms DECIMAL(10,3) NOT NULL,
    rows_examined BIGINT,
    rows_returned BIGINT,
    database_name VARCHAR(100),
    user_name VARCHAR(100),
    client_ip INET,
    executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (executed_at);

-- Automated performance alerting
CREATE OR REPLACE FUNCTION check_slow_queries()
RETURNS VOID AS $$
DECLARE
    slow_query_threshold INTERVAL := '5 seconds';
    alert_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO alert_count
    FROM query_performance_log
    WHERE executed_at >= CURRENT_TIMESTAMP - INTERVAL '5 minutes'
      AND execution_time_ms > EXTRACT(EPOCH FROM slow_query_threshold) * 1000;
    
    IF alert_count > 10 THEN
        INSERT INTO system_alerts (alert_type, severity, message, details)
        VALUES (
            'SLOW_QUERY_SPIKE',
            'HIGH',
            'Unusual number of slow queries detected',
            jsonb_build_object(
                'slow_query_count', alert_count,
                'threshold_ms', EXTRACT(EPOCH FROM slow_query_threshold) * 1000,
                'time_window', '5 minutes'
            )
        );
    END IF;
END;
$$ LANGUAGE plpgsql;