-- ============================================================
-- thequoteform — PostgreSQL schema
-- Mirrors the original Mongo/Prisma models, adapted for Postgres
-- and expanded with the tables the admin auth + settings system needs.
-- Run once via the key-guarded install runner (see sql/README.md).
-- ============================================================

-- Admin users (dashboard login — separate from public site visitors)
CREATE TABLE IF NOT EXISTS admin_users (
    id                  SERIAL PRIMARY KEY,
    name                VARCHAR(255),
    email               VARCHAR(255) UNIQUE NOT NULL,
    hashed_password     VARCHAR(255) NOT NULL,
    is_active           BOOLEAN DEFAULT TRUE,
    role                VARCHAR(50) DEFAULT 'admin',
    password_reset_token          VARCHAR(255),
    password_reset_token_expires  TIMESTAMP,
    session_token       VARCHAR(255),
    created_at          TIMESTAMP DEFAULT NOW(),
    updated_at          TIMESTAMP DEFAULT NOW()
);

-- Rate limiting for login attempts (per JCFC security baseline)
CREATE TABLE IF NOT EXISTS login_attempts (
    id            SERIAL PRIMARY KEY,
    email         VARCHAR(255) NOT NULL,
    ip_address    VARCHAR(64) NOT NULL,
    success       BOOLEAN DEFAULT FALSE,
    attempted_at  TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_login_attempts_lookup ON login_attempts (email, ip_address, attempted_at);

-- Quotes (the "Car" model — every submitted quote request)
CREATE TABLE IF NOT EXISTS quotes (
    id                       SERIAL PRIMARY KEY,
    year                     VARCHAR(10) NOT NULL,
    make                     VARCHAR(100) NOT NULL,
    model                    VARCHAR(100) NOT NULL,
    trim                     VARCHAR(100),
    ownership_document       VARCHAR(255) NOT NULL,
    paid_off                 VARCHAR(255) NOT NULL,
    vehicle_condition        VARCHAR(255) NOT NULL,
    wheels                   VARCHAR(255) NOT NULL,
    body_damage              VARCHAR(255) NOT NULL,
    part_missing             VARCHAR(255) NOT NULL,
    all_wheels               VARCHAR(255) NOT NULL,
    battery                  VARCHAR(255) NOT NULL,
    catalytic                VARCHAR(255) NOT NULL,
    vin                      VARCHAR(32) NOT NULL,
    mileage                  VARCHAR(32) NOT NULL,
    body_damage_description  TEXT,
    part_missing_description TEXT,
    city                     VARCHAR(100) NOT NULL,
    state                    VARCHAR(50) NOT NULL,
    zip                      VARCHAR(16) NOT NULL,
    address                  VARCHAR(255),
    phone                    VARCHAR(32) NOT NULL,
    formatted_phone          VARCHAR(32),
    phone2                   VARCHAR(32),
    formatted_phone2         VARCHAR(32),
    name                     VARCHAR(100) NOT NULL,
    lastname                 VARCHAR(100),
    engine                   VARCHAR(100),
    no_order                 VARCHAR(50),
    sell_type                VARCHAR(50),
    price                    VARCHAR(50),
    old_price                VARCHAR(50),
    price2                   VARCHAR(50),
    old_price2               VARCHAR(50),
    price3                   VARCHAR(50),
    old_price3               VARCHAR(50),
    price4                   VARCHAR(50),
    old_price4               VARCHAR(50),
    status                   VARCHAR(50) DEFAULT 'new',
    buyer_name               VARCHAR(255),
    buyer_email              VARCHAR(255),
    notes                    TEXT,
    vehicle_color            VARCHAR(100),
    vehicle_color_i          VARCHAR(100),
    vehicle_location          VARCHAR(255),
    vehicle_location_i        VARCHAR(255),
    location                 VARCHAR(255),
    created_at               TIMESTAMP DEFAULT NOW(),
    updated_at               TIMESTAMP,
    accepted_at              TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_quotes_status ON quotes (status);
CREATE INDEX IF NOT EXISTS idx_quotes_created_at ON quotes (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_quotes_vin ON quotes (vin);
CREATE INDEX IF NOT EXISTS idx_quotes_phone ON quotes (phone);

-- Buyers (the network of used-car buyers quotes get routed to)
CREATE TABLE IF NOT EXISTS buyers (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    phone       VARCHAR(32),
    address     VARCHAR(255),
    created_at  TIMESTAMP DEFAULT NOW()
);

-- Notifications (admin-to-admin / system notifications shown in dashboard bell)
CREATE TABLE IF NOT EXISTS notifications (
    id            SERIAL PRIMARY KEY,
    recipient_id  INTEGER NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    sender_id     INTEGER REFERENCES admin_users(id) ON DELETE SET NULL,
    item_id       VARCHAR(50),
    item_name     VARCHAR(255),
    item2_id      VARCHAR(50),
    type          VARCHAR(50) NOT NULL,
    content       TEXT NOT NULL,
    count         INTEGER DEFAULT 1,
    status        SMALLINT DEFAULT 0,
    created_at    TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON notifications (recipient_id, status);

-- Settings (key -> JSON value; drives public form config, thank-you page,
-- business contact info, SEO metadata, etc. — same pattern as the site()
-- helper on JCFC/WPCFC, just JSON-valued instead of flat fields)
CREATE TABLE IF NOT EXISTS settings (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) UNIQUE NOT NULL,
    value       JSONB NOT NULL DEFAULT '{}'::jsonb,
    updated_at  TIMESTAMP DEFAULT NOW()
);

-- Seed default settings rows so the app has something to read on first load
INSERT INTO settings (name, value) VALUES
    ('form', '{}'::jsonb),
    ('successform', '{}'::jsonb),
    ('metadata', '{}'::jsonb),
    ('businesscontact', '{}'::jsonb)
ON CONFLICT (name) DO NOTHING;