-- Database schema for CSV transformation import
-- This schema supports customer, vehicle, agreement, and equity snapshot tracking

CREATE TABLE IF NOT EXISTS customer (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT,
    phone TEXT,
    postcode TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_customer_email ON customer(email);
CREATE INDEX IF NOT EXISTS idx_customer_phone ON customer(phone);

CREATE TABLE IF NOT EXISTS vehicle (
    id SERIAL PRIMARY KEY,
    registration TEXT UNIQUE NOT NULL,
    vin TEXT,
    make TEXT,
    model TEXT,
    derivative TEXT,
    fuel_type TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_vehicle_registration ON vehicle(registration);

CREATE TABLE IF NOT EXISTS agreement (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customer(id),
    vehicle_id INTEGER REFERENCES vehicle(id),
    agreement_number TEXT UNIQUE NOT NULL,
    start_date DATE,
    term_months INTEGER,
    monthly_payment NUMERIC(10,2),
    balloon NUMERIC(10,2),
    settlement NUMERIC(10,2),
    payments_left NUMERIC(10,2),
    imported_at TIMESTAMP,
    expires_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_agreement_number ON agreement(agreement_number);
CREATE INDEX IF NOT EXISTS idx_agreement_customer ON agreement(customer_id);
CREATE INDEX IF NOT EXISTS idx_agreement_vehicle ON agreement(vehicle_id);

CREATE TABLE IF NOT EXISTS equity_snapshot (
    id SERIAL PRIMARY KEY,
    agreement_id INTEGER REFERENCES agreement(id),
    snapshot_date DATE NOT NULL,
    settlement NUMERIC(10,2),
    payments_left NUMERIC(10,2),
    raw_row JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(agreement_id, snapshot_date)
);

CREATE INDEX IF NOT EXISTS idx_snapshot_agreement ON equity_snapshot(agreement_id);
CREATE INDEX IF NOT EXISTS idx_snapshot_date ON equity_snapshot(snapshot_date);
