Implementation Guide: Analyze sales velocity, margin, and inventory turns to identify winners and underperformers

curl -X GET https://connect.squareup.com/v2/catalog/list \
  -H 'Authorization: Bearer {ACCESS_TOKEN}' \
  -H 'Content-Type: application/json' | python3 -m json.tool | head -100

curl -X GET https://{store}.myshopify.com/admin/api/2024-01/products/count.json \
  -H 'X-Shopify-Access-Token: {ACCESS_TOKEN}'

# verify item list with cost data (use QBO API sandbox or Postman collection
# to test)

GET https://quickbooks.api.intuit.com/v3/company/{companyId}/query?query=SELECT * FROM Item WHERE Type='Inventory' MAXRESULTS 10

az login

az group create \
  --name rg-retail-analytics-{clientname} \
  --location eastus

az sql server create \
  --name sql-retail-{clientname} \
  --resource-group rg-retail-analytics-{clientname} \
  --location eastus \
  --admin-user sqladmin \
  --admin-password '{STRONG_PASSWORD_HERE}'

az sql db create \
  --resource-group rg-retail-analytics-{clientname} \
  --server sql-retail-{clientname} \
  --name RetailAnalyticsDB \
  --service-objective S1

az sql server firewall-rule create \
  --resource-group rg-retail-analytics-{clientname} \
  --server sql-retail-{clientname} \
  --name AllowAzureServices \
  --start-ip-address 0.0.0.0 \
  --end-ip-address 0.0.0.0

az sql server firewall-rule create \
  --resource-group rg-retail-analytics-{clientname} \
  --server sql-retail-{clientname} \
  --name MSPOffice \
  --start-ip-address {MSP_OFFICE_IP} \
  --end-ip-address {MSP_OFFICE_IP}

az sql server ad-admin create \
  --resource-group rg-retail-analytics-{clientname} \
  --server sql-retail-{clientname} \
  --display-name 'SQL Admin Group' \
  --object-id {AZURE_AD_GROUP_OBJECT_ID}

-- ==============================================
-- DIMENSION TABLES
-- ==============================================

CREATE TABLE dim_product (
    product_id NVARCHAR(100) PRIMARY KEY,
    sku NVARCHAR(50) NOT NULL,
    product_name NVARCHAR(255) NOT NULL,
    category NVARCHAR(100),
    subcategory NVARCHAR(100),
    brand NVARCHAR(100),
    vendor_name NVARCHAR(200),
    unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,  -- landed cost / COGS per unit
    current_retail_price DECIMAL(10,2),
    is_active BIT DEFAULT 1,
    created_date DATETIME2 DEFAULT GETUTCDATE(),
    updated_date DATETIME2 DEFAULT GETUTCDATE()
);

CREATE TABLE dim_location (
    location_id NVARCHAR(100) PRIMARY KEY,
    location_name NVARCHAR(200) NOT NULL,
    address NVARCHAR(500),
    city NVARCHAR(100),
    state NVARCHAR(50),
    zip_code NVARCHAR(20),
    is_active BIT DEFAULT 1
);

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,          -- YYYYMMDD format
    full_date DATE NOT NULL,
    day_of_week NVARCHAR(10),
    day_of_month INT,
    week_of_year INT,
    month_num INT,
    month_name NVARCHAR(20),
    quarter_num INT,
    year_num INT,
    is_weekend BIT,
    fiscal_week INT,
    fiscal_month INT,
    fiscal_year INT
);

-- ==============================================
-- FACT TABLES
-- ==============================================

CREATE TABLE fact_sales (
    sale_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    transaction_id NVARCHAR(100) NOT NULL,
    transaction_date DATETIME2 NOT NULL,
    date_key INT NOT NULL REFERENCES dim_date(date_key),
    product_id NVARCHAR(100) NOT NULL REFERENCES dim_product(product_id),
    location_id NVARCHAR(100) NOT NULL REFERENCES dim_location(location_id),
    quantity_sold INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,         -- actual selling price (after discounts)
    unit_cost DECIMAL(10,2) NOT NULL,          -- COGS at time of sale
    gross_revenue DECIMAL(12,2) NOT NULL,      -- quantity * unit_price
    total_cogs DECIMAL(12,2) NOT NULL,         -- quantity * unit_cost
    gross_margin_dollars DECIMAL(12,2) NOT NULL, -- gross_revenue - total_cogs
    gross_margin_pct AS (CASE WHEN gross_revenue > 0 THEN (gross_margin_dollars / gross_revenue) * 100 ELSE 0 END),
    discount_amount DECIMAL(10,2) DEFAULT 0,
    source_system NVARCHAR(50),                -- 'square', 'shopify', 'lightspeed'
    etl_loaded_at DATETIME2 DEFAULT GETUTCDATE()
);

CREATE INDEX IX_fact_sales_date ON fact_sales(date_key);
CREATE INDEX IX_fact_sales_product ON fact_sales(product_id);
CREATE INDEX IX_fact_sales_location ON fact_sales(location_id);
CREATE INDEX IX_fact_sales_txn_date ON fact_sales(transaction_date);

CREATE TABLE fact_inventory_snapshot (
    snapshot_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    snapshot_date DATE NOT NULL,
    date_key INT NOT NULL REFERENCES dim_date(date_key),
    product_id NVARCHAR(100) NOT NULL REFERENCES dim_product(product_id),
    location_id NVARCHAR(100) NOT NULL REFERENCES dim_location(location_id),
    quantity_on_hand INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    inventory_value DECIMAL(12,2) NOT NULL,    -- quantity_on_hand * unit_cost
    etl_loaded_at DATETIME2 DEFAULT GETUTCDATE()
);

CREATE INDEX IX_inventory_snapshot_date ON fact_inventory_snapshot(snapshot_date);
CREATE INDEX IX_inventory_snapshot_product ON fact_inventory_snapshot(product_id);

-- ==============================================
-- KPI SUMMARY TABLE (refreshed daily by ETL)
-- ==============================================

