-- AWS REDSHIFT ARCHITECTURE OVERZICHT

-- 1. CLUSTER CREATIE EN CONFIGURATIE
CREATE CLUSTER sales_data_warehouse
WITH (
    CLUSTER_TYPE = MULTI_NODE,
    NODE_TYPE = RA3.4XLARGE,
    NUMBER_OF_NODES = 4,
    MASTER_USERNAME = 'admin',
    MASTER_PASSWORD = 'SecurePassword123!',
    DATABASE_NAME = 'sales_dw',
    PORT = 5439,
    ENCRYPTED = TRUE,
    ENHANCED_VPC_ROUTING = TRUE,
    PUBLICLY_ACCESSIBLE = FALSE,
    IAM_ROLES = 'arn:aws:iam::123456789012:role/RedshiftS3Access'
);

-- 2. DATABASE EN SCHEMA CREATIE
CREATE DATABASE sales_dw
WITH ENCODING 'UTF8'
CONNECTION LIMIT 50;

CREATE SCHEMA IF NOT EXISTS sales_schema;
CREATE SCHEMA IF NOT EXISTS staging_schema;
CREATE SCHEMA IF NOT EXISTS reporting_schema;

-- 3. TABEL CREATIE MET DISTRIBUTIE EN SORT KEYS
CREATE TABLE sales_schema.fact_sales (
    sales_id BIGINT IDENTITY(1,1),
    customer_id INTEGER NOT NULL DISTKEY,
    product_id INTEGER NOT NULL,
    store_id INTEGER NOT NULL,
    sales_date DATE NOT NULL SORTKEY,
    sales_amount DECIMAL(18,2) NOT NULL,
    quantity INTEGER NOT NULL,
    discount_amount DECIMAL(18,2) DEFAULT 0,
    created_timestamp TIMESTAMP DEFAULT SYSDATE,
    updated_timestamp TIMESTAMP DEFAULT SYSDATE
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (sales_date, product_id)
COMPOUND SORTKEY (sales_date, product_id);

-- 4. DIMENSION TABLES MET REPLICATE DISTRIBUTIE
CREATE TABLE sales_schema.dim_customer (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone_number VARCHAR(50),
    address_line1 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(50),
    postal_code VARCHAR(20),
    country VARCHAR(100),
    customer_segment VARCHAR(50),
    signup_date DATE,
    is_active BOOLEAN DEFAULT TRUE,
    created_date DATE DEFAULT CURRENT_DATE
)
DISTSTYLE ALL  -- Replicated across all nodes
SORTKEY (customer_id);

-- 5. EXTERNE TABEL VOOR SPECTRUM INTEGRATIE
CREATE EXTERNAL SCHEMA spectrum_sales
FROM DATA CATALOG
DATABASE 'sales_data_lake'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

CREATE EXTERNAL TABLE spectrum_sales.external_sales (
    customer_id INT,
    product_id INT,
    sales_date DATE,
    amount DECIMAL(18,2),
    quantity INT,
    region VARCHAR(50)
)
PARTITIONED BY (sales_year INT, sales_month INT)
STORED AS PARQUET
LOCATION 's3://sales-data-lake/processed/sales/'
TABLE PROPERTIES ('compression'='snappy');

-- NODE TYPE SELECTIE EN CONFIGURATIE

-- 1. DC2 CLUSTER VOOR PERFORMANCE-SENSITIVE WORKLOADS
-- SQL: DC2 cluster creatie voor high-performance data warehouse
CREATE CLUSTER performance_dw
WITH (
    CLUSTER_TYPE = MULTI_NODE,
    NODE_TYPE = DC2.8XLARGE,  -- 2.56 TB SSD per node
    NUMBER_OF_NODES = 8,      -- Totale storage: ~20 TB
    ENCRYPTED = TRUE,
    ENHANCED_VPC_ROUTING = TRUE,
    AUTOMATED_SNAPSHOT_RETENTION_PERIOD = 7,  -- 7 dagen retention
    MANUAL_SNAPSHOT_RETENTION_PERIOD = 30     -- 30 dagen voor manual
);

-- 2. RA3 CLUSTER VOOR ENTERPRISE WORKLOADS MET DATA LAKE
CREATE CLUSTER enterprise_dw
WITH (
    CLUSTER_TYPE = MULTI_NODE,
    NODE_TYPE = RA3.4XLARGE,      -- Managed storage, 96 vCPUs
    NUMBER_OF_NODES = 16,         -- Schaalbaar compute
    ENCRYPTED = TRUE,
    ENHANCED_VPC_ROUTING = TRUE,
    IAM_ROLES = 'arn:aws:iam::123456789012:role/RedshiftS3Access',
    DEFAULT_IAM_ROLE_ARN = 'arn:aws:iam::123456789012:role/RedshiftDefaultRole'
);

-- 3. SERVERLESS CONFIGURATIE (NO-SQL, UI-BASED)
-- AWS Console of Infrastructure as Code (CloudFormation/Terraform)
/*
Serverless Workgroup Configuration:
- Base capacity: 128 RPUs (Redshift Processing Units)
- Auto-scaling: Min 32 RPUs, Max 512 RPUs
- VPC: vpc-1234567890abcdef0
- Subnets: Private subnets in 3 AZs
- Security groups: sg-redshift-serverless
- Namespace: sales-namespace
- Database: sales_db
- Admin user: admin
*/

-- 4. RESIZE OPERATIE VOOR SCHALING
-- Elastic resize voor DC2/RA3
ALTER CLUSTER performance_dw
RESIZE TO NODE TYPE RA3.4XLARGE
NUMBER OF NODES 32;

-- Classic resize voor complexe wijzigingen
ALTER CLUSTER enterprise_dw
WITH NODE_TYPE RA3.16XLARGE
NUMBER_OF_NODES 64;

-- 5. WORKLOAD MANAGEMENT (WLM) CONFIGURATIE
CREATE OR REPLACE WLM CONFIGURATION
WITH (
    QUERY_GROUP 'reporting' (
        USER_GROUP 'reporting_users',
        QUERY_CONCURRENCY 15,
        MEMORY_PERCENT 30
    ),
    QUERY_GROUP 'etl' (
        USER_GROUP 'etl_users',
        QUERY_CONCURRENCY 5,
        MEMORY_PERCENT 50,
        PRIORITY 'HIGH'
    ),
    QUERY_GROUP 'adhoc' (
        USER_GROUP 'all_users',
        QUERY_CONCURRENCY 30,
        MEMORY_PERCENT 20,
        PRIORITY 'LOW'
    )
);

-- 6. AUTOMATED PERFORMANCE FEATURES
-- Automatic Table Optimization (ATO)
ALTER TABLE sales_schema.fact_sales
SET AUTO_SORT = TRUE;

-- Automatic Vacuum Delete
ALTER TABLE sales_schema.fact_sales
SET AUTO_VACUUM = TRUE;

-- Automatic Compression Analysis
ANALYZE COMPRESSION sales_schema.fact_sales;

-- AWS REDSHIFT DATA LOADING PATTERNS

-- 1. COPY COMMAND VOOR S3 DATA LOADING
COPY sales_schema.fact_sales (
    customer_id,
    product_id,
    sales_date,
    sales_amount,
    quantity,
    discount_amount
)
FROM 's3://sales-data-bucket/raw/sales_2024*.parquet'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
FORMAT PARQUET
COMPUPDATE OFF
STATUPDATE OFF
MAXERROR 100
TIMEFORMAT 'auto'
ACCEPTINVCHARS
EMPTYASNULL
BLANKSASNULL
TRUNCATECOLUMNS
COMPROWS 1000000
GZIP;

-- 2. MANIFEST FILES VOOR PRECIESE FILE SELECTIE
COPY sales_schema.fact_sales
FROM 's3://sales-data-bucket/manifests/sales_manifest.json'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
MANIFEST
JSON 'auto';

-- 3. PARALLEL DATA LOADING MET MULTIPLE FILES
-- Create multiple data slices voor parallel loading
COPY sales_schema.fact_sales
FROM 's3://sales-data-bucket/raw/partitioned/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
PARTITION BY (sales_date DATE)
FORMAT PARQUET
PARALLEL ON;

-- 4. INCREMENTAL DATA LOADING PATTERN
-- Staging table voor incremental loads
CREATE TEMPORARY TABLE staging_sales (
    customer_id INTEGER,
    product_id INTEGER,
    sales_date DATE,
    sales_amount DECIMAL(18,2),
    quantity INTEGER
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (sales_date);

-- Load data naar staging table
COPY staging_sales
FROM 's3://sales-data-bucket/incremental/sales_delta_*.parquet'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftS3Access'
FORMAT PARQUET;

-- Merge/upsert naar main table
BEGIN TRANSACTION;

-- Delete bestaande records
DELETE FROM sales_schema.fact_sales
WHERE (customer_id, product_id, sales_date) IN (
    SELECT customer_id, product_id, sales_date
    FROM staging_sales
);

-- Insert nieuwe/geupdate records
INSERT INTO sales_schema.fact_sales (
    customer_id,
    product_id,
    sales_date,
    sales_amount,
    quantity,
    discount_amount,
    created_timestamp,
    updated_timestamp
)
SELECT 
    customer_id,
    product_id,
    sales_date,
    sales_amount,
    quantity,
    0 AS discount_amount,
    SYSDATE AS created_timestamp,
    SYSDATE AS updated_timestamp
FROM staging_sales;

COMMIT;

-- 5. REAL-TIME DATA LOADING MET KINESIS DATA STREAMS
-- Kinesis Data Streams voor real-time ingestie
CREATE EXTERNAL SCHEMA kinesis_sales
FROM KINESIS
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftKinesisRole';

CREATE MATERIALIZED VIEW sales_schema.realtime_sales_mv AS
SELECT 
    approximate_arrival_timestamp,
    partition_key,
    shard_id,
    JSON_EXTRACT_PATH_TEXT(FROM VARBYTE(kinesis_data), 'customer_id')::INT AS customer_id,
    JSON_EXTRACT_PATH_TEXT(FROM VARBYTE(kinesis_data), 'product_id')::INT AS product_id,
    JSON_EXTRACT_PATH_TEXT(FROM VARBYTE(kinesis_data), 'sales_amount')::DECIMAL(18,2) AS sales_amount,
    JSON_EXTRACT_PATH_TEXT(FROM VARBYTE(kinesis_data), 'timestamp')::TIMESTAMP AS event_timestamp
FROM kinesis_sales.sales_stream
WHERE CAN_JSON_PARSE(kinesis_data);

-- 6. DATA VALIDATION EN QUALITY CHECKS
CREATE OR REPLACE VIEW sales_schema.data_quality_checks AS
SELECT 
    'fact_sales' AS table_name,
    COUNT(*) AS total_rows,
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(DISTINCT product_id) AS unique_products,
    SUM(CASE WHEN sales_amount < 0 THEN 1 ELSE 0 END) AS negative_sales_count,
    SUM(CASE WHEN quantity < 0 THEN 1 ELSE 0 END) AS negative_quantity_count,
    MIN(sales_date) AS min_sales_date,
    MAX(sales_date) AS max_sales_date,
    SYSDATE AS check_timestamp
FROM sales_schema.fact_sales;

-- AWS REDSHIFT PERFORMANCE TUNING

-- 1. DISTRIBUTION STYLES OPTIMALISATIE
-- EVEN distributie voor staging tables
CREATE TABLE sales_schema.staging_sales (
    sales_id BIGINT,
    customer_id INTEGER,
    -- ... andere kolommen
)
DISTSTYLE EVEN
SORTKEY AUTO;

-- KEY distributie voor fact tables
CREATE TABLE sales_schema.fact_sales_large (
    sales_id BIGINT,
    customer_id INTEGER DISTKEY,
    sales_date DATE SORTKEY,
    -- ... andere kolommen
)
DISTSTYLE KEY
DISTKEY (customer_id)
COMPOUND SORTKEY (sales_date, customer_id, product_id);

-- ALL distributie voor kleine dimension tables
CREATE TABLE sales_schema.dim_country (
    country_id INTEGER PRIMARY KEY,
    country_code VARCHAR(3),
    country_name VARCHAR(100)
)
DISTSTYLE ALL;

-- 2. SORT KEYS EN COMPRESSION OPTIMALISATIE
CREATE TABLE sales_schema.optimized_sales (
    sales_id BIGINT ENCODE az64,
    customer_id INTEGER DISTKEY ENCODE az64,
    product_id INTEGER ENCODE az64,
    sales_date DATE SORTKEY ENCODE az64,
    sales_amount DECIMAL(18,2) ENCODE az64,
    quantity INTEGER ENCODE az64
)
DISTSTYLE KEY
DISTKEY (customer_id)
COMPOUND SORTKEY (sales_date, customer_id, product_id)
COMPRESSION AUTO;

-- 3. ANALYZE COMPRESSION VOOR AUTOMATISCKE COMPRESSIE
ANALYZE COMPRESSION sales_schema.fact_sales;

-- 4. VACUUM EN ANALYZE OPERATIES
-- Vacuum voor space reclaim en sort order maintenance
VACUUM sales_schema.fact_sales
TO 100 PERCENT
SORT ONLY;

-- Vacuum delete voor deleted rows cleanup
VACUUM DELETE ONLY sales_schema.fact_sales;

-- Analyze voor statistics bijwerken
ANALYZE sales_schema.fact_sales
PREDICATE COLUMNS;

-- 5. QUERY PERFORMANCE ANALYSE MET EXPLAIN
EXPLAIN
SELECT 
    c.customer_segment,
    DATE_TRUNC('month', s.sales_date) AS sales_month,
    COUNT(*) AS transaction_count,
    SUM(s.sales_amount) AS total_sales,
    AVG(s.sales_amount) AS avg_transaction_value
FROM sales_schema.fact_sales s
JOIN sales_schema.dim_customer c 
    ON s.customer_id = c.customer_id
WHERE s.sales_date >= '2024-01-01'
    AND c.customer_segment IN ('Premium', 'Business')
GROUP BY 
    c.customer_segment,
    DATE_TRUNC('month', s.sales_date)
ORDER BY 
    sales_month DESC,
    total_sales DESC;

-- 6. SYSTEM TABLES VOOR PERFORMANCE MONITORING
SELECT 
    query,
    query_type,
    starttime,
    endtime,
    elapsed,
    ROUND(elapsed::FLOAT / 1000000, 2) AS elapsed_seconds,
    aborted,
    label,
    is_diskbased,
    rows,
    bytes,
    cpu_time,
    exec_time,
    queue_time,
    io_time
FROM svl_qlog
WHERE userid > 1
    AND starttime >= DATEADD(day, -7, GETDATE())
    AND elapsed > 10000000  -- Meer dan 10 seconden
ORDER BY elapsed DESC
LIMIT 50;

-- 7. CONCURRENCY SCALING CONFIGURATIE
-- Enable concurrency scaling
ALTER CLUSTER sales_data_warehouse
SET enable_concurrency_scaling = true;

-- Configure concurrency scaling mode
ALTER CLUSTER sales_data_warehouse
SET concurrency_scaling_mode = 'auto';

-- 8. MATERIALIZED VIEWS VOOR PERFORMANCE
CREATE MATERIALIZED VIEW sales_schema.mv_daily_sales_summary
AUTO REFRESH YES
AS
SELECT 
    sales_date,
    customer_id,
    COUNT(*) AS transaction_count,
    SUM(sales_amount) AS daily_sales,
    AVG(sales_amount) AS avg_transaction_value
FROM sales_schema.fact_sales
WHERE sales_date >= DATEADD(month, -12, GETDATE())
GROUP BY sales_date, customer_id;

-- REDSHIFT ML: MACHINE LEARNING IN REDSHIFT

-- 1. CREATE MODEL VOOR SUPERVISED LEARNING
CREATE MODEL sales_schema.customer_churn_prediction
FROM (
    SELECT 
        customer_id,
        days_since_last_purchase,
        total_purchases,
        avg_purchase_value,
        total_spent,
        days_since_signup,
        CASE 
            WHEN churned = TRUE THEN 1
            ELSE 0
        END AS label
    FROM sales_schema.customer_behavior
    WHERE signup_date <= DATEADD(month, -3, GETDATE())
)
TARGET label
FUNCTION fn_predict_churn
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole'
SETTINGS (
    S3_BUCKET 'redshift-ml-bucket',
    KMS_KEY_ID 'alias/aws/s3',
    MAX_RUNTIME 3600
);

-- 2. AUTO ML VOOR AUTOMATISCHE MODEL SELECTIE
CREATE MODEL sales_schema.customer_lifetime_value_automl
FROM (
    SELECT 
        customer_id,
        days_since_last_purchase,
        total_purchases,
        avg_purchase_value,
        days_since_signup,
        customer_segment,
        region,
        lifetime_value
    FROM sales_schema.customer_ltv_training
)
TARGET lifetime_value
FUNCTION fn_predict_ltv
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole'
AUTO ON
SETTINGS (
    PROBLEM_TYPE 'REGRESSION',
    OBJECTIVE 'MSE',
    PREPROCESSORS 'none'
);

-- 3. MODEL TRAINING MET CUSTOM ALGORITHM
CREATE MODEL sales_schema.product_recommendation_xgboost
FROM (
    SELECT 
        user_id,
        product_id,
        view_count,
        purchase_count,
        avg_rating,
        days_since_last_view,
        days_since_last_purchase,
        purchased
    FROM sales_schema.user_product_interactions
)
TARGET purchased
FUNCTION fn_predict_product_purchase
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole'
SETTINGS (
    MODEL_TYPE = 'xgboost',
    OBJECTIVE = 'binary:logistic',
    MAX_DEPTH = 6,
    ETA = 0.3,
    NUM_ROUND = 100
);

-- 4. MODEL PREDICTIONS IN QUERIES
SELECT 
    customer_id,
    customer_name,
    total_spent,
    days_since_last_purchase,
    fn_predict_churn(
        days_since_last_purchase,
        total_purchases,
        avg_purchase_value,
        total_spent,
        days_since_signup
    ) AS churn_probability,
    CASE 
        WHEN churn_probability > 0.7 THEN 'High Risk'
        WHEN churn_probability > 0.3 THEN 'Medium Risk'
        ELSE 'Low Risk'
    END AS churn_risk_category
FROM sales_schema.dim_customer c
JOIN sales_schema.customer_behavior cb 
    ON c.customer_id = cb.customer_id
WHERE c.is_active = TRUE
ORDER BY churn_probability DESC
LIMIT 100;

-- 5. MODEL MONITORING EN EVALUATIE
SELECT 
    model_name,
    model_type,
    training_start_time,
    training_end_time,
    training_duration,
    s3_location,
    accuracy,
    f1,
    precision,
    recall,
    mse,
    mae,
    r2_score
FROM svv_ml_model_info
WHERE schema_name = 'sales_schema'
ORDER BY training_start_time DESC;

-- 6. BATCH PREDICTIONS VOOR ALLE CUSTOMERS
CREATE TABLE sales_schema.customer_churn_predictions AS
SELECT 
    customer_id,
    fn_predict_churn(
        days_since_last_purchase,
        total_purchases,
        avg_purchase_value,
        total_spent,
        days_since_signup
    ) AS churn_probability,
    GETDATE() AS prediction_date
FROM sales_schema.customer_behavior
WHERE is_active = TRUE;

-- 7. MODEL SHOW COMMAND VOOR DETAILS
SHOW MODEL sales_schema.customer_churn_prediction;

-- 8. MODEL UPDATES EN RETRAINING
-- Drop oude model
DROP MODEL sales_schema.customer_churn_prediction;

-- Create nieuwe model met meer data
CREATE MODEL sales_schema.customer_churn_prediction_v2
FROM (
    SELECT * FROM sales_schema.customer_behavior
    WHERE signup_date <= DATEADD(month, -6, GETDATE())
)
TARGET churned
FUNCTION fn_predict_churn_v2
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole';

-- MIGRATION ROADMAP VOOR AWS REDSHIFT

-- 1. SCHEMA CONVERSION EN OPTIMALISATIE
-- Source: SQL Server schema
-- Target: Redshift optimized schema

-- SQL Server source schema
/*
CREATE TABLE Sales.Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    TotalAmount DECIMAL(18,2),
    Status VARCHAR(50)
)
*/

-- Redshift target schema met optimalisaties
CREATE TABLE sales_schema.fact_orders (
    order_id INTEGER IDENTITY(1,1) ENCODE az64,
    customer_id INTEGER DISTKEY ENCODE az64,
    order_date DATE SORTKEY ENCODE az64,
    order_timestamp TIMESTAMP ENCODE az64,
    total_amount DECIMAL(18,2) ENCODE az64,
    status VARCHAR(50) ENCODE lzo,
    created_date DATE DEFAULT CURRENT_DATE,
    updated_date DATE DEFAULT CURRENT_DATE
)
DISTSTYLE KEY
DISTKEY (customer_id)
COMPOUND SORTKEY (order_date, customer_id, status);

-- 2. DATA MIGRATION MET AWS DMS
-- AWS DMS task configuration
/*
Source Endpoint: SQL Server on-premise
Target Endpoint: Redshift cluster
Migration Type: Full load + CDC
Table Mappings: Include specific schemas/tables
Transformation Rules: Data type conversions
*/

-- 3. INCREMENTAL DATA SYNC
-- CDC (Change Data Capture) implementation
CREATE TABLE staging_schema.cdc_orders (
    operation VARCHAR(10),  -- 'INSERT', 'UPDATE', 'DELETE'
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    total_amount DECIMAL(18,2),
    status VARCHAR(50),
    change_timestamp TIMESTAMP
);

-- Merge procedure voor CDC data
CREATE OR REPLACE PROCEDURE sales_schema.merge_cdc_orders()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Delete operations
    DELETE FROM sales_schema.fact_orders o
    USING staging_schema.cdc_orders c
    WHERE o.order_id = c.order_id
        AND c.operation = 'DELETE';
    
    -- Update operations
    UPDATE sales_schema.fact_orders o
    SET 
        customer_id = c.customer_id,
        order_date = c.order_date,
        total_amount = c.total_amount,
        status = c.status,
        updated_date = CURRENT_DATE
    FROM staging_schema.cdc_orders c
    WHERE o.order_id = c.order_id
        AND c.operation = 'UPDATE';
    
    -- Insert operations
    INSERT INTO sales_schema.fact_orders (
        customer_id,
        order_date,
        order_timestamp,
        total_amount,
        status
    )
    SELECT 
        customer_id,
        order_date,
        change_timestamp,
        total_amount,
        status
    FROM staging_schema.cdc_orders
    WHERE operation = 'INSERT'
        AND order_id NOT IN (
            SELECT order_id FROM sales_schema.fact_orders
        );
    
    -- Clear staging table
    TRUNCATE TABLE staging_schema.cdc_orders;
    
    COMMIT;
END;
$$;

-- 4. DATA VALIDATION POST-MIGRATION
CREATE OR REPLACE VIEW migration_schema.data_validation_report AS
WITH source_counts AS (
    SELECT 
        'Orders' AS table_name,
        COUNT(*) AS source_count,
        SUM(TotalAmount) AS source_total_amount
    FROM SQLServer.Sales.Orders
    WHERE OrderDate >= '2024-01-01'
),
target_counts AS (
    SELECT 
        'Orders' AS table_name,
        COUNT(*) AS target_count,
        SUM(total_amount) AS target_total_amount
    FROM sales_schema.fact_orders
    WHERE order_date >= '2024-01-01'
)
SELECT 
    s.table_name,
    s.source_count,
    t.target_count,
    ABS(s.source_count - t.target_count) AS count_difference,
    CASE 
        WHEN s.source_count = t.target_count THEN 'PASS'
        ELSE 'FAIL'
    END AS count_validation,
    s.source_total_amount,
    t.target_total_amount,
    ABS(s.source_total_amount - t.target_total_amount) AS amount_difference,
    CASE 
        WHEN ABS(s.source_total_amount - t.target_total_amount) < 0.01 THEN 'PASS'
        ELSE 'FAIL'
    END AS amount_validation
FROM source_counts s
JOIN target_counts t ON s.table_name = t.table_name;

-- 5. PERFORMANCE BASELINING EN VALIDATION
CREATE TABLE migration_schema.performance_baselines (
    test_name VARCHAR(100),
    source_execution_time_ms INTEGER,
    target_execution_time_ms INTEGER,
    performance_improvement_pct DECIMAL(5,2),
    test_date DATE DEFAULT CURRENT_DATE
);

-- Sample query performance test
INSERT INTO migration_schema.performance_baselines
SELECT 
    'Monthly Sales Report' AS test_name,
    12500 AS source_execution_time_ms,  -- SQL Server: 12.5 seconden
    450 AS target_execution_time_ms,    -- Redshift: 0.45 seconden
    ROUND((12500 - 450)::FLOAT / 12500 * 100, 2) AS performance_improvement_pct,
    CURRENT_DATE;