/* ============================================================
   EB_SampleDB — EndriasBridge Illustrative Sample Database (MySQL 8.0)
   ------------------------------------------------------------
   WHAT THIS IS
   This is a small, hand-populated, fully self-contained sample database
   covering six industries (Healthcare, E-commerce, Finance,
   Manufacturing/IoT, Retail, Government) that mirrors the logical schema
   produced by generate_mysql.py in this same folder. It is meant as a
   quick, realistic "AdventureWorks-style" demo dataset that anyone can
   load in seconds to explore EndriasBridge's migration tooling, try
   sample queries, or poke at joins/views/procedures/triggers without
   waiting on a multi-gigabyte bulk load.

   This is NOT the full-scale dataset. generate_mysql.py can generate a
   much heavier-duty version of this same schema (tens of millions of
   rows, up to ~50 GB at scale=1.0) for anyone who wants a bigger demo:
       python generate_mysql.py --username root --password secret --scale 0.1

   HOW TO RUN
       mysql -u root -p < EB_SampleDB_mysql.sql
   Requirements: MySQL 8.0+ (or MariaDB 10.5+), a user with CREATE
   privileges. The script creates the `EB_SampleDB` database itself —
   no pre-existing database or schema is required.

   WHAT'S INSIDE (in this order)
     1. CREATE DATABASE / USE
     2. CREATE TABLE — 37 tables across 6 industries (InnoDB, utf8mb4)
     3. INSERT — hand-written sample data (~15-40 rows/table), inserted
        in parent-before-child order for referential integrity
     4. FOREIGN KEY constraints (added after data load, avoiding
        insert-order headaches)
     5. CHECK constraints (mirrors the authoritative schema's data
        validation rules)
     6. FUNCTIONS — 6 scalar helper functions
     7. PROCEDURES — 10 stored procedures
     8. VIEWS — 6 reporting views joining across tables
     9. TRIGGERS — 4 triggers (fraud auto-flagging, audit logging,
        anomaly detection)

   Table and column names match generate_mysql.py exactly, so this file
   is a drop-in, smaller-scale equivalent of that same logical schema.
============================================================ */

DROP DATABASE IF EXISTS EB_SampleDB;
CREATE DATABASE EB_SampleDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE EB_SampleDB;