CREATE TABLE kpi_product_performance (
    kpi_id BIGINT IDENTITY(1,1) PRIMARY KEY,
    calculation_date DATE NOT NULL,
    product_id NVARCHAR(100) NOT NULL REFERENCES dim_product(product_id),
    location_id NVARCHAR(100) NOT NULL REFERENCES dim_location(location_id),
    -- Sales Velocity
    units_sold_7d INT DEFAULT 0,
    units_sold_30d INT DEFAULT 0,
    units_sold_90d INT DEFAULT 0,
    avg_daily_velocity_30d DECIMAL(10,4) DEFAULT 0,
    velocity_trend_pct DECIMAL(10,2) DEFAULT 0,   -- 30d vs prior 30d
    -- Margin
    gross_margin_pct_30d DECIMAL(10,2) DEFAULT 0,
    gross_margin_dollars_30d DECIMAL(12,2) DEFAULT 0,
    margin_trend_pct DECIMAL(10,2) DEFAULT 0,      -- 30d vs prior 30d
    -- Inventory Turns
    annualized_cogs DECIMAL(14,2) DEFAULT 0,
    avg_inventory_value_30d DECIMAL(12,2) DEFAULT 0,
    inventory_turns_annualized DECIMAL(10,2) DEFAULT 0,
    -- GMROI
    gmroi DECIMAL(10,2) DEFAULT 0,
    -- Days of Supply
    current_on_hand INT DEFAULT 0,
    days_of_supply DECIMAL(10,1) DEFAULT 0,
    -- Sell-Through Rate
    sell_through_rate_30d DECIMAL(10,2) DEFAULT 0,
    -- Classification
    performance_tier NVARCHAR(20),  -- 'STAR', 'OPPORTUNITY', 'UNDERPERFORMER', 'DEAD_STOCK'
    calculated_at DATETIME2 DEFAULT GETUTCDATE()
);

CREATE INDEX IX_kpi_product_date ON kpi_product_performance(calculation_date);
CREATE INDEX IX_kpi_product_tier ON kpi_product_performance(performance_tier);

-- ==============================================
-- POPULATE dim_date (5 years: 2022-2027)
-- ==============================================

WITH DateCTE AS (
    SELECT CAST('2022-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt) FROM DateCTE WHERE dt < '2027-12-31'
)
INSERT INTO dim_date (date_key, full_date, day_of_week, day_of_month, week_of_year, month_num, month_name, quarter_num, year_num, is_weekend, fiscal_week, fiscal_month, fiscal_year)
SELECT 
    CAST(FORMAT(dt, 'yyyyMMdd') AS INT),
    dt,
    DATENAME(WEEKDAY, dt),
    DAY(dt),
    DATEPART(WEEK, dt),
    MONTH(dt),
    DATENAME(MONTH, dt),
    DATEPART(QUARTER, dt),
    YEAR(dt),
    CASE WHEN DATEPART(WEEKDAY, dt) IN (1,7) THEN 1 ELSE 0 END,
    DATEPART(WEEK, dt),   -- Adjust for client fiscal calendar if needed
    MONTH(dt),
    YEAR(dt)
FROM DateCTE
OPTION (MAXRECURSION 2200);

CREATE LOGIN fivetran_user WITH PASSWORD = '{STRONG_PASSWORD}';
CREATE USER fivetran_user FOR LOGIN fivetran_user;
ALTER ROLE db_owner ADD MEMBER fivetran_user;
-- Fivetran requires db_owner to manage schema creation. 
-- After initial sync stabilizes, you can reduce to db_datawriter + ddl_admin.

--- SQUARE CONNECTOR ---
Connector Type: Square
Auth: OAuth 2.0 (redirect to Square login)
Tables to sync: transactions, payments, catalog_items,
                 inventory_counts, locations
Sync frequency: Every 6 hours
Schema prefix: square_raw

--- SHOPIFY CONNECTOR (if applicable) ---
Connector Type: Shopify
Auth: Custom App API credentials
Tables to sync: orders, order_line_items, products,
                 variants, inventory_levels, locations
Sync frequency: Every 6 hours
Schema prefix: shopify_raw

--- QUICKBOOKS ONLINE CONNECTOR ---
Connector Type: QuickBooks Online
Auth: OAuth 2.0
Tables to sync: items, invoices, invoice_lines,
                 purchases, purchase_lines, accounts
Sync frequency: Every 12 hours
Schema prefix: qbo_raw

SELECT TABLE_SCHEMA, TABLE_NAME, 
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS c 
        WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME) as column_count
FROM INFORMATION_SCHEMA.TABLES t
WHERE TABLE_SCHEMA IN ('square_raw', 'shopify_raw', 'qbo_raw')
ORDER BY TABLE_SCHEMA, TABLE_NAME;

-- Check row counts on key tables
SELECT 'square_raw.transactions' as tbl, COUNT(*) as rows FROM square_raw.transactions
UNION ALL
SELECT 'square_raw.catalog_items', COUNT(*) FROM square_raw.catalog_items
UNION ALL
SELECT 'qbo_raw.items', COUNT(*) FROM qbo_raw.items;

-- ==============================================
-- STORED PROCEDURE: Load dim_product from Square + QBO
-- ==============================================
CREATE OR ALTER PROCEDURE sp_load_dim_product
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Merge products from Square catalog with costs from QBO
    MERGE dim_product AS target
    USING (
        SELECT 
            sq.id AS product_id,
            COALESCE(sq.item_data_name, 'UNKNOWN') AS sku,
            COALESCE(sq.item_data_name, 'Unnamed Product') AS product_name,
            sq.item_data_category_id AS category,
            NULL AS subcategory,
            NULL AS brand,
            NULL AS vendor_name,
            COALESCE(
                -- Try QBO cost first
                qbo.purchase_cost,
                -- Then Square cost if available  
                CAST(sq.item_data_variations_0_item_variation_data_price_money_amount AS DECIMAL(10,2)) / 100 * 0.5,
                -- Default to 50% of retail as fallback
                0
            ) AS unit_cost,
            CAST(sq.item_data_variations_0_item_variation_data_price_money_amount AS DECIMAL(10,2)) / 100 AS current_retail_price,
            CASE WHEN sq.is_deleted = 'true' THEN 0 ELSE 1 END AS is_active
        FROM square_raw.catalog_objects sq
        LEFT JOIN qbo_raw.items qbo ON LOWER(qbo.name) = LOWER(sq.item_data_name)
        WHERE sq.type = 'ITEM'
    ) AS source
    ON target.product_id = source.product_id
    WHEN MATCHED THEN
        UPDATE SET 
            product_name = source.product_name,
            unit_cost = source.unit_cost,
            current_retail_price = source.current_retail_price,
            is_active = source.is_active,
            updated_date = GETUTCDATE()
    WHEN NOT MATCHED THEN
        INSERT (product_id, sku, product_name, category, subcategory, brand, vendor_name, unit_cost, current_retail_price, is_active)
        VALUES (source.product_id, source.sku, source.product_name, source.category, source.subcategory, source.brand, source.vendor_name, source.unit_cost, source.current_retail_price, source.is_active);
    
    PRINT 'dim_product loaded: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows affected';
