-- seeds/common/mssql.sql — SELF-CONTAINED canonical seed (DDL + data + garbage).
-- Requires SQL Server 2022+ (GENERATE_SERIES). DDL from src/bin/seed/mssql.rs
-- SCHEMA_DDL; fill = the seeder's GENERATE_SERIES fast path; garbage appended.
DROP VIEW IF EXISTS orders_sparse_for_export;
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100) NOT NULL, email NVARCHAR(200) NOT NULL,
    age INT NULL, balance DECIMAL(12,2) NULL, is_active BIT NOT NULL, bio NVARCHAR(MAX) NULL,
    created_at DATETIME2(6) NOT NULL, updated_at DATETIME2(6) NOT NULL);
CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY, user_id INT NOT NULL, product NVARCHAR(200) NOT NULL,
    quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, status NVARCHAR(20) NOT NULL,
    notes NVARCHAR(MAX) NULL, ordered_at DATETIME2(6) NOT NULL, updated_at DATETIME2(6) NOT NULL);
CREATE TABLE events (
    id BIGINT IDENTITY(1,1) PRIMARY KEY, user_id INT NOT NULL, event_type NVARCHAR(50) NOT NULL,
    payload NVARCHAR(MAX) NULL, ip_address NVARCHAR(45) NULL, created_at DATETIME2(6) NOT NULL);
CREATE TABLE content_items (
    id BIGINT IDENTITY(1,1) PRIMARY KEY, title NVARCHAR(MAX) NOT NULL, body NVARCHAR(MAX) NOT NULL,
    raw_html NVARCHAR(MAX) NOT NULL, metadata NVARCHAR(MAX) NULL, tags NVARCHAR(MAX) NULL,
    author_name NVARCHAR(100) NOT NULL, author_email NVARCHAR(200) NOT NULL, source_url NVARCHAR(MAX) NULL,
    category NVARCHAR(50) NULL, status NVARCHAR(20) NOT NULL, priority INT NOT NULL, view_count INT NOT NULL,
    comment_count INT NOT NULL, word_count INT NOT NULL, language CHAR(2) NOT NULL,
    published_at DATETIME2(6) NULL, updated_at DATETIME2(6) NOT NULL, created_at DATETIME2(6) NOT NULL,
    extra_data NVARCHAR(MAX) NULL);
GO

GO

GO

GO

GO

GO

-- Sparse BIGINT ids — the 3-row sparse-footgun fixture (parity with PG/MySQL).
CREATE TABLE orders_sparse (id BIGINT PRIMARY KEY, payload NVARCHAR(MAX) NOT NULL);
GO
CREATE VIEW orders_sparse_for_export AS
SELECT id, payload, ROW_NUMBER() OVER (ORDER BY id) AS chunk_rownum FROM orders_sparse;
GO
INSERT INTO orders_sparse (id, payload) VALUES (1,N's0'),(2000001,N's1'),(4000001,N's2');
GO
-- Composite-cursor fixture (ADR-0007): ~33% NULL updated_at (deterministic value%3)
-- forces COALESCE(updated_at, created_at) progression.
CREATE TABLE orders_coalesce (
    id BIGINT IDENTITY(1,1) PRIMARY KEY, product NVARCHAR(200) NOT NULL, quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL, updated_at DATETIME2(6) NULL, created_at DATETIME2(6) NOT NULL);
CREATE INDEX idx_orders_coalesce_updated_at ON orders_coalesce(updated_at);
CREATE INDEX idx_orders_coalesce_created_at ON orders_coalesce(created_at);
GO
INSERT INTO orders_coalesce (product,quantity,price,updated_at,created_at)
SELECT CASE value%5 WHEN 0 THEN N'MacBook Pro 16"' WHEN 1 THEN N'Dell XPS 15' WHEN 2 THEN N'ThinkPad X1 Carbon' WHEN 3 THEN N'Surface Laptop' ELSE N'Ergonomic Chair' END,
       1+(value%10), ROUND(5+(value%4995)+0.0,2),
       CASE WHEN value%3=0 THEN NULL ELSE DATEADD(DAY,value%365,CONVERT(DATETIME2(6),'2025-01-01')) END,
       DATEADD(DAY,value%365,CONVERT(DATETIME2(6),'2024-01-01'))
