`migrations/006_business_pricing.sql`

42 lines
-- Captured 2026-05-04 from local DB schema drift before fresh-deploy reproducibility.
-- Idempotent — re-running on a populated DB is a no-op.

\restrict dFftKlWXyBtFds2c8ysfWTsGL16bwtTvHfrcFuXVQlLKvefKAeeRVZfZtbOJM8T

CREATE TABLE IF NOT EXISTS business_pricing (
    id bigint NOT NULL,
    business_id bigint NOT NULL,
    service_kind text NOT NULL,
    price_low integer,
    price_high integer,
    notes text,
    submitted_by text,
    source text DEFAULT 'owner'::text,
    recorded_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now()
);

CREATE SEQUENCE IF NOT EXISTS business_pricing_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE business_pricing_id_seq OWNED BY business_pricing.id;

ALTER TABLE ONLY business_pricing ALTER COLUMN id SET DEFAULT nextval('business_pricing_id_seq'::regclass);

ALTER TABLE ONLY business_pricing
    ADD CONSTRAINT business_pricing_pkey PRIMARY KEY (id);

CREATE INDEX IF NOT EXISTS idx_pricing_kind ON business_pricing USING btree (service_kind);

CREATE UNIQUE INDEX IF NOT EXISTS uq_pricing_biz_kind ON business_pricing USING btree (business_id, service_kind);

ALTER TABLE ONLY business_pricing
    ADD CONSTRAINT business_pricing_business_id_fkey FOREIGN KEY (business_id) REFERENCES businesses(id) ON DELETE CASCADE;

\unrestrict dFftKlWXyBtFds2c8ysfWTsGL16bwtTvHfrcFuXVQlLKvefKAeeRVZfZtbOJM8T