END;
-- ==============================================
-- STORED PROCEDURE: Load fact_sales from Square transactions
-- ==============================================
CREATE OR ALTER PROCEDURE sp_load_fact_sales
    @days_back INT = 7  -- Only process recent days for incremental loads
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @cutoff_date DATETIME2 = DATEADD(DAY, -@days_back, GETUTCDATE());
    
    INSERT INTO fact_sales (
        transaction_id, transaction_date, date_key, product_id, location_id,
        quantity_sold, unit_price, unit_cost, gross_revenue, total_cogs,
        gross_margin_dollars, discount_amount, source_system
    )
    SELECT 
        t.id AS transaction_id,
        CAST(t.created_at AS DATETIME2) AS transaction_date,
        CAST(FORMAT(CAST(t.created_at AS DATE), 'yyyyMMdd') AS INT) AS date_key,
        li.catalog_object_id AS product_id,
        t.location_id,
        COALESCE(CAST(li.quantity AS INT), 1) AS quantity_sold,
        CAST(li.base_price_money_amount AS DECIMAL(10,2)) / 100 AS unit_price,
        COALESCE(p.unit_cost, 0) AS unit_cost,
        CAST(li.total_money_amount AS DECIMAL(10,2)) / 100 AS gross_revenue,
        COALESCE(p.unit_cost, 0) * COALESCE(CAST(li.quantity AS INT), 1) AS total_cogs,
        (CAST(li.total_money_amount AS DECIMAL(10,2)) / 100) - (COALESCE(p.unit_cost, 0) * COALESCE(CAST(li.quantity AS INT), 1)) AS gross_margin_dollars,
        COALESCE(CAST(li.total_discount_money_amount AS DECIMAL(10,2)) / 100, 0) AS discount_amount,
        'square' AS source_system
    FROM square_raw.order_line_items li
    INNER JOIN square_raw.orders t ON li.order_id = t.id
    LEFT JOIN dim_product p ON li.catalog_object_id = p.product_id
    WHERE CAST(t.created_at AS DATETIME2) >= @cutoff_date
      AND t.state = 'COMPLETED'
      AND NOT EXISTS (
          SELECT 1 FROM fact_sales fs 
          WHERE fs.transaction_id = t.id 
            AND fs.product_id = li.catalog_object_id
      );
    
    PRINT 'fact_sales loaded: ' + CAST(@@ROWCOUNT AS VARCHAR) + ' new rows';
END;
-- ==============================================
-- STORED PROCEDURE: Daily inventory snapshot
-- ==============================================
CREATE OR ALTER PROCEDURE sp_load_inventory_snapshot
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @today DATE = CAST(GETUTCDATE() AS DATE);
    DECLARE @date_key INT = CAST(FORMAT(@today, 'yyyyMMdd') AS INT);
    
    -- Delete today's snapshot if re-running
    DELETE FROM fact_inventory_snapshot WHERE snapshot_date = @today;
    
    INSERT INTO fact_inventory_snapshot (
        snapshot_date, date_key, product_id, location_id,
        quantity_on_hand, unit_cost, inventory_value
    )
    SELECT 
        @today,
        @date_key,
        inv.catalog_object_id AS product_id,
        inv.location_id,
        CAST(inv.quantity AS INT) AS quantity_on_hand,
        COALESCE(p.unit_cost, 0) AS unit_cost,
        CAST(inv.quantity AS INT) * COALESCE(p.unit_cost, 0) AS inventory_value
    FROM square_raw.inventory_counts inv
    INNER JOIN dim_product p ON inv.catalog_object_id = p.product_id
    WHERE inv.state = 'IN_STOCK';
    
    PRINT 'Inventory snapshot loaded for ' + CAST(@today AS VARCHAR) + ': ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows';
END;
-- ==============================================
-- MASTER ETL ORCHESTRATION PROCEDURE
-- ==============================================
CREATE OR ALTER PROCEDURE sp_run_daily_etl
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @start DATETIME2 = GETUTCDATE();
    
    PRINT '=== RETAIL ANALYTICS ETL START: ' + CAST(@start AS VARCHAR) + ' ===';
    
    EXEC sp_load_dim_product;
    EXEC sp_load_fact_sales @days_back = 7;
    EXEC sp_load_inventory_snapshot;
    EXEC sp_calculate_product_kpis;  -- Defined in Custom AI Components section
    
    PRINT '=== ETL COMPLETE. Duration: ' + 
          CAST(DATEDIFF(SECOND, @start, GETUTCDATE()) AS VARCHAR) + ' seconds ===';
END;

az automation account create \
  --name auto-retail-{clientname} \
  --resource-group rg-retail-analytics-{clientname} \
  --location eastus

# PowerShell runbook that calls the stored procedure

$connectionString = "Server=sql-retail-{clientname}.database.windows.net;Database=RetailAnalyticsDB;User ID=sqladmin;Password={PASSWORD};Encrypt=True;TrustServerCertificate=False;"
$query = "EXEC sp_run_daily_etl;"

try {
    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
    $connection.Open()
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $command.CommandTimeout = 600  # 10 minute timeout
    $command.ExecuteNonQuery()
    Write-Output "ETL completed successfully at $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss UTC')"
} catch {
    Write-Error "ETL FAILED: $($_.Exception.Message)"
    throw
} finally {
    if ($connection.State -eq 'Open') { $connection.Close() }
}

az automation schedule create \
  --automation-account-name auto-retail-{clientname} \
  --resource-group rg-retail-analytics-{clientname} \
  --name DailyETL \
  --frequency Day \
  --interval 1 \
  --start-time "2025-01-01T09:00:00Z" \
  --timezone "America/New_York"

# create in Power BI Desktop → Modeling → New Measure

