/* ============================================================================
   EB_SampleDB — EndriasBridge Illustrative Multi-Industry Sample Database
   SQL Server edition
   ============================================================================

   WHAT THIS IS
   ------------
   A single, self-contained T-SQL script that builds a small but fully
   working, fully populated sample database called EB_SampleDB, spanning
   six industries: Healthcare, E-commerce, Finance, Manufacturing/IoT,
   Retail, and Government. It is meant as a quick, downloadable "kick the
   tires" dataset for evaluating EndriasBridge (a database migration tool)
   against a realistic-looking, multi-domain schema with foreign keys,
   check constraints, indexes, user-defined table types, scalar/table
   functions, stored procedures, views, and triggers. dbo.products is a
   system-versioned temporal table (auto history via dbo.products_history)
   so temporal-table migration/comparison can be exercised too. Also
   creates a SQL Agent operator, alert, and 3 jobs (server-level, msdb) to
   exercise the "Also migrate SQL Agent jobs, operators & alerts" feature,
   plus a Database Mail account/profile and a legacy-storage SSIS package
   to exercise the Health Check's Database Mail and SSIS packages
   detections.

   This is a SCALED-DOWN, ILLUSTRATIVE dataset (roughly a few hundred to a
   few thousand rows total across ~37 tables) — enough to demonstrate every
   relationship, join, and programmability object with real data, but small
   enough to load in seconds. It is NOT the full ~50 GB dataset.

   If you want a heavier-duty, large-scale version of this same schema
   (millions of rows, Faker-generated data, configurable via --scale),
   use the companion Python generator distributed alongside this script:
       sample_databases/generate_sqlserver.py
   Example:
       python generate_sqlserver.py --server <host> --database EB_SampleDB \
           --username <user> --password <pass> --scale 1.0

   HOW TO RUN THIS SCRIPT
   -----------------------
   Option A — sqlcmd (command line):
       sqlcmd -S localhost,1433 -U sa -P "YourStrong!Passw0rd" -C ^
           -i EB_SampleDB_sqlserver.sql

   Option B — SSMS:
       Open this file in SQL Server Management Studio and click Execute
       (F5). Make sure "SQLCMD Mode" is NOT required — this script uses
       only standard T-SQL batches separated by GO, which SSMS handles
       natively.

   Requirements: SQL Server 2016 or later (also works on Azure SQL
   Database and AWS RDS for SQL Server). No external tools or extensions
   required — this script is 100% self-contained.

   WHAT'S INSIDE (in execution order)
   -----------------------------------
     1. CREATE DATABASE EB_SampleDB (guarded, safe to re-run)
     2. CREATE TABLE — all 37 tables across the 6 industries
     3. CREATE INDEX — supporting indexes matching the base schema
     4. INSERT — hand-written, referentially-consistent sample data
        (parent tables populated before child tables, in FK order)
     5. FOREIGN KEY constraints tying every industry's tables together
     6. CHECK constraints on status/enum-like columns
     7. User-defined table types (dbo.PatientIdList, dbo.OrderItemInsert)
     8. Scalar & inline table-valued functions
     9. Stored procedures
    10. Views (new — join across tables for realistic reporting queries)
    11. Triggers (new — audit logging and business-rule automation)
    12. SQL Agent objects — 1 operator, 1 alert, 3 jobs (msdb, server-level;
        requires SQL Server Agent running)
    13. Database Mail — 1 account, 1 profile (msdb, server-level; metadata
        only, does not enable Database Mail XPs or send mail)
    14. SSIS package — 1 legacy-storage package (msdb, server-level;
        metadata only, not a real executable DTSX)

   This script is idempotent: every CREATE is guarded (IF NOT EXISTS /
   CREATE OR ALTER / OBJECT_ID checks) and every INSERT block is guarded
   by "IF NOT EXISTS (SELECT 1 FROM <table>)" so re-running this script
   against a database that's already populated will not create duplicate
   rows or error out. IDENTITY columns are used for all primary keys;
   sample data relies on default sequential IDENTITY assignment (1, 2, 3,
   ...) in insertion order — every FK value below was chosen to match the
   IDENTITY value that the referenced parent row will receive, assuming a
   single, uninterrupted run against a fresh (or already-matching) table.

   ============================================================================ */

SET NOCOUNT ON;
GO

/* ============================================================================
   0. DATABASE BOOTSTRAP
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N'EB_SampleDB')
BEGIN
    CREATE DATABASE EB_SampleDB;
END
GO

USE EB_SampleDB;
GO

PRINT '--- EB_SampleDB build starting ---';
GO

/* ============================================================================
   1. TABLES — HEALTHCARE
   ============================================================================ */

