/* ============================================================================
   EB_SampleDB — EndriasBridge Oracle Sample Database (Illustrative Edition)
   ============================================================================

   WHAT THIS IS
   ------------
   A single, self-contained SQL*Plus script that builds a small, fully
   populated, multi-industry sample database on Oracle 19c+ (tested against
   Oracle 19c/21c/23ai syntax; requires 12c+ for IDENTITY columns).

   It covers six industries in one schema, matching the logical data model
   produced by the companion Python generator (generate_oracle.py) in this
   same folder:
       1. Healthcare          — patients, providers, appointments, diagnoses,
                                 lab_results, insurance_claims, prescriptions
       2. E-commerce          — categories, products, customers, orders,
                                 order_items, reviews, cart_events
       3. Finance             — accounts, transactions, loans, fraud_alerts,
                                 market_prices, audit_log
       4. Manufacturing / IoT — machines, sensors, sensor_readings,
                                 production_batches, quality_checks
       5. Retail              — stores, loyalty_members, promotions,
                                 inventory, pos_transactions, pos_items
       6. Government           — citizens, gov_employees, permits, violations,
                                 public_contracts, service_requests

   This is a SCALED-DOWN, HAND-WRITTEN, illustrative dataset — roughly
   15-40 rows per table, enough to demonstrate joins, foreign keys,
   functions, procedures, views and triggers end-to-end. It is NOT the
   large-scale (multi-GB, randomly generated) dataset. If you want a much
   larger demo/load-test dataset, use generate_oracle.py in this same
   folder instead (requires Python + oracledb + faker):

       pip install oracledb faker tqdm
       python generate_oracle.py --host localhost --port 1521 \
           --service FREEPDB1 --username EB_SAMPLE --password Secret1 --scale 1.0

   HOW TO RUN THIS SCRIPT
   -----------------------
   1. Create a dedicated Oracle user/schema (recommended name: EB_SAMPLEDB),
      or run against any empty schema of your choosing, e.g.:

          sqlplus sys/password@localhost:1521/FREEPDB1 AS SYSDBA
          CREATE USER eb_sampledb IDENTIFIED BY "ChangeMe_123";
          GRANT DB_DEVELOPER_ROLE TO eb_sampledb;
          ALTER USER eb_sampledb QUOTA UNLIMITED ON USERS;
          EXIT;

   2. Run this script start-to-finish as that user:

          sqlplus eb_sampledb/ChangeMe_123@localhost:1521/FREEPDB1 @EB_SampleDB_oracle.sql

   3. The script creates all tables, loads sample data, adds foreign keys,
      then creates functions/procedures/views/triggers, all in one pass.
      It is safe to run against a completely empty schema. It is NOT
      idempotent/rerunnable as-is (tables are created fresh, not
      dropped-and-recreated) — drop the schema/user and recreate it if you
      want to run it again from scratch.

   NOTES ON ORACLE-SPECIFIC CHOICES
   ---------------------------------
   - SET DEFINE OFF disables '&' substitution-variable parsing, since some
     sample text fields contain literal '&' characters.
   - Surrogate keys use GENERATED BY DEFAULT AS IDENTITY (12c+), which
     allows explicit ID values to be supplied if ever needed, unlike
     GENERATED ALWAYS.
   - There is no native BOOLEAN column type in Oracle SQL; boolean-ish
     flags use NUMBER(1,0) (0/1), matching the companion Python generator.
   - Foreign keys are added via ALTER TABLE after all data is loaded, to
     keep insert ordering simple and avoid constraint-timing issues.
   - Every CREATE OR REPLACE FUNCTION/PROCEDURE/TRIGGER block below is
     followed by a lone "/" on its own line, which is required by SQL*Plus
     to execute a PL/SQL block terminated with a plain "END;".

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

SET DEFINE OFF;
SET SERVEROUTPUT ON SIZE UNLIMITED;
WHENEVER SQLERROR CONTINUE;

PROMPT ===========================================================
PROMPT EB_SampleDB (Oracle) -- build starting
PROMPT ===========================================================

/* ============================================================================
   SECTION 1: TABLES  (all 6 industries, no inline FKs -- FKs added later)
   ============================================================================ */

PROMPT --- Creating tables: Healthcare ---

CREATE TABLE patients (
    patient_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name       VARCHAR2(100)     NOT NULL,
    last_name        VARCHAR2(100)     NOT NULL,
    dob              DATE              NOT NULL,
    gender           VARCHAR2(20),
    ssn              VARCHAR2(11),
    email            VARCHAR2(200),
    phone            VARCHAR2(30),
    address          VARCHAR2(300),
    city             VARCHAR2(100),
    state            VARCHAR2(50),
    zip              VARCHAR2(10),
    insurance_id     VARCHAR2(50),
    blood_type       VARCHAR2(5),
    allergies        CLOB,
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_patients_gender CHECK (gender IN ('M','F','Other','Prefer not to say'))
);

CREATE TABLE providers (
    provider_id      NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name       VARCHAR2(100)     NOT NULL,
    last_name        VARCHAR2(100)     NOT NULL,
    specialty        VARCHAR2(100),
    npi              VARCHAR2(20),
    license_number   VARCHAR2(50),
    phone            VARCHAR2(30),
    email            VARCHAR2(200),
    hospital_affil   VARCHAR2(200),
    active           NUMBER(1,0)       DEFAULT 1,
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE appointments (
    appointment_id   NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    patient_id       NUMBER            NOT NULL,
    provider_id      NUMBER            NOT NULL,
    appt_date        TIMESTAMP         NOT NULL,
    appt_type        VARCHAR2(50),
    status           VARCHAR2(30),
    reason           VARCHAR2(500),
    notes            CLOB,
    duration_min     NUMBER(5,0),
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_appointments_status CHECK (status IN ('SCHEDULED','COMPLETED','CANCELLED','NO_SHOW','RESCHEDULED'))
);

CREATE TABLE diagnoses (
    diagnosis_id     NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    appointment_id   NUMBER            NOT NULL,
    patient_id       NUMBER            NOT NULL,
    icd10_code       VARCHAR2(20)      NOT NULL,
    description      VARCHAR2(500),
    severity         VARCHAR2(20),
    diagnosed_date   DATE,
    resolved_date    DATE,
    chronic          NUMBER(1,0)       DEFAULT 0,
    CONSTRAINT ck_diagnoses_severity CHECK (severity IN ('MILD','MODERATE','SEVERE','CRITICAL'))
);

CREATE TABLE lab_results (
    lab_id           NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    patient_id       NUMBER            NOT NULL,
    appointment_id   NUMBER,
    test_name        VARCHAR2(200)     NOT NULL,
    test_code        VARCHAR2(30),
    result_value     VARCHAR2(200),
    result_numeric   NUMBER(18,4),
    unit             VARCHAR2(30),
    reference_range  VARCHAR2(100),
    abnormal_flag    VARCHAR2(5),
    collected_at     TIMESTAMP,
    resulted_at      TIMESTAMP,
    lab_name         VARCHAR2(200),
    notes            CLOB
);

CREATE TABLE insurance_claims (
    claim_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    patient_id       NUMBER            NOT NULL,
    appointment_id   NUMBER,
    insurance_co     VARCHAR2(200),
    policy_number    VARCHAR2(50),
    claim_date       DATE,
    service_date     DATE,
    diagnosis_code   VARCHAR2(20),
    procedure_code   VARCHAR2(20),
    billed_amount    NUMBER(12,2),
    allowed_amount   NUMBER(12,2),
    paid_amount      NUMBER(12,2),
    status           VARCHAR2(30),
    denial_reason    VARCHAR2(300),
    CONSTRAINT ck_insurance_claims_status CHECK (status IN ('SUBMITTED','APPROVED','DENIED','PENDING','PAID','APPEALED'))
);

CREATE TABLE prescriptions (
    rx_id            NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    patient_id       NUMBER            NOT NULL,
    provider_id      NUMBER            NOT NULL,
    drug_name        VARCHAR2(200)     NOT NULL,
    ndc_code         VARCHAR2(20),
    dosage           VARCHAR2(100),
    frequency        VARCHAR2(100),
    quantity         NUMBER(10,2),
    refills          NUMBER(3,0),
    prescribed_date  DATE,
    expiry_date      DATE,
    pharmacy         VARCHAR2(200),
    status           VARCHAR2(30),
    CONSTRAINT ck_prescriptions_status CHECK (status IN ('ACTIVE','FILLED','EXPIRED','CANCELLED'))
);

PROMPT --- Creating tables: E-commerce ---

CREATE TABLE categories (
    category_id      NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name             VARCHAR2(200)     NOT NULL,
    parent_id        NUMBER,
    description      CLOB,
    active           NUMBER(1,0)       DEFAULT 1,
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sku              VARCHAR2(50)      NOT NULL,
    name             VARCHAR2(300)     NOT NULL,
    category_id      NUMBER,
    description      CLOB,
    unit_price       NUMBER(12,2)      NOT NULL,
    cost_price       NUMBER(12,2),
    weight_kg        NUMBER(8,3),
    stock_qty        NUMBER(12,0)      DEFAULT 0,
    reorder_level    NUMBER(12,0)      DEFAULT 10,
    brand            VARCHAR2(200),
    active           NUMBER(1,0)       DEFAULT 1,
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_products_price CHECK (unit_price >= 0)
);

CREATE TABLE customers (
    customer_id      NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name       VARCHAR2(100)     NOT NULL,
    last_name        VARCHAR2(100)     NOT NULL,
    email            VARCHAR2(200)     NOT NULL,
    phone            VARCHAR2(30),
    dob              DATE,
    gender           VARCHAR2(20),
    address          VARCHAR2(300),
    city             VARCHAR2(100),
    state            VARCHAR2(50),
    zip              VARCHAR2(10),
    country          VARCHAR2(50)      DEFAULT 'US',
    loyalty_points   NUMBER(12,0)      DEFAULT 0,
    tier             VARCHAR2(20),
    registered_at    TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_customers_gender CHECK (gender IN ('M','F','Other','Prefer not to say'))
);

CREATE TABLE orders (
    order_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id      NUMBER            NOT NULL,
    order_date       TIMESTAMP         NOT NULL,
    status           VARCHAR2(30),
    shipping_method  VARCHAR2(50),
    shipping_addr    VARCHAR2(400),
    subtotal         NUMBER(14,2),
    tax_amount       NUMBER(10,2),
    shipping_cost    NUMBER(10,2),
    discount_amount  NUMBER(10,2)      DEFAULT 0,
    total_amount     NUMBER(14,2),
    payment_method   VARCHAR2(50),
    notes            CLOB,
    CONSTRAINT ck_orders_status CHECK (status IN ('PENDING','CONFIRMED','SHIPPED','DELIVERED','CANCELLED','RETURNED')),
    CONSTRAINT ck_orders_total_nonneg CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
    item_id          NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id         NUMBER            NOT NULL,
    product_id       NUMBER            NOT NULL,
    quantity         NUMBER(10,0)      NOT NULL,
    unit_price       NUMBER(12,2)      NOT NULL,
    discount_pct     NUMBER(5,2)       DEFAULT 0,
    line_total       NUMBER(14,2),
    returned         NUMBER(1,0)       DEFAULT 0
);

CREATE TABLE reviews (
    review_id        NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id       NUMBER            NOT NULL,
    customer_id      NUMBER            NOT NULL,
    rating           NUMBER(2,0)       NOT NULL,
    title            VARCHAR2(300),
    body             CLOB,
    helpful_votes    NUMBER(8,0)       DEFAULT 0,
    verified_purchase NUMBER(1,0)      DEFAULT 0,
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_reviews_rating CHECK (rating BETWEEN 1 AND 5)
);

CREATE TABLE cart_events (
    event_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id      NUMBER,
    session_id       VARCHAR2(100),
    product_id       NUMBER,
    event_type       VARCHAR2(30),
    quantity         NUMBER(6,0),
    page_url         VARCHAR2(1000),
    ip_address       VARCHAR2(45),
    user_agent       VARCHAR2(500),
    event_ts         TIMESTAMP         NOT NULL
);

PROMPT --- Creating tables: Finance ---

CREATE TABLE accounts (
    account_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    account_number   VARCHAR2(30)      NOT NULL,
    account_type     VARCHAR2(30),
    owner_name       VARCHAR2(200),
    owner_email      VARCHAR2(200),
    routing_number   VARCHAR2(20),
    currency         VARCHAR2(5)       DEFAULT 'USD',
    balance          NUMBER(18,2)      DEFAULT 0,
    credit_limit     NUMBER(18,2),
    status           VARCHAR2(20)      DEFAULT 'ACTIVE',
    opened_date      DATE,
    closed_date      DATE,
    branch           VARCHAR2(100),
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_accounts_status CHECK (status IN ('ACTIVE','FROZEN','CLOSED'))
);

CREATE TABLE transactions (
    txn_id           NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    account_id       NUMBER            NOT NULL,
    txn_date         TIMESTAMP         NOT NULL,
    posted_date      TIMESTAMP,
    amount           NUMBER(18,2)      NOT NULL,
    txn_type         VARCHAR2(20),
    category         VARCHAR2(50),
    merchant_name    VARCHAR2(300),
    merchant_city    VARCHAR2(100),
    merchant_state   VARCHAR2(50),
    merchant_country VARCHAR2(5)       DEFAULT 'US',
    reference_no     VARCHAR2(50),
    description      VARCHAR2(500),
    balance_after    NUMBER(18,2),
    channel          VARCHAR2(30),
    status           VARCHAR2(20)      DEFAULT 'POSTED',
    CONSTRAINT ck_transactions_status CHECK (status IN ('POSTED','PENDING','DECLINED'))
);

CREATE TABLE loans (
    loan_id          NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    account_id       NUMBER,
    loan_type        VARCHAR2(30),
    principal        NUMBER(18,2)      NOT NULL,
    interest_rate    NUMBER(6,4),
    term_months      NUMBER(4,0),
    monthly_payment  NUMBER(14,2),
    origination_date DATE,
    maturity_date    DATE,
    outstanding_bal  NUMBER(18,2),
    status           VARCHAR2(20),
    collateral       VARCHAR2(300),
    officer_id       NUMBER(10,0),
    CONSTRAINT ck_loans_status CHECK (status IN ('CURRENT','DELINQUENT','PAID_OFF','DEFAULT','IN_REVIEW'))
);

CREATE TABLE fraud_alerts (
    alert_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    txn_id           NUMBER,
    account_id       NUMBER,
    alert_date       TIMESTAMP         NOT NULL,
    rule_name        VARCHAR2(100),
    risk_score       NUMBER(5,2),
    alert_type       VARCHAR2(50),
    description      VARCHAR2(500),
    status           VARCHAR2(20)      DEFAULT 'OPEN',
    resolved_by      VARCHAR2(100),
    resolved_at      TIMESTAMP,
    CONSTRAINT ck_fraud_alerts_status CHECK (status IN ('OPEN','RESOLVED','FALSE_POSITIVE','ESCALATED'))
);

CREATE TABLE market_prices (
    price_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ticker           VARCHAR2(20)      NOT NULL,
    price_date       DATE              NOT NULL,
    open_price       NUMBER(14,4),
    high_price       NUMBER(14,4),
    low_price        NUMBER(14,4),
    close_price      NUMBER(14,4)      NOT NULL,
    adj_close        NUMBER(14,4),
    volume           NUMBER(20,0),
    market_cap       NUMBER(22,2)
);

CREATE TABLE audit_log (
    audit_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    log_ts           TIMESTAMP         NOT NULL,
    db_user          VARCHAR2(100),
    app_user         VARCHAR2(100),
    schema_name      VARCHAR2(100),
    table_name       VARCHAR2(100),
    operation        VARCHAR2(10),
    row_id           NUMBER(20,0),
    old_values       CLOB,
    new_values       CLOB,
    ip_address       VARCHAR2(45),
    app_name         VARCHAR2(100),
    session_id       VARCHAR2(100)
);

PROMPT --- Creating tables: Manufacturing / IoT ---

CREATE TABLE machines (
    machine_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    asset_tag        VARCHAR2(50)      NOT NULL,
    name             VARCHAR2(200),
    type             VARCHAR2(100),
    manufacturer     VARCHAR2(200),
    model            VARCHAR2(100),
    serial_number    VARCHAR2(100),
    location         VARCHAR2(200),
    floor            VARCHAR2(50),
    install_date     DATE,
    last_maint_date  DATE,
    status           VARCHAR2(30)      DEFAULT 'RUNNING',
    rated_capacity   NUMBER(12,2),
    created_at       TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT ck_machines_status CHECK (status IN ('RUNNING','IDLE','MAINTENANCE','FAULT'))
);

CREATE TABLE sensors (
    sensor_id        NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    machine_id       NUMBER            NOT NULL,
    sensor_code      VARCHAR2(50)      NOT NULL,
    sensor_type      VARCHAR2(100),
    unit             VARCHAR2(20),
    min_threshold    NUMBER(14,4),
    max_threshold    NUMBER(14,4),
    active           NUMBER(1,0)       DEFAULT 1,
    installed_date   DATE,
    firmware_ver     VARCHAR2(30)
);

CREATE TABLE sensor_readings (
    reading_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    sensor_id        NUMBER            NOT NULL,
    machine_id       NUMBER            NOT NULL,
    reading_ts       TIMESTAMP         NOT NULL,
    value            NUMBER(18,6)      NOT NULL,
    quality          NUMBER(3,0)       DEFAULT 100,
    anomaly          NUMBER(1,0)       DEFAULT 0,
    batch_ref        VARCHAR2(50)
);

CREATE TABLE production_batches (
    batch_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    batch_number     VARCHAR2(50)      NOT NULL,
    product_code     VARCHAR2(50),
    machine_id       NUMBER,
    start_time       TIMESTAMP,
    end_time         TIMESTAMP,
    quantity_planned NUMBER(12,2),
    quantity_actual  NUMBER(12,2),
    defect_count     NUMBER(10,0)      DEFAULT 0,
    shift            VARCHAR2(20),
    operator_id      NUMBER(10,0),
    status           VARCHAR2(30),
    CONSTRAINT ck_production_batches_status CHECK (status IN ('COMPLETED','IN_PROGRESS','SCRAPPED','ON_HOLD'))
);

CREATE TABLE quality_checks (
    check_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    batch_id         NUMBER            NOT NULL,
    check_type       VARCHAR2(100),
    checked_by       VARCHAR2(100),
    check_ts         TIMESTAMP,
    result           VARCHAR2(20),
    measurement      NUMBER(14,4),
    unit             VARCHAR2(20),
    pass_threshold   NUMBER(14,4),
    fail_threshold   NUMBER(14,4),
    notes            CLOB,
    CONSTRAINT ck_quality_checks_result CHECK (result IN ('PASS','FAIL','MARGINAL'))
);

PROMPT --- Creating tables: Retail ---

CREATE TABLE stores (
    store_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_code       VARCHAR2(20)      NOT NULL,
    name             VARCHAR2(200),
    type             VARCHAR2(50),
    address          VARCHAR2(300),
    city             VARCHAR2(100),
    state            VARCHAR2(50),
    zip              VARCHAR2(10),
    country          VARCHAR2(50)      DEFAULT 'US',
    phone            VARCHAR2(30),
    manager          VARCHAR2(200),
    open_date        DATE,
    sq_footage       NUMBER(10,0),
    active           NUMBER(1,0)       DEFAULT 1
);

CREATE TABLE loyalty_members (
    member_id        NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    member_number    VARCHAR2(30)      NOT NULL,
    first_name       VARCHAR2(100),
    last_name        VARCHAR2(100),
    email            VARCHAR2(200),
    phone            VARCHAR2(30),
    tier             VARCHAR2(20)      DEFAULT 'BRONZE',
    points_balance   NUMBER(12,0)      DEFAULT 0,
    points_lifetime  NUMBER(14,0)      DEFAULT 0,
    joined_date      DATE,
    last_activity    DATE,
    store_id         NUMBER,
    opted_in_email   NUMBER(1,0)       DEFAULT 1
);

CREATE TABLE promotions (
    promo_id         NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    promo_code       VARCHAR2(30)      NOT NULL,
    description      VARCHAR2(500),
    promo_type       VARCHAR2(30),
    discount_value   NUMBER(10,2),
    discount_pct     NUMBER(5,2),
    min_purchase     NUMBER(12,2),
    start_date       DATE,
    end_date         DATE,
    usage_limit      NUMBER(10,0),
    times_used       NUMBER(10,0)      DEFAULT 0,
    store_id         NUMBER,
    active           NUMBER(1,0)       DEFAULT 1
);

CREATE TABLE inventory (
    inventory_id     NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_id         NUMBER            NOT NULL,
    product_code     VARCHAR2(50)      NOT NULL,
    product_name     VARCHAR2(300),
    category         VARCHAR2(100),
    qty_on_hand      NUMBER(12,0)      DEFAULT 0,
    qty_reserved     NUMBER(12,0)      DEFAULT 0,
    reorder_point    NUMBER(12,0)      DEFAULT 5,
    unit_cost        NUMBER(12,2),
    unit_price       NUMBER(12,2),
    last_received    DATE,
    last_counted     DATE,
    bin_location     VARCHAR2(30)
);

CREATE TABLE pos_transactions (
    pos_txn_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    store_id         NUMBER            NOT NULL,
    register_id      VARCHAR2(20),
    cashier_id       VARCHAR2(20),
    member_id        NUMBER,
    txn_ts           TIMESTAMP         NOT NULL,
    subtotal         NUMBER(12,2),
    tax_amount       NUMBER(10,2),
    discount_amount  NUMBER(10,2)      DEFAULT 0,
    total_amount     NUMBER(12,2),
    payment_type     VARCHAR2(30),
    promo_id         NUMBER,
    points_earned    NUMBER(10,0)      DEFAULT 0,
    voided           NUMBER(1,0)       DEFAULT 0
);

CREATE TABLE pos_items (
    pos_item_id      NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    pos_txn_id       NUMBER            NOT NULL,
    product_code     VARCHAR2(50),
    product_name     VARCHAR2(300),
    quantity         NUMBER(10,3)      NOT NULL,
    unit_price       NUMBER(12,2),
    discount_amt     NUMBER(10,2)      DEFAULT 0,
    line_total       NUMBER(12,2),
    returned         NUMBER(1,0)       DEFAULT 0
);

PROMPT --- Creating tables: Government ---

CREATE TABLE citizens (
    citizen_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name       VARCHAR2(100)     NOT NULL,
    last_name        VARCHAR2(100)     NOT NULL,
    dob              DATE,
    gender           VARCHAR2(20),
    national_id      VARCHAR2(30),
    email            VARCHAR2(200),
    phone            VARCHAR2(30),
    address          VARCHAR2(300),
    city             VARCHAR2(100),
    state            VARCHAR2(50),
    zip              VARCHAR2(10),
    registered_at    TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    active           NUMBER(1,0)       DEFAULT 1,
    CONSTRAINT ck_citizens_gender CHECK (gender IN ('M','F','Other','Prefer not to say'))
);

CREATE TABLE gov_employees (
    emp_id           NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    first_name       VARCHAR2(100)     NOT NULL,
    last_name        VARCHAR2(100)     NOT NULL,
    dept             VARCHAR2(200),
    title            VARCHAR2(200),
    grade            VARCHAR2(20),
    salary           NUMBER(14,2),
    hire_date        DATE,
    termination_date DATE,
    supervisor_id    NUMBER,
    email            VARCHAR2(200),
    active           NUMBER(1,0)       DEFAULT 1
);

CREATE TABLE permits (
    permit_id        NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    permit_number    VARCHAR2(50)      NOT NULL,
    citizen_id       NUMBER,
    permit_type      VARCHAR2(100),
    description      VARCHAR2(500),
    address          VARCHAR2(300),
    parcel_number    VARCHAR2(50),
    applied_date     DATE,
    issued_date      DATE,
    expiry_date      DATE,
    fee_amount       NUMBER(12,2),
    status           VARCHAR2(30),
    reviewer_id      NUMBER,
    notes            CLOB,
    CONSTRAINT ck_permits_status CHECK (status IN ('SUBMITTED','UNDER_REVIEW','APPROVED','DENIED','ISSUED','EXPIRED','FINALED','SUSPENDED'))
);

CREATE TABLE violations (
    violation_id     NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    citizen_id       NUMBER,
    violation_code   VARCHAR2(30)      NOT NULL,
    description      VARCHAR2(500),
    location         VARCHAR2(300),
    occurred_date    DATE,
    reported_date    DATE,
    officer_id       NUMBER,
    fine_amount      NUMBER(12,2),
    paid_amount      NUMBER(12,2)      DEFAULT 0,
    status           VARCHAR2(30),
    court_date       DATE,
    notes            CLOB,
    CONSTRAINT ck_violations_status CHECK (status IN ('OPEN','PAID','DISMISSED','CONTESTED','WARRANT'))
);

CREATE TABLE public_contracts (
    contract_id      NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    contract_number  VARCHAR2(50)      NOT NULL,
    vendor_name      VARCHAR2(300),
    vendor_id        VARCHAR2(50),
    dept             VARCHAR2(200),
    title            VARCHAR2(300),
    description      CLOB,
    contract_value   NUMBER(18,2),
    start_date       DATE,
    end_date         DATE,
    status           VARCHAR2(30),
    award_date       DATE,
    contact_officer  NUMBER,
    CONSTRAINT ck_public_contracts_status CHECK (status IN ('ACTIVE','COMPLETED','CANCELLED','PENDING','EXPIRED'))
);

CREATE TABLE service_requests (
    request_id       NUMBER            GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    citizen_id       NUMBER,
    request_type     VARCHAR2(100),
    category         VARCHAR2(100),
    description      CLOB,
    location         VARCHAR2(300),
    submitted_at     TIMESTAMP         DEFAULT CURRENT_TIMESTAMP,
    resolved_at      TIMESTAMP,
    assigned_emp     NUMBER,
    priority         VARCHAR2(20),
    status           VARCHAR2(30)      DEFAULT 'OPEN',
    satisfaction     NUMBER(2,0),
    CONSTRAINT ck_service_requests_status CHECK (status IN ('OPEN','ASSIGNED','IN_PROGRESS','RESOLVED','CLOSED','DUPLICATE')),
    CONSTRAINT ck_service_requests_priority CHECK (priority IN ('LOW','MEDIUM','HIGH','CRITICAL'))
);

PROMPT --- Tables created ---

/* ============================================================================
   SECTION 2: INDEXES  (on FK / commonly-filtered columns; PKs already
   have implicit unique indexes from the IDENTITY primary keys above)
   ============================================================================ */

PROMPT --- Creating indexes ---

-- Healthcare
CREATE INDEX ix_appointments_patient   ON appointments(patient_id);
CREATE INDEX ix_appointments_provider  ON appointments(provider_id);
CREATE INDEX ix_appointments_date      ON appointments(appt_date);
CREATE INDEX ix_diagnoses_patient      ON diagnoses(patient_id);
CREATE INDEX ix_diagnoses_appointment  ON diagnoses(appointment_id);
CREATE INDEX ix_lab_results_patient    ON lab_results(patient_id);
CREATE INDEX ix_insurance_claims_pat   ON insurance_claims(patient_id);
CREATE INDEX ix_prescriptions_patient  ON prescriptions(patient_id);
CREATE INDEX ix_prescriptions_provider ON prescriptions(provider_id);

-- E-commerce
CREATE INDEX ix_products_category      ON products(category_id);
CREATE INDEX ix_orders_customer        ON orders(customer_id);
CREATE INDEX ix_orders_date            ON orders(order_date);
CREATE INDEX ix_order_items_order      ON order_items(order_id);
CREATE INDEX ix_order_items_product    ON order_items(product_id);
CREATE INDEX ix_reviews_product        ON reviews(product_id);
CREATE INDEX ix_reviews_customer       ON reviews(customer_id);
CREATE INDEX ix_cart_events_customer   ON cart_events(customer_id);
CREATE INDEX ix_cart_events_product    ON cart_events(product_id);

-- Finance
CREATE INDEX ix_transactions_account   ON transactions(account_id);
CREATE INDEX ix_transactions_date      ON transactions(txn_date);
CREATE INDEX ix_loans_account          ON loans(account_id);
CREATE INDEX ix_fraud_alerts_account   ON fraud_alerts(account_id);
CREATE INDEX ix_fraud_alerts_txn       ON fraud_alerts(txn_id);
CREATE INDEX ix_market_prices_ticker   ON market_prices(ticker, price_date);

-- Manufacturing / IoT
CREATE INDEX ix_sensors_machine        ON sensors(machine_id);
CREATE INDEX ix_sensor_readings_sensor ON sensor_readings(sensor_id);
CREATE INDEX ix_sensor_readings_mach   ON sensor_readings(machine_id);
CREATE INDEX ix_prod_batches_machine   ON production_batches(machine_id);
CREATE INDEX ix_quality_checks_batch   ON quality_checks(batch_id);

-- Retail
CREATE INDEX ix_loyalty_members_store  ON loyalty_members(store_id);
CREATE INDEX ix_promotions_store       ON promotions(store_id);
CREATE INDEX ix_inventory_store        ON inventory(store_id);
CREATE INDEX ix_pos_transactions_store ON pos_transactions(store_id);
CREATE INDEX ix_pos_transactions_memb  ON pos_transactions(member_id);
CREATE INDEX ix_pos_items_txn          ON pos_items(pos_txn_id);

-- Government
CREATE INDEX ix_permits_citizen        ON permits(citizen_id);
CREATE INDEX ix_violations_citizen     ON violations(citizen_id);
CREATE INDEX ix_public_contracts_off   ON public_contracts(contact_officer);
CREATE INDEX ix_service_requests_cit   ON service_requests(citizen_id);
CREATE INDEX ix_service_requests_emp   ON service_requests(assigned_emp);

PROMPT --- Indexes created ---

/* ============================================================================
   SECTION 3: SAMPLE DATA -- Healthcare
   Insert order: patients -> providers -> appointments -> diagnoses ->
                 lab_results -> insurance_claims -> prescriptions
   (IDENTITY columns are omitted from column lists, so patient_id etc.
   assign sequentially starting at 1, in insert order, on a fresh schema.)
   ============================================================================ */

PROMPT --- Loading data: Healthcare ---

INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Maria','Gonzalez',DATE '1985-03-14','F','412-55-1001','maria.gonzalez@example.com','555-201-1001','142 Oak St','Austin','TX','78701','INS-100234','O+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('James','Whitfield',DATE '1972-11-02','M','412-55-1002','james.whitfield@example.com','555-201-1002','88 Maple Ave','Denver','CO','80202','INS-100235','A-','Penicillin');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Aisha','Bello',DATE '1990-07-22','F','412-55-1003','aisha.bello@example.com','555-201-1003','21 Birch Ln','Columbus','OH','43004','INS-100236','B+','Sulfa');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Robert','Kim',DATE '1968-01-30','M','412-55-1004','robert.kim@example.com','555-201-1004','375 Cedar Rd','Seattle','WA','98101','INS-100237','AB+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Linda','Souza',DATE '1995-05-09','F','412-55-1005','linda.souza@example.com','555-201-1005','60 Elm St','Miami','FL','33101','INS-100238','O-','Latex');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('David','Nguyen',DATE '1979-09-18','M','412-55-1006','david.nguyen@example.com','555-201-1006','19 Pine Ct','San Jose','CA','95101','INS-100239','A+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Karen','O''Brien',DATE '1988-12-25','F','412-55-1007','karen.obrien@example.com','555-201-1007','403 Willow Dr','Boston','MA','02108','INS-100240','B-','Aspirin');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Michael','Petrov',DATE '1960-04-11','M','412-55-1008','michael.petrov@example.com','555-201-1008','58 Spruce Ave','Chicago','IL','60601','INS-100241','O+','Nuts');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Sofia','Rossi',DATE '2001-02-17','F','412-55-1009','sofia.rossi@example.com','555-201-1009','12 Aspen Way','Portland','OR','97201','INS-100242','AB-','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Thomas','Baker',DATE '1955-08-06','M','412-55-1010','thomas.baker@example.com','555-201-1010','77 Poplar St','Phoenix','AZ','85001','INS-100243','A-','Penicillin');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Emily','Chen',DATE '1993-06-29','F','412-55-1011','emily.chen@example.com','555-201-1011','9 Chestnut Blvd','San Diego','CA','92101','INS-100244','B+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Daniel','Okafor',DATE '1983-10-03','M','412-55-1012','daniel.okafor@example.com','555-201-1012','230 Magnolia Dr','Atlanta','GA','30301','INS-100245','O+','Sulfa');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Grace','Park',DATE '1977-03-21','F','412-55-1013','grace.park@example.com','555-201-1013','14 Redwood Ln','Dallas','TX','75201','INS-100246','A+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Christopher','Lang',DATE '1965-07-14','M','412-55-1014','christopher.lang@example.com','555-201-1014','501 Fir Ave','Minneapolis','MN','55401','INS-100247','B-','Latex');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Natalie','Fischer',DATE '1998-01-12','F','412-55-1015','natalie.fischer@example.com','555-201-1015','83 Sycamore Rd','Charlotte','NC','28201','INS-100248','AB+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Kevin','Alvarez',DATE '1970-09-27','M','412-55-1016','kevin.alvarez@example.com','555-201-1016','66 Walnut St','Houston','TX','77002','INS-100249','O-','Aspirin');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Patricia','Moore',DATE '1948-11-19','F','412-55-1017','patricia.moore@example.com','555-201-1017','29 Hemlock Ct','Tampa','FL','33601','INS-100250','A-','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Brian','Kowalski',DATE '1982-05-05','M','412-55-1018','brian.kowalski@example.com','555-201-1018','510 Cypress Dr','Detroit','MI','48201','INS-100251','B+','Nuts');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Rachel','Adeyemi',DATE '1991-08-08','F','412-55-1019','rachel.adeyemi@example.com','555-201-1019','44 Sequoia Ave','Newark','NJ','07101','INS-100252','O+','None');
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, blood_type, allergies) VALUES ('Steven','Wallace',DATE '1975-02-02','M','412-55-1020','steven.wallace@example.com','555-201-1020','18 Alder St','Kansas City','MO','64101','INS-100253','AB-','Penicillin');

INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Elena','Marsh','Cardiology','1234567890','LIC-9001','555-301-2001','elena.marsh@ebhealth.example.com','St. Vincent Medical Center',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Samuel','Osei','Oncology','1234567891','LIC-9002','555-301-2002','samuel.osei@ebhealth.example.com','Riverside General',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Priya','Raman','Pediatrics','1234567892','LIC-9003','555-301-2003','priya.raman@ebhealth.example.com','Sunrise Children''s Hospital',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('John','Delacroix','Neurology','1234567893','LIC-9004','555-301-2004','john.delacroix@ebhealth.example.com','St. Vincent Medical Center',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Hannah','Weiss','Orthopedics','1234567894','LIC-9005','555-301-2005','hannah.weiss@ebhealth.example.com','Lakeside Orthopedic Group',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Marcus','Diallo','General Practice','1234567895','LIC-9006','555-301-2006','marcus.diallo@ebhealth.example.com','Riverside General',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Julia','Novak','Dermatology','1234567896','LIC-9007','555-301-2007','julia.novak@ebhealth.example.com','Sunrise Children''s Hospital',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Andres','Villanueva','Psychiatry','1234567897','LIC-9008','555-301-2008','andres.villanueva@ebhealth.example.com','St. Vincent Medical Center',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Ingrid','Larsen','Radiology','1234567898','LIC-9009','555-301-2009','ingrid.larsen@ebhealth.example.com','Riverside General',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Omar','Farouk','Surgery','1234567899','LIC-9010','555-301-2010','omar.farouk@ebhealth.example.com','Lakeside Orthopedic Group',1);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Beatrice','Lund','General Practice','1234567900','LIC-9011','555-301-2011','beatrice.lund@ebhealth.example.com','Sunrise Children''s Hospital',0);
INSERT INTO providers (first_name, last_name, specialty, npi, license_number, phone, email, hospital_affil, active) VALUES ('Victor','Ibarra','Cardiology','1234567901','LIC-9012','555-301-2012','victor.ibarra@ebhealth.example.com','St. Vincent Medical Center',1);