-- === SALES VELOCITY ===
Total Units Sold = SUM(fact_sales[quantity_sold])

Avg Daily Velocity = 
    DIVIDE(
        [Total Units Sold],
        DATEDIFF(MIN(fact_sales[transaction_date]), MAX(fact_sales[transaction_date]), DAY) + 1,
        0
    )

Velocity Trend % = 
    VAR CurrentPeriod = CALCULATE([Total Units Sold], DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]), -30, DAY))
    VAR PriorPeriod = CALCULATE([Total Units Sold], DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]) - 30, -30, DAY))
    RETURN DIVIDE(CurrentPeriod - PriorPeriod, PriorPeriod, 0) * 100

-- === MARGIN ===
Total Revenue = SUM(fact_sales[gross_revenue])
Total COGS = SUM(fact_sales[total_cogs])
Gross Margin $ = [Total Revenue] - [Total COGS]
Gross Margin % = DIVIDE([Gross Margin $], [Total Revenue], 0) * 100

Margin Trend % = 
    VAR CurrentMargin = CALCULATE([Gross Margin %], DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]), -30, DAY))
    VAR PriorMargin = CALCULATE([Gross Margin %], DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]) - 30, -30, DAY))
    RETURN CurrentMargin - PriorMargin

-- === INVENTORY TURNS ===
Annualized COGS = CALCULATE([Total COGS], DATESINPERIOD(dim_date[full_date], MAX(dim_date[full_date]), -365, DAY))

Avg Inventory Value = AVERAGEX(
    VALUES(fact_inventory_snapshot[snapshot_date]),
    CALCULATE(SUM(fact_inventory_snapshot[inventory_value]))
)

Inventory Turns = DIVIDE([Annualized COGS], [Avg Inventory Value], 0)

-- === GMROI ===
GMROI = DIVIDE([Gross Margin $], [Avg Inventory Value], 0)

-- === DAYS OF SUPPLY ===
Current On Hand = 
    CALCULATE(
        SUM(fact_inventory_snapshot[quantity_on_hand]),
        LASTDATE(fact_inventory_snapshot[snapshot_date])
    )

Days of Supply = DIVIDE([Current On Hand], [Avg Daily Velocity], 999)

-- === SELL-THROUGH RATE ===
Sell Through Rate = 
    DIVIDE([Total Units Sold], [Total Units Sold] + [Current On Hand], 0) * 100

-- === PERFORMANCE CLASSIFICATION ===
Performance Tier = 
    VAR Margin = [Gross Margin %]
    VAR Velocity = [Avg Daily Velocity]
    VAR MedianMargin = MEDIANX(ALL(dim_product), [Gross Margin %])
    VAR MedianVelocity = MEDIANX(ALL(dim_product), [Avg Daily Velocity])
    RETURN
    SWITCH(TRUE(),
        Velocity >= MedianVelocity && Margin >= MedianMargin, "⭐ STAR",
        Velocity >= MedianVelocity && Margin < MedianMargin, "🔄 OPPORTUNITY",
        Velocity < MedianVelocity && Margin >= MedianMargin, "⚠️ SLEEPER",
        "🔴 UNDERPERFORMER"
    )

SELECT 
    p.product_name,
    p.category,
    l.location_name,
    k.gross_margin_pct_30d,
    k.avg_daily_velocity_30d,
    k.days_of_supply,
    k.inventory_turns_annualized,
    k.gmroi,
    k.performance_tier
FROM kpi_product_performance k
JOIN dim_product p ON k.product_id = p.product_id
JOIN dim_location l ON k.location_id = l.location_id
WHERE k.calculation_date = CAST(GETUTCDATE() AS DATE)
  AND k.performance_tier = 'UNDERPERFORMER'
  AND k.current_on_hand > 0
  AND k.days_of_supply > 90
ORDER BY k.days_of_supply DESC;

SELECT 
    p.product_name,
    p.category,
    k.gross_margin_pct_30d AS current_margin_pct,
    k.margin_trend_pct AS margin_change,
    k.gross_margin_dollars_30d
FROM kpi_product_performance k
JOIN dim_product p ON k.product_id = p.product_id
WHERE k.calculation_date = CAST(GETUTCDATE() AS DATE)
  AND k.margin_trend_pct < -5  -- Margin dropped more than 5 points
  AND k.gross_margin_dollars_30d > 100  -- Only flag items with meaningful revenue
ORDER BY k.margin_trend_pct ASC;

-- items with zero sales in 90 days but positive on-hand inventory

SELECT 
    p.product_name,
    p.category,
    l.location_name,
    k.current_on_hand,
    k.days_of_supply,
    k.avg_daily_velocity_30d,
    snap.inventory_value
FROM kpi_product_performance k
JOIN dim_product p ON k.product_id = p.product_id
JOIN dim_location l ON k.location_id = l.location_id
JOIN fact_inventory_snapshot snap 
    ON k.product_id = snap.product_id 
    AND k.location_id = snap.location_id 
    AND snap.snapshot_date = CAST(GETUTCDATE() AS DATE)
WHERE k.calculation_date = CAST(GETUTCDATE() AS DATE)
  AND k.units_sold_90d = 0
  AND k.current_on_hand > 0
ORDER BY snap.inventory_value DESC;

az ad group create --display-name 'PBI-RetailAnalytics-Admins' --mail-nickname pbi-retail-admins
az ad group create --display-name 'PBI-RetailAnalytics-LocationManagers' --mail-nickname pbi-retail-managers
az ad group create --display-name 'PBI-RetailAnalytics-Viewers' --mail-nickname pbi-retail-viewers

// Role: LocationManager
// Table: dim_location
// DAX Filter:
[location_id] = LOOKUPVALUE(
   UserLocationMapping[location_id],
   UserLocationMapping[user_email],
   USERPRINCIPALNAME()
)

CREATE TABLE UserLocationMapping (
    user_email NVARCHAR(200) NOT NULL,
    location_id NVARCHAR(100) NOT NULL,
    PRIMARY KEY (user_email, location_id)
);

INSERT INTO UserLocationMapping VALUES
('manager1@clientdomain.com', 'LOC001'),
('manager2@clientdomain.com', 'LOC002');

-- 1. Data freshness check
SELECT 
    'fact_sales' AS table_name,
    MAX(transaction_date) AS latest_record,
    DATEDIFF(HOUR, MAX(transaction_date), GETUTCDATE()) AS hours_behind
