/* ============================================================================
   EB_SampleDB — PostgreSQL 15/16 Sample Database
   EndriasBridge (endriasbridge.com) — Database Migration Tool
   ============================================================================

   WHAT THIS IS
   ------------
   A single, self-contained, hand-built sample database covering six
   industries — Healthcare, E-commerce, Finance, Manufacturing/IoT, Retail,
   and Government — used to demonstrate EndriasBridge's schema and data
   migration/conversion capabilities against a realistic multi-domain schema.

   This is a SCALED-DOWN, illustrative dataset (37 tables, roughly 700-750
   hand-written rows total) meant to be readable, easy to inspect, and fast
   to load. It intentionally mirrors the table/column names and types
   produced by this project's `generate_postgresql.py` generator, which can
   instead produce a full ~50 GB, tens-of-millions-of-rows version of the
   same logical schema (via Faker-driven bulk generation, run separately)
   for anyone who wants a heavier-duty demo or load-testing dataset. Use
   this script when you want something quick to read, edit, and run
   end-to-end; use generate_postgresql.py when you want volume.

   This script also adds an "enrichment layer" beyond raw tables: foreign
   key constraints, scalar functions, stored procedures, views, and
   triggers — translated to PostgreSQL 15/16 syntax from the SQL Server
   reference version of the same enrichment layer
   (EB_SampleDB_enrich_sqlserver.sql), adapted where SQL Server features
   have no direct PostgreSQL equivalent (e.g. PostgreSQL has no user-defined
   TABLE TYPE / table-valued parameter concept, so
   sp_bulk_get_patient_appointments below takes a BIGINT[] array instead;
   multi-result-set procedures use INOUT refcursor parameters, which is the
   idiomatic PostgreSQL pattern for that use case).

   HOW TO RUN
   ----------
   PostgreSQL does not allow CREATE DATABASE inside a multi-statement
   script/transaction the way some other engines do, so create the database
   FIRST, as a separate manual step:

       psql -U postgres -c "CREATE DATABASE eb_sampledb;"

   Then run this script AGAINST that database:

       psql -U postgres -d eb_sampledb -f EB_SampleDB_postgresql.sql

   Requirements: PostgreSQL 15 or 16, a role with CREATE privileges on the
   target database (the default `postgres` superuser is fine). Everything
   below this point assumes you are already connected to `eb_sampledb`.

   SCRIPT ORDER
   ------------
     1. CREATE TABLE (all 37 tables, 6 industries)
     2. Secondary indexes (supporting FK lookups and common filters)
     3. INSERT statements (hand-written sample data, parent tables first —
        every table is loaded in strict dependency order so FK values below
        always point at rows already inserted above; surrogate keys use
        GENERATED ALWAYS AS IDENTITY and are allocated sequentially
        starting at 1 per table)
     4. FOREIGN KEY constraints (added after data load — avoids
        insert-order contention and validates referential integrity in one
        pass at the end)
     5. Scalar FUNCTIONs (6)
     6. Stored PROCEDUREs (10)
     7. VIEWs (6) — added beyond the SQL Server reference as useful
        cross-table demo queries
     8. TRIGGERs (3) with their supporting trigger FUNCTIONs — added
        beyond the SQL Server reference to show PostgreSQL trigger syntax
        (fraud auto-flagging, audit logging on sensitive-table updates)

   NOTE ON "table types": SQL Server's enrichment script defines two
   user-defined TABLE TYPEs (TVPs) for bulk parameter passing. PostgreSQL
   has no equivalent concept, so it is intentionally omitted here — the one
   procedure that used a TVP (usp_BulkGetPatientAppointments) is
   reimplemented below as sp_bulk_get_patient_appointments(BIGINT[], ...).

   NOTE ON IDENTIFIERS: all identifiers are lowercase and unquoted
   throughout, for consistency with PostgreSQL's default case-folding
   behavior.
============================================================================ */

-- ============================================================================
-- 1. TABLES
-- ============================================================================

-- ------------------------------------------------------------------
-- HEALTHCARE
-- ------------------------------------------------------------------