FROM GENERATE_SERIES(CONVERT(BIGINT,1),CONVERT(BIGINT,150000));
GO
-- ─── Type-matrix: the FULL canonical SQL Server type matrix ────────────────
-- Ports tests/type_roundtrip/fixtures/mssql_schema.sql + _seed.sql — the
-- natively-supported subset (no json/enum/interval/array — T-SQL has no
-- first-class type). Mirrors the PG/MySQL matrices in SQL Server's own types:
-- smallint→bigint, tinyint, bit, decimal×3, real/float, date/time(6),
-- datetime2, datetimeoffset (NON-UTC offsets, incl. negative), nvarchar/
-- varchar/char, varbinary, uniqueidentifier, nullable + all-null.
CREATE TABLE rivet_type_matrix (
    id            BIGINT          NOT NULL PRIMARY KEY,
    c_smallint    SMALLINT        NOT NULL,
    c_int         INT             NOT NULL,
    c_bigint      BIGINT          NOT NULL,
    c_tinyint     TINYINT         NOT NULL,
    c_bit         BIT             NOT NULL,
    amount        DECIMAL(18,2)   NULL,
    fee           DECIMAL(20,6)   NULL,
    price         DECIMAL(10,2)   NULL,
    c_real        REAL            NOT NULL,
    c_float       FLOAT           NOT NULL,
    c_date        DATE            NOT NULL,
    c_time        TIME(6)         NOT NULL,
    created_at    DATETIME2       NOT NULL,
    created_at_tz DATETIMEOFFSET  NULL,
    label         NVARCHAR(200)   NOT NULL,
    c_varchar     VARCHAR(50)     NOT NULL,
    c_char        CHAR(10)        NOT NULL,
    raw_bytes     VARBINARY(8)    NOT NULL,
    uid           UNIQUEIDENTIFIER NOT NULL,
    c_nvarchar    NVARCHAR(100)   NOT NULL,
    note_nullable NVARCHAR(500)   NULL,
    note_all_null NVARCHAR(500)   NULL
);
GO
INSERT INTO rivet_type_matrix
    (id, c_smallint, c_int, c_bigint, c_tinyint, c_bit, amount, fee, price,
     c_real, c_float, c_date, c_time, created_at, created_at_tz, label, c_varchar, c_char,
     raw_bytes, uid, c_nvarchar, note_nullable, note_all_null)
VALUES
    -- Row 1 datetimeoffset carries a NON-UTC positive offset (13:45:30+05:30).
    (1, 30000, 2000000000, 9000000000, 200, 1, 1234.56, 0.000001, 9999.99,
     2.5, 3.141592653589793, '2026-01-15', '13:45:30.123456',
     '2026-01-15T13:45:30.123456', '2026-01-15 13:45:30.123456 +05:30',
     N'first label', 'ascii-vc', 'fixedchar',
     0x00112233445566FF, '6F9619FF-8B86-D011-B42D-00C04FC964FF',
     N'héllo wörld', N'present', NULL),
    -- Row 2 carries a NEGATIVE (western) offset -08:00 (the naive-parser trap).
    (2, -30000, -2000000000, -9000000000, 0, 0, -0.01, -0.000001, -0.01,
     -0.5, -2.5, '1999-12-31', '00:00:00.000000',
     '2000-01-01T00:00:00', '2000-01-01 00:00:00.000000 -08:00',
     N'second', 'vc2', 'c2',
     0xDEADBEEF, '00000000-0000-0000-0000-000000000001',
     N'unicode: ✓ 日本語', NULL, NULL),
    (3, 0, 0, 0, 255, 1, 0.00, 0.000000, 0.00,
     0.0, 0.0, '2026-06-05', '23:59:59.999999',
     '2026-06-05T23:59:59.999999', NULL, N'third', 'vc3', 'c3',
     0x00, '11111111-2222-3333-4444-555555555555',
     N'plain', N'also present', NULL);