FROM fact_sales
UNION ALL
SELECT 
    'fact_inventory_snapshot',
    MAX(CAST(snapshot_date AS DATETIME2)),
    DATEDIFF(HOUR, MAX(CAST(snapshot_date AS DATETIME2)), GETUTCDATE())
FROM fact_inventory_snapshot;

-- 2. Revenue reconciliation (compare to POS reports)
SELECT 
    FORMAT(transaction_date, 'yyyy-MM') AS month,
    SUM(gross_revenue) AS total_revenue,
    COUNT(DISTINCT transaction_id) AS transaction_count
FROM fact_sales
GROUP BY FORMAT(transaction_date, 'yyyy-MM')
ORDER BY month DESC;

-- 3. Margin sanity check (flag items with suspicious margins)
SELECT 
    p.product_name,
    p.unit_cost,
    p.current_retail_price,
    (p.current_retail_price - p.unit_cost) / NULLIF(p.current_retail_price, 0) * 100 AS expected_margin_pct,
    k.gross_margin_pct_30d AS actual_margin_pct
FROM kpi_product_performance k
JOIN dim_product p ON k.product_id = p.product_id
WHERE k.calculation_date = CAST(GETUTCDATE() AS DATE)
  AND (k.gross_margin_pct_30d > 90 OR k.gross_margin_pct_30d < 0)
ORDER BY k.gross_margin_pct_30d;

-- 4. Inventory value reconciliation
SELECT 
    SUM(inventory_value) AS total_inventory_value,
    COUNT(DISTINCT product_id) AS unique_skus,
    SUM(quantity_on_hand) AS total_units
FROM fact_inventory_snapshot
WHERE snapshot_date = CAST(GETUTCDATE() AS DATE);

CREATE OR ALTER PROCEDURE sp_calculate_product_kpis
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @today DATE = CAST(GETUTCDATE() AS DATE);
    DECLARE @date_key INT = CAST(FORMAT(@today, 'yyyyMMdd') AS INT);
    
    -- Delete today's KPIs if re-running
    DELETE FROM kpi_product_performance WHERE calculation_date = @today;
    
    -- Calculate KPIs for every active product × location combination
    INSERT INTO kpi_product_performance (
        calculation_date, product_id, location_id,
        units_sold_7d, units_sold_30d, units_sold_90d,
        avg_daily_velocity_30d, velocity_trend_pct,
        gross_margin_pct_30d, gross_margin_dollars_30d, margin_trend_pct,
        annualized_cogs, avg_inventory_value_30d, inventory_turns_annualized,
        gmroi, current_on_hand, days_of_supply, sell_through_rate_30d,
        performance_tier
    )
    SELECT 
        @today AS calculation_date,
        p.product_id,
        l.location_id,
        
        -- Units Sold (7d, 30d, 90d)
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -7, @today) THEN fs.quantity_sold END), 0) AS units_sold_7d,
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) AS units_sold_30d,
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -90, @today) THEN fs.quantity_sold END), 0) AS units_sold_90d,
        
        -- Avg Daily Velocity (30d)
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) / 30.0 AS avg_daily_velocity_30d,
        
        -- Velocity Trend: (current 30d units - prior 30d units) / prior 30d units * 100
        CASE 
            WHEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) > 0
            THEN (
                COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0)
                - COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0)
            ) * 100.0 / COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 1)
            ELSE 0
        END AS velocity_trend_pct,
        
        -- Gross Margin % (30d)
        CASE 
            WHEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_revenue END), 0) > 0
            THEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_margin_dollars END), 0) 
                 * 100.0 / SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_revenue END)
            ELSE 0
        END AS gross_margin_pct_30d,
        
        -- Gross Margin $ (30d)
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_margin_dollars END), 0) AS gross_margin_dollars_30d,
        
        -- Margin Trend (current 30d margin% - prior 30d margin%)
        CASE 
            WHEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_revenue END), 0) > 0
                 AND COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.gross_revenue END), 0) > 0
            THEN (
                COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_margin_dollars END), 0) * 100.0 
                / SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.gross_revenue END)
            ) - (
                COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.gross_margin_dollars END), 0) * 100.0 
                / SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -60, @today) AND fs.transaction_date < DATEADD(DAY, -30, @today) THEN fs.gross_revenue END)
            )
            ELSE 0
        END AS margin_trend_pct,
        
        -- Annualized COGS (trailing 12 months extrapolated)
        COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -365, @today) THEN fs.total_cogs END), 
                 SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -90, @today) THEN fs.total_cogs END) * 4) AS annualized_cogs,
        
        -- Avg Inventory Value (30d avg from snapshots)
        COALESCE(inv_avg.avg_inv_value, 0) AS avg_inventory_value_30d,
        
        -- Inventory Turns (annualized)
        CASE 
            WHEN COALESCE(inv_avg.avg_inv_value, 0) > 0
            THEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -365, @today) THEN fs.total_cogs END),
                          SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -90, @today) THEN fs.total_cogs END) * 4)
                 / inv_avg.avg_inv_value
            ELSE 0
        END AS inventory_turns_annualized,
        
        -- GMROI
        CASE 
            WHEN COALESCE(inv_avg.avg_inv_value, 0) > 0
            THEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -365, @today) THEN fs.gross_margin_dollars END),
                          SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -90, @today) THEN fs.gross_margin_dollars END) * 4)
                 / inv_avg.avg_inv_value
            ELSE 0
        END AS gmroi,
        
        -- Current On Hand
        COALESCE(inv_current.quantity_on_hand, 0) AS current_on_hand,
        
        -- Days of Supply
        CASE 
            WHEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) > 0
            THEN COALESCE(inv_current.quantity_on_hand, 0) * 30.0 
                 / SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END)
            ELSE 999  -- infinite days of supply if no sales
        END AS days_of_supply,
        
        -- Sell-Through Rate (30d)
        CASE 
            WHEN (COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) 
                  + COALESCE(inv_current.quantity_on_hand, 0)) > 0
            THEN COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) * 100.0 
                 / (COALESCE(SUM(CASE WHEN fs.transaction_date >= DATEADD(DAY, -30, @today) THEN fs.quantity_sold END), 0) 
                    + COALESCE(inv_current.quantity_on_hand, 0))
            ELSE 0
        END AS sell_through_rate_30d,
        
        -- Performance Tier Classification
        'PENDING' AS performance_tier  -- Will be updated in second pass
        
    FROM dim_product p
    CROSS JOIN dim_location l
    LEFT JOIN fact_sales fs 
        ON p.product_id = fs.product_id 
        AND l.location_id = fs.location_id
        AND fs.transaction_date >= DATEADD(DAY, -365, @today)
    LEFT JOIN (
        SELECT product_id, location_id, AVG(inventory_value) AS avg_inv_value
        FROM fact_inventory_snapshot
        WHERE snapshot_date >= DATEADD(DAY, -30, CAST(GETUTCDATE() AS DATE))
        GROUP BY product_id, location_id
    ) inv_avg ON p.product_id = inv_avg.product_id AND l.location_id = inv_avg.location_id
    LEFT JOIN (
        SELECT product_id, location_id, quantity_on_hand
        FROM fact_inventory_snapshot
        WHERE snapshot_date = CAST(GETUTCDATE() AS DATE)
    ) inv_current ON p.product_id = inv_current.product_id AND l.location_id = inv_current.location_id
    WHERE p.is_active = 1
    GROUP BY p.product_id, l.location_id, inv_avg.avg_inv_value, inv_current.quantity_on_hand;
    
    -- Second pass: Assign performance tiers using percentile-based classification
    -- Calculate median velocity and median margin across all active products
    DECLARE @median_velocity DECIMAL(10,4);
    DECLARE @median_margin DECIMAL(10,2);
    
    SELECT @median_velocity = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY avg_daily_velocity_30d)
                               OVER ()
    FROM kpi_product_performance 
    WHERE calculation_date = @today AND units_sold_30d > 0;
    
    SELECT @median_margin = PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gross_margin_pct_30d)
                             OVER ()
    FROM kpi_product_performance 
    WHERE calculation_date = @today AND units_sold_30d > 0;
    
    -- Assign tiers
    UPDATE kpi_product_performance
    SET performance_tier = 
        CASE 
            WHEN units_sold_90d = 0 AND current_on_hand > 0 THEN 'DEAD_STOCK'
            WHEN avg_daily_velocity_30d >= COALESCE(@median_velocity, 0) 
                 AND gross_margin_pct_30d >= COALESCE(@median_margin, 0) THEN 'STAR'
            WHEN avg_daily_velocity_30d >= COALESCE(@median_velocity, 0) 
                 AND gross_margin_pct_30d < COALESCE(@median_margin, 0) THEN 'OPPORTUNITY'
            WHEN avg_daily_velocity_30d < COALESCE(@median_velocity, 0) 
                 AND gross_margin_pct_30d >= COALESCE(@median_margin, 0) THEN 'SLEEPER'
            ELSE 'UNDERPERFORMER'
        END
    WHERE calculation_date = @today;
    
    -- Log summary
    SELECT performance_tier, COUNT(*) AS product_count
    FROM kpi_product_performance
    WHERE calculation_date = @today
    GROUP BY performance_tier;
    
    PRINT 'KPI calculation complete for ' + CAST(@today AS VARCHAR);
