-- Sales Fact Table Example
CREATE TABLE fact_sales (
    sales_key BIGINT IDENTITY(1,1) PRIMARY KEY,
    date_key INT NOT NULL,
    product_key INT NOT NULL,
    customer_key INT NOT NULL,
    store_key INT NOT NULL,

    -- Measures
    quantity_sold DECIMAL(10,2),
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(12,2),
    discount_amount DECIMAL(10,2),

    -- Metadata
    created_date DATETIME2 DEFAULT GETDATE(),
    batch_id VARCHAR(50),

    -- Foreign Keys
    FOREIGN KEY (date_key) REFERENCES dim_date(date_key),
    FOREIGN KEY (product_key) REFERENCES dim_product(product_key),
    FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),
    FOREIGN KEY (store_key) REFERENCES dim_store(store_key)
);

-- Partitioning for performance
ALTER TABLE fact_sales
ADD CONSTRAINT pk_fact_sales_partitioned
PARTITION (date_key);

-- Customer Dimension with Slowly Changing Dimensions
CREATE TABLE dim_customer (
    customer_key INT IDENTITY(1,1) PRIMARY KEY,
    customer_id VARCHAR(50) NOT NULL, -- Natural key

    -- Attributes
    customer_name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(200),
    city VARCHAR(50),
    state VARCHAR(50),
    zip_code VARCHAR(10),
    customer_segment VARCHAR(20),

    -- SCD Type 2 fields
    effective_date DATE NOT NULL,
    expiry_date DATE NOT NULL DEFAULT '9999-12-31',
    is_current BIT NOT NULL DEFAULT 1,

    -- Metadata
    created_date DATETIME2 DEFAULT GETDATE(),
    updated_date DATETIME2 DEFAULT GETDATE()
);

-- Index for natural key lookups
CREATE INDEX ix_dim_customer_natural_key 
ON dim_customer (customer_id, is_current);

-- SCD Type 2 Dimension Update Procedure
CREATE PROCEDURE usp_load_customer_dimension
AS
BEGIN
    -- Insert new customers
    INSERT INTO dim_customer (customer_id, customer_name, email, phone, 
                             address, city, state, zip_code, customer_segment,
                             effective_date)
    SELECT s.customer_id, s.customer_name, s.email, s.phone,
           s.address, s.city, s.state, s.zip_code, s.customer_segment,
           GETDATE()
    FROM staging_customer s
    LEFT JOIN dim_customer d ON s.customer_id = d.customer_id AND d.is_current = 1
    WHERE d.customer_id IS NULL;

    -- Handle changed records (SCD Type 2)
    -- Close current records
    UPDATE dim_customer 
    SET expiry_date = GETDATE(),
        is_current = 0,
        updated_date = GETDATE()
    WHERE customer_id IN (
        SELECT d.customer_id
        FROM dim_customer d
        JOIN staging_customer s ON d.customer_id = s.customer_id
        WHERE d.is_current = 1
        AND (d.customer_name != s.customer_name OR 
             d.email != s.email OR 
             d.customer_segment != s.customer_segment)
    );

    -- Insert new versions
    INSERT INTO dim_customer (customer_id, customer_name, email, phone,
                             address, city, state, zip_code, customer_segment,
                             effective_date)
    SELECT s.customer_id, s.customer_name, s.email, s.phone,
           s.address, s.city, s.state, s.zip_code, s.customer_segment,
           GETDATE()
    FROM staging_customer s
    JOIN dim_customer d ON s.customer_id = d.customer_id
    WHERE d.is_current = 0 AND d.expiry_date = CAST(GETDATE() AS DATE);
END;

-- Fact table loading with validation
CREATE PROCEDURE usp_load_sales_fact
    @batch_date DATE
AS
BEGIN
    DECLARE @batch_id VARCHAR(50) = 'BATCH_' + FORMAT(@batch_date, 'yyyyMMdd');

    -- Data quality checks
    IF EXISTS (SELECT 1 FROM staging_sales WHERE total_amount < 0)
    BEGIN
        RAISERROR('Negative amounts found in staging data', 16, 1);
        RETURN;
    END;

    -- Load fact table with dimension key lookups
    INSERT INTO fact_sales (date_key, product_key, customer_key, store_key,
                           quantity_sold, unit_price, total_amount, 
                           discount_amount, batch_id)
    SELECT 
        dd.date_key,
        dp.product_key,
        dc.customer_key,
        ds.store_key,
        s.quantity_sold,
        s.unit_price,
        s.total_amount,
        s.discount_amount,
        @batch_id
    FROM staging_sales s
    JOIN dim_date dd ON s.transaction_date = dd.full_date
    JOIN dim_product dp ON s.product_id = dp.product_id AND dp.is_current = 1
    JOIN dim_customer dc ON s.customer_id = dc.customer_id AND dc.is_current = 1
    JOIN dim_store ds ON s.store_id = ds.store_id AND ds.is_current = 1
    WHERE s.batch_date = @batch_date;

    -- Log processing statistics
    INSERT INTO etl_log (batch_id, table_name, records_processed, process_date)
    VALUES (@batch_id, 'fact_sales', @@ROWCOUNT, GETDATE());
END;

-- Columnstore index for analytical queries
CREATE CLUSTERED COLUMNSTORE INDEX cci_fact_sales 
ON fact_sales;

-- Supporting indexes for common query patterns
CREATE INDEX ix_fact_sales_date_product 
ON fact_sales (date_key, product_key) 
INCLUDE (total_amount, quantity_sold);

-- Dimension table indexes
CREATE INDEX ix_dim_product_category 
ON dim_product (category, subcategory) 
WHERE is_current = 1;

-- Efficient aggregation query
SELECT 
    dd.year_month,
    dp.category,
    SUM(fs.total_amount) as total_sales,
    SUM(fs.quantity_sold) as total_quantity,
    COUNT(*) as transaction_count
FROM fact_sales fs
JOIN dim_date dd ON fs.date_key = dd.date_key
JOIN dim_product dp ON fs.product_key = dp.product_key
WHERE dd.calendar_year = 2024
    AND dp.is_current = 1
GROUP BY dd.year_month, dp.category
ORDER BY dd.year_month, total_sales DESC;

-- Data quality validation framework
CREATE TABLE data_quality_rules (
    rule_id INT IDENTITY(1,1) PRIMARY KEY,
    table_name VARCHAR(100),
    rule_name VARCHAR(100),
    rule_sql NVARCHAR(MAX),
    threshold_value DECIMAL(10,2),
    is_active BIT DEFAULT 1
);

-- Example quality rule
INSERT INTO data_quality_rules (table_name, rule_name, rule_sql, threshold_value)
VALUES ('fact_sales', 'Revenue_Range_Check', 
        'SELECT COUNT(*) FROM fact_sales WHERE total_amount > 10000 OR total_amount < 0', 
        0);