GO

-- === GARBAGE PROFILE ===
GO
-- Obfuscated GARBAGE-profile fixture (SQL Server) — see dev/garbage/postgres.sql
-- for the rationale. Materialized in a NON-default schema `ext`. ZERO source
-- identity, ZERO real data. Deterministic + idempotent. Via `make seed-garbage`.

IF SCHEMA_ID('ext') IS NOT NULL
BEGIN
    DECLARE @drop NVARCHAR(MAX) = N'';
    SELECT @drop = @drop + N'DROP TABLE ext.' + QUOTENAME(name) + N';'
    FROM sys.tables WHERE schema_id = SCHEMA_ID('ext');
    EXEC(@drop);
END
GO

GO

-- Fleet majority: bigint PK + BOTH timestamps → keyset(id).
CREATE TABLE ext.bigint_pk_dual_ts (
    id BIGINT PRIMARY KEY,
    payload NVARCHAR(200) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at DATETIME2 NULL
);
INSERT INTO ext.bigint_pk_dual_ts (id, payload, updated_at)
SELECT value, CONCAT(N'row', value), DATEADD(MINUTE, -value, SYSUTCDATETIME())
FROM GENERATE_SERIES(CAST(1 AS BIGINT), CAST(150000 AS BIGINT));
GO

-- Int-PK minority.
CREATE TABLE ext.int_pk_dual_ts (
    id INT PRIMARY KEY,
    payload NVARCHAR(200) NOT NULL,
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at DATETIME2 NULL
);
INSERT INTO ext.int_pk_dual_ts (id, payload, updated_at)
SELECT value, CONCAT(N'row', value), DATEADD(MINUTE, -value, SYSUTCDATETIME())
FROM GENERATE_SERIES(1, 150000);
GO

-- Sparse key: span vastly exceeds the row count (the sparse-guard shape).
CREATE TABLE ext.sparse_key (id BIGINT PRIMARY KEY, payload INT NOT NULL);
INSERT INTO ext.sparse_key (id, payload)
SELECT 1 + value * 1000000, value FROM GENERATE_SERIES(CAST(0 AS BIGINT), CAST(149999 AS BIGINT));
GO