END;

CREATE OR ALTER PROCEDURE sp_calculate_seasonality_index
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Create or refresh the seasonality index table
    IF OBJECT_ID('product_seasonality_index', 'U') IS NULL
    BEGIN
        CREATE TABLE product_seasonality_index (
            product_id NVARCHAR(100) NOT NULL,
            month_num INT NOT NULL,
            seasonality_index DECIMAL(6,4) NOT NULL,  -- 1.0 = average month, >1 = peak, <1 = trough
            avg_monthly_units DECIMAL(10,2),
            data_months INT,  -- number of months of data used
            calculated_at DATETIME2 DEFAULT GETUTCDATE(),
            PRIMARY KEY (product_id, month_num)
        );
    END
    
    -- Truncate and recalculate
    TRUNCATE TABLE product_seasonality_index;
    
    -- Calculate seasonality index: ratio of each month's avg sales to the overall monthly average
    ;WITH monthly_sales AS (
        SELECT 
            product_id,
            MONTH(transaction_date) AS month_num,
            YEAR(transaction_date) AS year_num,
            SUM(quantity_sold) AS monthly_units
        FROM fact_sales
        WHERE transaction_date >= DATEADD(YEAR, -2, GETUTCDATE())  -- Use last 2 years
        GROUP BY product_id, MONTH(transaction_date), YEAR(transaction_date)
    ),
    avg_by_month AS (
        SELECT 
            product_id,
            month_num,
            AVG(CAST(monthly_units AS DECIMAL(10,2))) AS avg_monthly_units,
            COUNT(*) AS data_months
        FROM monthly_sales
        GROUP BY product_id, month_num
    ),
    overall_avg AS (
        SELECT 
            product_id,
            AVG(avg_monthly_units) AS overall_avg_monthly
        FROM avg_by_month
        GROUP BY product_id
    )
    INSERT INTO product_seasonality_index (product_id, month_num, seasonality_index, avg_monthly_units, data_months)
    SELECT 
        m.product_id,
        m.month_num,
        CASE 
            WHEN o.overall_avg_monthly > 0 THEN m.avg_monthly_units / o.overall_avg_monthly
            ELSE 1.0
        END AS seasonality_index,
        m.avg_monthly_units,
        m.data_months
    FROM avg_by_month m
    JOIN overall_avg o ON m.product_id = o.product_id;
    
    -- Apply seasonal adjustment to KPI performance tiers
    -- Products in their trough month (index < 0.3) with low velocity 
    -- should not be classified as UNDERPERFORMER or DEAD_STOCK
    UPDATE k
    SET k.performance_tier = 
        CASE 
            WHEN k.performance_tier IN ('UNDERPERFORMER', 'DEAD_STOCK')
                 AND s.seasonality_index < 0.3
                 AND s.data_months >= 2  -- Need at least 2 data points
            THEN 'SEASONAL_LOW'  -- Reclassify: expected seasonal dip, not actually underperforming
            ELSE k.performance_tier
        END
    FROM kpi_product_performance k
    LEFT JOIN product_seasonality_index s 
        ON k.product_id = s.product_id 
        AND MONTH(k.calculation_date) = s.month_num
    WHERE k.calculation_date = CAST(GETUTCDATE() AS DATE);
    
    PRINT 'Seasonality index calculated. Products reclassified as SEASONAL_LOW: ' + 
          CAST((SELECT COUNT(*) FROM kpi_product_performance 
                WHERE calculation_date = CAST(GETUTCDATE() AS DATE) 
                AND performance_tier = 'SEASONAL_LOW') AS VARCHAR);