IF OBJECT_ID('dbo.providers', 'U') IS NULL
CREATE TABLE dbo.providers (
    provider_id     INT            IDENTITY(1,1) PRIMARY KEY,
    npi             VARCHAR(20)    NOT NULL,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    specialty       NVARCHAR(100)  NOT NULL,
    phone           VARCHAR(20)    NULL,
    email           NVARCHAR(200)  NULL,
    facility_name   NVARCHAR(200)  NULL,
    state           CHAR(2)        NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.patients', 'U') IS NULL
CREATE TABLE dbo.patients (
    patient_id      INT            IDENTITY(1,1) PRIMARY KEY,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    date_of_birth   DATE           NOT NULL,
    gender          CHAR(1)        NULL,
    ssn_hash        CHAR(64)       NULL,
    email           NVARCHAR(200)  NULL,
    phone           VARCHAR(20)    NULL,
    address_line1   NVARCHAR(200)  NULL,
    city            NVARCHAR(100)  NULL,
    state           CHAR(2)        NULL,
    zip_code        VARCHAR(10)    NULL,
    insurance_id    VARCHAR(50)    NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.appointments', 'U') IS NULL
CREATE TABLE dbo.appointments (
    appointment_id  INT            IDENTITY(1,1) PRIMARY KEY,
    patient_id      INT            NOT NULL,
    provider_id     INT            NOT NULL,
    appt_date       DATETIME2      NOT NULL,
    reason          NVARCHAR(500)  NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Scheduled',
    duration_min    SMALLINT       NULL,
    notes           NVARCHAR(1000) NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.diagnoses', 'U') IS NULL
CREATE TABLE dbo.diagnoses (
    diagnosis_id    INT            IDENTITY(1,1) PRIMARY KEY,
    patient_id      INT            NOT NULL,
    provider_id     INT            NOT NULL,
    icd10_code      VARCHAR(10)    NOT NULL,
    description     NVARCHAR(500)  NOT NULL,
    diagnosed_date  DATE           NOT NULL,
    resolved_date   DATE           NULL,
    severity        NVARCHAR(50)   NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.lab_results', 'U') IS NULL
CREATE TABLE dbo.lab_results (
    lab_result_id   BIGINT         IDENTITY(1,1) PRIMARY KEY,
    patient_id      INT            NOT NULL,
    provider_id     INT            NOT NULL,
    test_name       NVARCHAR(200)  NOT NULL,
    test_code       VARCHAR(20)    NULL,
    result_value    NVARCHAR(100)  NOT NULL,
    unit            NVARCHAR(50)   NULL,
    reference_range NVARCHAR(100)  NULL,
    abnormal_flag   BIT            NOT NULL DEFAULT 0,
    collected_at    DATETIME2      NOT NULL,
    resulted_at     DATETIME2      NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.prescriptions', 'U') IS NULL
CREATE TABLE dbo.prescriptions (
    rx_id           INT            IDENTITY(1,1) PRIMARY KEY,
    patient_id      INT            NOT NULL,
    provider_id     INT            NOT NULL,
    drug_name       NVARCHAR(200)  NOT NULL,
    ndc_code        VARCHAR(20)    NULL,
    dosage          NVARCHAR(100)  NULL,
    quantity        INT            NULL,
    refills         SMALLINT       NULL,
    prescribed_date DATE           NOT NULL,
    expiry_date     DATE           NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Active',
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.insurance_claims', 'U') IS NULL
CREATE TABLE dbo.insurance_claims (
    claim_id        INT            IDENTITY(1,1) PRIMARY KEY,
    patient_id      INT            NOT NULL,
    provider_id     INT            NOT NULL,
    claim_date      DATE           NOT NULL,
    service_date    DATE           NOT NULL,
    diagnosis_code  VARCHAR(10)    NOT NULL,
    procedure_code  VARCHAR(10)    NULL,
    billed_amount   DECIMAL(12,2)  NOT NULL,
    allowed_amount  DECIMAL(12,2)  NULL,
    paid_amount     DECIMAL(12,2)  NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Pending',
    payer_name      NVARCHAR(200)  NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* ============================================================================
   1. TABLES — E-COMMERCE
   ============================================================================ */

IF OBJECT_ID('dbo.categories', 'U') IS NULL
CREATE TABLE dbo.categories (
    category_id     INT            IDENTITY(1,1) PRIMARY KEY,
    name            NVARCHAR(200)  NOT NULL,
    parent_id       INT            NULL,
    description     NVARCHAR(1000) NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

-- System-versioned temporal table: SQL Server auto-tracks every UPDATE/DELETE
-- to dbo.products in dbo.products_history, so price/brand/etc. changes over
-- time are queryable with "... FOR SYSTEM_TIME AS OF/BETWEEN/ALL" without any
-- application-level audit logging. Included as a sample so EndriasBridge can
-- be exercised against temporal tables (Compare/migration already exclude
-- the auto-generated history table from the user-table list via
-- temporal_type <> 1 - see db_compare.py).
IF OBJECT_ID('dbo.products', 'U') IS NULL
CREATE TABLE dbo.products (
    product_id      INT            IDENTITY(1,1) PRIMARY KEY,
    sku             VARCHAR(50)    NOT NULL,
    name            NVARCHAR(500)  NOT NULL,
    category_id     INT            NULL,
    brand           NVARCHAR(200)  NULL,
    description     NVARCHAR(2000) NULL,
    unit_price      DECIMAL(12,2)  NOT NULL,
    cost_price      DECIMAL(12,2)  NULL,
    weight_kg       DECIMAL(8,3)   NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME(),
    sys_start_time  DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL DEFAULT SYSUTCDATETIME(),
    sys_end_time    DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (sys_start_time, sys_end_time)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.products_history));
GO

IF OBJECT_ID('dbo.customers', 'U') IS NULL
CREATE TABLE dbo.customers (
    customer_id     INT            IDENTITY(1,1) PRIMARY KEY,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    email           NVARCHAR(300)  NOT NULL,
    phone           VARCHAR(20)    NULL,
    address_line1   NVARCHAR(200)  NULL,
    city            NVARCHAR(100)  NULL,
    state           NVARCHAR(100)  NULL,
    zip_code        VARCHAR(20)    NULL,
    country         VARCHAR(3)     NOT NULL DEFAULT 'USA',
    loyalty_points  INT            NOT NULL DEFAULT 0,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.orders', 'U') IS NULL
CREATE TABLE dbo.orders (
    order_id        INT            IDENTITY(1,1) PRIMARY KEY,
    customer_id     INT            NOT NULL,
    order_date      DATETIME2      NOT NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Pending',
    shipping_addr   NVARCHAR(500)  NULL,
    subtotal        DECIMAL(14,2)  NOT NULL,
    tax_amount      DECIMAL(12,2)  NOT NULL DEFAULT 0,
    shipping_cost   DECIMAL(10,2)  NOT NULL DEFAULT 0,
    total_amount    DECIMAL(14,2)  NOT NULL,
    channel         NVARCHAR(50)   NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.order_items', 'U') IS NULL
CREATE TABLE dbo.order_items (
    order_item_id   INT            IDENTITY(1,1) PRIMARY KEY,
    order_id        INT            NOT NULL,
    product_id      INT            NOT NULL,
    quantity        INT            NOT NULL,
    unit_price      DECIMAL(12,2)  NOT NULL,
    discount        DECIMAL(10,2)  NOT NULL DEFAULT 0,
    line_total      DECIMAL(14,2)  NOT NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.reviews', 'U') IS NULL
CREATE TABLE dbo.reviews (
    review_id       INT            IDENTITY(1,1) PRIMARY KEY,
    product_id      INT            NOT NULL,
    customer_id     INT            NOT NULL,
    rating          TINYINT        NOT NULL,
    title           NVARCHAR(300)  NULL,
    body            NVARCHAR(2000) NULL,
    verified        BIT            NOT NULL DEFAULT 0,
    helpful_votes   INT            NOT NULL DEFAULT 0,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.cart_events', 'U') IS NULL
CREATE TABLE dbo.cart_events (
    event_id        BIGINT         IDENTITY(1,1) PRIMARY KEY,
    customer_id     INT            NOT NULL,
    product_id      INT            NOT NULL,
    event_type      NVARCHAR(50)   NOT NULL,
    quantity        INT            NULL,
    session_id      VARCHAR(64)    NULL,
    device_type     NVARCHAR(50)   NULL,
    event_ts        DATETIME2      NOT NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* ============================================================================
   1. TABLES — FINANCE
   ============================================================================ */

IF OBJECT_ID('dbo.fin_accounts', 'U') IS NULL
CREATE TABLE dbo.fin_accounts (
    account_id      INT            IDENTITY(1,1) PRIMARY KEY,
    account_number  VARCHAR(30)    NOT NULL,
    account_type    NVARCHAR(50)   NOT NULL,
    owner_name      NVARCHAR(200)  NOT NULL,
    owner_email     NVARCHAR(300)  NULL,
    balance         DECIMAL(18,2)  NOT NULL DEFAULT 0,
    currency        CHAR(3)        NOT NULL DEFAULT 'USD',
    opened_date     DATE           NOT NULL,
    closed_date     DATE           NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Active',
    routing_number  VARCHAR(20)    NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.transactions', 'U') IS NULL
CREATE TABLE dbo.transactions (
    txn_id          BIGINT         IDENTITY(1,1) PRIMARY KEY,
    account_id      INT            NOT NULL,
    txn_date        DATETIME2      NOT NULL,
    txn_type        NVARCHAR(50)   NOT NULL,
    amount          DECIMAL(18,2)  NOT NULL,
    currency        CHAR(3)        NOT NULL DEFAULT 'USD',
    merchant_name   NVARCHAR(300)  NULL,
    merchant_cat    NVARCHAR(100)  NULL,
    channel         NVARCHAR(50)   NULL,
    reference_no    VARCHAR(50)    NULL,
    balance_after   DECIMAL(18,2)  NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.loans', 'U') IS NULL
CREATE TABLE dbo.loans (
    loan_id         INT            IDENTITY(1,1) PRIMARY KEY,
    account_id      INT            NOT NULL,
    loan_type       NVARCHAR(100)  NOT NULL,
    principal       DECIMAL(18,2)  NOT NULL,
    interest_rate   DECIMAL(6,4)   NOT NULL,
    term_months     INT            NOT NULL,
    start_date      DATE           NOT NULL,
    maturity_date   DATE           NOT NULL,
    outstanding_bal DECIMAL(18,2)  NOT NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Current',
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.fraud_alerts', 'U') IS NULL
CREATE TABLE dbo.fraud_alerts (
    alert_id        INT            IDENTITY(1,1) PRIMARY KEY,
    txn_id          BIGINT         NULL,
    account_id      INT            NOT NULL,
    alert_type      NVARCHAR(100)  NOT NULL,
    risk_score      DECIMAL(5,2)   NULL,
    detected_at     DATETIME2      NOT NULL,
    resolved_at     DATETIME2      NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Open',
    notes           NVARCHAR(1000) NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.market_prices', 'U') IS NULL
CREATE TABLE dbo.market_prices (
    price_id        INT            IDENTITY(1,1) PRIMARY KEY,
    ticker          VARCHAR(10)    NOT NULL,
    price_date      DATE           NOT NULL,
    open_price      DECIMAL(14,4)  NULL,
    high_price      DECIMAL(14,4)  NULL,
    low_price       DECIMAL(14,4)  NULL,
    close_price     DECIMAL(14,4)  NOT NULL,
    volume          BIGINT         NULL,
    adjusted_close  DECIMAL(14,4)  NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.audit_log', 'U') IS NULL
CREATE TABLE dbo.audit_log (
    audit_id        BIGINT         IDENTITY(1,1) PRIMARY KEY,
    table_name      NVARCHAR(200)  NOT NULL,
    operation       NVARCHAR(20)   NOT NULL,
    record_id       BIGINT         NULL,
    changed_by      NVARCHAR(200)  NULL,
    changed_at      DATETIME2      NOT NULL,
    old_values      NVARCHAR(2000) NULL,
    new_values      NVARCHAR(2000) NULL,
    ip_address      VARCHAR(45)    NULL,
    session_id      VARCHAR(100)   NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* ============================================================================
   1. TABLES — MANUFACTURING / IoT
   ============================================================================ */

IF OBJECT_ID('dbo.machines', 'U') IS NULL
CREATE TABLE dbo.machines (
    machine_id      INT            IDENTITY(1,1) PRIMARY KEY,
    machine_code    VARCHAR(50)    NOT NULL,
    machine_name    NVARCHAR(200)  NOT NULL,
    machine_type    NVARCHAR(100)  NOT NULL,
    location        NVARCHAR(200)  NULL,
    manufacturer    NVARCHAR(200)  NULL,
    model_no        VARCHAR(100)   NULL,
    install_date    DATE           NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Operational',
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.sensors', 'U') IS NULL
CREATE TABLE dbo.sensors (
    sensor_id       INT            IDENTITY(1,1) PRIMARY KEY,
    machine_id      INT            NOT NULL,
    sensor_code     VARCHAR(50)    NOT NULL,
    sensor_type     NVARCHAR(100)  NOT NULL,
    unit            NVARCHAR(50)   NULL,
    min_threshold   DECIMAL(12,4)  NULL,
    max_threshold   DECIMAL(12,4)  NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.production_batches', 'U') IS NULL
CREATE TABLE dbo.production_batches (
    batch_id        INT            IDENTITY(1,1) PRIMARY KEY,
    batch_number    VARCHAR(50)    NOT NULL,
    machine_id      INT            NOT NULL,
    product_code    VARCHAR(50)    NOT NULL,
    start_time      DATETIME2      NOT NULL,
    end_time        DATETIME2      NULL,
    planned_qty     INT            NOT NULL,
    actual_qty      INT            NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'In Progress',
    operator_id     INT            NULL,
    notes           NVARCHAR(1000) NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.sensor_readings', 'U') IS NULL
CREATE TABLE dbo.sensor_readings (
    reading_id      BIGINT         IDENTITY(1,1) PRIMARY KEY,
    sensor_id       INT            NOT NULL,
    machine_id      INT            NOT NULL,
    reading_ts      DATETIME2      NOT NULL,
    value           DECIMAL(14,4)  NOT NULL,
    quality_flag    TINYINT        NOT NULL DEFAULT 0,
    raw_value       DECIMAL(14,4)  NULL,
    unit            NVARCHAR(20)   NULL,
    batch_id        INT            NULL,
    anomaly         BIT            NOT NULL DEFAULT 0
);
GO

IF OBJECT_ID('dbo.quality_checks', 'U') IS NULL
CREATE TABLE dbo.quality_checks (
    qc_id           INT            IDENTITY(1,1) PRIMARY KEY,
    batch_id        INT            NOT NULL,
    check_type      NVARCHAR(100)  NOT NULL,
    checked_at      DATETIME2      NOT NULL,
    result          NVARCHAR(50)   NOT NULL,
    defect_count    INT            NOT NULL DEFAULT 0,
    sample_size     INT            NULL,
    inspector_name  NVARCHAR(200)  NULL,
    notes           NVARCHAR(1000) NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* ============================================================================
   1. TABLES — RETAIL
   ============================================================================ */

IF OBJECT_ID('dbo.stores', 'U') IS NULL
CREATE TABLE dbo.stores (
    store_id        INT            IDENTITY(1,1) PRIMARY KEY,
    store_code      VARCHAR(20)    NOT NULL,
    store_name      NVARCHAR(200)  NOT NULL,
    address         NVARCHAR(300)  NULL,
    city            NVARCHAR(100)  NULL,
    state           CHAR(2)        NULL,
    zip_code        VARCHAR(10)    NULL,
    phone           VARCHAR(20)    NULL,
    manager_name    NVARCHAR(200)  NULL,
    sq_footage      INT            NULL,
    active          BIT            NOT NULL DEFAULT 1,
    opened_date     DATE           NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.inventory', 'U') IS NULL
CREATE TABLE dbo.inventory (
    inventory_id    INT            IDENTITY(1,1) PRIMARY KEY,
    store_id        INT            NOT NULL,
    product_id      INT            NOT NULL,
    quantity_on_hand INT           NOT NULL DEFAULT 0,
    reorder_point   INT            NULL,
    reorder_qty     INT            NULL,
    last_replenish  DATETIME2      NULL,
    unit_cost       DECIMAL(12,2)  NULL,
    updated_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.pos_transactions', 'U') IS NULL
CREATE TABLE dbo.pos_transactions (
    pos_txn_id      INT            IDENTITY(1,1) PRIMARY KEY,
    store_id        INT            NOT NULL,
    register_no     SMALLINT       NOT NULL,
    txn_date        DATETIME2      NOT NULL,
    cashier_id      INT            NULL,
    customer_id     INT            NULL,
    subtotal        DECIMAL(12,2)  NOT NULL,
    tax_amount      DECIMAL(10,2)  NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10,2)  NOT NULL DEFAULT 0,
    total_amount    DECIMAL(12,2)  NOT NULL,
    payment_method  NVARCHAR(50)   NULL,
    loyalty_used    INT            NOT NULL DEFAULT 0,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.pos_items', 'U') IS NULL
CREATE TABLE dbo.pos_items (
    pos_item_id     INT            IDENTITY(1,1) PRIMARY KEY,
    pos_txn_id      INT            NOT NULL,
    product_id      INT            NOT NULL,
    quantity        INT            NOT NULL,
    unit_price      DECIMAL(12,2)  NOT NULL,
    discount        DECIMAL(10,2)  NOT NULL DEFAULT 0,
    line_total      DECIMAL(12,2)  NOT NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.loyalty_members', 'U') IS NULL
CREATE TABLE dbo.loyalty_members (
    member_id       INT            IDENTITY(1,1) PRIMARY KEY,
    customer_id     INT            NULL,
    member_number   VARCHAR(30)    NOT NULL,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    email           NVARCHAR(300)  NULL,
    tier            NVARCHAR(50)   NOT NULL DEFAULT 'Bronze',
    points_balance  INT            NOT NULL DEFAULT 0,
    total_spent     DECIMAL(14,2)  NOT NULL DEFAULT 0,
    enrolled_date   DATE           NOT NULL,
    last_activity   DATETIME2      NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.promotions', 'U') IS NULL
CREATE TABLE dbo.promotions (
    promo_id        INT            IDENTITY(1,1) PRIMARY KEY,
    promo_code      VARCHAR(30)    NOT NULL,
    name            NVARCHAR(200)  NOT NULL,
    discount_type   NVARCHAR(50)   NOT NULL,
    discount_value  DECIMAL(10,4)  NOT NULL,
    min_purchase    DECIMAL(12,2)  NULL,
    start_date      DATE           NOT NULL,
    end_date        DATE           NULL,
    usage_limit     INT            NULL,
    times_used      INT            NOT NULL DEFAULT 0,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

/* ============================================================================
   1. TABLES — GOVERNMENT
   ============================================================================ */

IF OBJECT_ID('dbo.gov_employees', 'U') IS NULL
CREATE TABLE dbo.gov_employees (
    employee_id     INT            IDENTITY(1,1) PRIMARY KEY,
    employee_no     VARCHAR(20)    NOT NULL,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    department      NVARCHAR(200)  NOT NULL,
    job_title       NVARCHAR(200)  NULL,
    hire_date       DATE           NOT NULL,
    salary          DECIMAL(12,2)  NULL,
    pay_grade       VARCHAR(10)    NULL,
    supervisor_id   INT            NULL,
    active          BIT            NOT NULL DEFAULT 1,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.citizens', 'U') IS NULL
CREATE TABLE dbo.citizens (
    citizen_id      INT            IDENTITY(1,1) PRIMARY KEY,
    first_name      NVARCHAR(100)  NOT NULL,
    last_name       NVARCHAR(100)  NOT NULL,
    date_of_birth   DATE           NULL,
    id_number       VARCHAR(30)    NULL,
    email           NVARCHAR(300)  NULL,
    phone           VARCHAR(20)    NULL,
    address         NVARCHAR(400)  NULL,
    city            NVARCHAR(100)  NULL,
    state           CHAR(2)        NULL,
    zip_code        VARCHAR(10)    NULL,
    registered_date DATE           NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.permits', 'U') IS NULL
CREATE TABLE dbo.permits (
    permit_id       INT            IDENTITY(1,1) PRIMARY KEY,
    permit_number   VARCHAR(30)    NOT NULL,
    citizen_id      INT            NULL,
    permit_type     NVARCHAR(100)  NOT NULL,
    description     NVARCHAR(500)  NULL,
    issue_date      DATE           NULL,
    expiry_date     DATE           NULL,
    issued_by       INT            NULL,
    fee_amount      DECIMAL(10,2)  NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Pending',
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.violations', 'U') IS NULL
CREATE TABLE dbo.violations (
    violation_id    INT            IDENTITY(1,1) PRIMARY KEY,
    citizen_id      INT            NULL,
    violation_code  VARCHAR(20)    NOT NULL,
    description     NVARCHAR(500)  NOT NULL,
    violation_date  DATE           NOT NULL,
    location        NVARCHAR(300)  NULL,
    fine_amount     DECIMAL(10,2)  NULL,
    paid_amount     DECIMAL(10,2)  NULL DEFAULT 0,
    officer_id      INT            NULL,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Outstanding',
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.public_contracts', 'U') IS NULL
CREATE TABLE dbo.public_contracts (
    contract_id     INT            IDENTITY(1,1) PRIMARY KEY,
    contract_number VARCHAR(30)    NOT NULL,
    vendor_name     NVARCHAR(300)  NOT NULL,
    department      NVARCHAR(200)  NOT NULL,
    description     NVARCHAR(1000) NULL,
    start_date      DATE           NOT NULL,
    end_date        DATE           NULL,
    contract_value  DECIMAL(18,2)  NOT NULL,
    paid_to_date    DECIMAL(18,2)  NOT NULL DEFAULT 0,
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Active',
    awarded_by      INT            NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

IF OBJECT_ID('dbo.service_requests', 'U') IS NULL
CREATE TABLE dbo.service_requests (
    request_id      INT            IDENTITY(1,1) PRIMARY KEY,
    citizen_id      INT            NULL,
    request_type    NVARCHAR(100)  NOT NULL,
    description     NVARCHAR(1000) NULL,
    submitted_at    DATETIME2      NOT NULL,
    resolved_at     DATETIME2      NULL,
    assigned_to     INT            NULL,
    priority        NVARCHAR(20)   NOT NULL DEFAULT 'Normal',
    status          NVARCHAR(50)   NOT NULL DEFAULT 'Open',
    resolution_notes NVARCHAR(1000) NULL,
    created_at      DATETIME2      NOT NULL DEFAULT SYSUTCDATETIME()
);
GO

PRINT 'Tables created (37 tables across 6 industries)';
GO

/* ============================================================================
   2. INDEXES
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_appt_patient' AND object_id = OBJECT_ID('dbo.appointments'))
    CREATE INDEX ix_appt_patient ON dbo.appointments (patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_appt_provider' AND object_id = OBJECT_ID('dbo.appointments'))
    CREATE INDEX ix_appt_provider ON dbo.appointments (provider_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_appt_date' AND object_id = OBJECT_ID('dbo.appointments'))
    CREATE INDEX ix_appt_date ON dbo.appointments (appt_date);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_diag_patient' AND object_id = OBJECT_ID('dbo.diagnoses'))
    CREATE INDEX ix_diag_patient ON dbo.diagnoses (patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_lab_patient' AND object_id = OBJECT_ID('dbo.lab_results'))
    CREATE INDEX ix_lab_patient ON dbo.lab_results (patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_lab_collected' AND object_id = OBJECT_ID('dbo.lab_results'))
    CREATE INDEX ix_lab_collected ON dbo.lab_results (collected_at);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_rx_patient' AND object_id = OBJECT_ID('dbo.prescriptions'))
    CREATE INDEX ix_rx_patient ON dbo.prescriptions (patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_claim_patient' AND object_id = OBJECT_ID('dbo.insurance_claims'))
    CREATE INDEX ix_claim_patient ON dbo.insurance_claims (patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_claim_date' AND object_id = OBJECT_ID('dbo.insurance_claims'))
    CREATE INDEX ix_claim_date ON dbo.insurance_claims (claim_date);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_prod_category' AND object_id = OBJECT_ID('dbo.products'))
    CREATE INDEX ix_prod_category ON dbo.products (category_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_prod_sku' AND object_id = OBJECT_ID('dbo.products'))
    CREATE INDEX ix_prod_sku ON dbo.products (sku);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_order_customer' AND object_id = OBJECT_ID('dbo.orders'))
    CREATE INDEX ix_order_customer ON dbo.orders (customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_order_date' AND object_id = OBJECT_ID('dbo.orders'))
    CREATE INDEX ix_order_date ON dbo.orders (order_date);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_oi_order' AND object_id = OBJECT_ID('dbo.order_items'))
    CREATE INDEX ix_oi_order ON dbo.order_items (order_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_oi_product' AND object_id = OBJECT_ID('dbo.order_items'))
    CREATE INDEX ix_oi_product ON dbo.order_items (product_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_rev_product' AND object_id = OBJECT_ID('dbo.reviews'))
    CREATE INDEX ix_rev_product ON dbo.reviews (product_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_cart_customer' AND object_id = OBJECT_ID('dbo.cart_events'))
    CREATE INDEX ix_cart_customer ON dbo.cart_events (customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_cart_ts' AND object_id = OBJECT_ID('dbo.cart_events'))
    CREATE INDEX ix_cart_ts ON dbo.cart_events (event_ts);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_txn_account' AND object_id = OBJECT_ID('dbo.transactions'))
    CREATE INDEX ix_txn_account ON dbo.transactions (account_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_txn_date' AND object_id = OBJECT_ID('dbo.transactions'))
    CREATE INDEX ix_txn_date ON dbo.transactions (txn_date);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_loan_account' AND object_id = OBJECT_ID('dbo.loans'))
    CREATE INDEX ix_loan_account ON dbo.loans (account_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_fraud_account' AND object_id = OBJECT_ID('dbo.fraud_alerts'))
    CREATE INDEX ix_fraud_account ON dbo.fraud_alerts (account_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_fraud_detected' AND object_id = OBJECT_ID('dbo.fraud_alerts'))
    CREATE INDEX ix_fraud_detected ON dbo.fraud_alerts (detected_at);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_mktprice_ticker' AND object_id = OBJECT_ID('dbo.market_prices'))
    CREATE INDEX ix_mktprice_ticker ON dbo.market_prices (ticker, price_date);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_audit_table' AND object_id = OBJECT_ID('dbo.audit_log'))
    CREATE INDEX ix_audit_table ON dbo.audit_log (table_name, changed_at);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_audit_changed' AND object_id = OBJECT_ID('dbo.audit_log'))
    CREATE INDEX ix_audit_changed ON dbo.audit_log (changed_at);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_sensor_machine' AND object_id = OBJECT_ID('dbo.sensors'))
    CREATE INDEX ix_sensor_machine ON dbo.sensors (machine_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_sr_sensor' AND object_id = OBJECT_ID('dbo.sensor_readings'))
    CREATE INDEX ix_sr_sensor ON dbo.sensor_readings (sensor_id, reading_ts);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_sr_machine' AND object_id = OBJECT_ID('dbo.sensor_readings'))
    CREATE INDEX ix_sr_machine ON dbo.sensor_readings (machine_id, reading_ts);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_sr_ts' AND object_id = OBJECT_ID('dbo.sensor_readings'))
    CREATE INDEX ix_sr_ts ON dbo.sensor_readings (reading_ts);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_batch_machine' AND object_id = OBJECT_ID('dbo.production_batches'))
    CREATE INDEX ix_batch_machine ON dbo.production_batches (machine_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_qc_batch' AND object_id = OBJECT_ID('dbo.quality_checks'))
    CREATE INDEX ix_qc_batch ON dbo.quality_checks (batch_id);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_inv_store' AND object_id = OBJECT_ID('dbo.inventory'))
    CREATE INDEX ix_inv_store ON dbo.inventory (store_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_inv_product' AND object_id = OBJECT_ID('dbo.inventory'))
    CREATE INDEX ix_inv_product ON dbo.inventory (product_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_pos_store' AND object_id = OBJECT_ID('dbo.pos_transactions'))
    CREATE INDEX ix_pos_store ON dbo.pos_transactions (store_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_pos_date' AND object_id = OBJECT_ID('dbo.pos_transactions'))
    CREATE INDEX ix_pos_date ON dbo.pos_transactions (txn_date);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_positem_txn' AND object_id = OBJECT_ID('dbo.pos_items'))
    CREATE INDEX ix_positem_txn ON dbo.pos_items (pos_txn_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_positem_product' AND object_id = OBJECT_ID('dbo.pos_items'))
    CREATE INDEX ix_positem_product ON dbo.pos_items (product_id);
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_permit_citizen' AND object_id = OBJECT_ID('dbo.permits'))
    CREATE INDEX ix_permit_citizen ON dbo.permits (citizen_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_viol_citizen' AND object_id = OBJECT_ID('dbo.violations'))
    CREATE INDEX ix_viol_citizen ON dbo.violations (citizen_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_svcreq_citizen' AND object_id = OBJECT_ID('dbo.service_requests'))
    CREATE INDEX ix_svcreq_citizen ON dbo.service_requests (citizen_id);
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'ix_svcreq_submitted' AND object_id = OBJECT_ID('dbo.service_requests'))
    CREATE INDEX ix_svcreq_submitted ON dbo.service_requests (submitted_at);
GO

PRINT 'Indexes created';
GO
/* ============================================================================
   3. SAMPLE DATA — HEALTHCARE
   (parent tables first: providers, patients; then dependents)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.providers)
BEGIN
    INSERT INTO dbo.providers (npi, first_name, last_name, specialty, phone, email, facility_name, state, active) VALUES
    ('NPI1234567890','John','Smith','Family Medicine','212-555-0101','john.smith@ebhealth.example','Riverside Medical Center','NY',1),
    ('NPI1234567891','Maria','Garcia','Cardiology','212-555-0102','maria.garcia@ebhealth.example','Riverside Medical Center','NY',1),
    ('NPI1234567892','David','Chen','Pediatrics','415-555-0103','david.chen@ebhealth.example','Bayview Children''s Clinic','CA',1),
    ('NPI1234567893','Sarah','Johnson','Orthopedics','415-555-0104','sarah.johnson@ebhealth.example','Bayview Ortho Group','CA',1),
    ('NPI1234567894','Michael','Williams','Dermatology','312-555-0105','michael.williams@ebhealth.example','Lakeside Dermatology','IL',1),
    ('NPI1234567895','Emily','Brown','Internal Medicine','312-555-0106','emily.brown@ebhealth.example','Lakeside Medical Group','IL',1),
    ('NPI1234567896','James','Davis','Neurology','713-555-0107','james.davis@ebhealth.example','Gulf Coast Neurology','TX',1),
    ('NPI1234567897','Linda','Martinez','Obstetrics','713-555-0108','linda.martinez@ebhealth.example','Gulf Coast Women''s Health','TX',1),
    ('NPI1234567898','Robert','Wilson','Endocrinology','602-555-0109','robert.wilson@ebhealth.example','Desert Health Partners','AZ',1),
    ('NPI1234567899','Jennifer','Taylor','Psychiatry','602-555-0110','jennifer.taylor@ebhealth.example','Desert Behavioral Health','AZ',0);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.patients)
BEGIN
    INSERT INTO dbo.patients (first_name, last_name, date_of_birth, gender, ssn_hash, email, phone, address_line1, city, state, zip_code, insurance_id) VALUES
    ('Alice','Thompson','1985-03-12','F',NULL,'alice.thompson@example.com','212-555-0201','145 Elm St','New York','NY','10001','INS-100234'),
    ('Brian','Anderson','1978-11-05','M',NULL,'brian.anderson@example.com','212-555-0202','22 Oak Ave','New York','NY','10002','INS-100235'),
    ('Carol','Martinez','1990-07-22','F',NULL,'carol.martinez@example.com','415-555-0203','88 Pine St','San Francisco','CA','94102','INS-100236'),
    ('Daniel','Lee','1965-01-30','M',NULL,'daniel.lee@example.com','415-555-0204','310 Bay St','San Francisco','CA','94103','INS-100237'),
    ('Eva','Rodriguez','2001-09-14','F',NULL,'eva.rodriguez@example.com','312-555-0205','77 Lake Shore Dr','Chicago','IL','60601','INS-100238'),
    ('Frank','Wilson','1972-05-18','M',NULL,'frank.wilson@example.com','312-555-0206','405 Michigan Ave','Chicago','IL','60602','INS-100239'),
    ('Grace','Kim','1995-12-02','F',NULL,'grace.kim@example.com','713-555-0207','19 Main St','Houston','TX','77002','INS-100240'),
    ('Henry','Clark','1988-04-09','M',NULL,'henry.clark@example.com','713-555-0208','501 Travis St','Houston','TX','77003','INS-100241'),
    ('Isabella','Lewis','1999-08-27','F',NULL,'isabella.lewis@example.com','602-555-0209','233 Central Ave','Phoenix','AZ','85004','INS-100242'),
    ('Jack','Walker','1955-02-14','M',NULL,'jack.walker@example.com','602-555-0210','88 Camelback Rd','Phoenix','AZ','85005','INS-100243'),
    ('Karen','Hall','1982-10-19','F',NULL,'karen.hall@example.com','206-555-0211','44 Pike St','Seattle','WA','98101','INS-100244'),
    ('Leo','Young','1970-06-25','M',NULL,'leo.young@example.com','206-555-0212','900 Pine St','Seattle','WA','98102','INS-100245'),
    ('Mia','Allen','2003-01-08','F',NULL,'mia.allen@example.com','303-555-0213','12 16th St','Denver','CO','80202','INS-100246'),
    ('Nathan','King','1960-03-03','M',NULL,'nathan.king@example.com','303-555-0214','500 Colfax Ave','Denver','CO','80203','INS-100247'),
    ('Olivia','Wright','1993-11-30','F',NULL,'olivia.wright@example.com','617-555-0215','77 Boylston St','Boston','MA','02116','INS-100248'),
    ('Peter','Scott','1948-07-17','M',NULL,'peter.scott@example.com','617-555-0216','200 Newbury St','Boston','MA','02117','INS-100249'),
    ('Quinn','Baker','1987-09-09','O',NULL,'quinn.baker@example.com','305-555-0217','33 Ocean Dr','Miami','FL','33139','INS-100250'),
    ('Rachel','Green','1991-02-21','F',NULL,'rachel.green@example.com','305-555-0218','88 Collins Ave','Miami','FL','33140','INS-100251'),
    ('Samuel','Adams','1975-12-12','M',NULL,'samuel.adams@example.com','404-555-0219','15 Peachtree St','Atlanta','GA','30303','INS-100252'),
    ('Tina','Nelson','1996-05-05','F',NULL,'tina.nelson@example.com','404-555-0220','400 Marietta St','Atlanta','GA','30304','INS-100253');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.appointments)
BEGIN
    INSERT INTO dbo.appointments (patient_id, provider_id, appt_date, reason, status, duration_min, notes) VALUES
    (1,1,'2026-01-05T09:00:00','Annual physical exam','Completed',30,'Routine checkup, no concerns'),
    (2,2,'2026-01-06T10:30:00','Chest pain evaluation','Completed',45,'EKG ordered'),
    (3,3,'2026-01-06T14:00:00','Child wellness visit','Completed',20,'Growth on track'),
    (4,4,'2026-01-07T11:00:00','Knee pain','Scheduled',30,NULL),
    (5,5,'2026-01-07T15:30:00','Skin rash','Completed',15,'Prescribed topical cream'),
    (6,6,'2026-01-08T09:15:00','Follow-up diabetes management','Completed',30,'A1C improved'),
    (7,7,'2026-01-08T13:00:00','Migraine consultation','Scheduled',45,NULL),
    (8,8,'2026-01-09T08:45:00','Prenatal checkup','Completed',30,'Normal progress'),
    (9,9,'2026-01-09T16:00:00','Thyroid follow-up','Completed',20,'Dosage adjusted'),
    (10,10,'2026-01-10T10:00:00','Anxiety consultation','No-Show',45,NULL),
    (11,1,'2026-01-12T09:30:00','Annual physical exam','Completed',30,'Cholesterol slightly elevated'),
    (12,2,'2026-01-12T11:00:00','Hypertension follow-up','Completed',30,'BP well controlled'),
    (13,3,'2026-01-13T09:00:00','Sports physical','Completed',20,'Cleared for sports'),
    (14,4,'2026-01-13T14:30:00','Shoulder injury','Scheduled',30,NULL),
    (15,5,'2026-01-14T10:00:00','Acne treatment','Completed',15,'Follow up in 6 weeks'),
    (16,6,'2026-01-14T15:00:00','General checkup','Cancelled',30,'Patient rescheduled'),
    (17,7,'2026-01-15T09:00:00','Headache evaluation','Completed',30,'MRI recommended'),
    (18,8,'2026-01-15T13:30:00','Postpartum checkup','Completed',30,'Recovering well'),
    (19,9,'2026-01-16T08:30:00','Diabetes screening','Completed',20,'Results pending'),
    (20,10,'2026-01-16T16:30:00','Therapy session','Completed',60,'Good progress noted'),
    (1,2,'2026-02-01T09:00:00','Cardiology referral','Scheduled',45,NULL),
    (3,4,'2026-02-02T11:30:00','Fracture follow-up','Rescheduled',30,'Patient requested new date'),
    (5,6,'2026-02-03T10:00:00','Annual physical','Scheduled',30,NULL),
    (8,9,'2026-02-04T09:00:00','Endocrinology consult','Scheduled',30,NULL),
    (12,7,'2026-02-05T14:00:00','Neurology follow-up','Completed',30,'Symptoms improved');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.diagnoses)
BEGIN
    INSERT INTO dbo.diagnoses (patient_id, provider_id, icd10_code, description, diagnosed_date, resolved_date, severity) VALUES
    (1,1,'Z00.00','Encounter for general adult medical examination without abnormal findings','2026-01-05',NULL,'Mild'),
    (2,2,'I10','Essential (primary) hypertension','2026-01-06',NULL,'Moderate'),
    (3,3,'Z00.129','Encounter for routine child health examination','2026-01-06','2026-01-06','Mild'),
    (4,4,'M25.561','Pain in right knee','2026-01-07',NULL,'Moderate'),
    (5,5,'L30.9','Dermatitis, unspecified','2026-01-07','2026-02-01','Mild'),
    (6,6,'E11.9','Type 2 diabetes mellitus without complications','2026-01-08',NULL,'Moderate'),
    (7,7,'G43.909','Migraine, unspecified, not intractable','2026-01-08',NULL,'Moderate'),
    (8,8,'Z34.90','Encounter for supervision of normal pregnancy, unspecified trimester','2026-01-09',NULL,'Mild'),
    (9,9,'E03.9','Hypothyroidism, unspecified','2026-01-09',NULL,'Mild'),
    (11,1,'E78.5','Hyperlipidemia, unspecified','2026-01-12',NULL,'Mild'),
    (12,2,'I10','Essential (primary) hypertension','2026-01-12',NULL,'Mild'),
    (13,3,'Z02.5','Encounter for examination for participation in sport','2026-01-13','2026-01-13','Mild'),
    (14,4,'S43.401A','Sprain of right shoulder joint, initial encounter','2026-01-13',NULL,'Moderate'),
    (15,5,'L70.0','Acne vulgaris','2026-01-14',NULL,'Mild'),
    (17,7,'R51.9','Headache, unspecified','2026-01-15',NULL,'Severe'),
    (18,8,'Z39.2','Encounter for routine postpartum follow-up','2026-01-15','2026-01-15','Mild'),
    (19,9,'R73.03','Prediabetes','2026-01-16',NULL,'Moderate'),
    (7,7,'G43.109','Migraine with aura, not intractable','2026-02-05',NULL,'Severe'),
    (2,2,'I25.10','Atherosclerotic heart disease of native coronary artery without angina pectoris','2026-01-06',NULL,'Critical'),
    (10,10,'F41.1','Generalized anxiety disorder','2026-01-10',NULL,'Moderate');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.lab_results)
BEGIN
    INSERT INTO dbo.lab_results (patient_id, provider_id, test_name, test_code, result_value, unit, reference_range, abnormal_flag, collected_at, resulted_at) VALUES
    (1,1,'Complete Blood Count','CBC','Normal','','WNL',0,'2026-01-05T09:15:00','2026-01-05T12:00:00'),
    (2,2,'Lipid Panel','LIPID','215','mg/dL','<200',1,'2026-01-06T10:45:00','2026-01-06T15:00:00'),
    (2,2,'Troponin I','TROP','0.02','ng/mL','<0.04',0,'2026-01-06T10:45:00','2026-01-06T12:30:00'),
    (5,5,'Skin Biopsy','BIOPSY','Benign','','N/A',0,'2026-01-07T15:45:00','2026-01-10T09:00:00'),
    (6,6,'Hemoglobin A1C','HBA1C','7.2','%','<5.7',1,'2026-01-08T09:30:00','2026-01-08T14:00:00'),
    (6,6,'Fasting Glucose','GLU','145','mg/dL','70-99',1,'2026-01-08T09:30:00','2026-01-08T14:00:00'),
    (8,8,'Complete Blood Count','CBC','Normal','','WNL',0,'2026-01-09T09:00:00','2026-01-09T13:00:00'),
    (9,9,'TSH','TSH','5.8','mIU/L','0.4-4.0',1,'2026-01-09T16:15:00','2026-01-10T09:00:00'),
    (9,9,'Free T4','FT4','1.1','ng/dL','0.8-1.8',0,'2026-01-09T16:15:00','2026-01-10T09:00:00'),
    (11,1,'Lipid Panel','LIPID','245','mg/dL','<200',1,'2026-01-12T09:45:00','2026-01-12T14:00:00'),
    (12,2,'Basic Metabolic Panel','BMP','Normal','','WNL',0,'2026-01-12T11:15:00','2026-01-12T16:00:00'),
    (17,7,'MRI Brain','MRI','No acute findings','','N/A',0,'2026-01-20T08:00:00','2026-01-22T09:00:00'),
    (19,9,'Fasting Glucose','GLU','108','mg/dL','70-99',1,'2026-01-16T08:45:00','2026-01-16T13:00:00'),
    (19,9,'Hemoglobin A1C','HBA1C','5.9','%','<5.7',1,'2026-01-16T08:45:00','2026-01-16T13:00:00'),
    (3,3,'Complete Blood Count','CBC','Normal','','WNL',0,'2026-01-06T14:15:00','2026-01-06T17:00:00'),
    (4,4,'X-Ray Knee','XRAY','No fracture','','N/A',0,'2026-01-07T11:15:00','2026-01-07T13:00:00'),
    (7,7,'CT Scan Head','CT','Normal','','N/A',0,'2026-01-08T13:15:00','2026-01-08T17:00:00'),
    (10,10,'Basic Metabolic Panel','BMP','Normal','','WNL',0,'2026-01-10T10:15:00','2026-01-10T14:00:00'),
    (13,3,'Complete Blood Count','CBC','Normal','','WNL',0,'2026-01-13T09:15:00','2026-01-13T12:00:00'),
    (14,4,'X-Ray Shoulder','XRAY','Mild sprain, no fracture','','N/A',0,'2026-01-13T14:45:00','2026-01-13T17:00:00'),
    (15,5,'Skin Culture','CULTURE','No growth','','N/A',0,'2026-01-14T10:15:00','2026-01-16T09:00:00'),
    (18,8,'Complete Blood Count','CBC','Normal','','WNL',0,'2026-01-15T13:45:00','2026-01-15T17:00:00'),
    (20,10,'Basic Metabolic Panel','BMP','Normal','','WNL',0,'2026-01-16T16:45:00','2026-01-17T09:00:00'),
    (1,1,'Vitamin D','VITD','28','ng/mL','30-100',1,'2026-01-05T09:15:00','2026-01-05T12:00:00'),
    (16,6,'Basic Metabolic Panel','BMP','Normal','','WNL',0,'2026-01-14T15:15:00','2026-01-14T18:00:00');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.prescriptions)
BEGIN
    INSERT INTO dbo.prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, quantity, refills, prescribed_date, expiry_date, status) VALUES
    (1,1,'Atorvastatin','00071-0155-23','20mg once daily',30,3,'2026-01-05','2027-01-05','Active'),
    (2,2,'Lisinopril','00093-0058-01','10mg once daily',30,5,'2026-01-06','2027-01-06','Active'),
    (2,2,'Metoprolol','00378-0018-01','25mg twice daily',60,3,'2026-01-06','2027-01-06','Active'),
    (5,5,'Hydrocortisone Cream','00168-0028-31','1% apply twice daily',1,1,'2026-01-07','2026-07-07','Active'),
    (6,6,'Metformin','00087-6060-05','500mg twice daily',60,5,'2026-01-08','2027-01-08','Active'),
    (7,7,'Sumatriptan','00006-0764-31','50mg as needed',9,2,'2026-01-08','2026-07-08','Active'),
    (8,8,'Prenatal Vitamins','00113-0197-01','1 tablet daily',90,3,'2026-01-09','2026-10-09','Active'),
    (9,9,'Levothyroxine','00074-6624-13','75mcg once daily',30,5,'2026-01-09','2027-01-09','Active'),
    (11,1,'Atorvastatin','00071-0156-23','40mg once daily',30,3,'2026-01-12','2027-01-12','Active'),
    (12,2,'Amlodipine','00093-7159-01','5mg once daily',30,5,'2026-01-12','2027-01-12','Active'),
    (14,4,'Ibuprofen','00904-5813-60','400mg every 6 hours as needed',20,0,'2026-01-13','2026-04-13','Active'),
    (15,5,'Doxycycline','00093-0069-01','100mg twice daily',30,1,'2026-01-14','2026-04-14','Active'),
    (17,7,'Naproxen','00904-6134-60','500mg twice daily',14,0,'2026-01-15','2026-04-15','Active'),
    (19,9,'Metformin','00087-6060-05','500mg once daily',30,5,'2026-01-16','2027-01-16','Active'),
    (10,10,'Sertraline','00049-4960-66','50mg once daily',30,5,'2026-01-10','2027-01-10','Active'),
    (20,10,'Alprazolam','00093-0308-01','0.25mg as needed',10,0,'2026-01-16','2026-04-16','Discontinued'),
    (4,4,'Acetaminophen','00904-2015-60','500mg every 6 hours as needed',20,0,'2026-01-07','2026-04-07','Expired'),
    (13,3,'Amoxicillin','00093-4155-73','500mg three times daily',21,0,'2026-01-13','2026-01-27','Inactive'),
    (18,8,'Prenatal Vitamins','00113-0197-01','1 tablet daily',30,0,'2026-01-15','2026-04-15','Active'),
    (9,9,'Levothyroxine','00074-6625-13','88mcg once daily',30,5,'2026-02-10','2027-02-10','Active');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.insurance_claims)
BEGIN
    INSERT INTO dbo.insurance_claims (patient_id, provider_id, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status, payer_name) VALUES
    (1,1,'2026-01-06','2026-01-05','Z00.00','99396',250.00,180.00,180.00,'Paid','Blue Shield Health'),
    (2,2,'2026-01-07','2026-01-06','I10','99213',180.00,140.00,140.00,'Paid','Aetna'),
    (3,3,'2026-01-07','2026-01-06','Z00.129','99392',200.00,150.00,150.00,'Paid','United Healthcare'),
    (4,4,'2026-01-08','2026-01-07','M25.561','99214',220.00,160.00,NULL,'In Review','Cigna'),
    (5,5,'2026-01-08','2026-01-07','L30.9','99213',150.00,110.00,110.00,'Paid','Blue Shield Health'),
    (6,6,'2026-01-09','2026-01-08','E11.9','99214',230.00,175.00,175.00,'Paid','Aetna'),
    (7,7,'2026-01-09','2026-01-08','G43.909','99204',350.00,260.00,NULL,'Pending','United Healthcare'),
    (8,8,'2026-01-10','2026-01-09','Z34.90','99213',200.00,150.00,150.00,'Paid','Cigna'),
    (9,9,'2026-01-10','2026-01-09','E03.9','99214',210.00,160.00,160.00,'Paid','Blue Shield Health'),
    (10,10,'2026-01-11','2026-01-10','F41.1','99215',280.00,NULL,NULL,'Denied','Aetna'),
    (11,1,'2026-01-13','2026-01-12','E78.5','99396',260.00,190.00,190.00,'Paid','United Healthcare'),
    (12,2,'2026-01-13','2026-01-12','I10','99213',180.00,140.00,140.00,'Paid','Cigna'),
    (13,3,'2026-01-14','2026-01-13','Z02.5','99392',150.00,110.00,110.00,'Paid','Blue Shield Health'),
    (14,4,'2026-01-14','2026-01-13','S43.401A','99214',300.00,220.00,NULL,'Appealed','Aetna'),
    (15,5,'2026-01-15','2026-01-14','L70.0','99213',140.00,100.00,100.00,'Paid','United Healthcare'),
    (17,7,'2026-01-16','2026-01-15','R51.9','99215',320.00,240.00,240.00,'Paid','Cigna'),
    (18,8,'2026-01-16','2026-01-15','Z39.2','99213',180.00,140.00,140.00,'Paid','Blue Shield Health'),
    (19,9,'2026-01-17','2026-01-16','R73.03','99214',210.00,160.00,NULL,'In Review','Aetna'),
    (20,10,'2026-01-17','2026-01-16','F41.1','90837',400.00,300.00,300.00,'Paid','United Healthcare'),
    (2,2,'2026-01-08','2026-01-06','I25.10','93000',450.00,340.00,NULL,'Pending','Aetna');
END
GO

PRINT 'Healthcare sample data loaded';
GO
/* ============================================================================
   3. SAMPLE DATA — E-COMMERCE
   (categories before products; customers before orders; orders before items)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.categories)
BEGIN
    INSERT INTO dbo.categories (name, parent_id, description, active) VALUES
    ('Electronics',NULL,'Consumer electronics and gadgets',1),
    ('Home & Kitchen',NULL,'Appliances and home goods',1),
    ('Apparel',NULL,'Clothing for men and women',1),
    ('Sports & Outdoors',NULL,'Sporting goods and outdoor gear',1),
    ('Books',NULL,'Fiction, non-fiction, and reference books',1);
    INSERT INTO dbo.categories (name, parent_id, description, active) VALUES
    ('Laptops & Computers',1,'Laptops, desktops, and accessories',1),
    ('Mobile Phones',1,'Smartphones and phone accessories',1),
    ('Kitchen Appliances',2,'Small and large kitchen appliances',1),
    ('Men''s Clothing',3,'Apparel for men',1),
    ('Women''s Clothing',3,'Apparel for women',1);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.products)
BEGIN
    INSERT INTO dbo.products (sku, name, category_id, brand, description, unit_price, cost_price, weight_kg, active) VALUES
    ('SKU-EL-1001','Wireless Bluetooth Headphones',1,'SoundWave','Over-ear wireless headphones with noise cancellation',89.99,42.00,0.280,1),
    ('SKU-EL-1002','4K Ultra HD Smart TV 55"',1,'VisionMax','55-inch 4K smart TV with HDR support',549.99,310.00,15.500,1),
    ('SKU-CO-1003','15-inch Laptop Pro',6,'CompuTech','15-inch laptop, 16GB RAM, 512GB SSD',1199.99,780.00,1.800,1),
    ('SKU-CO-1004','Wireless Mouse',6,'CompuTech','Ergonomic wireless mouse',24.99,9.50,0.120,1),
    ('SKU-MP-1005','Smartphone X12',7,'NovaPhone','6.5-inch display smartphone, 128GB storage',699.99,430.00,0.190,1),
    ('SKU-MP-1006','Phone Case Clear',7,'ShieldGuard','Clear protective phone case',14.99,3.50,0.040,1),
    ('SKU-HK-1007','Stand Mixer',2,'BakeMaster','5-quart stand mixer with attachments',249.99,140.00,6.500,1),
    ('SKU-KA-1008','Air Fryer 6qt',8,'CrispCo','6-quart digital air fryer',99.99,52.00,5.200,1),
    ('SKU-KA-1009','Coffee Maker Deluxe',8,'BrewRight','12-cup programmable coffee maker',79.99,38.00,3.100,1),
    ('SKU-AP-1010','Cotton T-Shirt',3,'ComfortWear','100% cotton crew neck t-shirt',19.99,6.00,0.180,1),
    ('SKU-MC-1011','Men''s Denim Jacket',9,'UrbanEdge','Classic denim jacket for men',79.99,32.00,0.900,1),
    ('SKU-MC-1012','Men''s Running Shoes',9,'StrideFit','Lightweight running shoes',89.99,40.00,0.700,1),
    ('SKU-WC-1013','Women''s Yoga Pants',10,'FlexFit','High-waisted stretch yoga pants',49.99,18.00,0.250,1),
    ('SKU-WC-1014','Women''s Summer Dress',10,'SunStyle','Floral print summer dress',59.99,22.00,0.300,1),
    ('SKU-SP-1015','Mountain Bike 27.5"',4,'TrailBlaze','27.5-inch mountain bike, 21-speed',449.99,260.00,14.200,1),
    ('SKU-SP-1016','Yoga Mat',4,'FlexFit','Non-slip yoga mat, 6mm thick',29.99,9.00,1.100,1),
    ('SKU-SP-1017','Camping Tent 4-Person',4,'TrailBlaze','Waterproof 4-person camping tent',159.99,85.00,4.500,1),
    ('SKU-BK-1018','The Silent Ocean (Novel)',5,'Harborview Press','Bestselling fiction novel',16.99,6.50,0.350,1),
    ('SKU-BK-1019','Cooking for Everyone (Cookbook)',5,'Harborview Press','Beginner-friendly cookbook',24.99,9.00,0.600,1),
    ('SKU-EL-1020','Bluetooth Speaker Mini',1,'SoundWave','Portable waterproof Bluetooth speaker',39.99,16.00,0.250,1),
    ('SKU-CO-1021','Mechanical Keyboard',6,'CompuTech','RGB backlit mechanical keyboard',69.99,28.00,0.950,1),
    ('SKU-MP-1022','Wireless Charger Pad',7,'ShieldGuard','10W fast wireless charging pad',22.99,8.00,0.150,1),
    ('SKU-HK-1023','Non-Stick Cookware Set',2,'BakeMaster','10-piece non-stick cookware set',139.99,65.00,7.800,1),
    ('SKU-AP-1024','Wool Blend Sweater',3,'ComfortWear','Warm wool blend crew sweater',54.99,21.00,0.450,1),
    ('SKU-SP-1025','Insulated Water Bottle',4,'TrailBlaze','32oz insulated stainless steel bottle',27.99,9.50,0.400,0);
END
GO

-- Simulate a round of price changes so dbo.products_history (the temporal
-- table's auto-generated history table) isn't empty - each UPDATE below
-- moves the pre-change row into history automatically, no application code
-- involved. Guarded on the history table being empty so re-running this
-- script doesn't pile up duplicate history rows.
IF NOT EXISTS (SELECT 1 FROM dbo.products_history)
BEGIN
    UPDATE dbo.products SET unit_price = 79.99  WHERE sku = 'SKU-EL-1001';  -- was 89.99
    UPDATE dbo.products SET unit_price = 499.99 WHERE sku = 'SKU-EL-1002';  -- was 549.99
    UPDATE dbo.products SET unit_price = 1099.99 WHERE sku = 'SKU-CO-1003'; -- was 1199.99
    UPDATE dbo.products SET unit_price = 649.99 WHERE sku = 'SKU-MP-1005';  -- was 699.99
    UPDATE dbo.products SET unit_price = 94.99  WHERE sku = 'SKU-KA-1008';  -- was 99.99
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.customers)
BEGIN
    INSERT INTO dbo.customers (first_name, last_name, email, phone, address_line1, city, state, zip_code, country, loyalty_points) VALUES
    ('Sophia','Turner','sophia.turner@example.com','646-555-0301','12 Broadway','New York','NY','10003','USA',450),
    ('Liam','Martin','liam.martin@example.com','646-555-0302','88 5th Ave','New York','NY','10004','USA',120),
    ('Ava','Robinson','ava.robinson@example.com','415-555-0303','200 Market St','San Francisco','CA','94103','USA',890),
    ('Noah','Clark','noah.clark@example.com','415-555-0304','55 Mission St','San Francisco','CA','94105','USA',60),
    ('Emma','Lewis','emma.lewis@example.com','312-555-0305','300 State St','Chicago','IL','60604','USA',340),
    ('Oliver','Walker','oliver.walker@example.com','312-555-0306','150 Wacker Dr','Chicago','IL','60606','USA',0),
    ('Charlotte','Hall','charlotte.hall@example.com','713-555-0307','45 Main St','Houston','TX','77002','USA',210),
    ('Elijah','Young','elijah.young@example.com','713-555-0308','900 Louisiana St','Houston','TX','77002','USA',780),
    ('Amelia','King','amelia.king@example.com','602-555-0309','500 Van Buren St','Phoenix','AZ','85004','USA',55),
    ('Lucas','Wright','lucas.wright@example.com','602-555-0310','20 Adams St','Phoenix','AZ','85003','USA',999),
    ('Mia','Scott','mia.scott@example.com','206-555-0311','700 Pike St','Seattle','WA','98101','USA',130),
    ('Benjamin','Baker','benjamin.baker@example.com','206-555-0312','10 Union St','Seattle','WA','98104','USA',410),
    ('Harper','Green','harper.green@example.com','303-555-0313','88 Larimer St','Denver','CO','80202','USA',25),
    ('James','Adams','james.adams@example.com','303-555-0314','400 15th St','Denver','CO','80202','USA',560),
    ('Ella','Nelson','ella.nelson@example.com','617-555-0315','12 Boylston St','Boston','MA','02116','USA',675),
    ('Henry','Carter','henry.carter@example.com','617-555-0316','200 Federal St','Boston','MA','02110','USA',90),
    ('Grace','Mitchell','grace.mitchell@example.com','305-555-0317','44 Brickell Ave','Miami','FL','33131','USA',320),
    ('Jack','Perez','jack.perez@example.com','305-555-0318','900 Biscayne Blvd','Miami','FL','33132','USA',15),
    ('Chloe','Roberts','chloe.roberts@example.com','404-555-0319','20 Peachtree St','Atlanta','GA','30303','USA',480),
    ('Owen','Turner','owen.turner@example.com','404-555-0320','100 Centennial Olympic Park Dr','Atlanta','GA','30313','USA',200);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.orders)
BEGIN
    INSERT INTO dbo.orders (customer_id, order_date, status, shipping_addr, subtotal, tax_amount, shipping_cost, total_amount, channel) VALUES
    (1,'2026-01-02T08:10:00','Delivered','12 Broadway, New York, NY 10003',119.97,9.60,0.00,129.57,'Web'),
    (2,'2026-01-03T09:10:00','Delivered','88 5th Ave, New York, NY 10004',24.99,2.00,5.99,32.98,'Web'),
    (3,'2026-01-03T14:12:00','Delivered','200 Market St, San Francisco, CA 94103',1199.99,96.00,0.00,1295.99,'Web'),
    (4,'2026-01-04T10:15:00','Shipped','55 Mission St, San Francisco, CA 94105',722.98,57.84,0.00,780.82,'Web'),
    (5,'2026-01-05T11:10:00','Delivered','300 State St, Chicago, IL 60604',249.99,20.00,0.00,269.99,'Web'),
    (6,'2026-01-06T12:00:00','Processing','150 Wacker Dr, Chicago, IL 60606',179.98,14.40,0.00,194.38,'Mobile App'),
    (7,'2026-01-06T13:00:00','Delivered','45 Main St, Houston, TX 77002',59.97,4.80,5.99,70.76,'Web'),
    (8,'2026-01-07T09:30:00','Delivered','900 Louisiana St, Houston, TX 77002',169.98,13.60,0.00,183.58,'Web'),
    (9,'2026-01-07T15:00:00','Cancelled','500 Van Buren St, Phoenix, AZ 85004',99.98,8.00,0.00,107.98,'Web'),
    (10,'2026-01-08T10:00:00','Delivered','20 Adams St, Phoenix, AZ 85003',59.99,4.80,5.99,70.78,'Mobile App'),
    (11,'2026-01-09T08:00:00','Shipped','700 Pike St, Seattle, WA 98101',449.99,36.00,0.00,485.99,'Web'),
    (12,'2026-01-09T14:00:00','Delivered','10 Union St, Seattle, WA 98104',219.97,17.60,0.00,237.57,'Web'),
    (13,'2026-01-10T09:00:00','Delivered','88 Larimer St, Denver, CO 80202',41.98,3.36,5.99,51.33,'Web'),
    (14,'2026-01-10T16:00:00','Returned','400 15th St, Denver, CO 80202',39.99,3.20,5.99,49.18,'Web'),
    (15,'2026-01-11T11:00:00','Delivered','12 Boylston St, Boston, MA 02116',619.98,49.60,0.00,669.58,'Web'),
    (16,'2026-01-12T08:30:00','Processing','200 Federal St, Boston, MA 02110',139.99,11.20,0.00,151.19,'Web'),
    (17,'2026-01-12T13:00:00','Delivered','44 Brickell Ave, Miami, FL 33131',82.98,6.64,0.00,89.62,'Mobile App'),
    (18,'2026-01-13T10:00:00','Refunded','900 Biscayne Blvd, Miami, FL 33132',89.99,7.20,0.00,97.19,'Web'),
    (19,'2026-01-13T15:00:00','Delivered','20 Peachtree St, Atlanta, GA 30303',179.98,14.40,0.00,194.38,'Web'),
    (20,'2026-01-14T09:00:00','Pending','100 Centennial Olympic Park Dr, Atlanta, GA 30313',14.99,1.20,5.99,22.18,'Web'),
    (1,'2026-01-20T09:00:00','Delivered','12 Broadway, New York, NY 10003',169.98,13.60,0.00,183.58,'Web'),
    (3,'2026-01-21T10:00:00','Shipped','200 Market St, San Francisco, CA 94103',59.99,4.80,5.99,70.78,'Mobile App'),
    (5,'2026-01-22T11:00:00','Delivered','300 State St, Chicago, IL 60604',33.98,2.72,5.99,42.69,'Web'),
    (8,'2026-01-23T12:00:00','Processing','900 Louisiana St, Houston, TX 77002',699.99,56.00,0.00,755.99,'Web'),
    (10,'2026-01-24T13:00:00','Delivered','20 Adams St, Phoenix, AZ 85003',94.98,7.60,0.00,102.58,'Web');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.order_items)
BEGIN
    INSERT INTO dbo.order_items (order_id, product_id, quantity, unit_price, discount, line_total) VALUES
    (1,1,1,89.99,0,89.99),
    (1,6,2,14.99,0,29.98),
    (2,4,1,24.99,0,24.99),
    (3,3,1,1199.99,0,1199.99),
    (4,5,1,699.99,0,699.99),
    (4,22,1,22.99,0,22.99),
    (5,7,1,249.99,0,249.99),
    (6,8,1,99.99,0,99.99),
    (6,9,1,79.99,0,79.99),
    (7,10,3,19.99,0,59.97),
    (8,11,1,79.99,0,79.99),
    (8,12,1,89.99,0,89.99),
    (9,13,2,49.99,0,99.98),
    (10,14,1,59.99,0,59.99),
    (11,15,1,449.99,0,449.99),
    (12,16,2,29.99,0,59.98),
    (12,17,1,159.99,0,159.99),
    (13,18,1,16.99,0,16.99),
    (13,19,1,24.99,0,24.99),
    (14,20,1,39.99,0,39.99),
    (15,21,1,69.99,0,69.99),
    (15,2,1,549.99,0,549.99),
    (16,23,1,139.99,0,139.99),
    (17,24,1,54.99,0,54.99),
    (17,25,1,27.99,0,27.99),
    (18,1,1,89.99,0,89.99),
    (19,9,1,79.99,0,79.99),
    (19,8,1,99.99,0,99.99),
    (20,6,1,14.99,0,14.99),
    (21,12,1,89.99,0,89.99),
    (21,11,1,79.99,0,79.99),
    (22,14,1,59.99,0,59.99),
    (23,18,2,16.99,0,33.98),
    (24,5,1,699.99,0,699.99),
    (25,21,1,69.99,0,69.99),
    (25,4,1,24.99,0,24.99);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.reviews)
BEGIN
    INSERT INTO dbo.reviews (product_id, customer_id, rating, title, body, verified, helpful_votes) VALUES
    (1,1,5,'Amazing sound quality','These headphones exceeded my expectations, great battery life too.',1,12),
    (3,3,4,'Great laptop for work','Fast and reliable, though it runs a bit warm under heavy load.',1,8),
    (5,4,5,'Love this phone','Camera quality is fantastic and battery lasts all day.',1,20),
    (7,5,4,'Solid mixer','Works well for bread dough, a bit noisy.',1,5),
    (8,6,3,'Decent air fryer','Does the job but basket is smaller than expected.',0,2),
    (10,7,5,'Perfect fit','Soft fabric and true to size.',1,15),
    (11,8,4,'Good quality jacket','Looks great, runs slightly large.',1,6),
    (13,9,5,'Super comfortable','Best yoga pants I have owned.',1,18),
    (15,11,4,'Fun to ride','Great for trails, gearing could be smoother.',1,9),
    (18,13,5,'Couldn''t put it down','Gripping story from start to finish.',1,22),
    (2,15,4,'Great picture quality','Colors are vivid, sound is a bit weak.',1,7),
    (21,15,5,'Satisfying to type on','Love the tactile feel of these switches.',1,11),
    (23,16,4,'Good starter cookware set','Nonstick coating holds up well so far.',0,3),
    (24,17,3,'Warm but itchy','Nice color, wish it were softer.',0,1),
    (9,19,5,'Makes great coffee','Programmable timer is very convenient.',1,14),
    (12,8,5,'Best running shoes','Very lightweight and comfortable for long runs.',1,10),
    (14,14,4,'Cute summer dress','Fits well, fabric is a bit thin.',1,4),
    (17,12,4,'Sturdy tent','Kept us dry in the rain, easy to set up.',1,6),
    (4,2,3,'Works fine','Nothing special but gets the job done.',0,2),
    (6,20,5,'Great protective case','Fits perfectly and looks sleek.',1,9);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.cart_events)
BEGIN
    INSERT INTO dbo.cart_events (customer_id, product_id, event_type, quantity, session_id, device_type, event_ts) VALUES
    (1,1,'view',NULL,'sess-0001-a1','Desktop','2026-01-02T08:00:00'),
    (1,1,'add_to_cart',1,'sess-0001-a1','Desktop','2026-01-02T08:02:00'),
    (1,6,'add_to_cart',2,'sess-0001-a1','Desktop','2026-01-02T08:05:00'),
    (1,1,'purchase',1,'sess-0001-a1','Desktop','2026-01-02T08:10:00'),
    (2,4,'view',NULL,'sess-0002-b2','Mobile','2026-01-03T09:00:00'),
    (2,4,'add_to_cart',1,'sess-0002-b2','Mobile','2026-01-03T09:03:00'),
    (2,4,'purchase',1,'sess-0002-b2','Mobile','2026-01-03T09:10:00'),
    (3,3,'view',NULL,'sess-0003-c3','Desktop','2026-01-03T14:00:00'),
    (3,3,'add_to_cart',1,'sess-0003-c3','Desktop','2026-01-03T14:05:00'),
    (3,3,'purchase',1,'sess-0003-c3','Desktop','2026-01-03T14:12:00'),
    (4,5,'view',NULL,'sess-0004-d4','Mobile','2026-01-04T10:00:00'),
    (4,22,'view',NULL,'sess-0004-d4','Mobile','2026-01-04T10:02:00'),
    (4,5,'add_to_cart',1,'sess-0004-d4','Mobile','2026-01-04T10:04:00'),
    (4,22,'add_to_cart',1,'sess-0004-d4','Mobile','2026-01-04T10:06:00'),
    (4,5,'purchase',1,'sess-0004-d4','Mobile','2026-01-04T10:15:00'),
    (5,7,'view',NULL,'sess-0005-e5','Desktop','2026-01-05T11:00:00'),
    (5,7,'add_to_cart',1,'sess-0005-e5','Desktop','2026-01-05T11:03:00'),
    (5,7,'purchase',1,'sess-0005-e5','Desktop','2026-01-05T11:10:00'),
    (9,13,'view',NULL,'sess-0009-i9','Mobile','2026-01-07T16:00:00'),
    (9,13,'remove_from_cart',1,'sess-0009-i9','Mobile','2026-01-07T16:05:00'),
    (14,20,'view',NULL,'sess-0014-n4','Tablet','2026-01-10T13:00:00'),
    (14,20,'add_to_cart',1,'sess-0014-n4','Tablet','2026-01-10T13:02:00'),
    (16,23,'view',NULL,'sess-0016-p6','Desktop','2026-01-12T09:00:00'),
    (16,23,'add_to_cart',1,'sess-0016-p6','Desktop','2026-01-12T09:04:00'),
    (16,23,'purchase',1,'sess-0016-p6','Desktop','2026-01-12T09:12:00');
END
GO

PRINT 'E-commerce sample data loaded';
GO
/* ============================================================================
   3. SAMPLE DATA — FINANCE
   (fin_accounts before transactions/loans/fraud_alerts;
    market_prices and audit_log are standalone — no FKs)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.fin_accounts)
BEGIN
    INSERT INTO dbo.fin_accounts (account_number, account_type, owner_name, owner_email, balance, currency, opened_date, closed_date, status, routing_number) VALUES
    ('ACC-100001','Checking','John Carter','john.carter@ebbank.example',5230.45,'USD','2020-03-15',NULL,'Active','021000021'),
    ('ACC-100002','Savings','John Carter','john.carter@ebbank.example',15230.10,'USD','2020-03-15',NULL,'Active','021000021'),
    ('ACC-100003','Checking','Maria Lopez','maria.lopez@ebbank.example',3120.00,'USD','2019-07-22',NULL,'Active','026009593'),
    ('ACC-100004','Savings','Maria Lopez','maria.lopez@ebbank.example',42500.75,'USD','2019-07-22',NULL,'Active','026009593'),
    ('ACC-100005','Checking','David Kim','david.kim@ebbank.example',890.25,'USD','2021-01-10',NULL,'Active','121000248'),
    ('ACC-100006','Business Checking','Kim Consulting LLC','accounts@kimconsulting.example',78500.00,'USD','2018-11-05',NULL,'Active','121000248'),
    ('ACC-100007','Checking','Susan Park','susan.park@ebbank.example',210.50,'USD','2022-05-18',NULL,'Active','111000025'),
    ('ACC-100008','Savings','Susan Park','susan.park@ebbank.example',6800.00,'USD','2022-05-18',NULL,'Active','111000025'),
    ('ACC-100009','Checking','Robert Nguyen','robert.nguyen@ebbank.example',15400.30,'USD','2017-09-30',NULL,'Active','091000019'),
    ('ACC-100010','Money Market','Robert Nguyen','robert.nguyen@ebbank.example',55200.00,'USD','2017-09-30',NULL,'Active','091000019'),
    ('ACC-100011','Checking','Linda Foster','linda.foster@ebbank.example',-150.20,'USD','2023-02-14',NULL,'Active','054001204'),
    ('ACC-100012','Checking','Carlos Ramirez','carlos.ramirez@ebbank.example',3200.00,'USD','2020-08-01',NULL,'Active','071000013'),
    ('ACC-100013','Savings','Carlos Ramirez','carlos.ramirez@ebbank.example',12800.60,'USD','2020-08-01',NULL,'Active','071000013'),
    ('ACC-100014','Checking','Nancy White','nancy.white@ebbank.example',980.00,'USD','2016-04-12','2025-12-01','Closed','044000037'),
    ('ACC-100015','Business Checking','White Design Studio','accounts@whitedesign.example',24000.00,'USD','2019-06-25',NULL,'Active','044000037');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.transactions)
BEGIN
    INSERT INTO dbo.transactions (account_id, txn_date, txn_type, amount, currency, merchant_name, merchant_cat, channel, reference_no, balance_after) VALUES
    (1,'2026-01-02T09:00:00','Deposit',2500.00,'USD','Employer Payroll','Payroll','ACH','REF100001',5230.45),
    (1,'2026-01-05T14:22:00','Debit',-85.32,'USD','Whole Foods Market','Groceries','Card','REF100002',5145.13),
    (1,'2026-01-08T10:15:00','Debit',-120.00,'USD','Con Edison','Utilities','ACH','REF100003',5025.13),
    (2,'2026-01-03T08:00:00','Transfer',1000.00,'USD','Internal Transfer','Transfer','Online','REF100004',15230.10),
    (2,'2026-01-15T12:00:00','Interest',12.45,'USD','Interest Payment','Interest','System','REF100005',15242.55),
    (3,'2026-01-04T09:30:00','Debit',-45.00,'USD','Shell Gas Station','Fuel','Card','REF100006',3120.00),
    (3,'2026-01-10T16:45:00','Deposit',500.00,'USD','Mobile Check Deposit','Deposit','Mobile','REF100007',3575.00),
    (4,'2026-01-06T11:00:00','Withdrawal',-2000.00,'USD','ATM Withdrawal','Cash','ATM','REF100008',42500.75),
    (5,'2026-01-07T13:15:00','Debit',-32.50,'USD','Starbucks','Dining','Card','REF100009',890.25),
    (6,'2026-01-09T09:00:00','Wire Transfer',15000.00,'USD','Acme Corp Invoice #4521','Business Income','Wire','REF100010',78500.00),
    (6,'2026-01-12T10:00:00','Debit',-4200.00,'USD','Office Lease LLC','Rent','ACH','REF100011',74300.00),
    (7,'2026-01-08T09:20:00','Debit',-18.75,'USD','Chipotle','Dining','Card','REF100012',210.50),
    (8,'2026-01-11T15:00:00','Deposit',300.00,'USD','Mobile Check Deposit','Deposit','Mobile','REF100013',6800.00),
    (9,'2026-01-05T09:45:00','Deposit',3200.00,'USD','Employer Payroll','Payroll','ACH','REF100014',15400.30),
    (9,'2026-01-14T13:30:00','Debit',-950.00,'USD','American Express Payment','Credit Card Payment','ACH','REF100015',14450.30),
    (10,'2026-01-13T08:00:00','Transfer',5000.00,'USD','Internal Transfer','Transfer','Online','REF100016',55200.00),
    (11,'2026-01-16T09:00:00','Debit',-200.00,'USD','Overdraft Fee','Fee','System','REF100017',-150.20),
    (12,'2026-01-04T14:00:00','Deposit',1200.00,'USD','Client Payment','Business Income','ACH','REF100018',3200.00),
    (13,'2026-01-10T09:15:00','Interest',18.20,'USD','Interest Payment','Interest','System','REF100019',12800.60),
    (14,'2025-12-01T10:00:00','Withdrawal',-980.00,'USD','Account Closure Withdrawal','Closure','Branch','REF100020',0.00),
    (15,'2026-01-06T09:00:00','Wire Transfer',12000.00,'USD','Client Retainer - Johnson Corp','Business Income','Wire','REF100021',24000.00),
    (15,'2026-01-18T10:00:00','Debit',-3500.00,'USD','Studio Rent','Rent','ACH','REF100022',20500.00),
    (1,'2026-01-20T09:00:00','Deposit',2500.00,'USD','Employer Payroll','Payroll','ACH','REF100023',7525.13),
    (2,'2026-01-22T09:00:00','Withdrawal',-500.00,'USD','ATM Withdrawal','Cash','ATM','REF100024',14742.55),
    (3,'2026-01-25T09:00:00','Debit',-65.00,'USD','Target','Retail','Card','REF100025',3510.00),
    (6,'2026-01-27T09:00:00','Wire Transfer',22000.00,'USD','Beta LLC Invoice #4599','Business Income','Wire','REF100026',96300.00),
    (9,'2026-01-28T09:00:00','Debit',-1200.00,'USD','Mortgage Payment','Mortgage','ACH','REF100027',13250.30),
    (11,'2026-01-29T09:00:00','Deposit',500.00,'USD','Mobile Check Deposit','Deposit','Mobile','REF100028',349.80),
    (13,'2026-01-30T09:00:00','Debit',-200.00,'USD','Costco','Retail','Card','REF100029',12600.60),
    (15,'2026-01-31T09:00:00','Debit',-18000.00,'USD','Equipment Purchase - Design Gear Co','Equipment','Wire','REF100030',2500.00);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.loans)
BEGIN
    INSERT INTO dbo.loans (account_id, loan_type, principal, interest_rate, term_months, start_date, maturity_date, outstanding_bal, status) VALUES
    (1,'Auto Loan',28000.00,0.0549,60,'2023-06-01','2028-06-01',19500.00,'Current'),
    (3,'Personal Loan',10000.00,0.0899,36,'2024-01-15','2027-01-15',6200.00,'Current'),
    (4,'Mortgage',350000.00,0.0625,360,'2019-08-01','2049-08-01',312400.00,'Current'),
    (6,'Business Line of Credit',100000.00,0.0725,24,'2022-03-01','2024-03-01',0.00,'Paid Off'),
    (7,'Auto Loan',22000.00,0.0599,48,'2023-01-10','2027-01-10',14800.00,'Current'),
    (9,'Mortgage',420000.00,0.0575,360,'2017-09-01','2047-09-01',378900.00,'Current'),
    (11,'Personal Loan',5000.00,0.1199,24,'2024-06-01','2026-06-01',1200.00,'Delinquent'),
    (12,'Auto Loan',18000.00,0.0649,60,'2022-08-01','2027-08-01',7400.00,'Current'),
    (13,'Home Equity Loan',45000.00,0.0699,120,'2021-05-01','2031-05-01',31200.00,'Current'),
    (14,'Personal Loan',8000.00,0.0999,36,'2020-04-12','2023-04-12',0.00,'Charged Off'),
    (15,'Business Line of Credit',50000.00,0.0799,36,'2023-07-01','2026-07-01',18500.00,'Current'),
    (2,'Student Loan',35000.00,0.0450,120,'2015-09-01','2025-09-01',2100.00,'Current'),
    (5,'Personal Loan',3000.00,0.1299,12,'2025-11-01','2026-11-01',2400.00,'Current'),
    (10,'Auto Loan',31000.00,0.0575,72,'2024-02-15','2030-02-15',27200.00,'Current'),
    (8,'Personal Loan',4500.00,0.1050,24,'2025-03-01','2027-03-01',3100.00,'Default');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.fraud_alerts)
BEGIN
    INSERT INTO dbo.fraud_alerts (txn_id, account_id, alert_type, risk_score, detected_at, resolved_at, status, notes) VALUES
    (10,6,'High-Value Transaction',68.50,'2026-01-09T09:05:00',NULL,'Investigating','Wire transfer significantly above account average'),
    (26,6,'High-Value Transaction',72.00,'2026-01-27T09:05:00',NULL,'Open','Large wire transfer flagged for manual review'),
    (30,15,'High-Value Transaction',81.25,'2026-01-31T09:05:00',NULL,'Open','Equipment purchase wire exceeds typical spend pattern'),
    (17,11,'Overdraft Pattern',45.00,'2026-01-16T09:05:00','2026-01-17T10:00:00','Closed - Not Fraud','Confirmed legitimate overdraft fee'),
    (NULL,9,'Unusual Login Location',55.30,'2026-01-14T08:00:00',NULL,'Investigating','Login detected from new device or location'),
    (2,1,'Card Testing Pattern',30.00,'2026-01-05T14:25:00','2026-01-06T09:00:00','Closed - Not Fraud','Reviewed, normal grocery purchase'),
    (21,15,'High-Value Transaction',60.00,'2026-01-06T09:05:00','2026-01-08T09:00:00','Closed - Not Fraud','Verified client retainer payment'),
    (NULL,4,'Velocity Check Triggered',40.10,'2026-01-06T11:30:00','2026-01-06T15:00:00','Closed - Not Fraud','Multiple ATM withdrawals within short window, confirmed by customer'),
    (8,4,'Large Cash Withdrawal',52.75,'2026-01-06T11:05:00',NULL,'Investigating','ATM withdrawal above typical daily limit'),
    (NULL,11,'Negative Balance Alert',35.00,'2026-01-16T09:10:00','2026-01-18T09:00:00','Closed - Not Fraud','Customer contacted, resolved with deposit'),
    (16,10,'Large Internal Transfer',25.00,'2026-01-13T08:05:00','2026-01-13T12:00:00','Closed - Not Fraud','Routine transfer between own accounts'),
    (NULL,6,'New Payee Added',48.60,'2026-01-12T10:05:00',NULL,'Open','New ACH payee added shortly before large debit'),
    (14,9,'Deposit Anomaly',20.00,'2026-01-05T09:50:00','2026-01-05T14:00:00','Closed - Not Fraud','Confirmed payroll deposit'),
    (NULL,7,'Card Not Present Risk',58.90,'2026-01-08T09:25:00',NULL,'Investigating','Online transaction risk score elevated'),
    (27,9,'Large Mortgage Debit',22.00,'2026-01-28T09:05:00','2026-01-28T12:00:00','Closed - Not Fraud','Confirmed recurring mortgage payment');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.market_prices)
BEGIN
    INSERT INTO dbo.market_prices (ticker, price_date, open_price, high_price, low_price, close_price, volume, adjusted_close) VALUES
    ('AAPL','2026-01-02',190.00,192.50,189.20,191.80,52000000,191.80),
    ('AAPL','2026-01-03',191.80,193.00,190.50,192.40,48000000,192.40),
    ('AAPL','2026-01-04',192.40,194.10,191.80,193.75,50500000,193.75),
    ('AAPL','2026-01-05',193.75,195.00,192.90,194.20,47000000,194.20),
    ('AAPL','2026-01-06',194.20,196.30,193.50,195.90,53000000,195.90),
    ('MSFT','2026-01-02',410.00,414.20,408.50,412.75,28000000,412.75),
    ('MSFT','2026-01-03',412.75,416.00,411.00,415.30,26500000,415.30),
    ('MSFT','2026-01-04',415.30,418.90,414.00,417.60,27000000,417.60),
    ('MSFT','2026-01-05',417.60,420.00,416.20,419.10,25500000,419.10),
    ('MSFT','2026-01-06',419.10,422.50,418.00,421.80,29000000,421.80),
    ('GOOGL','2026-01-02',142.00,144.10,141.20,143.50,21000000,143.50),
    ('GOOGL','2026-01-03',143.50,145.00,142.80,144.20,19500000,144.20),
    ('GOOGL','2026-01-04',144.20,146.30,143.60,145.75,20500000,145.75),
    ('GOOGL','2026-01-05',145.75,147.00,144.90,146.30,18800000,146.30),
    ('GOOGL','2026-01-06',146.30,148.20,145.50,147.90,22000000,147.90),
    ('AMZN','2026-01-02',178.00,180.50,177.20,179.80,33000000,179.80),
    ('AMZN','2026-01-03',179.80,182.00,178.90,181.40,31000000,181.40),
    ('AMZN','2026-01-04',181.40,183.50,180.60,182.90,32500000,182.90),
    ('AMZN','2026-01-05',182.90,184.70,181.80,183.60,30000000,183.60),
    ('AMZN','2026-01-06',183.60,186.00,182.90,185.20,34000000,185.20);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.audit_log)
BEGIN
    INSERT INTO dbo.audit_log (table_name, operation, record_id, changed_by, changed_at, old_values, new_values, ip_address, session_id) VALUES
    ('patients','UPDATE',5,'admin@ebhealth.example','2026-01-08T10:00:00','email=old_eva@example.com','email=eva.rodriguez@example.com','10.0.0.15','sess-adm-001'),
    ('fin_accounts','UPDATE',11,'admin@ebbank.example','2026-01-16T09:15:00','status=Active','status=Active; balance flagged','10.0.0.22','sess-adm-002'),
    ('products','UPDATE',25,'admin@ebshop.example','2026-01-18T11:00:00','active=1','active=0','10.0.0.30','sess-adm-003'),
    ('orders','UPDATE',18,'admin@ebshop.example','2026-01-14T13:00:00','status=Delivered','status=Refunded','10.0.0.30','sess-adm-004'),
    ('violations','UPDATE',3,'admin@ebgov.example','2026-01-20T09:00:00','status=Outstanding','status=Paid','10.0.0.44','sess-adm-005'),
    ('inventory','UPDATE',7,'admin@ebretail.example','2026-01-19T15:00:00','quantity_on_hand=50','quantity_on_hand=12','10.0.0.55','sess-adm-006'),
    ('gov_employees','UPDATE',2,'admin@ebgov.example','2026-01-11T09:00:00','salary=68000.00','salary=71000.00','10.0.0.44','sess-adm-007'),
    ('fin_accounts','UPDATE',14,'admin@ebbank.example','2025-12-01T10:00:00','status=Active','status=Closed','10.0.0.22','sess-adm-008'),
    ('patients','UPDATE',12,'admin@ebhealth.example','2026-01-13T09:00:00','phone=206-555-0000','phone=206-555-0212','10.0.0.15','sess-adm-009'),
    ('promotions','UPDATE',3,'admin@ebretail.example','2026-01-15T10:00:00','active=1','active=0','10.0.0.55','sess-adm-010'),
    ('machines','UPDATE',4,'admin@ebmfg.example','2026-01-17T08:00:00','status=Operational','status=Maintenance','10.0.0.66','sess-adm-011'),
    ('permits','UPDATE',6,'admin@ebgov.example','2026-01-21T09:00:00','status=Pending','status=Approved','10.0.0.44','sess-adm-012'),
    ('customers','UPDATE',6,'admin@ebshop.example','2026-01-06T09:00:00','loyalty_points=0','loyalty_points=50','10.0.0.30','sess-adm-013'),
    ('loans','UPDATE',7,'admin@ebbank.example','2026-01-22T09:00:00','status=Current','status=Delinquent','10.0.0.22','sess-adm-014'),
    ('service_requests','UPDATE',5,'admin@ebgov.example','2026-01-23T09:00:00','status=Open','status=Resolved','10.0.0.44','sess-adm-015');
END
GO

PRINT 'Finance sample data loaded';
GO
/* ============================================================================
   3. SAMPLE DATA — MANUFACTURING / IoT
   (machines -> sensors -> production_batches -> sensor_readings/quality_checks)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.machines)
BEGIN
    INSERT INTO dbo.machines (machine_code, machine_name, machine_type, location, manufacturer, model_no, install_date, status) VALUES
    ('MCH-001','CNC Mill Alpha','CNC Mill','Plant 1 - Bay A','Haas Automation','VF-2SS','2019-05-10','Operational'),
    ('MCH-002','CNC Lathe Beta','CNC Lathe','Plant 1 - Bay A','Haas Automation','ST-20','2019-05-10','Operational'),
    ('MCH-003','Injection Molder 1','Injection Molder','Plant 1 - Bay B','Engel','e-motion 310','2018-11-02','Operational'),
    ('MCH-004','Injection Molder 2','Injection Molder','Plant 1 - Bay B','Engel','e-motion 310','2018-11-02','Maintenance'),
    ('MCH-005','Robotic Welder A','Welding Robot','Plant 1 - Bay C','FANUC','ARC Mate 100iD','2020-02-15','Operational'),
    ('MCH-006','Robotic Welder B','Welding Robot','Plant 1 - Bay C','FANUC','ARC Mate 100iD','2020-02-15','Operational'),
    ('MCH-007','Conveyor Line 1','Conveyor','Plant 1 - Bay D','Dorner','2200 Series','2017-07-20','Operational'),
    ('MCH-008','Packaging Line 1','Packaging Machine','Plant 2 - Bay A','Bosch Packaging','Pack 201','2021-03-30','Operational'),
    ('MCH-009','Stamping Press 1','Stamping Press','Plant 2 - Bay B','Schuler','MSP 800','2016-09-12','Idle'),
    ('MCH-010','Stamping Press 2','Stamping Press','Plant 2 - Bay B','Schuler','MSP 800','2016-09-12','Operational'),
    ('MCH-011','Paint Booth 1','Paint Booth','Plant 2 - Bay C','Wagner','PB-500','2022-01-05','Operational'),
    ('MCH-012','Quality Scanner 1','3D Scanner','Plant 2 - Bay D','Zeiss','COMET Pro AE','2021-08-18','Offline');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.sensors)
BEGIN
    INSERT INTO dbo.sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active) VALUES
    (1,'SEN-001-T','Temperature','C',10.0,80.0,1),
    (1,'SEN-001-V','Vibration','mm/s',0.0,5.0,1),
    (2,'SEN-002-T','Temperature','C',10.0,75.0,1),
    (2,'SEN-002-RPM','RPM','rpm',0.0,4000.0,1),
    (3,'SEN-003-T','Temperature','C',150.0,250.0,1),
    (3,'SEN-003-P','Pressure','bar',50.0,200.0,1),
    (4,'SEN-004-T','Temperature','C',150.0,250.0,1),
    (4,'SEN-004-P','Pressure','bar',50.0,200.0,0),
    (5,'SEN-005-CUR','Current','A',5.0,45.0,1),
    (6,'SEN-006-CUR','Current','A',5.0,45.0,1),
    (7,'SEN-007-SPD','Speed','m/min',0.0,30.0,1),
    (8,'SEN-008-CNT','Count','units/min',0.0,120.0,1),
    (9,'SEN-009-P','Pressure','bar',100.0,400.0,1),
    (10,'SEN-010-P','Pressure','bar',100.0,400.0,1),
    (10,'SEN-010-T','Temperature','C',10.0,60.0,1),
    (11,'SEN-011-HUM','Humidity','%RH',30.0,70.0,1),
    (11,'SEN-011-T','Temperature','C',15.0,35.0,1),
    (12,'SEN-012-ACC','Accuracy','mm',0.0,0.05,0),
    (5,'SEN-005-T','Temperature','C',20.0,90.0,1),
    (7,'SEN-007-T','Temperature','C',10.0,50.0,1);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.production_batches)
BEGIN
    INSERT INTO dbo.production_batches (batch_number, machine_id, product_code, start_time, end_time, planned_qty, actual_qty, status, operator_id, notes) VALUES
    ('BATCH-2601-01',1,'PN-2201','2026-01-05T06:00:00','2026-01-05T14:00:00',500,498,'Completed',101,'Minor scrap rate, within tolerance'),
    ('BATCH-2601-02',2,'PN-2202','2026-01-05T06:00:00','2026-01-05T14:00:00',400,400,'Completed',102,NULL),
    ('BATCH-2601-03',3,'PN-3301','2026-01-06T06:00:00','2026-01-06T18:00:00',2000,1985,'Completed',103,'Two mold cavities underperformed'),
    ('BATCH-2601-04',4,'PN-3302','2026-01-06T06:00:00',NULL,1800,NULL,'In Progress',103,'Paused for maintenance'),
    ('BATCH-2601-05',5,'PN-4401','2026-01-07T06:00:00','2026-01-07T14:00:00',300,300,'Completed',104,NULL),
    ('BATCH-2601-06',6,'PN-4402','2026-01-07T06:00:00','2026-01-07T14:00:00',300,297,'Completed',104,'Three units failed weld inspection'),
    ('BATCH-2601-07',7,'PN-5501','2026-01-08T06:00:00','2026-01-08T22:00:00',5000,4990,'Completed',105,NULL),
    ('BATCH-2601-08',8,'PN-6601','2026-01-09T06:00:00','2026-01-09T14:00:00',10000,9950,'Completed',106,'Packaging line jam mid-shift'),
    ('BATCH-2601-09',9,'PN-7701','2026-01-10T06:00:00',NULL,1500,NULL,'On Hold',107,'Machine idle, awaiting parts'),
    ('BATCH-2601-10',10,'PN-7702','2026-01-10T06:00:00','2026-01-10T16:00:00',1500,1480,'Completed',107,NULL),
    ('BATCH-2601-11',11,'PN-8801','2026-01-11T06:00:00','2026-01-11T14:00:00',600,595,'Completed',108,NULL),
    ('BATCH-2601-12',1,'PN-2201','2026-01-12T06:00:00','2026-01-12T14:00:00',500,500,'Completed',101,NULL),
    ('BATCH-2601-13',3,'PN-3301','2026-01-13T06:00:00','2026-01-13T18:00:00',2000,1978,'Completed',103,'Higher scrap due to material batch variance'),
    ('BATCH-2601-14',9,'PN-7701','2026-01-14T06:00:00','2026-01-14T20:00:00',1500,1490,'Completed',107,'Resumed after part replacement'),
    ('BATCH-2601-15',5,'PN-4401','2026-01-15T06:00:00',NULL,300,NULL,'In Progress',104,NULL);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.sensor_readings)
BEGIN
    INSERT INTO dbo.sensor_readings (sensor_id, machine_id, reading_ts, value, quality_flag, raw_value, unit, batch_id, anomaly) VALUES
    (1,1,'2026-01-05T06:15:00',45.20,0,45.20,'C',1,0),
    (2,1,'2026-01-05T06:15:00',1.20,0,1.20,'mm/s',1,0),
    (1,1,'2026-01-05T10:00:00',52.80,0,52.80,'C',1,0),
    (2,1,'2026-01-05T10:00:00',1.35,0,1.35,'mm/s',1,0),
    (3,2,'2026-01-05T06:15:00',40.10,0,40.10,'C',2,0),
    (4,2,'2026-01-05T06:15:00',3200.00,0,3200.00,'rpm',2,0),
    (5,3,'2026-01-06T06:30:00',210.50,0,210.50,'C',3,0),
    (6,3,'2026-01-06T06:30:00',120.00,0,120.00,'bar',3,0),
    (5,3,'2026-01-06T12:00:00',265.00,1,265.00,'C',3,1),
    (6,3,'2026-01-06T12:00:00',210.00,1,210.00,'bar',3,1),
    (7,4,'2026-01-06T07:00:00',205.00,0,205.00,'C',4,0),
    (8,4,'2026-01-06T07:00:00',110.00,0,110.00,'bar',4,0),
    (9,5,'2026-01-07T06:20:00',22.50,0,22.50,'A',5,0),
    (19,5,'2026-01-07T06:20:00',55.00,0,55.00,'C',5,0),
    (10,6,'2026-01-07T06:20:00',24.10,0,24.10,'A',6,0),
    (11,7,'2026-01-08T06:30:00',18.00,0,18.00,'m/min',7,0),
    (20,7,'2026-01-08T06:30:00',30.00,0,30.00,'C',7,0),
    (12,8,'2026-01-09T06:15:00',95.00,0,95.00,'units/min',8,0),
    (12,8,'2026-01-09T09:00:00',125.00,1,125.00,'units/min',8,1),
    (13,9,'2026-01-10T06:15:00',350.00,0,350.00,'bar',9,0),
    (14,10,'2026-01-10T06:15:00',210.00,0,210.00,'bar',10,0),
    (15,10,'2026-01-10T06:15:00',35.00,0,35.00,'C',10,0),
    (16,11,'2026-01-11T06:20:00',55.00,0,55.00,'%RH',11,0),
    (17,11,'2026-01-11T06:20:00',24.00,0,24.00,'C',11,0),
    (1,1,'2026-01-12T06:15:00',44.00,0,44.00,'C',12,0),
    (2,1,'2026-01-12T06:15:00',1.10,0,1.10,'mm/s',12,0),
    (5,3,'2026-01-13T06:30:00',215.00,0,215.00,'C',13,0),
    (6,3,'2026-01-13T06:30:00',125.00,0,125.00,'bar',13,0),
    (13,9,'2026-01-14T06:30:00',360.00,0,360.00,'bar',14,0),
    (9,5,'2026-01-15T06:20:00',23.00,0,23.00,'A',15,0),
    (19,5,'2026-01-15T06:20:00',56.00,0,56.00,'C',15,0),
    (3,2,'2026-01-05T12:00:00',85.00,1,85.00,'C',2,1),
    (4,2,'2026-01-05T12:00:00',4200.00,1,4200.00,'rpm',2,1),
    (7,4,'2026-01-06T13:00:00',258.00,1,258.00,'C',4,1),
    (8,4,'2026-01-06T13:00:00',215.00,1,215.00,'bar',4,1),
    (11,7,'2026-01-08T14:00:00',19.50,0,19.50,'m/min',7,0),
    (20,7,'2026-01-08T14:00:00',32.00,0,32.00,'C',7,0),
    (16,11,'2026-01-11T14:00:00',72.00,1,72.00,'%RH',11,1),
    (17,11,'2026-01-11T14:00:00',36.50,1,36.50,'C',11,1),
    (14,10,'2026-01-10T14:00:00',420.00,1,420.00,'bar',10,1);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.quality_checks)
BEGIN
    INSERT INTO dbo.quality_checks (batch_id, check_type, checked_at, result, defect_count, sample_size, inspector_name, notes) VALUES
    (1,'Dimensional Inspection','2026-01-05T14:15:00','Pass',2,50,'Tom Reyes','Within spec'),
    (2,'Visual Inspection','2026-01-05T14:15:00','Pass',0,40,'Tom Reyes',NULL),
    (3,'Dimensional Inspection','2026-01-06T18:15:00','Conditional Pass',15,100,'Angela Price','Two cavities need mold adjustment'),
    (5,'Weld Integrity Test','2026-01-07T14:15:00','Pass',0,30,'Marcus Lee',NULL),
    (6,'Weld Integrity Test','2026-01-07T14:15:00','Fail',3,30,'Marcus Lee','Porosity detected in 3 welds'),
    (7,'Visual Inspection','2026-01-08T22:15:00','Pass',10,200,'Nina Patel','Minor scuffing, acceptable'),
    (8,'Packaging Seal Test','2026-01-09T14:15:00','Rework Required',50,200,'Nina Patel','Seal integrity below threshold, batch reworked'),
    (10,'Dimensional Inspection','2026-01-10T16:15:00','Pass',5,60,'Angela Price',NULL),
    (11,'Paint Finish Inspection','2026-01-11T14:15:00','Pass',5,40,'Carlos Diaz','Slight orange peel texture, within tolerance'),
    (12,'Dimensional Inspection','2026-01-12T14:15:00','Pass',0,50,'Tom Reyes',NULL),
    (13,'Dimensional Inspection','2026-01-13T18:15:00','Fail',22,100,'Angela Price','Material batch variance caused out-of-spec parts'),
    (14,'Pressure Test','2026-01-14T20:15:00','Pass',10,150,'Marcus Lee',NULL),
    (1,'Weight Check','2026-01-05T14:30:00','Pass',1,50,'Tom Reyes',NULL),
    (3,'Visual Inspection','2026-01-06T18:30:00','Pass',3,100,'Angela Price',NULL),
    (6,'Visual Inspection','2026-01-07T14:30:00','Conditional Pass',8,30,'Marcus Lee','Cosmetic defects only'),
    (7,'Dimensional Inspection','2026-01-08T22:30:00','Pass',5,200,'Nina Patel',NULL),
    (8,'Weight Check','2026-01-09T14:30:00','Pass',2,200,'Nina Patel',NULL),
    (10,'Weight Check','2026-01-10T16:30:00','Pass',3,60,'Angela Price',NULL),
    (11,'Dimensional Inspection','2026-01-11T14:30:00','Pass',1,40,'Carlos Diaz',NULL),
    (13,'Weight Check','2026-01-13T18:30:00','Conditional Pass',18,100,'Angela Price','Retest scheduled');
END
GO

PRINT 'Manufacturing/IoT sample data loaded';
GO
/* ============================================================================
   3. SAMPLE DATA — RETAIL
   (stores before inventory/pos_transactions; pos_transactions before pos_items;
    product_id/customer_id cross-reference the E-commerce tables loaded above)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.stores)
BEGIN
    INSERT INTO dbo.stores (store_code, store_name, address, city, state, zip_code, phone, manager_name, sq_footage, active, opened_date) VALUES
    ('ST-001','EB Retail - Manhattan','500 5th Ave','New York','NY','10110','212-555-0401','Rebecca Stone',8500,1,'2015-03-01'),
    ('ST-002','EB Retail - Brooklyn','200 Flatbush Ave','Brooklyn','NY','11217','718-555-0402','Marcus Diaz',6200,1,'2017-06-15'),
    ('ST-003','EB Retail - SF Union Square','333 Powell St','San Francisco','CA','94102','415-555-0403','Jenny Wu',7300,1,'2016-09-20'),
    ('ST-004','EB Retail - Chicago Loop','111 State St','Chicago','IL','60603','312-555-0404','Aaron Bell',6800,1,'2014-11-10'),
    ('ST-005','EB Retail - Houston Galleria','5085 Westheimer Rd','Houston','TX','77056','713-555-0405','Priya Shah',9200,1,'2018-02-25'),
    ('ST-006','EB Retail - Phoenix Scottsdale','7014 E Camelback Rd','Phoenix','AZ','85251','602-555-0406','Derek Holt',5400,1,'2019-07-04'),
    ('ST-007','EB Retail - Seattle Downtown','1601 5th Ave','Seattle','WA','98101','206-555-0407','Laura Kim',6100,1,'2020-01-18'),
    ('ST-008','EB Retail - Denver Cherry Creek','2800 E 1st Ave','Denver','CO','80206','303-555-0408','Sam Osei',5800,1,'2016-05-30'),
    ('ST-009','EB Retail - Boston Back Bay','800 Boylston St','Boston','MA','02199','617-555-0409','Rachel Kim',7000,1,'2015-10-12'),
    ('ST-010','EB Retail - Miami Brickell','901 S Miami Ave','Miami','FL','33130','305-555-0410','Tony Marquez',6400,0,'2013-08-08');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.inventory)
BEGIN
    INSERT INTO dbo.inventory (store_id, product_id, quantity_on_hand, reorder_point, reorder_qty, last_replenish, unit_cost, updated_at) VALUES
    (1,1,45,20,50,'2026-01-01T08:00:00',42.00,'2026-01-15T09:00:00'),
    (1,3,8,10,15,'2025-12-20T08:00:00',780.00,'2026-01-15T09:00:00'),
    (1,5,30,15,40,'2026-01-05T08:00:00',430.00,'2026-01-15T09:00:00'),
    (2,2,12,8,10,'2026-01-02T08:00:00',310.00,'2026-01-15T09:00:00'),
    (2,4,60,25,60,'2026-01-03T08:00:00',9.50,'2026-01-15T09:00:00'),
    (2,6,90,30,80,'2026-01-04T08:00:00',3.50,'2026-01-15T09:00:00'),
    (3,7,15,10,20,'2026-01-02T08:00:00',140.00,'2026-01-15T09:00:00'),
    (3,8,22,15,30,'2026-01-03T08:00:00',52.00,'2026-01-15T09:00:00'),
    (3,9,18,12,25,'2026-01-03T08:00:00',38.00,'2026-01-15T09:00:00'),
    (4,10,100,40,120,'2026-01-04T08:00:00',6.00,'2026-01-15T09:00:00'),
    (4,11,25,15,30,'2026-01-04T08:00:00',32.00,'2026-01-15T09:00:00'),
    (4,12,20,15,30,'2026-01-05T08:00:00',40.00,'2026-01-15T09:00:00'),
    (5,13,35,20,40,'2026-01-05T08:00:00',18.00,'2026-01-15T09:00:00'),
    (5,14,28,15,30,'2026-01-06T08:00:00',22.00,'2026-01-15T09:00:00'),
    (5,15,6,8,10,'2025-12-28T08:00:00',260.00,'2026-01-15T09:00:00'),
    (6,16,55,25,60,'2026-01-06T08:00:00',9.00,'2026-01-15T09:00:00'),
    (6,17,14,10,20,'2026-01-07T08:00:00',85.00,'2026-01-15T09:00:00'),
    (6,18,40,20,50,'2026-01-07T08:00:00',6.50,'2026-01-15T09:00:00'),
    (7,19,33,20,40,'2026-01-08T08:00:00',9.00,'2026-01-15T09:00:00'),
    (7,20,48,25,60,'2026-01-08T08:00:00',16.00,'2026-01-15T09:00:00'),
    (7,21,19,15,30,'2026-01-09T08:00:00',28.00,'2026-01-15T09:00:00'),
    (8,22,26,15,30,'2026-01-09T08:00:00',8.00,'2026-01-15T09:00:00'),
    (8,23,11,10,20,'2026-01-10T08:00:00',65.00,'2026-01-15T09:00:00'),
    (8,24,17,12,25,'2026-01-10T08:00:00',21.00,'2026-01-15T09:00:00'),
    (9,25,4,10,20,'2025-12-15T08:00:00',9.50,'2026-01-15T09:00:00'),
    (9,1,38,20,50,'2026-01-11T08:00:00',42.00,'2026-01-15T09:00:00'),
    (9,2,9,8,10,'2026-01-11T08:00:00',310.00,'2026-01-15T09:00:00'),
    (10,3,5,10,15,'2025-12-10T08:00:00',780.00,'2026-01-15T09:00:00'),
    (10,4,70,25,60,'2026-01-12T08:00:00',9.50,'2026-01-15T09:00:00'),
    (10,5,20,15,40,'2026-01-12T08:00:00',430.00,'2026-01-15T09:00:00');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.pos_transactions)
BEGIN
    INSERT INTO dbo.pos_transactions (store_id, register_no, txn_date, cashier_id, customer_id, subtotal, tax_amount, discount_amount, total_amount, payment_method, loyalty_used) VALUES
    (1,1,'2026-01-10T10:15:00',201,1,89.99,7.20,0,97.19,'Credit Card',0),
    (1,2,'2026-01-10T14:30:00',202,NULL,29.98,2.40,0,32.38,'Cash',0),
    (2,1,'2026-01-10T11:00:00',203,3,64.97,5.20,10.00,60.17,'Debit Card',50),
    (2,2,'2026-01-11T09:45:00',204,NULL,24.99,2.00,0,26.99,'Mobile Pay',0),
    (3,1,'2026-01-11T13:00:00',205,5,249.99,20.00,0,269.99,'Credit Card',0),
    (3,2,'2026-01-12T10:00:00',206,NULL,99.99,8.00,0,107.99,'Cash',0),
    (4,1,'2026-01-12T15:00:00',207,7,59.97,4.80,0,64.77,'Credit Card',0),
    (4,2,'2026-01-13T09:00:00',208,NULL,79.99,6.40,0,86.39,'Debit Card',0),
    (5,1,'2026-01-13T11:30:00',209,9,99.98,8.00,5.00,102.98,'Credit Card',20),
    (5,2,'2026-01-14T16:00:00',210,NULL,59.99,4.80,0,64.79,'Mobile Pay',0),
    (6,1,'2026-01-14T10:00:00',211,11,29.99,2.40,0,32.39,'Cash',0),
    (6,2,'2026-01-15T13:00:00',212,NULL,159.99,12.80,0,172.79,'Credit Card',0),
    (7,1,'2026-01-15T09:30:00',213,13,39.99,3.20,0,43.19,'Debit Card',0),
    (7,2,'2026-01-16T14:00:00',214,NULL,22.99,1.84,0,24.83,'Mobile Pay',0),
    (8,1,'2026-01-16T10:15:00',215,15,139.99,11.20,0,151.19,'Credit Card',0),
    (8,2,'2026-01-17T15:30:00',216,NULL,54.99,4.40,0,59.39,'Cash',0),
    (9,1,'2026-01-17T11:00:00',217,17,27.99,2.24,0,30.23,'Credit Card',0),
    (9,2,'2026-01-18T09:45:00',218,NULL,34.98,2.80,0,37.78,'Debit Card',0),
    (10,1,'2026-01-18T13:15:00',219,19,699.99,56.00,0,755.99,'Credit Card',0),
    (10,2,'2026-01-19T10:00:00',220,NULL,14.99,1.20,0,16.19,'Cash',0),
    (1,3,'2026-01-19T16:00:00',201,2,14.99,1.20,0,16.19,'Mobile Pay',0),
    (3,3,'2026-01-20T09:15:00',205,4,699.99,56.00,0,755.99,'Credit Card',0),
    (5,3,'2026-01-20T14:00:00',209,6,79.99,6.40,0,86.39,'Debit Card',0),
    (7,3,'2026-01-21T11:30:00',213,8,89.99,7.20,0,97.19,'Credit Card',0),
    (9,3,'2026-01-21T15:00:00',217,10,24.99,2.00,0,26.99,'Cash',0);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.pos_items)
BEGIN
    INSERT INTO dbo.pos_items (pos_txn_id, product_id, quantity, unit_price, discount, line_total) VALUES
    (1,1,1,89.99,0,89.99),
    (2,6,2,14.99,0,29.98),
    (3,4,2,24.99,0,49.98),
    (3,6,1,14.99,0,14.99),
    (4,4,1,24.99,0,24.99),
    (5,7,1,249.99,0,249.99),
    (6,8,1,99.99,0,99.99),
    (7,10,3,19.99,0,59.97),
    (8,11,1,79.99,0,79.99),
    (9,13,2,49.99,0,99.98),
    (10,14,1,59.99,0,59.99),
    (11,16,1,29.99,0,29.99),
    (12,17,1,159.99,0,159.99),
    (13,20,1,39.99,0,39.99),
    (14,22,1,22.99,0,22.99),
    (15,23,1,139.99,0,139.99),
    (16,24,1,54.99,0,54.99),
    (17,25,1,27.99,0,27.99),
    (18,6,1,14.99,0,14.99),
    (18,10,1,19.99,0,19.99),
    (19,5,1,699.99,0,699.99),
    (20,6,1,14.99,0,14.99),
    (21,6,1,14.99,0,14.99),
    (22,5,1,699.99,0,699.99),
    (23,11,1,79.99,0,79.99),
    (24,1,1,89.99,0,89.99),
    (25,4,1,24.99,0,24.99);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.loyalty_members)
BEGIN
    INSERT INTO dbo.loyalty_members (customer_id, member_number, first_name, last_name, email, tier, points_balance, total_spent, enrolled_date, last_activity, active) VALUES
    (1,'LM-000001','Sophia','Turner','sophia.turner@example.com','Gold',450,2340.50,'2022-03-01','2026-01-20T09:00:00',1),
    (2,'LM-000002','Liam','Martin','liam.martin@example.com','Bronze',120,540.00,'2023-05-15','2026-01-10T09:00:00',1),
    (3,'LM-000003','Ava','Robinson','ava.robinson@example.com','Platinum',890,8900.25,'2020-11-20','2026-01-21T09:00:00',1),
    (4,'LM-000004','Noah','Clark','noah.clark@example.com','Bronze',60,210.00,'2024-01-10','2025-12-15T09:00:00',1),
    (5,'LM-000005','Emma','Lewis','emma.lewis@example.com','Silver',340,1890.00,'2021-07-04','2026-01-05T09:00:00',1),
    (6,'LM-000006','Oliver','Walker','oliver.walker@example.com','Bronze',0,50.00,'2025-09-01',NULL,0),
    (7,'LM-000007','Charlotte','Hall','charlotte.hall@example.com','Silver',210,1200.00,'2022-08-19','2026-01-06T09:00:00',1),
    (8,'LM-000008','Elijah','Young','elijah.young@example.com','Diamond',780,15600.00,'2019-04-30','2026-01-21T09:00:00',1),
    (9,'LM-000009','Amelia','King','amelia.king@example.com','Bronze',55,180.00,'2024-06-15','2025-11-20T09:00:00',1),
    (10,'LM-000010','Lucas','Wright','lucas.wright@example.com','Diamond',999,21000.00,'2018-02-14','2026-01-24T09:00:00',1),
    (11,'LM-000011','Mia','Scott','mia.scott@example.com','Silver',130,950.00,'2023-03-22','2025-12-30T09:00:00',1),
    (12,'LM-000012','Benjamin','Baker','benjamin.baker@example.com','Gold',410,3100.00,'2021-10-05','2026-01-09T09:00:00',1),
    (13,'LM-000013','Harper','Green','harper.green@example.com','Bronze',25,100.00,'2025-01-18',NULL,1),
    (14,'LM-000014','James','Adams','james.adams@example.com','Gold',560,4200.00,'2020-06-01','2026-01-10T09:00:00',1),
    (15,'LM-000015','Ella','Nelson','ella.nelson@example.com','Platinum',675,7200.00,'2019-12-11','2026-01-11T09:00:00',1),
    (16,'LM-000016','Henry','Carter','henry.carter@example.com','Bronze',90,300.00,'2024-03-09','2025-10-05T09:00:00',1),
    (17,'LM-000017','Grace','Mitchell','grace.mitchell@example.com','Silver',320,1750.00,'2022-02-28','2026-01-12T09:00:00',1),
    (18,'LM-000018','Jack','Perez','jack.perez@example.com','Bronze',15,60.00,'2025-07-22',NULL,1),
    (19,'LM-000019','Chloe','Roberts','chloe.roberts@example.com','Gold',480,3600.00,'2021-05-17','2026-01-13T09:00:00',1),
    (NULL,'LM-000020','Tyler','Fox','tyler.fox@example.com','Bronze',0,0.00,'2026-01-05',NULL,1);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.promotions)
BEGIN
    INSERT INTO dbo.promotions (promo_code, name, discount_type, discount_value, min_purchase, start_date, end_date, usage_limit, times_used, active) VALUES
    ('WINTER10','Winter Sale 10% Off','Percent',10.0000,50.00,'2026-01-01','2026-02-15',500,120,1),
    ('FREESHIP','Free Shipping Weekend','Fixed',5.99,25.00,'2026-01-10','2026-01-12',1000,340,0),
    ('SAVE20','Save $20 on Orders Over $150','Fixed',20.00,150.00,'2026-01-05','2026-03-01',300,45,1),
    ('NEWCUST15','New Customer 15% Off','Percent',15.0000,0.00,'2025-01-01',NULL,NULL,890,1),
    ('LOYALTY5','Loyalty Member 5% Off','Percent',5.0000,0.00,'2025-06-01',NULL,NULL,2100,1),
    ('SPRING25','Spring Clearance 25% Off','Percent',25.0000,75.00,'2026-03-01','2026-04-15',200,0,1),
    ('BUNDLE10','Bundle Deal $10 Off','Fixed',10.00,100.00,'2026-01-15','2026-02-28',400,60,1),
    ('VIP20','VIP Customer 20% Off','Percent',20.0000,0.00,'2025-11-01','2026-12-31',NULL,310,1),
    ('HOLIDAY30','Holiday Mega Sale 30% Off','Percent',30.0000,100.00,'2025-11-25','2025-12-31',600,598,0),
    ('FLASH50','Flash Sale $50 Off','Fixed',50.00,300.00,'2026-01-20','2026-01-21',100,88,0);
END
GO

PRINT 'Retail sample data loaded';
GO
/* ============================================================================
   3. SAMPLE DATA — GOVERNMENT
   (gov_employees before permits/violations/public_contracts/service_requests
    that reference issued_by/officer_id/awarded_by/assigned_to;
    citizens before permits/violations/service_requests)
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM dbo.gov_employees)
BEGIN
    INSERT INTO dbo.gov_employees (employee_no, first_name, last_name, department, job_title, hire_date, salary, pay_grade, supervisor_id, active) VALUES
    ('EMP-0001','Diane','Foster','City Manager''s Office','City Manager','2010-01-15',145000.00,'GS-15',NULL,1);
    INSERT INTO dbo.gov_employees (employee_no, first_name, last_name, department, job_title, hire_date, salary, pay_grade, supervisor_id, active) VALUES
    ('EMP-0002','Harold','Jennings','Public Works','Public Works Director','2012-03-10',118000.00,'GS-14',1,1),
    ('EMP-0003','Patricia','Nguyen','Finance Department','Finance Director','2011-06-01',120000.00,'GS-14',1,1),
    ('EMP-0004','Steven','Ortiz','Police Department','Chief of Police','2009-08-20',135000.00,'GS-15',1,1),
    ('EMP-0005','Michelle','Adams','Parks & Recreation','Parks Director','2014-02-17',98000.00,'GS-13',1,1),
    ('EMP-0006','Kevin','Bailey','Public Works','Roads Supervisor','2015-05-11',78000.00,'GS-11',2,1),
    ('EMP-0007','Laura','Simmons','Finance Department','Budget Analyst','2016-09-01',68000.00,'GS-10',3,1),
    ('EMP-0008','Brian','Foster','Finance Department','Accountant','2018-01-22',62000.00,'GS-09',3,1),
    ('EMP-0009','Angela','Ramos','Police Department','Police Captain','2013-11-05',95000.00,'GS-12',4,1),
    ('EMP-0010','Gary','Cole','Police Department','Patrol Officer','2019-04-14',58000.00,'GS-08',9,1),
    ('EMP-0011','Denise','Hayes','Parks & Recreation','Recreation Coordinator','2017-07-19',52000.00,'GS-08',5,1),
    ('EMP-0012','Thomas','Reed','Public Works','Building Inspector','2016-03-28',65000.00,'GS-09',2,1),
    ('EMP-0013','Rachel','Kim','Permitting Office','Permit Technician','2020-06-15',48000.00,'GS-07',12,1),
    ('EMP-0014','Walter','Munoz','Police Department','Patrol Officer','2021-02-08',56000.00,'GS-08',9,1),
    ('EMP-0015','Sandra','Price','City Clerk''s Office','City Clerk','2012-10-01',72000.00,'GS-10',1,0);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.citizens)
BEGIN
    INSERT INTO dbo.citizens (first_name, last_name, date_of_birth, id_number, email, phone, address, city, state, zip_code, registered_date) VALUES
    ('Ethan','Brooks','1980-04-12','ID-500001','ethan.brooks@example.com','404-555-0501','12 Peachtree St','Atlanta','GA','30303','2015-01-10'),
    ('Isabella','Cruz','1992-09-03','ID-500002','isabella.cruz@example.com','404-555-0502','45 Marietta St','Atlanta','GA','30303','2016-05-22'),
    ('Mason','Reed','1975-12-25','ID-500003','mason.reed@example.com','404-555-0503','88 Peachtree Center Ave','Atlanta','GA','30303','2012-03-15'),
    ('Ava','Bennett','1988-06-18','ID-500004','ava.bennett@example.com','404-555-0504','200 Auburn Ave','Atlanta','GA','30303','2018-09-01'),
    ('Logan','Price','1996-02-27','ID-500005','logan.price@example.com','404-555-0505','15 Ted Turner Dr','Atlanta','GA','30303','2020-11-11'),
    ('Zoey','Coleman','1983-08-09','ID-500006','zoey.coleman@example.com','404-555-0506','77 Forsyth St','Atlanta','GA','30303','2014-07-04'),
    ('Lucas','Foster','1970-11-30','ID-500007','lucas.foster@example.com','404-555-0507','300 International Blvd','Atlanta','GA','30303','2011-02-14'),
    ('Aria','Morgan','1999-05-05','ID-500008','aria.morgan@example.com','404-555-0508','55 Alabama St','Atlanta','GA','30303','2021-08-30'),
    ('Jackson','Bell','1965-01-19','ID-500009','jackson.bell@example.com','404-555-0509','120 Ivan Allen Jr Blvd','Atlanta','GA','30313','2010-06-20'),
    ('Layla','Simmons','1990-10-14','ID-500010','layla.simmons@example.com','404-555-0510','400 W Peachtree St','Atlanta','GA','30308','2017-04-05'),
    ('Ryan','Ward','1985-03-22','ID-500011','ryan.ward@example.com','404-555-0511','88 Luckie St','Atlanta','GA','30303','2013-12-01'),
    ('Nora','Perry','1993-07-11','ID-500012','nora.perry@example.com','404-555-0512','250 Williams St','Atlanta','GA','30303','2019-03-19'),
    ('Caleb','Long','1978-04-02','ID-500013','caleb.long@example.com','404-555-0513','15 Andrew Young Intl Blvd','Atlanta','GA','30303','2012-08-08'),
    ('Hannah','Ross','1991-11-16','ID-500014','hannah.ross@example.com','404-555-0514','60 Baker St','Atlanta','GA','30308','2016-10-27'),
    ('Isaac','Sanders','1987-09-28','ID-500015','isaac.sanders@example.com','404-555-0515','333 Piedmont Ave','Atlanta','GA','30308','2015-05-13'),
    ('Lily','Patterson','1994-12-05','ID-500016','lily.patterson@example.com','404-555-0516','800 Glenwood Ave','Atlanta','GA','30316','2018-01-25'),
    ('Owen','Jenkins','1982-06-30','ID-500017','owen.jenkins@example.com','404-555-0517','1200 Caroline St','Atlanta','GA','30307','2014-09-09'),
    ('Scarlett','Powell','1998-02-14','ID-500018','scarlett.powell@example.com','404-555-0518','450 Dekalb Ave','Atlanta','GA','30312','2022-02-02'),
    ('Wyatt','Barnes','1973-10-08','ID-500019','wyatt.barnes@example.com','404-555-0519','900 Metropolitan Pkwy','Atlanta','GA','30310','2011-11-11'),
    ('Victoria','Flores','1989-05-21','ID-500020','victoria.flores@example.com','404-555-0520','1500 Howell Mill Rd','Atlanta','GA','30318','2017-06-06');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.permits)
BEGIN
    INSERT INTO dbo.permits (permit_number, citizen_id, permit_type, description, issue_date, expiry_date, issued_by, fee_amount, status) VALUES
    ('PMT-2026-0001',1,'Building Permit','Residential deck addition','2026-01-05','2027-01-05',13,250.00,'Approved'),
    ('PMT-2026-0002',2,'Business License','New retail store license','2026-01-06','2027-01-06',13,150.00,'Approved'),
    ('PMT-2026-0003',3,'Building Permit','Kitchen remodel','2026-01-07',NULL,13,180.00,'In Review'),
    ('PMT-2026-0004',4,'Parking Permit','Residential parking permit','2026-01-08','2027-01-08',13,25.00,'Approved'),
    ('PMT-2026-0005',5,'Event Permit','Street fair - Ted Turner Dr','2026-02-01','2026-02-02',13,100.00,'Pending'),
    ('PMT-2026-0006',6,'Building Permit','Fence installation','2026-01-09','2027-01-09',13,75.00,'Approved'),
    ('PMT-2026-0007',7,'Business License','Restaurant license renewal','2026-01-10','2027-01-10',13,200.00,'Approved'),
    ('PMT-2026-0008',8,'Building Permit','Garage conversion','2026-01-11',NULL,13,300.00,'Denied'),
    ('PMT-2026-0009',9,'Parking Permit','Commercial loading zone permit','2026-01-12','2027-01-12',13,50.00,'Approved'),
    ('PMT-2026-0010',10,'Event Permit','Block party','2026-02-15','2026-02-15',13,60.00,'Pending'),
    ('PMT-2026-0011',11,'Building Permit','Roof replacement','2026-01-13','2027-01-13',13,220.00,'Approved'),
    ('PMT-2026-0012',12,'Business License','Food truck license','2026-01-14','2027-01-14',13,175.00,'Approved'),
    ('PMT-2026-0013',13,'Building Permit','Pool installation','2026-01-15',NULL,13,400.00,'In Review'),
    ('PMT-2026-0014',14,'Parking Permit','Disability parking permit','2026-01-16','2028-01-16',13,0.00,'Approved'),
    ('PMT-2026-0015',15,'Building Permit','Solar panel installation','2026-01-17','2027-01-17',13,190.00,'Approved'),
    ('PMT-2026-0016',16,'Event Permit','Wedding in city park','2026-03-01','2026-03-01',13,120.00,'Pending'),
    ('PMT-2026-0017',17,'Business License','Home-based business license','2026-01-18','2027-01-18',13,90.00,'Approved'),
    ('PMT-2026-0018',18,'Building Permit','Deck removal','2020-05-01','2021-05-01',13,150.00,'Expired'),
    ('PMT-2026-0019',19,'Business License','Retail license - revoked for code violations','2019-01-01','2020-01-01',13,150.00,'Revoked'),
    ('PMT-2026-0020',20,'Building Permit','Home addition','2026-01-19',NULL,13,350.00,'In Review');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.violations)
BEGIN
    INSERT INTO dbo.violations (citizen_id, violation_code, description, violation_date, location, fine_amount, paid_amount, officer_id, status) VALUES
    (1,'PKG-101','Expired parking meter','2026-01-05','12 Peachtree St',35.00,35.00,10,'Paid'),
    (3,'TRF-205','Speeding 10-15 mph over limit','2026-01-06','I-75 Northbound',150.00,0.00,14,'Outstanding'),
    (5,'NOI-301','Noise ordinance violation','2026-01-07','15 Ted Turner Dr',75.00,0.00,10,'Outstanding'),
    (7,'PKG-102','Parking in a no-parking zone','2026-01-08','300 International Blvd',60.00,60.00,14,'Paid'),
    (9,'TRF-210','Running a red light','2026-01-09','120 Ivan Allen Jr Blvd',200.00,0.00,10,'Warrant Issued'),
    (11,'CDE-401','Building code violation - unpermitted addition','2026-01-10','88 Luckie St',500.00,0.00,14,'Appealed'),
    (13,'PKG-101','Expired parking meter','2026-01-11','15 Andrew Young Intl Blvd',35.00,0.00,10,'Outstanding'),
    (15,'TRF-205','Speeding 10-15 mph over limit','2026-01-12','I-20 Eastbound',150.00,150.00,14,'Paid'),
    (17,'NOI-301','Noise ordinance violation','2026-01-13','1200 Caroline St',75.00,0.00,10,'Dismissed'),
    (19,'CDE-402','Overgrown lot / property maintenance','2026-01-14','900 Metropolitan Pkwy',100.00,0.00,14,'Outstanding'),
    (2,'PKG-102','Parking in a no-parking zone','2026-01-15','45 Marietta St',60.00,60.00,10,'Paid'),
    (4,'TRF-210','Running a red light','2026-01-16','200 Auburn Ave',200.00,0.00,14,'Outstanding'),
    (6,'NOI-301','Noise ordinance violation','2026-01-17','77 Forsyth St',75.00,75.00,10,'Paid'),
    (8,'CDE-401','Building code violation - unpermitted addition','2026-01-18','55 Alabama St',500.00,0.00,14,'Outstanding'),
    (10,'PKG-101','Expired parking meter','2026-01-19','400 W Peachtree St',35.00,0.00,10,'Dismissed');
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.public_contracts)
BEGIN
    INSERT INTO dbo.public_contracts (contract_number, vendor_name, department, description, start_date, end_date, contract_value, paid_to_date, status, awarded_by) VALUES
    ('CTR-2026-001','Atlas Road Construction Inc.','Public Works','Resurfacing of Peachtree St corridor','2026-01-01','2026-12-31',2500000.00,450000.00,'Active',2),
    ('CTR-2026-002','GreenScape Landscaping Co.','Parks & Recreation','Park maintenance services citywide','2026-01-01','2026-12-31',480000.00,120000.00,'Active',5),
    ('CTR-2026-003','SecureTech Systems LLC','Police Department','Body camera equipment and support','2025-07-01','2027-06-30',900000.00,300000.00,'Active',4),
    ('CTR-2026-004','Metro Waste Solutions','Public Works','Municipal waste collection services','2024-01-01','2026-12-31',3200000.00,2450000.00,'Active',2),
    ('CTR-2026-005','BrightLine Engineering','Public Works','Bridge inspection and repair','2025-03-01','2025-12-31',650000.00,650000.00,'Completed',2),
    ('CTR-2026-006','CivicSoft Technologies','Finance Department','ERP system implementation','2026-02-01','2027-08-01',1200000.00,0.00,'Pending Approval',3),
    ('CTR-2026-007','SafeGuard Security Services','Police Department','Courthouse security staffing','2025-01-01','2026-12-31',540000.00,405000.00,'Active',4),
    ('CTR-2026-008','Riverside Landscape Group','Parks & Recreation','Tree planting initiative','2025-09-01','2026-03-01',95000.00,60000.00,'Active',5),
    ('CTR-2026-009','ClearWater Utilities Consulting','Public Works','Water infrastructure assessment','2023-05-01','2024-05-01',310000.00,310000.00,'Completed',2),
    ('CTR-2026-010','Apex Fleet Services','Public Works','Municipal vehicle fleet maintenance','2026-01-15','2026-01-20',15000.00,0.00,'Cancelled',2);
END
GO

IF NOT EXISTS (SELECT 1 FROM dbo.service_requests)
BEGIN
    INSERT INTO dbo.service_requests (citizen_id, request_type, description, submitted_at, resolved_at, assigned_to, priority, status, resolution_notes) VALUES
    (1,'Pothole Repair','Large pothole on Peachtree St near 12th','2026-01-05T09:00:00','2026-01-08T14:00:00',6,'High','Resolved','Pothole patched by road crew'),
    (2,'Streetlight Outage','Streetlight not working at Marietta/Forsyth','2026-01-06T10:00:00',NULL,6,'Normal','Open',NULL),
    (3,'Noise Complaint','Ongoing construction noise before permitted hours','2026-01-07T08:00:00','2026-01-07T16:00:00',9,'Normal','Resolved','Contractor warned, hours adjusted'),
    (4,'Trash Pickup Missed','Missed collection on Auburn Ave','2026-01-08T09:00:00','2026-01-09T11:00:00',6,'Low','Resolved','Rescheduled for next-day pickup'),
    (5,'Tree Trimming Request','Overgrown tree blocking sidewalk','2026-01-09T09:00:00',NULL,11,'Low','Pending',NULL),
    (6,'Stray Animal Report','Stray dog reported near Forsyth St','2026-01-10T09:00:00','2026-01-10T15:00:00',10,'High','Resolved','Animal control dispatched and dog collected'),
    (7,'Water Main Break','Water main break flooding street','2026-01-11T06:00:00','2026-01-11T18:00:00',6,'High','Resolved','Emergency repair crew dispatched'),
    (8,'Graffiti Removal','Graffiti on public building wall','2026-01-12T09:00:00',NULL,11,'Low','In Progress',NULL),
    (9,'Sidewalk Repair','Cracked sidewalk creating trip hazard','2026-01-13T09:00:00',NULL,6,'Normal','Open',NULL),
    (10,'Illegal Dumping Report','Illegal dumping in vacant lot','2026-01-14T09:00:00',NULL,14,'Normal','In Progress',NULL),
    (11,'Parking Complaint','Vehicle blocking driveway repeatedly','2026-01-15T09:00:00','2026-01-16T10:00:00',10,'Normal','Resolved','Officer issued citation'),
    (12,'Building Inspection Request','Request inspection for new deck','2026-01-16T09:00:00',NULL,12,'Normal','Open',NULL),
    (13,'Park Maintenance','Broken playground equipment','2026-01-17T09:00:00',NULL,11,'High','In Progress',NULL),
    (14,'Noise Complaint','Loud parties on weekends','2026-01-18T09:00:00','2026-01-19T09:00:00',9,'Normal','Resolved','Warning issued to residents'),
    (15,'Streetlight Outage','Multiple streetlights out on Piedmont Ave','2026-01-19T09:00:00',NULL,6,'Normal','Open',NULL),
    (16,'Pothole Repair','Pothole causing tire damage','2026-01-20T09:00:00',NULL,6,'High','Pending',NULL),
    (17,'Trash Pickup Missed','Missed recycling pickup','2026-01-21T09:00:00','2026-01-22T09:00:00',6,'Low','Closed','Confirmed picked up on retry'),
    (18,'Animal Control','Aggressive dog reported off-leash','2026-01-22T09:00:00',NULL,10,'High','In Progress',NULL),
    (19,'Code Enforcement','Unpermitted commercial signage','2026-01-23T09:00:00',NULL,12,'Normal','Open',NULL),
    (20,'General Inquiry','Question about business license renewal','2026-01-24T09:00:00','2026-01-24T11:00:00',13,'Low','Closed','Provided renewal instructions via email');
END
GO

PRINT 'Government sample data loaded';
GO
/* ============================================================================
   4. ENRICHMENT LAYER
   Folded in from EB_SampleDB_enrich_sqlserver.sql: foreign keys, check
   constraints, user-defined table types, functions, and stored procedures —
   plus NEW views and triggers added specifically for this sample script.
   Every CREATE below is idempotent (guarded or CREATE OR ALTER), so this
   script is safe to re-run against an already-built EB_SampleDB.
   ============================================================================ */

PRINT '--- EB_SampleDB enrichment starting ---';
GO

/* ----------------------------------------------------------------------
   4.1 FOREIGN KEYS
   ---------------------------------------------------------------------- */

-- Healthcare
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_appointments_patients')
    ALTER TABLE dbo.appointments      WITH CHECK ADD CONSTRAINT FK_appointments_patients      FOREIGN KEY (patient_id)  REFERENCES dbo.patients(patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_appointments_providers')
    ALTER TABLE dbo.appointments      WITH CHECK ADD CONSTRAINT FK_appointments_providers     FOREIGN KEY (provider_id) REFERENCES dbo.providers(provider_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_diagnoses_patients')
    ALTER TABLE dbo.diagnoses         WITH CHECK ADD CONSTRAINT FK_diagnoses_patients         FOREIGN KEY (patient_id)  REFERENCES dbo.patients(patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_diagnoses_providers')
    ALTER TABLE dbo.diagnoses         WITH CHECK ADD CONSTRAINT FK_diagnoses_providers        FOREIGN KEY (provider_id) REFERENCES dbo.providers(provider_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_lab_results_patients')
    ALTER TABLE dbo.lab_results       WITH CHECK ADD CONSTRAINT FK_lab_results_patients       FOREIGN KEY (patient_id)  REFERENCES dbo.patients(patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_lab_results_providers')
    ALTER TABLE dbo.lab_results       WITH CHECK ADD CONSTRAINT FK_lab_results_providers      FOREIGN KEY (provider_id) REFERENCES dbo.providers(provider_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_prescriptions_patients')
    ALTER TABLE dbo.prescriptions     WITH CHECK ADD CONSTRAINT FK_prescriptions_patients     FOREIGN KEY (patient_id)  REFERENCES dbo.patients(patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_prescriptions_providers')
    ALTER TABLE dbo.prescriptions     WITH CHECK ADD CONSTRAINT FK_prescriptions_providers    FOREIGN KEY (provider_id) REFERENCES dbo.providers(provider_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_claims_patients')
    ALTER TABLE dbo.insurance_claims  WITH CHECK ADD CONSTRAINT FK_claims_patients            FOREIGN KEY (patient_id)  REFERENCES dbo.patients(patient_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_claims_providers')
    ALTER TABLE dbo.insurance_claims  WITH CHECK ADD CONSTRAINT FK_claims_providers           FOREIGN KEY (provider_id) REFERENCES dbo.providers(provider_id);
GO
PRINT 'FKs: Healthcare done';
GO

-- E-commerce (categories.parent_id is self-referential — always points to an earlier-inserted row)
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_categories_parent')
    ALTER TABLE dbo.categories        WITH CHECK ADD CONSTRAINT FK_categories_parent          FOREIGN KEY (parent_id)   REFERENCES dbo.categories(category_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_products_categories')
    ALTER TABLE dbo.products          WITH CHECK ADD CONSTRAINT FK_products_categories        FOREIGN KEY (category_id) REFERENCES dbo.categories(category_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_orders_customers')
    ALTER TABLE dbo.orders            WITH CHECK ADD CONSTRAINT FK_orders_customers           FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_order_items_orders')
    ALTER TABLE dbo.order_items       WITH CHECK ADD CONSTRAINT FK_order_items_orders         FOREIGN KEY (order_id)    REFERENCES dbo.orders(order_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_order_items_products')
    ALTER TABLE dbo.order_items       WITH CHECK ADD CONSTRAINT FK_order_items_products       FOREIGN KEY (product_id)  REFERENCES dbo.products(product_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_reviews_products')
    ALTER TABLE dbo.reviews           WITH CHECK ADD CONSTRAINT FK_reviews_products           FOREIGN KEY (product_id)  REFERENCES dbo.products(product_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_reviews_customers')
    ALTER TABLE dbo.reviews           WITH CHECK ADD CONSTRAINT FK_reviews_customers          FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_cart_events_customers')
    ALTER TABLE dbo.cart_events       WITH CHECK ADD CONSTRAINT FK_cart_events_customers      FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_cart_events_products')
    ALTER TABLE dbo.cart_events       WITH CHECK ADD CONSTRAINT FK_cart_events_products       FOREIGN KEY (product_id)  REFERENCES dbo.products(product_id);
GO
PRINT 'FKs: E-commerce done';
GO

-- Finance
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_transactions_accounts')
    ALTER TABLE dbo.transactions      WITH CHECK ADD CONSTRAINT FK_transactions_accounts      FOREIGN KEY (account_id)  REFERENCES dbo.fin_accounts(account_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_loans_accounts')
    ALTER TABLE dbo.loans             WITH CHECK ADD CONSTRAINT FK_loans_accounts             FOREIGN KEY (account_id)  REFERENCES dbo.fin_accounts(account_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_fraud_alerts_accounts')
    ALTER TABLE dbo.fraud_alerts      WITH CHECK ADD CONSTRAINT FK_fraud_alerts_accounts      FOREIGN KEY (account_id)  REFERENCES dbo.fin_accounts(account_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_fraud_alerts_transactions')
    ALTER TABLE dbo.fraud_alerts      WITH CHECK ADD CONSTRAINT FK_fraud_alerts_transactions  FOREIGN KEY (txn_id)      REFERENCES dbo.transactions(txn_id);
GO
PRINT 'FKs: Finance done';
GO

-- Manufacturing / IoT
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_sensors_machines')
    ALTER TABLE dbo.sensors           WITH CHECK ADD CONSTRAINT FK_sensors_machines          FOREIGN KEY (machine_id)  REFERENCES dbo.machines(machine_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_sensor_readings_sensors')
    ALTER TABLE dbo.sensor_readings   WITH CHECK ADD CONSTRAINT FK_sensor_readings_sensors    FOREIGN KEY (sensor_id)   REFERENCES dbo.sensors(sensor_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_sensor_readings_machines')
    ALTER TABLE dbo.sensor_readings   WITH CHECK ADD CONSTRAINT FK_sensor_readings_machines   FOREIGN KEY (machine_id)  REFERENCES dbo.machines(machine_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_batches_machines')
    ALTER TABLE dbo.production_batches WITH CHECK ADD CONSTRAINT FK_batches_machines          FOREIGN KEY (machine_id)  REFERENCES dbo.machines(machine_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_quality_checks_batches')
    ALTER TABLE dbo.quality_checks    WITH CHECK ADD CONSTRAINT FK_quality_checks_batches     FOREIGN KEY (batch_id)    REFERENCES dbo.production_batches(batch_id);
GO
PRINT 'FKs: Manufacturing/IoT done';
GO

-- Retail (product_id/customer_id cross-reference the E-commerce tables — same synthetic company, shared catalog/customer base)
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_inventory_stores')
    ALTER TABLE dbo.inventory         WITH CHECK ADD CONSTRAINT FK_inventory_stores          FOREIGN KEY (store_id)    REFERENCES dbo.stores(store_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_inventory_products')
    ALTER TABLE dbo.inventory         WITH CHECK ADD CONSTRAINT FK_inventory_products        FOREIGN KEY (product_id)  REFERENCES dbo.products(product_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_pos_transactions_stores')
    ALTER TABLE dbo.pos_transactions  WITH CHECK ADD CONSTRAINT FK_pos_transactions_stores    FOREIGN KEY (store_id)    REFERENCES dbo.stores(store_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_pos_transactions_customers')
    ALTER TABLE dbo.pos_transactions  WITH CHECK ADD CONSTRAINT FK_pos_transactions_customers FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_pos_items_pos_transactions')
    ALTER TABLE dbo.pos_items         WITH CHECK ADD CONSTRAINT FK_pos_items_pos_transactions FOREIGN KEY (pos_txn_id)  REFERENCES dbo.pos_transactions(pos_txn_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_pos_items_products')
    ALTER TABLE dbo.pos_items         WITH CHECK ADD CONSTRAINT FK_pos_items_products         FOREIGN KEY (product_id)  REFERENCES dbo.products(product_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_loyalty_members_customers')
    ALTER TABLE dbo.loyalty_members   WITH CHECK ADD CONSTRAINT FK_loyalty_members_customers  FOREIGN KEY (customer_id) REFERENCES dbo.customers(customer_id);
GO
PRINT 'FKs: Retail done';
GO

-- Government (gov_employees.supervisor_id is self-referential — always points to an earlier-inserted row)
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_gov_employees_supervisor')
    ALTER TABLE dbo.gov_employees     WITH CHECK ADD CONSTRAINT FK_gov_employees_supervisor   FOREIGN KEY (supervisor_id) REFERENCES dbo.gov_employees(employee_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_permits_citizens')
    ALTER TABLE dbo.permits           WITH CHECK ADD CONSTRAINT FK_permits_citizens           FOREIGN KEY (citizen_id)  REFERENCES dbo.citizens(citizen_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_violations_citizens')
    ALTER TABLE dbo.violations        WITH CHECK ADD CONSTRAINT FK_violations_citizens        FOREIGN KEY (citizen_id) REFERENCES dbo.citizens(citizen_id);
IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_service_requests_citizens')
    ALTER TABLE dbo.service_requests  WITH CHECK ADD CONSTRAINT FK_service_requests_citizens  FOREIGN KEY (citizen_id) REFERENCES dbo.citizens(citizen_id);
GO
PRINT 'FKs: Government done';
GO

/* ----------------------------------------------------------------------
   4.2 CHECK CONSTRAINTS
   (matches generate_sqlserver.py's DDL_CHECK_CONSTRAINTS; all sample
   data above was written to already satisfy every one of these)
   ---------------------------------------------------------------------- */

IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_patients_gender')
    ALTER TABLE dbo.patients ADD CONSTRAINT CK_patients_gender CHECK (gender IN ('M','F','O'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_appointments_status')
    ALTER TABLE dbo.appointments ADD CONSTRAINT CK_appointments_status CHECK (status IN ('Scheduled','Completed','Cancelled','No-Show','Rescheduled'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_diagnoses_severity')
    ALTER TABLE dbo.diagnoses ADD CONSTRAINT CK_diagnoses_severity CHECK (severity IS NULL OR severity IN ('Mild','Moderate','Severe','Critical'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_prescriptions_status')
    ALTER TABLE dbo.prescriptions ADD CONSTRAINT CK_prescriptions_status CHECK (status IN ('Active','Inactive','Expired','Discontinued'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_insurance_claims_status')
    ALTER TABLE dbo.insurance_claims ADD CONSTRAINT CK_insurance_claims_status CHECK (status IN ('Pending','Approved','Denied','Paid','In Review','Appealed'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_products_price')
    ALTER TABLE dbo.products ADD CONSTRAINT CK_products_price CHECK (unit_price >= 0);
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_orders_status')
    ALTER TABLE dbo.orders ADD CONSTRAINT CK_orders_status CHECK (status IN ('Pending','Processing','Shipped','Delivered','Cancelled','Returned','Refunded'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_orders_total_nonneg')
    ALTER TABLE dbo.orders ADD CONSTRAINT CK_orders_total_nonneg CHECK (total_amount >= 0 AND subtotal >= 0);
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_reviews_rating')
    ALTER TABLE dbo.reviews ADD CONSTRAINT CK_reviews_rating CHECK (rating BETWEEN 1 AND 5);
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_loans_status')
    ALTER TABLE dbo.loans ADD CONSTRAINT CK_loans_status CHECK (status IN ('Current','Delinquent','Default','Paid Off','Charged Off'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_loans_interest_rate')
    ALTER TABLE dbo.loans ADD CONSTRAINT CK_loans_interest_rate CHECK (interest_rate >= 0);
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_fraud_alerts_status')
    ALTER TABLE dbo.fraud_alerts ADD CONSTRAINT CK_fraud_alerts_status CHECK (status IN ('Open','Investigating','Closed - Fraud','Closed - Not Fraud'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_quality_checks_result')
    ALTER TABLE dbo.quality_checks ADD CONSTRAINT CK_quality_checks_result CHECK (result IN ('Pass','Fail','Conditional Pass','Rework Required'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_pos_transactions_total_nonneg')
    ALTER TABLE dbo.pos_transactions ADD CONSTRAINT CK_pos_transactions_total_nonneg CHECK (total_amount >= 0);
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_permits_status')
    ALTER TABLE dbo.permits ADD CONSTRAINT CK_permits_status CHECK (status IN ('Pending','Approved','Denied','Expired','Revoked','In Review'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_violations_status')
    ALTER TABLE dbo.violations ADD CONSTRAINT CK_violations_status CHECK (status IN ('Outstanding','Paid','Dismissed','Appealed','Warrant Issued'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_public_contracts_status')
    ALTER TABLE dbo.public_contracts ADD CONSTRAINT CK_public_contracts_status CHECK (status IN ('Active','Completed','Cancelled','Pending Approval','Suspended'));
IF NOT EXISTS (SELECT 1 FROM sys.check_constraints WHERE name = 'CK_service_requests_status')
    ALTER TABLE dbo.service_requests ADD CONSTRAINT CK_service_requests_status CHECK (status IN ('Open','In Progress','Pending','Resolved','Closed','Cancelled'));
GO
PRINT 'Check constraints created';
GO

/* ----------------------------------------------------------------------
   4.3 USER-DEFINED TABLE TYPES
   (SQL-Server-specific TVP types — no direct equivalent in
   PostgreSQL/MySQL/Oracle, useful as a schema-conversion test case)
   ---------------------------------------------------------------------- */

IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = 'PatientIdList' AND is_user_defined = 1)
    EXEC('CREATE TYPE dbo.PatientIdList AS TABLE (patient_id INT NOT NULL PRIMARY KEY)');
GO

IF NOT EXISTS (SELECT 1 FROM sys.types WHERE name = 'OrderItemInsert' AND is_user_defined = 1)
    EXEC('CREATE TYPE dbo.OrderItemInsert AS TABLE (
        product_id  INT            NOT NULL,
        quantity    INT            NOT NULL,
        unit_price  DECIMAL(12,2)  NOT NULL
    )');
GO

PRINT 'Table types created';
GO

/* ----------------------------------------------------------------------
   4.4 FUNCTIONS
   ---------------------------------------------------------------------- */

CREATE OR ALTER FUNCTION dbo.ufn_PatientAge (@date_of_birth DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @date_of_birth, GETDATE())
         - CASE WHEN (MONTH(@date_of_birth) > MONTH(GETDATE()))
                  OR (MONTH(@date_of_birth) = MONTH(GETDATE()) AND DAY(@date_of_birth) > DAY(GETDATE()))
                THEN 1 ELSE 0 END;
END;
GO

CREATE OR ALTER FUNCTION dbo.ufn_OrderItemCount (@order_id INT)
RETURNS INT
AS
BEGIN
    DECLARE @cnt INT;
    SELECT @cnt = COUNT(*) FROM dbo.order_items WHERE order_id = @order_id;
    RETURN ISNULL(@cnt, 0);
END;
GO

CREATE OR ALTER FUNCTION dbo.ufn_AccountBalance (@account_id INT)
RETURNS DECIMAL(18,2)
AS
BEGIN
    DECLARE @bal DECIMAL(18,2);
    SELECT @bal = balance FROM dbo.fin_accounts WHERE account_id = @account_id;
    RETURN @bal;
END;
GO

CREATE OR ALTER FUNCTION dbo.ufn_LoyaltyTierDiscount (@tier NVARCHAR(50))
RETURNS DECIMAL(5,2)
AS
BEGIN
    RETURN CASE @tier
        WHEN 'Bronze'   THEN 0.00
        WHEN 'Silver'   THEN 2.50
        WHEN 'Gold'     THEN 5.00
        WHEN 'Platinum' THEN 7.50
        WHEN 'Diamond'  THEN 10.00
        ELSE 0.00
    END;
END;
GO

CREATE OR ALTER FUNCTION dbo.ufn_PermitIsExpired (@expiry_date DATE)
RETURNS BIT
AS
BEGIN
    RETURN CASE WHEN @expiry_date IS NOT NULL AND @expiry_date < CAST(GETDATE() AS DATE)
                THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END;
END;
GO

-- Inline table-valued function — notoriously tricky to migrate cross-engine
CREATE OR ALTER FUNCTION dbo.ufn_GetTopProducts (@top_n INT)
RETURNS TABLE
AS
RETURN (
    SELECT TOP (@top_n)
        p.product_id, p.name, p.unit_price,
        COUNT(oi.order_item_id) AS times_ordered,
        SUM(oi.quantity)        AS total_qty_sold
    FROM dbo.products p
    JOIN dbo.order_items oi ON oi.product_id = p.product_id
    GROUP BY p.product_id, p.name, p.unit_price
);
GO

PRINT 'Functions created';
GO

/* ----------------------------------------------------------------------
   4.5 STORED PROCEDURES
   ---------------------------------------------------------------------- */

CREATE OR ALTER PROCEDURE dbo.usp_GetPatientHistory
    @patient_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT appointment_id, appt_date, status, reason FROM dbo.appointments WHERE patient_id = @patient_id ORDER BY appt_date DESC;
    SELECT diagnosis_id, icd10_code, description, diagnosed_date FROM dbo.diagnoses WHERE patient_id = @patient_id ORDER BY diagnosed_date DESC;
    SELECT rx_id, drug_name, dosage, prescribed_date, status FROM dbo.prescriptions WHERE patient_id = @patient_id ORDER BY prescribed_date DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_AddInsuranceClaim
    @patient_id INT, @provider_id INT, @claim_date DATE, @service_date DATE,
    @diagnosis_code VARCHAR(10), @billed_amount DECIMAL(12,2), @payer_name NVARCHAR(200)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.insurance_claims (patient_id, provider_id, claim_date, service_date, diagnosis_code, billed_amount, payer_name)
    VALUES (@patient_id, @provider_id, @claim_date, @service_date, @diagnosis_code, @billed_amount, @payer_name);
    SELECT SCOPE_IDENTITY() AS new_claim_id;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetCustomerOrderSummary
    @customer_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        c.customer_id, c.first_name, c.last_name,
        COUNT(o.order_id)          AS total_orders,
        ISNULL(SUM(o.total_amount), 0) AS lifetime_value,
        MAX(o.order_date)          AS last_order_date
    FROM dbo.customers c
    LEFT JOIN dbo.orders o ON o.customer_id = c.customer_id
    WHERE c.customer_id = @customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_ApplyPromotion
    @promo_code VARCHAR(30), @order_id INT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @discount_type NVARCHAR(50), @discount_value DECIMAL(10,4), @active BIT;
    SELECT @discount_type = discount_type, @discount_value = discount_value, @active = active
    FROM dbo.promotions WHERE promo_code = @promo_code;

    IF @active IS NULL OR @active = 0
    BEGIN
        RAISERROR('Promotion code not found or inactive.', 16, 1);
        RETURN;
    END

    UPDATE dbo.orders
    SET total_amount = CASE WHEN @discount_type = 'Percent'
                             THEN total_amount * (1 - @discount_value / 100.0)
                             ELSE total_amount - @discount_value END
    WHERE order_id = @order_id;

    UPDATE dbo.promotions SET times_used = times_used + 1 WHERE promo_code = @promo_code;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetAccountStatement
    @account_id INT, @start_date DATETIME2, @end_date DATETIME2
AS
BEGIN
    SET NOCOUNT ON;
    SELECT txn_id, txn_date, txn_type, amount, merchant_name, balance_after
    FROM dbo.transactions
    WHERE account_id = @account_id AND txn_date BETWEEN @start_date AND @end_date
    ORDER BY txn_date;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_FlagFraudAlert
    @txn_id BIGINT, @account_id INT, @alert_type NVARCHAR(100), @risk_score DECIMAL(5,2)
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.fraud_alerts (txn_id, account_id, alert_type, risk_score, detected_at, status)
    VALUES (@txn_id, @account_id, @alert_type, @risk_score, SYSUTCDATETIME(), 'Open');
    SELECT SCOPE_IDENTITY() AS new_alert_id;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetMachineHealthSummary
    @machine_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        m.machine_id, m.machine_name, m.status,
        COUNT(sr.reading_id)                              AS reading_count,
        SUM(CASE WHEN sr.anomaly = 1 THEN 1 ELSE 0 END)    AS anomaly_count,
        AVG(sr.value)                                      AS avg_reading_value
    FROM dbo.machines m
    LEFT JOIN dbo.sensor_readings sr ON sr.machine_id = m.machine_id
    WHERE m.machine_id = @machine_id
    GROUP BY m.machine_id, m.machine_name, m.status;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetStoreSalesSummary
    @store_id INT, @sales_date DATE
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        s.store_id, s.store_name,
        COUNT(pt.pos_txn_id)          AS transaction_count,
        ISNULL(SUM(pt.total_amount), 0) AS total_sales
    FROM dbo.stores s
    LEFT JOIN dbo.pos_transactions pt
        ON pt.store_id = s.store_id AND CAST(pt.txn_date AS DATE) = @sales_date
    WHERE s.store_id = @store_id
    GROUP BY s.store_id, s.store_name;
END;
GO

CREATE OR ALTER PROCEDURE dbo.usp_GetCitizenServiceHistory
    @citizen_id INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT permit_id, permit_type, issue_date, expiry_date, status FROM dbo.permits WHERE citizen_id = @citizen_id;
    SELECT violation_id, violation_code, violation_date, fine_amount, status FROM dbo.violations WHERE citizen_id = @citizen_id;
    SELECT request_id, request_type, submitted_at, status FROM dbo.service_requests WHERE citizen_id = @citizen_id;
END;
GO

-- Demonstrates a table-valued parameter (uses the PatientIdList type above)
CREATE OR ALTER PROCEDURE dbo.usp_BulkGetPatientAppointments
    @patient_ids dbo.PatientIdList READONLY
AS
BEGIN
    SET NOCOUNT ON;
    SELECT a.patient_id, a.appointment_id, a.appt_date, a.status
    FROM dbo.appointments a
    JOIN @patient_ids pid ON pid.patient_id = a.patient_id
    ORDER BY a.patient_id, a.appt_date DESC;
END;
GO

PRINT 'Stored procedures created';
GO
/* ----------------------------------------------------------------------
   4.6 VIEWS (new — did not exist in EB_SampleDB_enrich_sqlserver.sql)
   Each demonstrates a realistic cross-table reporting query for one
   industry, using real JOINs (not just correlated subqueries) so they
   double as EndriasBridge migration test cases for view definitions.
   ---------------------------------------------------------------------- */

-- Healthcare: patients + appointments + providers, one row per visit
CREATE OR ALTER VIEW dbo.vw_healthcare_patient_care_summary AS
SELECT
    p.patient_id,
    p.first_name + ' ' + p.last_name AS patient_name,
    p.date_of_birth,
    p.state          AS patient_state,
    a.appointment_id,
    a.appt_date,
    a.status          AS appointment_status,
    a.reason,
    pr.provider_id,
    pr.first_name + ' ' + pr.last_name AS provider_name,
    pr.specialty
FROM dbo.patients p
JOIN dbo.appointments a ON a.patient_id = p.patient_id
JOIN dbo.providers pr   ON pr.provider_id = a.provider_id;
GO

-- E-commerce: order totals with product and customer detail, one row per line item
CREATE OR ALTER VIEW dbo.vw_ecommerce_order_totals AS
SELECT
    o.order_id,
    o.order_date,
    o.status,
    c.customer_id,
    c.first_name + ' ' + c.last_name AS customer_name,
    c.email,
    oi.order_item_id,
    p.product_id,
    p.name  AS product_name,
    p.sku,
    oi.quantity,
    oi.unit_price,
    oi.line_total,
    o.total_amount AS order_total_amount
FROM dbo.orders o
JOIN dbo.customers c    ON c.customer_id = o.customer_id
JOIN dbo.order_items oi ON oi.order_id = o.order_id
JOIN dbo.products p     ON p.product_id = oi.product_id;
GO

-- Finance: account-level rollup of transaction activity and open fraud alerts
CREATE OR ALTER VIEW dbo.vw_finance_account_transaction_summary AS
SELECT
    fa.account_id,
    fa.account_number,
    fa.owner_name,
    fa.account_type,
    fa.balance,
    fa.status,
    COUNT(t.txn_id)                                        AS transaction_count,
    SUM(CASE WHEN t.amount > 0 THEN t.amount ELSE 0 END)    AS total_credits,
    SUM(CASE WHEN t.amount < 0 THEN -t.amount ELSE 0 END)   AS total_debits,
    (SELECT COUNT(*) FROM dbo.fraud_alerts fal
       WHERE fal.account_id = fa.account_id AND fal.status IN ('Open','Investigating')) AS open_fraud_alerts
FROM dbo.fin_accounts fa
LEFT JOIN dbo.transactions t ON t.account_id = fa.account_id
GROUP BY fa.account_id, fa.account_number, fa.owner_name, fa.account_type, fa.balance, fa.status;
GO

-- Manufacturing/IoT: machine-level rollup of production batches and QC results
CREATE OR ALTER VIEW dbo.vw_manufacturing_machine_oee AS
SELECT
    m.machine_id,
    m.machine_name,
    m.machine_type,
    m.status,
    COUNT(DISTINCT pb.batch_id)             AS batch_count,
    SUM(ISNULL(pb.actual_qty, 0))           AS total_actual_qty,
    SUM(ISNULL(pb.planned_qty, 0))          AS total_planned_qty,
    SUM(CASE WHEN qc.result = 'Fail' THEN 1 ELSE 0 END) AS failed_qc_count,
    COUNT(qc.qc_id)                         AS total_qc_count
FROM dbo.machines m
LEFT JOIN dbo.production_batches pb ON pb.machine_id = m.machine_id
LEFT JOIN dbo.quality_checks qc     ON qc.batch_id = pb.batch_id
GROUP BY m.machine_id, m.machine_name, m.machine_type, m.status;
GO

-- Retail: store-level sales performance plus a low-stock indicator
CREATE OR ALTER VIEW dbo.vw_retail_store_performance AS
SELECT
    s.store_id,
    s.store_name,
    s.city,
    s.state,
    COUNT(DISTINCT pt.pos_txn_id)   AS transaction_count,
    ISNULL(SUM(pt.total_amount), 0) AS total_sales,
    (SELECT COUNT(*) FROM dbo.inventory inv
       WHERE inv.store_id = s.store_id
         AND inv.reorder_point IS NOT NULL
         AND inv.quantity_on_hand < inv.reorder_point) AS low_stock_items
FROM dbo.stores s
LEFT JOIN dbo.pos_transactions pt ON pt.store_id = s.store_id
GROUP BY s.store_id, s.store_name, s.city, s.state;
GO

-- Government: citizen-level rollup of permits, violations, and service requests
CREATE OR ALTER VIEW dbo.vw_government_citizen_case_summary AS
SELECT
    c.citizen_id,
    c.first_name + ' ' + c.last_name AS citizen_name,
    c.city,
    c.state,
    (SELECT COUNT(*) FROM dbo.permits p WHERE p.citizen_id = c.citizen_id) AS permit_count,
    (SELECT COUNT(*) FROM dbo.violations v WHERE v.citizen_id = c.citizen_id AND v.status = 'Outstanding') AS outstanding_violations,
    (SELECT ISNULL(SUM(v.fine_amount), 0) FROM dbo.violations v WHERE v.citizen_id = c.citizen_id) AS total_fines,
    (SELECT COUNT(*) FROM dbo.service_requests sr WHERE sr.citizen_id = c.citizen_id AND sr.status IN ('Open','In Progress')) AS open_service_requests
FROM dbo.citizens c;
GO

PRINT 'Views created (6 new views across all 6 industries)';
GO

/* ----------------------------------------------------------------------
   4.7 TRIGGERS (new — did not exist in EB_SampleDB_enrich_sqlserver.sql)
   Set-based AFTER triggers (correct for multi-row INSERT/UPDATE batches,
   not just single-row assumptions) demonstrating audit logging and
   business-rule automation across four different industries.
   ---------------------------------------------------------------------- */

-- Healthcare: log every UPDATE to patients (PII/PHI) into audit_log
CREATE OR ALTER TRIGGER dbo.TR_patients_audit_update
ON dbo.patients
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.audit_log (table_name, operation, record_id, changed_by, changed_at, old_values, new_values)
    SELECT
        'patients',
        'UPDATE',
        i.patient_id,
        SUSER_SNAME(),
        SYSUTCDATETIME(),
        CONCAT('email=', d.email, '; phone=', d.phone, '; address=', d.address_line1),
        CONCAT('email=', i.email, '; phone=', i.phone, '; address=', i.address_line1)
    FROM inserted i
    JOIN deleted d ON d.patient_id = i.patient_id;
END;
GO

-- Finance: automatically flag newly inserted high-value transactions as open fraud alerts
CREATE OR ALTER TRIGGER dbo.TR_transactions_flag_high_value
ON dbo.transactions
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.fraud_alerts (txn_id, account_id, alert_type, risk_score, detected_at, status, notes)
    SELECT
        i.txn_id,
        i.account_id,
        'High-Value Transaction',
        75.00,
        SYSUTCDATETIME(),
        'Open',
        CONCAT('Auto-flagged: transaction amount ', i.amount, ' exceeds the $10,000 threshold')
    FROM inserted i
    WHERE ABS(i.amount) > 10000;
END;
GO

-- Retail: when a restock/update drops an item below its reorder point, log it for the ops team
CREATE OR ALTER TRIGGER dbo.TR_inventory_low_stock_alert
ON dbo.inventory
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.audit_log (table_name, operation, record_id, changed_by, changed_at, old_values, new_values)
    SELECT
        'inventory',
        'LOW_STOCK_ALERT',
        i.inventory_id,
        SUSER_SNAME(),
        SYSUTCDATETIME(),
        CONCAT('quantity_on_hand=', d.quantity_on_hand),
        CONCAT('quantity_on_hand=', i.quantity_on_hand, '; reorder_point=', i.reorder_point)
    FROM inserted i
    JOIN deleted d ON d.inventory_id = i.inventory_id
    WHERE i.reorder_point IS NOT NULL
      AND i.quantity_on_hand < i.reorder_point
      AND (d.reorder_point IS NULL OR d.quantity_on_hand >= d.reorder_point);
END;
GO

-- Government: when a violation's status is updated to 'Paid', auto-settle paid_amount to fine_amount
CREATE OR ALTER TRIGGER dbo.TR_violations_auto_paid
ON dbo.violations
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(status)
    BEGIN
        UPDATE v
        SET v.paid_amount = v.fine_amount
        FROM dbo.violations v
        JOIN inserted i ON i.violation_id = v.violation_id
        JOIN deleted d  ON d.violation_id = v.violation_id
        WHERE i.status = 'Paid'
          AND d.status <> 'Paid'
          AND v.fine_amount IS NOT NULL
          AND (v.paid_amount IS NULL OR v.paid_amount < v.fine_amount);
    END
END;
GO

PRINT 'Triggers created (4 new triggers across Healthcare, Finance, Retail, and Government)';
GO

/* ============================================================================
   5. SQL AGENT OBJECTS — 1 operator, 1 alert, 3 jobs (msdb, server-level)
   ============================================================================
   Included so EndriasBridge's SQL Agent migration feature (Migrate tab:
   "Also migrate SQL Agent jobs, operators & alerts") has something real to
   exercise. These are server-level msdb objects, not part of EB_SampleDB
   itself, so they show up once per SQL Server instance regardless of how
   many times this script is re-run against different databases on that
   instance. Requires SQL Server Agent to be running - if this section
   errors out, check the SQL Server Agent service status first.
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysoperators WHERE name = N'EB_DBA_Team')
BEGIN
    EXEC msdb.dbo.sp_add_operator
        @name = N'EB_DBA_Team',
        @enabled = 1,
        @email_address = N'dba-team@example.com';
END
GO

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysalerts WHERE name = N'EB_Low_Disk_Space')
BEGIN
    EXEC msdb.dbo.sp_add_alert
        @name = N'EB_Low_Disk_Space',
        @message_id = 0,
        @severity = 19,
        @enabled = 1;
    EXEC msdb.dbo.sp_add_notification
        @alert_name = N'EB_Low_Disk_Space',
        @operator_name = N'EB_DBA_Team',
        @notification_method = 1;
END
GO

-- Job 1: nightly purge of old audit_log rows
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'EB_Nightly_AuditLog_Purge')
BEGIN
    EXEC msdb.dbo.sp_add_job
        @job_name = N'EB_Nightly_AuditLog_Purge',
        @enabled = 1,
        @description = N'Deletes EB_SampleDB.dbo.audit_log rows older than 90 days.';

    EXEC msdb.dbo.sp_add_jobstep
        @job_name = N'EB_Nightly_AuditLog_Purge',
        @step_id = 1,
        @step_name = N'Purge old audit rows',
        @subsystem = N'TSQL',
        @command = N'DELETE FROM dbo.audit_log WHERE changed_at < DATEADD(DAY, -90, SYSUTCDATETIME());',
        @database_name = N'EB_SampleDB';

    EXEC msdb.dbo.sp_add_schedule
        @schedule_name = N'EB_Nightly_2AM',
        @freq_type = 4,           -- daily
        @freq_interval = 1,
        @active_start_time = 020000;

    EXEC msdb.dbo.sp_attach_schedule
        @job_name = N'EB_Nightly_AuditLog_Purge',
        @schedule_name = N'EB_Nightly_2AM';

    EXEC msdb.dbo.sp_add_jobserver
        @job_name = N'EB_Nightly_AuditLog_Purge';
END
GO

-- Job 2: daily low-stock report
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'EB_Daily_LowStock_Report')
BEGIN
    EXEC msdb.dbo.sp_add_job
        @job_name = N'EB_Daily_LowStock_Report',
        @enabled = 1,
        @description = N'Logs EB_SampleDB inventory rows at/under reorder point to audit_log for the daily ops report.';

    EXEC msdb.dbo.sp_add_jobstep
        @job_name = N'EB_Daily_LowStock_Report',
        @step_id = 1,
        @step_name = N'Log low-stock rows',
        @subsystem = N'TSQL',
        @command = N'INSERT INTO dbo.audit_log (table_name, operation, record_id, changed_by, changed_at, old_values, new_values)
SELECT ''inventory'', ''LOW_STOCK_REPORT'', inventory_id, ''EB_Daily_LowStock_Report'', SYSUTCDATETIME(), NULL, CONCAT(''quantity_on_hand='', quantity_on_hand)
FROM dbo.inventory WHERE reorder_point IS NOT NULL AND quantity_on_hand <= reorder_point;',
        @database_name = N'EB_SampleDB';

    EXEC msdb.dbo.sp_add_schedule
        @schedule_name = N'EB_Daily_7AM',
        @freq_type = 4,
        @freq_interval = 1,
        @active_start_time = 070000;

    EXEC msdb.dbo.sp_attach_schedule
        @job_name = N'EB_Daily_LowStock_Report',
        @schedule_name = N'EB_Daily_7AM';

    EXEC msdb.dbo.sp_add_jobserver
        @job_name = N'EB_Daily_LowStock_Report';
END
GO

-- Job 3: weekly fraud-alert sweep — created disabled, to demonstrate that
-- disabled jobs are also detected/migrated correctly (enabled=0 round-trip)
IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'EB_Weekly_FraudAlert_Sweep')
BEGIN
    EXEC msdb.dbo.sp_add_job
        @job_name = N'EB_Weekly_FraudAlert_Sweep',
        @enabled = 0,
        @description = N'Weekly sweep of fraud_alerts for unresolved high-risk cases. Disabled by default in the sample DB.';

    EXEC msdb.dbo.sp_add_jobstep
        @job_name = N'EB_Weekly_FraudAlert_Sweep',
        @step_id = 1,
        @step_name = N'Flag unresolved high-risk fraud alerts',
        @subsystem = N'TSQL',
        @command = N'SELECT alert_id, account_id, alert_type, risk_score, detected_at FROM dbo.fraud_alerts WHERE risk_score >= 80 AND status = ''Open'';',
        @database_name = N'EB_SampleDB';

    EXEC msdb.dbo.sp_add_schedule
        @schedule_name = N'EB_Weekly_Monday_6AM',
        @freq_type = 8,               -- weekly
        @freq_interval = 2,           -- Monday
        @freq_recurrence_factor = 1,  -- every 1 week (required for freq_type=8, must be >=1)
        @active_start_time = 060000;

    EXEC msdb.dbo.sp_attach_schedule
        @job_name = N'EB_Weekly_FraudAlert_Sweep',
        @schedule_name = N'EB_Weekly_Monday_6AM';

    EXEC msdb.dbo.sp_add_jobserver
        @job_name = N'EB_Weekly_FraudAlert_Sweep';
END
GO

PRINT 'SQL Agent objects created (1 operator, 1 alert, 3 jobs — one disabled by design)';
GO

/* ============================================================================
   6. DATABASE MAIL — 1 account, 1 profile (msdb, server-level)
   ============================================================================
   So EndriasBridge's Health Check "Database Mail" detection (flags mail
   profiles found; never auto-migrated, since SQL Server stores the SMTP
   account password encrypted and never exposes it via any query) has
   something real to find. Only creates the profile/account metadata -
   does NOT enable Database Mail XPs (sp_configure) or send any mail, so
   this is safe to run without touching instance-level mail configuration.
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_account WHERE name = N'EB_DBA_MailAccount')
BEGIN
    EXEC msdb.dbo.sysmail_add_account_sp
        @account_name = N'EB_DBA_MailAccount',
        @email_address = N'eb-alerts@example.com',
        @display_name = N'EndriasBridge Sample Alerts',
        @mailserver_name = N'smtp.example.com';
END
GO

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysmail_profile WHERE name = N'EB_DBA_MailProfile')
BEGIN
    EXEC msdb.dbo.sysmail_add_profile_sp
        @profile_name = N'EB_DBA_MailProfile',
        @description = N'Sample profile for EndriasBridge Database Mail detection testing.';
    EXEC msdb.dbo.sysmail_add_profileaccount_sp
        @profile_name = N'EB_DBA_MailProfile',
        @account_name = N'EB_DBA_MailAccount',
        @sequence_number = 1;
END
GO

PRINT 'Database Mail sample profile created (1 account, 1 profile)';
GO

/* ============================================================================
   7. SSIS PACKAGE — 1 legacy-storage package (msdb, server-level)
   ============================================================================
   So EndriasBridge's DMF "SSIS packages" detection (checks both the SSISDB
   catalog and legacy msdb.dbo.sysssispackages, excluding the 8 built-in
   Data Collector packages every instance ships by default) has something
   real to find. This is metadata + a tiny placeholder blob, NOT a real,
   executable DTSX package - it exists purely to be detected, not run.
   Uses sp_ssis_addfolder/sp_ssis_putpackage (the same procs SSMS uses under
   the hood) rather than a raw INSERT, since sysssispackages requires a
   valid folderid and direct inserts fail without one.
   NOTE: NEWID()/GETDATE() must be pre-assigned to variables rather than
   passed as inline function calls in the EXEC parameter list - some
   ODBC Driver 18 / sqlcmd combinations reject the latter with a syntax
   error ("Incorrect syntax near ')'") even though it's otherwise valid
   T-SQL, so this avoids that entirely rather than depending on it.
   ============================================================================ */

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysssispackagefolders WHERE foldername = N'EndriasBridge Samples')
BEGIN
    EXEC msdb.dbo.sp_ssis_addfolder
        @parentfolderid = '00000000-0000-0000-0000-000000000000',
        @name = N'EndriasBridge Samples';
END
GO

IF NOT EXISTS (SELECT 1 FROM msdb.dbo.sysssispackages WHERE name = N'EB_SampleETL_LoadOrders')
BEGIN
    DECLARE @folderid uniqueidentifier = (SELECT folderid FROM msdb.dbo.sysssispackagefolders WHERE foldername = N'EndriasBridge Samples');
    DECLARE @pkgid uniqueidentifier = NEWID();
    DECLARE @verid uniqueidentifier = NEWID();
    DECLARE @now datetime = GETDATE();

    EXEC msdb.dbo.sp_ssis_putpackage
        @name = N'EB_SampleETL_LoadOrders',
        @id = @pkgid,
        @description = N'Sample legacy-storage SSIS package for EndriasBridge SSIS detection testing.',
        @createdate = @now,
        @folderid = @folderid,
        @packagedata = 0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E3C452F3E,
        @packageformat = 8,
        @packagetype = 5,
        @vermajor = 1,
        @verminor = 0,
        @verbuild = 0,
        @vercomments = N'Sample package - metadata only, not a real executable DTSX',
        @verid = @verid;
END
GO

PRINT 'SSIS sample package created (1 folder, 1 legacy-storage package)';
GO

PRINT '--- EB_SampleDB build complete ---';
GO