/* ============================================================
   1. CREATE TABLE — HEALTHCARE
============================================================ */
CREATE TABLE patients (
    patient_id       INT AUTO_INCREMENT PRIMARY KEY,
    first_name       VARCHAR(50)  NOT NULL,
    last_name        VARCHAR(50)  NOT NULL,
    date_of_birth    DATE         NOT NULL,
    gender           VARCHAR(10)  NOT NULL,
    ssn              VARCHAR(11)  NOT NULL,
    email            VARCHAR(120),
    phone            VARCHAR(20),
    address          VARCHAR(200),
    city             VARCHAR(80),
    state            VARCHAR(50),
    zip              VARCHAR(10),
    blood_type       VARCHAR(5),
    allergies        TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE providers (
    provider_id      INT AUTO_INCREMENT PRIMARY KEY,
    first_name       VARCHAR(50)  NOT NULL,
    last_name        VARCHAR(50)  NOT NULL,
    specialty        VARCHAR(100) NOT NULL,
    npi              VARCHAR(20)  NOT NULL,
    phone            VARCHAR(20),
    email            VARCHAR(120),
    hospital_name    VARCHAR(200),
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE appointments (
    appointment_id   INT AUTO_INCREMENT PRIMARY KEY,
    patient_id       INT          NOT NULL,
    provider_id      INT          NOT NULL,
    scheduled_at     DATETIME     NOT NULL,
    duration_minutes INT          NOT NULL DEFAULT 30,
    status           VARCHAR(20)  NOT NULL DEFAULT 'scheduled',
    reason           VARCHAR(255),
    notes            TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_appointments_patient (patient_id),
    INDEX idx_appointments_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE diagnoses (
    diagnosis_id     INT AUTO_INCREMENT PRIMARY KEY,
    patient_id       INT          NOT NULL,
    provider_id      INT          NOT NULL,
    icd10_code       VARCHAR(10)  NOT NULL,
    description      VARCHAR(255) NOT NULL,
    diagnosed_at     DATETIME     NOT NULL,
    is_primary       TINYINT(1)   NOT NULL DEFAULT 1,
    notes            TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_diagnoses_patient (patient_id),
    INDEX idx_diagnoses_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE lab_results (
    lab_result_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    patient_id       INT          NOT NULL,
    provider_id      INT          NOT NULL,
    test_name        VARCHAR(100) NOT NULL,
    test_code        VARCHAR(20)  NOT NULL,
    result_value     VARCHAR(50)  NOT NULL,
    unit             VARCHAR(30),
    reference_range  VARCHAR(50),
    is_abnormal      TINYINT(1)   NOT NULL DEFAULT 0,
    collected_at     DATETIME     NOT NULL,
    resulted_at      DATETIME     NOT NULL,
    notes            TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lab_results_patient (patient_id),
    INDEX idx_lab_results_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE insurance_claims (
    claim_id         INT AUTO_INCREMENT PRIMARY KEY,
    patient_id       INT             NOT NULL,
    provider_id      INT             NOT NULL,
    claim_number     VARCHAR(30)     NOT NULL,
    service_date     DATE            NOT NULL,
    submitted_date   DATE            NOT NULL,
    amount_billed    DECIMAL(12,2)   NOT NULL,
    amount_allowed   DECIMAL(12,2),
    amount_paid      DECIMAL(12,2),
    status           VARCHAR(30)     NOT NULL DEFAULT 'pending',
    denial_reason    VARCHAR(255),
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_claims_patient (patient_id),
    INDEX idx_claims_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE prescriptions (
    prescription_id  INT AUTO_INCREMENT PRIMARY KEY,
    patient_id       INT          NOT NULL,
    provider_id      INT          NOT NULL,
    drug_name        VARCHAR(150) NOT NULL,
    ndc_code         VARCHAR(20),
    dosage           VARCHAR(50)  NOT NULL,
    frequency        VARCHAR(50)  NOT NULL,
    quantity         INT          NOT NULL,
    refills          INT          NOT NULL DEFAULT 0,
    prescribed_at    DATETIME     NOT NULL,
    expires_at       DATETIME,
    is_controlled    TINYINT(1)   NOT NULL DEFAULT 0,
    notes            TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_prescriptions_patient (patient_id),
    INDEX idx_prescriptions_provider (provider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ============================================================
   1. CREATE TABLE — E-COMMERCE
============================================================ */
CREATE TABLE categories (
    category_id      INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    parent_id        INT,
    description      TEXT,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categories_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    product_id       INT AUTO_INCREMENT PRIMARY KEY,
    category_id      INT             NOT NULL,
    sku              VARCHAR(50)     NOT NULL,
    name             VARCHAR(200)    NOT NULL,
    description      TEXT,
    price            DECIMAL(10,2)   NOT NULL,
    cost             DECIMAL(10,2)   NOT NULL,
    stock_qty        INT             NOT NULL DEFAULT 0,
    weight_kg        DECIMAL(8,3),
    is_active        TINYINT(1)      NOT NULL DEFAULT 1,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_products_category (category_id),
    UNIQUE INDEX uq_products_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE customers (
    customer_id      INT AUTO_INCREMENT PRIMARY KEY,
    first_name       VARCHAR(50)  NOT NULL,
    last_name        VARCHAR(50)  NOT NULL,
    email            VARCHAR(120) NOT NULL,
    phone            VARCHAR(20),
    address          VARCHAR(200),
    city             VARCHAR(80),
    state            VARCHAR(50),
    zip              VARCHAR(10),
    country          VARCHAR(50)  NOT NULL DEFAULT 'US',
    loyalty_points   INT          NOT NULL DEFAULT 0,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    order_id         INT AUTO_INCREMENT PRIMARY KEY,
    customer_id      INT             NOT NULL,
    status           VARCHAR(30)     NOT NULL DEFAULT 'pending',
    subtotal         DECIMAL(12,2)   NOT NULL,
    tax              DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    shipping         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    total            DECIMAL(12,2)   NOT NULL,
    payment_method   VARCHAR(30),
    shipped_at       DATETIME,
    delivered_at     DATETIME,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_orders_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    order_item_id    INT AUTO_INCREMENT PRIMARY KEY,
    order_id         INT             NOT NULL,
    product_id       INT             NOT NULL,
    quantity         INT             NOT NULL DEFAULT 1,
    unit_price       DECIMAL(10,2)   NOT NULL,
    discount         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    line_total       DECIMAL(12,2)   NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_items_order (order_id),
    INDEX idx_order_items_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE reviews (
    review_id        INT AUTO_INCREMENT PRIMARY KEY,
    product_id       INT          NOT NULL,
    customer_id      INT          NOT NULL,
    rating           TINYINT      NOT NULL,
    title            VARCHAR(200),
    body             TEXT,
    is_verified      TINYINT(1)   NOT NULL DEFAULT 0,
    helpful_votes    INT          NOT NULL DEFAULT 0,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reviews_product (product_id),
    INDEX idx_reviews_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cart_events (
    cart_event_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id      INT          NOT NULL,
    product_id       INT          NOT NULL,
    event_type       VARCHAR(30)  NOT NULL,
    quantity         INT          NOT NULL DEFAULT 1,
    session_id       VARCHAR(64),
    ip_address       VARCHAR(45),
    user_agent       VARCHAR(255),
    event_at         DATETIME     NOT NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cart_events_customer (customer_id),
    INDEX idx_cart_events_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ============================================================
   1. CREATE TABLE — FINANCE
============================================================ */
CREATE TABLE accounts (
    account_id       INT AUTO_INCREMENT PRIMARY KEY,
    account_number   VARCHAR(20)     NOT NULL,
    account_type     VARCHAR(30)     NOT NULL,
    holder_name      VARCHAR(100)    NOT NULL,
    balance          DECIMAL(18,2)   NOT NULL DEFAULT 0.00,
    currency         VARCHAR(3)      NOT NULL DEFAULT 'USD',
    status           VARCHAR(20)     NOT NULL DEFAULT 'active',
    opened_at        DATE            NOT NULL,
    closed_at        DATE,
    routing_number   VARCHAR(20),
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX uq_accounts_number (account_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE transactions (
    transaction_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    account_id       INT             NOT NULL,
    transaction_type VARCHAR(30)     NOT NULL,
    amount           DECIMAL(18,2)   NOT NULL,
    currency         VARCHAR(3)      NOT NULL DEFAULT 'USD',
    description      VARCHAR(255),
    reference_id     VARCHAR(50),
    channel          VARCHAR(30),
    merchant_name    VARCHAR(100),
    merchant_category VARCHAR(50),
    balance_after    DECIMAL(18,2),
    status           VARCHAR(20)     NOT NULL DEFAULT 'completed',
    transacted_at    DATETIME        NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_transactions_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loans (
    loan_id          INT AUTO_INCREMENT PRIMARY KEY,
    account_id       INT             NOT NULL,
    loan_type        VARCHAR(30)     NOT NULL,
    principal        DECIMAL(18,2)   NOT NULL,
    interest_rate    DECIMAL(6,4)    NOT NULL,
    term_months      INT             NOT NULL,
    monthly_payment  DECIMAL(12,2)   NOT NULL,
    balance          DECIMAL(18,2)   NOT NULL,
    status           VARCHAR(20)     NOT NULL DEFAULT 'active',
    originated_at    DATE            NOT NULL,
    maturity_date    DATE            NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_loans_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fraud_alerts (
    alert_id         INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id   BIGINT          NOT NULL,
    account_id       INT             NOT NULL,
    alert_type       VARCHAR(50)     NOT NULL,
    risk_score       DECIMAL(5,2)    NOT NULL,
    details          TEXT,
    status           VARCHAR(20)     NOT NULL DEFAULT 'open',
    resolved_at      DATETIME,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fraud_alerts_transaction (transaction_id),
    INDEX idx_fraud_alerts_account (account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE market_prices (
    price_id         INT AUTO_INCREMENT PRIMARY KEY,
    ticker           VARCHAR(10)     NOT NULL,
    price_open       DECIMAL(14,4)   NOT NULL,
    price_high       DECIMAL(14,4)   NOT NULL,
    price_low        DECIMAL(14,4)   NOT NULL,
    price_close      DECIMAL(14,4)   NOT NULL,
    volume           BIGINT          NOT NULL,
    price_date       DATE            NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_market_prices_ticker_date (ticker, price_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_log (
    audit_id         BIGINT AUTO_INCREMENT PRIMARY KEY,
    table_name       VARCHAR(64)  NOT NULL,
    record_id        BIGINT       NOT NULL,
    action           VARCHAR(10)  NOT NULL,
    changed_by       VARCHAR(100) NOT NULL,
    old_values       TEXT,
    new_values       TEXT,
    ip_address       VARCHAR(45),
    changed_at       DATETIME     NOT NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_log_table_record (table_name, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ============================================================
   1. CREATE TABLE — MANUFACTURING / IoT
============================================================ */
CREATE TABLE machines (
    machine_id       INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    model            VARCHAR(100),
    serial_number    VARCHAR(50)  NOT NULL,
    location         VARCHAR(100),
    status           VARCHAR(20)  NOT NULL DEFAULT 'running',
    installed_at     DATE         NOT NULL,
    last_maintenance DATE,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sensors (
    sensor_id        INT AUTO_INCREMENT PRIMARY KEY,
    machine_id       INT          NOT NULL,
    sensor_type      VARCHAR(50)  NOT NULL,
    unit             VARCHAR(20)  NOT NULL,
    min_threshold    DECIMAL(12,4),
    max_threshold    DECIMAL(12,4),
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    installed_at     DATETIME     NOT NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sensors_machine (machine_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sensor_readings (
    reading_id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    sensor_id        INT             NOT NULL,
    machine_id       INT             NOT NULL,
    value            DECIMAL(14,4)   NOT NULL,
    is_anomaly       TINYINT(1)      NOT NULL DEFAULT 0,
    quality          TINYINT         NOT NULL DEFAULT 100,
    read_at          DATETIME        NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sensor_readings_sensor (sensor_id),
    INDEX idx_sensor_readings_machine (machine_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE production_batches (
    batch_id         INT AUTO_INCREMENT PRIMARY KEY,
    machine_id       INT          NOT NULL,
    product_code     VARCHAR(50)  NOT NULL,
    planned_qty      INT          NOT NULL,
    actual_qty       INT,
    reject_qty       INT          NOT NULL DEFAULT 0,
    started_at       DATETIME     NOT NULL,
    completed_at     DATETIME,
    operator         VARCHAR(100),
    status           VARCHAR(20)  NOT NULL DEFAULT 'in_progress',
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_production_batches_machine (machine_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE quality_checks (
    check_id         INT AUTO_INCREMENT PRIMARY KEY,
    batch_id         INT          NOT NULL,
    inspector        VARCHAR(100) NOT NULL,
    check_type       VARCHAR(50)  NOT NULL,
    result           VARCHAR(20)  NOT NULL,
    defect_code      VARCHAR(20),
    notes            TEXT,
    checked_at       DATETIME     NOT NULL,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_quality_checks_batch (batch_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ============================================================
   1. CREATE TABLE — RETAIL
============================================================ */
CREATE TABLE stores (
    store_id         INT AUTO_INCREMENT PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    address          VARCHAR(200) NOT NULL,
    city             VARCHAR(80)  NOT NULL,
    state            VARCHAR(50)  NOT NULL,
    zip              VARCHAR(10),
    phone            VARCHAR(20),
    manager_name     VARCHAR(100),
    opened_at        DATE         NOT NULL,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inventory (
    inventory_id     INT AUTO_INCREMENT PRIMARY KEY,
    store_id         INT          NOT NULL,
    product_id       INT          NOT NULL,
    quantity_on_hand INT          NOT NULL DEFAULT 0,
    reorder_point    INT          NOT NULL DEFAULT 10,
    reorder_qty      INT          NOT NULL DEFAULT 50,
    last_restocked   DATE,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_inventory_store (store_id),
    INDEX idx_inventory_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE loyalty_members (
    member_id        INT AUTO_INCREMENT PRIMARY KEY,
    customer_id      INT          NOT NULL,
    store_id         INT          NOT NULL,
    member_number    VARCHAR(20)  NOT NULL,
    tier             VARCHAR(20)  NOT NULL DEFAULT 'bronze',
    points           INT          NOT NULL DEFAULT 0,
    total_spent      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    joined_at        DATE         NOT NULL,
    expires_at       DATE,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_loyalty_members_customer (customer_id),
    INDEX idx_loyalty_members_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE promotions (
    promotion_id     INT AUTO_INCREMENT PRIMARY KEY,
    store_id         INT,
    name             VARCHAR(150) NOT NULL,
    promo_type       VARCHAR(30)  NOT NULL,
    discount_pct     DECIMAL(5,2),
    discount_amount  DECIMAL(10,2),
    min_purchase     DECIMAL(10,2),
    starts_at        DATE         NOT NULL,
    ends_at          DATE         NOT NULL,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_promotions_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pos_transactions (
    pos_txn_id       INT AUTO_INCREMENT PRIMARY KEY,
    store_id         INT             NOT NULL,
    cashier_id       INT             NOT NULL,
    member_id        INT,
    subtotal         DECIMAL(12,2)   NOT NULL,
    tax              DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    discount         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    total            DECIMAL(12,2)   NOT NULL,
    payment_method   VARCHAR(30)     NOT NULL,
    register_number  INT             NOT NULL DEFAULT 1,
    voided           TINYINT(1)      NOT NULL DEFAULT 0,
    transacted_at    DATETIME        NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pos_transactions_store (store_id),
    INDEX idx_pos_transactions_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pos_items (
    pos_item_id      INT AUTO_INCREMENT PRIMARY KEY,
    pos_txn_id       INT             NOT NULL,
    product_id       INT             NOT NULL,
    quantity         INT             NOT NULL DEFAULT 1,
    unit_price       DECIMAL(10,2)   NOT NULL,
    discount         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    line_total       DECIMAL(12,2)   NOT NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pos_items_txn (pos_txn_id),
    INDEX idx_pos_items_product (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* ============================================================
   1. CREATE TABLE — GOVERNMENT
============================================================ */
CREATE TABLE citizens (
    citizen_id       INT AUTO_INCREMENT PRIMARY KEY,
    first_name       VARCHAR(50)  NOT NULL,
    last_name        VARCHAR(50)  NOT NULL,
    date_of_birth    DATE         NOT NULL,
    gender           VARCHAR(10),
    national_id      VARCHAR(20)  NOT NULL,
    email            VARCHAR(120),
    phone            VARCHAR(20),
    address          VARCHAR(200),
    city             VARCHAR(80),
    state            VARCHAR(50),
    zip              VARCHAR(10),
    registered_voter TINYINT(1)   NOT NULL DEFAULT 0,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE gov_employees (
    employee_id      INT AUTO_INCREMENT PRIMARY KEY,
    first_name       VARCHAR(50)  NOT NULL,
    last_name        VARCHAR(50)  NOT NULL,
    department       VARCHAR(100) NOT NULL,
    title            VARCHAR(100) NOT NULL,
    employee_number  VARCHAR(20)  NOT NULL,
    salary           DECIMAL(12,2) NOT NULL,
    hire_date        DATE         NOT NULL,
    is_active        TINYINT(1)   NOT NULL DEFAULT 1,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE permits (
    permit_id        INT AUTO_INCREMENT PRIMARY KEY,
    citizen_id       INT          NOT NULL,
    permit_number    VARCHAR(30)  NOT NULL,
    permit_type      VARCHAR(50)  NOT NULL,
    description      VARCHAR(255),
    status           VARCHAR(20)  NOT NULL DEFAULT 'pending',
    applied_at       DATE         NOT NULL,
    approved_at      DATE,
    expires_at       DATE,
    fee              DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    reviewed_by      INT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permits_citizen (citizen_id),
    INDEX idx_permits_reviewer (reviewed_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE violations (
    violation_id     INT AUTO_INCREMENT PRIMARY KEY,
    citizen_id       INT          NOT NULL,
    violation_code   VARCHAR(20)  NOT NULL,
    description      VARCHAR(255) NOT NULL,
    location         VARCHAR(200),
    fine_amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    is_paid          TINYINT(1)   NOT NULL DEFAULT 0,
    issued_at        DATETIME     NOT NULL,
    due_date         DATE,
    officer_id       INT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_violations_citizen (citizen_id),
    INDEX idx_violations_officer (officer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE public_contracts (
    contract_id      INT AUTO_INCREMENT PRIMARY KEY,
    contract_number  VARCHAR(30)  NOT NULL,
    title            VARCHAR(200) NOT NULL,
    vendor_name      VARCHAR(200) NOT NULL,
    department       VARCHAR(100) NOT NULL,
    value            DECIMAL(18,2) NOT NULL,
    status           VARCHAR(20)  NOT NULL DEFAULT 'active',
    start_date       DATE         NOT NULL,
    end_date         DATE         NOT NULL,
    description      TEXT,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE service_requests (
    request_id       INT AUTO_INCREMENT PRIMARY KEY,
    citizen_id       INT          NOT NULL,
    service_type     VARCHAR(50)  NOT NULL,
    description      TEXT,
    priority         VARCHAR(20)  NOT NULL DEFAULT 'normal',
    status           VARCHAR(20)  NOT NULL DEFAULT 'open',
    assigned_to      INT,
    location         VARCHAR(200),
    submitted_at     DATETIME     NOT NULL,
    resolved_at      DATETIME,
    created_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_service_requests_citizen (citizen_id),
    INDEX idx_service_requests_assignee (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ============================================================
-- END OF SCHEMA SECTION. INSERT statements follow below.
-- ============================================================

/* ============================================================
   2. INSERT DATA — HEALTHCARE
   (patients 1-20, providers 1-12)
============================================================ */
INSERT INTO patients (first_name, last_name, date_of_birth, gender, ssn, email, phone, address, city, state, zip, blood_type, allergies) VALUES
('James','Whitfield','1985-03-14','Male','412-55-1023','james.whitfield@example.com','555-201-1001','482 Larkspur Ave','Austin','TX','78701','O+','Penicillin'),
('Maria','Gonzalez','1990-07-22','Female','412-55-1024','maria.gonzalez@example.com','555-201-1002','119 Cedar St','Austin','TX','78702','A+',NULL),
('Wei','Chen','1978-11-02','Male','412-55-1025','wei.chen@example.com','555-201-1003','775 Birchwood Dr','Dallas','TX','75201','B-','Sulfa'),
('Aisha','Bello','2001-05-30','Female','412-55-1026','aisha.bello@example.com','555-201-1004','32 Magnolia Ln','Dallas','TX','75202','O-','None known'),
('Robert','Nguyen','1965-01-18','Male','412-55-1027','robert.nguyen@example.com','555-201-1005','908 Palmetto Ct','Houston','TX','77001','AB+',NULL),
('Emily','Carter','1993-09-09','Female','412-55-1028','emily.carter@example.com','555-201-1006','256 Willow Way','Houston','TX','77002','A-','Latex'),
('David','Okafor','1972-12-25','Male','412-55-1029','david.okafor@example.com','555-201-1007','61 Sequoia Rd','San Antonio','TX','78201','O+','Nuts'),
('Linda','Park','1988-04-11','Female','412-55-1030','linda.park@example.com','555-201-1008','740 Aspen Blvd','San Antonio','TX','78202','B+',NULL),
('Michael','Reyes','1959-06-07','Male','412-55-1031','michael.reyes@example.com','555-201-1009','15 Cypress Ave','Austin','TX','78703','O+',NULL),
('Sophia','Kim','1997-02-19','Female','412-55-1032','sophia.kim@example.com','555-201-1010','389 Maple St','Austin','TX','78704','A+','Penicillin'),
('Daniel','Brooks','1982-10-30','Male','412-55-1033','daniel.brooks@example.com','555-201-1011','904 Elm Dr','Dallas','TX','75203','AB-',NULL),
('Grace','Adeyemi','1975-08-16','Female','412-55-1034','grace.adeyemi@example.com','555-201-1012','227 Poplar Ct','Dallas','TX','75204','O-','None known'),
('Thomas','Muller','1968-03-05','Male','412-55-1035','thomas.muller@example.com','555-201-1013','58 Redwood Ln','Houston','TX','77003','B+',NULL),
('Olivia','Santos','2003-12-01','Female','412-55-1036','olivia.santos@example.com','555-201-1014','812 Chestnut Way','Houston','TX','77004','A-',NULL),
('Kevin','Osei','1991-06-27','Male','412-55-1037','kevin.osei@example.com','555-201-1015','143 Hickory Rd','San Antonio','TX','78203','O+','Sulfa'),
('Natalie','Fischer','1980-01-13','Female','412-55-1038','natalie.fischer@example.com','555-201-1016','670 Spruce Blvd','San Antonio','TX','78204','AB+',NULL),
('Samuel','Ibrahim','1955-05-22','Male','412-55-1039','samuel.ibrahim@example.com','555-201-1017','21 Dogwood Ave','Austin','TX','78705','O-',NULL),
('Chloe','Dubois','1999-09-17','Female','412-55-1040','chloe.dubois@example.com','555-201-1018','365 Juniper St','Austin','TX','78706','B-','Latex'),
('Anthony','Russo','1987-07-08','Male','412-55-1041','anthony.russo@example.com','555-201-1019','788 Sycamore Dr','Dallas','TX','75205','A+',NULL),
('Fatima','Haidari','1994-11-29','Female','412-55-1042','fatima.haidari@example.com','555-201-1020','49 Walnut Ct','Dallas','TX','75206','O+','None known');

INSERT INTO providers (first_name, last_name, specialty, npi, phone, email, hospital_name) VALUES
('Susan','Whitaker','Internal Medicine','1023456789','555-301-2001','s.whitaker@centralhealth.example','Central Health Medical Center'),
('Raj','Patel','Cardiology','1023456790','555-301-2002','r.patel@centralhealth.example','Central Health Medical Center'),
('Karen','Liu','Pediatrics','1023456791','555-301-2003','k.liu@stmarys.example','St. Mary''s Hospital'),
('Marcus','Bennett','Orthopedics','1023456792','555-301-2004','m.bennett@stmarys.example','St. Mary''s Hospital'),
('Elena','Rossi','Dermatology','1023456793','555-301-2005','e.rossi@lonestarclinic.example','Lone Star Clinic'),
('Jason','Kim','Emergency Medicine','1023456794','555-301-2006','j.kim@centralhealth.example','Central Health Medical Center'),
('Priya','Sharma','Endocrinology','1023456795','555-301-2007','p.sharma@lonestarclinic.example','Lone Star Clinic'),
('Brian','O''Connell','Family Medicine','1023456796','555-301-2008','b.oconnell@familyfirst.example','Family First Practice'),
('Hana','Tanaka','Oncology','1023456797','555-301-2009','h.tanaka@stmarys.example','St. Mary''s Hospital'),
('Gregory','Adams','Psychiatry','1023456798','555-301-2010','g.adams@centralhealth.example','Central Health Medical Center'),
('Nadia','Youssef','Neurology','1023456799','555-301-2011','n.youssef@lonestarclinic.example','Lone Star Clinic'),
('Victor','Hughes','Gastroenterology','1023456800','555-301-2012','v.hughes@familyfirst.example','Family First Practice');

INSERT INTO appointments (patient_id, provider_id, scheduled_at, duration_minutes, status, reason, notes) VALUES
(1,1,'2024-01-10 09:00:00',30,'completed','Annual physical',NULL),
(2,3,'2024-01-11 10:30:00',20,'completed','Wellness check for child',NULL),
(3,2,'2024-01-12 14:00:00',45,'completed','Chest pain follow-up','Referred for stress test'),
(4,5,'2024-01-15 11:15:00',30,'completed','Skin rash','Prescribed topical cream'),
(5,7,'2024-01-16 08:45:00',30,'no_show','Diabetes checkup',NULL),
(6,4,'2024-01-18 13:00:00',45,'completed','Knee pain','MRI ordered'),
(7,6,'2024-01-20 16:30:00',30,'completed','Laceration - hand',NULL),
(8,1,'2024-02-01 09:30:00',30,'completed','Follow-up hypertension',NULL),
(9,2,'2024-02-02 10:00:00',30,'scheduled','Cardiology consult',NULL),
(10,8,'2024-02-03 15:00:00',20,'completed','Sinus infection',NULL),
(11,9,'2024-02-05 11:00:00',60,'completed','Oncology consult','New patient intake'),
(12,10,'2024-02-06 09:15:00',45,'cancelled','Anxiety follow-up',NULL),
(13,11,'2024-02-08 14:30:00',45,'completed','Migraine evaluation',NULL),
(14,3,'2024-02-10 10:45:00',20,'completed','Well-child visit',NULL),
(15,12,'2024-02-12 13:30:00',30,'completed','Reflux symptoms',NULL),
(16,5,'2024-02-14 09:00:00',30,'completed','Acne treatment',NULL),
(17,7,'2024-02-15 11:30:00',30,'scheduled','A1C recheck',NULL),
(18,1,'2024-03-01 08:30:00',30,'completed','Annual physical',NULL),
(19,4,'2024-03-02 15:45:00',45,'completed','Shoulder pain',NULL),
(20,6,'2024-03-03 17:00:00',30,'completed','Minor burn',NULL),
(1,2,'2024-03-05 09:00:00',30,'completed','Cardiology follow-up',NULL),
(3,9,'2024-03-06 10:00:00',60,'scheduled','Oncology second opinion',NULL),
(5,7,'2024-03-08 08:45:00',30,'completed','Diabetes checkup',NULL),
(9,11,'2024-03-10 13:00:00',45,'completed','Neurology follow-up',NULL),
(12,10,'2024-03-11 09:30:00',45,'completed','Anxiety follow-up',NULL);

INSERT INTO diagnoses (patient_id, provider_id, icd10_code, description, diagnosed_at, is_primary, notes) VALUES
(1,1,'I10','Essential hypertension','2024-01-10 09:20:00',1,NULL),
(2,3,'J06.9','Acute upper respiratory infection','2024-01-11 10:45:00',1,NULL),
(3,2,'I25.10','Atherosclerotic heart disease of native coronary artery','2024-01-12 14:30:00',1,'Cardiology referral'),
(4,5,'M54.5','Low back pain','2024-01-15 11:30:00',0,NULL),
(5,7,'E11.9','Type 2 diabetes mellitus without complications','2024-01-16 08:45:00',1,NULL),
(6,4,'M54.5','Low back pain','2024-01-18 13:20:00',1,NULL),
(7,6,'J06.9','Acute upper respiratory infection','2024-01-20 16:45:00',0,NULL),
(8,1,'I10','Essential hypertension','2024-02-01 09:45:00',1,'Medication adjusted'),
(10,8,'J06.9','Acute upper respiratory infection','2024-02-03 15:15:00',1,NULL),
(11,9,'C34.10','Malignant neoplasm of upper lobe, bronchus or lung','2024-02-05 11:45:00',1,'New diagnosis, staging pending'),
(13,11,'G30.9','Alzheimer''s disease, unspecified','2024-02-08 15:00:00',0,'Early-stage evaluation'),
(14,3,'J45.909','Unspecified asthma, uncomplicated','2024-02-10 11:00:00',1,NULL),
(15,12,'K21.0','Gastro-esophageal reflux disease with esophagitis','2024-02-12 13:50:00',1,NULL),
(16,5,'F32.9','Major depressive disorder, single episode','2024-02-14 09:20:00',0,NULL),
(18,1,'I10','Essential hypertension','2024-03-01 08:50:00',1,NULL),
(19,4,'M54.5','Low back pain','2024-03-02 16:00:00',0,NULL),
(1,2,'I25.10','Atherosclerotic heart disease of native coronary artery','2024-03-05 09:20:00',0,NULL),
(5,7,'Z79.4','Long-term (current) use of insulin','2024-03-08 09:00:00',0,NULL),
(9,11,'G30.9','Alzheimer''s disease, unspecified','2024-03-10 13:20:00',0,NULL),
(20,6,'N18.3','Chronic kidney disease, stage 3','2024-03-03 17:15:00',1,'Incidental finding on labs');

INSERT INTO lab_results (patient_id, provider_id, test_name, test_code, result_value, unit, reference_range, is_abnormal, collected_at, resulted_at, notes) VALUES
(1,1,'Lipid Panel','57698-3','215','mg/dL','125-200',1,'2024-01-10 09:10:00','2024-01-11 07:00:00',NULL),
(2,3,'CBC','58410-2','7.2','cells/mcL','4.5-11.0',0,'2024-01-11 10:35:00','2024-01-12 06:30:00',NULL),
(3,2,'Troponin I','10839-9','0.02','ng/mL','0.00-0.04',0,'2024-01-12 14:10:00','2024-01-12 18:00:00',NULL),
(5,7,'HbA1c','4548-4','8.1','%','4.0-5.6',1,'2024-01-16 08:50:00','2024-01-17 07:00:00','Poor control'),
(5,7,'BMP','24320-4','142','mmol/L','136-145',0,'2024-01-16 08:50:00','2024-01-17 07:00:00',NULL),
(6,4,'CRP','1988-5','12.5','mg/L','0.0-8.0',1,'2024-01-18 13:10:00','2024-01-19 07:00:00',NULL),
(8,1,'Lipid Panel','57698-3','198','mg/dL','125-200',0,'2024-02-01 09:35:00','2024-02-02 07:00:00',NULL),
(9,2,'Troponin I','10839-9','0.01','ng/mL','0.00-0.04',0,'2024-02-02 10:10:00','2024-02-02 15:00:00',NULL),
(11,9,'CBC','58410-2','3.1','cells/mcL','4.5-11.0',1,'2024-02-05 11:10:00','2024-02-06 07:00:00','Low WBC, monitor'),
(13,11,'TSH','3016-3','2.4','mIU/L','0.4-4.0',0,'2024-02-08 14:40:00','2024-02-09 07:00:00',NULL),
(14,3,'eGFR','33914-3','98','mL/min','>90',0,'2024-02-10 10:55:00','2024-02-11 07:00:00',NULL),
(15,12,'CBC','58410-2','6.8','cells/mcL','4.5-11.0',0,'2024-02-12 13:40:00','2024-02-13 07:00:00',NULL),
(16,5,'TSH','3016-3','5.9','mIU/L','0.4-4.0',1,'2024-02-14 09:10:00','2024-02-15 07:00:00',NULL),
(18,1,'Lipid Panel','57698-3','230','mg/dL','125-200',1,'2024-03-01 08:40:00','2024-03-02 07:00:00',NULL),
(19,4,'CRP','1988-5','4.2','mg/L','0.0-8.0',0,'2024-03-02 15:55:00','2024-03-03 07:00:00',NULL),
(20,6,'eGFR','33914-3','52','mL/min','>90',1,'2024-03-03 17:10:00','2024-03-04 07:00:00','Stage 3 CKD'),
(1,2,'Lipid Panel','57698-3','205','mg/dL','125-200',1,'2024-03-05 09:10:00','2024-03-06 07:00:00',NULL),
(5,7,'HbA1c','4548-4','7.4','%','4.0-5.6',1,'2024-03-08 08:55:00','2024-03-09 07:00:00','Improving'),
(9,11,'PSA','2857-1','1.1','ng/mL','0.0-4.0',0,'2024-03-10 13:10:00','2024-03-11 07:00:00',NULL),
(12,10,'INR','6301-6','1.0','ratio','0.8-1.2',0,'2024-02-06 09:25:00','2024-02-06 14:00:00',NULL);

INSERT INTO insurance_claims (patient_id, provider_id, claim_number, service_date, submitted_date, amount_billed, amount_allowed, amount_paid, status, denial_reason) VALUES
(1,1,'CLM-10000001','2024-01-10','2024-01-12',350.00,280.00,280.00,'paid',NULL),
(2,3,'CLM-10000002','2024-01-11','2024-01-13',180.00,150.00,150.00,'paid',NULL),
(3,2,'CLM-10000003','2024-01-12','2024-01-14',1200.00,950.00,950.00,'paid',NULL),
(4,5,'CLM-10000004','2024-01-15','2024-01-17',220.00,180.00,180.00,'paid',NULL),
(5,7,'CLM-10000005','2024-01-16','2024-01-18',410.00,NULL,NULL,'denied','Prior auth required'),
(6,4,'CLM-10000006','2024-01-18','2024-01-20',890.00,700.00,700.00,'paid',NULL),
(7,6,'CLM-10000007','2024-01-20','2024-01-22',560.00,450.00,450.00,'paid',NULL),
(8,1,'CLM-10000008','2024-02-01','2024-02-03',330.00,265.00,265.00,'paid',NULL),
(10,8,'CLM-10000009','2024-02-03','2024-02-05',195.00,160.00,NULL,'under_review',NULL),
(11,9,'CLM-10000010','2024-02-05','2024-02-07',4200.00,3600.00,3600.00,'paid',NULL),
(13,11,'CLM-10000011','2024-02-08','2024-02-10',780.00,620.00,620.00,'paid',NULL),
(14,3,'CLM-10000012','2024-02-10','2024-02-12',175.00,140.00,140.00,'paid',NULL),
(15,12,'CLM-10000013','2024-02-12','2024-02-14',260.00,NULL,NULL,'pending',NULL),
(16,5,'CLM-10000014','2024-02-14','2024-02-16',300.00,240.00,240.00,'paid',NULL),
(20,6,'CLM-10000015','2024-03-03','2024-03-05',920.00,NULL,NULL,'denied','Not medically necessary');

INSERT INTO prescriptions (patient_id, provider_id, drug_name, ndc_code, dosage, frequency, quantity, refills, prescribed_at, expires_at, is_controlled, notes) VALUES
(1,1,'Lisinopril 10mg','0071-0155-23','10 mg','once daily',30,3,'2024-01-10 09:25:00','2025-01-10 00:00:00',0,NULL),
(3,2,'Atorvastatin 40mg','0071-0156-23','40 mg','once daily',30,5,'2024-01-12 14:35:00','2025-01-12 00:00:00',0,NULL),
(4,5,'Gabapentin 300mg','0071-0157-23','300 mg','three times daily',60,1,'2024-01-15 11:35:00','2024-07-15 00:00:00',0,NULL),
(5,7,'Metformin 500mg','0071-0158-23','500 mg','twice daily',60,5,'2024-01-16 08:55:00','2025-01-16 00:00:00',0,NULL),
(6,4,'Gabapentin 300mg','0071-0157-23','300 mg','twice daily',60,2,'2024-01-18 13:25:00','2024-07-18 00:00:00',0,NULL),
(8,1,'Losartan 50mg','0071-0159-23','50 mg','once daily',30,3,'2024-02-01 09:50:00','2025-02-01 00:00:00',0,NULL),
(9,2,'Metoprolol 50mg','0071-0160-23','50 mg','twice daily',60,3,'2024-02-02 10:20:00','2025-02-02 00:00:00',0,NULL),
(10,8,'Montelukast 10mg','0071-0161-23','10 mg','once daily',30,2,'2024-02-03 15:20:00','2024-08-03 00:00:00',0,NULL),
(11,9,'Ondansetron 8mg','0071-0162-23','8 mg','as needed',20,0,'2024-02-05 11:50:00',NULL,0,'For chemo-related nausea'),
(13,11,'Sertraline 50mg','0071-0163-23','50 mg','once daily',30,3,'2024-02-08 15:05:00','2025-02-08 00:00:00',0,NULL),
(14,3,'Albuterol 90mcg','0071-0164-23','90 mcg','as needed',1,5,'2024-02-10 11:05:00','2025-02-10 00:00:00',0,NULL),
(15,12,'Pantoprazole 40mg','0071-0165-23','40 mg','once daily',30,3,'2024-02-12 13:55:00','2025-02-12 00:00:00',0,NULL),
(16,5,'Escitalopram 10mg','0071-0166-23','10 mg','once daily',30,3,'2024-02-14 09:25:00','2025-02-14 00:00:00',0,NULL),
(18,1,'Lisinopril 10mg','0071-0155-23','10 mg','once daily',30,3,'2024-03-01 08:55:00','2025-03-01 00:00:00',0,NULL),
(19,4,'Gabapentin 300mg','0071-0157-23','300 mg','twice daily',60,1,'2024-03-02 16:05:00','2024-09-02 00:00:00',0,NULL),
(20,6,'Furosemide 40mg','0071-0167-23','40 mg','once daily',30,3,'2024-03-03 17:20:00','2025-03-03 00:00:00',0,NULL),
(1,2,'Atorvastatin 40mg','0071-0156-23','40 mg','once daily',30,5,'2024-03-05 09:25:00','2025-03-05 00:00:00',0,NULL),
(5,7,'Metformin 500mg','0071-0158-23','500 mg','twice daily',60,5,'2024-03-08 09:05:00','2025-03-08 00:00:00',0,NULL),
(9,11,'Levothyroxine 50mcg','0071-0168-23','50 mcg','once daily',30,5,'2024-03-10 13:25:00','2025-03-10 00:00:00',0,NULL),
(12,10,'Escitalopram 10mg','0071-0166-23','10 mg','once daily',30,3,'2024-02-06 09:35:00','2025-02-06 00:00:00',0,NULL);

/* ============================================================
   2. INSERT DATA — E-COMMERCE
   (categories 1-15, products 1-30, customers 1-25, orders 1-30)
============================================================ */
INSERT INTO categories (name, parent_id, description, is_active) VALUES
('Electronics',NULL,'Consumer electronics and gadgets',1),
('Clothing',NULL,'Apparel for all ages',1),
('Home & Garden',NULL,'Home goods and outdoor equipment',1),
('Sports',NULL,'Sporting goods and fitness equipment',1),
('Books',NULL,'Physical and digital books',1),
('Laptops & Computers',1,'Notebooks, desktops, and accessories',1),
('Phones & Accessories',1,'Smartphones, cases, and chargers',1),
('Men''s Clothing',2,'Apparel for men',1),
('Women''s Clothing',2,'Apparel for women',1),
('Furniture',3,'Indoor and outdoor furniture',1),
('Kitchen',3,'Kitchen tools and appliances',1),
('Fitness Equipment',4,'Home gym and exercise gear',1),
('Team Sports',4,'Equipment for team sports',1),
('Fiction',5,'Novels and short story collections',1),
('Non-Fiction',5,'Biographies, history, and reference',1);

INSERT INTO products (category_id, sku, name, description, price, cost, stock_qty, weight_kg, is_active) VALUES
(6,'SKU-0000001','Aurora 14" Ultrabook','Lightweight laptop with 16GB RAM and 512GB SSD',899.99,540.00,120,1.300,1),
(6,'SKU-0000002','Aurora 15" Gaming Laptop','High-performance gaming laptop with dedicated GPU',1399.99,980.00,60,2.100,1),
(6,'SKU-0000003','Wireless Mechanical Keyboard','RGB backlit mechanical keyboard',79.99,32.00,300,0.950,1),
(7,'SKU-0000004','Nova X12 Smartphone','6.5" display, 128GB storage',649.99,410.00,200,0.180,1),
(7,'SKU-0000005','Slim Phone Case','Shock-absorbent protective case',19.99,4.50,800,0.050,1),
(7,'SKU-0000006','65W USB-C Fast Charger','Compact GaN charger',29.99,9.00,500,0.120,1),
(8,'SKU-0000007','Men''s Classic Fit Chinos','Cotton blend chino pants',49.99,18.00,250,0.450,1),
(8,'SKU-0000008','Men''s Oxford Shirt','Long-sleeve button-down',44.99,16.00,220,0.300,1),
(9,'SKU-0000009','Women''s Wrap Dress','Midi-length wrap dress',59.99,21.00,180,0.350,1),
(9,'SKU-0000010','Women''s Cropped Jacket','Lightweight denim jacket',69.99,25.00,150,0.500,1),
(10,'SKU-0000011','Oakridge 3-Seat Sofa','Fabric upholstered sofa',899.00,410.00,25,45.000,1),
(10,'SKU-0000012','Adjustable Standing Desk','Electric height-adjustable desk',329.99,150.00,80,32.000,1),
(11,'SKU-0000013','8-Piece Cookware Set','Non-stick stainless cookware set',159.99,70.00,140,9.500,1),
(11,'SKU-0000014','Stainless Steel French Press','34oz coffee press',34.99,12.00,300,0.700,1),
(12,'SKU-0000015','Adjustable Dumbbell Set','5-50 lb adjustable dumbbells (pair)',249.99,140.00,60,25.000,1),
(12,'SKU-0000016','Yoga Mat Pro','Extra thick non-slip yoga mat',39.99,12.00,400,1.200,1),
(13,'SKU-0000017','Official Size Soccer Ball','Match-grade soccer ball',24.99,8.00,350,0.450,1),
(13,'SKU-0000018','Basketball - Indoor/Outdoor','Composite leather basketball',29.99,10.00,300,0.620,1),
(14,'SKU-0000019','The Glass Orchard (Novel)','Bestselling literary fiction',16.99,5.00,400,0.400,1),
(14,'SKU-0000020','Midnight in Lisbon (Novel)','Mystery thriller',15.99,4.50,350,0.380,1),
(15,'SKU-0000021','A History of Flight','Non-fiction reference book',22.99,7.50,220,0.600,1),
(15,'SKU-0000022','The Founders'' Notebook','Startup biography collection',19.99,6.50,180,0.450,1),
(1,'SKU-0000023','SmartHome Hub v2','Voice-controlled smart home hub',99.99,42.00,150,0.400,1),
(1,'SKU-0000024','4K Streaming Stick','Compact 4K HDR streaming device',49.99,18.00,400,0.080,1),
(2,'SKU-0000025','Unisex Rain Jacket','Waterproof packable rain jacket',89.99,32.00,200,0.550,1),
(3,'SKU-0000026','Ceramic Planter Set','Set of 3 ceramic planters',44.99,15.00,180,3.200,1),
(4,'SKU-0000027','Trail Running Shoes','Lightweight trail runners',119.99,48.00,220,0.700,1),
(5,'SKU-0000028','The Long Horizon (Novel)','Sci-fi epic, first in trilogy',18.99,6.00,300,0.420,1),
(6,'SKU-0000029','27" 4K Monitor','IPS 4K monitor with USB-C',379.99,220.00,90,5.500,1),
(11,'SKU-0000030','Espresso Machine Deluxe','15-bar semi-automatic espresso machine',449.99,240.00,70,8.900,1);

INSERT INTO customers (first_name, last_name, email, phone, address, city, state, zip, country, loyalty_points) VALUES
('Ethan','Walker','ethan.walker@example.com','555-401-3001','12 Riverside Dr','Denver','CO','80201','US',1200),
('Ava','Mitchell','ava.mitchell@example.com','555-401-3002','88 Lakeshore Ave','Denver','CO','80202','US',3400),
('Noah','Bryant','noah.bryant@example.com','555-401-3003','245 Sunset Blvd','Portland','OR','97201','US',560),
('Isabella','Foster','isabella.foster@example.com','555-401-3004','19 Harbor St','Portland','OR','97202','US',8900),
('Liam','Coleman','liam.coleman@example.com','555-401-3005','330 Meadow Ln','Seattle','WA','98101','US',0),
('Mia','Torres','mia.torres@example.com','555-401-3006','77 Highland Rd','Seattle','WA','98102','US',2200),
('Lucas','Hernandez','lucas.hernandez@example.com','555-401-3007','501 Forest Ave','Chicago','IL','60601','US',150),
('Amelia','Diaz','amelia.diaz@example.com','555-401-3008','64 Union St','Chicago','IL','60602','US',4700),
('Mason','Powell','mason.powell@example.com','555-401-3009','912 Grand Blvd','Boston','MA','02101','US',300),
('Harper','Simmons','harper.simmons@example.com','555-401-3010','28 Beacon St','Boston','MA','02102','US',6100),
('Logan','Ramirez','logan.ramirez@example.com','555-401-3011','143 Court St','Miami','FL','33101','US',0),
('Ella','Griffin','ella.griffin@example.com','555-401-3012','56 Ocean Dr','Miami','FL','33102','US',1900),
('Jack','Bailey','jack.bailey@example.com','555-401-3013','870 Pine St','Denver','CO','80203','US',750),
('Scarlett','Wood','scarlett.wood@example.com','555-401-3014','35 Cascade Way','Portland','OR','97203','US',2500),
('Henry','Cooper','henry.cooper@example.com','555-401-3015','622 Third Ave','Seattle','WA','98103','US',0),
('Layla','Price','layla.price@example.com','555-401-3016','99 Sheridan Rd','Chicago','IL','60603','US',3300),
('Sebastian','Long','sebastian.long@example.com','555-401-3017','410 Franklin St','Boston','MA','02103','US',1100),
('Zoe','Patterson','zoe.patterson@example.com','555-401-3018','18 Bayview Ct','Miami','FL','33103','US',600),
('Benjamin','Foster','benjamin.foster@example.com','555-401-3019','753 Prairie Ave','Denver','CO','80204','US',0),
('Victoria','Hayes','victoria.hayes@example.com','555-401-3020','266 Willamette Dr','Portland','OR','97204','US',5400),
('Owen','Ward','owen.ward@example.com','555-401-3021','48 Alki Ave','Seattle','WA','98104','US',890),
('Lily','Reed','lily.reed@example.com','555-401-3022','305 Michigan Ave','Chicago','IL','60604','US',2000),
('Wyatt','Bell','wyatt.bell@example.com','555-401-3023','67 Newbury St','Boston','MA','02104','US',0),
('Aria','Fisher','aria.fisher@example.com','555-401-3024','182 Collins Ave','Miami','FL','33104','US',1450),
('Carter','Murphy','carter.murphy@example.com','555-401-3025','940 Larimer St','Denver','CO','80205','US',3700);

INSERT INTO orders (customer_id, status, subtotal, tax, shipping, total, payment_method, shipped_at, delivered_at) VALUES
(1,'delivered',899.99,72.00,0.00,971.99,'credit_card','2024-01-05 10:00:00','2024-01-08 14:00:00'),
(2,'delivered',95.98,7.68,5.99,109.65,'credit_card','2024-01-06 09:00:00','2024-01-09 11:00:00'),
(3,'delivered',649.99,52.00,0.00,701.99,'debit_card','2024-01-10 08:30:00','2024-01-13 16:00:00'),
(4,'shipped',329.99,26.40,9.99,366.38,'credit_card','2024-01-15 12:00:00',NULL),
(5,'processing',159.99,12.80,5.99,178.78,'mobile_pay',NULL,NULL),
(6,'delivered',44.99,3.60,4.99,53.58,'gift_card','2024-01-20 09:00:00','2024-01-23 13:00:00'),
(7,'cancelled',249.99,20.00,0.00,269.99,'credit_card',NULL,NULL),
(8,'delivered',1399.99,112.00,0.00,1511.99,'credit_card','2024-01-25 10:15:00','2024-01-29 15:00:00'),
(9,'delivered',34.99,2.80,4.99,42.78,'debit_card','2024-01-26 09:30:00','2024-01-29 12:00:00'),
(10,'returned',89.99,7.20,5.99,103.18,'credit_card','2024-01-28 11:00:00','2024-01-31 10:00:00'),
(11,'delivered',79.99,6.40,5.99,92.38,'mobile_pay','2024-02-01 08:00:00','2024-02-04 14:00:00'),
(12,'delivered',119.99,9.60,5.99,135.58,'credit_card','2024-02-02 09:00:00','2024-02-05 13:00:00'),
(13,'shipped',449.99,36.00,0.00,485.99,'credit_card','2024-02-05 10:30:00',NULL),
(14,'delivered',59.99,4.80,4.99,69.78,'debit_card','2024-02-06 11:00:00','2024-02-09 16:00:00'),
(15,'pending',24.99,2.00,4.99,31.98,'mobile_pay',NULL,NULL),
(16,'delivered',379.99,30.40,0.00,410.39,'credit_card','2024-02-10 09:00:00','2024-02-13 15:00:00'),
(17,'delivered',69.99,5.60,4.99,80.58,'gift_card','2024-02-11 08:30:00','2024-02-14 12:00:00'),
(18,'delivered',99.99,8.00,4.99,112.98,'credit_card','2024-02-12 10:00:00','2024-02-15 14:00:00'),
(19,'processing',29.99,2.40,4.99,37.38,'debit_card',NULL,NULL),
(20,'delivered',649.99,52.00,0.00,701.99,'credit_card','2024-02-15 09:15:00','2024-02-18 13:00:00'),
(21,'delivered',44.99,3.60,4.99,53.58,'mobile_pay','2024-02-16 10:00:00','2024-02-19 15:00:00'),
(22,'shipped',159.99,12.80,5.99,178.78,'credit_card','2024-02-18 11:30:00',NULL),
(23,'delivered',19.99,1.60,4.99,26.58,'debit_card','2024-02-19 09:00:00','2024-02-22 12:00:00'),
(24,'delivered',899.00,72.00,0.00,971.00,'credit_card','2024-02-20 10:00:00','2024-02-25 16:00:00'),
(25,'cancelled',49.99,4.00,4.99,58.98,'gift_card',NULL,NULL),
(1,'delivered',379.99,30.40,0.00,410.39,'credit_card','2024-03-01 09:00:00','2024-03-04 14:00:00'),
(4,'delivered',34.99,2.80,4.99,42.78,'debit_card','2024-03-02 10:00:00','2024-03-05 12:00:00'),
(8,'processing',79.99,6.40,5.99,92.38,'credit_card',NULL,NULL),
(11,'delivered',449.99,36.00,0.00,485.99,'mobile_pay','2024-03-05 11:00:00','2024-03-08 15:00:00'),
(16,'pending',119.99,9.60,5.99,135.58,'credit_card',NULL,NULL);

INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount, line_total) VALUES
(1,1,1,899.99,0.00,899.99),
(2,5,2,19.99,0.00,39.98),
(2,6,2,29.99,0.00,59.98),
(3,4,1,649.99,0.00,649.99),
(4,12,1,329.99,0.00,329.99),
(5,13,1,159.99,0.00,159.99),
(6,26,1,44.99,0.00,44.99),
(7,15,1,249.99,0.00,249.99),
(8,2,1,1399.99,0.00,1399.99),
(9,14,1,34.99,0.00,34.99),
(10,25,1,89.99,0.00,89.99),
(11,3,1,79.99,0.00,79.99),
(12,27,1,119.99,0.00,119.99),
(13,30,1,449.99,0.00,449.99),
(14,9,1,59.99,0.00,59.99),
(15,17,1,24.99,0.00,24.99),
(16,29,1,379.99,0.00,379.99),
(17,10,1,69.99,0.00,69.99),
(18,23,1,99.99,0.00,99.99),
(19,18,1,29.99,0.00,29.99),
(20,4,1,649.99,0.00,649.99),
(21,26,1,44.99,0.00,44.99),
(22,13,1,159.99,0.00,159.99),
(23,19,1,16.99,0.00,16.99),
(23,20,1,15.99,3.00,12.99),
(24,11,1,899.00,0.00,899.00),
(25,6,1,29.99,0.00,29.99),
(26,29,1,379.99,0.00,379.99),
(27,14,1,34.99,0.00,34.99),
(28,3,1,79.99,0.00,79.99),
(29,30,1,449.99,0.00,449.99),
(30,27,1,119.99,0.00,119.99),
(1,6,1,29.99,0.00,29.99),
(9,21,1,22.99,0.00,22.99),
(4,16,1,39.99,0.00,39.99),
(2,24,1,49.99,0.00,49.99),
(20,7,1,49.99,0.00,49.99),
(6,8,1,44.99,0.00,44.99),
(13,2,1,1399.99,150.00,1249.99),
(24,22,1,19.99,0.00,19.99),
(11,1,1,899.99,0.00,899.99),
(19,25,1,89.99,0.00,89.99),
(8,5,1,19.99,0.00,19.99),
(17,12,1,329.99,0.00,329.99),
(30,9,1,59.99,0.00,59.99);

INSERT INTO reviews (product_id, customer_id, rating, title, body, is_verified, helpful_votes) VALUES
(1,1,5,'Great laptop','Fast, light, and the battery lasts all day.',1,12),
(4,3,4,'Good phone for the price','Camera is solid, battery could be better.',1,8),
(12,4,5,'Perfect for my home office','Sturdy and easy to adjust.',1,20),
(13,5,4,'Nice cookware set','Non-stick coating holds up well so far.',1,5),
(26,6,3,'Cute but fragile','One planter arrived cracked.',1,2),
(2,8,5,'Excellent gaming performance','Runs everything I throw at it.',1,30),
(14,9,5,'Love this french press','Makes great coffee every morning.',1,9),
(25,10,2,'Not very waterproof','Got soaked in a light rain.',0,4),
(3,11,4,'Satisfying to type on','Great tactile feedback.',1,15),
(27,12,5,'Comfortable trail shoes','Great grip on rocky terrain.',1,11),
(30,13,5,'Cafe-quality espresso at home','Worth every penny.',1,18),
(9,14,4,'Flattering fit','True to size and comfortable.',1,6),
(17,15,4,'Good match ball','Holds air pressure well.',1,3),
(29,16,5,'Crisp display','Colors are vivid, great for design work.',1,14),
(10,17,3,'Decent jacket','Runs a little small.',1,2),
(23,18,4,'Handy smart hub','Setup was quick and easy.',1,7),
(18,19,4,'Good indoor/outdoor ball','Durable so far.',1,1),
(4,20,5,'Upgraded from my old phone','Huge improvement in speed.',1,22),
(26,21,4,'Nice addition to the patio','Good quality ceramic.',1,3),
(13,22,5,'Cooks evenly','No hot spots at all.',1,10),
(19,23,5,'Couldn''t put it down','Best novel I''ve read this year.',1,25),
(11,24,4,'Comfortable sofa','Took two people to assemble.',1,9),
(6,25,3,'Charges fast but gets warm','Works as advertised otherwise.',1,4),
(29,1,4,'Great monitor for the price','USB-C hub feature is a nice touch.',1,6),
(14,4,5,'Simple and effective','Best french press I''ve owned.',1,5);

INSERT INTO cart_events (customer_id, product_id, event_type, quantity, session_id, ip_address, user_agent, event_at) VALUES
(1,1,'view',1,'sess-a1b2c3d4','203.0.113.10','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-04 18:00:00'),
(1,1,'add_to_cart',1,'sess-a1b2c3d4','203.0.113.10','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-04 18:05:00'),
(1,1,'purchase',1,'sess-a1b2c3d4','203.0.113.10','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-05 09:55:00'),
(2,5,'view',1,'sess-e5f6g7h8','203.0.113.11','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15)','2024-01-05 20:00:00'),
(2,5,'add_to_cart',2,'sess-e5f6g7h8','203.0.113.11','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15)','2024-01-05 20:05:00'),
(3,4,'view',1,'sess-i9j0k1l2','203.0.113.12','Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)','2024-01-09 12:00:00'),
(3,4,'wishlist',1,'sess-i9j0k1l2','203.0.113.12','Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)','2024-01-09 12:10:00'),
(3,4,'purchase',1,'sess-i9j0k1l2','203.0.113.12','Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)','2024-01-10 08:25:00'),
(4,12,'view',1,'sess-m3n4o5p6','203.0.113.13','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-14 19:00:00'),
(4,16,'view',1,'sess-m3n4o5p6','203.0.113.13','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-14 19:10:00'),
(5,13,'add_to_cart',1,'sess-q7r8s9t0','203.0.113.14','Mozilla/5.0 (X11; Linux x86_64)','2024-01-19 09:00:00'),
(6,26,'purchase',1,'sess-u1v2w3x4','203.0.113.15','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15)','2024-01-20 08:55:00'),
(7,15,'add_to_cart',1,'sess-y5z6a7b8','203.0.113.16','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-21 10:00:00'),
(7,15,'remove_from_cart',1,'sess-y5z6a7b8','203.0.113.16','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-21 10:15:00'),
(8,2,'view',1,'sess-c9d0e1f2','203.0.113.17','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-24 21:00:00'),
(8,2,'purchase',1,'sess-c9d0e1f2','203.0.113.17','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-25 10:10:00'),
(9,14,'checkout',1,'sess-g3h4i5j6','203.0.113.18','Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)','2024-01-26 09:25:00'),
(10,25,'view',1,'sess-k7l8m9n0','203.0.113.19','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15)','2024-01-27 17:00:00'),
(11,3,'wishlist',1,'sess-o1p2q3r4','203.0.113.20','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-01-31 20:00:00'),
(12,27,'add_to_cart',1,'sess-s5t6u7v8','203.0.113.21','Mozilla/5.0 (X11; Linux x86_64)','2024-02-01 18:00:00'),
(16,29,'view',1,'sess-w9x0y1z2','203.0.113.22','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-02-09 19:00:00'),
(20,4,'purchase',1,'sess-a3b4c5d6','203.0.113.23','Mozilla/5.0 (iPhone; CPU iPhone OS 17_0)','2024-02-15 09:10:00'),
(23,19,'add_to_cart',1,'sess-e7f8g9h0','203.0.113.24','Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15)','2024-02-18 21:30:00'),
(24,11,'checkout',1,'sess-i1j2k3l4','203.0.113.25','Mozilla/5.0 (Windows NT 10.0; Win64; x64)','2024-02-19 22:00:00'),
(25,6,'remove_from_cart',1,'sess-m5n6o7p8','203.0.113.26','Mozilla/5.0 (X11; Linux x86_64)','2024-02-20 08:00:00');

/* ============================================================
   2. INSERT DATA — FINANCE
   (accounts 1-20, transactions 1-35, loans 1-15)
============================================================ */
INSERT INTO accounts (account_number, account_type, holder_name, balance, currency, status, opened_at, closed_at, routing_number) VALUES
('ACCT-10000001','checking','James Whitfield',4520.33,'USD','active','2018-03-01',NULL,'021000021'),
('ACCT-10000002','savings','James Whitfield',18250.00,'USD','active','2018-03-01',NULL,'021000021'),
('ACCT-10000003','checking','Maria Gonzalez',1120.75,'USD','active','2019-06-15',NULL,'021000021'),
('ACCT-10000004','money_market','Wei Chen',52300.10,'USD','active','2015-01-20',NULL,'011401533'),
('ACCT-10000005','checking','Aisha Bello',890.40,'USD','active','2021-09-10',NULL,'011401533'),
('ACCT-10000006','savings','Robert Nguyen',9800.00,'USD','frozen','2012-11-05',NULL,'011401533'),
('ACCT-10000007','checking','Emily Carter',2350.60,'USD','active','2020-02-14',NULL,'026009593'),
('ACCT-10000008','brokerage','David Okafor',124500.00,'USD','active','2016-07-22',NULL,'026009593'),
('ACCT-10000009','checking','Linda Park',430.10,'USD','active','2022-04-01',NULL,'026009593'),
('ACCT-10000010','ira','Michael Reyes',87600.00,'USD','active','2010-05-19',NULL,'091000019'),
('ACCT-10000011','checking','Sophia Kim',6200.00,'USD','active','2019-12-01',NULL,'091000019'),
('ACCT-10000012','savings','Daniel Brooks',15400.25,'USD','active','2017-08-08',NULL,'091000019'),
('ACCT-10000013','checking','Grace Adeyemi',780.90,'USD','inactive','2014-03-17','2023-01-10','122105278'),
('ACCT-10000014','401k','Thomas Muller',210300.00,'USD','active','2005-10-03',NULL,'122105278'),
('ACCT-10000015','checking','Olivia Santos',3300.00,'USD','active','2023-02-20',NULL,'122105278'),
('ACCT-10000016','savings','Kevin Osei',12800.55,'USD','active','2018-01-11',NULL,'071000013'),
('ACCT-10000017','checking','Natalie Fischer',1560.00,'USD','active','2016-06-30',NULL,'071000013'),
('ACCT-10000018','money_market','Samuel Ibrahim',34200.00,'USD','active','2011-09-09',NULL,'071000013'),
('ACCT-10000019','checking','Chloe Dubois',270.15,'USD','closed','2020-01-01','2022-12-15','111000025'),
('ACCT-10000020','checking','Anthony Russo',5600.80,'USD','active','2021-05-05',NULL,'111000025');

INSERT INTO transactions (account_id, transaction_type, amount, currency, description, reference_id, channel, merchant_name, merchant_category, balance_after, status, transacted_at) VALUES
(1,'debit',85.20,'USD','Grocery purchase','REF-30000001','pos','Whole Foods Market','grocery',4520.33,'completed','2024-01-03 17:32:00'),
(1,'deposit',2000.00,'USD','Payroll deposit','REF-30000002','ach','Acme Corp Payroll','utilities',6520.33,'completed','2024-01-05 08:00:00'),
(2,'transfer',500.00,'USD','Transfer to checking','REF-30000003','online',NULL,NULL,17750.00,'completed','2024-01-06 10:00:00'),
(3,'debit',42.10,'USD','Restaurant charge','REF-30000004','pos','The Corner Bistro','restaurant',1120.75,'completed','2024-01-07 19:15:00'),
(4,'withdrawal',1500.00,'USD','ATM withdrawal','REF-30000005','atm',NULL,NULL,50800.10,'completed','2024-01-08 12:00:00'),
(5,'debit',120.00,'USD','Utility bill payment','REF-30000006','online','City Power & Light','utilities',890.40,'completed','2024-01-09 09:30:00'),
(6,'deposit',3000.00,'USD','Wire transfer in','REF-30000007','wire',NULL,NULL,9800.00,'completed','2024-01-10 14:00:00'),
(7,'debit',65.75,'USD','Gas station','REF-30000008','pos','Shell','gas_station',2350.60,'completed','2024-01-11 07:45:00'),
(8,'debit',15000.00,'USD','Brokerage transfer out','REF-30000009','wire',NULL,NULL,124500.00,'completed','2024-01-12 11:00:00'),
(9,'debit',210.00,'USD','Insurance premium','REF-30000010','ach','SafeGuard Insurance','insurance',430.10,'completed','2024-01-13 08:00:00'),
(10,'deposit',5000.00,'USD','IRA contribution','REF-30000011','ach',NULL,NULL,87600.00,'completed','2024-01-14 09:00:00'),
(11,'debit',350.00,'USD','Electronics purchase','REF-30000012','pos','TechMart','retail',6200.00,'completed','2024-01-15 16:20:00'),
(12,'transfer',1000.00,'USD','Savings transfer','REF-30000013','mobile',NULL,NULL,15400.25,'completed','2024-01-16 10:10:00'),
(13,'debit',45.00,'USD','Pharmacy','REF-30000014','pos','Walgreens','healthcare',780.90,'completed','2024-01-17 13:00:00'),
(14,'deposit',4200.00,'USD','401k employer match','REF-30000015','ach',NULL,NULL,210300.00,'completed','2024-01-18 08:00:00'),
(15,'debit',89.99,'USD','Online retail purchase','REF-30000016','online','Aurora Electronics','retail',3300.00,'completed','2024-01-19 21:00:00'),
(16,'debit',30.00,'USD','Streaming subscription','REF-30000017','online','StreamPlus','entertainment',12800.55,'completed','2024-01-20 00:05:00'),
(17,'debit',56.40,'USD','Restaurant charge','REF-30000018','pos','Maple Diner','restaurant',1560.00,'completed','2024-01-21 12:30:00'),
(18,'deposit',10000.00,'USD','Certificate of deposit rollover','REF-30000019','branch',NULL,NULL,34200.00,'completed','2024-01-22 10:00:00'),
(19,'debit',20.00,'USD','Monthly maintenance fee','REF-30000020','branch',NULL,NULL,270.15,'completed','2024-01-23 00:00:00'),
(20,'debit',150.00,'USD','Airline ticket','REF-30000021','online','SkyWay Airlines','travel',5600.80,'completed','2024-01-24 15:00:00'),
(1,'debit',12500.00,'USD','Large furniture purchase','REF-30000022','pos','Oakridge Home','retail',-7979.67,'completed','2024-02-01 15:00:00'),
(2,'deposit',1200.00,'USD','Bonus deposit','REF-30000023','ach',NULL,NULL,18950.00,'completed','2024-02-02 08:00:00'),
(4,'debit',22000.00,'USD','Wire to external account','REF-30000024','wire',NULL,NULL,30300.10,'completed','2024-02-03 09:00:00'),
(6,'debit',75.00,'USD','Card not present purchase','REF-30000025','online','GlobalGoods','retail',9725.00,'flagged','2024-02-04 03:14:00'),
(8,'deposit',20000.00,'USD','Stock sale proceeds','REF-30000026','online',NULL,NULL,144500.00,'completed','2024-02-05 10:00:00'),
(9,'debit',300.00,'USD','Rent payment','REF-30000027','ach','Parkview Apartments','utilities',130.10,'completed','2024-02-06 08:00:00'),
(11,'debit',18000.00,'USD','Unusually large purchase','REF-30000028','pos','LuxeGoods','retail',-11800.00,'flagged','2024-02-07 23:50:00'),
(14,'withdrawal',5000.00,'USD','Hardship withdrawal','REF-30000029','branch',NULL,NULL,205300.00,'completed','2024-02-08 09:00:00'),
(16,'deposit',600.00,'USD','Freelance payment','REF-30000030','ach',NULL,NULL,13400.55,'completed','2024-02-09 08:00:00'),
(20,'debit',13000.00,'USD','Large electronics order','REF-30000031','online','TechMart','retail',-7399.20,'flagged','2024-02-10 02:22:00'),
(3,'debit',18.50,'USD','Coffee shop','REF-30000032','pos','Daily Grind Coffee','restaurant',1102.25,'completed','2024-02-11 07:15:00'),
(7,'deposit',900.00,'USD','Payroll deposit','REF-30000033','ach','Acme Corp Payroll','utilities',3250.60,'completed','2024-02-12 08:00:00'),
(12,'debit',250.00,'USD','Home improvement store','REF-30000034','pos','BuildRight','retail',15150.25,'completed','2024-02-13 14:00:00'),
(17,'debit',40.00,'USD','Pet supplies','REF-30000035','pos','Pet Paradise','retail',1520.00,'completed','2024-02-14 11:00:00');

INSERT INTO loans (account_id, loan_type, principal, interest_rate, term_months, monthly_payment, balance, status, originated_at, maturity_date) VALUES
(1,'auto',28000.00,0.0549,60,535.20,19800.00,'active','2022-03-01','2027-03-01'),
(2,'mortgage',380000.00,0.0625,360,2340.15,362000.00,'active','2020-06-15','2050-06-15'),
(4,'business',150000.00,0.0710,84,2250.00,98000.00,'active','2019-01-10','2026-01-10'),
(6,'personal',12000.00,0.0899,36,381.50,4200.00,'active','2022-08-01','2025-08-01'),
(8,'mortgage',620000.00,0.0575,360,3620.75,598000.00,'active','2021-04-01','2051-04-01'),
(9,'auto',22000.00,0.0625,48,520.00,15400.00,'delinquent','2021-11-15','2025-11-15'),
(10,'home_equity',85000.00,0.0680,120,975.30,60200.00,'active','2018-09-01','2028-09-01'),
(12,'student',45000.00,0.0450,120,468.90,32100.00,'active','2015-08-01','2030-08-01'),
(13,'personal',8000.00,0.1050,24,372.60,0.00,'paid_off','2021-01-01','2023-01-01'),
(14,'mortgage',290000.00,0.0399,360,1382.20,275500.00,'active','2017-05-15','2047-05-15'),
(16,'auto',19500.00,0.0575,60,375.40,9800.00,'active','2021-07-01','2026-07-01'),
(17,'personal',5000.00,0.1150,24,233.10,2100.00,'active','2023-01-15','2025-01-15'),
(18,'business',95000.00,0.0650,60,1857.30,71000.00,'active','2022-02-01','2027-02-01'),
(20,'home_equity',40000.00,0.0710,84,600.20,33000.00,'active','2022-10-01','2029-10-01'),
(11,'auto',24500.00,0.0599,60,472.80,20100.00,'active','2023-05-01','2028-05-01');

INSERT INTO fraud_alerts (transaction_id, account_id, alert_type, risk_score, details, status, resolved_at) VALUES
(22,1,'large_amount',88.50,'Purchase far exceeds account average transaction size','resolved','2024-02-02 09:00:00'),
(24,4,'large_amount',82.00,'Large outbound wire to unfamiliar recipient','under_review',NULL),
(25,6,'card_not_present',91.20,'CNP purchase from new merchant flagged by rules engine','open',NULL),
(28,11,'large_amount',94.00,'Purchase resulted in negative balance','open',NULL),
(31,20,'large_amount',85.30,'Large online electronics order at unusual hour','under_review',NULL),
(9,8,'velocity',76.40,'Multiple large transfers within short window','false_positive','2024-01-13 10:00:00'),
(5,4,'geo_anomaly',68.00,'Withdrawal location inconsistent with recent activity','resolved','2024-01-09 08:00:00'),
(19,18,'large_amount',60.50,'Large CD rollover deposit, consistent with account history','false_positive','2024-01-23 09:00:00'),
(1,1,'velocity',40.10,'Two transactions in quick succession','resolved','2024-01-04 08:00:00'),
(30,16,'geo_anomaly',55.00,'Freelance payment from new payer','false_positive','2024-02-10 10:00:00'),
(21,20,'account_takeover',89.90,'Airline ticket purchase flagged after password reset','resolved','2024-01-25 12:00:00'),
(27,9,'large_amount',58.75,'Rent payment slightly above historical average','false_positive','2024-02-07 09:00:00');

INSERT INTO market_prices (ticker, price_open, price_high, price_low, price_close, volume, price_date) VALUES
('AAPL',185.20,187.90,184.10,186.50,58000000,'2024-01-02'),
('AAPL',186.60,189.30,186.00,188.75,54000000,'2024-01-03'),
('AAPL',188.80,190.10,187.50,189.90,49500000,'2024-01-04'),
('AAPL',189.95,191.40,188.60,190.10,51000000,'2024-01-05'),
('MSFT',372.50,376.20,371.00,375.40,22000000,'2024-01-02'),
('MSFT',375.50,378.90,374.20,377.10,21000000,'2024-01-03'),
('MSFT',377.20,380.00,375.80,379.60,23500000,'2024-01-04'),
('MSFT',379.70,382.10,378.30,381.25,20500000,'2024-01-05'),
('AMZN',151.30,153.80,150.50,152.90,40000000,'2024-01-02'),
('AMZN',153.00,155.40,152.10,154.60,38500000,'2024-01-03'),
('AMZN',154.70,156.90,153.90,155.80,37000000,'2024-01-04'),
('AMZN',155.90,157.60,154.80,156.40,39500000,'2024-01-05'),
('TSLA',248.40,252.10,245.60,250.30,105000000,'2024-01-02'),
('TSLA',250.50,254.80,247.90,252.10,98000000,'2024-01-03'),
('TSLA',252.20,255.90,249.30,251.50,102000000,'2024-01-04'),
('TSLA',251.60,253.40,246.80,248.90,110000000,'2024-01-05'),
('JPM',171.20,173.50,170.40,172.80,12000000,'2024-01-02'),
('JPM',172.90,174.60,171.80,173.90,11500000,'2024-01-03'),
('JPM',174.00,175.80,173.10,174.60,10800000,'2024-01-04'),
('JPM',174.70,176.20,173.90,175.40,11200000,'2024-01-05'),
('NVDA',495.20,502.80,491.60,499.40,45000000,'2024-01-02'),
('NVDA',500.10,508.30,497.50,505.90,47500000,'2024-01-03'),
('NVDA',506.30,515.60,503.20,512.80,50000000,'2024-01-04'),
('NVDA',513.20,520.40,509.80,517.60,48500000,'2024-01-05');

INSERT INTO audit_log (table_name, record_id, action, changed_by, old_values, new_values, ip_address, changed_at) VALUES
('accounts',1,'UPDATE','svc_batch_job','balance=4520.33','balance=6520.33','10.0.0.5','2024-01-05 08:00:05'),
('accounts',4,'UPDATE','svc_batch_job','balance=52300.10','balance=50800.10','10.0.0.5','2024-01-08 12:00:05'),
('customers',9,'UPDATE','admin_jsmith','loyalty_points=100','loyalty_points=300','10.0.0.12','2024-01-26 09:35:00'),
('patients',5,'UPDATE','dr_sharma','allergies=NULL','allergies=None known','10.0.0.20','2024-01-16 09:00:00'),
('orders',7,'UPDATE','system','status=pending','status=cancelled','10.0.0.5','2024-01-22 08:00:00'),
('accounts',6,'UPDATE','svc_batch_job','status=active','status=frozen','10.0.0.5','2024-02-04 03:15:00'),
('permits',3,'UPDATE','clerk_rwilliams','status=pending','status=approved','10.0.0.30','2024-01-20 10:00:00'),
('pos_transactions',5,'UPDATE','mgr_tlee','voided=0','voided=1','10.0.0.40','2024-01-15 18:00:00'),
('loans',6,'UPDATE','svc_batch_job','status=active','status=delinquent','10.0.0.5','2024-02-16 06:00:00'),
('citizens',10,'UPDATE','clerk_bmartin','registered_voter=0','registered_voter=1','10.0.0.30','2024-01-30 14:00:00'),
('accounts',11,'UPDATE','svc_batch_job','balance=6200.00','balance=-11800.00','10.0.0.5','2024-02-07 23:50:05'),
('customers',2,'UPDATE','admin_jsmith','loyalty_points=3200','loyalty_points=3400','10.0.0.12','2024-02-02 08:05:00'),
('accounts',20,'UPDATE','svc_batch_job','balance=5600.80','balance=-7399.20','10.0.0.5','2024-02-10 02:22:05'),
('orders',10,'UPDATE','clerk_avazquez','status=delivered','status=returned','10.0.0.45','2024-02-01 09:00:00'),
('providers',9,'UPDATE','admin_hr','hospital_name=St. Mary Hospital','hospital_name=St. Mary''s Hospital','10.0.0.50','2024-01-02 11:00:00');

/* ============================================================
   2. INSERT DATA — MANUFACTURING / IoT
   (machines 1-15, sensors 1-20, production_batches 1-15)
============================================================ */
INSERT INTO machines (name, model, serial_number, location, status, installed_at, last_maintenance) VALUES
('CNC Mill Alpha','HAAS VF-2','SN-100000001','Line 1 Bay 1','running','2016-04-10','2024-01-15'),
('CNC Mill Beta','HAAS VF-2','SN-100000002','Line 1 Bay 2','running','2016-04-10','2024-01-15'),
('Injection Molder 1','Engel e-motion 310','SN-100000003','Line 2 Bay 1','running','2018-08-22','2024-02-01'),
('Injection Molder 2','Engel e-motion 310','SN-100000004','Line 2 Bay 2','maintenance','2018-08-22','2024-03-01'),
('Robotic Welder A1','FANUC ARC Mate 100iD','SN-100000005','Line 3 Bay 1','running','2019-02-14','2024-01-20'),
('Robotic Welder A2','FANUC ARC Mate 100iD','SN-100000006','Line 3 Bay 2','idle','2019-02-14','2024-01-20'),
('Conveyor System 1','Dorner 2200 Series','SN-100000007','Line 1 Bay 3','running','2015-11-01','2023-12-10'),
('Packaging Line 1','Bosch Pack 403','SN-100000008','Line 4 Bay 1','running','2020-06-05','2024-02-15'),
('Packaging Line 2','Bosch Pack 403','SN-100000009','Line 4 Bay 2','fault','2020-06-05','2024-03-05'),
('Laser Cutter X1','Trumpf TruLaser 3030','SN-100000010','Line 5 Bay 1','running','2021-01-18','2024-01-25'),
('Press Brake 1','Amada HG 1003','SN-100000011','Line 5 Bay 2','running','2017-09-09','2023-11-30'),
('Stamping Press A','Schuler MSP','SN-100000012','Line 6 Bay 1','running','2014-03-03','2024-02-20'),
('Extruder Line 1','Davis-Standard DS-E','SN-100000013','Line 6 Bay 2','idle','2019-07-12','2024-01-05'),
('Paint Booth 1','Wagner ProSpray','SN-100000014','Line 7 Bay 1','running','2016-12-01','2023-12-22'),
('Assembly Robot B1','KUKA KR 10 R1100','SN-100000015','Line 3 Bay 3','running','2020-10-10','2024-02-28');

INSERT INTO sensors (machine_id, sensor_type, unit, min_threshold, max_threshold, is_active, installed_at) VALUES
(1,'temperature','°C',10.0000,80.0000,1,'2016-04-10 00:00:00'),
(1,'vibration','mm/s',0.0000,12.0000,1,'2016-04-10 00:00:00'),
(2,'temperature','°C',10.0000,80.0000,1,'2016-04-10 00:00:00'),
(3,'pressure','bar',5.0000,150.0000,1,'2018-08-22 00:00:00'),
(3,'temperature','°C',150.0000,230.0000,1,'2018-08-22 00:00:00'),
(4,'pressure','bar',5.0000,150.0000,1,'2018-08-22 00:00:00'),
(5,'current','A',2.0000,45.0000,1,'2019-02-14 00:00:00'),
(6,'current','A',2.0000,45.0000,1,'2019-02-14 00:00:00'),
(7,'rpm','RPM',0.0000,120.0000,1,'2015-11-01 00:00:00'),
(8,'humidity','%',20.0000,60.0000,1,'2020-06-05 00:00:00'),
(9,'humidity','%',20.0000,60.0000,1,'2020-06-05 00:00:00'),
(10,'temperature','°C',15.0000,120.0000,1,'2021-01-18 00:00:00'),
(11,'pressure','bar',10.0000,200.0000,1,'2017-09-09 00:00:00'),
(12,'vibration','mm/s',0.0000,15.0000,1,'2014-03-03 00:00:00'),
(13,'flow_rate','L/min',5.0000,80.0000,1,'2019-07-12 00:00:00'),
(14,'voltage','V',200.0000,240.0000,1,'2016-12-01 00:00:00'),
(15,'current','A',1.0000,30.0000,1,'2020-10-10 00:00:00'),
(2,'vibration','mm/s',0.0000,12.0000,1,'2016-04-10 00:00:00'),
(7,'temperature','°C',10.0000,60.0000,1,'2015-11-01 00:00:00'),
(11,'temperature','°C',10.0000,70.0000,1,'2017-09-09 00:00:00');

INSERT INTO sensor_readings (sensor_id, machine_id, value, is_anomaly, quality, read_at) VALUES
(1,1,45.2000,0,100,'2024-03-01 08:00:00'),
(1,1,46.8000,0,100,'2024-03-01 09:00:00'),
(1,1,84.5000,1,95,'2024-03-01 10:00:00'),
(2,1,6.4000,0,100,'2024-03-01 08:00:00'),
(2,1,7.1000,0,99,'2024-03-01 09:00:00'),
(3,2,42.0000,0,100,'2024-03-01 08:00:00'),
(4,3,88.5000,0,100,'2024-03-01 08:00:00'),
(5,3,205.3000,0,98,'2024-03-01 08:00:00'),
(5,3,236.0000,1,92,'2024-03-01 12:00:00'),
(6,4,92.1000,0,100,'2024-03-01 08:00:00'),
(7,5,18.2000,0,100,'2024-03-01 08:00:00'),
(8,6,0.5000,0,100,'2024-03-01 08:00:00'),
(9,7,65.0000,0,100,'2024-03-01 08:00:00'),
(9,7,125.0000,1,90,'2024-03-01 14:00:00'),
(10,8,42.0000,0,100,'2024-03-01 08:00:00'),
(11,9,71.0000,1,85,'2024-03-01 08:00:00'),
(12,10,68.0000,0,100,'2024-03-01 08:00:00'),
(13,11,95.0000,0,100,'2024-03-01 08:00:00'),
(14,12,8.1000,0,100,'2024-03-01 08:00:00'),
(15,13,32.5000,0,100,'2024-03-01 08:00:00'),
(16,14,220.0000,0,100,'2024-03-01 08:00:00'),
(17,15,12.4000,0,100,'2024-03-01 08:00:00'),
(18,2,5.8000,0,100,'2024-03-01 09:30:00'),
(19,7,28.0000,0,100,'2024-03-01 09:30:00'),
(20,11,44.0000,0,100,'2024-03-01 09:30:00'),
(1,1,47.9000,0,100,'2024-03-02 08:00:00'),
(3,2,43.5000,0,100,'2024-03-02 08:00:00'),
(9,7,68.0000,0,100,'2024-03-02 08:00:00'),
(11,9,-5.0000,1,80,'2024-03-02 08:00:00'),
(16,14,238.0000,0,100,'2024-03-02 08:00:00');

INSERT INTO production_batches (machine_id, product_code, planned_qty, actual_qty, reject_qty, started_at, completed_at, operator, status) VALUES
(1,'SKU-00001',5000,4950,25,'2024-01-10 06:00:00','2024-01-10 14:00:00','Carlos Mendez','completed'),
(2,'SKU-00002',3000,2980,10,'2024-01-11 06:00:00','2024-01-11 13:30:00','Angela Brooks','completed'),
(3,'SKU-00003',8000,7850,80,'2024-01-12 06:00:00','2024-01-12 18:00:00','Marcus Webb','completed'),
(4,'SKU-00004',6000,NULL,0,'2024-03-01 06:00:00',NULL,'Priya Nair','on_hold'),
(5,'SKU-00005',2000,1990,5,'2024-01-15 07:00:00','2024-01-15 15:00:00','Daniel Osei','completed'),
(6,'SKU-00006',2000,1850,60,'2024-01-16 07:00:00','2024-01-16 16:00:00','Rachel Kim','completed'),
(7,'SKU-00007',10000,9900,20,'2024-01-17 06:00:00','2024-01-17 20:00:00','Tom Reilly','completed'),
(8,'SKU-00008',15000,14800,40,'2024-01-18 06:00:00','2024-01-18 22:00:00','Grace Lin','completed'),
(9,'SKU-00009',15000,NULL,0,'2024-03-05 06:00:00',NULL,'Grace Lin','rejected'),
(10,'SKU-00010',4000,3980,15,'2024-01-20 06:00:00','2024-01-20 14:00:00','Victor Alvarez','completed'),
(11,'SKU-00011',3500,3450,30,'2024-01-21 06:00:00','2024-01-21 15:00:00','Nina Petrov','completed'),
(12,'SKU-00012',20000,19750,90,'2024-01-22 06:00:00','2024-01-22 23:00:00','Sam Okoro','completed'),
(13,'SKU-00013',5000,4900,20,'2024-01-23 06:00:00','2024-01-23 16:00:00','Ella Fischer','completed'),
(14,'SKU-00014',1200,1180,5,'2024-01-24 06:00:00','2024-01-24 12:00:00','Marcus Webb','completed'),
(15,'SKU-00015',6000,5980,10,'2024-01-25 06:00:00','2024-01-25 17:00:00','Angela Brooks','completed');

INSERT INTO quality_checks (batch_id, inspector, check_type, result, defect_code, notes, checked_at) VALUES
(1,'Fatima Noor','dimensional','pass',NULL,NULL,'2024-01-10 14:15:00'),
(1,'Fatima Noor','visual','pass',NULL,NULL,'2024-01-10 14:20:00'),
(2,'George Silva','functional','pass',NULL,NULL,'2024-01-11 13:45:00'),
(3,'Fatima Noor','chemical','conditional','DEF002','Minor variance in resin mix','2024-01-12 18:15:00'),
(3,'George Silva','visual','pass',NULL,NULL,'2024-01-12 18:20:00'),
(5,'Hannah Ross','functional','pass',NULL,NULL,'2024-01-15 15:15:00'),
(6,'George Silva','visual','fail','DEF001','Surface porosity on weld','2024-01-16 16:15:00'),
(7,'Fatima Noor','dimensional','pass',NULL,NULL,'2024-01-17 20:15:00'),
(8,'Hannah Ross','visual','pass',NULL,NULL,'2024-01-18 22:15:00'),
(10,'George Silva','dimensional','pass',NULL,NULL,'2024-01-20 14:15:00'),
(11,'Fatima Noor','stress_test','pass',NULL,NULL,'2024-01-21 15:15:00'),
(12,'Hannah Ross','dimensional','conditional','DEF003','Slight thickness deviation','2024-01-22 23:15:00'),
(13,'George Silva','chemical','pass',NULL,NULL,'2024-01-23 16:15:00'),
(14,'Fatima Noor','visual','pass',NULL,NULL,'2024-01-24 12:15:00'),
(15,'Hannah Ross','functional','pass',NULL,NULL,'2024-01-25 17:15:00'),
(3,'Hannah Ross','stress_test','fail','DEF004','Batch flagged for rework after chemical variance','2024-01-13 09:00:00'),
(6,'Fatima Noor','functional','fail','DEF001','Weld strength below spec','2024-01-16 17:00:00'),
(12,'George Silva','stress_test','pass',NULL,NULL,'2024-01-23 08:00:00'),
(9,'Hannah Ross','visual','fail','DEF005','Packaging line fault mid-run','2024-03-05 10:00:00'),
(4,'Fatima Noor','visual','conditional','DEF002','Awaiting maintenance completion','2024-03-01 07:00:00');

/* ============================================================
   2. INSERT DATA — RETAIL
   (stores 1-10, inventory, loyalty_members 1-20, pos_transactions 1-25)
============================================================ */
INSERT INTO stores (name, address, city, state, zip, phone, manager_name, opened_at, is_active) VALUES
('Store #0001 - Denver','100 16th St Mall','Denver','CO','80202','555-501-4001','Tara Nolan','2010-05-01',1),
('Store #0002 - Portland','200 SW Morrison St','Portland','OR','97204','555-501-4002','Marcus Webb','2012-08-15',1),
('Store #0003 - Seattle','300 Pike St','Seattle','WA','98101','555-501-4003','Ella Fischer','2011-03-20',1),
('Store #0004 - Chicago','400 N Michigan Ave','Chicago','IL','60611','555-501-4004','Sam Okoro','2009-11-11',1),
('Store #0005 - Boston','500 Boylston St','Boston','MA','02116','555-501-4005','Nina Petrov','2013-06-01',1),
('Store #0006 - Miami','600 Lincoln Rd','Miami','FL','33139','555-501-4006','Victor Alvarez','2014-02-14',1),
('Store #0007 - Austin','700 S Congress Ave','Austin','TX','78704','555-501-4007','Grace Lin','2015-09-09',1),
('Store #0008 - Dallas','800 Elm St','Dallas','TX','75202','555-501-4008','Tom Reilly','2016-01-20',1),
('Store #0009 - Houston','900 Main St','Houston','TX','77002','555-501-4009','Rachel Kim','2017-04-04',1),
('Store #0010 - San Antonio','1000 Commerce St','San Antonio','TX','78205','555-501-4010','Daniel Osei','2018-07-18',0);

INSERT INTO inventory (store_id, product_id, quantity_on_hand, reorder_point, reorder_qty, last_restocked) VALUES
(1,1,15,5,20,'2024-02-01'),(1,3,40,10,50,'2024-02-01'),(1,17,60,15,60,'2024-02-05'),
(2,4,25,10,30,'2024-02-02'),(2,5,120,30,100,'2024-02-02'),(2,25,18,5,20,'2024-02-06'),
(3,12,10,5,15,'2024-02-03'),(3,15,22,8,25,'2024-02-03'),(3,27,30,10,30,'2024-02-07'),
(4,19,50,15,50,'2024-02-04'),(4,20,45,15,50,'2024-02-04'),(4,14,35,10,30,'2024-02-08'),
(5,11,4,3,10,'2024-02-05'),(5,13,28,8,25,'2024-02-05'),(5,30,9,3,10,'2024-02-09'),
(6,26,20,8,25,'2024-02-06'),(6,9,33,10,30,'2024-02-06'),(6,10,17,5,20,'2024-02-10'),
(7,16,55,15,50,'2024-02-07'),(7,18,40,15,50,'2024-02-07'),(7,23,26,8,25,'2024-02-11'),
(8,2,7,3,10,'2024-02-08'),(8,29,12,5,15,'2024-02-08'),(8,6,90,20,80,'2024-02-12'),
(9,7,60,15,50,'2024-02-09'),(9,8,48,15,50,'2024-02-09'),(9,21,38,10,30,'2024-02-13'),
(10,22,20,8,25,'2024-02-10'),(10,24,44,15,50,'2024-02-10'),(10,28,31,10,30,'2024-02-14');

INSERT INTO loyalty_members (customer_id, store_id, member_number, tier, points, total_spent, joined_at, expires_at) VALUES
(1,1,'MBR-20000001','gold',1200,3400.00,'2021-05-10','2026-05-10'),
(2,2,'MBR-20000002','platinum',3400,8900.00,'2019-08-20','2026-08-20'),
(3,3,'MBR-20000003','bronze',560,900.00,'2023-01-15','2026-01-15'),
(4,4,'MBR-20000004','platinum',8900,15200.00,'2018-03-01','2026-03-01'),
(5,3,'MBR-20000005','bronze',0,150.00,'2024-01-05','2027-01-05'),
(6,5,'MBR-20000006','silver',2200,4100.00,'2020-11-11','2026-11-11'),
(7,4,'MBR-20000007','bronze',150,300.00,'2023-09-09','2026-09-09'),
(8,6,'MBR-20000008','gold',4700,7200.00,'2019-02-02','2026-02-02'),
(9,5,'MBR-20000009','bronze',300,500.00,'2022-06-06','2025-06-06'),
(10,7,'MBR-20000010','platinum',6100,11800.00,'2017-07-07','2026-07-07'),
(11,6,'MBR-20000011','bronze',0,100.00,'2024-02-01','2027-02-01'),
(12,7,'MBR-20000012','silver',1900,3300.00,'2021-10-10','2026-10-10'),
(13,1,'MBR-20000013','bronze',750,1200.00,'2022-12-01','2025-12-01'),
(14,2,'MBR-20000014','silver',2500,4600.00,'2020-04-15','2026-04-15'),
(15,3,'MBR-20000015','bronze',0,90.00,'2024-01-20','2027-01-20'),
(16,4,'MBR-20000016','gold',3300,6700.00,'2019-11-25','2026-11-25'),
(17,5,'MBR-20000017','bronze',1100,1900.00,'2022-03-03','2025-03-03'),
(18,6,'MBR-20000018','bronze',600,1000.00,'2023-05-05','2026-05-05'),
(19,7,'MBR-20000019','bronze',0,50.00,'2024-02-10','2027-02-10'),
(20,2,'MBR-20000020','platinum',5400,9900.00,'2018-06-06','2026-06-06');

INSERT INTO promotions (store_id, name, promo_type, discount_pct, discount_amount, min_purchase, starts_at, ends_at, is_active) VALUES
(1,'Winter Clearance','percent_off',20.00,NULL,50.00,'2024-01-01','2024-01-31',0),
(2,'Spring Refresh','percent_off',15.00,NULL,40.00,'2024-03-01','2024-03-31',1),
(NULL,'Site-wide Free Shipping','free_shipping',NULL,NULL,75.00,'2024-01-01','2024-12-31',1),
(3,'Buy One Get One - Fitness','bogo',NULL,NULL,0.00,'2024-02-01','2024-02-29',1),
(4,'Loyalty Bonus Points','loyalty_bonus',NULL,NULL,0.00,'2024-01-01','2024-06-30',1),
(5,'Flat $10 Off','flat_discount',NULL,10.00,60.00,'2024-02-15','2024-03-15',1),
(6,'Electronics Weekend','percent_off',10.00,NULL,100.00,'2024-02-10','2024-02-12',0),
(7,'New Store Opening','flat_discount',NULL,25.00,100.00,'2015-09-09','2015-10-09',0),
(NULL,'Holiday Mega Sale','percent_off',30.00,NULL,50.00,'2023-11-20','2023-12-31',0),
(8,'Back to School','percent_off',12.00,NULL,30.00,'2024-08-01','2024-09-15',1),
(9,'Clearance Kitchenware','flat_discount',NULL,15.00,50.00,'2024-02-20','2024-03-20',1),
(10,'Grand Reopening','percent_off',25.00,NULL,25.00,'2024-04-01','2024-04-30',1);

INSERT INTO pos_transactions (store_id, cashier_id, member_id, subtotal, tax, discount, total, payment_method, register_number, voided, transacted_at) VALUES
(1,101,1,45.98,3.68,0.00,49.66,'credit_card',1,0,'2024-02-01 10:15:00'),
(1,102,13,79.99,6.40,0.00,86.39,'cash',2,0,'2024-02-01 14:30:00'),
(2,103,2,159.99,12.80,15.99,156.80,'debit_card',1,0,'2024-02-02 11:00:00'),
(2,103,14,19.99,1.60,0.00,21.59,'mobile_pay',1,0,'2024-02-02 16:45:00'),
(3,104,3,24.99,2.00,0.00,26.99,'cash',1,0,'2024-02-03 09:30:00'),
(3,104,5,249.99,20.00,0.00,269.99,'credit_card',2,0,'2024-02-03 15:00:00'),
(4,105,4,29.99,2.40,0.00,32.39,'credit_card',1,0,'2024-02-04 10:00:00'),
(4,105,7,44.99,3.60,0.00,48.59,'gift_card',1,0,'2024-02-04 13:20:00'),
(5,106,6,899.00,71.92,0.00,970.92,'credit_card',1,0,'2024-02-05 12:00:00'),
(5,106,9,34.99,2.80,0.00,37.79,'debit_card',2,0,'2024-02-05 17:15:00'),
(6,107,8,44.99,3.60,4.50,44.09,'mobile_pay',1,0,'2024-02-06 11:30:00'),
(6,107,11,59.99,4.80,0.00,64.79,'credit_card',1,0,'2024-02-06 16:00:00'),
(7,108,10,39.99,3.20,0.00,43.19,'cash',1,0,'2024-02-07 10:45:00'),
(7,108,12,29.99,2.40,0.00,32.39,'credit_card',2,0,'2024-02-07 14:00:00'),
(8,109,NULL,79.99,6.40,0.00,86.39,'debit_card',1,0,'2024-02-08 09:15:00'),
(8,109,20,379.99,30.40,0.00,410.39,'credit_card',1,1,'2024-02-08 18:30:00'),
(9,110,16,49.99,4.00,0.00,53.99,'mobile_pay',1,0,'2024-02-09 11:00:00'),
(9,110,18,89.99,7.20,0.00,97.19,'credit_card',2,0,'2024-02-09 15:30:00'),
(10,111,NULL,22.99,1.84,0.00,24.83,'cash',1,0,'2024-02-10 10:00:00'),
(10,111,15,16.99,1.36,0.00,18.35,'credit_card',1,0,'2024-02-10 13:45:00'),
(1,102,17,119.99,9.60,0.00,129.59,'credit_card',2,0,'2024-02-11 12:00:00'),
(2,103,19,29.99,2.40,0.00,32.39,'debit_card',1,0,'2024-02-12 09:00:00'),
(3,104,NULL,99.99,8.00,0.00,107.99,'mobile_pay',2,0,'2024-02-13 16:00:00'),
(4,105,2,15.99,1.28,0.00,17.27,'cash',1,0,'2024-02-14 10:30:00'),
(6,107,8,69.99,5.60,0.00,75.59,'credit_card',1,0,'2024-02-15 11:00:00');

INSERT INTO pos_items (pos_txn_id, product_id, quantity, unit_price, discount, line_total) VALUES
(1,3,1,45.98,0.00,45.98),
(2,3,1,79.99,0.00,79.99),
(3,13,1,159.99,15.99,144.00),
(4,20,1,19.99,0.00,19.99),
(5,17,1,24.99,0.00,24.99),
(6,15,1,249.99,0.00,249.99),
(7,18,1,29.99,0.00,29.99),
(8,26,1,44.99,0.00,44.99),
(9,11,1,899.00,0.00,899.00),
(10,14,1,34.99,0.00,34.99),
(11,26,1,44.99,4.50,40.49),
(12,9,1,59.99,0.00,59.99),
(13,16,1,39.99,0.00,39.99),
(14,18,1,29.99,0.00,29.99),
(15,3,1,79.99,0.00,79.99),
(16,29,1,379.99,0.00,379.99),
(17,24,1,49.99,0.00,49.99),
(18,25,1,89.99,0.00,89.99),
(19,21,1,22.99,0.00,22.99),
(20,19,1,16.99,0.00,16.99),
(21,27,1,119.99,0.00,119.99),
(22,6,1,29.99,0.00,29.99),
(23,23,1,99.99,0.00,99.99),
(24,20,1,15.99,0.00,15.99),
(25,10,1,69.99,0.00,69.99),
(2,17,1,24.99,0.00,24.99),
(6,16,1,39.99,0.00,39.99),
(9,30,1,449.99,0.00,449.99),
(23,2,1,1399.99,0.00,1399.99);

/* ============================================================
   2. INSERT DATA — GOVERNMENT
   (citizens 1-20, gov_employees 1-15)
============================================================ */
INSERT INTO citizens (first_name, last_name, date_of_birth, gender, national_id, email, phone, address, city, state, zip, registered_voter) VALUES
('Andrew','Collins','1980-02-11','Male','ID-10000001','andrew.collins@example.com','555-601-5001','12 Oak St','Springfield','IL','62701',1),
('Beatrice','Nwosu','1975-06-19','Female','ID-10000002','beatrice.nwosu@example.com','555-601-5002','34 Elm St','Springfield','IL','62702',1),
('Carlos','Ibanez','1992-09-05','Male','ID-10000003','carlos.ibanez@example.com','555-601-5003','56 Pine St','Springfield','IL','62703',0),
('Deborah','Sanchez','1988-12-30','Female','ID-10000004','deborah.sanchez@example.com','555-601-5004','78 Maple St','Springfield','IL','62704',1),
('Edward','Turner','1965-04-14','Male','ID-10000005','edward.turner@example.com','555-601-5005','90 Cedar St','Springfield','IL','62705',1),
('Fiona','Walsh','1998-01-08','Female','ID-10000006','fiona.walsh@example.com','555-601-5006','23 Birch St','Springfield','IL','62706',0),
('George','Papadopoulos','1971-07-25','Male','ID-10000007','george.papadopoulos@example.com','555-601-5007','45 Spruce St','Springfield','IL','62707',1),
('Helen','Osborne','1983-11-11','Female','ID-10000008','helen.osborne@example.com','555-601-5008','67 Willow St','Springfield','IL','62708',1),
('Ian','Fitzgerald','1990-03-22','Male','ID-10000009','ian.fitzgerald@example.com','555-601-5009','89 Ash St','Springfield','IL','62709',0),
('Julia','Moreno','1977-08-17','Female','ID-10000010','julia.moreno@example.com','555-601-5010','101 Poplar St','Springfield','IL','62710',1),
('Kenji','Watanabe','1994-05-02','Male','ID-10000011','kenji.watanabe@example.com','555-601-5011','13 Chestnut St','Riverside','IL','62711',1),
('Laura','Bianchi','1969-10-29','Female','ID-10000012','laura.bianchi@example.com','555-601-5012','25 Walnut St','Riverside','IL','62712',1),
('Mateus','Silva','1986-06-06','Male','ID-10000013','mateus.silva@example.com','555-601-5013','37 Hickory St','Riverside','IL','62713',0),
('Nadia','Rahman','1991-02-27','Female','ID-10000014','nadia.rahman@example.com','555-601-5014','49 Sycamore St','Riverside','IL','62714',1),
('Oscar','Delgado','1973-09-13','Male','ID-10000015','oscar.delgado@example.com','555-601-5015','61 Dogwood St','Riverside','IL','62715',1),
('Patricia','Novak','1996-12-20','Female','ID-10000016','patricia.novak@example.com','555-601-5016','73 Juniper St','Riverside','IL','62716',0),
('Quentin','Hall','1982-04-04','Male','ID-10000017','quentin.hall@example.com','555-601-5017','85 Redwood St','Riverside','IL','62717',1),
('Rosa','Fernandez','1979-01-16','Female','ID-10000018','rosa.fernandez@example.com','555-601-5018','97 Sequoia St','Riverside','IL','62718',1),
('Simon','Kaczmarek','1987-07-07','Male','ID-10000019','simon.kaczmarek@example.com','555-601-5019','109 Magnolia St','Riverside','IL','62719',0),
('Tanya','Okonkwo','1993-03-03','Female','ID-10000020','tanya.okonkwo@example.com','555-601-5020','121 Larkspur St','Riverside','IL','62720',1);

INSERT INTO gov_employees (first_name, last_name, department, title, employee_number, salary, hire_date, is_active) VALUES
('William','Foster','Public Works','Civil Engineer','EMP-100001',78500.00,'2012-03-01',1),
('Nancy','Delacroix','Finance','Budget Analyst','EMP-100002',68200.00,'2015-06-15',1),
('Robert','Kessler','Health','Health Inspector','EMP-100003',61500.00,'2018-01-20',1),
('Susan','Vargas','Transportation','Traffic Engineer','EMP-100004',82300.00,'2011-09-10',1),
('Charles','Nakamura','Police','Police Officer','EMP-100005',72800.00,'2014-04-05',1),
('Patricia','O''Malley','Fire','Fire Captain','EMP-100006',88900.00,'2009-11-01',1),
('Kevin','Adeyemi','Parks & Recreation','Parks Manager','EMP-100007',59400.00,'2017-07-18',1),
('Diane','Zhou','IT','Systems Administrator','EMP-100008',76200.00,'2016-02-22',1),
('Frank','Whitmore','Legal','Assistant City Attorney','EMP-100009',95600.00,'2013-05-30',1),
('Gloria','Petrov','Education','Program Coordinator','EMP-100010',54300.00,'2019-08-12',1),
('Harold','Simmons','Public Works','Permit Reviewer','EMP-100011',63700.00,'2020-01-06',1),
('Irene','Castillo','Police','Patrol Sergeant','EMP-100012',79100.00,'2010-10-15',1),
('Jack','Farrell','Health','Environmental Health Specialist','EMP-100013',66800.00,'2021-03-03',1),
('Karen','Lindqvist','Transportation','Permit Coordinator','EMP-100014',58900.00,'2018-09-25',1),
('Leon','Dubois','Fire','Fire Inspector','EMP-100015',71200.00,'2015-12-01',1);

INSERT INTO permits (citizen_id, permit_number, permit_type, description, status, applied_at, approved_at, expires_at, fee, reviewed_by) VALUES
(1,'PRMT-100001','building','Detached garage construction','approved','2023-11-01','2023-11-20','2025-11-20',450.00,11),
(2,'PRMT-100002','electrical','Panel upgrade','approved','2023-11-05','2023-11-15','2025-11-15',150.00,1),
(3,'PRMT-100003','business_license','New retail storefront','pending','2024-01-10',NULL,NULL,300.00,NULL),
(4,'PRMT-100004','plumbing','Bathroom remodel','approved','2023-12-01','2023-12-10','2025-12-10',175.00,11),
(5,'PRMT-100005','sign','New illuminated business sign','denied','2024-01-15',NULL,NULL,80.00,1),
(6,'PRMT-100006','event','Neighborhood block party','approved','2024-02-01','2024-02-05','2024-03-01',50.00,7),
(7,'PRMT-100007','building','Home addition','pending','2024-02-10',NULL,NULL,600.00,NULL),
(8,'PRMT-100008','demolition','Shed removal','approved','2023-10-01','2023-10-08','2024-10-08',120.00,11),
(9,'PRMT-100009','electrical','Solar panel installation','approved','2024-01-20','2024-02-01','2026-02-01',350.00,1),
(10,'PRMT-100010','business_license','Home bakery license','approved','2023-09-15','2023-09-30','2025-09-30',200.00,NULL),
(11,'PRMT-100011','plumbing','Water heater replacement','approved','2024-01-25','2024-01-28','2026-01-28',100.00,11),
(12,'PRMT-100012','building','Deck construction','expired','2022-05-01','2022-05-15','2023-05-15',275.00,11),
(13,'PRMT-100013','sign','Storefront sign replacement','approved','2024-02-05','2024-02-12','2026-02-12',90.00,1),
(14,'PRMT-100014','event','Farmers market','approved','2024-03-01','2024-03-05','2024-11-01',75.00,7),
(15,'PRMT-100015','business_license','Food truck permit','pending','2024-02-20',NULL,NULL,250.00,NULL),
(16,'PRMT-100016','electrical','Rewire older home','approved','2023-08-01','2023-08-20','2025-08-20',400.00,1),
(17,'PRMT-100017','building','Fence installation','approved','2024-01-05','2024-01-12','2026-01-12',60.00,11),
(18,'PRMT-100018','demolition','Interior wall removal','revoked','2023-06-01','2023-06-10','2024-06-10',150.00,11),
(19,'PRMT-100019','plumbing','Sewer line repair','approved','2024-02-15','2024-02-18','2026-02-18',220.00,11),
(20,'PRMT-100020','sign','Temporary banner','approved','2024-03-02','2024-03-03','2024-04-03',30.00,1);

INSERT INTO violations (citizen_id, violation_code, description, location, fine_amount, is_paid, issued_at, due_date, officer_id) VALUES
(3,'VIO-001','Overdue parking meter','200 Main St',35.00,1,'2024-01-05 09:15:00','2024-02-05',12),
(6,'VIO-002','Noise ordinance violation','45 Birch St',150.00,0,'2024-01-10 23:30:00','2024-02-10',5),
(9,'VIO-003','Illegal dumping','Riverside Park',500.00,0,'2024-01-15 14:00:00','2024-02-15',13),
(11,'VIO-001','Expired parking permit','88 Chestnut St',35.00,1,'2024-01-20 10:00:00','2024-02-20',12),
(13,'VIO-010','Unlicensed business operation','37 Hickory St',750.00,0,'2024-01-25 11:00:00','2024-02-25',NULL),
(16,'VIO-004','Failure to maintain sidewalk','73 Juniper St',120.00,1,'2024-02-01 08:00:00','2024-03-01',11),
(19,'VIO-002','Noise ordinance violation','109 Magnolia St',150.00,0,'2024-02-05 22:45:00','2024-03-05',5),
(2,'VIO-020','Unauthorized signage','34 Elm St',200.00,1,'2024-02-08 13:00:00','2024-03-08',7),
(5,'VIO-001','Expired parking permit','90 Cedar St',35.00,0,'2024-02-10 09:30:00','2024-03-10',12),
(8,'VIO-005','Building code violation - unpermitted work','67 Willow St',900.00,0,'2024-02-12 15:00:00','2024-03-12',13),
(14,'VIO-003','Illegal dumping','Lakeside Trail',500.00,1,'2024-02-15 16:30:00','2024-03-15',13),
(17,'VIO-004','Failure to maintain sidewalk','85 Redwood St',120.00,0,'2024-02-18 08:15:00','2024-03-18',11),
(1,'VIO-001','Expired parking permit','12 Oak St',35.00,1,'2024-02-20 09:00:00','2024-03-20',12),
(10,'VIO-002','Noise ordinance violation','101 Poplar St',150.00,0,'2024-02-22 23:00:00','2024-03-22',5),
(15,'VIO-010','Unlicensed business operation','61 Dogwood St',750.00,0,'2024-02-25 10:00:00','2024-03-25',NULL);

INSERT INTO public_contracts (contract_number, title, vendor_name, department, value, status, start_date, end_date, description) VALUES
('CONT-100001','Road Resurfacing Program 2024','Statewide Paving Co.','Public Works',4500000.00,'active','2024-01-01','2024-12-31','Annual road resurfacing across 40 lane-miles'),
('CONT-100002','City Hall IT Modernization','TechSpan Solutions','IT',1200000.00,'active','2023-09-01','2025-03-01','Network and hardware refresh for city offices'),
('CONT-100003','Park Maintenance Services','GreenScape Grounds LLC','Parks & Recreation',650000.00,'active','2024-01-01','2024-12-31','Grounds maintenance for 12 city parks'),
('CONT-100004','Fire Station Renovation','Beacon Construction Group','Fire',3200000.00,'active','2023-06-01','2024-08-01','Renovation of Fire Station No. 3'),
('CONT-100005','School Bus Fleet Maintenance','Reliable Fleet Services','Education',890000.00,'active','2024-02-01','2025-01-31','Maintenance contract for 60-bus fleet'),
('CONT-100006','Water Treatment Plant Upgrade','AquaTech Engineering','Public Works',12000000.00,'active','2022-03-01','2025-06-01','Upgrade of primary filtration systems'),
('CONT-100007','Municipal Legal Services','Harmon & Reyes LLP','Legal',420000.00,'active','2024-01-01','2024-12-31','Outside counsel retainer'),
('CONT-100008','Public Transit Signage','Metro Signworks Inc.','Transportation',310000.00,'completed','2023-01-01','2023-10-01','Bus stop signage replacement citywide'),
('CONT-100009','Police Vehicle Fleet Purchase','Capital Fleet Motors','Police',2100000.00,'active','2024-03-01','2024-09-01','Purchase of 25 patrol vehicles'),
('CONT-100010','Emergency Dispatch System','SecureComm Systems','Fire',1750000.00,'pending','2024-04-01','2026-04-01','New 911 dispatch software and hardware'),
('CONT-100011','Downtown Streetlight LED Conversion','BrightPath Electric','Public Works',980000.00,'active','2023-11-01','2024-11-01','LED conversion of 3,200 streetlights'),
('CONT-100012','Senior Center Food Services','Community Meals Co.','Health',275000.00,'terminated','2022-01-01','2023-06-01','Contract terminated for underperformance');

INSERT INTO service_requests (citizen_id, service_type, description, priority, status, assigned_to, location, submitted_at, resolved_at) VALUES
(1,'pothole_repair','Large pothole causing vehicle damage','high','resolved',11,'12 Oak St','2024-01-06 08:00:00','2024-01-10 14:00:00'),
(2,'streetlight_outage','Streetlight has been out for two weeks','normal','resolved',11,'34 Elm St','2024-01-08 09:00:00','2024-01-15 10:00:00'),
(3,'noise_complaint','Construction noise before permitted hours','normal','closed',12,'56 Pine St','2024-01-09 07:30:00','2024-01-09 12:00:00'),
(4,'graffiti_removal','Graffiti on public library wall','low','resolved',7,'78 Maple St','2024-01-10 10:00:00','2024-01-20 09:00:00'),
(5,'tree_trimming','Overgrown tree blocking sidewalk','normal','open',7,'90 Cedar St','2024-01-12 11:00:00',NULL),
(6,'water_leak','Water main leak flooding street','urgent','resolved',1,'23 Birch St','2024-01-14 06:45:00','2024-01-14 11:00:00'),
(7,'permit_inquiry','Question about fence permit requirements','low','closed',14,'45 Spruce St','2024-01-16 13:00:00','2024-01-17 09:00:00'),
(8,'refuse_collection','Missed trash pickup','normal','resolved',NULL,'67 Willow St','2024-01-18 08:00:00','2024-01-19 08:00:00'),
(9,'pothole_repair','Multiple potholes on residential street','high','in_progress',11,'89 Ash St','2024-01-20 09:00:00',NULL),
(10,'streetlight_outage','Flickering streetlight, safety concern','normal','open',NULL,'101 Poplar St','2024-01-22 19:00:00',NULL),
(11,'noise_complaint','Loud party past midnight','normal','closed',12,'13 Chestnut St','2024-01-25 00:30:00','2024-01-25 01:15:00'),
(12,'water_leak','Fire hydrant leaking continuously','urgent','resolved',1,'25 Walnut St','2024-02-01 07:00:00','2024-02-01 12:00:00'),
(13,'graffiti_removal','Graffiti on underpass','low','open',7,'37 Hickory St','2024-02-03 10:00:00',NULL),
(14,'tree_trimming','Branches touching power lines','high','in_progress',7,'49 Sycamore St','2024-02-05 08:30:00',NULL),
(15,'permit_inquiry','Business license renewal question','low','resolved',14,'61 Dogwood St','2024-02-07 14:00:00','2024-02-08 09:00:00'),
(16,'refuse_collection','Recycling bin damaged, needs replacement','normal','closed',NULL,'73 Juniper St','2024-02-09 11:00:00','2024-02-12 10:00:00'),
(17,'pothole_repair','Deep pothole near school crossing','urgent','resolved',11,'85 Redwood St','2024-02-11 07:00:00','2024-02-12 09:00:00'),
(18,'streetlight_outage','Entire block streetlights out','high','open',NULL,'97 Sequoia St','2024-02-13 20:00:00',NULL);

/* ============================================================
   3. FOREIGN KEY CONSTRAINTS
   Added after all data is loaded (avoids insert-order headaches).
   Every referenced column below was cross-checked against the actual
   rows inserted above, so re-enabling FK checks here validates cleanly.
============================================================ */

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

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

-- Retail (product_id/customer_id cross-reference the E-commerce tables — same synthetic company, shared catalog/customer base;
--         pos_transactions links to a customer indirectly through loyalty_members rather than a direct customer_id column)
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 loyalty_members  ADD CONSTRAINT fk_loyalty_members_customers      FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
ALTER TABLE loyalty_members  ADD CONSTRAINT fk_loyalty_members_stores        FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE promotions       ADD CONSTRAINT fk_promotions_stores             FOREIGN KEY (store_id)    REFERENCES stores(store_id);
ALTER TABLE 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_loyalty      FOREIGN KEY (member_id)   REFERENCES loyalty_members(member_id);
ALTER TABLE pos_items        ADD CONSTRAINT fk_pos_items_pos_transactions    FOREIGN KEY (pos_txn_id)  REFERENCES pos_transactions(pos_txn_id);
ALTER TABLE pos_items        ADD CONSTRAINT fk_pos_items_products            FOREIGN KEY (product_id)  REFERENCES products(product_id);

-- Government (gov_employees has no self-referential supervisor column in this schema, unlike the SQL Server
--             reference, so that particular FK has no MySQL equivalent here; the employee-link FKs below are
--             additional relationships this schema supports via reviewed_by/officer_id/assigned_to)
ALTER TABLE permits           ADD CONSTRAINT fk_permits_citizens          FOREIGN KEY (citizen_id)   REFERENCES citizens(citizen_id);
ALTER TABLE permits           ADD CONSTRAINT fk_permits_reviewer          FOREIGN KEY (reviewed_by)  REFERENCES gov_employees(employee_id);
ALTER TABLE violations        ADD CONSTRAINT fk_violations_citizens       FOREIGN KEY (citizen_id)   REFERENCES citizens(citizen_id);
ALTER TABLE violations        ADD CONSTRAINT fk_violations_officer        FOREIGN KEY (officer_id)   REFERENCES gov_employees(employee_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);

/* ============================================================
   4. CHECK CONSTRAINTS
   Mirrors generate_mysql.py's CHECK_CONSTRAINTS list (the authoritative
   validation rules for this schema). The transactions status check is
   widened by one value ('flagged') versus the generator's list because
   this hand-written dataset uses 'flagged' on a few rows to illustrate
   fraud-alert-worthy transactions.
============================================================ */
ALTER TABLE patients            ADD CONSTRAINT ck_patients_gender               CHECK (gender IN ('Male','Female','Non-binary','Other'));
ALTER TABLE citizens             ADD CONSTRAINT ck_citizens_gender               CHECK (gender IS NULL OR gender IN ('Male','Female','Non-binary','Other'));
ALTER TABLE appointments         ADD CONSTRAINT ck_appointments_status           CHECK (status IN ('scheduled','completed','cancelled','no_show'));
ALTER TABLE insurance_claims     ADD CONSTRAINT ck_insurance_claims_status       CHECK (status IN ('pending','approved','denied','under_review','paid'));
ALTER TABLE products             ADD CONSTRAINT ck_products_price                CHECK (price >= 0);
ALTER TABLE reviews              ADD CONSTRAINT ck_reviews_rating                CHECK (rating BETWEEN 1 AND 5);
ALTER TABLE orders               ADD CONSTRAINT ck_orders_status                 CHECK (status IN ('pending','processing','shipped','delivered','cancelled','returned'));
ALTER TABLE orders               ADD CONSTRAINT ck_orders_totals_nonneg          CHECK (subtotal >= 0 AND total >= 0);
ALTER TABLE accounts             ADD CONSTRAINT ck_accounts_status               CHECK (status IN ('active','inactive','frozen','closed'));
ALTER TABLE transactions         ADD CONSTRAINT ck_transactions_status           CHECK (status IN ('completed','pending','failed','reversed','flagged'));
ALTER TABLE loans                ADD CONSTRAINT ck_loans_status                  CHECK (status IN ('active','paid_off','default','delinquent'));
ALTER TABLE fraud_alerts         ADD CONSTRAINT ck_fraud_alerts_status           CHECK (status IN ('open','under_review','resolved','false_positive'));
ALTER TABLE machines             ADD CONSTRAINT ck_machines_status               CHECK (status IN ('running','idle','maintenance','fault'));
ALTER TABLE production_batches   ADD CONSTRAINT ck_production_batches_status     CHECK (status IN ('completed','in_progress','rejected','on_hold'));
ALTER TABLE quality_checks       ADD CONSTRAINT ck_quality_checks_result         CHECK (result IN ('pass','fail','conditional'));
ALTER TABLE pos_transactions     ADD CONSTRAINT ck_pos_transactions_total_nonneg CHECK (total >= 0);
ALTER TABLE permits              ADD CONSTRAINT ck_permits_status                CHECK (status IN ('pending','approved','denied','expired','revoked'));
ALTER TABLE public_contracts     ADD CONSTRAINT ck_public_contracts_status       CHECK (status IN ('active','completed','terminated','pending'));
ALTER TABLE service_requests     ADD CONSTRAINT ck_service_requests_status       CHECK (status IN ('open','in_progress','resolved','closed','cancelled'));
ALTER TABLE service_requests     ADD CONSTRAINT ck_service_requests_priority     CHECK (priority IN ('low','normal','high','urgent'));

SELECT 'Foreign keys and check constraints added' AS status;

/* ============================================================
   5. FUNCTIONS — 6 scalar helper functions, one per industry
============================================================ */
DELIMITER $$

-- 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
DETERMINISTIC
READS SQL DATA
BEGIN
    RETURN TIMESTAMPDIFF(YEAR, p_date_of_birth, CURDATE());
END$$

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

-- Finance: current balance for an account
CREATE FUNCTION fn_account_balance (p_account_id INT)
RETURNS DECIMAL(18,2)
READS SQL DATA
BEGIN
    DECLARE v_bal DECIMAL(18,2);
    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(20))
RETURNS DECIMAL(5,2)
DETERMINISTIC
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 TINYINT(1)
DETERMINISTIC
BEGIN
    RETURN CASE WHEN p_expires_at IS NOT NULL AND p_expires_at < CURDATE()
                THEN 1 ELSE 0 END;
END$$

-- Manufacturing/IoT: days since a machine's last maintenance
CREATE FUNCTION fn_machine_days_since_maintenance (p_machine_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE v_last_maint DATE;
    SELECT last_maintenance INTO v_last_maint FROM machines WHERE machine_id = p_machine_id;
    IF v_last_maint IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN DATEDIFF(CURDATE(), v_last_maint);
END$$

DELIMITER ;

SELECT 'Functions created' AS status;

/* ============================================================
   6. STORED PROCEDURES — 10 procedures across all six industries
============================================================ */
DELIMITER $$

-- 1. Healthcare: full clinical history for a patient (3 result sets)
CREATE PROCEDURE usp_GetPatientHistory (IN p_patient_id INT)
BEGIN
    SELECT appointment_id, scheduled_at, status, reason
      FROM appointments WHERE patient_id = p_patient_id ORDER BY scheduled_at DESC;

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

    SELECT prescription_id, drug_name, dosage, prescribed_at, expires_at
      FROM prescriptions WHERE patient_id = p_patient_id ORDER BY prescribed_at DESC;
END$$

-- 2. Healthcare: file a new insurance claim
CREATE PROCEDURE usp_AddInsuranceClaim (
    IN p_patient_id INT, IN p_provider_id INT, IN p_claim_number VARCHAR(30),
    IN p_service_date DATE, IN p_submitted_date DATE, IN p_amount_billed DECIMAL(12,2)
)
BEGIN
    INSERT INTO insurance_claims (patient_id, provider_id, claim_number, service_date, submitted_date, amount_billed, status)
    VALUES (p_patient_id, p_provider_id, p_claim_number, p_service_date, p_submitted_date, p_amount_billed, 'pending');

    SELECT LAST_INSERT_ID() AS new_claim_id;
END$$

-- 3. E-commerce: lifetime order summary for a customer
CREATE PROCEDURE usp_GetCustomerOrderSummary (IN p_customer_id INT)
BEGIN
    SELECT
        c.customer_id, c.first_name, c.last_name,
        COUNT(o.order_id)            AS total_orders,
        IFNULL(SUM(o.total), 0)      AS lifetime_value,
        MAX(o.created_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's discount to an order's total
CREATE PROCEDURE usp_ApplyPromotionToOrder (IN p_promotion_id INT, IN p_order_id INT)
BEGIN
    DECLARE v_discount_pct DECIMAL(5,2);
    DECLARE v_discount_amount DECIMAL(10,2);
    DECLARE v_active TINYINT(1);

    SELECT discount_pct, discount_amount, is_active
      INTO v_discount_pct, v_discount_amount, v_active
      FROM promotions WHERE promotion_id = p_promotion_id;

    IF v_active IS NULL OR v_active = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Promotion not found or inactive.';
    END IF;

    UPDATE orders
       SET total = CASE
                        WHEN v_discount_pct IS NOT NULL THEN total * (1 - v_discount_pct / 100.0)
                        WHEN v_discount_amount IS NOT NULL THEN GREATEST(total - v_discount_amount, 0)
                        ELSE total
                   END
     WHERE order_id = p_order_id;
END$$

-- 5. Finance: statement of transactions on an account within a date range
CREATE PROCEDURE usp_GetAccountStatement (IN p_account_id INT, IN p_start_date DATETIME, IN p_end_date DATETIME)
BEGIN
    SELECT transaction_id, transacted_at, transaction_type, amount, merchant_name, 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 usp_FlagFraudAlert (
    IN p_transaction_id BIGINT, IN p_account_id INT, IN p_alert_type VARCHAR(50), IN p_risk_score DECIMAL(5,2)
)
BEGIN
    INSERT INTO fraud_alerts (transaction_id, account_id, alert_type, risk_score, details, status)
    VALUES (p_transaction_id, p_account_id, p_alert_type, p_risk_score, 'Manually flagged via usp_FlagFraudAlert', 'open');

    SELECT LAST_INSERT_ID() AS new_alert_id;
END$$

-- 7. Manufacturing/IoT: health summary (reading count, anomaly count, average value) for a machine
CREATE PROCEDURE usp_GetMachineHealthSummary (IN p_machine_id INT)
BEGIN
    SELECT
        m.machine_id, m.name, m.status,
        COUNT(sr.reading_id)                                AS reading_count,
        SUM(CASE WHEN sr.is_anomaly = 1 THEN 1 ELSE 0 END)   AS anomaly_count,
        AVG(sr.value)                                        AS avg_reading_value
    FROM machines m
    LEFT JOIN sensor_readings sr ON sr.machine_id = m.machine_id
    WHERE m.machine_id = p_machine_id
    GROUP BY m.machine_id, m.name, m.status;
END$$

-- 8. Retail: sales summary for a store on a given calendar day
CREATE PROCEDURE usp_GetStoreSalesSummary (IN p_store_id INT, IN p_sales_date DATE)
BEGIN
    SELECT
        s.store_id, s.name,
        COUNT(pt.pos_txn_id)          AS transaction_count,
        IFNULL(SUM(pt.total), 0)      AS total_sales
    FROM stores s
    LEFT JOIN pos_transactions pt
           ON pt.store_id = s.store_id AND DATE(pt.transacted_at) = p_sales_date AND pt.voided = 0
    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)
CREATE PROCEDURE usp_GetCitizenServiceHistory (IN p_citizen_id INT)
BEGIN
    SELECT permit_id, permit_type, applied_at, expires_at, status
      FROM permits WHERE citizen_id = p_citizen_id;

    SELECT violation_id, violation_code, issued_at, fine_amount, is_paid
      FROM violations WHERE citizen_id = p_citizen_id;

    SELECT request_id, service_type, submitted_at, status
      FROM service_requests WHERE citizen_id = p_citizen_id;
END$$

-- 10. E-commerce: top N best-selling products by quantity sold
--     (MySQL has no table-valued functions, and LIMIT can't safely take a routine
--     parameter across all 8.0 versions, so this uses dynamic SQL — the standard
--     MySQL translation of a parameterized "top N" query.)
CREATE PROCEDURE usp_GetTopProducts (IN p_top_n INT)
BEGIN
    SET @top_n = p_top_n;
    SET @sql = 'SELECT p.product_id, p.name, p.price, COUNT(oi.order_item_id) AS times_ordered, SUM(oi.quantity) AS total_qty_sold
                 FROM products p JOIN order_items oi ON oi.product_id = p.product_id
                 GROUP BY p.product_id, p.name, p.price
                 ORDER BY total_qty_sold DESC
                 LIMIT ?';
    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @top_n;
    DEALLOCATE PREPARE stmt;
END$$

DELIMITER ;

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 OR REPLACE VIEW vw_patient_appointment_summary AS
SELECT
    a.appointment_id,
    p.patient_id,
    CONCAT(p.first_name, ' ', p.last_name)  AS patient_name,
    pr.provider_id,
    CONCAT(pr.first_name, ' ', pr.last_name) AS provider_name,
    pr.specialty,
    a.scheduled_at,
    a.status,
    a.reason
FROM appointments a
JOIN patients p  ON p.patient_id  = a.patient_id
JOIN providers pr ON pr.provider_id = a.provider_id;

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

-- Finance: per-account transaction activity and fraud-alert exposure
CREATE OR REPLACE VIEW vw_account_transaction_summary AS
SELECT
    ac.account_id,
    ac.account_number,
    ac.holder_name,
    ac.status                                             AS account_status,
    ac.balance,
    COUNT(t.transaction_id)                                AS transaction_count,
    IFNULL(SUM(CASE WHEN t.transaction_type = 'debit' THEN t.amount ELSE 0 END), 0)  AS total_debits,
    IFNULL(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.holder_name, ac.status, ac.balance;

-- Manufacturing/IoT: machine health rollup from sensor readings
CREATE OR REPLACE VIEW vw_machine_sensor_health AS
SELECT
    m.machine_id,
    m.name                                              AS machine_name,
    m.status                                             AS machine_status,
    COUNT(sr.reading_id)                                 AS reading_count,
    SUM(CASE WHEN sr.is_anomaly = 1 THEN 1 ELSE 0 END)   AS anomaly_count,
    ROUND(AVG(sr.value), 2)                              AS avg_reading_value,
    MAX(sr.read_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 with loyalty-member participation
CREATE OR REPLACE VIEW vw_store_sales_summary AS
SELECT
    s.store_id,
    s.name                                                   AS store_name,
    s.city,
    s.state,
    COUNT(pt.pos_txn_id)                                     AS transaction_count,
    IFNULL(SUM(CASE WHEN pt.voided = 0 THEN pt.total ELSE 0 END), 0) AS total_sales,
    (SELECT COUNT(*) FROM loyalty_members lm WHERE lm.store_id = s.store_id) AS loyalty_member_count
FROM stores s
LEFT JOIN pos_transactions pt ON pt.store_id = s.store_id
GROUP BY s.store_id, s.name, s.city, s.state;

-- Government: per-citizen civic activity rollup (permits, violations, service requests)
CREATE OR REPLACE VIEW vw_citizen_government_summary AS
SELECT
    c.citizen_id,
    CONCAT(c.first_name, ' ', c.last_name)                              AS citizen_name,
    c.city,
    c.registered_voter,
    (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,
    IFNULL((SELECT SUM(v.fine_amount) FROM violations v WHERE v.citizen_id = c.citizen_id AND v.is_paid = 0), 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 — 4 triggers (fraud auto-flagging, audit logging,
      anomaly detection, data validation)
============================================================ */
DELIMITER $$

-- Finance: auto-flag any newly inserted transaction over $10,000 for fraud review
CREATE TRIGGER trg_transactions_flag_large
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    IF NEW.amount > 10000 THEN
        INSERT INTO fraud_alerts (transaction_id, account_id, alert_type, risk_score, details, status)
        VALUES (NEW.transaction_id, NEW.account_id, 'large_amount', 70.00,
                CONCAT('Auto-flagged by trigger: transaction amount ', NEW.amount, ' exceeds $10,000 threshold'),
                'open');
    END IF;
END$$

-- Finance: audit-log any change to an account's balance (sensitive-table audit trail)
CREATE TRIGGER trg_accounts_audit_balance
AFTER UPDATE ON accounts
FOR EACH ROW
BEGIN
    IF NOT (NEW.balance <=> OLD.balance) THEN
        INSERT INTO audit_log (table_name, record_id, action, changed_by, old_values, new_values, changed_at)
        VALUES ('accounts', NEW.account_id, 'UPDATE', 'trg_accounts_audit_balance',
                CONCAT('balance=', OLD.balance), CONCAT('balance=', NEW.balance), NOW());
    END IF;
END$$

-- Manufacturing/IoT: flag a sensor reading as an anomaly if it falls outside the
-- sensor's configured thresholds, before the row is written
CREATE TRIGGER trg_sensor_readings_detect_anomaly
BEFORE INSERT ON sensor_readings
FOR EACH ROW
BEGIN
    DECLARE v_min DECIMAL(12,4);
    DECLARE v_max DECIMAL(12,4);

    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
        SET NEW.is_anomaly = 1;
    END IF;
END$$

-- Government: reject a violation record with a negative fine amount before it's written
CREATE TRIGGER trg_violations_validate_fine
BEFORE INSERT ON violations
FOR EACH ROW
BEGIN
    IF NEW.fine_amount < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'violations.fine_amount cannot be negative';
    END IF;
END$$

DELIMITER ;

SELECT 'Triggers created' AS status;

/* ============================================================
   9. BUILD COMPLETE
============================================================ */
SELECT 'EB_SampleDB build complete' AS status,
       (SELECT COUNT(*) FROM information_schema.tables  WHERE table_schema = 'EB_SampleDB' AND table_type = 'BASE TABLE') AS tables_created,
       (SELECT COUNT(*) FROM information_schema.views    WHERE table_schema = 'EB_SampleDB') AS views_created,
       (SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema = 'EB_SampleDB' AND routine_type = 'FUNCTION') AS functions_created,
       (SELECT COUNT(*) FROM information_schema.routines WHERE routine_schema = 'EB_SampleDB' AND routine_type = 'PROCEDURE') AS procedures_created,
       (SELECT COUNT(*) FROM information_schema.triggers WHERE trigger_schema = 'EB_SampleDB') AS triggers_created;

