-- Create database-scoped credential
CREATE DATABASE SCOPED CREDENTIAL ManagedIdentityCredential
WITH IDENTITY = 'Managed Identity';

-- Create external data source
CREATE EXTERNAL DATA SOURCE ADLSDataSource
WITH (
    LOCATION = 'https://stdatalake001.dfs.core.windows.net',
    CREDENTIAL = ManagedIdentityCredential
);

-- Create external file format for Parquet
CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);

-- Create external table
CREATE EXTERNAL TABLE raw.Sales
WITH (
    LOCATION = 'raw/sales/',
    DATA_SOURCE = ADLSDataSource,
    FILE_FORMAT = ParquetFormat
)
AS
SELECT * FROM OPENROWSET(
    BULK 'raw/sales/**/*.parquet',
    FORMAT = 'PARQUET'
) WITH (
    sale_id BIGINT,
    customer_id INT,
    sale_date DATE,
    quantity INT,
    unit_price DECIMAL(18,2),
    total_amount DECIMAL(18,2)
) AS Sales;

-- Create view for curated data
CREATE VIEW curated.vw_FactSales
AS
SELECT
    s.sale_id,
    s.customer_id,
    s.sale_date,
    s.quantity,
    s.total_amount,
    c.customer_name,
    c.segment
FROM raw.Sales s
LEFT JOIN raw.Customers c ON s.customer_id = c.customer_id;

-- Create lake database (schema)
CREATE SCHEMA analytics;
GO

-- Create statistics for better query plans
CREATE STATISTICS STAT_Sales_SaleDate
ON raw.Sales(sale_date);

-- Create view for time-based queries
CREATE VIEW analytics.vw_DailySales
AS
SELECT
    sale_date,
    COUNT(*) AS transactions,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM raw.Sales
GROUP BY sale_date;