-- Scale-0 DECIMAL PK → an explicit range chunk_column must LOUDLY bail (#103).
CREATE TABLE ext.decimal_key (dkey DECIMAL(15,0) PRIMARY KEY, payload NVARCHAR(200) NOT NULL);
INSERT INTO ext.decimal_key (dkey, payload)
SELECT value, CONCAT(N'row', value) FROM GENERATE_SERIES(CAST(1 AS BIGINT), CAST(150000 AS BIGINT));
GO

-- Keyless, cursorless → full-mode fallback.
CREATE TABLE ext.no_pk_no_ts (label NVARCHAR(200) NOT NULL, amount INT NOT NULL);
GO

-- ── The MESSY reality (distilled from a real stuck run's state DB) ───────────
-- HISTORY/VERSION table: NO `id`, keyed by a non-PK integer `ref_id` (range-
-- chunked). Non-unique index on ref_id, no PK.
CREATE TABLE ext.ref_id_history (
    ref_id BIGINT NOT NULL,
    version INT NOT NULL,
    field NVARCHAR(200),
    field_cur NVARCHAR(10),
    sign SMALLINT NOT NULL DEFAULT 1,
    status NVARCHAR(50),
    -- money as DECIMAL(11,4) VALUE columns — the DOMINANT field profile (177 such
    -- columns across the real DB); init emits a `columns:` override for each.
    cart DECIMAL(11,4),
    earning DECIMAL(11,4),
    subtotal DECIMAL(11,4),
    total DECIMAL(11,4),
    amount_cur NVARCHAR(10),
    user_cur NCHAR(3),
    purchase_type NVARCHAR(50),
    created_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at DATETIME2
);
CREATE INDEX ix_ref_id_history_ref_id ON ext.ref_id_history (ref_id);
INSERT INTO ext.ref_id_history (ref_id, version, field, field_cur, sign, status, cart, earning, subtotal, total, amount_cur, user_cur, purchase_type, created_at, updated_at)
SELECT (value % 400) + 1, (value / 400) + 1, CONCAT(N'f', value), N'EUR', IIF(value % 2 = 0, 1, -1),
       CHOOSE(1 + value % 3, N'pending', N'done', N'void'),
       CAST((value % 900000 + 1) AS DECIMAL(11,4)) / 100, CAST((value % 500000 + 1) AS DECIMAL(11,4)) / 100,
       CAST((value % 300000 + 1) AS DECIMAL(11,4)) / 100, CAST((value % 1200000 + 1) AS DECIMAL(11,4)) / 100,
       N'USD', CHOOSE(1 + value % 3, N'EUR', N'USD', N'PLN'), CHOOSE(1 + value % 2, N'a', N'b'),
       DATEADD(MINUTE, -value, SYSUTCDATETIME()), DATEADD(SECOND, -value, SYSUTCDATETIME())
FROM GENERATE_SERIES(1, 150000);
GO

-- Keyed by `order_id` (NOT `id`): a unique index makes it keyset-able on a
-- non-`id` key. Wide-ish string columns.
CREATE TABLE ext.order_keyed (
    order_id BIGINT NOT NULL,
    md5 CHAR(32) NOT NULL,
    currency CHAR(3) NOT NULL,
    status NVARCHAR(50) NOT NULL,
    subid NVARCHAR(50),
    advcampaign_id INT
);
CREATE UNIQUE INDEX ux_order_keyed_order_id ON ext.order_keyed (order_id);
INSERT INTO ext.order_keyed (order_id, md5, currency, status, subid, advcampaign_id)
SELECT value, CONVERT(CHAR(32), HASHBYTES('MD5', CAST(value AS VARCHAR(20))), 2),
       CHOOSE(1 + value % 3, 'EUR', 'USD', 'PLN'), CHOOSE(1 + value % 2, N'approved', N'declined'),
       CONCAT(N'sub', value % 50), (value % 900) + 100
FROM GENERATE_SERIES(CAST(1 AS BIGINT), CAST(150000 AS BIGINT));
GO

-- HEAP: no PK, no index → full mode only.
CREATE TABLE ext.heap_no_key (payload NVARCHAR(200) NOT NULL, n INT NOT NULL);
GO

-- NAME-TRAP: a column literally named `id` that is NOT a PK and NOT indexed.
-- init picks keyset ONLY for a catalog single-column PRIMARY KEY (never by the
-- name `id`), so this must NOT scaffold `chunk_by_key: id` — it falls to range/
-- full and `check` flags the missing index. Proves every keyset key is indexed.
CREATE TABLE ext.unindexed_id (id BIGINT NOT NULL, label NVARCHAR(200) NOT NULL, amount INT NOT NULL);
INSERT INTO ext.unindexed_id (id, label, amount)
SELECT value, CONCAT(N'row', value), value % 100 FROM GENERATE_SERIES(CAST(1 AS BIGINT), CAST(150000 AS BIGINT));
GO

-- WIDE table: 160 int columns of 30-char names. The introspection STRING_AGG of
-- the names exceeds the nvarchar 4000-byte cap and raised Msg 9829 before the
-- CONVERT(nvarchar(max)) fix (#21) — every chunked/keyset plan on it failed.
DECLARE @cols NVARCHAR(MAX) = N'';
DECLARE @i INT = 0;
WHILE @i < 160
BEGIN
    SET @cols = @cols + N', ' + N'col_' + RIGHT(REPLICATE('0', 26) + CAST(@i AS VARCHAR(3)), 26) + N' INT NOT NULL DEFAULT 0';
    SET @i = @i + 1;
INSERT INTO ext.wide_cols (id) VALUES (1),(2),(3),(4),(5);
GO
