# script: scripts/02_cst_setup_legacy_data.sql | database: lab-cst | started (UTC): 2026-10-16 01:49:40
/* 02 - Setup on the CST database, while it is still UTC (TIME_ZONE = LOCAL)
   Creates schema tzlab and table tzlab.Orders, then inserts "legacy" rows the way a 5-year-old app would:
   naive datetime2 values coming from GETDATE() (UTC today).
   Three timestamp columns are captured on every row so we can prove what happened later:
     OrderDate        datetime2       DEFAULT GETDATE()            <- naive, follows the time zone setting
     OrderDateOffset  datetimeoffset  DEFAULT SYSDATETIMEOFFSET()  <- carries its own offset
     OrderDateUtc     datetime2       DEFAULT SYSUTCDATETIME()     <- ground truth, always UTC */
SET NOCOUNT ON;
PRINT '=== 02 Setup tzlab + legacy (UTC-era) data on ' + DB_NAME();
=== 02 Setup tzlab + legacy (UTC-era) data on lab-cst
-- Make the script re-runnable: start from UTC.
IF (SELECT CAST(value AS sysname) FROM sys.database_scoped_configurations WHERE name = 'TIME_ZONE') <> 'LOCAL'
    ALTER DATABASE SCOPED CONFIGURATION SET TIME_ZONE = LOCAL;
IF SCHEMA_ID('tzlab') IS NULL EXEC ('CREATE SCHEMA tzlab');
IF OBJECT_ID('tzlab.ProductPrice') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('tzlab.ProductPrice'), 'TableTemporalType') = 2
    ALTER TABLE tzlab.ProductPrice SET (SYSTEM_VERSIONING = OFF);
DROP TABLE IF EXISTS tzlab.ProductPrice, tzlab.ProductPriceHistory, tzlab.Orders_Migrated, tzlab.Orders,
                     tzlab.Events, tzlab.TriggerAudit, tzlab.TriggerTarget;
CREATE TABLE tzlab.Orders
(
    OrderID         int IDENTITY(1, 1) CONSTRAINT PK_tzlab_Orders PRIMARY KEY,
    Phase           varchar(20)       NOT NULL,   -- 'legacy-UTC' or 'after-CST'
    CustomerID      int               NOT NULL,
    OrderDate       datetime2(3)      NOT NULL CONSTRAINT DF_Orders_OrderDate       DEFAULT (GETDATE()),
    OrderDateOffset datetimeoffset(3) NOT NULL CONSTRAINT DF_Orders_OrderDateOffset DEFAULT (SYSDATETIMEOFFSET()),
    OrderDateUtc    datetime2(3)      NOT NULL CONSTRAINT DF_Orders_OrderDateUtc    DEFAULT (SYSUTCDATETIME()),
    TotalDue        money             NOT NULL
);
-- 10 legacy orders over the last 100 minutes (10 minutes apart), oldest first.
WITH src AS (
    SELECT TOP (10) CustomerID, TotalDue, ROW_NUMBER() OVER (ORDER BY SalesOrderID) AS rn
    FROM SalesLT.SalesOrderHeader ORDER BY SalesOrderID
)
INSERT tzlab.Orders (Phase, CustomerID, OrderDate, OrderDateOffset, OrderDateUtc, TotalDue)
SELECT 'legacy-UTC', CustomerID,
       DATEADD(MINUTE, -10 * (11 - rn), GETDATE()),
       DATEADD(MINUTE, -10 * (11 - rn), SYSDATETIMEOFFSET()),
       DATEADD(MINUTE, -10 * (11 - rn), SYSUTCDATETIME()),
       TotalDue
FROM src ORDER BY rn;
SELECT CURRENT_TIMEZONE_ID() AS tz_during_insert, COUNT(*) AS legacy_rows,
       MIN(OrderDate) AS min_OrderDate, MAX(OrderDate) AS max_OrderDate
FROM tzlab.Orders;
SELECT OrderID, Phase, OrderDate, OrderDateOffset, OrderDateUtc FROM tzlab.Orders ORDER BY OrderID;
tz_during_insert|legacy_rows|min_OrderDate|max_OrderDate
----------------|-----------|-------------|-------------
UTC|10|2026-10-16 00:09:40.970|2026-10-16 01:39:40.970

OrderID|Phase|OrderDate|OrderDateOffset|OrderDateUtc
-------|-----|---------|---------------|------------
1|legacy-UTC|2026-10-16 00:09:40.970|2026-10-16 00:09:40.974 +00:00|2026-10-16 00:09:40.974
2|legacy-UTC|2026-10-16 00:19:40.970|2026-10-16 00:19:40.974 +00:00|2026-10-16 00:19:40.974
3|legacy-UTC|2026-10-16 00:29:40.970|2026-10-16 00:29:40.974 +00:00|2026-10-16 00:29:40.974
4|legacy-UTC|2026-10-16 00:39:40.970|2026-10-16 00:39:40.974 +00:00|2026-10-16 00:39:40.974
5|legacy-UTC|2026-10-16 00:49:40.970|2026-10-16 00:49:40.974 +00:00|2026-10-16 00:49:40.974
6|legacy-UTC|2026-10-16 00:59:40.970|2026-10-16 00:59:40.974 +00:00|2026-10-16 00:59:40.974
7|legacy-UTC|2026-10-16 01:09:40.970|2026-10-16 01:09:40.974 +00:00|2026-10-16 01:09:40.974
8|legacy-UTC|2026-10-16 01:19:40.970|2026-10-16 01:19:40.974 +00:00|2026-10-16 01:19:40.974
9|legacy-UTC|2026-10-16 01:29:40.970|2026-10-16 01:29:40.974 +00:00|2026-10-16 01:29:40.974
10|legacy-UTC|2026-10-16 01:39:40.970|2026-10-16 01:39:40.974 +00:00|2026-10-16 01:39:40.974

# sqlcmd exit code: 0