END;

# paste into Power BI Copilot or GPT-based assistant

Analyze the following product performance data for {store_name} as of {date}:

### STAR Products (High Velocity + High Margin):
{star_products_table}

### UNDERPERFORMER Products (Low Velocity + Low Margin):
{underperformer_products_table}

### DEAD STOCK Products (Zero Sales in 90 Days with Inventory On Hand):
{dead_stock_table}

### MARGIN EROSION ALERTS (Margin dropped >5 points vs prior period):
{margin_erosion_table}

For each category, provide:
1. **Summary**: What's happening in 1-2 sentences
2. **Top 3 Action Items**: Specific, prioritized recommendations
3. **Financial Impact**: Estimated dollar impact of recommended actions
4. **Timeline**: When to act (immediate, this week, this month)

For STAR products, recommend: reorder quantities, whether to expand facings/placement, cross-sell opportunities.
For UNDERPERFORMERS, recommend: markdown percentages, bundle opportunities, vendor return eligibility, discontinuation.
For DEAD STOCK, recommend: clearance pricing (suggest specific discount %), donation write-off, or return-to-vendor.
For MARGIN EROSION, recommend: price adjustment, cost renegotiation with vendor, or substitution with higher-margin alternative.

### ⭐ Stars: Keep Winning
**Summary**: Your top performers are generating strong velocity and healthy margins...
**Actions**:
1. Increase reorder quantity for [Product X] by 25% — current 14-day supply is below your 30-day target
2. ...

### 🔴 Underperformers: Act Now
**Summary**: 12 items are consuming $4,200 in inventory capital while generating below-average returns...
**Actions**:
1. Mark down [Product Y] by 30% — at current velocity, you have 180 days of supply
2. ...

CREATE OR ALTER PROCEDURE sp_generate_reorder_recommendations
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Create output table if not exists
    IF OBJECT_ID('reorder_recommendations', 'U') IS NULL
    BEGIN
        CREATE TABLE reorder_recommendations (
            recommendation_id BIGINT IDENTITY(1,1) PRIMARY KEY,
            recommendation_date DATE NOT NULL,
            product_id NVARCHAR(100) NOT NULL,
            location_id NVARCHAR(100) NOT NULL,
            product_name NVARCHAR(255),
            current_on_hand INT,
            avg_daily_velocity DECIMAL(10,4),
            lead_time_days INT DEFAULT 14,  -- Configurable per vendor
            safety_stock_days INT DEFAULT 7, -- Buffer
            reorder_point INT,
            suggested_order_qty INT,
            estimated_cost DECIMAL(12,2),
            days_until_stockout DECIMAL(10,1),
            priority NVARCHAR(20),  -- URGENT, HIGH, NORMAL, LOW
            performance_tier NVARCHAR(20),
            seasonality_index DECIMAL(6,4),
            calculated_at DATETIME2 DEFAULT GETUTCDATE()
        );
    END
    
    DECLARE @today DATE = CAST(GETUTCDATE() AS DATE);
    
    -- Clear today's recommendations
    DELETE FROM reorder_recommendations WHERE recommendation_date = @today;
    
    INSERT INTO reorder_recommendations (
        recommendation_date, product_id, location_id, product_name,
        current_on_hand, avg_daily_velocity, lead_time_days, safety_stock_days,
        reorder_point, suggested_order_qty, estimated_cost,
        days_until_stockout, priority, performance_tier, seasonality_index
    )
    SELECT 
        @today,
        k.product_id,
        k.location_id,
        p.product_name,
        k.current_on_hand,
        k.avg_daily_velocity_30d,
        14 AS lead_time_days,  -- Default; TODO: configure per vendor via vendor_lead_times table
        7 AS safety_stock_days,
        
        -- Reorder Point = (daily velocity × lead time) + safety stock
        -- Adjusted for seasonality
        CEILING(
            k.avg_daily_velocity_30d 
            * COALESCE(si.seasonality_index, 1.0)  -- seasonal adjustment
            * (14 + 7)  -- lead time + safety stock days
        ) AS reorder_point,
        
        -- Suggested Order Qty = 30-day supply (adjusted for season) minus current stock + reorder point gap
        CASE 
            WHEN k.current_on_hand < CEILING(k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0) * 21)
            THEN CEILING(
                k.avg_daily_velocity_30d 
                * COALESCE(si.seasonality_index, 1.0) 
                * 30  -- 30-day supply target
            ) - k.current_on_hand
            ELSE 0
        END AS suggested_order_qty,
        
        -- Estimated cost
        CASE 
            WHEN k.current_on_hand < CEILING(k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0) * 21)
            THEN (CEILING(k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0) * 30) - k.current_on_hand) * p.unit_cost
            ELSE 0
        END AS estimated_cost,
        
        -- Days until stockout
        CASE 
            WHEN k.avg_daily_velocity_30d > 0
            THEN k.current_on_hand / (k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0))
            ELSE 999
        END AS days_until_stockout,
        
        -- Priority
        CASE 
            WHEN k.current_on_hand <= 0 AND k.avg_daily_velocity_30d > 0 THEN 'URGENT'
            WHEN k.current_on_hand / NULLIF(k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0), 0) < 14 
                 AND k.performance_tier IN ('STAR', 'OPPORTUNITY') THEN 'HIGH'
            WHEN k.current_on_hand / NULLIF(k.avg_daily_velocity_30d * COALESCE(si.seasonality_index, 1.0), 0) < 21 THEN 'NORMAL'
            ELSE 'LOW'
        END AS priority,
        
        k.performance_tier,
        COALESCE(si.seasonality_index, 1.0)
        
    FROM kpi_product_performance k
    JOIN dim_product p ON k.product_id = p.product_id
    LEFT JOIN product_seasonality_index si 
        ON k.product_id = si.product_id 
        AND MONTH(@today) = si.month_num
    WHERE k.calculation_date = @today
      AND k.avg_daily_velocity_30d > 0  -- Only recommend for items that actually sell
      AND p.is_active = 1
      AND k.performance_tier IN ('STAR', 'OPPORTUNITY', 'SLEEPER', 'SEASONAL_LOW');
    
    -- Remove recommendations where suggested qty = 0
    DELETE FROM reorder_recommendations 
    WHERE recommendation_date = @today AND suggested_order_qty <= 0;
    
    -- Summary output
    SELECT priority, COUNT(*) AS item_count, SUM(estimated_cost) AS total_cost
    FROM reorder_recommendations
    WHERE recommendation_date = @today
    GROUP BY priority
    ORDER BY 
        CASE priority WHEN 'URGENT' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'NORMAL' THEN 3 ELSE 4 END;
    
    PRINT 'Reorder recommendations generated: ' + 
          CAST((SELECT COUNT(*) FROM reorder_recommendations WHERE recommendation_date = @today) AS VARCHAR) + ' items';
