CREATE TABLE IF NOT EXISTS app.leads (
    lead_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name VARCHAR NOT NULL,
    last_name VARCHAR NOT NULL,
    email VARCHAR NOT NULL,
    phone VARCHAR,
    facility_name VARCHAR NOT NULL,
    facility_type VARCHAR NOT NULL,
    nurses_needed_range VARCHAR,
    message TEXT,
    source VARCHAR NOT NULL DEFAULT 'pilot',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    last_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS leads_created_at_idx ON app.leads (created_at DESC);
CREATE INDEX IF NOT EXISTS leads_email_idx ON app.leads (email);

DROP TRIGGER IF EXISTS update_leads_timestamp ON app.leads;
CREATE TRIGGER update_leads_timestamp
    BEFORE UPDATE ON app.leads
    FOR EACH ROW EXECUTE PROCEDURE app.update_lastupdated_timestamp();