-- appointment_id 1..20 map 1:1 to patient_id 1..20 (provider_id cycles 1..12); 21..30 revisit patients 1..10
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (1,1,TIMESTAMP '2024-01-10 09:00:00','Office Visit','COMPLETED','Chest pain follow-up',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (2,2,TIMESTAMP '2024-01-11 10:00:00','Follow-up','COMPLETED','Chemo follow-up',45);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (3,3,TIMESTAMP '2024-01-12 11:00:00','Office Visit','COMPLETED','Annual pediatric checkup',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (4,4,TIMESTAMP '2024-01-15 09:30:00','Office Visit','COMPLETED','Recurring headaches',40);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (5,5,TIMESTAMP '2024-01-16 13:00:00','Procedure','COMPLETED','Knee arthroscopy consult',60);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (6,6,TIMESTAMP '2024-01-17 08:15:00','Office Visit','COMPLETED','Annual physical',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (7,7,TIMESTAMP '2024-01-18 14:00:00','Office Visit','COMPLETED','Skin rash',15);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (8,8,TIMESTAMP '2024-01-19 10:45:00','Telehealth','COMPLETED','Anxiety medication review',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (9,9,TIMESTAMP '2024-01-22 09:00:00','Lab Only','COMPLETED','Imaging review',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (10,10,TIMESTAMP '2024-01-23 11:30:00','Procedure','COMPLETED','Pre-op surgical eval',45);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (11,11,TIMESTAMP '2024-01-24 08:00:00','Office Visit','COMPLETED','General wellness visit',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (12,12,TIMESTAMP '2024-01-25 15:00:00','Office Visit','COMPLETED','Palpitations',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (13,1,TIMESTAMP '2024-02-01 09:00:00','Follow-up','COMPLETED','Hypertension follow-up',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (14,2,TIMESTAMP '2024-02-02 10:00:00','Office Visit','SCHEDULED','New patient oncology consult',60);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (15,3,TIMESTAMP '2024-02-03 11:00:00','Office Visit','COMPLETED','Well-child visit',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (16,4,TIMESTAMP '2024-02-05 09:30:00','Telehealth','NO_SHOW','Migraine check-in',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (17,5,TIMESTAMP '2024-02-06 13:30:00','Office Visit','COMPLETED','Sports injury eval',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (18,6,TIMESTAMP '2024-02-07 08:45:00','Office Visit','CANCELLED','Diabetes check-in',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (19,7,TIMESTAMP '2024-02-08 14:15:00','Office Visit','COMPLETED','Eczema follow-up',15);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (20,8,TIMESTAMP '2024-02-09 10:00:00','Telehealth','COMPLETED','Depression screening',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (1,9,TIMESTAMP '2024-03-01 09:00:00','Lab Only','COMPLETED','Cardiac imaging',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (2,10,TIMESTAMP '2024-03-02 09:30:00','Procedure','SCHEDULED','Tumor resection follow-up',60);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (3,11,TIMESTAMP '2024-03-03 10:15:00','Office Visit','COMPLETED','Vaccination visit',15);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (4,12,TIMESTAMP '2024-03-04 11:00:00','Office Visit','COMPLETED','Cardiology referral',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (5,1,TIMESTAMP '2024-03-05 08:30:00','Office Visit','RESCHEDULED','Blood pressure check',15);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (6,2,TIMESTAMP '2024-03-06 09:45:00','Follow-up','COMPLETED','Post-treatment labs',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (7,3,TIMESTAMP '2024-03-07 13:00:00','Office Visit','COMPLETED','Sports physical',20);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (8,4,TIMESTAMP '2024-03-08 14:30:00','Office Visit','COMPLETED','Nerve pain consult',30);
INSERT INTO appointments (patient_id, provider_id, appt_date, appt_type, status, reason, duration_min) VALUES (9,5,TIMESTAMP '2024-03-11 09:00:00','Procedure','COMPLETED','Shoulder injection',30);

-- diagnoses: appointment_id i maps to patient_id i for i = 1..20 (per the mapping above)
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (1,1,'I10','Essential hypertension','MODERATE',DATE '2024-01-10',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (2,2,'I25.10','Coronary artery disease','SEVERE',DATE '2024-01-11',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (3,3,'Z00.00','Routine child health exam, normal','MILD',DATE '2024-01-12',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (4,4,'G47.00','Insomnia, unspecified','MODERATE',DATE '2024-01-15',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (5,5,'M54.5','Low back pain','MODERATE',DATE '2024-01-16',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (6,6,'E11.9','Type 2 diabetes mellitus','MODERATE',DATE '2024-01-17',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (7,7,'J06.9','Acute upper respiratory infection','MILD',DATE '2024-01-18',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (8,8,'F41.1','Generalized anxiety disorder','MODERATE',DATE '2024-01-19',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (9,9,'N39.0','Urinary tract infection','MILD',DATE '2024-01-22',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (10,10,'K21.0','GERD with esophagitis','MODERATE',DATE '2024-01-23',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (11,11,'J18.9','Pneumonia, unspecified organism','SEVERE',DATE '2024-01-24',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (12,12,'I10','Essential hypertension','MILD',DATE '2024-01-25',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (13,13,'E78.5','Hyperlipidemia, unspecified','MILD',DATE '2024-02-01',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (15,15,'Z00.00','Routine child health exam, normal','MILD',DATE '2024-02-03',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (17,17,'M54.5','Low back pain, sports-related','MODERATE',DATE '2024-02-06',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (19,19,'J06.9','Acute upper respiratory infection','MILD',DATE '2024-02-08',0);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (20,20,'F41.1','Generalized anxiety disorder','SEVERE',DATE '2024-02-09',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (7,7,'N39.0','Recurrent UTI','MODERATE',DATE '2024-01-18',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (12,12,'I25.10','Coronary artery disease, mild','SEVERE',DATE '2024-01-25',1);
INSERT INTO diagnoses (appointment_id, patient_id, icd10_code, description, severity, diagnosed_date, chronic) VALUES (4,4,'F41.1','Anxiety contributing to insomnia','MILD',DATE '2024-01-15',0);

INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (1,1,'Lipid Panel','80061','188',188,'mg/dL','<200','N',TIMESTAMP '2024-01-10 09:15:00',TIMESTAMP '2024-01-10 14:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (2,2,'CBC','85025','Abnormal WBC',13.4,'K/uL','4.5-11.0','Y',TIMESTAMP '2024-01-11 10:15:00',TIMESTAMP '2024-01-11 16:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (3,3,'CBC','85025','Normal',6.2,'K/uL','4.5-11.0','N',TIMESTAMP '2024-01-12 11:15:00',TIMESTAMP '2024-01-12 15:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (4,4,'TSH','84443','2.1',2.1,'mIU/L','0.4-4.0','N',TIMESTAMP '2024-01-15 09:45:00',TIMESTAMP '2024-01-15 13:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (5,5,'CMP','80053','Normal',NULL,NULL,'Panel','N',TIMESTAMP '2024-01-16 13:15:00',TIMESTAMP '2024-01-16 18:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (6,6,'HbA1c','83036','7.8',7.8,'%','<5.7','Y',TIMESTAMP '2024-01-17 08:30:00',TIMESTAMP '2024-01-17 12:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (7,7,'Blood Culture','87040','No growth',NULL,NULL,'Negative','N',TIMESTAMP '2024-01-18 14:15:00',TIMESTAMP '2024-01-20 09:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (8,8,'BMP','80048','Normal',NULL,NULL,'Panel','N',TIMESTAMP '2024-01-19 11:00:00',TIMESTAMP '2024-01-19 15:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (9,9,'UA','81001','Positive nitrites',NULL,NULL,'Negative','Y',TIMESTAMP '2024-01-22 09:15:00',TIMESTAMP '2024-01-22 11:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (10,10,'CMP','80053','Normal',NULL,NULL,'Panel','N',TIMESTAMP '2024-01-23 11:45:00',TIMESTAMP '2024-01-23 16:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (11,11,'CBC','85025','Elevated WBC',15.2,'K/uL','4.5-11.0','Y',TIMESTAMP '2024-01-24 08:15:00',TIMESTAMP '2024-01-24 12:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (12,12,'Lipid Panel','80061','210',210,'mg/dL','<200','Y',TIMESTAMP '2024-01-25 15:15:00',TIMESTAMP '2024-01-25 19:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (13,13,'Lipid Panel','80061','176',176,'mg/dL','<200','N',TIMESTAMP '2024-02-01 09:15:00',TIMESTAMP '2024-02-01 14:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (15,15,'CBC','85025','Normal',5.9,'K/uL','4.5-11.0','N',TIMESTAMP '2024-02-03 11:15:00',TIMESTAMP '2024-02-03 15:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (17,17,'PT/INR','85610','1.0',1.0,'INR','0.8-1.2','N',TIMESTAMP '2024-02-06 13:45:00',TIMESTAMP '2024-02-06 17:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (19,19,'COVID-19 PCR','87635','Negative',NULL,NULL,'Negative','N',TIMESTAMP '2024-02-08 14:30:00',TIMESTAMP '2024-02-09 08:00:00','LabCorp Regional');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (20,20,'TSH','84443','5.6',5.6,'mIU/L','0.4-4.0','Y',TIMESTAMP '2024-02-09 10:15:00',TIMESTAMP '2024-02-09 14:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (1,21,'CMP','80053','Normal',NULL,NULL,'Panel','N',TIMESTAMP '2024-03-01 09:15:00',TIMESTAMP '2024-03-01 13:00:00','QuestPath Labs');
INSERT INTO lab_results (patient_id, appointment_id, test_name, test_code, result_value, result_numeric, unit, reference_range, abnormal_flag, collected_at, resulted_at, lab_name) VALUES (3,23,'UA','81001','Normal',NULL,NULL,'Negative','N',TIMESTAMP '2024-03-03 10:30:00',TIMESTAMP '2024-03-03 14:00:00','LabCorp Regional');

INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (1,1,'BlueCross BlueShield','POL-55001',DATE '2024-01-12',DATE '2024-01-10','I10','99213',220.00,180.00,150.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (2,2,'Aetna','POL-55002',DATE '2024-01-13',DATE '2024-01-11','I25.10','99214',450.00,380.00,320.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (3,3,'Cigna','POL-55003',DATE '2024-01-14',DATE '2024-01-12','Z00.00','99392',150.00,150.00,150.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (4,4,'UnitedHealth','POL-55004',DATE '2024-01-17',DATE '2024-01-15','G47.00','99214',300.00,250.00,0.00,'DENIED');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (5,5,'Humana','POL-55005',DATE '2024-01-18',DATE '2024-01-16','M54.5','20610',600.00,500.00,400.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (6,6,'Molina Healthcare','POL-55006',DATE '2024-01-19',DATE '2024-01-17','E11.9','99214',280.00,240.00,240.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (7,7,'Kaiser Permanente','POL-55007',DATE '2024-01-20',DATE '2024-01-18','J06.9','99213',150.00,120.00,120.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (8,8,'BlueCross BlueShield','POL-55008',DATE '2024-01-21',DATE '2024-01-19','F41.1','99214',200.00,170.00,0.00,'PENDING');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (9,9,'Aetna','POL-55009',DATE '2024-01-24',DATE '2024-01-22','N39.0','81001',90.00,75.00,75.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (10,10,'Cigna','POL-55010',DATE '2024-01-25',DATE '2024-01-23','K21.0','99214',260.00,220.00,220.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (11,11,'UnitedHealth','POL-55011',DATE '2024-01-26',DATE '2024-01-24','J18.9','99215',700.00,600.00,500.00,'APPEALED');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (12,12,'Humana','POL-55012',DATE '2024-01-27',DATE '2024-01-25','I10','99213',220.00,180.00,180.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (13,13,'Molina Healthcare','POL-55013',DATE '2024-02-03',DATE '2024-02-01','E78.5','99214',180.00,150.00,0.00,'SUBMITTED');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (15,15,'Kaiser Permanente','POL-55015',DATE '2024-02-05',DATE '2024-02-03','Z00.00','99392',150.00,150.00,150.00,'PAID');
INSERT INTO insurance_claims (patient_id, appointment_id, insurance_co, policy_number, claim_date, service_date, diagnosis_code, procedure_code, billed_amount, allowed_amount, paid_amount, status) VALUES (17,17,'BlueCross BlueShield','POL-55017',DATE '2024-02-08',DATE '2024-02-06','M54.5','99214',240.00,200.00,200.00,'PAID');

INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (1,1,'Lisinopril','NDC-11101','10mg','Once daily',30,3,DATE '2024-01-10',DATE '2025-01-10','Main St Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (2,2,'Atorvastatin','NDC-11102','20mg','Once daily',30,5,DATE '2024-01-11',DATE '2025-01-11','Riverside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (4,4,'Gabapentin','NDC-11104','300mg','Three times daily',90,2,DATE '2024-01-15',DATE '2024-07-15','Main St Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (5,5,'Metoprolol','NDC-11105','25mg','Twice daily',60,3,DATE '2024-01-16',DATE '2024-10-16','Lakeside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (6,6,'Metformin','NDC-11106','500mg','Twice daily',60,5,DATE '2024-01-17',DATE '2025-01-17','Main St Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (7,7,'Amoxicillin','NDC-11107','500mg','Three times daily',21,0,DATE '2024-01-18',DATE '2024-02-01','Riverside Pharmacy','FILLED');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (8,8,'Sertraline','NDC-11108','50mg','Once daily',30,3,DATE '2024-01-19',DATE '2025-01-19','Main St Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (9,9,'Azithromycin','NDC-11109','250mg','Once daily',6,0,DATE '2024-01-22',DATE '2024-02-05','Riverside Pharmacy','FILLED');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (10,10,'Omeprazole','NDC-11110','20mg','Once daily',30,5,DATE '2024-01-23',DATE '2025-01-23','Lakeside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (11,11,'Azithromycin','NDC-11111','250mg','Once daily',6,0,DATE '2024-01-24',DATE '2024-02-07','Main St Pharmacy','FILLED');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (12,12,'Lisinopril','NDC-11112','10mg','Once daily',30,3,DATE '2024-01-25',DATE '2025-01-25','Riverside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (13,1,'Atorvastatin','NDC-11113','40mg','Once daily',30,5,DATE '2024-02-01',DATE '2025-02-01','Main St Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (16,4,'Sumatriptan','NDC-11116','50mg','As needed',9,2,DATE '2024-02-05',DATE '2024-08-05','Lakeside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (18,6,'Metformin','NDC-11118','500mg','Twice daily',60,5,DATE '2024-02-07',DATE '2025-02-07','Main St Pharmacy','CANCELLED');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (20,8,'Sertraline','NDC-11120','25mg','Once daily',30,3,DATE '2024-02-09',DATE '2025-02-09','Riverside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (3,3,'Amoxicillin','NDC-11103','250mg','Twice daily',14,0,DATE '2024-01-12',DATE '2024-01-26','Lakeside Pharmacy','EXPIRED');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (14,2,'Ondansetron','NDC-11114','8mg','As needed',10,2,DATE '2024-02-02',DATE '2024-08-02','Riverside Pharmacy','ACTIVE');
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_date, expiry_date, pharmacy, status) VALUES (19,7,'Hydrocortisone Cream','NDC-11119','1%','Twice daily',1,1,DATE '2024-02-08',DATE '2024-08-08','Main St Pharmacy','ACTIVE');

COMMIT;

PROMPT --- Healthcare data loaded (20 patients, 12 providers, 30 appointments, 20 diagnoses, 20 lab_results, 15 insurance_claims, 20 prescriptions) ---

/* ============================================================================
   SECTION 3 (cont.): SAMPLE DATA -- E-commerce
   Insert order: categories -> products -> customers -> orders ->
                 order_items -> reviews -> cart_events
   category_id 9 and 10 are subcategories (parent_id references 1 and 2,
   both already inserted earlier in this same statement batch).
   ============================================================================ */

PROMPT --- Loading data: E-commerce ---

INSERT INTO categories (name, parent_id, description, active) VALUES ('Electronics',NULL,'Consumer electronics and gadgets',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Clothing',NULL,'Apparel for all ages',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Home & Garden',NULL,'Home goods and outdoor living',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Sports',NULL,'Sporting goods and fitness equipment',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Books',NULL,'Print and digital books',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Toys & Games',NULL,'Toys, games, and puzzles',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Beauty',NULL,'Cosmetics and personal care',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Automotive',NULL,'Car parts and accessories',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Laptops & Computers',1,'Laptops, desktops, and accessories',1);
INSERT INTO categories (name, parent_id, description, active) VALUES ('Men''s Apparel',2,'Clothing for men',1);

INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100001','TechPro 15" Laptop 16GB/512GB',9,'Lightweight laptop for work and play',899.99,540.00,1.800,120,15,'TechPro','1');
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100002','TechPro Wireless Mouse',1,'Ergonomic wireless mouse',19.99,7.50,0.120,500,50,'TechPro',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100003','StyleMax Men''s Denim Jacket',10,'Classic denim jacket',64.99,28.00,0.900,200,20,'StyleMax',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100004','HomeFirst Non-Stick Cookware Set',3,'10-piece non-stick cookware set',129.99,55.00,6.500,80,10,'HomeFirst',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100005','SportEdge Yoga Mat',4,'Non-slip yoga mat, 6mm',24.99,9.00,1.100,300,30,'SportEdge',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100006','NaturePure Vitamin C Serum',7,'30ml facial serum',22.50,6.00,0.080,400,40,'NaturePure',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100007','UrbanGear Backpack',1,'Water-resistant 25L backpack',49.99,18.00,0.700,150,15,'UrbanGear',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100008','ClassicLine Hardcover Novel Set',5,'Boxed set of 3 classic novels',39.99,15.00,1.400,90,10,'ClassicLine',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100009','ProBrand Building Blocks 500pc',6,'Compatible building block set',34.99,14.00,1.600,220,20,'ProBrand',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100010','EcoChoice Car Floor Mats',8,'All-weather rubber floor mats',54.99,22.00,3.200,140,15,'EcoChoice',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100011','PremiumPlus Bluetooth Headphones',1,'Over-ear noise cancelling headphones',149.99,60.00,0.300,180,20,'PremiumPlus',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100012','TechPro 27" 4K Monitor',9,'4K UHD IPS monitor',349.99,190.00,5.400,60,10,'TechPro',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100013','StyleMax Women''s Running Shoes',2,'Lightweight running shoes',79.99,32.00,0.600,240,25,'StyleMax',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100014','HomeFirst Memory Foam Pillow',3,'Contour memory foam pillow',34.99,12.00,1.200,260,25,'HomeFirst',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100015','SportEdge Adjustable Dumbbell Set',4,'5-50 lb adjustable dumbbells',249.99,120.00,25.000,40,5,'SportEdge',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100016','NaturePure Shampoo & Conditioner Set',7,'Sulfate-free hair care duo',18.99,5.50,0.900,350,35,'NaturePure',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100017','UrbanGear Insulated Water Bottle',4,'32oz stainless steel bottle',22.99,7.00,0.400,400,40,'UrbanGear',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100018','ClassicLine Cookbook: Everyday Meals',5,'250 recipes for home cooks',24.99,9.00,0.700,110,10,'ClassicLine',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100019','ProBrand Remote Control Car',6,'RC car with rechargeable battery',44.99,18.00,1.300,150,15,'ProBrand',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100020','EcoChoice Dash Cam',8,'1080p dashboard camera',59.99,24.00,0.250,100,10,'EcoChoice',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100021','TechPro Mechanical Keyboard',1,'RGB backlit mechanical keyboard',89.99,35.00,0.950,170,15,'TechPro',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100022','StyleMax Men''s Chino Pants',10,'Slim fit chino trousers',44.99,17.00,0.500,220,20,'StyleMax',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100023','HomeFirst LED Desk Lamp',3,'Dimmable LED lamp with USB port',29.99,11.00,0.850,190,20,'HomeFirst',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100024','PremiumPlus Smartwatch',1,'Fitness tracking smartwatch',199.99,85.00,0.060,130,15,'PremiumPlus',1);
INSERT INTO products (sku, name, category_id, description, unit_price, cost_price, weight_kg, stock_qty, reorder_level, brand, active) VALUES ('SKU-100025','SportEdge Foam Roller',4,'High-density recovery foam roller',17.99,6.00,0.450,260,25,'SportEdge',1);

INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Olivia','Turner','olivia.turner@example.com','555-401-3001',DATE '1992-04-15','F','12 Market St','Austin','TX','78701','US',1250,'GOLD');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Ethan','Brooks','ethan.brooks@example.com','555-401-3002',DATE '1988-08-22','M','45 Commerce Ave','Denver','CO','80202','US',320,'SILVER');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Ava','Martinez','ava.martinez@example.com','555-401-3003',DATE '1995-01-05','F','78 Trade Blvd','Columbus','OH','43004','US',75,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Liam','Foster','liam.foster@example.com','555-401-3004',DATE '1979-11-30','M','5 Union St','Seattle','WA','98101','US',4200,'PLATINUM');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Sophia','Nakamura','sophia.nakamura@example.com','555-401-3005',DATE '1991-06-18','F','203 Harbor Rd','Miami','FL','33101','US',890,'GOLD');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Noah','Reyes','noah.reyes@example.com','555-401-3006',DATE '1984-02-27','M','99 Bayview Dr','San Jose','CA','95101','US',150,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Isabella','Coleman','isabella.coleman@example.com','555-401-3007',DATE '1998-09-09','F','67 Lakeshore Ln','Boston','MA','02108','US',60,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Mason','Hughes','mason.hughes@example.com','555-401-3008',DATE '1975-12-14','M','410 Riverside Dr','Chicago','IL','60601','US',7800,'DIAMOND');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Mia','Jansen','mia.jansen@example.com','555-401-3009',DATE '2000-03-03','F','8 Meadow Ct','Portland','OR','97201','US',20,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Lucas','Fitzgerald','lucas.fitzgerald@example.com','555-401-3010',DATE '1966-07-21','M','156 Summit Ave','Phoenix','AZ','85001','US',3100,'PLATINUM');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Charlotte','Diaz','charlotte.diaz@example.com','555-401-3011',DATE '1993-10-11','F','29 Garden St','San Diego','CA','92101','US',540,'SILVER');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Benjamin','Ricci','benjamin.ricci@example.com','555-401-3012',DATE '1987-05-19','M','340 Highland Ave','Atlanta','GA','30301','US',95,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Amelia','Voss','amelia.voss@example.com','555-401-3013',DATE '1996-08-02','F','17 Willow Bend','Dallas','TX','75201','US',210,'SILVER');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Henry','Blackwood','henry.blackwood@example.com','555-401-3014',DATE '1971-01-25','M','62 Northgate Rd','Minneapolis','MN','55401','US',6100,'DIAMOND');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Evelyn','Sato','evelyn.sato@example.com','555-401-3015',DATE '1999-04-08','F','5 Crescent Way','Charlotte','NC','28201','US',40,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Jack','Okonkwo','jack.okonkwo@example.com','555-401-3016',DATE '1982-11-16','M','88 Prairie Ln','Houston','TX','77002','US',1800,'GOLD');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Harper','Lindqvist','harper.lindqvist@example.com','555-401-3017',DATE '1990-02-14','F','33 Brookfield Dr','Tampa','FL','33601','US',700,'SILVER');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Sebastian','Novak','sebastian.novak@example.com','555-401-3018',DATE '1978-06-06','M','120 Fairview St','Detroit','MI','48201','US',2500,'GOLD');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('Zoe','Andersen','zoe.andersen@example.com','555-401-3019',DATE '2002-09-29','F','15 Meridian Pl','Newark','NJ','07101','US',10,'BRONZE');
INSERT INTO customers (first_name, last_name, email, phone, dob, gender, address, city, state, zip, country, loyalty_points, tier) VALUES ('William','Castillo','william.castillo@example.com','555-401-3020',DATE '1969-03-23','M','501 Liberty Ave','Kansas City','MO','64101','US',5200,'PLATINUM');

INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (1,TIMESTAMP '2024-02-01 10:15:00','DELIVERED','STANDARD','12 Market St, Austin, TX 78701',919.98,73.60,0.00,0.00,993.58,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (2,TIMESTAMP '2024-02-02 14:20:00','DELIVERED','EXPEDITED','45 Commerce Ave, Denver, CO 80202',64.99,5.20,9.99,0.00,80.18,'DEBIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (3,TIMESTAMP '2024-02-03 09:05:00','SHIPPED','STANDARD','78 Trade Blvd, Columbus, OH 43004',129.99,10.40,0.00,0.00,140.39,'PAYPAL');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (4,TIMESTAMP '2024-02-04 16:45:00','DELIVERED','OVERNIGHT','5 Union St, Seattle, WA 98101',349.99,28.00,19.99,20.00,377.98,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (5,TIMESTAMP '2024-02-05 11:30:00','DELIVERED','STANDARD','203 Harbor Rd, Miami, FL 33101',47.49,3.80,5.99,0.00,57.28,'APPLE_PAY');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (6,TIMESTAMP '2024-02-06 13:10:00','CANCELLED','STANDARD','99 Bayview Dr, San Jose, CA 95101',149.99,12.00,0.00,0.00,161.99,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (7,TIMESTAMP '2024-02-07 08:50:00','DELIVERED','STORE_PICKUP','67 Lakeshore Ln, Boston, MA 02108',39.99,3.20,0.00,0.00,43.19,'GIFT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (8,TIMESTAMP '2024-02-08 17:00:00','DELIVERED','SAME_DAY','410 Riverside Dr, Chicago, IL 60601',599.98,48.00,14.99,60.00,602.97,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (9,TIMESTAMP '2024-02-09 12:25:00','PENDING','STANDARD','8 Meadow Ct, Portland, OR 97201',34.99,2.80,5.99,0.00,43.78,'DEBIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (10,TIMESTAMP '2024-02-10 15:40:00','DELIVERED','EXPEDITED','156 Summit Ave, Phoenix, AZ 85001',54.99,4.40,9.99,0.00,69.38,'GOOGLE_PAY');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (11,TIMESTAMP '2024-02-11 09:15:00','DELIVERED','STANDARD','29 Garden St, San Diego, CA 92101',149.99,12.00,0.00,0.00,161.99,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (12,TIMESTAMP '2024-02-12 10:05:00','SHIPPED','STANDARD','340 Highland Ave, Atlanta, GA 30301',349.99,28.00,0.00,0.00,377.99,'PAYPAL');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (13,TIMESTAMP '2024-02-13 14:30:00','DELIVERED','STANDARD','17 Willow Bend, Dallas, TX 75201',79.99,6.40,5.99,0.00,92.38,'DEBIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (14,TIMESTAMP '2024-02-14 11:00:00','DELIVERED','OVERNIGHT','62 Northgate Rd, Minneapolis, MN 55401',249.99,20.00,19.99,25.00,264.98,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (15,TIMESTAMP '2024-02-15 09:45:00','RETURNED','STANDARD','5 Crescent Way, Charlotte, NC 28201',18.99,1.50,5.99,0.00,26.48,'PAYPAL');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (16,TIMESTAMP '2024-02-16 13:20:00','DELIVERED','STANDARD','88 Prairie Ln, Houston, TX 77002',89.99,7.20,0.00,0.00,97.19,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (17,TIMESTAMP '2024-02-17 16:10:00','DELIVERED','EXPEDITED','33 Brookfield Dr, Tampa, FL 33601',44.99,3.60,9.99,0.00,58.58,'APPLE_PAY');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (18,TIMESTAMP '2024-02-18 10:35:00','CONFIRMED','STANDARD','120 Fairview St, Detroit, MI 48201',29.99,2.40,5.99,0.00,38.38,'DEBIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (19,TIMESTAMP '2024-02-19 12:55:00','DELIVERED','STANDARD','15 Meridian Pl, Newark, NJ 07101',199.99,16.00,0.00,15.00,200.99,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (20,TIMESTAMP '2024-02-20 08:20:00','DELIVERED','SAME_DAY','501 Liberty Ave, Kansas City, MO 64101',17.99,1.40,14.99,0.00,34.38,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (1,TIMESTAMP '2024-03-01 09:00:00','DELIVERED','STANDARD','12 Market St, Austin, TX 78701',89.99,7.20,0.00,0.00,97.19,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (4,TIMESTAMP '2024-03-02 15:30:00','DELIVERED','EXPEDITED','5 Union St, Seattle, WA 98101',199.99,16.00,9.99,0.00,225.98,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (8,TIMESTAMP '2024-03-03 11:15:00','SHIPPED','STANDARD','410 Riverside Dr, Chicago, IL 60601',44.99,3.60,5.99,0.00,54.58,'CREDIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (14,TIMESTAMP '2024-03-04 14:00:00','PENDING','STANDARD','62 Northgate Rd, Minneapolis, MN 55401',34.99,2.80,5.99,0.00,43.78,'DEBIT_CARD');
INSERT INTO orders (customer_id, order_date, status, shipping_method, shipping_addr, subtotal, tax_amount, shipping_cost, discount_amount, total_amount, payment_method) VALUES (20,TIMESTAMP '2024-03-05 10:40:00','DELIVERED','STANDARD','501 Liberty Ave, Kansas City, MO 64101',22.99,1.80,5.99,0.00,30.78,'CREDIT_CARD');

INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (1,1,1,899.99,0,899.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (1,2,1,19.99,0,19.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (2,3,1,64.99,0,64.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (3,4,1,129.99,0,129.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (4,12,1,349.99,0,349.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (5,5,1,24.99,0,24.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (5,6,1,22.50,0,22.50,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (6,11,1,149.99,0,149.99,1);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (7,8,1,39.99,0,39.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (8,15,1,249.99,0,249.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (8,17,1,22.99,0,22.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (8,25,1,17.99,0,17.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (8,16,1,18.99,0,18.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (9,9,1,34.99,0,34.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (10,10,1,54.99,0,54.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (11,11,1,149.99,0,149.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (12,12,1,349.99,0,349.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (13,13,1,79.99,0,79.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (14,15,1,249.99,10,224.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (15,16,1,18.99,0,18.99,1);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (16,21,1,89.99,0,89.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (17,22,1,44.99,0,44.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (18,23,1,29.99,0,29.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (19,24,1,199.99,7.5,184.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (20,25,1,17.99,0,17.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (21,21,1,89.99,0,89.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (22,24,1,199.99,0,199.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (23,22,1,44.99,0,44.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (24,9,1,34.99,0,34.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (25,17,1,22.99,0,22.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (2,7,1,49.99,0,49.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (3,14,1,34.99,0,34.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (4,19,1,44.99,0,44.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (6,20,1,59.99,0,59.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (9,18,1,24.99,0,24.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (11,13,1,79.99,0,79.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (12,2,2,19.99,0,39.98,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (16,5,1,24.99,0,24.99,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (19,6,1,22.50,0,22.50,0);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount_pct, line_total, returned) VALUES (23,10,1,54.99,0,54.99,0);

INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (1,1,5,'Excellent laptop','Fast, light, and the battery lasts all day.',24,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (3,2,4,'Great jacket','Good quality denim, fits true to size.',8,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (4,3,5,'Love this cookware','Non-stick coating works great, easy to clean.',15,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (12,4,4,'Sharp display','Colors are vivid, good value for a 4K monitor.',12,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (5,5,3,'Decent mat','A bit thin but does the job.',3,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (11,6,2,'Not for me','Ear cups felt uncomfortable after an hour.',5,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (8,7,5,'Beautiful set','These classics look great on my shelf.',6,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (15,8,5,'Solid dumbbells','Adjustable mechanism is smooth and sturdy.',19,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (9,9,4,'Kids love it','Good quality blocks, compatible with other brands.',7,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (10,10,4,'Fits well','Mats fit our SUV perfectly.',4,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (11,11,5,'Great sound','Noise cancelling is impressive for the price.',22,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (12,12,3,'Good but pricey','Picture quality is nice, wish it were cheaper.',2,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (13,13,5,'Comfortable shoes','Great for long runs, true to size.',10,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (15,14,4,'Great value','Adjustable weights save a lot of space.',9,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (16,15,1,'Broke quickly','Pump on the bottle stopped working after a week.',11,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (21,16,5,'Great typing feel','Keys are satisfying and backlight looks great.',13,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (22,17,4,'Comfortable fit','Good everyday pants, holds up well.',3,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (23,18,4,'Nice lamp','Good brightness levels, USB port is handy.',5,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (24,19,5,'Love the smartwatch','Battery lasts days, tracks workouts well.',17,1);
INSERT INTO reviews (product_id, customer_id, rating, title, body, helpful_votes, verified_purchase) VALUES (25,20,4,'Good roller','Firm enough for deep tissue work.',6,1);

INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (1,'sess-a1001',1,'VIEW',1,'https://shop.example.com/products/sku-100001','203.0.113.10',TIMESTAMP '2024-01-30 08:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (1,'sess-a1001',1,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.10',TIMESTAMP '2024-01-30 08:02:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (1,'sess-a1001',2,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.10',TIMESTAMP '2024-01-30 08:03:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (1,'sess-a1001',1,'PURCHASE',1,'https://shop.example.com/checkout/confirm','203.0.113.10',TIMESTAMP '2024-02-01 10:15:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (2,'sess-b2002',3,'VIEW',1,'https://shop.example.com/products/sku-100003','203.0.113.11',TIMESTAMP '2024-02-02 14:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (2,'sess-b2002',3,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.11',TIMESTAMP '2024-02-02 14:05:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (3,'sess-c3003',4,'VIEW',1,'https://shop.example.com/products/sku-100004','203.0.113.12',TIMESTAMP '2024-02-03 08:55:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (3,'sess-c3003',4,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.12',TIMESTAMP '2024-02-03 09:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (6,'sess-d4004',11,'VIEW',1,'https://shop.example.com/products/sku-100011','203.0.113.13',TIMESTAMP '2024-02-06 13:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (6,'sess-d4004',11,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.13',TIMESTAMP '2024-02-06 13:05:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (6,'sess-d4004',11,'REMOVE_FROM_CART',1,'https://shop.example.com/cart/remove','203.0.113.13',TIMESTAMP '2024-02-06 13:08:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (6,'sess-d4004',11,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.13',TIMESTAMP '2024-02-06 13:09:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (6,'sess-d4004',11,'PURCHASE',1,'https://shop.example.com/checkout/confirm','203.0.113.13',TIMESTAMP '2024-02-06 13:10:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (NULL,'sess-guest001',9,'VIEW',1,'https://shop.example.com/products/sku-100009','203.0.113.20',TIMESTAMP '2024-02-08 09:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (NULL,'sess-guest001',9,'SEARCH',NULL,'https://shop.example.com/search?q=blocks','203.0.113.20',TIMESTAMP '2024-02-08 09:01:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (8,'sess-e5005',15,'VIEW',1,'https://shop.example.com/products/sku-100015','203.0.113.14',TIMESTAMP '2024-02-08 16:50:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (8,'sess-e5005',15,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.14',TIMESTAMP '2024-02-08 16:55:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (8,'sess-e5005',17,'ADD_TO_CART',1,'https://shop.example.com/cart/add','203.0.113.14',TIMESTAMP '2024-02-08 16:57:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (8,'sess-e5005',15,'PURCHASE',1,'https://shop.example.com/checkout/confirm','203.0.113.14',TIMESTAMP '2024-02-08 17:00:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (15,'sess-f6006',16,'VIEW',1,'https://shop.example.com/products/sku-100016','203.0.113.15',TIMESTAMP '2024-02-15 09:40:00');
INSERT INTO cart_events (customer_id, session_id, product_id, event_type, quantity, page_url, ip_address, event_ts) VALUES (15,'sess-f6006',16,'WISHLIST',1,'https://shop.example.com/wishlist/add','203.0.113.15',TIMESTAMP '2024-02-15 09:42:00');

COMMIT;

PROMPT --- E-commerce data loaded (10 categories, 25 products, 20 customers, 25 orders, 40 order_items, 20 reviews, 20 cart_events) ---

/* ============================================================================
   SECTION 3 (cont.): SAMPLE DATA -- Finance
   Insert order: accounts -> transactions -> loans -> fraud_alerts ->
                 market_prices -> audit_log
   ============================================================================ */

PROMPT --- Loading data: Finance ---

INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700001','CHECKING','Olivia Turner','olivia.turner@example.com','021000021','USD',5230.44,NULL,'ACTIVE',DATE '2018-03-11','Austin Downtown');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700002','SAVINGS','Olivia Turner','olivia.turner@example.com','021000021','USD',18240.12,NULL,'ACTIVE',DATE '2018-03-11','Austin Downtown');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700003','CREDIT','Ethan Brooks','ethan.brooks@example.com','021000021','USD',-1240.55,10000.00,'ACTIVE',DATE '2019-07-22','Denver Central');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700004','CHECKING','Ava Martinez','ava.martinez@example.com','021000021','USD',870.00,NULL,'ACTIVE',DATE '2020-01-15','Columbus Main');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700005','INVESTMENT','Liam Foster','liam.foster@example.com','021000021','USD',245300.75,NULL,'ACTIVE',DATE '2015-06-01','Seattle North');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700006','CHECKING','Sophia Nakamura','sophia.nakamura@example.com','021000021','USD',3120.90,NULL,'ACTIVE',DATE '2021-02-19','Miami Beach');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700007','MONEY_MARKET','Noah Reyes','noah.reyes@example.com','021000021','USD',42100.00,NULL,'ACTIVE',DATE '2017-09-30','San Jose West');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700008','CD','Isabella Coleman','isabella.coleman@example.com','021000021','USD',15000.00,NULL,'ACTIVE',DATE '2022-04-05','Boston Financial');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700009','CHECKING','Mason Hughes','mason.hughes@example.com','021000021','USD',95400.60,NULL,'ACTIVE',DATE '2010-11-11','Chicago Loop');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700010','SAVINGS','Mia Jansen','mia.jansen@example.com','021000021','USD',210.35,NULL,'FROZEN',DATE '2023-01-09','Portland Central');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700011','CREDIT','Lucas Fitzgerald','lucas.fitzgerald@example.com','021000021','USD',-3400.20,15000.00,'ACTIVE',DATE '2013-08-17','Phoenix Central');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700012','CHECKING','Charlotte Diaz','charlotte.diaz@example.com','021000021','USD',6320.00,NULL,'ACTIVE',DATE '2019-12-02','San Diego Gaslamp');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700013','SAVINGS','Henry Blackwood','henry.blackwood@example.com','021000021','USD',88250.00,NULL,'ACTIVE',DATE '2012-05-23','Minneapolis Uptown');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch, closed_date) VALUES ('BBAN-700014','CHECKING','Sebastian Novak','sebastian.novak@example.com','021000021','USD',0.00,NULL,'CLOSED',DATE '2016-02-14','Detroit Riverfront',DATE '2023-11-30');
INSERT INTO accounts (account_number, account_type, owner_name, owner_email, routing_number, currency, balance, credit_limit, status, opened_date, branch) VALUES ('BBAN-700015','CHECKING','William Castillo','william.castillo@example.com','021000021','USD',12980.40,NULL,'ACTIVE',DATE '2014-10-08','Kansas City Plaza');

INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (1,TIMESTAMP '2024-02-01 09:12:00',TIMESTAMP '2024-02-01 09:12:00',85.32,'DEBIT','Groceries','Whole Foods Market','Austin','TX','REF-800001','Grocery purchase',5230.44,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (1,TIMESTAMP '2024-02-02 18:40:00',TIMESTAMP '2024-02-02 18:40:00',42.10,'DEBIT','Dining','Torchy''s Tacos','Austin','TX','REF-800002','Dinner',5188.34,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (1,TIMESTAMP '2024-02-05 08:00:00',TIMESTAMP '2024-02-05 08:00:00',2500.00,'CREDIT','Salary','Acme Corp Payroll','Austin','TX','REF-800003','Biweekly salary deposit',7688.34,'ACH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (2,TIMESTAMP '2024-02-06 10:00:00',TIMESTAMP '2024-02-06 10:00:00',15.20,'INTEREST','Transfer','Interest Payment',NULL,NULL,'REF-800004','Monthly interest',18255.32,'BRANCH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (3,TIMESTAMP '2024-02-07 12:30:00',TIMESTAMP '2024-02-07 12:30:00',320.55,'DEBIT','Shopping','Best Buy','Denver','CO','REF-800005','Electronics purchase',-1561.10,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (3,TIMESTAMP '2024-02-08 09:00:00',TIMESTAMP '2024-02-08 09:00:00',500.00,'CREDIT','Transfer','Payment Received',NULL,NULL,'REF-800006','Credit card payment',-1061.10,'ONLINE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (4,TIMESTAMP '2024-02-09 15:00:00',TIMESTAMP '2024-02-09 15:00:00',120.00,'DEBIT','Utilities','City Power & Light','Columbus','OH','REF-800007','Electric bill',870.00,'ACH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (5,TIMESTAMP '2024-02-10 11:15:00',TIMESTAMP '2024-02-10 11:15:00',15000.00,'CREDIT','Transfer','Brokerage Deposit',NULL,NULL,'REF-800008','Investment deposit',245300.75,'WIRE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (6,TIMESTAMP '2024-02-11 08:20:00',TIMESTAMP '2024-02-11 08:20:00',60.75,'DEBIT','Gas','Shell','Miami','FL','REF-800009','Fuel purchase',3120.90,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (7,TIMESTAMP '2024-02-12 09:45:00',TIMESTAMP '2024-02-12 09:45:00',2000.00,'WITHDRAWAL','ATM','ATM Withdrawal','San Jose','CA','REF-800010','Cash withdrawal',42100.00,'ATM','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (9,TIMESTAMP '2024-02-13 13:00:00',TIMESTAMP '2024-02-13 13:00:00',18500.00,'DEBIT','Travel','Delta Airlines','Chicago','IL','REF-800011','First class booking',95400.60,'ONLINE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (10,TIMESTAMP '2024-02-14 10:00:00',TIMESTAMP '2024-02-14 10:00:00',35.00,'FEE','Fees','Monthly Maintenance Fee',NULL,NULL,'REF-800012','Account fee',210.35,'BRANCH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (11,TIMESTAMP '2024-02-15 17:30:00',TIMESTAMP '2024-02-15 17:30:00',450.00,'DEBIT','Shopping','Macy''s','Phoenix','AZ','REF-800013','Clothing purchase',-3850.20,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (12,TIMESTAMP '2024-02-16 08:10:00',TIMESTAMP '2024-02-16 08:10:00',95.00,'DEBIT','Entertainment','AMC Theatres','San Diego','CA','REF-800014','Movie tickets',6225.00,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (13,TIMESTAMP '2024-02-17 09:00:00',TIMESTAMP '2024-02-17 09:00:00',250.00,'INTEREST','Transfer','Interest Payment',NULL,NULL,'REF-800015','Monthly interest',88250.00,'BRANCH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (15,TIMESTAMP '2024-02-18 12:40:00',TIMESTAMP '2024-02-18 12:40:00',1300.00,'DEBIT','Rent','Liberty Ave Apartments','Kansas City','MO','REF-800016','Monthly rent',12980.40,'ACH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (1,TIMESTAMP '2024-02-19 07:30:00',TIMESTAMP '2024-02-19 07:30:00',12.99,'DEBIT','Entertainment','Netflix','Austin','TX','REF-800017','Streaming subscription',7675.35,'ONLINE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (1,TIMESTAMP '2024-02-20 19:00:00',TIMESTAMP '2024-02-20 19:00:00',150.00,'DEBIT','Shopping','Target','Austin','TX','REF-800018','Household goods',7525.35,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (3,TIMESTAMP '2024-02-21 10:00:00',TIMESTAMP '2024-02-21 10:00:00',75.00,'FEE','Fees','Late Payment Fee',NULL,NULL,'REF-800019','Late fee',-1136.10,'BRANCH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (5,TIMESTAMP '2024-02-22 14:20:00',TIMESTAMP '2024-02-22 14:20:00',12500.00,'DEBIT','Transfer','Wire Out - Real Estate','Seattle','WA','REF-800020','Property down payment',232800.75,'WIRE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (9,TIMESTAMP '2024-02-23 09:15:00',TIMESTAMP '2024-02-23 09:15:00',300.00,'DEBIT','Dining','Alinea','Chicago','IL','REF-800021','Business dinner',95100.60,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (6,TIMESTAMP '2024-02-24 16:00:00',TIMESTAMP '2024-02-24 16:00:00',22.40,'DEBIT','Groceries','Publix','Miami','FL','REF-800022','Grocery purchase',3098.50,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (7,TIMESTAMP '2024-02-25 08:00:00',TIMESTAMP '2024-02-25 08:00:00',5000.00,'CREDIT','Transfer','Bonus Deposit',NULL,NULL,'REF-800023','Annual bonus',47100.00,'ACH','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (11,TIMESTAMP '2024-02-26 11:00:00',NULL,220.00,'DEBIT','Shopping','Nordstrom','Phoenix','AZ','REF-800024','Pending purchase',-4070.20,'POS','PENDING');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (12,TIMESTAMP '2024-02-27 13:30:00',TIMESTAMP '2024-02-27 13:30:00',18.75,'DEBIT','Gas','Chevron','San Diego','CA','REF-800025','Fuel purchase',6206.25,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (13,TIMESTAMP '2024-02-28 09:00:00',NULL,50000.00,'WITHDRAWAL','Transfer','Large Cash Withdrawal',NULL,NULL,'REF-800026','Large withdrawal',38250.00,'BRANCH','DECLINED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (15,TIMESTAMP '2024-03-01 07:45:00',TIMESTAMP '2024-03-01 07:45:00',65.20,'DEBIT','Healthcare','CVS Pharmacy','Kansas City','MO','REF-800027','Pharmacy copay',12915.20,'POS','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (9,TIMESTAMP '2024-03-02 10:30:00',TIMESTAMP '2024-03-02 10:30:00',25000.00,'DEBIT','Shopping','Sotheby''s Auction','Chicago','IL','REF-800028','Art purchase',70100.60,'WIRE','POSTED');
INSERT INTO transactions (account_id, txn_date, posted_date, amount, txn_type, category, merchant_name, merchant_city, merchant_state, reference_no, description, balance_after, channel, status) VALUES (2,TIMESTAMP '2024-03-03 09:00:00',TIMESTAMP '2024-03-03 09:00:00',500.00,'TRANSFER','Transfer','Transfer to Checking',NULL,NULL,'REF-800029','Internal transfer',17755.32,'ONLINE','POSTED');

INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (1,'AUTO',28000.00,5.4500,60,532.10,DATE '2022-06-01',DATE '2027-06-01',19850.00,'CURRENT','2022 Honda Accord',101);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (5,'MORTGAGE',450000.00,4.1000,360,2178.50,DATE '2019-04-15',DATE '2049-04-15',398250.00,'CURRENT','412 Union St, Seattle WA',102);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (9,'PERSONAL',15000.00,8.9900,36,476.80,DATE '2023-02-10',DATE '2026-02-10',9800.00,'CURRENT',NULL,103);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (3,'STUDENT',42000.00,6.2000,120,470.25,DATE '2016-09-01',DATE '2026-09-01',12300.00,'DELINQUENT',NULL,101);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (13,'HELOC',75000.00,7.1500,180,675.00,DATE '2021-11-20',DATE '2036-11-20',61200.00,'CURRENT','88 Prairie Ln, Minneapolis MN',102);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (11,'BUSINESS',120000.00,6.7500,84,1780.00,DATE '2020-01-05',DATE '2027-01-05',54000.00,'DELINQUENT','Business equipment lien',104);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (15,'AUTO',22000.00,4.9000,48,505.60,DATE '2023-05-15',DATE '2027-05-15',15200.00,'CURRENT','2023 Toyota RAV4',101);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (6,'PERSONAL',8000.00,9.5000,24,368.40,DATE '2022-08-01',DATE '2024-08-01',0.00,'PAID_OFF',NULL,103);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (12,'MORTGAGE',310000.00,4.7500,360,1617.30,DATE '2018-03-01',DATE '2048-03-01',279400.00,'CURRENT','29 Garden St, San Diego CA',102);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (7,'AUTO',31000.00,5.2000,60,588.90,DATE '2021-10-10',DATE '2026-10-10',9800.00,'CURRENT','2021 Ford F-150',101);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (NULL,'PERSONAL',5000.00,10.5000,12,441.20,DATE '2023-09-01',DATE '2024-09-01',1200.00,'IN_REVIEW',NULL,104);
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, origination_date, maturity_date, outstanding_bal, status, collateral, officer_id) VALUES (2,'STUDENT',28000.00,5.8000,120,306.00,DATE '2015-08-15',DATE '2025-08-15',3100.00,'DEFAULT',NULL,103);

INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status, resolved_by, resolved_at) VALUES (9,9,TIMESTAMP '2024-02-13 13:05:00','HIGH_AMOUNT',88.50,'LARGE_TXN','Large travel purchase flagged for review','RESOLVED','fraud.team@ebfinance.example.com',TIMESTAMP '2024-02-14 09:00:00');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status, resolved_by, resolved_at) VALUES (20,5,TIMESTAMP '2024-02-22 14:25:00','HIGH_AMOUNT',72.00,'LARGE_TXN','Large wire transfer flagged','RESOLVED','fraud.team@ebfinance.example.com',TIMESTAMP '2024-02-23 08:00:00');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (28,9,TIMESTAMP '2024-03-02 10:35:00','HIGH_AMOUNT',95.20,'LARGE_TXN','Large art auction purchase flagged','OPEN');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (25,13,TIMESTAMP '2024-02-28 09:05:00','MULTIPLE_COUNTRIES',91.00,'GEO_MISMATCH','Large withdrawal attempt declined','ESCALATED');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (11,3,TIMESTAMP '2024-02-07 12:35:00','RAPID_SUCCESSION',55.00,'VELOCITY','Multiple purchases in short window','FALSE_POSITIVE');
INSERT INTO fraud_alerts (account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (11,TIMESTAMP '2024-02-15 17:35:00','CARD_TESTING',63.40,'CARD_NOT_PRESENT','Suspicious small authorizations preceding large purchase','OPEN');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status, resolved_by, resolved_at) VALUES (12,6,TIMESTAMP '2024-02-16 08:15:00','MERCHANT_RISK',40.00,'UNUSUAL_PATTERN','Merchant category mismatch','RESOLVED','fraud.team@ebfinance.example.com',TIMESTAMP '2024-02-16 12:00:00');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (24,11,TIMESTAMP '2024-02-26 11:05:00','ACCOUNT_TAKEOVER',80.00,'UNUSUAL_PATTERN','Purchase from new device flagged','OPEN');
INSERT INTO fraud_alerts (txn_id, account_id, alert_date, rule_name, risk_score, alert_type, description, status, resolved_by, resolved_at) VALUES (27,9,TIMESTAMP '2024-03-02 10:40:00','HIGH_AMOUNT',65.00,'LARGE_TXN','Business dinner amount reviewed','RESOLVED','fraud.team@ebfinance.example.com',TIMESTAMP '2024-03-02 15:00:00');
INSERT INTO fraud_alerts (account_id, alert_date, rule_name, risk_score, alert_type, description, status) VALUES (13,TIMESTAMP '2024-02-28 09:10:00','RAPID_SUCCESSION',97.50,'VELOCITY','Repeated large withdrawal attempts','ESCALATED');

INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('AAPL',DATE '2024-02-01',185.20,187.10,184.50,186.75,186.75,52000000,2900000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('AAPL',DATE '2024-02-02',186.90,189.30,186.10,188.45,188.45,48000000,2925000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('MSFT',DATE '2024-02-01',405.10,409.80,403.20,408.50,408.50,21000000,3030000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('MSFT',DATE '2024-02-02',408.70,412.00,406.90,410.30,410.30,19500000,3050000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('GOOGL',DATE '2024-02-01',142.50,144.20,141.80,143.90,143.90,25000000,1800000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('AMZN',DATE '2024-02-01',168.30,171.00,167.50,170.10,170.10,38000000,1760000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('TSLA',DATE '2024-02-01',188.20,192.50,185.00,190.40,190.40,95000000,605000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('TSLA',DATE '2024-02-02',190.80,195.00,188.30,193.70,193.70,88000000,616000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('META',DATE '2024-02-01',468.10,474.00,465.50,472.30,472.30,15500000,1200000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('NVDA',DATE '2024-02-01',615.40,628.90,610.20,625.75,625.75,42000000,1540000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('NVDA',DATE '2024-02-02',627.00,645.20,624.10,640.50,640.50,45000000,1575000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('JPM',DATE '2024-02-01',172.30,174.80,171.20,173.90,173.90,9800000,500000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('BAC',DATE '2024-02-01',33.20,33.90,32.90,33.60,33.60,42000000,265000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('WMT',DATE '2024-02-01',163.40,165.00,162.70,164.50,164.50,7200000,441000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('JNJ',DATE '2024-02-01',158.90,160.10,157.80,159.30,159.30,6100000,383000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('PG',DATE '2024-02-01',148.60,149.90,147.90,149.20,149.20,5900000,352000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('V',DATE '2024-02-01',268.10,271.50,267.20,270.30,270.30,6300000,555000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('MA',DATE '2024-02-01',445.20,449.80,443.00,448.10,448.10,2700000,420000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('UNH',DATE '2024-02-01',520.30,527.90,518.10,525.60,525.60,3100000,485000000000.00);
INSERT INTO market_prices (ticker, price_date, open_price, high_price, low_price, close_price, adj_close, volume, market_cap) VALUES ('AAPL',DATE '2024-02-05',187.00,188.50,185.90,187.80,187.80,44000000,2915000000000.00);

INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-01 09:12:05','EB_SAMPLEDB','olivia.turner','EB_SAMPLEDB','TRANSACTIONS','INSERT',1,NULL,'amount=85.32','198.51.100.5','WebPortal','sid-90001');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-05 08:00:10','EB_SAMPLEDB','batch_job','EB_SAMPLEDB','TRANSACTIONS','INSERT',3,NULL,'amount=2500.00','198.51.100.9','BatchJob','sid-90002');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-10 11:16:00','EB_SAMPLEDB','liam.foster','EB_SAMPLEDB','ACCOUNTS','UPDATE',5,'balance=230300.75','balance=245300.75','198.51.100.14','MobileApp','sid-90003');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-13 13:06:00','EB_SAMPLEDB','fraud_engine','EB_SAMPLEDB','FRAUD_ALERTS','INSERT',1,NULL,'status=OPEN','198.51.100.20','AdminTool','sid-90004');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-14 09:00:05','EB_SAMPLEDB','fraud.team','EB_SAMPLEDB','FRAUD_ALERTS','UPDATE',1,'status=OPEN','status=RESOLVED','198.51.100.20','AdminTool','sid-90005');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-16 08:15:10','EB_SAMPLEDB','api_gateway','EB_SAMPLEDB','TRANSACTIONS','INSERT',22,NULL,'amount=95.00','198.51.100.30','APIGateway','sid-90006');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-21 10:00:20','EB_SAMPLEDB','batch_job','EB_SAMPLEDB','LOANS','UPDATE',4,'status=CURRENT','status=DELINQUENT','198.51.100.9','BatchJob','sid-90007');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-22 14:26:00','EB_SAMPLEDB','fraud_engine','EB_SAMPLEDB','FRAUD_ALERTS','INSERT',2,NULL,'status=OPEN','198.51.100.20','AdminTool','sid-90008');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-02-28 09:06:00','EB_SAMPLEDB','fraud_engine','EB_SAMPLEDB','TRANSACTIONS','UPDATE',25,'status=PENDING','status=DECLINED','198.51.100.20','AdminTool','sid-90009');
INSERT INTO audit_log (log_ts, db_user, app_user, schema_name, table_name, operation, row_id, old_values, new_values, ip_address, app_name, session_id) VALUES (TIMESTAMP '2024-03-02 10:41:00','EB_SAMPLEDB','mason.hughes','EB_SAMPLEDB','ACCOUNTS','UPDATE',9,'balance=95100.60','balance=70100.60','198.51.100.44','WebPortal','sid-90010');

COMMIT;

PROMPT --- Finance data loaded (15 accounts, 30 transactions, 12 loans, 10 fraud_alerts, 20 market_prices, 10 audit_log) ---

/* ============================================================================
   SECTION 3 (cont.): SAMPLE DATA -- Manufacturing / IoT
   Insert order: machines -> sensors -> sensor_readings ->
                 production_batches -> quality_checks
   ============================================================================ */

PROMPT --- Loading data: Manufacturing / IoT ---

INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0001','CNC Mill Alpha','CNC Mill','Haas Automation','VF-2SS','SN-CNC-0001','Plant 1 - Bay A','1',DATE '2018-05-10',DATE '2024-01-15','RUNNING',500.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0002','Lathe Bravo','Lathe','Okuma','LB3000','SN-LTH-0002','Plant 1 - Bay A','1',DATE '2017-08-22','2024-01-10','RUNNING',400.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0003','Conveyor Line 1','Conveyor','Dorner','2200 Series','SN-CNV-0003','Plant 1 - Bay B','1',DATE '2015-03-01',DATE '2023-12-20','RUNNING',1200.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0004','Robot Arm Delta','Robot Arm','FANUC','M-20iD','SN-ROB-0004','Plant 1 - Bay B','1',DATE '2019-11-12',DATE '2024-01-05','RUNNING',300.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0005','3D Printer Echo','3D Printer','Stratasys','F900','SN-3DP-0005','Plant 2 - Bay A','2',DATE '2021-02-18',DATE '2023-11-30','IDLE',50.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0006','CNC Mill Foxtrot','CNC Mill','Haas Automation','VF-4','SN-CNC-0006','Plant 2 - Bay A','2',DATE '2016-06-25',DATE '2023-10-14','MAINTENANCE',550.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0007','Lathe Golf','Lathe','Mazak','QT-250','SN-LTH-0007','Plant 2 - Bay B','2',DATE '2020-01-09',DATE '2024-01-02','RUNNING',420.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0008','Conveyor Line 2','Conveyor','Dorner','2200 Series','SN-CNV-0008','Plant 2 - Bay B','2',DATE '2015-03-01',DATE '2023-12-22','RUNNING',1200.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0009','Robot Arm Hotel','Robot Arm','ABB','IRB 2600','SN-ROB-0009','Plant 1 - Bay C','1',DATE '2022-04-14',DATE '2024-01-18','RUNNING',320.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0010','3D Printer India','3D Printer','Ultimaker','S5','SN-3DP-0010','Plant 2 - Bay C','2',DATE '2020-09-30',DATE '2023-09-25','FAULT',45.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0011','CNC Mill Juliet','CNC Mill','DMG Mori','NHX 4000','SN-CNC-0011','Plant 1 - Bay C','1',DATE '2019-07-19',DATE '2024-01-11','RUNNING',480.00);
INSERT INTO machines (asset_tag, name, type, manufacturer, model, serial_number, location, floor, install_date, last_maint_date, status, rated_capacity) VALUES ('AST-0012','Lathe Kilo','Lathe','Okuma','LB3000EX','SN-LTH-0012','Plant 1 - Bay D','1',DATE '2021-10-01',DATE '2023-12-29','RUNNING',410.00);

INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (1,'SEN-0001-T','Temperature','C',10.0000,85.0000,1,DATE '2018-05-10','v2.3.1');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (1,'SEN-0001-V','Vibration','mm/s',0.0000,4.5000,1,DATE '2018-05-10','v2.3.1');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (3,'SEN-0003-C','Current','A',0.0000,25.0000,1,DATE '2015-03-01','v1.9.4');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (4,'SEN-0004-V','Vibration','mm/s',0.0000,3.8000,1,DATE '2019-11-12','v3.0.2');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (4,'SEN-0004-T','Temperature','C',10.0000,75.0000,1,DATE '2019-11-12','v3.0.2');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (5,'SEN-0005-T','Temperature','C',15.0000,60.0000,1,DATE '2021-02-18','v1.4.0');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (6,'SEN-0006-T','Temperature','C',10.0000,85.0000,1,DATE '2016-06-25','v2.0.5');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (6,'SEN-0006-P','Pressure','psi',0.0000,150.0000,1,DATE '2016-06-25','v2.0.5');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (7,'SEN-0007-RPM','RPM','rpm',0.0000,3200.0000,1,DATE '2020-01-09','v2.6.0');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (8,'SEN-0008-C','Current','A',0.0000,28.0000,1,DATE '2015-03-01','v1.9.4');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (9,'SEN-0009-V','Vibration','mm/s',0.0000,4.0000,1,DATE '2022-04-14','v3.2.1');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (9,'SEN-0009-T','Temperature','C',10.0000,70.0000,1,DATE '2022-04-14','v3.2.1');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (10,'SEN-0010-T','Temperature','C',15.0000,55.0000,0,DATE '2020-09-30','v1.2.9');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (11,'SEN-0011-T','Temperature','C',10.0000,85.0000,1,DATE '2019-07-19','v2.4.3');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (11,'SEN-0011-V','Vibration','mm/s',0.0000,4.2000,1,DATE '2019-07-19','v2.4.3');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (12,'SEN-0012-RPM','RPM','rpm',0.0000,3400.0000,1,DATE '2021-10-01','v2.7.5');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (12,'SEN-0012-T','Temperature','C',10.0000,78.0000,1,DATE '2021-10-01','v2.7.5');
INSERT INTO sensors (machine_id, sensor_code, sensor_type, unit, min_threshold, max_threshold, active, installed_date, firmware_ver) VALUES (10,'SEN-0010-C','Current','A',0.0000,15.0000,0,DATE '2020-09-30','v1.2.9');

INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (1,1,TIMESTAMP '2024-02-01 06:00:00',62.400000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (1,1,TIMESTAMP '2024-02-01 07:00:00',64.100000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (1,1,TIMESTAMP '2024-02-01 08:00:00',88.900000,97,1);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (2,1,TIMESTAMP '2024-02-01 06:00:00',2.100000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (2,1,TIMESTAMP '2024-02-01 07:00:00',2.400000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (5,3,TIMESTAMP '2024-02-01 06:00:00',14.200000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (5,3,TIMESTAMP '2024-02-01 07:00:00',15.800000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (6,4,TIMESTAMP '2024-02-01 06:00:00',1.900000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (7,4,TIMESTAMP '2024-02-01 06:00:00',55.400000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (8,5,TIMESTAMP '2024-02-01 06:00:00',42.100000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (9,6,TIMESTAMP '2024-02-01 06:00:00',71.300000,90,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (9,6,TIMESTAMP '2024-02-01 07:00:00',89.900000,85,1);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (10,6,TIMESTAMP '2024-02-01 06:00:00',95.500000,90,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (11,7,TIMESTAMP '2024-02-01 06:00:00',2950.000000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (12,8,TIMESTAMP '2024-02-01 06:00:00',18.700000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (13,9,TIMESTAMP '2024-02-01 06:00:00',1.400000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (14,9,TIMESTAMP '2024-02-01 06:00:00',48.600000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (16,11,TIMESTAMP '2024-02-01 06:00:00',60.100000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (17,11,TIMESTAMP '2024-02-01 06:00:00',2.300000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (18,12,TIMESTAMP '2024-02-01 06:00:00',3100.000000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (19,12,TIMESTAMP '2024-02-01 06:00:00',56.800000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (1,1,TIMESTAMP '2024-02-02 06:00:00',63.000000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (5,3,TIMESTAMP '2024-02-02 06:00:00',14.900000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (8,5,TIMESTAMP '2024-02-02 06:00:00',41.700000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (11,7,TIMESTAMP '2024-02-02 06:00:00',3010.000000,100,0);
INSERT INTO sensor_readings (sensor_id, machine_id, reading_ts, value, quality, anomaly) VALUES (18,12,TIMESTAMP '2024-02-02 06:00:00',3150.000000,100,0);

INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-01','PC-BRACKET-A',1,TIMESTAMP '2024-02-01 06:00:00',TIMESTAMP '2024-02-01 14:00:00',500,498,3,'MORNING',501,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-02','PC-SHAFT-B',2,TIMESTAMP '2024-02-01 06:00:00',TIMESTAMP '2024-02-01 14:00:00',300,300,0,'MORNING',502,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-03','PC-HOUSING-C',6,TIMESTAMP '2024-02-01 06:00:00',TIMESTAMP '2024-02-01 14:00:00',400,362,38,'MORNING',503,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-04','PC-BRACKET-A',9,TIMESTAMP '2024-02-01 14:00:00',TIMESTAMP '2024-02-01 22:00:00',500,505,2,'AFTERNOON',504,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-05','PC-GEAR-D',7,TIMESTAMP '2024-02-01 14:00:00',TIMESTAMP '2024-02-01 22:00:00',250,248,1,'AFTERNOON',505,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240201-06','PC-SHAFT-B',12,TIMESTAMP '2024-02-01 22:00:00',TIMESTAMP '2024-02-02 06:00:00',300,295,4,'NIGHT',506,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-01','PC-BRACKET-A',1,TIMESTAMP '2024-02-02 06:00:00',TIMESTAMP '2024-02-02 14:00:00',500,500,0,'MORNING',501,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-02','PC-HOUSING-C',11,TIMESTAMP '2024-02-02 06:00:00',TIMESTAMP '2024-02-02 14:00:00',400,388,12,'MORNING',503,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-03','PC-GEAR-D',7,TIMESTAMP '2024-02-02 14:00:00',NULL,250,120,0,'AFTERNOON',505,'IN_PROGRESS');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-04','PC-PRINT-E',5,TIMESTAMP '2024-02-02 08:00:00',TIMESTAMP '2024-02-02 20:00:00',20,20,0,'MORNING',507,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-05','PC-PRINT-F',10,TIMESTAMP '2024-02-02 08:00:00',NULL,15,0,0,'MORNING',507,'ON_HOLD');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240202-06','PC-SHAFT-B',2,TIMESTAMP '2024-02-02 06:00:00',TIMESTAMP '2024-02-02 14:00:00',300,0,0,'MORNING',502,'SCRAPPED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240203-01','PC-BRACKET-A',1,TIMESTAMP '2024-02-03 06:00:00',TIMESTAMP '2024-02-03 14:00:00',500,497,5,'MORNING',501,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240203-02','PC-GEAR-D',12,TIMESTAMP '2024-02-03 06:00:00',TIMESTAMP '2024-02-03 14:00:00',250,250,0,'MORNING',506,'COMPLETED');
INSERT INTO production_batches (batch_number, product_code, machine_id, start_time, end_time, quantity_planned, quantity_actual, defect_count, shift, operator_id, status) VALUES ('BATCH-20240203-03','PC-HOUSING-C',9,TIMESTAMP '2024-02-03 14:00:00',TIMESTAMP '2024-02-03 22:00:00',400,401,6,'AFTERNOON',504,'COMPLETED');

INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (1,'DIMENSIONAL','Q. Alvarado',TIMESTAMP '2024-02-01 14:10:00','PASS',10.020,'mm',10.100,9.900,'Within tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (1,'VISUAL','Q. Alvarado',TIMESTAMP '2024-02-01 14:15:00','PASS',NULL,NULL,NULL,NULL,'No visible defects');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (2,'DIMENSIONAL','R. Sung',TIMESTAMP '2024-02-01 14:20:00','PASS',25.010,'mm',25.050,24.950,'Within tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (3,'DIMENSIONAL','Q. Alvarado',TIMESTAMP '2024-02-01 14:30:00','FAIL',18.900,'mm',18.500,17.500,'Housing warped -- suspect thermal issue');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (3,'FUNCTIONAL','R. Sung',TIMESTAMP '2024-02-01 14:40:00','FAIL',NULL,NULL,NULL,NULL,'Housing failed fit-check on 38 units');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (4,'VISUAL','T. Marsh',TIMESTAMP '2024-02-01 22:05:00','PASS',NULL,NULL,NULL,NULL,'No visible defects');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (5,'DIMENSIONAL','T. Marsh',TIMESTAMP '2024-02-01 22:10:00','PASS',12.010,'mm',12.100,11.900,'Within tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (6,'DIMENSIONAL','Q. Alvarado',TIMESTAMP '2024-02-02 06:10:00','MARGINAL',25.070,'mm',25.050,24.950,'Slightly outside spec, monitor');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (7,'PRESSURE_TEST','R. Sung',TIMESTAMP '2024-02-02 14:05:00','PASS',148.500,'psi',150.000,100.000,'Held pressure for 10 min');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (8,'DIMENSIONAL','T. Marsh',TIMESTAMP '2024-02-02 14:15:00','FAIL',19.200,'mm',18.500,17.500,'12 units out of tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (9,'CHEMICAL','Q. Alvarado',TIMESTAMP '2024-02-02 16:00:00','PASS',NULL,NULL,NULL,NULL,'Material composition within spec');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (10,'VISUAL','R. Sung',TIMESTAMP '2024-02-02 20:05:00','PASS',NULL,NULL,NULL,NULL,'Print quality acceptable');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (13,'DIMENSIONAL','T. Marsh',TIMESTAMP '2024-02-03 14:05:00','PASS',10.010,'mm',10.100,9.900,'Within tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (14,'FUNCTIONAL','Q. Alvarado',TIMESTAMP '2024-02-03 14:15:00','PASS',NULL,NULL,NULL,NULL,'All units functional');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (15,'DIMENSIONAL','R. Sung',TIMESTAMP '2024-02-03 22:10:00','MARGINAL',18.520,'mm',18.500,17.500,'Borderline, recommend recheck');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (1,'PRESSURE_TEST','T. Marsh',TIMESTAMP '2024-02-01 14:25:00','PASS',101.200,'psi',105.000,95.000,'Nominal');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (2,'FUNCTIONAL','Q. Alvarado',TIMESTAMP '2024-02-01 14:35:00','PASS',NULL,NULL,NULL,NULL,'All units functional');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (4,'CHEMICAL','R. Sung',TIMESTAMP '2024-02-01 22:15:00','PASS',NULL,NULL,NULL,NULL,'Composition within spec');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (5,'VISUAL','T. Marsh',TIMESTAMP '2024-02-01 22:20:00','PASS',NULL,NULL,NULL,NULL,'No visible defects');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (9,'DIMENSIONAL','Q. Alvarado',TIMESTAMP '2024-02-02 16:10:00','PASS',24.980,'mm',25.050,24.950,'Within tolerance');
INSERT INTO quality_checks (batch_id, check_type, checked_by, check_ts, result, measurement, unit, pass_threshold, fail_threshold, notes) VALUES (13,'VISUAL','T. Marsh',TIMESTAMP '2024-02-03 14:10:00','PASS',NULL,NULL,NULL,NULL,'No visible defects');

COMMIT;

PROMPT --- Manufacturing/IoT data loaded (12 machines, 20 sensors, 30 sensor_readings, 15 production_batches, 20 quality_checks) ---

/* ============================================================================
   SECTION 3 (cont.): SAMPLE DATA -- Retail
   Insert order: stores -> loyalty_members -> promotions -> inventory ->
                 pos_transactions -> pos_items
   ============================================================================ */

PROMPT --- Loading data: Retail ---

INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-001','EB Retail Downtown Austin','FLAGSHIP','200 Congress Ave','Austin','TX','78701','US','555-501-4001','Rebecca Hunt',DATE '2012-03-15',18000,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-002','EB Retail Denver Uptown','OUTLET','1500 Larimer St','Denver','CO','80202','US','555-501-4002','Andre Michaels',DATE '2014-06-01',9500,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-003','EB Retail Columbus Mall','FLAGSHIP','300 Bridge St','Columbus','OH','43004','US','555-501-4003','Denise Farrow',DATE '2016-09-20',22000,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-004','EB Retail Seattle Pike','EXPRESS','88 Pike St','Seattle','WA','98101','US','555-501-4004','Gordon Blake',DATE '2018-01-10',4200,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-005','EB Retail Miami South Beach','OUTLET','450 Ocean Dr','Miami','FL','33101','US','555-501-4005','Isabel Cruz',DATE '2013-11-05',11000,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-006','EB Retail Online','ONLINE',NULL,NULL,NULL,NULL,'US','555-501-4006','Priya Shah',DATE '2015-05-01',0,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-007','EB Retail Chicago Loop','FLAGSHIP','25 State St','Chicago','IL','60601','US','555-501-4007','Marcus Webb',DATE '2011-08-12',24000,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-008','EB Retail Portland Pearl','EXPRESS','700 NW Couch St','Portland','OR','97201','US','555-501-4008','Nora Kimball',DATE '2019-04-22',3800,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-009','EB Retail Phoenix Central','OUTLET','900 N Central Ave','Phoenix','AZ','85001','US','555-501-4009','Felix Ortega',DATE '2017-02-28',9800,1);
INSERT INTO stores (store_code, name, type, address, city, state, zip, country, phone, manager, open_date, sq_footage, active) VALUES ('STR-010','EB Retail San Diego Gaslamp','FLAGSHIP','410 5th Ave','San Diego','CA','92101','US','555-501-4010','Tanya Reeves',DATE '2020-10-01',16000,1);

INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900001','Olivia','Turner','olivia.turner@example.com','555-401-3001','GOLD',1250,5400,DATE '2019-05-01',DATE '2024-02-01',1,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900002','Ethan','Brooks','ethan.brooks@example.com','555-401-3002','SILVER',320,1800,DATE '2020-02-15',DATE '2024-01-22',2,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900003','Ava','Martinez','ava.martinez@example.com','555-401-3003','BRONZE',75,300,DATE '2022-08-10',DATE '2024-01-05',3,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900004','Liam','Foster','liam.foster@example.com','555-401-3004','PLATINUM',4200,22000,DATE '2013-11-20',DATE '2024-02-10',4,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900005','Sophia','Nakamura','sophia.nakamura@example.com','555-401-3005','GOLD',890,3600,DATE '2018-04-04',DATE '2024-02-08',5,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900006','Noah','Reyes','noah.reyes@example.com','555-401-3006','BRONZE',150,600,DATE '2023-01-15',DATE '2024-01-30',6,0);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900007','Isabella','Coleman','isabella.coleman@example.com','555-401-3007','BRONZE',60,200,DATE '2023-06-01',DATE '2024-01-18',7,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900008','Mason','Hughes','mason.hughes@example.com','555-401-3008','PLATINUM',7800,41000,DATE '2010-07-07',DATE '2024-02-11',7,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900009','Mia','Jansen','mia.jansen@example.com','555-401-3009','BRONZE',20,80,DATE '2023-12-01',DATE '2024-01-02',8,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900010','Lucas','Fitzgerald','lucas.fitzgerald@example.com','555-401-3010','PLATINUM',3100,18000,DATE '2014-02-20',DATE '2024-02-05',9,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900011','Charlotte','Diaz','charlotte.diaz@example.com','555-401-3011','SILVER',540,2100,DATE '2019-09-09',DATE '2024-01-29',10,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900012','Benjamin','Ricci','benjamin.ricci@example.com','555-401-3012','BRONZE',95,350,DATE '2022-03-11',DATE '2024-01-14',1,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900013','Amelia','Voss','amelia.voss@example.com','555-401-3013','SILVER',210,900,DATE '2021-05-19',DATE '2024-02-02',2,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900014','Henry','Blackwood','henry.blackwood@example.com','555-401-3014','PLATINUM',6100,29000,DATE '2012-10-02',DATE '2024-02-09',3,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900015','Evelyn','Sato','evelyn.sato@example.com','555-401-3015','BRONZE',40,160,DATE '2023-08-25',DATE '2024-01-11',4,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900016','Jack','Okonkwo','jack.okonkwo@example.com','555-401-3016','GOLD',1800,7200,DATE '2017-12-12',DATE '2024-02-06',5,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900017','Harper','Lindqvist','harper.lindqvist@example.com','555-401-3017','SILVER',700,2800,DATE '2020-06-06',DATE '2024-01-24',7,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900018','Sebastian','Novak','sebastian.novak@example.com','555-401-3018','GOLD',2500,9800,DATE '2016-01-30',DATE '2024-01-19',9,1);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900019','Zoe','Andersen','zoe.andersen@example.com','555-401-3019','BRONZE',10,40,DATE '2024-01-02',DATE '2024-01-20',10,0);
INSERT INTO loyalty_members (member_number, first_name, last_name, email, phone, tier, points_balance, points_lifetime, joined_date, last_activity, store_id, opted_in_email) VALUES ('LM-900020','William','Castillo','william.castillo@example.com','555-401-3020','PLATINUM',5200,26000,DATE '2011-04-18',DATE '2024-02-04',1,1);

INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('WINTER10','10% off winter clearance','PERCENT_OFF',NULL,10.00,25.00,DATE '2024-01-01',DATE '2024-02-29',1000,412,NULL,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('SAVE5','$5 off any purchase over $30','FIXED_OFF',5.00,NULL,30.00,DATE '2024-01-15',DATE '2024-03-15',500,88,NULL,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('FREESHIP','Free shipping on online orders','FREE_SHIPPING',0.00,NULL,50.00,DATE '2024-01-01',DATE '2024-12-31',NULL,930,6,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('BOGO50','Buy one get one 50% off select apparel','BOGO',NULL,50.00,0.00,DATE '2024-02-01',DATE '2024-02-14',300,140,1,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('DENVER15','Denver store anniversary 15% off','PERCENT_OFF',NULL,15.00,20.00,DATE '2024-02-01',DATE '2024-02-28',200,52,2,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('BUNDLE20','Bundle 3+ items save $20','BUNDLE',20.00,NULL,75.00,DATE '2024-01-01',DATE '2024-06-30',NULL,205,NULL,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('CHIFLAG','Chicago flagship grand re-opening','PERCENT_OFF',NULL,20.00,40.00,DATE '2023-11-01',DATE '2023-12-01',500,498,7,0);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('SPRING5','$5 off spring collection','FIXED_OFF',5.00,NULL,25.00,DATE '2024-03-01',DATE '2024-04-30',400,0,NULL,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('VIPGOLD','Extra 5% for Gold+ loyalty members','PERCENT_OFF',NULL,5.00,0.00,DATE '2024-01-01',DATE '2024-12-31',NULL,610,NULL,1);
INSERT INTO promotions (promo_code, description, promo_type, discount_value, discount_pct, min_purchase, start_date, end_date, usage_limit, times_used, store_id, active) VALUES ('PHX10','Phoenix outlet 10% off','PERCENT_OFF',NULL,10.00,15.00,DATE '2024-01-10',DATE '2024-03-10',250,77,9,1);

INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (1,'SKU-100001','TechPro 15" Laptop 16GB/512GB','Electronics',18,2,5,540.00,899.99,DATE '2024-01-20',DATE '2024-02-01','A-12');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (1,'SKU-100011','PremiumPlus Bluetooth Headphones','Electronics',30,1,10,60.00,149.99,DATE '2024-01-22',DATE '2024-02-01','A-15');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (2,'SKU-100003','StyleMax Men''s Denim Jacket','Clothing',45,0,10,28.00,64.99,DATE '2024-01-18',DATE '2024-02-02','B-04');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (2,'SKU-100013','StyleMax Women''s Running Shoes','Clothing',60,3,15,32.00,79.99,DATE '2024-01-19',DATE '2024-02-02','B-08');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (3,'SKU-100004','HomeFirst Non-Stick Cookware Set','Home',22,0,5,55.00,129.99,DATE '2024-01-15',DATE '2024-01-30','C-02');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (3,'SKU-100014','HomeFirst Memory Foam Pillow','Home',75,2,15,12.00,34.99,DATE '2024-01-16',DATE '2024-01-30','C-06');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (4,'SKU-100007','UrbanGear Backpack','Electronics',40,1,10,18.00,49.99,DATE '2024-01-25',DATE '2024-02-05','D-01');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (5,'SKU-100006','NaturePure Vitamin C Serum','Beauty',90,4,20,6.00,22.50,DATE '2024-01-20',DATE '2024-02-01','E-03');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (5,'SKU-100016','NaturePure Shampoo & Conditioner Set','Beauty',110,0,25,5.50,18.99,DATE '2024-01-20',DATE '2024-02-01','E-05');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (6,'SKU-100002','TechPro Wireless Mouse','Electronics',300,10,50,7.50,19.99,DATE '2024-01-10',DATE '2024-01-25','ONLINE-01');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (6,'SKU-100021','TechPro Mechanical Keyboard','Electronics',85,3,15,35.00,89.99,DATE '2024-01-11',DATE '2024-01-25','ONLINE-02');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (7,'SKU-100012','TechPro 27" 4K Monitor','Electronics',15,1,5,190.00,349.99,DATE '2024-01-14',DATE '2024-01-29','F-01');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (7,'SKU-100024','PremiumPlus Smartwatch','Electronics',48,2,10,85.00,199.99,DATE '2024-01-14',DATE '2024-01-29','F-04');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (8,'SKU-100008','ClassicLine Hardcover Novel Set','Books',33,0,10,15.00,39.99,DATE '2024-01-12',DATE '2024-01-27','G-02');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (9,'SKU-100010','EcoChoice Car Floor Mats','Automotive',52,0,15,22.00,54.99,DATE '2024-01-08',DATE '2024-01-23','H-03');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (9,'SKU-100020','EcoChoice Dash Cam','Automotive',30,1,10,24.00,59.99,DATE '2024-01-08',DATE '2024-01-23','H-06');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (10,'SKU-100015','SportEdge Adjustable Dumbbell Set','Sports',12,1,5,120.00,249.99,DATE '2024-01-17',DATE '2024-02-01','I-01');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (10,'SKU-100005','SportEdge Yoga Mat','Sports',70,3,20,9.00,24.99,DATE '2024-01-17',DATE '2024-02-01','I-04');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (1,'SKU-100022','StyleMax Men''s Chino Pants','Clothing',55,2,15,17.00,44.99,DATE '2024-01-21',DATE '2024-02-01','B-11');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (2,'SKU-100017','UrbanGear Insulated Water Bottle','Electronics',95,0,20,7.00,22.99,DATE '2024-01-19',DATE '2024-02-02','D-05');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (3,'SKU-100023','HomeFirst LED Desk Lamp','Home',40,1,10,11.00,29.99,DATE '2024-01-16',DATE '2024-01-30','C-09');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (4,'SKU-100025','SportEdge Foam Roller','Sports',65,0,20,6.00,17.99,DATE '2024-01-25',DATE '2024-02-05','I-08');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (9,'SKU-100009','ProBrand Building Blocks 500pc','Toys',48,2,15,14.00,34.99,DATE '2024-01-08',DATE '2024-01-23','J-02');
INSERT INTO inventory (store_id, product_code, product_name, category, qty_on_hand, qty_reserved, reorder_point, unit_cost, unit_price, last_received, last_counted, bin_location) VALUES (10,'SKU-100019','ProBrand Remote Control Car','Toys',36,1,10,18.00,44.99,DATE '2024-01-17',DATE '2024-02-01','J-05');

INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (1,'REG-01','CSH-101',1,TIMESTAMP '2024-02-01 11:00:00',899.99,72.00,90.00,881.99,'CREDIT',1,88,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (2,'REG-01','CSH-201',2,TIMESTAMP '2024-02-02 12:15:00',64.99,5.20,9.75,60.44,'DEBIT',5,6,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (3,'REG-02','CSH-301',3,TIMESTAMP '2024-02-03 09:30:00',129.99,10.40,0.00,140.39,'CASH',NULL,13,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (4,'REG-01','CSH-401',4,TIMESTAMP '2024-02-04 15:45:00',49.99,4.00,0.00,53.99,'MOBILE_PAY',NULL,5,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (5,'REG-01','CSH-501',5,TIMESTAMP '2024-02-05 10:20:00',41.49,3.30,0.00,44.79,'CREDIT',NULL,4,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (6,'ONLINE','SYSTEM',6,TIMESTAMP '2024-02-06 08:00:00',19.99,1.60,0.00,21.59,'CREDIT',3,2,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (7,'REG-03','CSH-701',7,TIMESTAMP '2024-02-07 13:15:00',349.99,28.00,0.00,377.99,'CREDIT',NULL,35,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (7,'REG-04','CSH-702',8,TIMESTAMP '2024-02-08 09:50:00',199.99,16.00,0.00,215.99,'GIFT_CARD',9,20,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (8,'REG-01','CSH-801',9,TIMESTAMP '2024-02-09 14:00:00',39.99,3.20,0.00,43.19,'CASH',NULL,4,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (9,'REG-02','CSH-901',10,TIMESTAMP '2024-02-10 16:30:00',54.99,4.40,5.50,53.89,'DEBIT',10,5,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (10,'REG-01','CSH-1001',11,TIMESTAMP '2024-02-11 11:45:00',249.99,20.00,0.00,269.99,'CREDIT',NULL,25,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (1,'REG-02','CSH-102',12,TIMESTAMP '2024-02-12 10:10:00',44.99,3.60,0.00,48.59,'CREDIT',NULL,4,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (2,'REG-02','CSH-202',13,TIMESTAMP '2024-02-13 15:00:00',79.99,6.40,0.00,86.39,'DEBIT',NULL,8,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (3,'REG-01','CSH-302',14,TIMESTAMP '2024-02-14 12:20:00',34.99,2.80,0.00,37.79,'CASH',NULL,3,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (4,'REG-01','CSH-402',15,TIMESTAMP '2024-02-15 09:55:00',22.99,1.80,0.00,24.79,'MOBILE_PAY',NULL,2,1);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (5,'REG-02','CSH-502',16,TIMESTAMP '2024-02-16 13:35:00',89.99,7.20,0.00,97.19,'CREDIT',NULL,9,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (7,'REG-05','CSH-703',17,TIMESTAMP '2024-02-17 16:20:00',44.99,3.60,0.00,48.59,'CREDIT',NULL,4,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (9,'REG-03','CSH-902',18,TIMESTAMP '2024-02-18 10:50:00',29.99,2.40,0.00,32.39,'DEBIT',NULL,3,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (1,'REG-03','CSH-103',19,TIMESTAMP '2024-02-19 13:05:00',199.99,16.00,10.00,205.99,'CREDIT',9,20,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (10,'REG-02','CSH-1002',20,TIMESTAMP '2024-02-20 08:40:00',17.99,1.40,0.00,19.39,'CASH',NULL,1,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (6,'ONLINE','SYSTEM',1,TIMESTAMP '2024-03-01 09:20:00',89.99,7.20,0.00,97.19,'CREDIT',3,9,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (4,'REG-01','CSH-403',4,TIMESTAMP '2024-03-02 15:50:00',199.99,16.00,0.00,215.99,'CREDIT',NULL,20,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (7,'REG-04','CSH-704',8,TIMESTAMP '2024-03-03 11:30:00',44.99,3.60,0.00,48.59,'CREDIT',NULL,4,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (5,'REG-02','CSH-503',NULL,TIMESTAMP '2024-03-04 14:10:00',34.99,2.80,0.00,37.79,'CASH',NULL,0,0);
INSERT INTO pos_transactions (store_id, register_id, cashier_id, member_id, txn_ts, subtotal, tax_amount, discount_amount, total_amount, payment_type, promo_id, points_earned, voided) VALUES (10,'REG-01','CSH-1003',20,TIMESTAMP '2024-03-05 10:55:00',22.99,1.80,0.00,24.79,'DEBIT',NULL,2,0);

INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (1,'SKU-100001','TechPro 15" Laptop 16GB/512GB',1,899.99,90.00,809.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (2,'SKU-100003','StyleMax Men''s Denim Jacket',1,64.99,9.75,55.24,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (3,'SKU-100004','HomeFirst Non-Stick Cookware Set',1,129.99,0.00,129.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (4,'SKU-100007','UrbanGear Backpack',1,49.99,0.00,49.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (5,'SKU-100006','NaturePure Vitamin C Serum',1,22.50,0.00,22.50,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (5,'SKU-100016','NaturePure Shampoo & Conditioner Set',1,18.99,0.00,18.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (6,'SKU-100002','TechPro Wireless Mouse',1,19.99,0.00,19.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (7,'SKU-100012','TechPro 27" 4K Monitor',1,349.99,0.00,349.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (8,'SKU-100024','PremiumPlus Smartwatch',1,199.99,0.00,199.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (9,'SKU-100008','ClassicLine Hardcover Novel Set',1,39.99,0.00,39.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (10,'SKU-100010','EcoChoice Car Floor Mats',1,54.99,5.50,49.49,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (11,'SKU-100015','SportEdge Adjustable Dumbbell Set',1,249.99,0.00,249.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (12,'SKU-100022','StyleMax Men''s Chino Pants',1,44.99,0.00,44.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (13,'SKU-100013','StyleMax Women''s Running Shoes',1,79.99,0.00,79.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (14,'SKU-100023','HomeFirst LED Desk Lamp',1,29.99,0.00,29.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (14,'SKU-100014','HomeFirst Memory Foam Pillow',1,34.99,0.00,34.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (15,'SKU-100017','UrbanGear Insulated Water Bottle',1,22.99,0.00,22.99,1);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (16,'SKU-100021','TechPro Mechanical Keyboard',1,89.99,0.00,89.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (17,'SKU-100025','SportEdge Foam Roller',1,17.99,0.00,17.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (17,'SKU-100005','SportEdge Yoga Mat',1,24.99,0.00,24.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (18,'SKU-100009','ProBrand Building Blocks 500pc',1,29.99,0.00,29.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (19,'SKU-100024','PremiumPlus Smartwatch',1,199.99,10.00,189.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (20,'SKU-100019','ProBrand Remote Control Car',1,17.99,0.00,17.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (21,'SKU-100021','TechPro Mechanical Keyboard',1,89.99,0.00,89.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (22,'SKU-100024','PremiumPlus Smartwatch',1,199.99,0.00,199.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (23,'SKU-100022','StyleMax Men''s Chino Pants',1,44.99,0.00,44.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (24,'SKU-100005','SportEdge Yoga Mat',1,24.99,0.00,24.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (24,'SKU-100025','SportEdge Foam Roller',1,17.99,0.00,17.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (25,'SKU-100017','UrbanGear Insulated Water Bottle',1,22.99,0.00,22.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (1,'SKU-100002','TechPro Wireless Mouse',1,19.99,0.00,19.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (7,'SKU-100011','PremiumPlus Bluetooth Headphones',1,149.99,0.00,149.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (11,'SKU-100005','SportEdge Yoga Mat',1,24.99,0.00,24.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (9,'SKU-100018','ClassicLine Cookbook: Everyday Meals',1,24.99,0.00,24.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (20,'SKU-100009','ProBrand Building Blocks 500pc',1,34.99,0.00,34.99,0);
INSERT INTO pos_items (pos_txn_id, product_code, product_name, quantity, unit_price, discount_amt, line_total, returned) VALUES (13,'SKU-100017','UrbanGear Insulated Water Bottle',1,22.99,0.00,22.99,0);

COMMIT;

PROMPT --- Retail data loaded (10 stores, 20 loyalty_members, 10 promotions, 25 inventory, 25 pos_transactions, 35 pos_items) ---

/* ============================================================================
   SECTION 3 (cont.): SAMPLE DATA -- Government
   Insert order: citizens -> gov_employees -> permits -> violations ->
                 public_contracts -> service_requests
   (gov_employees.supervisor_id is self-referential -- always points to an
   earlier-inserted row, so gov_employees is loaded before the tables that
   reference it as a reviewing/assigned officer.)
   ============================================================================ */

PROMPT --- Loading data: Government ---

INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Michael','Turner',DATE '1985-04-12','M','NID-700000001','michael.turner@civicmail.example','555-601-1001','12 Capitol St','Austin','TX','73301',TIMESTAMP '2015-01-10 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Sandra','Wells',DATE '1990-08-23','F','NID-700000002','sandra.wells@civicmail.example','555-601-1002','88 Legislature Ave','Austin','TX','73302',TIMESTAMP '2015-02-14 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Raymond','Ibarra',DATE '1978-11-02','M','NID-700000003','raymond.ibarra@civicmail.example','555-601-1003','204 Founders Blvd','Dallas','TX','75201',TIMESTAMP '2015-03-20 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Diane','Kessler',DATE '1982-02-17','F','NID-700000004','diane.kessler@civicmail.example','555-601-1004','55 Heritage Ln','Dallas','TX','75202',TIMESTAMP '2015-04-25 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Anthony','Vega',DATE '1995-06-30','M','NID-700000005','anthony.vega@civicmail.example','555-601-1005','901 Independence Way','Houston','TX','77001',TIMESTAMP '2016-01-15 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Patricia','Hoffman',DATE '1988-09-09','F','NID-700000006','patricia.hoffman@civicmail.example','555-601-1006','33 Liberty Cir','Houston','TX','77002',TIMESTAMP '2016-02-18 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('George','Padilla',DATE '1975-12-25','M','NID-700000007','george.padilla@civicmail.example','555-601-1007','77 Union Sq','San Antonio','TX','78201',TIMESTAMP '2016-05-30 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Linda','Marsh',DATE '1992-03-14','F','NID-700000008','linda.marsh@civicmail.example','555-601-1008','18 Statehouse Dr','San Antonio','TX','78202',TIMESTAMP '2016-07-11 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Kevin','Booth',DATE '1980-07-04','M','NID-700000009','kevin.booth@civicmail.example','555-601-1009','410 Municipal Row','Austin','TX','73303',TIMESTAMP '2017-02-01 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Rebecca','Solis',DATE '1998-01-19','F','NID-700000010','rebecca.solis@civicmail.example','555-601-1010','62 Courthouse Sq','Dallas','TX','75203',TIMESTAMP '2017-04-09 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Timothy','Nash',DATE '1983-10-08','M','NID-700000011','timothy.nash@civicmail.example','555-601-1011','5 City Hall Plz','Houston','TX','77003',TIMESTAMP '2017-06-22 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Angela','Frost',DATE '1991-05-27','F','NID-700000012','angela.frost@civicmail.example','555-601-1012','89 Precinct Rd','San Antonio','TX','78203',TIMESTAMP '2017-09-14 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Steven','Cordova',DATE '1979-08-16','M','NID-700000013','steven.cordova@civicmail.example','555-601-1013','140 Township Ave','Austin','TX','73304',TIMESTAMP '2018-01-05 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Melissa','Farley',DATE '1994-12-01','F','NID-700000014','melissa.farley@civicmail.example','555-601-1014','27 District Ct','Dallas','TX','75204',TIMESTAMP '2018-03-19 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Jason','Bowman',DATE '1987-04-05','M','NID-700000015','jason.bowman@civicmail.example','555-601-1015','963 Ward St','Houston','TX','77004',TIMESTAMP '2018-06-27 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Christine','Kirk',DATE '1996-09-22','F','NID-700000016','christine.kirk@civicmail.example','555-601-1016','71 Borough Ln','San Antonio','TX','78204',TIMESTAMP '2018-08-30 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Larry','Odom',DATE '1973-06-11','M','NID-700000017','larry.odom@civicmail.example','555-601-1017','15 Parish Dr','Austin','TX','73305',TIMESTAMP '2019-01-14 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Teresa','Guzman',DATE '1989-02-28','F','NID-700000018','teresa.guzman@civicmail.example','555-601-1018','48 Commonwealth Ave','Dallas','TX','75205',TIMESTAMP '2019-05-06 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Bruce','Melton',DATE '1984-11-19','M','NID-700000019','bruce.melton@civicmail.example','555-601-1019','302 Federal Way','Houston','TX','77005',TIMESTAMP '2019-07-23 09:00:00');
INSERT INTO citizens (first_name, last_name, dob, gender, national_id, email, phone, address, city, state, zip, registered_at) VALUES ('Dawn','Espinoza',DATE '1993-07-07','F','NID-700000020','dawn.espinoza@civicmail.example','555-601-1020','19 State Rte 8','San Antonio','TX','78205',TIMESTAMP '2019-10-02 09:00:00');

-- gov_employees (15) -- supervisor_id self-references an earlier emp_id (NULL for the top of the chain)
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Harold','Simms','City Manager''s Office','City Manager','GS-15',112000.00,DATE '2004-01-05',NULL,'harold.simms@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Cheryl','Nakamura','Finance','Finance Director','GS-13',92000.00,DATE '2008-04-01',1,'cheryl.nakamura@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Gordon','Ashby','IT','IT Director','GS-13',95000.00,DATE '2009-02-01',1,'gordon.ashby@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Dennis','Whitfield','Parks & Recreation','Parks Director','GS-12',84000.00,DATE '2007-05-01',1,'dennis.whitfield@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Walter','Hines','Fire','Fire Marshal','GS-13',91000.00,DATE '2006-03-01',1,'walter.hines@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Marcus','Delgado','Police','Police Captain','GS-13',93000.00,DATE '2004-09-01',1,'marcus.delgado@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Priscilla','Yoon','Health','Health Director','GS-12',86000.00,DATE '2010-02-01',1,'priscilla.yoon@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Renee','Castillo','Public Works','Public Works Director','GS-13',90000.00,DATE '2005-06-01',1,'renee.castillo@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Julia','Bennett','Building & Permits','Permit Technician','GS-7',52000.00,DATE '2015-06-15',8,'julia.bennett@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Curtis','Odell','Building & Permits','Building Inspector','GS-10',66500.00,DATE '2014-09-01',8,'curtis.odell@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Yvonne','Marsh','Public Works','Sanitation Supervisor','GS-8',54500.00,DATE '2013-04-01',8,'yvonne.marsh@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Roland','Beck','Police','Traffic Enforcement Officer','GS-8',56000.00,DATE '2015-10-01',6,'roland.beck@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Stephanie','Roque','Finance','Revenue Collections Officer','GS-9',60000.00,DATE '2017-01-15',2,'stephanie.roque@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Vivian','Prescott','Clerk''s Office','City Clerk Assistant','GS-6',46000.00,DATE '2019-03-15',1,'vivian.prescott@citygov.example');
INSERT INTO gov_employees (first_name, last_name, dept, title, grade, salary, hire_date, supervisor_id, email) VALUES ('Bradley','Concepcion','311 Services','Citizen Services Coordinator','GS-7',50500.00,DATE '2020-06-01',1,'bradley.concepcion@citygov.example');

-- permits (20) -- citizen_id 1-20, reviewer_id -> gov_employees (Building & Permits staff: 9, 10)
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-B-0001',1,'BUILDING','Residential deck addition','12 Capitol St','PCL-10001',DATE '2023-02-15',DATE '2023-03-01',DATE '2024-03-01',150.00,'ISSUED',10,'Approved after inspection');
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-BZ-0001',2,'BUSINESS','Retail storefront license renewal','88 Legislature Ave',NULL,DATE '2023-01-05',DATE '2023-01-15',DATE '2025-01-15',300.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-E-0001',3,'EVENT','Neighborhood block party','204 Founders Blvd',NULL,DATE '2023-05-20',DATE '2023-06-01',DATE '2023-06-02',50.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-P-0001',4,'PARKING','Residential parking permit','55 Heritage Ln',NULL,DATE '2022-12-20',DATE '2023-01-01',DATE '2023-12-31',25.00,'EXPIRED',9,'Not renewed');
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-C-0001',5,'CONTRACTOR','General contractor license','901 Independence Way',NULL,DATE '2022-04-15',DATE '2022-05-01',DATE '2025-05-01',500.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-B-0002',6,'BUILDING','Garage conversion','33 Liberty Cir','PCL-10006',DATE '2024-01-10',NULL,NULL,0.00,'SUBMITTED',NULL,'Awaiting plan review');
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-A-0001',7,'ALCOHOL','Restaurant beer and wine license','77 Union Sq',NULL,DATE '2023-07-15',DATE '2023-08-01',DATE '2024-08-01',800.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-BZ-0002',8,'BUSINESS','Home-based bakery license','18 Statehouse Dr',NULL,DATE '2024-01-20',NULL,NULL,0.00,'DENIED',9,'Zoning conflict');
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-F-0001',9,'FIREARM','Concealed carry permit','410 Municipal Row',NULL,DATE '2022-08-10',DATE '2022-09-01',DATE '2027-09-01',100.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-E-0002',10,'EVENT','Charity 5K road closure','62 Courthouse Sq',NULL,DATE '2023-08-30',DATE '2023-09-15',DATE '2023-09-16',75.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-B-0003',11,'BUILDING','Backyard swimming pool','5 City Hall Plz','PCL-10011',DATE '2023-03-25',DATE '2023-04-10',DATE '2024-04-10',250.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-P-0002',12,'PARKING','Commercial loading zone permit','89 Precinct Rd',NULL,DATE '2021-12-15',DATE '2022-01-01',DATE '2022-12-31',40.00,'EXPIRED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-C-0002',13,'CONTRACTOR','Electrical contractor license','140 Township Ave',NULL,DATE '2023-01-20',DATE '2023-02-01',DATE '2026-02-01',500.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-BZ-0003',14,'BUSINESS','Food truck vendor permit','27 District Ct',NULL,DATE '2023-10-15',DATE '2023-11-01',DATE '2024-11-01',200.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-B-0004',15,'BUILDING','Fence installation','963 Ward St','PCL-10015',DATE '2023-12-20',DATE '2024-01-05',DATE '2024-07-05',60.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-E-0003',16,'EVENT','Farmers market vendor day','71 Borough Ln',NULL,DATE '2024-02-01',NULL,NULL,0.00,'SUBMITTED',NULL,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-A-0002',17,'ALCOHOL','Special event liquor license','15 Parish Dr',NULL,DATE '2022-05-15',DATE '2022-06-01',DATE '2023-06-01',400.00,'SUSPENDED',9,'Revoked after compliance violation');
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-F-0002',18,'FIREARM','Firearms dealer license','48 Commonwealth Ave',NULL,DATE '2021-02-10',DATE '2021-03-01',DATE '2026-03-01',750.00,'ISSUED',10,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-B-0005',19,'BUILDING','Home addition remodel','302 Federal Way','PCL-10019',DATE '2023-06-15',DATE '2023-07-01',DATE '2024-07-01',180.00,'ISSUED',9,NULL);
INSERT INTO permits (permit_number, citizen_id, permit_type, description, address, parcel_number, applied_date, issued_date, expiry_date, fee_amount, status, reviewer_id, notes) VALUES ('PMT-BZ-0004',20,'BUSINESS','Salon operating license','19 State Rte 8',NULL,DATE '2023-04-20',DATE '2023-05-01',DATE '2025-05-01',220.00,'ISSUED',10,NULL);

-- violations (18) -- citizen_id 1-18, officer_id -> gov_employees (Police: 6, 12)
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (1,'VC-2201','Parked in a no-parking zone','5th St & Congress Ave',DATE '2023-11-01',DATE '2023-11-01',12,75.00,75.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (2,'VC-2202','Exceeded posted speed limit by 12 mph','Preston Rd',DATE '2023-11-05',DATE '2023-11-05',12,150.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (3,'VC-2203','Excessive noise after 10pm','Founders Blvd residence',DATE '2023-11-10',DATE '2023-11-11',6,100.00,100.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (4,'VC-2204','Improper disposal of trash','Heritage Ln',DATE '2023-11-15',DATE '2023-11-15',6,50.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (5,'VC-2205','Vehicle registration expired over 60 days','Independence Way',DATE '2023-11-20',DATE '2023-11-20',12,90.00,90.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (6,'VC-2206','Parked in disabled space without permit','Liberty Cir',DATE '2023-11-22',DATE '2023-11-22',12,250.00,0.00,'CONTESTED',DATE '2024-02-15');
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (7,'VC-2207','School zone speeding violation','Union Sq',DATE '2023-11-25',DATE '2023-11-25',12,300.00,300.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (8,'VC-2208','Dumping construction debris on public land','Statehouse Dr',DATE '2023-12-01',DATE '2023-12-02',6,500.00,0.00,'WARRANT',DATE '2024-03-01');
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (9,'VC-2209','Construction noise outside permitted hours','Municipal Row',DATE '2023-12-05',DATE '2023-12-05',6,120.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (10,'VC-2210','Expired parking meter','Courthouse Sq',DATE '2023-12-08',DATE '2023-12-08',12,40.00,40.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (11,'VC-2211','Exceeded speed limit by 20 mph','City Hall Plz',DATE '2023-12-12',DATE '2023-12-12',12,220.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (12,'VC-2212','Cigarette litter in public park','Precinct Rd park',DATE '2023-12-15',DATE '2023-12-15',6,35.00,35.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (13,'VC-2213','Failure to renew vehicle registration','Township Ave',DATE '2023-12-18',DATE '2023-12-18',12,90.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (14,'VC-2214','Blocking a fire hydrant','District Ct',DATE '2023-12-20',DATE '2023-12-20',12,200.00,200.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (15,'VC-2215','Amplified sound without a permit','Ward St',DATE '2024-01-03',DATE '2024-01-04',6,150.00,0.00,'CONTESTED',DATE '2024-03-20');
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (16,'VC-2216','Speeding in residential zone','Borough Ln',DATE '2024-01-08',DATE '2024-01-08',12,175.00,0.00,'OPEN',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (17,'VC-2217','Illegal dumping of household waste','Parish Dr',DATE '2024-01-12',DATE '2024-01-13',6,300.00,300.00,'PAID',NULL);
INSERT INTO violations (citizen_id, violation_code, description, location, occurred_date, reported_date, officer_id, fine_amount, paid_amount, status, court_date) VALUES (18,'VC-2218','Overnight parking violation','Commonwealth Ave',DATE '2024-01-18',DATE '2024-01-18',12,60.00,0.00,'OPEN',NULL);

-- public_contracts (15) -- no FK to citizens; contact_officer -> gov_employees
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-001','Lonestar Paving Co.','VEND-1001','Public Works','Downtown Road Resurfacing Phase 2','Repaving 4.2 miles of downtown arterial roads',1250000.00,DATE '2023-04-01',DATE '2024-03-31','ACTIVE',DATE '2023-03-01',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-002','Texas Climate Systems','VEND-1002','Facilities Management','City Hall HVAC Replacement','Full HVAC system replacement for City Hall',480000.00,DATE '2023-05-15',DATE '2023-11-15','COMPLETED',DATE '2023-04-20',1);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-003','AutoCare Fleet Services','VEND-1003','Public Works','Municipal Fleet Vehicle Maintenance','Ongoing maintenance for city vehicle fleet',210000.00,DATE '2023-01-01',DATE '2024-12-31','ACTIVE',DATE '2022-12-10',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-004','NexGen IT Solutions','VEND-1004','IT','Public Library IT Modernization','Network and workstation refresh across library branches',350000.00,DATE '2023-06-01',DATE '2024-06-01','ACTIVE',DATE '2023-05-10',3);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-005','GreenFlow Irrigation LLC','VEND-1005','Parks & Recreation','Parks Irrigation System Upgrade','Smart irrigation retrofit across 6 city parks',175000.00,DATE '2023-03-01',DATE '2023-08-01','COMPLETED',DATE '2023-02-05',4);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-006','Sentinel Optics Inc.','VEND-1006','Police','Body-Worn Camera Program','Department-wide body camera rollout',620000.00,DATE '2023-02-01',DATE '2025-02-01','ACTIVE',DATE '2023-01-10',6);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-007','AquaPure Chemical Supply','VEND-1007','Public Works','Water Treatment Plant Chemicals Supply','Annual water treatment chemical supply contract',890000.00,DATE '2023-01-01',DATE '2024-01-01','COMPLETED',DATE '2022-11-15',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-008','Beacon Communications Group','VEND-1008','Fire','Emergency Dispatch System Upgrade','911 dispatch console and radio system upgrade',540000.00,DATE '2023-07-01',DATE '2024-07-01','ACTIVE',DATE '2023-06-01',5);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-009','Brightline Electric Contractors','VEND-1009','Public Works','Downtown Streetlight LED Conversion','Conversion of 1,200 streetlights to LED',320000.00,DATE '2023-09-01',DATE '2024-04-01','ACTIVE',DATE '2023-08-05',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-010','Cornerstone Builders LLC','VEND-1010','Health','Animal Shelter Renovation','Renovation and expansion of city animal shelter',410000.00,DATE '2023-03-15',DATE '2023-12-15','COMPLETED',DATE '2023-02-20',7);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-011','Civic Digital Studio','VEND-1011','IT','City Website Redesign','Full redesign and CMS migration of citygov.example',95000.00,DATE '2023-10-01',DATE '2024-02-01','ACTIVE',DATE '2023-09-10',3);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-012','Metro Coach Manufacturing','VEND-1012','Public Works','Public Bus Fleet Overhaul','Overhaul of 40 transit buses',2100000.00,DATE '2023-01-15',DATE '2025-01-15','ACTIVE',DATE '2022-12-01',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-013','Guardian Systems Integration','VEND-1013','Facilities Management','Courthouse Security Upgrade','Access control and camera upgrade at county courthouse',265000.00,DATE '2022-11-01',DATE '2023-05-01','CANCELLED',DATE '2022-10-01',1);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-014','Terra Engineering Group','VEND-1014','Public Works','Landfill Expansion Engineering Study','Engineering feasibility study for landfill expansion',145000.00,DATE '2023-05-01',DATE '2023-11-01','COMPLETED',DATE '2023-04-05',8);
INSERT INTO public_contracts (contract_number, vendor_name, vendor_id, dept, title, description, contract_value, start_date, end_date, status, award_date, contact_officer) VALUES ('PC-2023-015','Sunrise Solar Contractors','VEND-1015','Parks & Recreation','Recreation Center Solar Panel Installation','Rooftop solar installation at 3 recreation centers',385000.00,DATE '2024-01-01',DATE '2024-08-01','PENDING',DATE '2023-11-20',4);

-- service_requests (20) -- citizen_id 1-20, assigned_emp -> gov_employees (nullable)
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (1,'POTHOLE','ROAD_MAINTENANCE','Large pothole on Congress Ave near 5th St','Congress Ave & 5th St',TIMESTAMP '2024-01-04 08:30:00',TIMESTAMP '2024-01-10 14:00:00',8,'HIGH','RESOLVED',4);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (2,'STREETLIGHT','ELECTRICAL','Streetlight out on Preston Rd','Preston Rd',TIMESTAMP '2024-01-06 19:00:00',TIMESTAMP '2024-01-12 10:00:00',11,'MEDIUM','RESOLVED',5);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (3,'NOISE_COMPLAINT','RESIDENTIAL','Ongoing construction noise before 7am','Founders Blvd',TIMESTAMP '2024-01-08 07:15:00',TIMESTAMP '2024-01-09 09:00:00',6,'MEDIUM','CLOSED',3);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (4,'TRASH_PICKUP','MISSED_COLLECTION','Trash not collected on scheduled day','Heritage Ln',TIMESTAMP '2024-01-09 09:00:00',TIMESTAMP '2024-01-11 08:00:00',11,'LOW','RESOLVED',4);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (5,'WATER_LEAK','INFRASTRUCTURE','Water main leak flooding sidewalk','Independence Way',TIMESTAMP '2024-01-10 06:45:00',TIMESTAMP '2024-01-10 12:00:00',8,'CRITICAL','RESOLVED',5);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (6,'TREE_REMOVAL','PARKS','Dead tree at risk of falling on power lines','Liberty Cir',TIMESTAMP '2024-01-12 10:00:00',NULL,4,'HIGH','IN_PROGRESS',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (7,'GRAFFITI','VANDALISM','Graffiti on underpass wall','Union Sq underpass',TIMESTAMP '2024-01-14 13:30:00',TIMESTAMP '2024-01-20 11:00:00',8,'LOW','RESOLVED',4);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (8,'ANIMAL_CONTROL','STRAY_ANIMAL','Stray dog roaming school zone','Statehouse Dr',TIMESTAMP '2024-01-15 15:00:00',TIMESTAMP '2024-01-16 09:30:00',NULL,'MEDIUM','RESOLVED',4);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (9,'POTHOLE','ROAD_MAINTENANCE','Multiple potholes on Westheimer Rd','Westheimer Rd',TIMESTAMP '2024-01-17 08:00:00',NULL,8,'HIGH','OPEN',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (10,'STREETLIGHT','ELECTRICAL','Flickering streetlight causing safety concern','Courthouse Sq',TIMESTAMP '2024-01-18 20:15:00',NULL,NULL,'MEDIUM','OPEN',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (11,'TRASH_PICKUP','BULK_ITEM','Bulk furniture pickup request','City Hall Plz',TIMESTAMP '2024-01-19 09:30:00',TIMESTAMP '2024-01-25 08:00:00',11,'LOW','CLOSED',3);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (12,'WATER_LEAK','INFRASTRUCTURE','Fire hydrant leaking continuously','Precinct Rd',TIMESTAMP '2024-01-20 11:00:00',TIMESTAMP '2024-01-22 15:00:00',8,'HIGH','RESOLVED',5);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (13,'NOISE_COMPLAINT','COMMERCIAL','Bar exceeding noise ordinance limits nightly','Township Ave',TIMESTAMP '2024-01-22 23:00:00',NULL,6,'MEDIUM','IN_PROGRESS',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (14,'TREE_REMOVAL','PARKS','Overgrown tree blocking sidewalk','District Ct',TIMESTAMP '2024-01-24 14:00:00',NULL,4,'LOW','OPEN',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (15,'GRAFFITI','VANDALISM','Graffiti on public library exterior','Ward St library',TIMESTAMP '2024-01-26 10:00:00',NULL,NULL,'LOW','IN_PROGRESS',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (16,'ANIMAL_CONTROL','WILDLIFE','Raccoon nesting in storm drain','Borough Ln',TIMESTAMP '2024-01-27 16:30:00',TIMESTAMP '2024-01-29 10:00:00',NULL,'MEDIUM','RESOLVED',4);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (17,'POTHOLE','ROAD_MAINTENANCE','Pothole causing tire damage reports','Parish Dr',TIMESTAMP '2024-01-29 07:45:00',TIMESTAMP '2024-01-30 13:00:00',8,'CRITICAL','RESOLVED',5);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (18,'WATER_LEAK','INFRASTRUCTURE','Suspected underground pipe leak in yard','Commonwealth Ave',TIMESTAMP '2024-01-31 12:00:00',NULL,8,'MEDIUM','OPEN',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (19,'STREETLIGHT','ELECTRICAL','New streetlight request for dark intersection','Federal Way',TIMESTAMP '2024-02-02 09:00:00',NULL,NULL,'LOW','OPEN',NULL);
INSERT INTO service_requests (citizen_id, request_type, category, description, location, submitted_at, resolved_at, assigned_emp, priority, status, satisfaction) VALUES (20,'TRASH_PICKUP','RECYCLING','Recycling bin damaged, needs replacement','State Rte 8',TIMESTAMP '2024-02-03 10:30:00',TIMESTAMP '2024-02-06 09:00:00',11,'LOW','RESOLVED',4);

COMMIT;

PROMPT --- Government data loaded (20 citizens, 15 gov_employees, 20 permits, 18 violations, 15 public_contracts, 20 service_requests) ---

/* ============================================================================
   SECTION 4: FOREIGN KEY CONSTRAINTS
   Added after all data is loaded (see header notes). Column names below were
   verified against the CREATE TABLE statements in Section 1 above -- a few
   diverge from what a generic cross-engine reference schema might assume
   (e.g. diagnoses/lab_results/insurance_claims have no provider_id column in
   this schema; inventory/pos_items key products by product_code text rather
   than a numeric product_id, so no FK is declared there; loyalty_members and
   promotions key off store_id rather than customer_id).
   ============================================================================ */

PROMPT --- Adding foreign key constraints ---

-- Healthcare
ALTER TABLE appointments      ADD CONSTRAINT fk_appointments_patients      FOREIGN KEY (patient_id)  REFERENCES patients(patient_id);
ALTER TABLE appointments      ADD CONSTRAINT fk_appointments_providers     FOREIGN KEY (provider_id) REFERENCES providers(provider_id);
ALTER TABLE diagnoses         ADD CONSTRAINT fk_diagnoses_patients         FOREIGN KEY (patient_id)  REFERENCES patients(patient_id);
ALTER TABLE lab_results       ADD CONSTRAINT fk_lab_results_patients       FOREIGN KEY (patient_id)  REFERENCES patients(patient_id);
ALTER TABLE insurance_claims  ADD CONSTRAINT fk_insurance_claims_patients  FOREIGN KEY (patient_id)  REFERENCES patients(patient_id);
ALTER TABLE prescriptions     ADD CONSTRAINT fk_prescriptions_patients     FOREIGN KEY (patient_id)  REFERENCES patients(patient_id);
ALTER TABLE prescriptions     ADD CONSTRAINT fk_prescriptions_providers    FOREIGN KEY (provider_id) REFERENCES providers(provider_id);

PROMPT --- FKs: Healthcare done ---

-- E-commerce (categories.parent_id is self-referential -- always points to an earlier-inserted row)
ALTER TABLE categories        ADD CONSTRAINT fk_categories_parent          FOREIGN KEY (parent_id)   REFERENCES categories(category_id);
ALTER TABLE products          ADD CONSTRAINT fk_products_categories        FOREIGN KEY (category_id) REFERENCES categories(category_id);
ALTER TABLE orders            ADD CONSTRAINT fk_orders_customers           FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE order_items       ADD CONSTRAINT fk_order_items_orders         FOREIGN KEY (order_id)    REFERENCES orders(order_id);
ALTER TABLE order_items       ADD CONSTRAINT fk_order_items_products       FOREIGN KEY (product_id)  REFERENCES products(product_id);
ALTER TABLE reviews           ADD CONSTRAINT fk_reviews_products           FOREIGN KEY (product_id)  REFERENCES products(product_id);
ALTER TABLE reviews           ADD CONSTRAINT fk_reviews_customers          FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE cart_events       ADD CONSTRAINT fk_cart_events_customers      FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE cart_events       ADD CONSTRAINT fk_cart_events_products       FOREIGN KEY (product_id)  REFERENCES products(product_id);

PROMPT --- FKs: E-commerce done ---

-- Finance
ALTER TABLE transactions      ADD CONSTRAINT fk_transactions_accounts      FOREIGN KEY (account_id)  REFERENCES accounts(account_id);
ALTER TABLE loans             ADD CONSTRAINT fk_loans_accounts             FOREIGN KEY (account_id)  REFERENCES accounts(account_id);
ALTER TABLE fraud_alerts      ADD CONSTRAINT fk_fraud_alerts_accounts      FOREIGN KEY (account_id)  REFERENCES accounts(account_id);
ALTER TABLE fraud_alerts      ADD CONSTRAINT fk_fraud_alerts_transactions  FOREIGN KEY (txn_id)      REFERENCES transactions(txn_id);

PROMPT --- FKs: Finance done ---

-- Manufacturing / IoT
ALTER TABLE sensors             ADD CONSTRAINT fk_sensors_machines          FOREIGN KEY (machine_id) REFERENCES machines(machine_id);
ALTER TABLE sensor_readings     ADD CONSTRAINT fk_sensor_readings_sensors   FOREIGN KEY (sensor_id)  REFERENCES sensors(sensor_id);
ALTER TABLE sensor_readings     ADD CONSTRAINT fk_sensor_readings_machines  FOREIGN KEY (machine_id) REFERENCES machines(machine_id);
ALTER TABLE production_batches  ADD CONSTRAINT fk_batches_machines          FOREIGN KEY (machine_id) REFERENCES machines(machine_id);
ALTER TABLE quality_checks      ADD CONSTRAINT fk_quality_checks_batches    FOREIGN KEY (batch_id)   REFERENCES production_batches(batch_id);

PROMPT --- FKs: Manufacturing/IoT done ---

-- Retail (store_id/member_id columns tie back to Retail's own stores/loyalty_members;
-- inventory and pos_items key products by product_code text, not a numeric FK)
ALTER TABLE loyalty_members   ADD CONSTRAINT fk_loyalty_members_stores      FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE promotions        ADD CONSTRAINT fk_promotions_stores           FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE inventory         ADD CONSTRAINT fk_inventory_stores            FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE pos_transactions  ADD CONSTRAINT fk_pos_transactions_stores     FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE pos_transactions  ADD CONSTRAINT fk_pos_transactions_members    FOREIGN KEY (member_id)   REFERENCES loyalty_members(member_id);
ALTER TABLE pos_items         ADD CONSTRAINT fk_pos_items_pos_transactions  FOREIGN KEY (pos_txn_id)  REFERENCES pos_transactions(pos_txn_id);

PROMPT --- FKs: Retail done ---

-- Government (gov_employees.supervisor_id is self-referential -- always points to an
-- earlier-inserted row; reviewer_id/officer_id/contact_officer/assigned_emp columns
-- were added with matching indexes in Section 2, so their FKs are declared here too)
ALTER TABLE gov_employees     ADD CONSTRAINT fk_gov_employees_supervisor    FOREIGN KEY (supervisor_id)   REFERENCES gov_employees(emp_id);
ALTER TABLE permits           ADD CONSTRAINT fk_permits_citizens            FOREIGN KEY (citizen_id)      REFERENCES citizens(citizen_id);
ALTER TABLE permits           ADD CONSTRAINT fk_permits_reviewer            FOREIGN KEY (reviewer_id)     REFERENCES gov_employees(emp_id);
ALTER TABLE violations        ADD CONSTRAINT fk_violations_citizens         FOREIGN KEY (citizen_id)      REFERENCES citizens(citizen_id);
ALTER TABLE violations        ADD CONSTRAINT fk_violations_officer          FOREIGN KEY (officer_id)      REFERENCES gov_employees(emp_id);
ALTER TABLE public_contracts  ADD CONSTRAINT fk_public_contracts_officer    FOREIGN KEY (contact_officer) REFERENCES gov_employees(emp_id);
ALTER TABLE service_requests  ADD CONSTRAINT fk_service_requests_citizens   FOREIGN KEY (citizen_id)      REFERENCES citizens(citizen_id);
ALTER TABLE service_requests  ADD CONSTRAINT fk_service_requests_assignee   FOREIGN KEY (assigned_emp)    REFERENCES gov_employees(emp_id);

PROMPT --- FKs: Government done ---
PROMPT --- Foreign key constraints added ---

/* ============================================================================
   SECTION 5: SQL TYPES
   A schema-level collection type used below by usp_bulk_get_patient_appointments
   as the Oracle equivalent of a SQL Server table-valued parameter -- there is
   no direct TVP concept in PL/SQL, so a nested table of NUMBER is the
   idiomatic translation.
   ============================================================================ */

CREATE OR REPLACE TYPE num_id_list AS TABLE OF NUMBER;
/

PROMPT --- SQL types created ---

/* ============================================================================
   SECTION 6: FUNCTIONS -- 6 scalar helper functions, one per industry
   ============================================================================ */

-- Healthcare: exact age in years as of today, from a date of birth
CREATE OR REPLACE FUNCTION fn_patient_age (p_dob IN DATE)
RETURN NUMBER
IS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(TRUNC(SYSDATE), p_dob) / 12);
END fn_patient_age;
/

-- E-commerce: number of line items on a given order
CREATE OR REPLACE FUNCTION fn_order_item_count (p_order_id IN NUMBER)
RETURN NUMBER
IS
    v_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM order_items WHERE order_id = p_order_id;
    RETURN NVL(v_cnt, 0);
END fn_order_item_count;
/

-- Finance: current balance for an account
CREATE OR REPLACE FUNCTION fn_account_balance (p_account_id IN NUMBER)
RETURN NUMBER
IS
    v_bal NUMBER;
BEGIN
    SELECT balance INTO v_bal FROM accounts WHERE account_id = p_account_id;
    RETURN v_bal;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END fn_account_balance;
/

-- Retail: percent discount associated with a loyalty tier
CREATE OR REPLACE FUNCTION fn_loyalty_tier_discount (p_tier IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
    RETURN CASE UPPER(p_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 fn_loyalty_tier_discount;
/

-- Government: whether a permit has expired as of today (0/1 -- no native BOOLEAN in Oracle SQL)
CREATE OR REPLACE FUNCTION fn_permit_is_expired (p_expiry_date IN DATE)
RETURN NUMBER
IS
BEGIN
    IF p_expiry_date IS NOT NULL AND p_expiry_date < TRUNC(SYSDATE) THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END fn_permit_is_expired;
/

-- Manufacturing/IoT: days since a machine's last maintenance
CREATE OR REPLACE FUNCTION fn_machine_days_since_maintenance (p_machine_id IN NUMBER)
RETURN NUMBER
IS
    v_last DATE;
BEGIN
    SELECT last_maint_date INTO v_last FROM machines WHERE machine_id = p_machine_id;
    IF v_last IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN TRUNC(SYSDATE) - v_last;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;
END fn_machine_days_since_maintenance;
/

PROMPT --- Functions created ---

/* ============================================================================
   SECTION 7: STORED PROCEDURES -- 10 procedures across all six industries.
   Multi-row result sets use OUT SYS_REFCURSOR parameters, the standard
   PL/SQL equivalent of a SQL Server procedure returning a SELECT directly.
   ============================================================================ */

-- 1. Healthcare: full clinical history for a patient (3 result sets via 3 cursors)
CREATE OR REPLACE PROCEDURE usp_get_patient_history (
    p_patient_id    IN  NUMBER,
    p_appointments  OUT SYS_REFCURSOR,
    p_diagnoses     OUT SYS_REFCURSOR,
    p_prescriptions OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_appointments FOR
        SELECT appointment_id, appt_date, status, reason
        FROM appointments WHERE patient_id = p_patient_id ORDER BY appt_date DESC;

    OPEN p_diagnoses FOR
        SELECT diagnosis_id, icd10_code, description, diagnosed_date
        FROM diagnoses WHERE patient_id = p_patient_id ORDER BY diagnosed_date DESC;

    OPEN p_prescriptions FOR
        SELECT rx_id, drug_name, dosage, prescribed_date, status
        FROM prescriptions WHERE patient_id = p_patient_id ORDER BY prescribed_date DESC;
END usp_get_patient_history;
/

-- 2. Healthcare: file a new insurance claim (no provider_id column on insurance_claims in this schema)
CREATE OR REPLACE PROCEDURE usp_add_insurance_claim (
    p_patient_id     IN  NUMBER,
    p_claim_date     IN  DATE,
    p_service_date   IN  DATE,
    p_diagnosis_code IN  VARCHAR2,
    p_billed_amount  IN  NUMBER,
    p_insurance_co   IN  VARCHAR2,
    p_new_claim_id   OUT NUMBER
)
IS
BEGIN
    INSERT INTO insurance_claims (patient_id, claim_date, service_date, diagnosis_code, billed_amount, insurance_co, status)
    VALUES (p_patient_id, p_claim_date, p_service_date, p_diagnosis_code, p_billed_amount, p_insurance_co, 'SUBMITTED')
    RETURNING claim_id INTO p_new_claim_id;
END usp_add_insurance_claim;
/

-- 3. E-commerce: lifetime order summary for a customer
CREATE OR REPLACE PROCEDURE usp_get_customer_order_summary (
    p_customer_id IN  NUMBER,
    p_result      OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT
            c.customer_id, c.first_name, c.last_name,
            COUNT(o.order_id)            AS total_orders,
            NVL(SUM(o.total_amount), 0)  AS lifetime_value,
            MAX(o.order_date)            AS last_order_date
        FROM customers c
        LEFT JOIN orders o ON o.customer_id = c.customer_id
        WHERE c.customer_id = p_customer_id
        GROUP BY c.customer_id, c.first_name, c.last_name;
END usp_get_customer_order_summary;
/

-- 4. Retail: apply a store promotion discount to a POS transaction total
--    (this schema's promotions table is Retail-scoped -- store_id, promo_type,
--    discount_value/discount_pct -- and ties to pos_transactions.promo_id, not
--    to e-commerce orders, which have no promotion linkage column)
CREATE OR REPLACE PROCEDURE usp_apply_promotion (
    p_promo_code IN VARCHAR2,
    p_pos_txn_id IN NUMBER
)
IS
    v_promo_type     promotions.promo_type%TYPE;
    v_discount_value promotions.discount_value%TYPE;
    v_discount_pct   promotions.discount_pct%TYPE;
    v_active         promotions.active%TYPE;
BEGIN
    SELECT promo_type, discount_value, discount_pct, active
      INTO v_promo_type, v_discount_value, v_discount_pct, v_active
      FROM promotions
     WHERE promo_code = p_promo_code;

    IF v_active IS NULL OR v_active = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Promotion code ' || p_promo_code || ' not found or inactive.');
    END IF;

    UPDATE pos_transactions
       SET total_amount = CASE WHEN v_promo_type = 'PERCENT_OFF'
                                THEN ROUND(total_amount * (1 - NVL(v_discount_pct, 0) / 100), 2)
                                ELSE GREATEST(total_amount - NVL(v_discount_value, 0), 0)
                           END
     WHERE pos_txn_id = p_pos_txn_id;

    UPDATE promotions SET times_used = times_used + 1 WHERE promo_code = p_promo_code;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20001, 'Promotion code ' || p_promo_code || ' not found or inactive.');
END usp_apply_promotion;
/

-- 5. Finance: statement of transactions on an account within a date range
CREATE OR REPLACE PROCEDURE usp_get_account_statement (
    p_account_id IN  NUMBER,
    p_start_date IN  TIMESTAMP,
    p_end_date   IN  TIMESTAMP,
    p_result     OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT txn_id, txn_date, txn_type, amount, merchant_name, balance_after
        FROM transactions
        WHERE account_id = p_account_id AND txn_date BETWEEN p_start_date AND p_end_date
        ORDER BY txn_date;
END usp_get_account_statement;
/

-- 6. Finance: manually raise a fraud alert against a transaction
CREATE OR REPLACE PROCEDURE usp_flag_fraud_alert (
    p_txn_id       IN  NUMBER,
    p_account_id   IN  NUMBER,
    p_alert_type   IN  VARCHAR2,
    p_risk_score   IN  NUMBER,
    p_new_alert_id OUT NUMBER
)
IS
BEGIN
    INSERT INTO fraud_alerts (txn_id, account_id, alert_date, alert_type, risk_score, status)
    VALUES (p_txn_id, p_account_id, SYSTIMESTAMP, p_alert_type, p_risk_score, 'OPEN')
    RETURNING alert_id INTO p_new_alert_id;
END usp_flag_fraud_alert;
/

-- 7. Manufacturing/IoT: health summary (reading count, anomaly count, average value) for a machine
CREATE OR REPLACE PROCEDURE usp_get_machine_health_summary (
    p_machine_id IN  NUMBER,
    p_result     OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT
            m.machine_id, m.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 machines m
        LEFT JOIN sensor_readings sr ON sr.machine_id = m.machine_id
        WHERE m.machine_id = p_machine_id
        GROUP BY m.machine_id, m.name, m.status;
END usp_get_machine_health_summary;
/

-- 8. Retail: sales summary for a store on a given calendar day
CREATE OR REPLACE PROCEDURE usp_get_store_sales_summary (
    p_store_id   IN  NUMBER,
    p_sales_date IN  DATE,
    p_result     OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT
            s.store_id, s.name,
            COUNT(pt.pos_txn_id)          AS transaction_count,
            NVL(SUM(pt.total_amount), 0)  AS total_sales
        FROM stores s
        LEFT JOIN pos_transactions pt
               ON pt.store_id = s.store_id
              AND TRUNC(pt.txn_ts) = p_sales_date
        WHERE s.store_id = p_store_id
        GROUP BY s.store_id, s.name;
END usp_get_store_sales_summary;
/

-- 9. Government: full civic service history for a citizen (3 result sets via 3 cursors)
CREATE OR REPLACE PROCEDURE usp_get_citizen_service_history (
    p_citizen_id IN  NUMBER,
    p_permits    OUT SYS_REFCURSOR,
    p_violations OUT SYS_REFCURSOR,
    p_requests   OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_permits FOR
        SELECT permit_id, permit_type, status, issued_date, expiry_date
        FROM permits WHERE citizen_id = p_citizen_id;

    OPEN p_violations FOR
        SELECT violation_id, violation_code, fine_amount, status, occurred_date
        FROM violations WHERE citizen_id = p_citizen_id;

    OPEN p_requests FOR
        SELECT request_id, request_type, status, submitted_at
        FROM service_requests WHERE citizen_id = p_citizen_id;
END usp_get_citizen_service_history;
/

-- 10. Healthcare: bulk-fetch recent appointments for a set of patients at once.
--     Replaces the SQL Server reference's table-valued parameter with the
--     num_id_list nested-table type declared in Section 5 -- the idiomatic
--     Oracle PL/SQL equivalent of a TVP, joined via the TABLE() collection operator.
CREATE OR REPLACE PROCEDURE usp_bulk_get_patient_appointments (
    p_patient_ids IN  num_id_list,
    p_result      OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_result FOR
        SELECT a.patient_id, a.appointment_id, a.appt_date, a.status
        FROM appointments a
        WHERE a.patient_id IN (SELECT COLUMN_VALUE FROM TABLE(p_patient_ids))
        ORDER BY a.patient_id, a.appt_date DESC;
END usp_bulk_get_patient_appointments;
/

PROMPT --- Stored procedures created ---

/* ============================================================================
   SECTION 8: VIEWS -- 6 reporting views joining across tables, one per industry
   ============================================================================ */

-- Healthcare: appointment schedule with patient and provider names
CREATE OR REPLACE VIEW vw_patient_appointment_summary AS
SELECT
    a.appointment_id,
    p.patient_id,
    p.first_name || ' ' || p.last_name   AS patient_name,
    pr.provider_id,
    pr.first_name || ' ' || pr.last_name AS provider_name,
    pr.specialty,
    a.appt_date,
    a.status,
    a.appt_type
FROM appointments a
JOIN patients p   ON p.patient_id   = a.patient_id
JOIN providers pr ON pr.provider_id = a.provider_id;

-- E-commerce: order totals with customer and line-item detail
CREATE OR REPLACE VIEW vw_order_details AS
SELECT
    o.order_id,
    c.customer_id,
    c.first_name || ' ' || c.last_name AS customer_name,
    o.status        AS order_status,
    o.total_amount  AS order_total,
    oi.item_id,
    pd.product_id,
    pd.name         AS product_name,
    oi.quantity,
    oi.unit_price,
    oi.line_total
FROM orders o
JOIN customers c    ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products pd    ON pd.product_id = oi.product_id;

-- Finance: per-account transaction activity and fraud-alert exposure
CREATE OR REPLACE VIEW vw_account_transaction_summary AS
SELECT
    ac.account_id,
    ac.account_number,
    ac.owner_name,
    ac.status AS account_status,
    ac.balance,
    COUNT(t.txn_id)                                                                AS transaction_count,
    NVL(SUM(CASE WHEN t.txn_type = 'DEBIT'  THEN t.amount ELSE 0 END), 0)          AS total_debits,
    NVL(SUM(CASE WHEN t.txn_type = 'CREDIT' THEN t.amount ELSE 0 END), 0)          AS total_credits,
    (SELECT COUNT(*) FROM fraud_alerts fa WHERE fa.account_id = ac.account_id)     AS fraud_alert_count
FROM accounts ac
LEFT JOIN transactions t ON t.account_id = ac.account_id
GROUP BY ac.account_id, ac.account_number, ac.owner_name, ac.status, ac.balance;

-- Manufacturing/IoT: machine health rollup from sensor readings
CREATE OR REPLACE VIEW vw_machine_sensor_health AS
SELECT
    m.machine_id,
    m.name    AS machine_name,
    m.status  AS machine_status,
    COUNT(sr.reading_id)                             AS reading_count,
    SUM(CASE WHEN sr.anomaly = 1 THEN 1 ELSE 0 END)  AS anomaly_count,
    ROUND(AVG(sr.value), 2)                          AS avg_reading_value,
    MAX(sr.reading_ts)                               AS last_reading_at
FROM machines m
LEFT JOIN sensor_readings sr ON sr.machine_id = m.machine_id
GROUP BY m.machine_id, m.name, m.status;

-- Retail: store sales performance
CREATE OR REPLACE VIEW vw_store_sales_summary AS
SELECT
    s.store_id,
    s.name AS store_name,
    s.city,
    s.state,
    COUNT(pt.pos_txn_id)                                                     AS transaction_count,
    NVL(SUM(CASE WHEN pt.voided = 0 THEN pt.total_amount ELSE 0 END), 0)     AS total_sales
FROM stores s
LEFT JOIN pos_transactions pt ON pt.store_id = s.store_id
GROUP BY s.store_id, s.name, s.city, s.state;

-- Government: per-citizen civic activity rollup (permits, violations, service requests)
CREATE OR REPLACE VIEW vw_citizen_government_summary AS
SELECT
    c.citizen_id,
    c.first_name || ' ' || c.last_name AS citizen_name,
    c.city,
    (SELECT COUNT(*) FROM permits p WHERE p.citizen_id = c.citizen_id)              AS permit_count,
    (SELECT COUNT(*) FROM violations v WHERE v.citizen_id = c.citizen_id)           AS violation_count,
    NVL((SELECT SUM(v.fine_amount - v.paid_amount) FROM violations v
          WHERE v.citizen_id = c.citizen_id AND v.status <> 'PAID'), 0)             AS unpaid_fines,
    (SELECT COUNT(*) FROM service_requests sr WHERE sr.citizen_id = c.citizen_id)   AS service_request_count
FROM citizens c;

PROMPT --- Views created ---

/* ============================================================================
   SECTION 9: TRIGGERS -- 4 triggers spanning fraud auto-flagging, audit
   logging on two different sensitive tables, and sensor anomaly detection
   ============================================================================ */

-- Finance: auto-flag any newly inserted transaction over $10,000 for fraud review
CREATE OR REPLACE TRIGGER trg_transactions_flag_large
AFTER INSERT ON transactions
FOR EACH ROW
WHEN (NEW.amount > 10000)
BEGIN
    INSERT INTO fraud_alerts (txn_id, account_id, alert_date, alert_type, risk_score, description, status)
    VALUES (:NEW.txn_id, :NEW.account_id, SYSTIMESTAMP, 'LARGE_AMOUNT', 70.00,
            'Auto-flagged by trigger: transaction amount ' || :NEW.amount || ' exceeds $10,000 threshold',
            'OPEN');
END trg_transactions_flag_large;
/

-- Finance: audit-log any change to an account balance (sensitive-table audit trail)
CREATE OR REPLACE TRIGGER trg_accounts_audit_balance
AFTER UPDATE ON accounts
FOR EACH ROW
WHEN (NVL(OLD.balance, -1) != NVL(NEW.balance, -1))
BEGIN
    INSERT INTO audit_log (log_ts, db_user, table_name, operation, row_id, old_values, new_values, app_name)
    VALUES (SYSTIMESTAMP, USER, 'ACCOUNTS', 'UPDATE', :NEW.account_id,
            'balance:' || :OLD.balance, 'balance:' || :NEW.balance, 'trg_accounts_audit_balance');
END trg_accounts_audit_balance;
/

-- Healthcare: audit-log any status change on an insurance claim (sensitive-table audit trail)
CREATE OR REPLACE TRIGGER trg_insurance_claims_audit
AFTER UPDATE ON insurance_claims
FOR EACH ROW
WHEN (NVL(OLD.status, 'x') != NVL(NEW.status, 'x'))
BEGIN
    INSERT INTO audit_log (log_ts, db_user, table_name, operation, row_id, old_values, new_values, app_name)
    VALUES (SYSTIMESTAMP, USER, 'INSURANCE_CLAIMS', 'UPDATE', :NEW.claim_id,
            'status:' || :OLD.status, 'status:' || :NEW.status, 'trg_insurance_claims_audit');
END trg_insurance_claims_audit;
/

-- Manufacturing/IoT: flag a sensor reading as an anomaly if it falls outside the
-- configured min/max threshold for that sensor, before the row is written
CREATE OR REPLACE TRIGGER trg_sensor_readings_detect_anomaly
BEFORE INSERT ON sensor_readings
FOR EACH ROW
DECLARE
    v_min sensors.min_threshold%TYPE;
    v_max sensors.max_threshold%TYPE;
BEGIN
    SELECT min_threshold, max_threshold INTO v_min, v_max
      FROM sensors WHERE sensor_id = :NEW.sensor_id;

    IF (v_min IS NOT NULL AND :NEW.value < v_min) OR (v_max IS NOT NULL AND :NEW.value > v_max) THEN
        :NEW.anomaly := 1;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
END trg_sensor_readings_detect_anomaly;
/

PROMPT --- Triggers created ---

/* ============================================================================
   BUILD COMPLETE
   ============================================================================ */

PROMPT ===========================================================
PROMPT EB_SampleDB (Oracle) -- build complete
PROMPT ===========================================================

SELECT 'EB_SampleDB build complete' AS status FROM dual;