END;

-- ==============================================
-- ADAPTER: Square → Common Schema
-- ==============================================
CREATE OR ALTER VIEW v_adapter_square_sales AS
SELECT 
    oli.order_id + '_' + CAST(ROW_NUMBER() OVER (PARTITION BY oli.order_id ORDER BY oli.uid) AS VARCHAR) AS transaction_line_id,
    o.id AS transaction_id,
    CAST(o.created_at AS DATETIME2) AS transaction_date,
    oli.catalog_object_id AS source_product_id,
    o.location_id AS source_location_id,
    COALESCE(CAST(oli.quantity AS INT), 1) AS quantity_sold,
    CAST(oli.base_price_money_amount AS DECIMAL(12,2)) / 100.0 AS unit_price,
    CAST(oli.total_money_amount AS DECIMAL(12,2)) / 100.0 AS line_total,
    COALESCE(CAST(oli.total_discount_money_amount AS DECIMAL(12,2)) / 100.0, 0) AS discount_amount,
    'square' AS source_system
FROM square_raw.order_line_items oli
JOIN square_raw.orders o ON oli.order_id = o.id
WHERE o.state = 'COMPLETED';

CREATE OR ALTER VIEW v_adapter_square_products AS
SELECT 
    co.id AS source_product_id,
    COALESCE(co.item_data_name, 'UNKNOWN-' + co.id) AS sku,
    COALESCE(co.item_data_name, 'Unnamed Product') AS product_name,
    co.item_data_category_id AS category_id,
    NULL AS subcategory,
    NULL AS brand,
    CAST(co.item_data_variations_0_item_variation_data_price_money_amount AS DECIMAL(12,2)) / 100.0 AS retail_price,
    CASE WHEN co.is_deleted = 'true' THEN 0 ELSE 1 END AS is_active,
    'square' AS source_system
FROM square_raw.catalog_objects co
WHERE co.type = 'ITEM';

CREATE OR ALTER VIEW v_adapter_square_inventory AS
SELECT 
    ic.catalog_object_id AS source_product_id,
    ic.location_id AS source_location_id,
    CAST(ic.quantity AS INT) AS quantity_on_hand,
    ic.state,
    'square' AS source_system
FROM square_raw.inventory_counts ic
WHERE ic.state = 'IN_STOCK';

-- ==============================================
-- ADAPTER: Shopify → Common Schema
-- ==============================================
CREATE OR ALTER VIEW v_adapter_shopify_sales AS
SELECT 
    CAST(oli.id AS VARCHAR) AS transaction_line_id,
    CAST(o.id AS VARCHAR) AS transaction_id,
    CAST(o.created_at AS DATETIME2) AS transaction_date,
    CAST(oli.product_id AS VARCHAR) AS source_product_id,
    CAST(o.location_id AS VARCHAR) AS source_location_id,
    oli.quantity AS quantity_sold,
    CAST(oli.price AS DECIMAL(12,2)) AS unit_price,
    CAST(oli.price AS DECIMAL(12,2)) * oli.quantity AS line_total,
    COALESCE(CAST(oli.total_discount AS DECIMAL(12,2)), 0) AS discount_amount,
    'shopify' AS source_system
FROM shopify_raw.order_line_items oli
JOIN shopify_raw.orders o ON oli.order_id = o.id
WHERE o.financial_status IN ('paid', 'partially_refunded');

CREATE OR ALTER VIEW v_adapter_shopify_products AS
SELECT 
    CAST(p.id AS VARCHAR) AS source_product_id,
    COALESCE(v.sku, CAST(p.id AS VARCHAR)) AS sku,
    p.title AS product_name,
    p.product_type AS category_id,
    NULL AS subcategory,
    p.vendor AS brand,
    CAST(v.price AS DECIMAL(12,2)) AS retail_price,
    CASE WHEN p.status = 'active' THEN 1 ELSE 0 END AS is_active,
    'shopify' AS source_system
FROM shopify_raw.products p
LEFT JOIN shopify_raw.variants v ON p.id = v.product_id;

CREATE OR ALTER VIEW v_adapter_shopify_inventory AS
SELECT 
    CAST(il.inventory_item_id AS VARCHAR) AS source_product_id,
    CAST(il.location_id AS VARCHAR) AS source_location_id,
    il.available AS quantity_on_hand,
    'IN_STOCK' AS state,
    'shopify' AS source_system
FROM shopify_raw.inventory_levels il
WHERE il.available > 0;

-- ==============================================
-- UNIFIED ADAPTER VIEWS (source-agnostic)
-- ==============================================
CREATE OR ALTER VIEW v_unified_sales AS
SELECT * FROM v_adapter_square_sales
UNION ALL
SELECT * FROM v_adapter_shopify_sales;
-- Add: UNION ALL SELECT * FROM v_adapter_lightspeed_sales when needed

CREATE OR ALTER VIEW v_unified_products AS
SELECT * FROM v_adapter_square_products
UNION ALL
SELECT * FROM v_adapter_shopify_products;

CREATE OR ALTER VIEW v_unified_inventory AS
SELECT * FROM v_adapter_square_inventory
UNION ALL
SELECT * FROM v_adapter_shopify_inventory;

-- Then modify sp_load_fact_sales and other ETL procedures to read from 
-- v_unified_sales / v_unified_products / v_unified_inventory instead of 
-- directly from square_raw.* tables. This makes the solution POS-agnostic.