CREATE TABLE patients (
    patient_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    dob             DATE NOT NULL,
    gender          VARCHAR(10),
    ssn             VARCHAR(20),
    email           VARCHAR(200),
    phone           VARCHAR(30),
    address         VARCHAR(300),
    city            VARCHAR(100),
    state           VARCHAR(50),
    zip             VARCHAR(20),
    insurance_id    VARCHAR(50),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE providers (
    provider_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    specialty       VARCHAR(100),
    npi             VARCHAR(20),
    email           VARCHAR(200),
    phone           VARCHAR(30),
    facility        VARCHAR(200),
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE appointments (
    appointment_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    provider_id     BIGINT NOT NULL,
    scheduled_at    TIMESTAMP NOT NULL,
    status          VARCHAR(30) NOT NULL,
    visit_type      VARCHAR(50),
    notes           TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE diagnoses (
    diagnosis_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    provider_id     BIGINT NOT NULL,
    icd10_code      VARCHAR(20) NOT NULL,
    description     VARCHAR(300),
    diagnosed_at    TIMESTAMP NOT NULL,
    severity        VARCHAR(20),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE lab_results (
    result_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    provider_id     BIGINT NOT NULL,
    test_name       VARCHAR(100) NOT NULL,
    test_code       VARCHAR(30),
    result_value    NUMERIC(12,4),
    unit            VARCHAR(30),
    normal_low      NUMERIC(12,4),
    normal_high     NUMERIC(12,4),
    abnormal        BOOLEAN,
    collected_at    TIMESTAMP NOT NULL,
    resulted_at     TIMESTAMP NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE insurance_claims (
    claim_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    provider_id     BIGINT NOT NULL,
    claim_date      DATE NOT NULL,
    service_date    DATE NOT NULL,
    icd10_code      VARCHAR(20),
    cpt_code        VARCHAR(20),
    billed_amount   NUMERIC(12,2),
    allowed_amount  NUMERIC(12,2),
    paid_amount     NUMERIC(12,2),
    status          VARCHAR(30),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE prescriptions (
    prescription_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    patient_id      BIGINT NOT NULL,
    provider_id     BIGINT NOT NULL,
    drug_name       VARCHAR(200) NOT NULL,
    ndc_code        VARCHAR(20),
    dosage          VARCHAR(50),
    frequency       VARCHAR(50),
    days_supply     INT,
    refills         INT,
    prescribed_at   TIMESTAMP NOT NULL,
    filled_at       TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ------------------------------------------------------------------
-- E-COMMERCE
-- ------------------------------------------------------------------

CREATE TABLE categories (
    category_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    parent_id       BIGINT,
    slug            VARCHAR(200),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    product_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id     BIGINT NOT NULL,
    name            VARCHAR(300) NOT NULL,
    sku             VARCHAR(100),
    description     TEXT,
    price           NUMERIC(12,2) NOT NULL,
    cost            NUMERIC(12,2),
    weight_kg       NUMERIC(8,3),
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE customers (
    customer_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email           VARCHAR(200) NOT NULL,
    first_name      VARCHAR(100),
    last_name       VARCHAR(100),
    phone           VARCHAR(30),
    address         VARCHAR(300),
    city            VARCHAR(100),
    state           VARCHAR(50),
    zip             VARCHAR(20),
    country         VARCHAR(50),
    registered_at   TIMESTAMP NOT NULL DEFAULT NOW(),
    last_login      TIMESTAMP
);

CREATE TABLE orders (
    order_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id     BIGINT NOT NULL,
    status          VARCHAR(30) NOT NULL,
    subtotal        NUMERIC(12,2),
    tax             NUMERIC(12,2),
    shipping        NUMERIC(12,2),
    total           NUMERIC(12,2),
    currency        VARCHAR(10) NOT NULL DEFAULT 'USD',
    placed_at       TIMESTAMP NOT NULL,
    shipped_at      TIMESTAMP,
    delivered_at    TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE order_items (
    item_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id        BIGINT NOT NULL,
    product_id      BIGINT NOT NULL,
    quantity        INT NOT NULL,
    unit_price      NUMERIC(12,2) NOT NULL,
    discount        NUMERIC(12,2) DEFAULT 0,
    line_total      NUMERIC(12,2) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE reviews (
    review_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id      BIGINT NOT NULL,
    customer_id     BIGINT NOT NULL,
    rating          SMALLINT NOT NULL,
    title           VARCHAR(300),
    body            TEXT,
    verified        BOOLEAN DEFAULT FALSE,
    helpful_votes   INT DEFAULT 0,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE cart_events (
    event_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id     BIGINT NOT NULL,
    product_id      BIGINT NOT NULL,
    event_type      VARCHAR(30) NOT NULL,
    quantity        INT DEFAULT 1,
    session_id      VARCHAR(100),
    ip_address      VARCHAR(50),
    user_agent      VARCHAR(500),
    occurred_at     TIMESTAMP NOT NULL
);

-- ------------------------------------------------------------------
-- FINANCE
-- ------------------------------------------------------------------

CREATE TABLE accounts (
    account_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_number  VARCHAR(30) NOT NULL,
    account_type    VARCHAR(30) NOT NULL,
    customer_name   VARCHAR(200),
    email           VARCHAR(200),
    balance         NUMERIC(18,4) NOT NULL DEFAULT 0,
    currency        VARCHAR(10) NOT NULL DEFAULT 'USD',
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    opened_at       TIMESTAMP NOT NULL,
    closed_at       TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE transactions (
    transaction_id  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id      BIGINT NOT NULL,
    transaction_type VARCHAR(30) NOT NULL,
    amount          NUMERIC(18,4) NOT NULL,
    currency        VARCHAR(10) NOT NULL DEFAULT 'USD',
    balance_after   NUMERIC(18,4),
    description     VARCHAR(500),
    reference_id    VARCHAR(100),
    channel         VARCHAR(30),
    status          VARCHAR(20) NOT NULL DEFAULT 'completed',
    transacted_at   TIMESTAMP NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE loans (
    loan_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id      BIGINT NOT NULL,
    loan_type       VARCHAR(50) NOT NULL,
    principal       NUMERIC(18,2) NOT NULL,
    interest_rate   NUMERIC(8,4) NOT NULL,
    term_months     INT NOT NULL,
    monthly_payment NUMERIC(18,2),
    balance         NUMERIC(18,2),
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    originated_at   DATE NOT NULL,
    maturity_date   DATE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE fraud_alerts (
    alert_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id      BIGINT NOT NULL,
    transaction_id  BIGINT,
    rule_name       VARCHAR(100),
    risk_score      NUMERIC(5,2),
    status          VARCHAR(20) NOT NULL DEFAULT 'open',
    description     VARCHAR(500),
    alerted_at      TIMESTAMP NOT NULL,
    resolved_at     TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE market_prices (
    price_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    ticker          VARCHAR(20) NOT NULL,
    price_open      NUMERIC(18,6),
    price_high      NUMERIC(18,6),
    price_low       NUMERIC(18,6),
    price_close     NUMERIC(18,6) NOT NULL,
    volume          BIGINT,
    market          VARCHAR(30),
    recorded_at     TIMESTAMP NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE audit_log (
    log_id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id         BIGINT,
    action          VARCHAR(100) NOT NULL,
    table_name      VARCHAR(100),
    record_id       BIGINT,
    old_value       TEXT,
    new_value       TEXT,
    ip_address      VARCHAR(50),
    user_agent      VARCHAR(300),
    occurred_at     TIMESTAMP NOT NULL
);

-- ------------------------------------------------------------------
-- MANUFACTURING / IoT
-- ------------------------------------------------------------------

CREATE TABLE machines (
    machine_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    model           VARCHAR(100),
    serial_number   VARCHAR(100),
    location        VARCHAR(200),
    plant           VARCHAR(100),
    status          VARCHAR(30) NOT NULL DEFAULT 'running',
    installed_at    DATE,
    last_service    DATE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE sensors (
    sensor_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    machine_id      BIGINT NOT NULL,
    sensor_type     VARCHAR(100) NOT NULL,
    unit            VARCHAR(30),
    min_threshold   NUMERIC(12,4),
    max_threshold   NUMERIC(12,4),
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    installed_at    TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE sensor_readings (
    reading_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sensor_id       BIGINT NOT NULL,
    machine_id      BIGINT NOT NULL,
    value           NUMERIC(14,6) NOT NULL,
    quality         SMALLINT DEFAULT 192,
    alarm           BOOLEAN DEFAULT FALSE,
    recorded_at     TIMESTAMP NOT NULL
);

CREATE TABLE production_batches (
    batch_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    machine_id      BIGINT NOT NULL,
    product_code    VARCHAR(100) NOT NULL,
    quantity        INT NOT NULL,
    unit            VARCHAR(30),
    status          VARCHAR(30) NOT NULL DEFAULT 'in_progress',
    started_at      TIMESTAMP NOT NULL,
    completed_at    TIMESTAMP,
    operator_id     BIGINT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE quality_checks (
    check_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    batch_id        BIGINT NOT NULL,
    machine_id      BIGINT NOT NULL,
    check_type      VARCHAR(100) NOT NULL,
    result          VARCHAR(20) NOT NULL,
    score           NUMERIC(5,2),
    defect_count    INT DEFAULT 0,
    inspector_id    BIGINT,
    checked_at      TIMESTAMP NOT NULL,
    notes           TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ------------------------------------------------------------------
-- RETAIL
-- ------------------------------------------------------------------

CREATE TABLE stores (
    store_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    code            VARCHAR(20),
    address         VARCHAR(300),
    city            VARCHAR(100),
    state           VARCHAR(50),
    zip             VARCHAR(20),
    region          VARCHAR(100),
    store_type      VARCHAR(50),
    opened_date     DATE,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE inventory (
    inventory_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    store_id        BIGINT NOT NULL,
    product_id      BIGINT NOT NULL,
    quantity_on_hand INT NOT NULL DEFAULT 0,
    quantity_reserved INT NOT NULL DEFAULT 0,
    reorder_point   INT DEFAULT 10,
    reorder_qty     INT DEFAULT 50,
    last_counted    TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE pos_transactions (
    pos_tx_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    store_id        BIGINT NOT NULL,
    register_id     INT,
    cashier_id      BIGINT,
    customer_id     BIGINT,
    subtotal        NUMERIC(12,2) NOT NULL,
    tax             NUMERIC(12,2),
    discount        NUMERIC(12,2) DEFAULT 0,
    total           NUMERIC(12,2) NOT NULL,
    payment_method  VARCHAR(30),
    status          VARCHAR(20) NOT NULL DEFAULT 'completed',
    transacted_at   TIMESTAMP NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE pos_items (
    pos_item_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pos_tx_id       BIGINT NOT NULL,
    product_id      BIGINT NOT NULL,
    quantity        INT NOT NULL,
    unit_price      NUMERIC(12,2) NOT NULL,
    discount        NUMERIC(12,2) DEFAULT 0,
    line_total      NUMERIC(12,2) NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE loyalty_members (
    member_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email           VARCHAR(200) NOT NULL,
    first_name      VARCHAR(100),
    last_name       VARCHAR(100),
    phone           VARCHAR(30),
    tier            VARCHAR(30) DEFAULT 'bronze',
    points          INT NOT NULL DEFAULT 0,
    lifetime_spend  NUMERIC(14,2) DEFAULT 0,
    enrolled_at     TIMESTAMP NOT NULL,
    last_activity   TIMESTAMP,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE promotions (
    promotion_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    promo_code      VARCHAR(50),
    discount_type   VARCHAR(30) NOT NULL,
    discount_value  NUMERIC(10,4) NOT NULL,
    min_purchase    NUMERIC(12,2),
    max_discount    NUMERIC(12,2),
    start_date      DATE NOT NULL,
    end_date        DATE,
    usage_limit     INT,
    times_used      INT DEFAULT 0,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ------------------------------------------------------------------
-- GOVERNMENT
-- ------------------------------------------------------------------

CREATE TABLE citizens (
    citizen_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    dob             DATE NOT NULL,
    gender          VARCHAR(10),
    national_id     VARCHAR(50),
    email           VARCHAR(200),
    phone           VARCHAR(30),
    address         VARCHAR(300),
    city            VARCHAR(100),
    state           VARCHAR(50),
    zip             VARCHAR(20),
    registered_at   TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE permits (
    permit_id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    citizen_id      BIGINT NOT NULL,
    permit_type     VARCHAR(100) NOT NULL,
    permit_number   VARCHAR(50),
    description     VARCHAR(500),
    status          VARCHAR(30) NOT NULL DEFAULT 'pending',
    issued_at       DATE,
    expires_at      DATE,
    fee_paid        NUMERIC(12,2),
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE violations (
    violation_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    citizen_id      BIGINT NOT NULL,
    violation_type  VARCHAR(100) NOT NULL,
    code            VARCHAR(50),
    description     VARCHAR(500),
    fine_amount     NUMERIC(12,2),
    paid_amount     NUMERIC(12,2) DEFAULT 0,
    status          VARCHAR(30) NOT NULL DEFAULT 'unpaid',
    occurred_at     TIMESTAMP NOT NULL,
    due_date        DATE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE public_contracts (
    contract_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title           VARCHAR(300) NOT NULL,
    agency          VARCHAR(200),
    vendor_name     VARCHAR(200),
    contract_number VARCHAR(50),
    contract_type   VARCHAR(50),
    value           NUMERIC(18,2),
    start_date      DATE,
    end_date        DATE,
    status          VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE service_requests (
    request_id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    citizen_id      BIGINT,
    category        VARCHAR(100) NOT NULL,
    subcategory     VARCHAR(100),
    description     TEXT,
    priority        VARCHAR(20) DEFAULT 'normal',
    status          VARCHAR(30) NOT NULL DEFAULT 'open',
    assigned_to     BIGINT,
    submitted_at    TIMESTAMP NOT NULL,
    resolved_at     TIMESTAMP,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE gov_employees (
    employee_id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name      VARCHAR(100) NOT NULL,
    last_name       VARCHAR(100) NOT NULL,
    email           VARCHAR(200),
    department      VARCHAR(200),
    title           VARCHAR(200),
    grade           VARCHAR(20),
    salary          NUMERIC(14,2),
    hire_date       DATE,
    active          BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMP NOT NULL DEFAULT NOW()
);

-- ============================================================================
-- 2. INDEXES
-- ============================================================================

-- Healthcare
CREATE INDEX idx_appointments_patient ON appointments(patient_id);
CREATE INDEX idx_appointments_provider ON appointments(provider_id);
CREATE INDEX idx_appointments_scheduled ON appointments(scheduled_at);
CREATE INDEX idx_diagnoses_patient ON diagnoses(patient_id);
CREATE INDEX idx_lab_results_patient ON lab_results(patient_id);
CREATE INDEX idx_lab_results_collected ON lab_results(collected_at);
CREATE INDEX idx_insurance_claims_patient ON insurance_claims(patient_id);
CREATE INDEX idx_prescriptions_patient ON prescriptions(patient_id);

-- E-commerce
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_placed_at ON orders(placed_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_cart_events_customer ON cart_events(customer_id);
CREATE INDEX idx_cart_events_occurred ON cart_events(occurred_at);
CREATE INDEX idx_cart_events_product ON cart_events(product_id);

-- Finance
CREATE INDEX idx_transactions_account ON transactions(account_id);
CREATE INDEX idx_transactions_transacted ON transactions(transacted_at);
CREATE INDEX idx_fraud_alerts_account ON fraud_alerts(account_id);
CREATE INDEX idx_market_prices_ticker ON market_prices(ticker);
CREATE INDEX idx_market_prices_recorded ON market_prices(recorded_at);
CREATE INDEX idx_audit_log_occurred ON audit_log(occurred_at);
CREATE INDEX idx_audit_log_table ON audit_log(table_name);

-- Manufacturing / IoT
CREATE INDEX idx_sensors_machine ON sensors(machine_id);
CREATE INDEX idx_sensor_readings_sensor ON sensor_readings(sensor_id);
CREATE INDEX idx_sensor_readings_recorded ON sensor_readings(recorded_at);
CREATE INDEX idx_sensor_readings_machine ON sensor_readings(machine_id);
CREATE INDEX idx_quality_checks_batch ON quality_checks(batch_id);

-- Retail
CREATE INDEX idx_inventory_store ON inventory(store_id);
CREATE INDEX idx_pos_transactions_store ON pos_transactions(store_id);
CREATE INDEX idx_pos_transactions_transacted ON pos_transactions(transacted_at);
CREATE INDEX idx_pos_items_pos_tx ON pos_items(pos_tx_id);

-- Government
CREATE INDEX idx_permits_citizen ON permits(citizen_id);
CREATE INDEX idx_violations_citizen ON violations(citizen_id);
CREATE INDEX idx_service_requests_citizen ON service_requests(citizen_id);
CREATE INDEX idx_service_requests_submitted ON service_requests(submitted_at);

-- ============================================================================
-- 3. DATA
-- ============================================================================

-- ------------------------------------------------------------------
-- HEALTHCARE DATA
-- ------------------------------------------------------------------

-- providers (10)
INSERT INTO providers (first_name, last_name, specialty, npi, email, phone, facility, active, created_at) VALUES
('James', 'Whitfield', 'Cardiology', '1023456789', 'j.whitfield@riverside-med.example', '555-201-1001', 'Riverside Medical Center', TRUE, '2015-03-12 08:00:00'),
('Elena', 'Cho', 'Orthopedics', '1023456790', 'e.cho@lakeside-ortho.example', '555-201-1002', 'Lakeside Orthopedics', TRUE, '2016-06-01 08:00:00'),
('Marcus', 'Bell', 'Neurology', '1023456791', 'm.bell@riverside-med.example', '555-201-1003', 'Riverside Medical Center', TRUE, '2014-01-20 08:00:00'),
('Priya', 'Nair', 'Pediatrics', '1023456792', 'p.nair@sunrise-peds.example', '555-201-1004', 'Sunrise Childrens Clinic', TRUE, '2017-09-15 08:00:00'),
('David', 'Kim', 'Family Medicine', '1023456793', 'd.kim@maplestreet-fp.example', '555-201-1005', 'Maple Street Family Practice', TRUE, '2012-11-03 08:00:00'),
('Sophia', 'Rossi', 'Dermatology', '1023456794', 's.rossi@clearview-derm.example', '555-201-1006', 'Clearview Dermatology', TRUE, '2018-02-27 08:00:00'),
('Omar', 'Haddad', 'Internal Medicine', '1023456795', 'o.haddad@riverside-med.example', '555-201-1007', 'Riverside Medical Center', TRUE, '2013-07-08 08:00:00'),
('Grace', 'Liu', 'Psychiatry', '1023456796', 'g.liu@harborview-bh.example', '555-201-1008', 'Harborview Behavioral Health', TRUE, '2019-04-22 08:00:00'),
('Nathan', 'Brooks', 'Emergency Medicine', '1023456797', 'n.brooks@stvincent.example', '555-201-1009', 'St. Vincent Hospital', TRUE, '2011-10-10 08:00:00'),
('Aisha', 'Patel', 'Oncology', '1023456798', 'a.patel@riverside-cancer.example', '555-201-1010', 'Riverside Cancer Institute', FALSE, '2020-01-05 08:00:00');

-- patients (20)
INSERT INTO patients (first_name, last_name, dob, gender, ssn, email, phone, address, city, state, zip, insurance_id, created_at) VALUES
('Michael', 'Anderson', '1985-04-12', 'M', '512-34-1001', 'michael.anderson@example.com', '555-301-2001', '412 Oakwood Dr', 'Springfield', 'IL', '62701', 'INS-10020001', '2019-05-01 09:00:00'),
('Sarah', 'Thompson', '1990-08-23', 'F', '512-34-1002', 'sarah.thompson@example.com', '555-301-2002', '18 Birchwood Ln', 'Springfield', 'IL', '62702', 'INS-10020002', '2019-05-02 09:10:00'),
('Robert', 'Garcia', '1978-01-30', 'M', '512-34-1003', 'robert.garcia@example.com', '555-301-2003', '77 Elm St', 'Chicago', 'IL', '60601', 'INS-10020003', '2019-06-15 09:20:00'),
('Linda', 'Martinez', '1965-11-05', 'F', '512-34-1004', 'linda.martinez@example.com', '555-301-2004', '900 Maple Ave', 'Chicago', 'IL', '60602', 'INS-10020004', '2019-06-20 09:30:00'),
('William', 'Lee', '2001-02-17', 'M', '512-34-1005', 'william.lee@example.com', '555-301-2005', '55 Cedar Ct', 'Naperville', 'IL', '60540', 'INS-10020005', '2020-01-10 09:40:00'),
('Jennifer', 'Walker', '1995-09-09', 'F', '512-34-1006', 'jennifer.walker@example.com', '555-301-2006', '23 Willow Way', 'Naperville', 'IL', '60540', 'INS-10020006', '2020-01-15 10:00:00'),
('Charles', 'Hall', '1972-06-28', 'M', '512-34-1007', 'charles.hall@example.com', '555-301-2007', '340 Pine St', 'Peoria', 'IL', '61601', 'INS-10020007', '2018-03-01 10:10:00'),
('Patricia', 'Young', '1988-12-19', 'F', '512-34-1008', 'patricia.young@example.com', '555-301-2008', '12 Aspen Rd', 'Peoria', 'IL', '61602', 'INS-10020008', '2018-03-05 10:20:00'),
('Joseph', 'King', '1959-03-03', 'M', '512-34-1009', 'joseph.king@example.com', '555-301-2009', '66 Chestnut Blvd', 'Rockford', 'IL', '61101', 'INS-10020009', '2017-11-11 10:30:00'),
('Barbara', 'Wright', '1993-07-14', 'F', '512-34-1010', 'barbara.wright@example.com', '555-301-2010', '5 Magnolia Dr', 'Rockford', 'IL', '61102', 'INS-10020010', '2021-02-02 10:40:00'),
('Thomas', 'Lopez', '1982-10-22', 'M', '512-34-1011', 'thomas.lopez@example.com', '555-301-2011', '88 Hickory Ln', 'Springfield', 'IL', '62703', 'INS-10020011', '2019-08-08 11:00:00'),
('Susan', 'Hill', '1970-05-16', 'F', '512-34-1012', 'susan.hill@example.com', '555-301-2012', '19 Sycamore Ave', 'Chicago', 'IL', '60603', 'INS-10020012', '2016-04-04 11:10:00'),
('Christopher', 'Scott', '1998-01-27', 'M', '512-34-1013', 'christopher.scott@example.com', '555-301-2013', '210 Poplar St', 'Naperville', 'IL', '60541', 'INS-10020013', '2022-06-06 11:20:00'),
('Nancy', 'Green', '1955-09-30', 'F', '512-34-1014', 'nancy.green@example.com', '555-301-2014', '31 Walnut Ct', 'Peoria', 'IL', '61603', 'INS-10020014', '2015-09-09 11:30:00'),
('Daniel', 'Adams', '1991-04-04', 'M', '512-34-1015', 'daniel.adams@example.com', '555-301-2015', '47 Spruce Rd', 'Rockford', 'IL', '61103', 'INS-10020015', '2020-10-10 11:40:00'),
('Karen', 'Baker', '1980-02-08', 'F', '512-34-1016', 'karen.baker@example.com', '555-301-2016', '63 Fir Ave', 'Springfield', 'IL', '62704', 'INS-10020016', '2019-12-12 11:50:00'),
('Mark', 'Nelson', '1968-11-11', 'M', '512-34-1017', 'mark.nelson@example.com', '555-301-2017', '9 Redwood Dr', 'Chicago', 'IL', '60604', 'INS-10020017', '2014-07-07 12:00:00'),
('Betty', 'Carter', '2005-06-06', 'F', '512-34-1018', 'betty.carter@example.com', '555-301-2018', '150 Sequoia St', 'Naperville', 'IL', '60542', 'INS-10020018', '2023-01-01 12:10:00'),
('Steven', 'Mitchell', '1975-08-19', 'M', '512-34-1019', 'steven.mitchell@example.com', '555-301-2019', '27 Dogwood Ln', 'Peoria', 'IL', '61604', 'INS-10020019', '2018-05-05 12:20:00'),
('Donna', 'Perez', '1962-03-25', 'F', '512-34-1020', 'donna.perez@example.com', '555-301-2020', '84 Juniper Ct', 'Rockford', 'IL', '61104', 'INS-10020020', '2013-02-02 12:30:00');

-- appointments (30) -- patient_id 1-20, provider_id 1-10
INSERT INTO appointments (patient_id, provider_id, scheduled_at, status, visit_type, notes, created_at) VALUES
(1, 1, '2024-01-10 09:00:00', 'completed', 'in_person', 'Routine cardiac checkup', '2024-01-05 10:00:00'),
(2, 5, '2024-01-11 10:00:00', 'completed', 'follow_up', 'Follow-up on blood pressure meds', '2024-01-06 10:00:00'),
(3, 3, '2024-01-12 11:00:00', 'completed', 'new_patient', NULL, '2024-01-07 10:00:00'),
(4, 7, '2024-01-15 09:30:00', 'no_show', 'in_person', NULL, '2024-01-08 10:00:00'),
(5, 5, '2024-01-16 13:00:00', 'completed', 'telehealth', 'Discussed lab results', '2024-01-09 10:00:00'),
(6, 4, '2024-01-17 14:00:00', 'completed', 'in_person', 'Annual pediatric visit', '2024-01-10 10:00:00'),
(7, 2, '2024-01-18 08:30:00', 'completed', 'follow_up', 'Knee pain follow-up', '2024-01-11 10:00:00'),
(8, 6, '2024-01-19 15:00:00', 'cancelled', 'in_person', 'Patient rescheduled', '2024-01-12 10:00:00'),
(9, 1, '2024-01-22 09:00:00', 'completed', 'in_person', NULL, '2024-01-15 10:00:00'),
(10, 8, '2024-01-23 11:30:00', 'completed', 'telehealth', 'Anxiety management', '2024-01-16 10:00:00'),
(11, 5, '2024-01-24 09:00:00', 'completed', 'in_person', NULL, '2024-01-17 10:00:00'),
(12, 3, '2024-01-25 10:00:00', 'completed', 'follow_up', 'Migraine follow-up', '2024-01-18 10:00:00'),
(13, 7, '2024-01-26 13:00:00', 'scheduled', 'new_patient', NULL, '2024-01-19 10:00:00'),
(14, 10, '2024-01-29 09:00:00', 'completed', 'in_person', 'Oncology consult', '2024-01-22 10:00:00'),
(15, 9, '2024-01-30 20:00:00', 'completed', 'in_person', 'ER visit for laceration', '2024-01-23 10:00:00'),
(16, 5, '2024-02-01 09:00:00', 'completed', 'in_person', NULL, '2024-01-25 10:00:00'),
(17, 1, '2024-02-02 10:00:00', 'completed', 'follow_up', 'Post-op cardiac follow-up', '2024-01-26 10:00:00'),
(18, 4, '2024-02-05 14:00:00', 'scheduled', 'new_patient', NULL, '2024-01-29 10:00:00'),
(19, 6, '2024-02-06 15:30:00', 'completed', 'in_person', 'Skin rash evaluation', '2024-01-30 10:00:00'),
(20, 2, '2024-02-07 08:00:00', 'no_show', 'follow_up', NULL, '2024-01-31 10:00:00'),
(1, 5, '2024-03-10 09:00:00', 'completed', 'follow_up', NULL, '2024-03-01 10:00:00'),
(3, 1, '2024-03-11 09:00:00', 'completed', 'in_person', 'Chest pain evaluation', '2024-03-02 10:00:00'),
(5, 8, '2024-03-12 11:00:00', 'completed', 'telehealth', NULL, '2024-03-03 10:00:00'),
(7, 2, '2024-03-13 09:00:00', 'cancelled', 'follow_up', NULL, '2024-03-04 10:00:00'),
(9, 3, '2024-03-14 13:00:00', 'completed', 'new_patient', NULL, '2024-03-05 10:00:00'),
(11, 7, '2024-03-15 09:00:00', 'scheduled', 'in_person', NULL, '2024-03-06 10:00:00'),
(13, 4, '2024-03-18 14:00:00', 'completed', 'in_person', 'Well-child visit', '2024-03-09 10:00:00'),
(15, 9, '2024-03-19 22:00:00', 'completed', 'in_person', 'ER follow-up', '2024-03-10 10:00:00'),
(17, 6, '2024-03-20 15:00:00', 'scheduled', 'follow_up', NULL, '2024-03-11 10:00:00'),
(19, 10, '2024-03-21 09:00:00', 'completed', 'new_patient', 'Second opinion consult', '2024-03-12 10:00:00');

-- diagnoses (20) -- patient_id 1-20, provider_id 1-10
INSERT INTO diagnoses (patient_id, provider_id, icd10_code, description, diagnosed_at, severity, created_at) VALUES
(1, 1, 'I10', 'Hypertension', '2024-01-10 09:30:00', 'moderate', '2024-01-10 10:00:00'),
(2, 5, 'E11.9', 'Type 2 Diabetes', '2024-01-11 10:30:00', 'moderate', '2024-01-11 11:00:00'),
(3, 3, 'M54.5', 'Low Back Pain', '2024-01-12 11:30:00', 'mild', '2024-01-12 12:00:00'),
(5, 5, 'J45.909', 'Asthma', '2024-01-16 13:30:00', 'mild', '2024-01-16 14:00:00'),
(6, 4, 'Z23', 'Immunization', '2024-01-17 14:30:00', 'mild', '2024-01-17 15:00:00'),
(7, 2, 'M54.5', 'Low Back Pain', '2024-01-18 09:00:00', 'moderate', '2024-01-18 09:30:00'),
(9, 1, 'I10', 'Hypertension', '2024-01-22 09:30:00', 'severe', '2024-01-22 10:00:00'),
(10, 8, 'F32.9', 'Depression', '2024-01-23 12:00:00', 'moderate', '2024-01-23 12:30:00'),
(11, 5, 'K21.0', 'GERD', '2024-01-24 09:30:00', 'mild', '2024-01-24 10:00:00'),
(12, 3, 'J06.9', 'Upper Respiratory Infection', '2024-01-25 10:30:00', 'mild', '2024-01-25 11:00:00'),
(14, 10, 'E78.5', 'Hyperlipidemia', '2024-01-29 09:30:00', 'moderate', '2024-01-29 10:00:00'),
(15, 9, 'S06.0X0A', 'Head Injury', '2024-01-30 20:30:00', 'severe', '2024-01-30 21:00:00'),
(16, 5, 'N39.0', 'Urinary Tract Infection', '2024-02-01 09:30:00', 'mild', '2024-02-01 10:00:00'),
(17, 1, 'I10', 'Hypertension', '2024-02-02 10:30:00', 'moderate', '2024-02-02 11:00:00'),
(19, 6, 'F32.9', 'Depression', '2024-02-06 16:00:00', 'mild', '2024-02-06 16:30:00'),
(1, 5, 'E78.5', 'Hyperlipidemia', '2024-03-10 09:30:00', 'mild', '2024-03-10 10:00:00'),
(3, 1, 'I10', 'Hypertension', '2024-03-11 09:30:00', 'critical', '2024-03-11 10:00:00'),
(5, 8, 'F32.9', 'Depression', '2024-03-12 11:30:00', 'moderate', '2024-03-12 12:00:00'),
(9, 3, 'M54.5', 'Low Back Pain', '2024-03-14 13:30:00', 'mild', '2024-03-14 14:00:00'),
(13, 4, 'Z23', 'Immunization', '2024-03-18 14:30:00', 'mild', '2024-03-18 15:00:00');

-- lab_results (25) -- patient_id 1-20, provider_id 1-10
INSERT INTO lab_results (patient_id, provider_id, test_name, test_code, result_value, unit, normal_low, normal_high, abnormal, collected_at, resulted_at, created_at) VALUES
(1, 1, 'Lipid Panel', '80061', 215.0000, 'mg/dL', 100.0000, 200.0000, TRUE, '2024-01-10 09:15:00', '2024-01-11 08:00:00', '2024-01-11 08:05:00'),
(2, 5, 'HbA1c', '83036', 7.2000, '%', 4.0000, 5.6000, TRUE, '2024-01-11 10:15:00', '2024-01-12 08:00:00', '2024-01-12 08:05:00'),
(2, 5, 'Glucose', '82947', 145.0000, 'mg/dL', 70.0000, 100.0000, TRUE, '2024-01-11 10:15:00', '2024-01-12 08:00:00', '2024-01-12 08:05:00'),
(3, 3, 'CBC', '85025', 8.5000, 'K/uL', 0.0000, 20.0000, FALSE, '2024-01-12 11:15:00', '2024-01-13 08:00:00', '2024-01-13 08:05:00'),
(4, 7, 'BMP', '80048', 95.0000, 'mg/dL', 70.0000, 110.0000, FALSE, '2024-01-15 09:15:00', '2024-01-16 08:00:00', '2024-01-16 08:05:00'),
(5, 5, 'TSH', '84443', 3.1000, 'mIU/L', 0.4000, 4.0000, FALSE, '2024-01-16 13:15:00', '2024-01-17 08:00:00', '2024-01-17 08:05:00'),
(6, 4, 'Hemoglobin', '85018', 13.2000, 'g/dL', 12.0000, 17.5000, FALSE, '2024-01-17 14:15:00', '2024-01-18 08:00:00', '2024-01-18 08:05:00'),
(7, 2, 'Creatinine', '82565', 1.1000, 'mg/dL', 0.7000, 1.3000, FALSE, '2024-01-18 09:15:00', '2024-01-19 08:00:00', '2024-01-19 08:05:00'),
(9, 1, 'Troponin', '84484', 0.0800, 'ng/mL', 0.0000, 0.0400, TRUE, '2024-01-22 09:15:00', '2024-01-22 12:00:00', '2024-01-22 12:05:00'),
(9, 1, 'CRP', '86140', 2.5000, 'mg/L', 0.0000, 1.0000, TRUE, '2024-01-22 09:15:00', '2024-01-22 12:00:00', '2024-01-22 12:05:00'),
(10, 8, 'BMP', '80048', 88.0000, 'mg/dL', 70.0000, 110.0000, FALSE, '2024-01-23 11:45:00', '2024-01-24 08:00:00', '2024-01-24 08:05:00'),
(11, 5, 'Lipid Panel', '80061', 190.0000, 'mg/dL', 100.0000, 200.0000, FALSE, '2024-01-24 09:15:00', '2024-01-25 08:00:00', '2024-01-25 08:05:00'),
(12, 3, 'CBC', '85025', 6.9000, 'K/uL', 0.0000, 20.0000, FALSE, '2024-01-25 10:15:00', '2024-01-26 08:00:00', '2024-01-26 08:05:00'),
(14, 10, 'Lipid Panel', '80061', 260.0000, 'mg/dL', 100.0000, 200.0000, TRUE, '2024-01-29 09:15:00', '2024-01-30 08:00:00', '2024-01-30 08:05:00'),
(15, 9, 'CBC', '85025', 12.4000, 'K/uL', 0.0000, 20.0000, FALSE, '2024-01-30 20:15:00', '2024-01-30 23:00:00', '2024-01-30 23:05:00'),
(16, 5, 'BMP', '80048', 102.0000, 'mg/dL', 70.0000, 110.0000, FALSE, '2024-02-01 09:15:00', '2024-02-02 08:00:00', '2024-02-02 08:05:00'),
(17, 1, 'Lipid Panel', '80061', 205.0000, 'mg/dL', 100.0000, 200.0000, TRUE, '2024-02-02 10:15:00', '2024-02-03 08:00:00', '2024-02-03 08:05:00'),
(19, 6, 'TSH', '84443', 4.5000, 'mIU/L', 0.4000, 4.0000, TRUE, '2024-02-06 16:15:00', '2024-02-07 08:00:00', '2024-02-07 08:05:00'),
(1, 5, 'HbA1c', '83036', 5.4000, '%', 4.0000, 5.6000, FALSE, '2024-03-10 09:15:00', '2024-03-11 08:00:00', '2024-03-11 08:05:00'),
(3, 1, 'Troponin', '84484', 0.1200, 'ng/mL', 0.0000, 0.0400, TRUE, '2024-03-11 09:15:00', '2024-03-11 12:00:00', '2024-03-11 12:05:00'),
(5, 8, 'CRP', '86140', 0.8000, 'mg/L', 0.0000, 1.0000, FALSE, '2024-03-12 11:15:00', '2024-03-13 08:00:00', '2024-03-13 08:05:00'),
(9, 3, 'Glucose', '82947', 92.0000, 'mg/dL', 70.0000, 100.0000, FALSE, '2024-03-14 13:15:00', '2024-03-15 08:00:00', '2024-03-15 08:05:00'),
(13, 4, 'CBC', '85025', 9.1000, 'K/uL', 0.0000, 20.0000, FALSE, '2024-03-18 14:15:00', '2024-03-19 08:00:00', '2024-03-19 08:05:00'),
(15, 9, 'Hemoglobin', '85018', 11.5000, 'g/dL', 12.0000, 17.5000, TRUE, '2024-03-19 22:15:00', '2024-03-20 08:00:00', '2024-03-20 08:05:00'),
(20, 2, 'BMP', '80048', 99.0000, 'mg/dL', 70.0000, 110.0000, FALSE, '2024-02-07 08:15:00', '2024-02-08 08:00:00', '2024-02-08 08:05:00');

-- insurance_claims (18) -- patient_id 1-20, provider_id 1-10
INSERT INTO insurance_claims (patient_id, provider_id, claim_date, service_date, icd10_code, cpt_code, billed_amount, allowed_amount, paid_amount, status, created_at) VALUES
(1, 1, '2024-01-20', '2024-01-10', 'I10', 'CPT-99213', 350.00, 210.00, 189.00, 'approved', '2024-01-20 09:00:00'),
(2, 5, '2024-01-21', '2024-01-11', 'E11.9', 'CPT-99214', 420.00, 260.00, 234.00, 'approved', '2024-01-21 09:00:00'),
(3, 3, '2024-01-22', '2024-01-12', 'M54.5', 'CPT-99203', 500.00, 300.00, 270.00, 'pending', '2024-01-22 09:00:00'),
(5, 5, '2024-01-26', '2024-01-16', 'J45.909', 'CPT-99441', 180.00, 120.00, 108.00, 'approved', '2024-01-26 09:00:00'),
(6, 4, '2024-01-27', '2024-01-17', 'Z23', 'CPT-90471', 90.00, 90.00, 90.00, 'approved', '2024-01-27 09:00:00'),
(7, 2, '2024-01-28', '2024-01-18', 'M54.5', 'CPT-99204', 600.00, 380.00, 342.00, 'approved', '2024-01-28 09:00:00'),
(9, 1, '2024-02-01', '2024-01-22', 'I10', 'CPT-99215', 480.00, 300.00, 0.00, 'denied', '2024-02-01 09:00:00'),
(10, 8, '2024-02-02', '2024-01-23', 'F32.9', 'CPT-90837', 250.00, 180.00, 162.00, 'approved', '2024-02-02 09:00:00'),
(11, 5, '2024-02-03', '2024-01-24', 'K21.0', 'CPT-99213', 300.00, 190.00, 171.00, 'approved', '2024-02-03 09:00:00'),
(12, 3, '2024-02-04', '2024-01-25', 'J06.9', 'CPT-99212', 220.00, 140.00, 126.00, 'submitted', '2024-02-04 09:00:00'),
(14, 10, '2024-02-08', '2024-01-29', 'E78.5', 'CPT-99215', 700.00, 450.00, 405.00, 'approved', '2024-02-08 09:00:00'),
(15, 9, '2024-01-31', '2024-01-30', 'S06.0X0A', 'CPT-99284', 2400.00, 1600.00, 1440.00, 'approved', '2024-01-31 09:00:00'),
(16, 5, '2024-02-11', '2024-02-01', 'N39.0', 'CPT-99213', 260.00, 165.00, 148.50, 'approved', '2024-02-11 09:00:00'),
(17, 1, '2024-02-12', '2024-02-02', 'I10', 'CPT-99214', 380.00, 240.00, 216.00, 'appealed', '2024-02-12 09:00:00'),
(19, 6, '2024-02-16', '2024-02-06', 'F32.9', 'CPT-90834', 230.00, 150.00, 135.00, 'approved', '2024-02-16 09:00:00'),
(1, 5, '2024-03-20', '2024-03-10', 'E78.5', 'CPT-99213', 320.00, 200.00, 180.00, 'approved', '2024-03-20 09:00:00'),
(3, 1, '2024-03-21', '2024-03-11', 'I10', 'CPT-99284', 1800.00, 1200.00, 1080.00, 'approved', '2024-03-21 09:00:00'),
(9, 3, '2024-03-24', '2024-03-14', 'M54.5', 'CPT-99203', 500.00, 300.00, 270.00, 'submitted', '2024-03-24 09:00:00');

-- prescriptions (22) -- patient_id 1-20, provider_id 1-10
INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, days_supply, refills, prescribed_at, filled_at, created_at) VALUES
(1, 1, 'Lisinopril', '0071-0155-23', '10mg', 'once daily', 30, 5, '2024-01-10 09:45:00', '2024-01-10 16:00:00', '2024-01-10 09:45:00'),
(2, 5, 'Metformin', '0093-1048-01', '500mg', 'twice daily', 90, 3, '2024-01-11 10:45:00', '2024-01-11 17:00:00', '2024-01-11 10:45:00'),
(3, 3, 'Gabapentin', '0603-3919-21', '300mg', 'three times daily', 30, 1, '2024-01-12 11:45:00', '2024-01-13 09:00:00', '2024-01-12 11:45:00'),
(5, 5, 'Albuterol', '0173-0682-20', '90mcg', 'as needed', 30, 5, '2024-01-16 13:45:00', '2024-01-16 18:00:00', '2024-01-16 13:45:00'),
(7, 2, 'Gabapentin', '0603-3919-21', '300mg', 'twice daily', 30, 2, '2024-01-18 09:45:00', '2024-01-19 09:00:00', '2024-01-18 09:45:00'),
(9, 1, 'Amlodipine', '0093-7156-01', '5mg', 'once daily', 30, 5, '2024-01-22 09:45:00', NULL, '2024-01-22 09:45:00'),
(10, 8, 'Sertraline', '0049-4960-66', '50mg', 'once daily', 30, 5, '2024-01-23 12:15:00', '2024-01-23 18:00:00', '2024-01-23 12:15:00'),
(11, 5, 'Omeprazole', '0186-5020-31', '20mg', 'once daily', 30, 3, '2024-01-24 09:45:00', '2024-01-24 15:00:00', '2024-01-24 09:45:00'),
(12, 3, 'Montelukast', '0006-0117-31', '10mg', 'once daily', 30, 2, '2024-01-25 10:45:00', '2024-01-26 09:00:00', '2024-01-25 10:45:00'),
(14, 10, 'Atorvastatin', '0071-0155-23', '40mg', 'once daily', 90, 3, '2024-01-29 09:45:00', '2024-01-29 16:00:00', '2024-01-29 09:45:00'),
(15, 9, 'Hydrochlorothiazide', '0378-0018-01', '25mg', 'once daily', 30, 1, '2024-01-30 21:00:00', NULL, '2024-01-30 21:00:00'),
(16, 5, 'Levothyroxine', '0074-6624-13', '75mcg', 'once daily', 90, 5, '2024-02-01 09:45:00', '2024-02-01 15:00:00', '2024-02-01 09:45:00'),
(17, 1, 'Losartan', '0093-7451-01', '50mg', 'once daily', 30, 5, '2024-02-02 10:45:00', '2024-02-02 17:00:00', '2024-02-02 10:45:00'),
(19, 6, 'Sertraline', '0049-4960-66', '25mg', 'once daily', 30, 3, '2024-02-06 16:30:00', '2024-02-07 09:00:00', '2024-02-06 16:30:00'),
(20, 2, 'Metoprolol', '0378-0725-01', '25mg', 'twice daily', 30, 3, '2024-02-07 08:30:00', NULL, '2024-02-07 08:30:00'),
(1, 5, 'Atorvastatin', '0071-0155-23', '20mg', 'once daily', 90, 3, '2024-03-10 09:45:00', '2024-03-10 14:00:00', '2024-03-10 09:45:00'),
(3, 1, 'Metoprolol', '0378-0725-01', '50mg', 'twice daily', 30, 5, '2024-03-11 09:45:00', '2024-03-11 12:00:00', '2024-03-11 09:45:00'),
(5, 8, 'Sertraline', '0049-4960-66', '50mg', 'once daily', 30, 5, '2024-03-12 11:45:00', '2024-03-12 17:00:00', '2024-03-12 11:45:00'),
(9, 3, 'Gabapentin', '0603-3919-21', '300mg', 'three times daily', 30, 1, '2024-03-14 13:45:00', '2024-03-15 09:00:00', '2024-03-14 13:45:00'),
(13, 4, 'Montelukast', '0006-0117-31', '5mg', 'once daily', 30, 2, '2024-03-18 14:45:00', '2024-03-19 09:00:00', '2024-03-18 14:45:00'),
(17, 6, 'Pantoprazole', '0008-0841-05', '40mg', 'once daily', 30, 3, '2024-03-20 15:30:00', NULL, '2024-03-20 15:30:00'),
(19, 10, 'Simvastatin', '0006-0749-54', '20mg', 'once daily', 90, 3, '2024-03-21 09:30:00', '2024-03-21 16:00:00', '2024-03-21 09:30:00');

-- ------------------------------------------------------------------
-- E-COMMERCE DATA
-- ------------------------------------------------------------------

-- categories (12) -- 1-10 top level, 11-12 sub (parent_id self-referential)
INSERT INTO categories (name, parent_id, slug, created_at) VALUES
('Electronics', NULL, 'electronics', '2018-01-01 00:00:00'),
('Clothing', NULL, 'clothing', '2018-01-01 00:00:00'),
('Home & Garden', NULL, 'home-garden', '2018-01-01 00:00:00'),
('Sports', NULL, 'sports', '2018-01-01 00:00:00'),
('Toys', NULL, 'toys', '2018-01-01 00:00:00'),
('Books', NULL, 'books', '2018-01-01 00:00:00'),
('Beauty', NULL, 'beauty', '2018-01-01 00:00:00'),
('Automotive', NULL, 'automotive', '2018-01-01 00:00:00'),
('Food', NULL, 'food', '2018-01-01 00:00:00'),
('Office', NULL, 'office', '2018-01-01 00:00:00'),
('Smart Home', 1, 'smart-home', '2019-03-01 00:00:00'),
('Running Shoes', 4, 'running-shoes', '2019-03-01 00:00:00');

-- products (25) -- category_id 1-12
INSERT INTO products (category_id, name, sku, description, price, cost, weight_kg, active, created_at) VALUES
(1, 'Wireless Noise-Cancelling Headphones', 'SKU-ELEC-0001', 'Over-ear Bluetooth headphones with active noise cancellation.', 199.99, 85.00, 0.290, TRUE, '2019-02-01 00:00:00'),
(1, '4K Streaming Media Player', 'SKU-ELEC-0002', 'Compact streaming device supporting 4K HDR.', 49.99, 18.00, 0.080, TRUE, '2019-02-02 00:00:00'),
(11, 'Smart WiFi Plug (2-Pack)', 'SKU-SMHM-0001', 'App-controlled smart plugs with energy monitoring.', 24.99, 8.50, 0.150, TRUE, '2019-03-05 00:00:00'),
(11, 'Smart Video Doorbell', 'SKU-SMHM-0002', '1080p video doorbell with motion alerts.', 129.99, 55.00, 0.400, TRUE, '2019-03-06 00:00:00'),
(2, 'Men''s Fleece Jacket', 'SKU-CLTH-0001', 'Full-zip fleece jacket for cool weather.', 59.99, 21.00, 0.600, TRUE, '2019-04-01 00:00:00'),
(2, 'Women''s Yoga Leggings', 'SKU-CLTH-0002', 'High-waisted stretch leggings.', 34.99, 11.00, 0.220, TRUE, '2019-04-02 00:00:00'),
(3, 'Ceramic Non-Stick Cookware Set', 'SKU-HMGD-0001', '10-piece ceramic-coated cookware set.', 149.99, 62.00, 6.500, TRUE, '2019-05-01 00:00:00'),
(3, 'Electric Garden Tiller', 'SKU-HMGD-0002', 'Corded electric tiller for small gardens.', 189.99, 78.00, 9.200, TRUE, '2019-05-02 00:00:00'),
(4, 'Adjustable Dumbbell Set', 'SKU-SPRT-0001', 'Pair of adjustable dumbbells, 5-50 lbs each.', 299.99, 140.00, 22.000, TRUE, '2019-06-01 00:00:00'),
(12, 'Trail Running Shoes', 'SKU-RUN-0001', 'Lightweight trail running shoes with grip sole.', 89.99, 34.00, 0.320, TRUE, '2019-06-05 00:00:00'),
(12, 'Road Running Shoes', 'SKU-RUN-0002', 'Cushioned road running shoes.', 99.99, 38.00, 0.300, TRUE, '2019-06-06 00:00:00'),
(5, 'Wooden Building Blocks Set', 'SKU-TOYS-0001', '100-piece natural wood building blocks.', 39.99, 14.00, 1.800, TRUE, '2019-07-01 00:00:00'),
(5, 'Remote Control Race Car', 'SKU-TOYS-0002', '1:16 scale RC race car.', 44.99, 17.00, 0.700, TRUE, '2019-07-02 00:00:00'),
(6, 'The Art of Clean Code (Paperback)', 'SKU-BOOK-0001', 'A practical guide to writing maintainable software.', 29.99, 9.00, 0.400, TRUE, '2019-08-01 00:00:00'),
(6, 'World Atlas 2024 Edition', 'SKU-BOOK-0002', 'Fully updated world atlas with political maps.', 34.99, 12.00, 1.100, TRUE, '2019-08-02 00:00:00'),
(7, 'Vitamin C Serum', 'SKU-BEAU-0001', 'Brightening facial serum, 30ml.', 24.99, 6.50, 0.050, TRUE, '2019-09-01 00:00:00'),
(7, 'Ceramic Hair Straightener', 'SKU-BEAU-0002', 'Dual-voltage ceramic flat iron.', 39.99, 14.00, 0.450, TRUE, '2019-09-02 00:00:00'),
(8, 'All-Weather Floor Mats', 'SKU-AUTO-0001', 'Set of 4 heavy-duty rubber floor mats.', 69.99, 25.00, 3.200, TRUE, '2019-10-01 00:00:00'),
(8, 'Portable Jump Starter', 'SKU-AUTO-0002', '12V portable battery jump starter with USB.', 79.99, 30.00, 0.900, TRUE, '2019-10-02 00:00:00'),
(9, 'Single-Origin Coffee Beans 2lb', 'SKU-FOOD-0001', 'Medium roast whole bean coffee.', 18.99, 7.00, 0.910, TRUE, '2019-11-01 00:00:00'),
(9, 'Organic Trail Mix 3lb', 'SKU-FOOD-0002', 'Nuts, seeds, and dried fruit blend.', 22.99, 9.50, 1.400, TRUE, '2019-11-02 00:00:00'),
(10, 'Ergonomic Office Chair', 'SKU-OFFC-0001', 'Mesh-back office chair with lumbar support.', 219.99, 95.00, 14.500, TRUE, '2019-12-01 00:00:00'),
(10, 'Standing Desk Converter', 'SKU-OFFC-0002', 'Height-adjustable desktop riser.', 159.99, 62.00, 8.700, TRUE, '2019-12-02 00:00:00'),
(1, 'Portable Bluetooth Speaker', 'SKU-ELEC-0003', 'Waterproof portable speaker, 12-hour battery.', 59.99, 22.00, 0.540, TRUE, '2020-01-01 00:00:00'),
(2, 'Merino Wool Socks (3-Pack)', 'SKU-CLTH-0003', 'Breathable merino wool crew socks.', 24.99, 8.00, 0.180, TRUE, '2020-01-02 00:00:00');

-- customers (20)
INSERT INTO customers (email, first_name, last_name, phone, address, city, state, zip, country, registered_at, last_login) VALUES
('alice.morgan@example.com', 'Alice', 'Morgan', '555-401-3001', '221 Baker St', 'Austin', 'TX', '73301', 'US', '2019-03-01 12:00:00', '2024-03-01 08:00:00'),
('brian.foster@example.com', 'Brian', 'Foster', '555-401-3002', '18 Elmwood Ave', 'Austin', 'TX', '73302', 'US', '2019-03-05 12:00:00', '2024-02-15 08:00:00'),
('carla.diaz@example.com', 'Carla', 'Diaz', '555-401-3003', '900 River Rd', 'Dallas', 'TX', '75201', 'US', '2019-04-10 12:00:00', '2024-03-10 08:00:00'),
('derek.chan@example.com', 'Derek', 'Chan', '555-401-3004', '77 Sunset Blvd', 'Dallas', 'TX', '75202', 'US', '2019-05-15 12:00:00', NULL),
('elena.vasquez@example.com', 'Elena', 'Vasquez', '555-401-3005', '33 Highland Dr', 'Houston', 'TX', '77001', 'US', '2019-06-20 12:00:00', '2024-01-20 08:00:00'),
('frank.oconnor@example.com', 'Frank', 'O''Connor', '555-401-3006', '12 Meadow Ln', 'Houston', 'TX', '77002', 'US', '2019-07-25 12:00:00', '2024-02-25 08:00:00'),
('grace.kim@example.com', 'Grace', 'Kim', '555-401-3007', '505 Bluebonnet Way', 'San Antonio', 'TX', '78201', 'US', '2019-08-30 12:00:00', '2024-03-05 08:00:00'),
('henry.osei@example.com', 'Henry', 'Osei', '555-401-3008', '64 Prairie Ct', 'San Antonio', 'TX', '78202', 'US', '2019-09-05 12:00:00', NULL),
('isabel.rossi@example.com', 'Isabel', 'Rossi', '555-401-3009', '150 Canyon Rd', 'Austin', 'TX', '73303', 'US', '2019-10-10 12:00:00', '2024-02-10 08:00:00'),
('jack.nguyen@example.com', 'Jack', 'Nguyen', '555-401-3010', '8 Trailhead Ave', 'Dallas', 'TX', '75203', 'US', '2019-11-15 12:00:00', '2024-01-15 08:00:00'),
('karen.oduya@example.com', 'Karen', 'Oduya', '555-401-3011', '210 Lighthouse Dr', 'Toronto', 'ON', 'M5V2T6', 'CA', '2020-01-01 12:00:00', '2024-03-12 08:00:00'),
('liam.walsh@example.com', 'Liam', 'Walsh', '555-401-3012', '45 Kingsway', 'London', 'ENG', 'SW1A1AA', 'GB', '2020-02-05 12:00:00', '2024-02-05 08:00:00'),
('mia.petrov@example.com', 'Mia', 'Petrov', '555-401-3013', '77 Harbor View', 'Sydney', 'NSW', '2000', 'AU', '2020-03-10 12:00:00', NULL),
('noah.fischer@example.com', 'Noah', 'Fischer', '555-401-3014', '19 Marktplatz', 'Berlin', 'BE', '10115', 'DE', '2020-04-15 12:00:00', '2024-03-15 08:00:00'),
('olivia.santos@example.com', 'Olivia', 'Santos', '555-401-3015', '303 Riverside Dr', 'Houston', 'TX', '77003', 'US', '2020-05-20 12:00:00', '2024-01-20 08:00:00'),
('peter.brandt@example.com', 'Peter', 'Brandt', '555-401-3016', '88 Forest Ave', 'San Antonio', 'TX', '78203', 'US', '2020-06-25 12:00:00', '2024-02-28 08:00:00'),
('quinn.avery@example.com', 'Quinn', 'Avery', '555-401-3017', '5 Summit Ct', 'Austin', 'TX', '73304', 'US', '2020-07-30 12:00:00', NULL),
('rosa.mendez@example.com', 'Rosa', 'Mendez', '555-401-3018', '620 Vista Dr', 'Dallas', 'TX', '75204', 'US', '2020-08-05 12:00:00', '2024-03-08 08:00:00'),
('samuel.grant@example.com', 'Samuel', 'Grant', '555-401-3019', '14 Orchard Ln', 'Houston', 'TX', '77004', 'US', '2020-09-10 12:00:00', '2024-02-18 08:00:00'),
('tara.blake@example.com', 'Tara', 'Blake', '555-401-3020', '256 Ridge Rd', 'San Antonio', 'TX', '78204', 'US', '2020-10-15 12:00:00', '2024-01-25 08:00:00');

-- orders (25) -- customer_id 1-20
INSERT INTO orders (customer_id, status, subtotal, tax, shipping, total, currency, placed_at, shipped_at, delivered_at, created_at) VALUES
(1, 'delivered', 249.98, 20.00, 5.99, 275.97, 'USD', '2024-01-05 10:00:00', '2024-01-06 09:00:00', '2024-01-09 14:00:00', '2024-01-05 10:00:00'),
(2, 'delivered', 59.99, 4.80, 0.00, 64.79, 'USD', '2024-01-08 11:00:00', '2024-01-09 09:00:00', '2024-01-12 14:00:00', '2024-01-08 11:00:00'),
(3, 'shipped', 189.99, 15.20, 9.99, 215.18, 'USD', '2024-01-15 12:00:00', '2024-01-16 09:00:00', NULL, '2024-01-15 12:00:00'),
(4, 'delivered', 34.99, 2.80, 4.99, 42.78, 'USD', '2024-01-18 09:30:00', '2024-01-19 09:00:00', '2024-01-22 14:00:00', '2024-01-18 09:30:00'),
(5, 'cancelled', 299.99, 24.00, 0.00, 323.99, 'USD', '2024-01-20 14:00:00', NULL, NULL, '2024-01-20 14:00:00'),
(6, 'delivered', 89.99, 7.20, 5.99, 103.18, 'USD', '2024-01-22 10:00:00', '2024-01-23 09:00:00', '2024-01-26 14:00:00', '2024-01-22 10:00:00'),
(7, 'delivered', 129.99, 10.40, 0.00, 140.39, 'USD', '2024-01-25 16:00:00', '2024-01-26 09:00:00', '2024-01-29 14:00:00', '2024-01-25 16:00:00'),
(8, 'processing', 44.99, 3.60, 4.99, 53.58, 'USD', '2024-02-01 09:00:00', NULL, NULL, '2024-02-01 09:00:00'),
(9, 'delivered', 79.98, 6.40, 5.99, 92.37, 'USD', '2024-02-03 11:30:00', '2024-02-04 09:00:00', '2024-02-07 14:00:00', '2024-02-03 11:30:00'),
(10, 'delivered', 219.99, 17.60, 0.00, 237.59, 'USD', '2024-02-05 13:00:00', '2024-02-06 09:00:00', '2024-02-10 14:00:00', '2024-02-05 13:00:00'),
(11, 'delivered', 39.99, 3.20, 6.99, 50.18, 'USD', '2024-02-07 10:00:00', '2024-02-08 09:00:00', '2024-02-13 14:00:00', '2024-02-07 10:00:00'),
(12, 'shipped', 59.99, 4.80, 6.99, 71.78, 'USD', '2024-02-10 15:00:00', '2024-02-11 09:00:00', NULL, '2024-02-10 15:00:00'),
(13, 'delivered', 149.99, 12.00, 0.00, 161.99, 'USD', '2024-02-12 09:00:00', '2024-02-13 09:00:00', '2024-02-17 14:00:00', '2024-02-12 09:00:00'),
(14, 'delivered', 29.99, 2.40, 4.99, 37.38, 'USD', '2024-02-14 12:00:00', '2024-02-15 09:00:00', '2024-02-19 14:00:00', '2024-02-14 12:00:00'),
(15, 'returned', 99.99, 8.00, 0.00, 107.99, 'USD', '2024-02-16 14:00:00', '2024-02-17 09:00:00', '2024-02-21 14:00:00', '2024-02-16 14:00:00'),
(16, 'delivered', 24.99, 2.00, 4.99, 31.98, 'USD', '2024-02-18 10:00:00', '2024-02-19 09:00:00', '2024-02-23 14:00:00', '2024-02-18 10:00:00'),
(17, 'delivered', 69.99, 5.60, 0.00, 75.59, 'USD', '2024-02-20 11:00:00', '2024-02-21 09:00:00', '2024-02-24 14:00:00', '2024-02-20 11:00:00'),
(18, 'pending', 199.99, 16.00, 0.00, 215.99, 'USD', '2024-02-22 09:00:00', NULL, NULL, '2024-02-22 09:00:00'),
(19, 'delivered', 34.99, 2.80, 6.99, 44.78, 'USD', '2024-02-24 13:00:00', '2024-02-25 09:00:00', '2024-02-29 14:00:00', '2024-02-24 13:00:00'),
(20, 'delivered', 159.99, 12.80, 0.00, 172.79, 'USD', '2024-02-26 15:00:00', '2024-02-27 09:00:00', '2024-03-02 14:00:00', '2024-02-26 15:00:00'),
(1, 'delivered', 59.99, 4.80, 4.99, 69.78, 'USD', '2024-03-01 10:00:00', '2024-03-02 09:00:00', '2024-03-06 14:00:00', '2024-03-01 10:00:00'),
(3, 'delivered', 89.99, 7.20, 0.00, 97.19, 'USD', '2024-03-03 09:00:00', '2024-03-04 09:00:00', '2024-03-08 14:00:00', '2024-03-03 09:00:00'),
(5, 'processing', 24.99, 2.00, 4.99, 31.98, 'USD', '2024-03-05 11:00:00', NULL, NULL, '2024-03-05 11:00:00'),
(9, 'delivered', 44.99, 3.60, 4.99, 53.58, 'USD', '2024-03-07 14:00:00', '2024-03-08 09:00:00', '2024-03-12 14:00:00', '2024-03-07 14:00:00'),
(12, 'shipped', 219.99, 17.60, 0.00, 237.59, 'USD', '2024-03-09 10:00:00', '2024-03-10 09:00:00', NULL, '2024-03-09 10:00:00');

-- order_items (45) -- order_id 1-25, product_id 1-25
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount, line_total, created_at) VALUES
(1, 1, 1, 199.99, 0, 199.99, '2024-01-05 10:00:00'),
(1, 2, 1, 49.99, 0, 49.99, '2024-01-05 10:00:00'),
(2, 25, 1, 59.99, 0, 59.99, '2024-01-08 11:00:00'),
(3, 8, 1, 189.99, 0, 189.99, '2024-01-15 12:00:00'),
(4, 6, 1, 34.99, 0, 34.99, '2024-01-18 09:30:00'),
(5, 9, 1, 299.99, 0, 299.99, '2024-01-20 14:00:00'),
(6, 10, 1, 89.99, 0, 89.99, '2024-01-22 10:00:00'),
(7, 4, 1, 129.99, 0, 129.99, '2024-01-25 16:00:00'),
(8, 13, 1, 44.99, 0, 44.99, '2024-02-01 09:00:00'),
(9, 16, 1, 24.99, 0, 24.99, '2024-02-03 11:30:00'),
(9, 17, 1, 39.99, 15.00, 24.99, '2024-02-03 11:30:00'),
(10, 22, 1, 219.99, 0, 219.99, '2024-02-05 13:00:00'),
(11, 14, 1, 29.99, 0, 29.99, '2024-02-07 10:00:00'),
(12, 25, 1, 59.99, 0, 59.99, '2024-02-10 15:00:00'),
(13, 7, 1, 149.99, 0, 149.99, '2024-02-12 09:00:00'),
(14, 14, 1, 29.99, 0, 29.99, '2024-02-14 12:00:00'),
(15, 11, 1, 99.99, 0, 99.99, '2024-02-16 14:00:00'),
(16, 3, 1, 24.99, 0, 24.99, '2024-02-18 10:00:00'),
(17, 18, 1, 69.99, 0, 69.99, '2024-02-20 11:00:00'),
(18, 1, 1, 199.99, 0, 199.99, '2024-02-22 09:00:00'),
(19, 6, 1, 34.99, 0, 34.99, '2024-02-24 13:00:00'),
(20, 23, 1, 159.99, 0, 159.99, '2024-02-26 15:00:00'),
(21, 25, 1, 59.99, 0, 59.99, '2024-03-01 10:00:00'),
(22, 10, 1, 89.99, 0, 89.99, '2024-03-03 09:00:00'),
(23, 16, 1, 24.99, 0, 24.99, '2024-03-05 11:00:00'),
(24, 13, 1, 44.99, 0, 44.99, '2024-03-07 14:00:00'),
(25, 22, 1, 219.99, 0, 219.99, '2024-03-09 10:00:00'),
(1, 20, 1, 79.99, 0, 79.99, '2024-01-05 10:00:00'),
(2, 24, 1, 59.99, 0, 59.99, '2024-01-08 11:00:00'),
(3, 5, 1, 59.99, 0, 59.99, '2024-01-15 12:00:00'),
(5, 12, 1, 39.99, 0, 39.99, '2024-01-20 14:00:00'),
(6, 19, 1, 79.99, 0, 79.99, '2024-01-22 10:00:00'),
(7, 3, 1, 24.99, 0, 24.99, '2024-01-25 16:00:00'),
(9, 21, 1, 22.99, 0, 22.99, '2024-02-03 11:30:00'),
(10, 15, 1, 34.99, 0, 34.99, '2024-02-05 13:00:00'),
(12, 4, 1, 129.99, 0, 129.99, '2024-02-10 15:00:00'),
(13, 2, 1, 49.99, 0, 49.99, '2024-02-12 09:00:00'),
(15, 9, 1, 299.99, 0, 299.99, '2024-02-16 14:00:00'),
(17, 8, 1, 189.99, 0, 189.99, '2024-02-20 11:00:00'),
(18, 6, 1, 34.99, 0, 34.99, '2024-02-22 09:00:00'),
(20, 7, 1, 149.99, 0, 149.99, '2024-02-26 15:00:00'),
(21, 3, 1, 24.99, 0, 24.99, '2024-03-01 10:00:00'),
(22, 18, 1, 69.99, 0, 69.99, '2024-03-03 09:00:00'),
(23, 1, 1, 199.99, 0, 199.99, '2024-03-05 11:00:00'),
(24, 14, 1, 29.99, 0, 29.99, '2024-03-07 14:00:00'),
(25, 5, 1, 59.99, 0, 59.99, '2024-03-09 10:00:00');

-- reviews (20) -- product_id 1-25, customer_id 1-20
INSERT INTO reviews (product_id, customer_id, rating, title, body, verified, helpful_votes, created_at) VALUES
(1, 1, 5, 'Fantastic sound quality', 'These headphones exceeded my expectations. Battery lasts all day.', TRUE, 42, '2024-01-12 09:00:00'),
(2, 2, 4, 'Good value streaming stick', 'Works well, occasional buffering on 4K content.', TRUE, 15, '2024-01-14 09:00:00'),
(8, 3, 3, 'Decent but heavy', 'Tiller works but is bulkier than expected.', TRUE, 8, '2024-01-18 09:00:00'),
(6, 4, 5, 'Super comfortable leggings', 'Great fit, no see-through issue at all.', TRUE, 27, '2024-01-22 09:00:00'),
(9, 5, 2, 'Not what I expected', 'Order was cancelled before I could try it.', FALSE, 3, '2024-01-25 09:00:00'),
(10, 6, 5, 'Great grip on trails', 'Perfect for muddy trail runs, true to size.', TRUE, 19, '2024-01-27 09:00:00'),
(4, 7, 4, 'Easy setup', 'Doorbell installed in 20 minutes, clear video.', TRUE, 11, '2024-01-30 09:00:00'),
(13, 8, 5, 'Kids love it', 'RC car is fast and durable.', TRUE, 6, '2024-02-04 09:00:00'),
(16, 9, 4, 'Nice glow', 'Noticed brighter skin after two weeks.', TRUE, 22, '2024-02-06 09:00:00'),
(22, 10, 5, 'Best office chair I have owned', 'Lumbar support is excellent for long workdays.', TRUE, 34, '2024-02-08 09:00:00'),
(14, 11, 4, 'Clear and useful', 'Good practical advice on writing better code.', TRUE, 18, '2024-02-10 09:00:00'),
(25, 12, 5, 'Loud for its size', 'Surprisingly powerful bass for a portable speaker.', TRUE, 9, '2024-02-13 09:00:00'),
(7, 13, 3, 'Handles are a bit loose', 'Cooks evenly but handles wobble slightly.', TRUE, 5, '2024-02-15 09:00:00'),
(14, 14, 5, 'A must-read', 'Changed how I approach refactoring.', TRUE, 41, '2024-02-16 09:00:00'),
(11, 15, 2, 'Sizing runs small', 'Had to return for a larger size.', TRUE, 4, '2024-02-19 09:00:00'),
(3, 16, 4, 'Handy little plugs', 'Easy to set schedules from the app.', TRUE, 12, '2024-02-20 09:00:00'),
(18, 17, 5, 'Fits perfectly', 'Mats trimmed easily to fit my SUV.', TRUE, 7, '2024-02-22 09:00:00'),
(1, 18, 4, 'Great but pricey', 'Sound is excellent, wish it cost less.', FALSE, 2, '2024-02-24 09:00:00'),
(6, 19, 5, 'Living in these', 'Wear them almost every day now.', TRUE, 16, '2024-02-26 09:00:00'),
(23, 20, 4, 'Solid riser', 'Sturdy and easy to adjust height.', TRUE, 10, '2024-02-28 09:00:00');

-- cart_events (30) -- customer_id 1-20, product_id 1-25
INSERT INTO cart_events (customer_id, product_id, event_type, quantity, session_id, ip_address, user_agent, occurred_at) VALUES
(1, 1, 'view', 1, 'sess-a1001', '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-05 09:45:00'),
(1, 1, 'add_to_cart', 1, 'sess-a1001', '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-05 09:50:00'),
(1, 2, 'add_to_cart', 1, 'sess-a1001', '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-05 09:55:00'),
(1, 1, 'purchase', 1, 'sess-a1001', '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-05 10:00:00'),
(2, 25, 'view', 1, 'sess-b1002', '203.0.113.11', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-08 10:45:00'),
(2, 25, 'add_to_cart', 1, 'sess-b1002', '203.0.113.11', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-08 10:55:00'),
(2, 25, 'purchase', 1, 'sess-b1002', '203.0.113.11', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-08 11:00:00'),
(3, 8, 'view', 1, 'sess-c1003', '203.0.113.12', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-15 11:45:00'),
(3, 8, 'add_to_cart', 1, 'sess-c1003', '203.0.113.12', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-15 11:55:00'),
(3, 5, 'wishlist', 1, 'sess-c1003', '203.0.113.12', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-15 11:57:00'),
(3, 8, 'purchase', 1, 'sess-c1003', '203.0.113.12', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-15 12:00:00'),
(4, 6, 'view', 2, 'sess-d1004', '203.0.113.13', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-18 09:15:00'),
(4, 6, 'add_to_cart', 1, 'sess-d1004', '203.0.113.13', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-18 09:20:00'),
(4, 6, 'purchase', 1, 'sess-d1004', '203.0.113.13', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-18 09:30:00'),
(5, 9, 'add_to_cart', 1, 'sess-e1005', '203.0.113.14', 'Mozilla/5.0 (Android 14; Mobile)', '2024-01-20 13:45:00'),
(5, 9, 'checkout_start', 1, 'sess-e1005', '203.0.113.14', 'Mozilla/5.0 (Android 14; Mobile)', '2024-01-20 13:55:00'),
(5, 9, 'remove_from_cart', 1, 'sess-e1005', '203.0.113.14', 'Mozilla/5.0 (Android 14; Mobile)', '2024-01-20 14:05:00'),
(6, 10, 'view', 1, 'sess-f1006', '203.0.113.15', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-22 09:45:00'),
(6, 10, 'add_to_cart', 1, 'sess-f1006', '203.0.113.15', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-22 09:55:00'),
(6, 10, 'purchase', 1, 'sess-f1006', '203.0.113.15', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-22 10:00:00'),
(7, 4, 'view', 1, 'sess-g1007', '203.0.113.16', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-25 15:45:00'),
(7, 4, 'share', 1, 'sess-g1007', '203.0.113.16', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-25 15:50:00'),
(7, 4, 'add_to_cart', 1, 'sess-g1007', '203.0.113.16', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-25 15:55:00'),
(7, 4, 'purchase', 1, 'sess-g1007', '203.0.113.16', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-25 16:00:00'),
(8, 13, 'view', 1, 'sess-h1008', '203.0.113.17', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-01 08:45:00'),
(8, 13, 'add_to_cart', 1, 'sess-h1008', '203.0.113.17', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-01 08:55:00'),
(9, 16, 'wishlist', 1, 'sess-i1009', '203.0.113.18', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-02-03 11:15:00'),
(9, 17, 'add_to_cart', 1, 'sess-i1009', '203.0.113.18', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-02-03 11:20:00'),
(10, 22, 'view', 1, 'sess-j1010', '203.0.113.19', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-05 12:45:00'),
(10, 22, 'purchase', 1, 'sess-j1010', '203.0.113.19', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-05 13:00:00');

-- ------------------------------------------------------------------
-- FINANCE DATA
-- ------------------------------------------------------------------

-- accounts (15)
INSERT INTO accounts (account_number, account_type, customer_name, email, balance, currency, status, opened_at, closed_at, created_at) VALUES
('BBAN-1000000001', 'checking', 'Alice Morgan', 'alice.morgan@example.com', 4250.7500, 'USD', 'active', '2019-03-01 12:00:00', NULL, '2019-03-01 12:00:00'),
('BBAN-1000000002', 'savings', 'Alice Morgan', 'alice.morgan@example.com', 18320.1200, 'USD', 'active', '2019-03-01 12:05:00', NULL, '2019-03-01 12:05:00'),
('BBAN-1000000003', 'checking', 'Brian Foster', 'brian.foster@example.com', 890.4000, 'USD', 'active', '2019-03-05 12:00:00', NULL, '2019-03-05 12:00:00'),
('BBAN-1000000004', 'money_market', 'Carla Diaz', 'carla.diaz@example.com', 52310.0000, 'USD', 'active', '2019-04-10 12:00:00', NULL, '2019-04-10 12:00:00'),
('BBAN-1000000005', 'checking', 'Derek Chan', 'derek.chan@example.com', -145.2000, 'USD', 'frozen', '2019-05-15 12:00:00', NULL, '2019-05-15 12:00:00'),
('BBAN-1000000006', 'savings', 'Elena Vasquez', 'elena.vasquez@example.com', 7600.0000, 'USD', 'active', '2019-06-20 12:00:00', NULL, '2019-06-20 12:00:00'),
('BBAN-1000000007', 'brokerage', 'Frank OConnor', 'frank.oconnor@example.com', 98750.5000, 'USD', 'active', '2019-07-25 12:00:00', NULL, '2019-07-25 12:00:00'),
('BBAN-1000000008', 'checking', 'Grace Kim', 'grace.kim@example.com', 1200.0000, 'USD', 'active', '2019-08-30 12:00:00', NULL, '2019-08-30 12:00:00'),
('BBAN-1000000009', 'ira', 'Henry Osei', 'henry.osei@example.com', 145000.0000, 'USD', 'active', '2019-09-05 12:00:00', NULL, '2019-09-05 12:00:00'),
('BBAN-1000000010', 'checking', 'Isabel Rossi', 'isabel.rossi@example.com', 3400.6000, 'USD', 'active', '2019-10-10 12:00:00', NULL, '2019-10-10 12:00:00'),
('BBAN-1000000011', 'cd', 'Jack Nguyen', 'jack.nguyen@example.com', 25000.0000, 'USD', 'active', '2019-11-15 12:00:00', NULL, '2019-11-15 12:00:00'),
('BBAN-1000000012', 'checking', 'Karen Oduya', 'karen.oduya@example.com', 610.0000, 'USD', 'closed', '2020-01-01 12:00:00', '2023-06-01 09:00:00', '2020-01-01 12:00:00'),
('BBAN-1000000013', 'savings', 'Liam Walsh', 'liam.walsh@example.com', 9450.3000, 'USD', 'active', '2020-02-05 12:00:00', NULL, '2020-02-05 12:00:00'),
('BBAN-1000000014', 'checking', 'Mia Petrov', 'mia.petrov@example.com', 2750.0000, 'USD', 'active', '2020-03-10 12:00:00', NULL, '2020-03-10 12:00:00'),
('BBAN-1000000015', 'money_market', 'Noah Fischer', 'noah.fischer@example.com', 15600.9000, 'USD', 'active', '2020-04-15 12:00:00', NULL, '2020-04-15 12:00:00');

-- transactions (40) -- account_id 1-15
INSERT INTO transactions (account_id, transaction_type, amount, currency, balance_after, description, reference_id, channel, status, transacted_at, created_at) VALUES
(1, 'deposit', 1500.0000, 'USD', 4250.7500, 'Payroll deposit', 'REF-100000001', 'online', 'completed', '2024-01-02 08:00:00', '2024-01-02 08:00:00'),
(1, 'debit', 85.2000, 'USD', 4165.5500, 'Grocery store purchase', 'REF-100000002', 'mobile', 'completed', '2024-01-03 17:30:00', '2024-01-03 17:30:00'),
(2, 'interest', 45.1200, 'USD', 18320.1200, 'Monthly interest credit', 'REF-100000003', 'online', 'completed', '2024-01-05 00:00:00', '2024-01-05 00:00:00'),
(3, 'withdrawal', 200.0000, 'USD', 890.4000, 'ATM withdrawal', 'REF-100000004', 'atm', 'completed', '2024-01-06 12:00:00', '2024-01-06 12:00:00'),
(4, 'transfer', 5000.0000, 'USD', 52310.0000, 'Transfer to brokerage', 'REF-100000005', 'online', 'completed', '2024-01-07 09:00:00', '2024-01-07 09:00:00'),
(5, 'fee', 35.0000, 'USD', -145.2000, 'Overdraft fee', 'REF-100000006', 'branch', 'completed', '2024-01-08 10:00:00', '2024-01-08 10:00:00'),
(6, 'deposit', 900.0000, 'USD', 7600.0000, 'Freelance payment', 'REF-100000007', 'mobile', 'completed', '2024-01-09 14:00:00', '2024-01-09 14:00:00'),
(7, 'debit', 12000.0000, 'USD', 98750.5000, 'Stock purchase', 'REF-100000008', 'online', 'completed', '2024-01-10 11:00:00', '2024-01-10 11:00:00'),
(8, 'debit', 60.0000, 'USD', 1200.0000, 'Utility bill payment', 'REF-100000009', 'online', 'completed', '2024-01-11 09:00:00', '2024-01-11 09:00:00'),
(9, 'deposit', 2000.0000, 'USD', 145000.0000, 'IRA contribution', 'REF-100000010', 'online', 'completed', '2024-01-12 08:00:00', '2024-01-12 08:00:00'),
(10, 'debit', 150.0000, 'USD', 3400.6000, 'Online purchase', 'REF-100000011', 'online', 'completed', '2024-01-13 16:00:00', '2024-01-13 16:00:00'),
(11, 'interest', 60.0000, 'USD', 25000.0000, 'CD interest credit', 'REF-100000012', 'online', 'completed', '2024-01-14 00:00:00', '2024-01-14 00:00:00'),
(12, 'withdrawal', 100.0000, 'USD', 610.0000, 'Cash withdrawal', 'REF-100000013', 'branch', 'completed', '2024-01-15 13:00:00', '2024-01-15 13:00:00'),
(13, 'deposit', 500.0000, 'USD', 9450.3000, 'Check deposit', 'REF-100000014', 'mobile', 'completed', '2024-01-16 10:00:00', '2024-01-16 10:00:00'),
(14, 'debit', 220.0000, 'USD', 2750.0000, 'Travel booking', 'REF-100000015', 'online', 'completed', '2024-01-17 09:00:00', '2024-01-17 09:00:00'),
(15, 'transfer', 1000.0000, 'USD', 15600.9000, 'Transfer from checking', 'REF-100000016', 'online', 'completed', '2024-01-18 15:00:00', '2024-01-18 15:00:00'),
(1, 'debit', 42500.0000, 'USD', -38249.2500, 'Wire transfer - large purchase', 'REF-100000017', 'wire', 'completed', '2024-01-20 09:00:00', '2024-01-20 09:00:00'),
(2, 'withdrawal', 300.0000, 'USD', 18020.1200, 'ATM withdrawal', 'REF-100000018', 'atm', 'completed', '2024-01-21 12:00:00', '2024-01-21 12:00:00'),
(3, 'deposit', 750.0000, 'USD', 1640.4000, 'Payroll deposit', 'REF-100000019', 'online', 'completed', '2024-01-22 08:00:00', '2024-01-22 08:00:00'),
(4, 'debit', 89.9900, 'USD', 52220.0100, 'Subscription renewal', 'REF-100000020', 'online', 'completed', '2024-01-23 09:00:00', '2024-01-23 09:00:00'),
(5, 'deposit', 500.0000, 'USD', 354.8000, 'Manual deposit to cover overdraft', 'REF-100000021', 'branch', 'completed', '2024-01-24 10:00:00', '2024-01-24 10:00:00'),
(6, 'debit', 130.0000, 'USD', 7470.0000, 'Insurance premium', 'REF-100000022', 'online', 'completed', '2024-01-25 09:00:00', '2024-01-25 09:00:00'),
(7, 'credit', 15000.0000, 'USD', 113750.5000, 'Dividend payout', 'REF-100000023', 'online', 'completed', '2024-01-26 08:00:00', '2024-01-26 08:00:00'),
(8, 'deposit', 400.0000, 'USD', 1600.0000, 'Refund', 'REF-100000024', 'online', 'completed', '2024-01-27 09:00:00', '2024-01-27 09:00:00'),
(9, 'debit', 500.0000, 'USD', 144500.0000, 'Advisory fee', 'REF-100000025', 'online', 'completed', '2024-01-28 09:00:00', '2024-01-28 09:00:00'),
(10, 'deposit', 1200.0000, 'USD', 4600.6000, 'Payroll deposit', 'REF-100000026', 'online', 'completed', '2024-01-29 08:00:00', '2024-01-29 08:00:00'),
(11, 'fee', 25.0000, 'USD', 24975.0000, 'Early withdrawal penalty', 'REF-100000027', 'branch', 'completed', '2024-01-30 10:00:00', '2024-01-30 10:00:00'),
(12, 'deposit', 50.0000, 'USD', 660.0000, 'Mobile check deposit', 'REF-100000028', 'mobile', 'pending', '2024-01-31 11:00:00', '2024-01-31 11:00:00'),
(13, 'debit', 90.0000, 'USD', 9360.3000, 'Online purchase', 'REF-100000029', 'online', 'completed', '2024-02-01 09:00:00', '2024-02-01 09:00:00'),
(14, 'deposit', 3000.0000, 'USD', 5750.0000, 'Bonus deposit', 'REF-100000030', 'online', 'completed', '2024-02-02 08:00:00', '2024-02-02 08:00:00'),
(15, 'debit', 600.0000, 'USD', 15000.9000, 'Rent payment', 'REF-100000031', 'online', 'completed', '2024-02-03 09:00:00', '2024-02-03 09:00:00'),
(1, 'deposit', 1500.0000, 'USD', -36749.2500, 'Payroll deposit', 'REF-100000032', 'online', 'completed', '2024-02-05 08:00:00', '2024-02-05 08:00:00'),
(2, 'debit', 200.0000, 'USD', 17820.1200, 'Insurance payment', 'REF-100000033', 'online', 'completed', '2024-02-06 09:00:00', '2024-02-06 09:00:00'),
(4, 'debit', 25000.0000, 'USD', 27220.0100, 'Wire transfer - real estate deposit', 'REF-100000034', 'wire', 'completed', '2024-02-08 09:00:00', '2024-02-08 09:00:00'),
(6, 'debit', 5000.0000, 'USD', 2470.0000, 'Large electronics purchase', 'REF-100000035', 'online', 'flagged', '2024-02-09 22:15:00', '2024-02-09 22:15:00'),
(7, 'debit', 45000.0000, 'USD', 68750.5000, 'Large brokerage withdrawal', 'REF-100000036', 'wire', 'flagged', '2024-02-10 23:40:00', '2024-02-10 23:40:00'),
(9, 'debit', 500.0000, 'USD', 144000.0000, 'Advisory fee', 'REF-100000037', 'online', 'completed', '2024-02-11 09:00:00', '2024-02-11 09:00:00'),
(10, 'debit', 8000.0000, 'USD', -3399.4000, 'Large cash-like transfer', 'REF-100000038', 'wire', 'flagged', '2024-02-12 03:20:00', '2024-02-12 03:20:00'),
(13, 'debit', 300.0000, 'USD', 9060.3000, 'Online purchase', 'REF-100000039', 'online', 'completed', '2024-02-13 09:00:00', '2024-02-13 09:00:00'),
(15, 'deposit', 700.0000, 'USD', 15700.9000, 'Payroll deposit', 'REF-100000040', 'online', 'completed', '2024-02-14 08:00:00', '2024-02-14 08:00:00');

-- loans (12) -- account_id 1-15
INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, balance, status, originated_at, maturity_date, created_at) VALUES
(1, 'auto', 28000.00, 5.9000, 60, 540.13, 19500.00, 'active', '2022-03-01', '2027-03-01', '2022-03-01 09:00:00'),
(2, 'mortgage', 320000.00, 4.2500, 360, 1574.35, 298000.00, 'active', '2020-06-15', '2050-06-15', '2020-06-15 09:00:00'),
(4, 'business', 150000.00, 7.5000, 84, 2229.87, 92000.00, 'active', '2021-09-01', '2028-09-01', '2021-09-01 09:00:00'),
(6, 'personal', 12000.00, 9.9000, 36, 386.90, 4200.00, 'active', '2023-01-10', '2026-01-10', '2023-01-10 09:00:00'),
(7, 'heloc', 75000.00, 6.7500, 120, 862.50, 61000.00, 'active', '2021-11-20', '2031-11-20', '2021-11-20 09:00:00'),
(8, 'auto', 18000.00, 6.2000, 48, 425.60, 6100.00, 'active', '2022-07-05', '2026-07-05', '2022-07-05 09:00:00'),
(9, 'mortgage', 410000.00, 3.9500, 360, 1946.02, 385000.00, 'active', '2019-04-12', '2049-04-12', '2019-04-12 09:00:00'),
(10, 'student', 45000.00, 5.0000, 120, 477.35, 33000.00, 'active', '2018-08-01', '2028-08-01', '2018-08-01 09:00:00'),
(11, 'personal', 8000.00, 10.5000, 24, 371.90, 0.00, 'paid_off', '2021-02-15', '2023-02-15', '2021-02-15 09:00:00'),
(13, 'auto', 22000.00, 5.5000, 60, 420.35, 15000.00, 'active', '2022-10-10', '2027-10-10', '2022-10-10 09:00:00'),
(14, 'personal', 5000.00, 11.2000, 24, 233.50, 5000.00, 'delinquent', '2023-11-01', '2025-11-01', '2023-11-01 09:00:00'),
(15, 'mortgage', 265000.00, 4.6000, 360, 1361.20, 254000.00, 'active', '2021-05-20', '2051-05-20', '2021-05-20 09:00:00');

-- fraud_alerts (10) -- account_id 1-15, transaction_id 1-40
INSERT INTO fraud_alerts (account_id, transaction_id, rule_name, risk_score, status, description, alerted_at, resolved_at, created_at) VALUES
(6, 34, 'large_amount', 82.50, 'investigating', 'Transaction amount significantly exceeds account average.', '2024-02-09 22:20:00', NULL, '2024-02-09 22:20:00'),
(7, 35, 'unusual_time', 76.00, 'investigating', 'Large withdrawal initiated outside normal business hours.', '2024-02-10 23:45:00', NULL, '2024-02-10 23:45:00'),
(10, 37, 'velocity_check', 91.25, 'open', 'Multiple large transfers within a short window.', '2024-02-12 03:25:00', NULL, '2024-02-12 03:25:00'),
(1, 17, 'large_amount', 68.00, 'resolved', 'Wire transfer flagged for manual review, confirmed by customer.', '2024-01-20 09:10:00', '2024-01-21 10:00:00', '2024-01-20 09:10:00'),
(4, 33, 'large_amount', 55.50, 'false_positive', 'Real estate deposit confirmed as legitimate.', '2024-02-08 09:15:00', '2024-02-08 15:00:00', '2024-02-08 09:15:00'),
(5, 6, 'geo_anomaly', 60.00, 'resolved', 'Fee transaction flagged due to prior overdraft pattern.', '2024-01-08 10:05:00', '2024-01-09 09:00:00', '2024-01-08 10:05:00'),
(9, 24, 'multiple_failures', 40.00, 'false_positive', 'Advisory fee matched recurring billing pattern.', '2024-01-28 09:10:00', '2024-01-28 12:00:00', '2024-01-28 09:10:00'),
(12, 28, 'new_device', 35.00, 'resolved', 'Mobile deposit from a new device, verified via SMS.', '2024-01-31 11:05:00', '2024-01-31 14:00:00', '2024-01-31 11:05:00'),
(2, 18, 'card_not_present', 48.00, 'open', 'ATM withdrawal flagged for pattern review.', '2024-01-21 12:05:00', NULL, '2024-01-21 12:05:00'),
(7, 8, 'blacklist_match', 88.75, 'investigating', 'Counterparty on stock purchase matched a watch-list entity.', '2024-01-10 11:05:00', NULL, '2024-01-10 11:05:00');

-- market_prices (20) -- no FK
INSERT INTO market_prices (ticker, price_open, price_high, price_low, price_close, volume, market, recorded_at, created_at) VALUES
('AAPL', 185.200000, 187.500000, 184.800000, 186.900000, 58230000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('MSFT', 402.100000, 405.750000, 400.500000, 404.300000, 21450000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('AMZN', 155.400000, 157.200000, 154.900000, 156.800000, 34120000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('GOOGL', 141.300000, 143.100000, 140.700000, 142.600000, 26340000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('META', 468.900000, 474.200000, 465.100000, 472.800000, 18760000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('TSLA', 188.500000, 191.300000, 185.200000, 187.100000, 98450000, 'NASDAQ', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('JPM', 172.400000, 174.100000, 171.600000, 173.500000, 9870000, 'NYSE', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('BAC', 33.200000, 33.800000, 32.900000, 33.600000, 41230000, 'NYSE', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('GS', 398.700000, 402.500000, 396.100000, 401.200000, 2340000, 'NYSE', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('V', 275.600000, 277.900000, 274.200000, 276.800000, 6540000, 'NYSE', '2024-02-01 16:00:00', '2024-02-01 16:05:00'),
('AAPL', 186.900000, 189.200000, 186.100000, 188.700000, 61200000, 'NASDAQ', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('MSFT', 404.300000, 407.600000, 403.100000, 406.900000, 19870000, 'NASDAQ', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('AMZN', 156.800000, 159.100000, 156.200000, 158.400000, 30120000, 'NASDAQ', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('TSLA', 187.100000, 193.400000, 186.500000, 192.200000, 105340000, 'NASDAQ', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('UNH', 512.300000, 516.700000, 510.100000, 515.400000, 3120000, 'NYSE', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('JNJ', 158.900000, 160.200000, 158.100000, 159.700000, 6870000, 'NYSE', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('XOM', 102.400000, 103.800000, 101.900000, 103.100000, 15230000, 'NYSE', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('WMT', 165.700000, 167.100000, 165.100000, 166.800000, 8340000, 'NYSE', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('COST', 725.400000, 731.200000, 722.800000, 729.600000, 2450000, 'NASDAQ', '2024-02-02 16:00:00', '2024-02-02 16:05:00'),
('HD', 358.200000, 361.500000, 356.900000, 360.100000, 4120000, 'NYSE', '2024-02-02 16:00:00', '2024-02-02 16:05:00');

-- audit_log (20) -- no FK
INSERT INTO audit_log (user_id, action, table_name, record_id, old_value, new_value, ip_address, user_agent, occurred_at) VALUES
(1, 'LOGIN', NULL, NULL, NULL, NULL, '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-05 09:40:00'),
(1, 'UPDATE', 'accounts', 1, 'balance:2750.75', 'balance:4250.75', '203.0.113.10', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-02 08:00:05'),
(2, 'LOGIN', NULL, NULL, NULL, NULL, '203.0.113.11', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-08 10:40:00'),
(NULL, 'CONFIG_CHANGE', 'promotions', 3, 'active:false', 'active:true', '198.51.100.5', 'internal-admin-tool/1.0', '2024-01-09 08:00:00'),
(3, 'UPDATE', 'orders', 3, 'status:processing', 'status:shipped', '203.0.113.12', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-16 09:00:10'),
(4, 'LOGOUT', NULL, NULL, NULL, NULL, '203.0.113.13', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-18 09:50:00'),
(NULL, 'EXPORT', 'customers', NULL, NULL, NULL, '198.51.100.6', 'internal-admin-tool/1.0', '2024-01-19 07:00:00'),
(5, 'DELETE', 'cart_events', 15, 'event_type:remove_from_cart', NULL, '203.0.113.14', 'Mozilla/5.0 (Android 14; Mobile)', '2024-01-20 14:05:05'),
(6, 'LOGIN', NULL, NULL, NULL, NULL, '203.0.113.15', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-22 09:40:00'),
(NULL, 'PERMISSION_CHANGE', 'gov_employees', 4, 'role:agent', 'role:supervisor', '198.51.100.7', 'internal-admin-tool/1.0', '2024-01-23 10:00:00'),
(7, 'UPDATE', 'transactions', 8, 'status:pending', 'status:completed', '203.0.113.16', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7)', '2024-01-10 11:05:10'),
(8, 'LOGIN', NULL, NULL, NULL, NULL, '203.0.113.17', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-01 08:40:00'),
(NULL, 'IMPORT', 'products', NULL, NULL, NULL, '198.51.100.8', 'internal-admin-tool/1.0', '2024-01-01 06:00:00'),
(9, 'UPDATE', 'accounts', 9, 'balance:143000.00', 'balance:145000.00', '203.0.113.18', 'Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)', '2024-01-12 08:00:05'),
(10, 'INSERT', 'orders', 10, NULL, 'status:pending', '203.0.113.19', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-02-05 13:00:05'),
(NULL, 'CONFIG_CHANGE', 'fraud_alerts', NULL, 'threshold:70', 'threshold:60', '198.51.100.9', 'internal-admin-tool/1.0', '2024-02-09 21:00:00'),
(6, 'UPDATE', 'transactions', 34, 'status:completed', 'status:flagged', '198.51.100.10', 'fraud-engine/2.3', '2024-02-09 22:20:05'),
(7, 'UPDATE', 'transactions', 35, 'status:completed', 'status:flagged', '198.51.100.10', 'fraud-engine/2.3', '2024-02-10 23:45:05'),
(10, 'UPDATE', 'transactions', 37, 'status:completed', 'status:flagged', '198.51.100.10', 'fraud-engine/2.3', '2024-02-12 03:25:05'),
(12, 'LOGIN', NULL, NULL, NULL, NULL, '203.0.113.20', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)', '2024-01-31 10:40:00');

-- ------------------------------------------------------------------
-- MANUFACTURING / IoT DATA
-- ------------------------------------------------------------------

-- machines (8)
INSERT INTO machines (name, model, serial_number, location, plant, status, installed_at, last_service, created_at) VALUES
('CNC Mill Alpha', 'Model-HX40', 'SN-100000001', 'Bay 1', 'Plant A', 'running', '2016-05-01', '2024-01-10', '2016-05-01 08:00:00'),
('CNC Mill Beta', 'Model-HX40', 'SN-100000002', 'Bay 2', 'Plant A', 'running', '2016-06-15', '2024-01-15', '2016-06-15 08:00:00'),
('Injection Molder 1', 'Model-IJ200', 'SN-100000003', 'Bay 5', 'Plant A', 'maintenance', '2017-02-20', '2024-02-01', '2017-02-20 08:00:00'),
('Injection Molder 2', 'Model-IJ200', 'SN-100000004', 'Bay 6', 'Plant B', 'running', '2018-03-10', '2023-12-20', '2018-03-10 08:00:00'),
('Conveyor Line 1', 'Model-CV10', 'SN-100000005', 'Assembly Line 1', 'Plant B', 'running', '2015-11-05', '2024-01-05', '2015-11-05 08:00:00'),
('Robotic Welder A', 'Model-RW7', 'SN-100000006', 'Bay 3', 'Plant B', 'idle', '2019-08-12', '2023-11-30', '2019-08-12 08:00:00'),
('Stamping Press 1', 'Model-SP500', 'SN-100000007', 'Bay 4', 'Facility 1', 'running', '2014-04-18', '2024-02-10', '2014-04-18 08:00:00'),
('Packaging Line A', 'Model-PK3', 'SN-100000008', 'Packaging Zone', 'Facility 1', 'offline', '2020-01-22', '2023-10-15', '2020-01-22 08:00:00');

-- sensors (16) -- machine_id 1-8, 2 sensors per machine
INSERT INTO sensors (machine_id, sensor_type, unit, min_threshold, max_threshold, active, installed_at, created_at) VALUES
(1, 'temperature', '°C', -5.0000, 90.0000, TRUE, '2016-05-01 09:00:00', '2016-05-01 09:00:00'),
(1, 'vibration', 'mm/s', 0.0000, 12.0000, TRUE, '2016-05-01 09:00:00', '2016-05-01 09:00:00'),
(2, 'temperature', '°C', -5.0000, 90.0000, TRUE, '2016-06-15 09:00:00', '2016-06-15 09:00:00'),
(2, 'vibration', 'mm/s', 0.0000, 12.0000, TRUE, '2016-06-15 09:00:00', '2016-06-15 09:00:00'),
(3, 'temperature', '°C', 0.0000, 220.0000, TRUE, '2017-02-20 09:00:00', '2017-02-20 09:00:00'),
(3, 'pressure', 'bar', 0.0000, 150.0000, TRUE, '2017-02-20 09:00:00', '2017-02-20 09:00:00'),
(4, 'temperature', '°C', 0.0000, 220.0000, TRUE, '2018-03-10 09:00:00', '2018-03-10 09:00:00'),
(4, 'pressure', 'bar', 0.0000, 150.0000, TRUE, '2018-03-10 09:00:00', '2018-03-10 09:00:00'),
(5, 'rpm', 'RPM', 0.0000, 500.0000, TRUE, '2015-11-05 09:00:00', '2015-11-05 09:00:00'),
(5, 'current', 'A', 0.0000, 40.0000, TRUE, '2015-11-05 09:00:00', '2015-11-05 09:00:00'),
(6, 'voltage', 'V', 200.0000, 480.0000, TRUE, '2019-08-12 09:00:00', '2019-08-12 09:00:00'),
(6, 'torque', 'Nm', 0.0000, 250.0000, TRUE, '2019-08-12 09:00:00', '2019-08-12 09:00:00'),
(7, 'pressure', 'bar', 0.0000, 300.0000, TRUE, '2014-04-18 09:00:00', '2014-04-18 09:00:00'),
(7, 'vibration', 'mm/s', 0.0000, 15.0000, TRUE, '2014-04-18 09:00:00', '2014-04-18 09:00:00'),
(8, 'humidity', '%', 10.0000, 70.0000, FALSE, '2020-01-22 09:00:00', '2020-01-22 09:00:00'),
(8, 'co2_level', 'ppm', 300.0000, 1000.0000, FALSE, '2020-01-22 09:00:00', '2020-01-22 09:00:00');

-- sensor_readings (40) -- sensor_id 1-16, machine_id 1-8
INSERT INTO sensor_readings (sensor_id, machine_id, value, quality, alarm, recorded_at) VALUES
(1, 1, 62.400000, 192, FALSE, '2024-02-01 08:00:00'),
(2, 1, 4.200000, 192, FALSE, '2024-02-01 08:00:00'),
(1, 1, 65.100000, 192, FALSE, '2024-02-01 09:00:00'),
(2, 1, 5.900000, 192, FALSE, '2024-02-01 09:00:00'),
(3, 2, 58.700000, 192, FALSE, '2024-02-01 08:00:00'),
(4, 2, 3.800000, 192, FALSE, '2024-02-01 08:00:00'),
(3, 2, 91.200000, 64, TRUE, '2024-02-01 09:00:00'),
(4, 2, 13.500000, 64, TRUE, '2024-02-01 09:00:00'),
(5, 3, 205.500000, 192, FALSE, '2024-02-01 08:00:00'),
(6, 3, 132.100000, 192, FALSE, '2024-02-01 08:00:00'),
(5, 3, 218.900000, 0, TRUE, '2024-02-01 09:00:00'),
(6, 3, 148.700000, 0, TRUE, '2024-02-01 09:00:00'),
(7, 4, 180.300000, 192, FALSE, '2024-02-01 08:00:00'),
(8, 4, 110.400000, 192, FALSE, '2024-02-01 08:00:00'),
(7, 4, 175.600000, 192, FALSE, '2024-02-01 09:00:00'),
(8, 4, 108.200000, 192, FALSE, '2024-02-01 09:00:00'),
(9, 5, 320.500000, 192, FALSE, '2024-02-01 08:00:00'),
(10, 5, 22.100000, 192, FALSE, '2024-02-01 08:00:00'),
(9, 5, 315.200000, 192, FALSE, '2024-02-01 09:00:00'),
(10, 5, 21.800000, 192, FALSE, '2024-02-01 09:00:00'),
(11, 6, 415.000000, 128, FALSE, '2024-02-01 08:00:00'),
(12, 6, 180.500000, 128, FALSE, '2024-02-01 08:00:00'),
(11, 6, 420.300000, 128, FALSE, '2024-02-01 09:00:00'),
(12, 6, 185.900000, 128, FALSE, '2024-02-01 09:00:00'),
(13, 7, 265.400000, 192, FALSE, '2024-02-01 08:00:00'),
(14, 7, 9.100000, 192, FALSE, '2024-02-01 08:00:00'),
(13, 7, 310.800000, 0, TRUE, '2024-02-01 09:00:00'),
(14, 7, 16.400000, 0, TRUE, '2024-02-01 09:00:00'),
(15, 8, 45.200000, 64, FALSE, '2024-02-01 08:00:00'),
(16, 8, 620.000000, 64, FALSE, '2024-02-01 08:00:00'),
(15, 8, 47.800000, 64, FALSE, '2024-02-01 09:00:00'),
(16, 8, 640.500000, 64, FALSE, '2024-02-01 09:00:00'),
(1, 1, 63.800000, 192, FALSE, '2024-02-02 08:00:00'),
(3, 2, 60.100000, 192, FALSE, '2024-02-02 08:00:00'),
(5, 3, 210.200000, 192, FALSE, '2024-02-02 08:00:00'),
(7, 4, 178.900000, 192, FALSE, '2024-02-02 08:00:00'),
(9, 5, 318.700000, 192, FALSE, '2024-02-02 08:00:00'),
(11, 6, 418.200000, 128, FALSE, '2024-02-02 08:00:00'),
(13, 7, 270.100000, 192, FALSE, '2024-02-02 08:00:00'),
(15, 8, 46.500000, 64, FALSE, '2024-02-02 08:00:00');

-- production_batches (15) -- machine_id 1-8
INSERT INTO production_batches (machine_id, product_code, quantity, unit, status, started_at, completed_at, operator_id, created_at) VALUES
(1, 'PROD-ALFA-0001', 500, 'units', 'completed', '2024-01-05 06:00:00', '2024-01-05 14:00:00', 101, '2024-01-05 06:00:00'),
(2, 'PROD-ALFA-0002', 480, 'units', 'completed', '2024-01-06 06:00:00', '2024-01-06 15:00:00', 102, '2024-01-06 06:00:00'),
(3, 'PROD-BETA-0001', 2000, 'units', 'completed', '2024-01-08 06:00:00', '2024-01-08 20:00:00', 103, '2024-01-08 06:00:00'),
(4, 'PROD-BETA-0002', 2200, 'units', 'in_progress', '2024-02-05 06:00:00', NULL, 104, '2024-02-05 06:00:00'),
(5, 'PROD-GAMMA-0001', 5000, 'meters', 'completed', '2024-01-10 06:00:00', '2024-01-10 18:00:00', 105, '2024-01-10 06:00:00'),
(6, 'PROD-DELTA-0001', 300, 'units', 'on_hold', '2024-01-12 06:00:00', NULL, NULL, '2024-01-12 06:00:00'),
(7, 'PROD-EPS-0001', 8000, 'units', 'completed', '2024-01-14 06:00:00', '2024-01-14 22:00:00', 106, '2024-01-14 06:00:00'),
(8, 'PROD-ZETA-0001', 10000, 'units', 'failed', '2024-01-16 06:00:00', '2024-01-16 10:00:00', 107, '2024-01-16 06:00:00'),
(1, 'PROD-ALFA-0003', 520, 'units', 'completed', '2024-01-20 06:00:00', '2024-01-20 14:30:00', 101, '2024-01-20 06:00:00'),
(2, 'PROD-ALFA-0004', 495, 'units', 'completed', '2024-01-22 06:00:00', '2024-01-22 15:15:00', 102, '2024-01-22 06:00:00'),
(3, 'PROD-BETA-0003', 1950, 'units', 'completed', '2024-01-25 06:00:00', '2024-01-25 19:45:00', 103, '2024-01-25 06:00:00'),
(5, 'PROD-GAMMA-0002', 4800, 'meters', 'completed', '2024-01-28 06:00:00', '2024-01-28 17:30:00', 105, '2024-01-28 06:00:00'),
(7, 'PROD-EPS-0002', 7800, 'units', 'completed', '2024-02-01 06:00:00', '2024-02-01 21:30:00', 106, '2024-02-01 06:00:00'),
(1, 'PROD-ALFA-0005', 510, 'units', 'in_progress', '2024-02-10 06:00:00', NULL, 101, '2024-02-10 06:00:00'),
(4, 'PROD-BETA-0004', 2100, 'units', 'completed', '2024-01-30 06:00:00', '2024-01-30 19:00:00', 104, '2024-01-30 06:00:00');

-- quality_checks (20) -- batch_id 1-15, machine_id 1-8
INSERT INTO quality_checks (batch_id, machine_id, check_type, result, score, defect_count, inspector_id, checked_at, notes, created_at) VALUES
(1, 1, 'dimensional', 'pass', 98.50, 2, 201, '2024-01-05 14:15:00', NULL, '2024-01-05 14:15:00'),
(2, 2, 'dimensional', 'pass', 97.20, 3, 201, '2024-01-06 15:15:00', NULL, '2024-01-06 15:15:00'),
(3, 3, 'visual_inspection', 'pass', 95.00, 8, 202, '2024-01-08 20:15:00', 'Minor surface blemishes within tolerance', '2024-01-08 20:15:00'),
(5, 5, 'functional', 'pass', 99.10, 1, 203, '2024-01-10 18:15:00', NULL, '2024-01-10 18:15:00'),
(7, 7, 'electrical', 'conditional', 88.00, 12, 204, '2024-01-14 22:15:00', 'Retest recommended on 3 units', '2024-01-14 22:15:00'),
(8, 8, 'chemical', 'fail', 42.50, 65, 205, '2024-01-16 10:15:00', 'Batch scrapped due to contamination', '2024-01-16 10:15:00'),
(9, 1, 'dimensional', 'pass', 99.00, 1, 201, '2024-01-20 14:45:00', NULL, '2024-01-20 14:45:00'),
(10, 2, 'dimensional', 'pass', 96.80, 4, 201, '2024-01-22 15:30:00', NULL, '2024-01-22 15:30:00'),
(11, 3, 'visual_inspection', 'pass', 94.50, 9, 202, '2024-01-25 20:00:00', NULL, '2024-01-25 20:00:00'),
(12, 5, 'functional', 'pass', 98.70, 2, 203, '2024-01-28 17:45:00', NULL, '2024-01-28 17:45:00'),
(13, 7, 'electrical', 'pass', 93.20, 5, 204, '2024-02-01 21:45:00', NULL, '2024-02-01 21:45:00'),
(15, 4, 'dimensional', 'pass', 97.90, 3, 206, '2024-01-30 19:15:00', NULL, '2024-01-30 19:15:00'),
(1, 1, 'functional', 'pass', 97.80, 1, 201, '2024-01-05 15:00:00', NULL, '2024-01-05 15:00:00'),
(3, 3, 'chemical', 'conditional', 85.00, 15, 202, '2024-01-08 21:00:00', 'Elevated defect rate, monitoring next batch', '2024-01-08 21:00:00'),
(7, 7, 'visual_inspection', 'pass', 91.50, 7, 204, '2024-01-14 22:30:00', NULL, '2024-01-14 22:30:00'),
(9, 1, 'functional', 'pass', 98.20, 1, 201, '2024-01-20 15:00:00', NULL, '2024-01-20 15:00:00'),
(10, 2, 'electrical', 'pass', 95.60, 3, 201, '2024-01-22 15:45:00', NULL, '2024-01-22 15:45:00'),
(11, 3, 'dimensional', 'fail', 58.00, 45, 202, '2024-01-25 20:15:00', 'Off-spec tolerances detected, escalated', '2024-01-25 20:15:00'),
(13, 7, 'chemical', 'pass', 96.40, 2, 204, '2024-02-01 22:00:00', NULL, '2024-02-01 22:00:00'),
(15, 4, 'functional', 'pass', 99.30, 0, 206, '2024-01-30 19:30:00', NULL, '2024-01-30 19:30:00');
-- MANUFACTURING / IoT DATA (end)
-- ------------------------------------------------------------------

-- ------------------------------------------------------------------
-- RETAIL DATA
-- ------------------------------------------------------------------

-- stores (10)
INSERT INTO stores (name, code, address, city, state, zip, region, store_type, opened_date, active, created_at) VALUES
('Downtown Austin', 'STR-001', '400 Congress Ave', 'Austin', 'TX', '73301', 'South Central', 'flagship', '2015-03-01', TRUE, '2015-03-01 00:00:00'),
('North Dallas', 'STR-002', '5000 Preston Rd', 'Dallas', 'TX', '75201', 'South Central', 'standard', '2016-05-15', TRUE, '2016-05-15 00:00:00'),
('Houston Galleria', 'STR-003', '5085 Westheimer Rd', 'Houston', 'TX', '77001', 'South Central', 'standard', '2016-09-10', TRUE, '2016-09-10 00:00:00'),
('San Antonio Riverwalk', 'STR-004', '849 E Commerce St', 'San Antonio', 'TX', '78201', 'South Central', 'standard', '2017-02-20', TRUE, '2017-02-20 00:00:00'),
('Austin South', 'STR-005', '2400 S Lamar Blvd', 'Austin', 'TX', '73302', 'South Central', 'express', '2018-06-01', TRUE, '2018-06-01 00:00:00'),
('Toronto Eaton Centre', 'STR-006', '220 Yonge St', 'Toronto', 'ON', 'M5B2H1', 'Canada', 'standard', '2018-11-15', TRUE, '2018-11-15 00:00:00'),
('London Oxford St', 'STR-007', '214 Oxford St', 'London', 'ENG', 'W1D1AN', 'Europe', 'standard', '2019-04-01', TRUE, '2019-04-01 00:00:00'),
('Dallas Outlet', 'STR-008', '12801 Dallas Pkwy', 'Dallas', 'TX', '75202', 'South Central', 'outlet', '2019-08-20', TRUE, '2019-08-20 00:00:00'),
('Houston Warehouse', 'STR-009', '9950 Katy Fwy', 'Houston', 'TX', '77002', 'South Central', 'warehouse', '2020-01-10', TRUE, '2020-01-10 00:00:00'),
('San Antonio North', 'STR-010', '18333 Blanco Rd', 'San Antonio', 'TX', '78202', 'South Central', 'express', '2021-05-05', FALSE, '2021-05-05 00:00:00');

-- inventory (30) -- store_id 1-10, product_id 1-25
INSERT INTO inventory (store_id, product_id, quantity_on_hand, quantity_reserved, reorder_point, reorder_qty, last_counted, updated_at) VALUES
(1, 1, 45, 3, 10, 50, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(1, 2, 80, 5, 15, 60, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(1, 22, 12, 1, 5, 20, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(2, 3, 120, 10, 25, 100, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(2, 4, 30, 2, 8, 30, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(2, 9, 15, 0, 5, 15, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(3, 5, 60, 4, 15, 50, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(3, 6, 90, 6, 20, 70, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(3, 10, 25, 2, 8, 25, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(4, 7, 18, 1, 5, 15, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(4, 8, 6, 0, 3, 10, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(4, 11, 40, 3, 10, 40, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(5, 12, 55, 5, 15, 40, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(5, 13, 70, 4, 15, 50, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(5, 14, 33, 2, 10, 30, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(6, 15, 28, 1, 8, 25, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(6, 16, 50, 3, 12, 40, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(6, 17, 22, 2, 8, 20, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(7, 18, 14, 1, 5, 15, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(7, 19, 19, 0, 6, 18, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(7, 20, 65, 5, 15, 50, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(8, 21, 8, 2, 20, 60, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(8, 23, 27, 1, 8, 25, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(8, 24, 44, 3, 12, 35, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(9, 25, 95, 6, 20, 80, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(9, 1, 60, 4, 15, 50, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(9, 9, 22, 2, 8, 25, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(10, 2, 5, 0, 10, 40, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(10, 6, 9, 1, 12, 45, '2024-02-01 06:00:00', '2024-02-01 06:00:00'),
(10, 14, 3, 0, 8, 25, '2024-02-01 06:00:00', '2024-02-01 06:00:00');


-- pos_transactions (20) -- store_id 1-10, customer_id 1-20 (nullable for walk-ins)
INSERT INTO pos_transactions (store_id, register_id, cashier_id, customer_id, subtotal, tax, discount, total, payment_method, status, transacted_at, created_at) VALUES
(1, 1, 301, 1, 199.99, 16.00, 0.00, 215.99, 'credit_card', 'completed', '2024-01-06 10:15:00', '2024-01-06 10:15:00'),
(1, 2, 302, NULL, 24.99, 2.00, 0.00, 26.99, 'cash', 'completed', '2024-01-07 14:30:00', '2024-01-07 14:30:00'),
(2, 1, 303, 3, 89.99, 7.20, 5.00, 92.19, 'credit_card', 'completed', '2024-01-09 11:00:00', '2024-01-09 11:00:00'),
(2, 2, 304, NULL, 59.99, 4.80, 0.00, 64.79, 'debit_card', 'completed', '2024-01-10 09:45:00', '2024-01-10 09:45:00'),
(3, 1, 305, 5, 149.99, 12.00, 0.00, 161.99, 'credit_card', 'completed', '2024-01-11 13:20:00', '2024-01-11 13:20:00'),
(3, 2, 306, NULL, 34.99, 2.80, 0.00, 37.79, 'cash', 'completed', '2024-01-12 16:00:00', '2024-01-12 16:00:00'),
(4, 1, 307, 7, 129.99, 10.40, 0.00, 140.39, 'credit_card', 'completed', '2024-01-13 10:00:00', '2024-01-13 10:00:00'),
(4, 2, 308, NULL, 79.99, 6.40, 0.00, 86.39, 'debit_card', 'completed', '2024-01-14 15:15:00', '2024-01-14 15:15:00'),
(5, 1, 309, 9, 44.99, 3.60, 0.00, 48.59, 'credit_card', 'completed', '2024-01-15 12:30:00', '2024-01-15 12:30:00'),
(5, 2, 310, NULL, 24.99, 2.00, 0.00, 26.99, 'cash', 'refunded', '2024-01-16 09:00:00', '2024-01-16 09:00:00'),
(6, 1, 311, 11, 39.99, 3.20, 0.00, 43.19, 'credit_card', 'completed', '2024-01-17 11:45:00', '2024-01-17 11:45:00'),
(6, 2, 312, NULL, 59.99, 4.80, 5.00, 59.79, 'debit_card', 'completed', '2024-01-18 14:00:00', '2024-01-18 14:00:00'),
(7, 1, 313, 13, 149.99, 12.00, 0.00, 161.99, 'credit_card', 'completed', '2024-01-19 10:30:00', '2024-01-19 10:30:00'),
(7, 2, 314, NULL, 29.99, 2.40, 0.00, 32.39, 'cash', 'completed', '2024-01-20 13:00:00', '2024-01-20 13:00:00'),
(8, 1, 315, 15, 99.99, 8.00, 0.00, 107.99, 'credit_card', 'voided', '2024-01-21 09:15:00', '2024-01-21 09:15:00'),
(8, 2, 316, NULL, 24.99, 2.00, 0.00, 26.99, 'debit_card', 'completed', '2024-01-22 15:45:00', '2024-01-22 15:45:00'),
(9, 1, 317, 17, 69.99, 5.60, 0.00, 75.59, 'credit_card', 'completed', '2024-01-23 12:00:00', '2024-01-23 12:00:00'),
(9, 2, 318, NULL, 199.99, 16.00, 10.00, 205.99, 'credit_card', 'completed', '2024-01-24 10:45:00', '2024-01-24 10:45:00'),
(10, 1, 319, 19, 34.99, 2.80, 0.00, 37.79, 'cash', 'completed', '2024-01-25 14:15:00', '2024-01-25 14:15:00'),
(10, 2, 320, NULL, 159.99, 12.80, 0.00, 172.79, 'debit_card', 'completed', '2024-01-26 11:30:00', '2024-01-26 11:30:00');

-- pos_items (20) -- pos_tx_id 1-20, product_id 1-25 (1:1 with pos_transactions, single line item each)
INSERT INTO pos_items (pos_tx_id, product_id, quantity, unit_price, discount, line_total, created_at) VALUES
(1, 1, 1, 199.99, 0.00, 199.99, '2024-01-06 10:15:00'),
(2, 3, 1, 24.99, 0.00, 24.99, '2024-01-07 14:30:00'),
(3, 10, 1, 89.99, 5.00, 84.99, '2024-01-09 11:00:00'),
(4, 24, 1, 59.99, 0.00, 59.99, '2024-01-10 09:45:00'),
(5, 7, 1, 149.99, 0.00, 149.99, '2024-01-11 13:20:00'),
(6, 6, 1, 34.99, 0.00, 34.99, '2024-01-12 16:00:00'),
(7, 4, 1, 129.99, 0.00, 129.99, '2024-01-13 10:00:00'),
(8, 19, 1, 79.99, 0.00, 79.99, '2024-01-14 15:15:00'),
(9, 13, 1, 44.99, 0.00, 44.99, '2024-01-15 12:30:00'),
(10, 3, 1, 24.99, 0.00, 24.99, '2024-01-16 09:00:00'),
(11, 12, 1, 39.99, 0.00, 39.99, '2024-01-17 11:45:00'),
(12, 24, 1, 59.99, 5.00, 54.99, '2024-01-18 14:00:00'),
(13, 7, 1, 149.99, 0.00, 149.99, '2024-01-19 10:30:00'),
(14, 14, 1, 29.99, 0.00, 29.99, '2024-01-20 13:00:00'),
(15, 11, 1, 99.99, 0.00, 99.99, '2024-01-21 09:15:00'),
(16, 3, 1, 24.99, 0.00, 24.99, '2024-01-22 15:45:00'),
(17, 18, 1, 69.99, 0.00, 69.99, '2024-01-23 12:00:00'),
(18, 1, 1, 199.99, 10.00, 189.99, '2024-01-24 10:45:00'),
(19, 6, 1, 34.99, 0.00, 34.99, '2024-01-25 14:15:00'),
(20, 23, 1, 159.99, 0.00, 159.99, '2024-01-26 11:30:00');

-- loyalty_members (20) -- no FK (standalone loyalty program roster)
INSERT INTO loyalty_members (email, first_name, last_name, phone, tier, points, lifetime_spend, enrolled_at, last_activity, active, created_at) VALUES
('nancy.ortiz@example.com', 'Nancy', 'Ortiz', '555-501-0001', 'gold', 4200, 1850.75, '2020-01-15 09:00:00', '2024-03-01 10:00:00', TRUE, '2020-01-15 09:00:00'),
('victor.reyes@example.com', 'Victor', 'Reyes', '555-501-0002', 'silver', 1350, 620.40, '2020-02-20 09:00:00', '2024-02-15 10:00:00', TRUE, '2020-02-20 09:00:00'),
('wendy.shaw@example.com', 'Wendy', 'Shaw', '555-501-0003', 'bronze', 320, 145.00, '2021-03-10 09:00:00', '2023-11-01 10:00:00', TRUE, '2021-03-10 09:00:00'),
('xavier.long@example.com', 'Xavier', 'Long', '555-501-0004', 'platinum', 9800, 5230.60, '2019-06-05 09:00:00', '2024-03-05 10:00:00', TRUE, '2019-06-05 09:00:00'),
('yolanda.price@example.com', 'Yolanda', 'Price', '555-501-0005', 'gold', 3900, 1620.20, '2020-07-18 09:00:00', '2024-01-20 10:00:00', TRUE, '2020-07-18 09:00:00'),
('zachary.holt@example.com', 'Zachary', 'Holt', '555-501-0006', 'bronze', 150, 88.50, '2022-01-25 09:00:00', '2023-08-14 10:00:00', TRUE, '2022-01-25 09:00:00'),
('amanda.cruz@example.com', 'Amanda', 'Cruz', '555-501-0007', 'silver', 1780, 745.00, '2020-09-14 09:00:00', '2024-02-28 10:00:00', TRUE, '2020-09-14 09:00:00'),
('benjamin.reed@example.com', 'Benjamin', 'Reed', '555-501-0008', 'diamond', 15600, 9840.00, '2018-11-02 09:00:00', '2024-03-08 10:00:00', TRUE, '2018-11-02 09:00:00'),
('cynthia.woods@example.com', 'Cynthia', 'Woods', '555-501-0009', 'bronze', 60, 32.99, '2023-05-01 09:00:00', '2023-06-01 10:00:00', FALSE, '2023-05-01 09:00:00'),
('daniel.russo@example.com', 'Daniel', 'Russo', '555-501-0010', 'gold', 4550, 2010.00, '2019-12-12 09:00:00', '2024-02-20 10:00:00', TRUE, '2019-12-12 09:00:00'),
('erin.mccoy@example.com', 'Erin', 'McCoy', '555-501-0011', 'silver', 2100, 890.25, '2021-04-22 09:00:00', '2024-01-10 10:00:00', TRUE, '2021-04-22 09:00:00'),
('felix.harmon@example.com', 'Felix', 'Harmon', '555-501-0012', 'bronze', 410, 210.00, '2022-08-09 09:00:00', '2023-12-15 10:00:00', TRUE, '2022-08-09 09:00:00'),
('georgia.pratt@example.com', 'Georgia', 'Pratt', '555-501-0013', 'platinum', 8700, 4600.50, '2019-03-30 09:00:00', '2024-03-02 10:00:00', TRUE, '2019-03-30 09:00:00'),
('hector.dunn@example.com', 'Hector', 'Dunn', '555-501-0014', 'bronze', 90, 45.00, '2023-09-19 09:00:00', '2023-10-01 10:00:00', TRUE, '2023-09-19 09:00:00'),
('irene.combs@example.com', 'Irene', 'Combs', '555-501-0015', 'gold', 3600, 1490.30, '2020-10-27 09:00:00', '2024-02-11 10:00:00', TRUE, '2020-10-27 09:00:00'),
('jonas.whitfield@example.com', 'Jonas', 'Whitfield', '555-501-0016', 'silver', 1900, 810.00, '2021-01-06 09:00:00', '2024-01-28 10:00:00', TRUE, '2021-01-06 09:00:00'),
('karla.sweeney@example.com', 'Karla', 'Sweeney', '555-501-0017', 'diamond', 21000, 12300.75, '2017-05-14 09:00:00', '2024-03-09 10:00:00', TRUE, '2017-05-14 09:00:00'),
('leon.tapia@example.com', 'Leon', 'Tapia', '555-501-0018', 'bronze', 25, 19.99, '2024-01-02 09:00:00', '2024-01-05 10:00:00', TRUE, '2024-01-02 09:00:00'),
('miriam.gallo@example.com', 'Miriam', 'Gallo', '555-501-0019', 'gold', 4100, 1780.00, '2020-04-16 09:00:00', '2024-02-24 10:00:00', TRUE, '2020-04-16 09:00:00'),
('omar.jennings@example.com', 'Omar', 'Jennings', '555-501-0020', 'silver', 1500, 655.40, '2021-08-08 09:00:00', '2023-12-30 10:00:00', FALSE, '2021-08-08 09:00:00');

-- promotions (15) -- no FK (standalone promo catalog)
INSERT INTO promotions (name, promo_code, discount_type, discount_value, min_purchase, max_discount, start_date, end_date, usage_limit, times_used, active, created_at) VALUES
('New Year Sale', 'NEWYEAR24', 'percent', 15.0000, 50.00, 100.00, '2024-01-01', '2024-01-15', 5000, 1820, TRUE, '2023-12-20 00:00:00'),
('Spring Electronics', 'SPRING10', 'percent', 10.0000, 100.00, 75.00, '2024-03-01', '2024-03-31', 2000, 340, TRUE, '2024-02-20 00:00:00'),
('Free Shipping Weekend', 'FREESHIP', 'fixed', 9.99, 25.00, NULL, '2024-02-10', '2024-02-12', NULL, 610, TRUE, '2024-02-01 00:00:00'),
('Loyalty Gold Bonus', 'GOLD5OFF', 'fixed', 5.00, 20.00, NULL, '2024-01-01', '2024-12-31', 10000, 2450, TRUE, '2024-01-01 00:00:00'),
('Clearance Blowout', 'CLEAR20', 'percent', 20.0000, 0.00, 150.00, '2024-01-20', '2024-02-05', 1000, 980, FALSE, '2024-01-15 00:00:00'),
('Summer Kickoff', 'SUMMER24', 'percent', 12.0000, 75.00, 90.00, '2024-06-01', '2024-06-30', 3000, 0, TRUE, '2024-05-15 00:00:00'),
('First Order Discount', 'WELCOME10', 'percent', 10.0000, 0.00, 50.00, '2023-01-01', NULL, NULL, 5230, TRUE, '2023-01-01 00:00:00'),
('Flash Sale Friday', 'FLASH25', 'percent', 25.0000, 30.00, 60.00, '2024-02-16', '2024-02-16', 500, 500, FALSE, '2024-02-10 00:00:00'),
('Bundle Savings', 'BUNDLE15', 'fixed', 15.00, 80.00, NULL, '2024-01-10', '2024-04-10', 4000, 720, TRUE, '2024-01-05 00:00:00'),
('VIP Platinum Perks', 'VIP7OFF', 'fixed', 7.50, 0.00, NULL, '2024-01-01', '2024-12-31', NULL, 1340, TRUE, '2024-01-01 00:00:00'),
('Back to School', 'SCHOOL24', 'percent', 18.0000, 40.00, 80.00, '2024-08-01', '2024-08-31', 2500, 0, TRUE, '2024-07-15 00:00:00'),
('Winter Storewide', 'WINTER24', 'percent', 22.0000, 60.00, 120.00, '2023-12-01', '2023-12-26', 6000, 5980, FALSE, '2023-11-20 00:00:00'),
('Referral Reward', 'REFER10', 'fixed', 10.00, 25.00, NULL, '2023-06-01', NULL, NULL, 890, TRUE, '2023-06-01 00:00:00'),
('Retail Anniversary', 'ANNIV24', 'percent', 15.0000, 50.00, 100.00, '2024-05-01', '2024-05-10', 1500, 0, TRUE, '2024-04-20 00:00:00'),
('Employee Family Discount', 'FAMILY12', 'percent', 12.0000, 0.00, 50.00, '2024-01-01', '2024-12-31', NULL, 210, TRUE, '2024-01-01 00:00:00');

-- ------------------------------------------------------------------
-- GOVERNMENT DATA
-- ------------------------------------------------------------------

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

-- permits (20) -- citizen_id 1-20
INSERT INTO permits (citizen_id, permit_type, permit_number, description, status, issued_at, expires_at, fee_paid, created_at) VALUES
(1, 'building', 'PMT-B-0001', 'Residential deck addition', 'approved', '2023-03-01', '2024-03-01', 150.00, '2023-02-15 09:00:00'),
(2, 'business', 'PMT-BZ-0001', 'Retail storefront license renewal', 'approved', '2023-01-15', '2025-01-15', 300.00, '2023-01-05 09:00:00'),
(3, 'event', 'PMT-E-0001', 'Neighborhood block party', 'approved', '2023-06-01', '2023-06-02', 50.00, '2023-05-20 09:00:00'),
(4, 'parking', 'PMT-P-0001', 'Residential parking permit', 'approved', '2023-01-01', '2023-12-31', 25.00, '2022-12-20 09:00:00'),
(5, 'contractor', 'PMT-C-0001', 'General contractor license', 'approved', '2022-05-01', '2025-05-01', 500.00, '2022-04-15 09:00:00'),
(6, 'building', 'PMT-B-0002', 'Garage conversion', 'pending', NULL, NULL, 0.00, '2024-01-10 09:00:00'),
(7, 'alcohol', 'PMT-A-0001', 'Restaurant beer and wine license', 'approved', '2023-08-01', '2024-08-01', 800.00, '2023-07-15 09:00:00'),
(8, 'business', 'PMT-BZ-0002', 'Home-based bakery license', 'denied', NULL, NULL, 0.00, '2024-01-20 09:00:00'),
(9, 'firearm', 'PMT-F-0001', 'Concealed carry permit', 'approved', '2022-09-01', '2027-09-01', 100.00, '2022-08-10 09:00:00'),
(10, 'event', 'PMT-E-0002', 'Charity 5K road closure', 'approved', '2023-09-15', '2023-09-16', 75.00, '2023-08-30 09:00:00'),
(11, 'building', 'PMT-B-0003', 'Backyard swimming pool', 'approved', '2023-04-10', '2024-04-10', 250.00, '2023-03-25 09:00:00'),
(12, 'parking', 'PMT-P-0002', 'Commercial loading zone permit', 'expired', '2022-01-01', '2022-12-31', 40.00, '2021-12-15 09:00:00'),
(13, 'contractor', 'PMT-C-0002', 'Electrical contractor license', 'approved', '2023-02-01', '2026-02-01', 500.00, '2023-01-20 09:00:00'),
(14, 'business', 'PMT-BZ-0003', 'Food truck vendor permit', 'approved', '2023-11-01', '2024-11-01', 200.00, '2023-10-15 09:00:00'),
(15, 'building', 'PMT-B-0004', 'Fence installation', 'approved', '2024-01-05', '2024-07-05', 60.00, '2023-12-20 09:00:00'),
(16, 'event', 'PMT-E-0003', 'Farmers market vendor day', 'pending', NULL, NULL, 0.00, '2024-02-01 09:00:00'),
(17, 'alcohol', 'PMT-A-0002', 'Special event liquor license', 'revoked', '2022-06-01', '2023-06-01', 400.00, '2022-05-15 09:00:00'),
(18, 'firearm', 'PMT-F-0002', 'Firearms dealer license', 'approved', '2021-03-01', '2026-03-01', 750.00, '2021-02-10 09:00:00'),
(19, 'building', 'PMT-B-0005', 'Home addition remodel', 'approved', '2023-07-01', '2024-07-01', 180.00, '2023-06-15 09:00:00'),
(20, 'business', 'PMT-BZ-0004', 'Salon operating license', 'approved', '2023-05-01', '2025-05-01', 220.00, '2023-04-20 09:00:00');

-- violations (18) -- citizen_id 1-20
INSERT INTO violations (citizen_id, violation_type, code, description, fine_amount, paid_amount, status, occurred_at, due_date, created_at) VALUES
(1, 'parking', 'VC-2201', 'Parked in a no-parking zone', 75.00, 75.00, 'paid', '2023-11-01 09:15:00', '2023-12-01', '2023-11-01 09:15:00'),
(2, 'speeding', 'VC-2202', 'Exceeded posted speed limit by 12 mph', 150.00, 0.00, 'unpaid', '2023-11-05 14:20:00', '2023-12-05', '2023-11-05 14:20:00'),
(3, 'noise_ordinance', 'VC-2203', 'Excessive noise after 10pm', 100.00, 100.00, 'paid', '2023-11-10 22:45:00', '2023-12-10', '2023-11-10 22:45:00'),
(4, 'littering', 'VC-2204', 'Improper disposal of trash', 50.00, 0.00, 'unpaid', '2023-11-15 08:30:00', '2023-12-15', '2023-11-15 08:30:00'),
(5, 'expired_registration', 'VC-2205', 'Vehicle registration expired over 60 days', 90.00, 90.00, 'paid', '2023-11-20 10:00:00', '2023-12-20', '2023-11-20 10:00:00'),
(6, 'parking', 'VC-2206', 'Parked in disabled space without permit', 250.00, 0.00, 'disputed', '2023-11-22 13:10:00', '2023-12-22', '2023-11-22 13:10:00'),
(7, 'speeding', 'VC-2207', 'School zone speeding violation', 300.00, 300.00, 'paid', '2023-11-25 07:50:00', '2023-12-25', '2023-11-25 07:50:00'),
(8, 'illegal_dumping', 'VC-2208', 'Dumping construction debris on public land', 500.00, 0.00, 'unpaid', '2023-12-01 16:00:00', '2024-01-01', '2023-12-01 16:00:00'),
(9, 'noise_ordinance', 'VC-2209', 'Construction noise outside permitted hours', 120.00, 0.00, 'unpaid', '2023-12-05 06:30:00', '2024-01-05', '2023-12-05 06:30:00'),
(10, 'parking', 'VC-2210', 'Expired parking meter', 40.00, 40.00, 'paid', '2023-12-08 11:20:00', '2024-01-08', '2023-12-08 11:20:00'),
(11, 'speeding', 'VC-2211', 'Exceeded speed limit by 20 mph', 220.00, 0.00, 'unpaid', '2023-12-12 17:45:00', '2024-01-12', '2023-12-12 17:45:00'),
(12, 'littering', 'VC-2212', 'Cigarette litter in public park', 35.00, 35.00, 'paid', '2023-12-15 09:00:00', '2024-01-15', '2023-12-15 09:00:00'),
(13, 'expired_registration', 'VC-2213', 'Failure to renew vehicle registration', 90.00, 0.00, 'unpaid', '2023-12-18 12:15:00', '2024-01-18', '2023-12-18 12:15:00'),
(14, 'parking', 'VC-2214', 'Blocking a fire hydrant', 200.00, 200.00, 'paid', '2023-12-20 15:30:00', '2024-01-20', '2023-12-20 15:30:00'),
(15, 'noise_ordinance', 'VC-2215', 'Amplified sound without a permit', 150.00, 0.00, 'disputed', '2024-01-03 20:00:00', '2024-02-03', '2024-01-03 20:00:00'),
(16, 'speeding', 'VC-2216', 'Speeding in residential zone', 175.00, 0.00, 'unpaid', '2024-01-08 08:40:00', '2024-02-08', '2024-01-08 08:40:00'),
(17, 'illegal_dumping', 'VC-2217', 'Illegal dumping of household waste', 300.00, 300.00, 'paid', '2024-01-12 14:00:00', '2024-02-12', '2024-01-12 14:00:00'),
(18, 'parking', 'VC-2218', 'Overnight parking violation', 60.00, 0.00, 'unpaid', '2024-01-18 23:10:00', '2024-02-18', '2024-01-18 23:10:00');

-- gov_employees (15) -- no FK
INSERT INTO gov_employees (first_name, last_name, email, department, title, grade, salary, hire_date, active, created_at) VALUES
('Harold', 'Simms', 'harold.simms@citygov.example', 'Public Works', 'Civil Engineer', 'GS-11', 78500.00, '2012-03-01', TRUE, '2012-03-01 09:00:00'),
('Julia', 'Bennett', 'julia.bennett@citygov.example', 'Building & Permits', 'Permit Technician', 'GS-7', 52000.00, '2015-06-15', TRUE, '2015-06-15 09:00:00'),
('Marcus', 'Delgado', 'marcus.delgado@citygov.example', 'Police', 'Patrol Officer', 'GS-9', 61000.00, '2013-09-01', TRUE, '2013-09-01 09:00:00'),
('Cheryl', 'Nakamura', 'cheryl.nakamura@citygov.example', 'Finance', 'Budget Analyst', 'GS-10', 68000.00, '2016-01-10', TRUE, '2016-01-10 09:00:00'),
('Dennis', 'Whitfield', 'dennis.whitfield@citygov.example', 'Parks & Recreation', 'Parks Supervisor', 'GS-9', 60500.00, '2014-05-20', TRUE, '2014-05-20 09:00:00'),
('Renee', 'Castillo', 'renee.castillo@citygov.example', 'Public Works', 'Code Enforcement Officer', 'GS-8', 55500.00, '2017-08-01', TRUE, '2017-08-01 09:00:00'),
('Walter', 'Hines', 'walter.hines@citygov.example', 'Fire', 'Fire Inspector', 'GS-10', 65500.00, '2011-11-15', TRUE, '2011-11-15 09:00:00'),
('Priscilla', 'Yoon', 'priscilla.yoon@citygov.example', 'Health', 'Environmental Health Specialist', 'GS-9', 59000.00, '2018-02-01', TRUE, '2018-02-01 09:00:00'),
('Gordon', 'Ashby', 'gordon.ashby@citygov.example', 'IT', 'Systems Administrator', 'GS-11', 82000.00, '2016-07-01', TRUE, '2016-07-01 09:00:00'),
('Vivian', 'Prescott', 'vivian.prescott@citygov.example', 'Clerk''s Office', 'City Clerk Assistant', 'GS-6', 46000.00, '2019-03-15', TRUE, '2019-03-15 09:00:00'),
('Roland', 'Beck', 'roland.beck@citygov.example', 'Police', 'Traffic Enforcement Officer', 'GS-8', 56000.00, '2015-10-01', TRUE, '2015-10-01 09:00:00'),
('Yvonne', 'Marsh', 'yvonne.marsh@citygov.example', 'Public Works', 'Sanitation Supervisor', 'GS-8', 54500.00, '2013-04-01', TRUE, '2013-04-01 09:00:00'),
('Curtis', 'Odell', 'curtis.odell@citygov.example', 'Building & Permits', 'Building Inspector', 'GS-10', 66500.00, '2014-09-01', TRUE, '2014-09-01 09:00:00'),
('Stephanie', 'Roque', 'stephanie.roque@citygov.example', 'Finance', 'Revenue Collections Officer', 'GS-9', 60000.00, '2017-01-15', TRUE, '2017-01-15 09:00:00'),
('Bradley', 'Concepcion', 'bradley.concepcion@citygov.example', '311 Services', 'Citizen Services Coordinator', 'GS-7', 50500.00, '2020-06-01', TRUE, '2020-06-01 09:00:00');

-- public_contracts (15) -- no FK
INSERT INTO public_contracts (title, agency, vendor_name, contract_number, contract_type, value, start_date, end_date, status, created_at) VALUES
('Downtown Road Resurfacing Phase 2', 'Public Works', 'Lonestar Paving Co.', 'PC-2023-001', 'construction', 1250000.00, '2023-04-01', '2024-03-31', 'active', '2023-03-01 09:00:00'),
('City Hall HVAC Replacement', 'Facilities Management', 'Texas Climate Systems', 'PC-2023-002', 'construction', 480000.00, '2023-05-15', '2023-11-15', 'completed', '2023-04-20 09:00:00'),
('Municipal Fleet Vehicle Maintenance', 'Public Works', 'AutoCare Fleet Services', 'PC-2023-003', 'services', 210000.00, '2023-01-01', '2024-12-31', 'active', '2022-12-10 09:00:00'),
('Public Library IT Modernization', 'IT', 'NexGen IT Solutions', 'PC-2023-004', 'IT', 350000.00, '2023-06-01', '2024-06-01', 'active', '2023-05-10 09:00:00'),
('Parks Irrigation System Upgrade', 'Parks & Recreation', 'GreenFlow Irrigation LLC', 'PC-2023-005', 'construction', 175000.00, '2023-03-01', '2023-08-01', 'completed', '2023-02-05 09:00:00'),
('Body-Worn Camera Program', 'Police', 'Sentinel Optics Inc.', 'PC-2023-006', 'supplies', 620000.00, '2023-02-01', '2025-02-01', 'active', '2023-01-10 09:00:00'),
('Water Treatment Plant Chemicals Supply', 'Water Utilities', 'AquaPure Chemical Supply', 'PC-2023-007', 'supplies', 890000.00, '2023-01-01', '2024-01-01', 'completed', '2022-11-15 09:00:00'),
('Emergency Dispatch System Upgrade', 'Fire', 'Beacon Communications Group', 'PC-2023-008', 'IT', 540000.00, '2023-07-01', '2024-07-01', 'active', '2023-06-01 09:00:00'),
('Downtown Streetlight LED Conversion', 'Public Works', 'Brightline Electric Contractors', 'PC-2023-009', 'construction', 320000.00, '2023-09-01', '2024-04-01', 'active', '2023-08-05 09:00:00'),
('Animal Shelter Renovation', 'Health', 'Cornerstone Builders LLC', 'PC-2023-010', 'construction', 410000.00, '2023-03-15', '2023-12-15', 'completed', '2023-02-20 09:00:00'),
('City Website Redesign', 'IT', 'Civic Digital Studio', 'PC-2023-011', 'services', 95000.00, '2023-10-01', '2024-02-01', 'active', '2023-09-10 09:00:00'),
('Public Bus Fleet Overhaul', 'Transit Authority', 'Metro Coach Manufacturing', 'PC-2023-012', 'supplies', 2100000.00, '2023-01-15', '2025-01-15', 'active', '2022-12-01 09:00:00'),
('Courthouse Security Upgrade', 'Facilities Management', 'Guardian Systems Integration', 'PC-2023-013', 'services', 265000.00, '2022-11-01', '2023-05-01', 'terminated', '2022-10-01 09:00:00'),
('Landfill Expansion Engineering Study', 'Public Works', 'Terra Engineering Group', 'PC-2023-014', 'services', 145000.00, '2023-05-01', '2023-11-01', 'completed', '2023-04-05 09:00:00'),
('Recreation Center Solar Panel Installation', 'Parks & Recreation', 'Sunrise Solar Contractors', 'PC-2023-015', 'construction', 385000.00, '2024-01-01', '2024-08-01', 'pending', '2023-11-20 09:00:00');

-- service_requests (20) -- citizen_id 1-20, assigned_to gov_employees.employee_id 1-15 (nullable)
INSERT INTO service_requests (citizen_id, category, subcategory, description, priority, status, assigned_to, submitted_at, resolved_at, created_at) VALUES
(1, 'pothole', 'road_maintenance', 'Large pothole on Congress Ave near 5th St', 'high', 'resolved', 1, '2024-01-04 08:30:00', '2024-01-10 14:00:00', '2024-01-04 08:30:00'),
(2, 'streetlight', 'electrical', 'Streetlight out on Preston Rd', 'normal', 'resolved', 6, '2024-01-06 19:00:00', '2024-01-12 10:00:00', '2024-01-06 19:00:00'),
(3, 'noise_complaint', 'residential', 'Ongoing construction noise before 7am', 'normal', 'closed', 6, '2024-01-08 07:15:00', '2024-01-09 09:00:00', '2024-01-08 07:15:00'),
(4, 'trash_pickup', 'missed_collection', 'Trash not collected on scheduled day', 'low', 'resolved', 12, '2024-01-09 09:00:00', '2024-01-11 08:00:00', '2024-01-09 09:00:00'),
(5, 'water_leak', 'infrastructure', 'Water main leak flooding sidewalk', 'urgent', 'resolved', 1, '2024-01-10 06:45:00', '2024-01-10 12:00:00', '2024-01-10 06:45:00'),
(6, 'tree_removal', 'parks', 'Dead tree at risk of falling on power lines', 'high', 'in_progress', 5, '2024-01-12 10:00:00', NULL, '2024-01-12 10:00:00'),
(7, 'graffiti', 'vandalism', 'Graffiti on underpass wall', 'low', 'resolved', 6, '2024-01-14 13:30:00', '2024-01-20 11:00:00', '2024-01-14 13:30:00'),
(8, 'animal_control', 'stray_animal', 'Stray dog roaming school zone', 'normal', 'resolved', NULL, '2024-01-15 15:00:00', '2024-01-16 09:30:00', '2024-01-15 15:00:00'),
(9, 'pothole', 'road_maintenance', 'Multiple potholes on Westheimer Rd', 'high', 'open', 1, '2024-01-17 08:00:00', NULL, '2024-01-17 08:00:00'),
(10, 'streetlight', 'electrical', 'Flickering streetlight causing safety concern', 'normal', 'open', NULL, '2024-01-18 20:15:00', NULL, '2024-01-18 20:15:00'),
(11, 'trash_pickup', 'bulk_item', 'Bulk furniture pickup request', 'low', 'closed', 12, '2024-01-19 09:30:00', '2024-01-25 08:00:00', '2024-01-19 09:30:00'),
(12, 'water_leak', 'infrastructure', 'Fire hydrant leaking continuously', 'high', 'resolved', 1, '2024-01-20 11:00:00', '2024-01-22 15:00:00', '2024-01-20 11:00:00'),
(13, 'noise_complaint', 'commercial', 'Bar exceeding noise ordinance limits nightly', 'normal', 'in_progress', 6, '2024-01-22 23:00:00', NULL, '2024-01-22 23:00:00'),
(14, 'tree_removal', 'parks', 'Overgrown tree blocking sidewalk', 'low', 'open', 5, '2024-01-24 14:00:00', NULL, '2024-01-24 14:00:00'),
(15, 'graffiti', 'vandalism', 'Graffiti on public library exterior', 'low', 'in_progress', NULL, '2024-01-26 10:00:00', NULL, '2024-01-26 10:00:00'),
(16, 'animal_control', 'wildlife', 'Raccoon nesting in storm drain', 'normal', 'resolved', NULL, '2024-01-27 16:30:00', '2024-01-29 10:00:00', '2024-01-27 16:30:00'),
(17, 'pothole', 'road_maintenance', 'Pothole causing tire damage reports', 'urgent', 'resolved', 1, '2024-01-29 07:45:00', '2024-01-30 13:00:00', '2024-01-29 07:45:00'),
(18, 'water_leak', 'infrastructure', 'Suspected underground pipe leak in yard', 'normal', 'open', 1, '2024-01-31 12:00:00', NULL, '2024-01-31 12:00:00'),
(19, 'streetlight', 'electrical', 'New streetlight request for dark intersection', 'low', 'open', NULL, '2024-02-02 09:00:00', NULL, '2024-02-02 09:00:00'),
(20, 'trash_pickup', 'recycling', 'Recycling bin damaged, needs replacement', 'low', 'resolved', 12, '2024-02-03 10:30:00', '2024-02-06 09:00:00', '2024-02-03 10:30:00');

-- ============================================================================
-- 4. 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 diagnoses         ADD CONSTRAINT fk_diagnoses_providers        FOREIGN KEY (provider_id)  REFERENCES providers(provider_id);
ALTER TABLE lab_results       ADD CONSTRAINT fk_lab_results_patients       FOREIGN KEY (patient_id)   REFERENCES patients(patient_id);
ALTER TABLE lab_results       ADD CONSTRAINT fk_lab_results_providers      FOREIGN KEY (provider_id)  REFERENCES providers(provider_id);
ALTER TABLE insurance_claims  ADD CONSTRAINT fk_claims_patients            FOREIGN KEY (patient_id)   REFERENCES patients(patient_id);
ALTER TABLE insurance_claims  ADD CONSTRAINT fk_claims_providers           FOREIGN KEY (provider_id)  REFERENCES providers(provider_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);

-- 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);

-- 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 (transaction_id) REFERENCES transactions(transaction_id);

-- 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);
ALTER TABLE quality_checks      ADD CONSTRAINT fk_quality_checks_machines  FOREIGN KEY (machine_id) REFERENCES machines(machine_id);

-- Retail (product_id/customer_id cross-reference the E-commerce tables -- same synthetic company, shared catalog/customer base)
ALTER TABLE inventory        ADD CONSTRAINT fk_inventory_stores          FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE inventory        ADD CONSTRAINT fk_inventory_products        FOREIGN KEY (product_id)  REFERENCES products(product_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_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE pos_items        ADD CONSTRAINT fk_pos_items_pos_transactions FOREIGN KEY (pos_tx_id)   REFERENCES pos_transactions(pos_tx_id);
ALTER TABLE pos_items        ADD CONSTRAINT fk_pos_items_products        FOREIGN KEY (product_id)  REFERENCES products(product_id);

-- Government
ALTER TABLE permits           ADD CONSTRAINT fk_permits_citizens           FOREIGN KEY (citizen_id)  REFERENCES citizens(citizen_id);
ALTER TABLE violations        ADD CONSTRAINT fk_violations_citizens        FOREIGN KEY (citizen_id)  REFERENCES citizens(citizen_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_to) REFERENCES gov_employees(employee_id);

SELECT 'Foreign keys added' AS status;

-- ============================================================================
-- 5. FUNCTIONS -- 6 scalar helper functions, one per industry
-- ============================================================================

-- Healthcare: exact age in years as of today, from a date of birth
CREATE FUNCTION fn_patient_age(p_date_of_birth DATE)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN DATE_PART('year', AGE(CURRENT_DATE, p_date_of_birth))::INT;
END;
$$;

-- E-commerce: number of line items on a given order
CREATE FUNCTION fn_order_item_count(p_order_id BIGINT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_cnt INT;
BEGIN
    SELECT COUNT(*) INTO v_cnt FROM order_items WHERE order_id = p_order_id;
    RETURN COALESCE(v_cnt, 0);
END;
$$;

-- Finance: current balance for an account
CREATE FUNCTION fn_account_balance(p_account_id BIGINT)
RETURNS NUMERIC(18,4)
LANGUAGE plpgsql
AS $$
DECLARE
    v_bal NUMERIC(18,4);
BEGIN
    SELECT balance INTO v_bal FROM accounts WHERE account_id = p_account_id;
    RETURN v_bal;
END;
$$;

-- Retail: percent discount associated with a loyalty tier
CREATE FUNCTION fn_loyalty_tier_discount(p_tier VARCHAR)
RETURNS NUMERIC(5,2)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN CASE 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;
$$;

-- Government: whether a permit has expired as of today
CREATE FUNCTION fn_permit_is_expired(p_expires_at DATE)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN p_expires_at IS NOT NULL AND p_expires_at < CURRENT_DATE;
END;
$$;

-- Manufacturing/IoT: days since a machine's last maintenance
CREATE FUNCTION fn_machine_days_since_maintenance(p_machine_id BIGINT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_last DATE;
BEGIN
    SELECT last_service INTO v_last FROM machines WHERE machine_id = p_machine_id;
    IF v_last IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN CURRENT_DATE - v_last;
END;
$$;

SELECT 'Functions created' AS status;

-- ============================================================================
-- 6. STORED PROCEDURES -- 10 procedures across all six industries
--
-- NOTE ON RESULT SETS: PostgreSQL procedures cannot directly return a
-- result set to the client the way SQL Server can via a SELECT inside a
-- procedure. The idiomatic PostgreSQL equivalent is an INOUT refcursor
-- parameter: the procedure OPENs a cursor bound to the query, and the
-- caller FETCHes from it (inside the same transaction) after CALLing the
-- procedure. Every get/list/history style procedure below follows that
-- pattern; write/insert style procedures use a plain OUT parameter instead.
-- ============================================================================

-- 1. Healthcare: full clinical history for a patient (3 result sets via 3 cursors)
CREATE PROCEDURE sp_get_patient_history(
    IN p_patient_id BIGINT,
    INOUT p_appointments REFCURSOR DEFAULT 'cur_appointments',
    INOUT p_diagnoses REFCURSOR DEFAULT 'cur_diagnoses',
    INOUT p_prescriptions REFCURSOR DEFAULT 'cur_prescriptions'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_appointments FOR
        SELECT appointment_id, scheduled_at, status, visit_type
        FROM appointments WHERE patient_id = p_patient_id ORDER BY scheduled_at DESC;

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

    OPEN p_prescriptions FOR
        SELECT prescription_id, drug_name, dosage, prescribed_at, filled_at
        FROM prescriptions WHERE patient_id = p_patient_id ORDER BY prescribed_at DESC;
END;
$$;

-- 2. Healthcare: file a new insurance claim
CREATE PROCEDURE sp_add_insurance_claim(
    IN p_patient_id BIGINT,
    IN p_provider_id BIGINT,
    IN p_claim_date DATE,
    IN p_service_date DATE,
    IN p_icd10_code VARCHAR,
    IN p_billed_amount NUMERIC,
    OUT p_new_claim_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO insurance_claims (patient_id, provider_id, claim_date, service_date, icd10_code, billed_amount, status)
    VALUES (p_patient_id, p_provider_id, p_claim_date, p_service_date, p_icd10_code, p_billed_amount, 'pending')
    RETURNING claim_id INTO p_new_claim_id;
END;
$$;

-- 3. E-commerce: lifetime order summary for a customer
CREATE PROCEDURE sp_get_customer_order_summary(
    IN p_customer_id BIGINT,
    INOUT p_result REFCURSOR DEFAULT 'cur_order_summary'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_result FOR
        SELECT
            c.customer_id, c.first_name, c.last_name,
            COUNT(o.order_id)          AS total_orders,
            COALESCE(SUM(o.total), 0)  AS lifetime_value,
            MAX(o.placed_at)           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;
$$;

-- 4. E-commerce: apply a store promotion discount to an order total
CREATE PROCEDURE sp_apply_promotion(
    IN p_promo_code VARCHAR,
    IN p_order_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_discount_type VARCHAR(30);
    v_discount_value NUMERIC(10,4);
    v_active BOOLEAN;
BEGIN
    SELECT discount_type, discount_value, active
      INTO v_discount_type, v_discount_value, v_active
      FROM promotions WHERE promo_code = p_promo_code;

    IF v_active IS NULL OR v_active = FALSE THEN
        RAISE EXCEPTION 'Promotion code % not found or inactive.', p_promo_code;
    END IF;

    UPDATE orders
       SET total = CASE WHEN v_discount_type = 'percent'
                         THEN ROUND(total * (1 - v_discount_value / 100.0), 2)
                         ELSE GREATEST(total - v_discount_value, 0)
                    END
     WHERE order_id = p_order_id;

    UPDATE promotions SET times_used = times_used + 1 WHERE promo_code = p_promo_code;
END;
$$;

-- 5. Finance: statement of transactions on an account within a date range
CREATE PROCEDURE sp_get_account_statement(
    IN p_account_id BIGINT,
    IN p_start_date TIMESTAMP,
    IN p_end_date TIMESTAMP,
    INOUT p_result REFCURSOR DEFAULT 'cur_statement'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_result FOR
        SELECT transaction_id, transacted_at, transaction_type, amount, description, balance_after
        FROM transactions
        WHERE account_id = p_account_id AND transacted_at BETWEEN p_start_date AND p_end_date
        ORDER BY transacted_at;
END;
$$;

-- 6. Finance: manually raise a fraud alert against a transaction
CREATE PROCEDURE sp_flag_fraud_alert(
    IN p_transaction_id BIGINT,
    IN p_account_id BIGINT,
    IN p_rule_name VARCHAR,
    IN p_risk_score NUMERIC,
    OUT p_new_alert_id BIGINT
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO fraud_alerts (account_id, transaction_id, rule_name, risk_score, status, description, alerted_at)
    VALUES (p_account_id, p_transaction_id, p_rule_name, p_risk_score, 'open',
            'Manually flagged via sp_flag_fraud_alert', NOW())
    RETURNING alert_id INTO p_new_alert_id;
END;
$$;

-- 7. Manufacturing/IoT: health summary (reading count, anomaly count, average value) for a machine
CREATE PROCEDURE sp_get_machine_health_summary(
    IN p_machine_id BIGINT,
    INOUT p_result REFCURSOR DEFAULT 'cur_machine_health'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_result FOR
        SELECT
            m.machine_id, m.name, m.status,
            COUNT(sr.reading_id)                      AS reading_count,
            SUM(CASE WHEN sr.alarm 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;
$$;

-- 8. Retail: sales summary for a store on a given calendar day
CREATE PROCEDURE sp_get_store_sales_summary(
    IN p_store_id BIGINT,
    IN p_sales_date DATE,
    INOUT p_result REFCURSOR DEFAULT 'cur_store_sales'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_result FOR
        SELECT
            s.store_id, s.name,
            COUNT(pt.pos_tx_id)         AS transaction_count,
            COALESCE(SUM(pt.total), 0)  AS total_sales
        FROM stores s
        LEFT JOIN pos_transactions pt
               ON pt.store_id = s.store_id
              AND pt.transacted_at::date = p_sales_date
              AND pt.status = 'completed'
        WHERE s.store_id = p_store_id
        GROUP BY s.store_id, s.name;
END;
$$;

-- 9. Government: full civic service history for a citizen (3 result sets via 3 cursors)
CREATE PROCEDURE sp_get_citizen_service_history(
    IN p_citizen_id BIGINT,
    INOUT p_permits REFCURSOR DEFAULT 'cur_permits',
    INOUT p_violations REFCURSOR DEFAULT 'cur_violations',
    INOUT p_requests REFCURSOR DEFAULT 'cur_requests'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_permits FOR
        SELECT permit_id, permit_type, status, issued_at, expires_at
        FROM permits WHERE citizen_id = p_citizen_id;

    OPEN p_violations FOR
        SELECT violation_id, violation_type, fine_amount, status, occurred_at
        FROM violations WHERE citizen_id = p_citizen_id;

    OPEN p_requests FOR
        SELECT request_id, category, status, submitted_at
        FROM service_requests WHERE citizen_id = p_citizen_id;
END;
$$;

-- 10. Healthcare: bulk-fetch recent appointments for a set of patients at once.
--     (Replaces the SQL Server reference usp_BulkGetPatientAppointments, which
--     takes a table-valued parameter -- PostgreSQL has no TVP concept, so a
--     BIGINT[] array parameter is the idiomatic translation.)
CREATE PROCEDURE sp_bulk_get_patient_appointments(
    IN p_patient_ids BIGINT[],
    INOUT p_result REFCURSOR DEFAULT 'cur_bulk_appointments'
)
LANGUAGE plpgsql
AS $$
BEGIN
    OPEN p_result FOR
        SELECT patient_id, appointment_id, scheduled_at, status
        FROM appointments
        WHERE patient_id = ANY(p_patient_ids)
        ORDER BY patient_id, scheduled_at DESC;
END;
$$;

SELECT 'Stored procedures created' AS status;

-- ============================================================================
-- 7. VIEWS -- 6 reporting views joining across tables, one per industry
-- ============================================================================

-- Healthcare: appointment schedule with patient and provider names
CREATE 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.scheduled_at,
    a.status,
    a.visit_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 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      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 VIEW vw_account_transaction_summary AS
SELECT
    ac.account_id,
    ac.account_number,
    ac.customer_name,
    ac.status AS account_status,
    ac.balance,
    COUNT(t.transaction_id) AS transaction_count,
    COALESCE(SUM(CASE WHEN t.transaction_type IN ('debit','withdrawal') THEN t.amount ELSE 0 END), 0) AS total_debits,
    COALESCE(SUM(CASE WHEN t.transaction_type = 'deposit' THEN t.amount ELSE 0 END), 0) AS total_deposits,
    (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.customer_name, ac.status, ac.balance;

-- Manufacturing/IoT: machine health rollup from sensor readings
CREATE 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.alarm THEN 1 ELSE 0 END) AS anomaly_count,
    ROUND(AVG(sr.value), 2) AS avg_reading_value,
    MAX(sr.recorded_at) 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 VIEW vw_store_sales_summary AS
SELECT
    s.store_id,
    s.name AS store_name,
    s.city,
    s.state,
    COUNT(pt.pos_tx_id) AS transaction_count,
    COALESCE(SUM(CASE WHEN pt.status = 'completed' THEN pt.total 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 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,
    COALESCE((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;

SELECT 'Views created' AS status;

-- ============================================================================
-- 8. TRIGGERS -- 3 triggers with their supporting trigger FUNCTIONs
--    (fraud auto-flagging, audit logging on a sensitive-table update,
--    sensor anomaly detection)
-- ============================================================================

-- Finance: auto-flag any newly inserted transaction over $10,000 for fraud review
CREATE FUNCTION trg_fn_transactions_flag_large()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.amount > 10000 THEN
        INSERT INTO fraud_alerts (account_id, transaction_id, rule_name, risk_score, status, description, alerted_at)
        VALUES (NEW.account_id, NEW.transaction_id, 'large_amount', 70.00, 'open',
                'Auto-flagged by trigger: transaction amount ' || NEW.amount || ' exceeds $10,000 threshold',
                NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_transactions_flag_large
AFTER INSERT ON transactions
FOR EACH ROW
EXECUTE FUNCTION trg_fn_transactions_flag_large();

-- Finance: audit-log any change to an account balance (sensitive-table audit trail)
CREATE FUNCTION trg_fn_accounts_audit_balance()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.balance IS DISTINCT FROM OLD.balance THEN
        INSERT INTO audit_log (user_id, action, table_name, record_id, old_value, new_value, ip_address, user_agent, occurred_at)
        VALUES (NULL, 'UPDATE', 'accounts', NEW.account_id,
                'balance:' || OLD.balance, 'balance:' || NEW.balance,
                NULL, 'trg_accounts_audit_balance', NOW());
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_accounts_audit_balance
AFTER UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION trg_fn_accounts_audit_balance();

-- 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 FUNCTION trg_fn_sensor_readings_detect_anomaly()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_min NUMERIC(12,4);
    v_max NUMERIC(12,4);
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.alarm := TRUE;
    END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sensor_readings_detect_anomaly
BEFORE INSERT ON sensor_readings
FOR EACH ROW
EXECUTE FUNCTION trg_fn_sensor_readings_detect_anomaly();

SELECT 'Triggers created' AS status;

-- ============================================================================
-- BUILD COMPLETE
-- ============================================================================

SELECT 'EB_SampleDB build complete' AS status;
