Real estate listings

CREATE TYPE listing_kind AS ENUM ('sale', 'rent');

CREATE TYPE offer_status AS ENUM ('submitted', 'countered', 'accepted', 'rejected', 'withdrawn');

CREATE TABLE agencies (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE agents (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    agency_id uuid NOT NULL,
    full_name text NOT NULL,
    email text NOT NULL UNIQUE
);
CREATE INDEX agents_agency_id_idx ON agents (agency_id);

CREATE TABLE properties (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    address text NOT NULL,
    city text NOT NULL,
    bedrooms int,
    bathrooms numeric(3,1),
    area_sqm numeric(8,2),
    year_built int
);

CREATE TABLE property_photos (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    property_id uuid NOT NULL,
    url text NOT NULL,
    position int DEFAULT 0 NOT NULL
);
CREATE INDEX property_photos_property_id_idx ON property_photos (property_id);

CREATE TABLE listings (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    property_id uuid NOT NULL,
    agent_id uuid NOT NULL,
    kind listing_kind NOT NULL,
    price numeric(12,2) NOT NULL,
    listed_at date DEFAULT current_date NOT NULL,
    active boolean DEFAULT true NOT NULL
);
CREATE INDEX listings_city_idx ON listings (kind, active, price);
CREATE INDEX listings_property_id_idx ON listings (property_id);
CREATE INDEX listings_agent_id_idx ON listings (agent_id);

CREATE TABLE viewings (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    listing_id uuid NOT NULL,
    visitor_name text NOT NULL,
    visitor_email text NOT NULL,
    scheduled_at timestamptz NOT NULL
);
CREATE INDEX viewings_listing_id_idx ON viewings (listing_id);

CREATE TABLE offers (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    listing_id uuid NOT NULL,
    bidder_name text NOT NULL,
    amount numeric(12,2) NOT NULL,
    status offer_status DEFAULT 'submitted' NOT NULL,
    made_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX offers_listing_id_idx ON offers (listing_id);

ALTER TABLE agents ADD FOREIGN KEY (agency_id) REFERENCES agencies (id) ON DELETE CASCADE;

ALTER TABLE property_photos ADD FOREIGN KEY (property_id) REFERENCES properties (id) ON DELETE CASCADE;

ALTER TABLE listings ADD FOREIGN KEY (property_id) REFERENCES properties (id);

ALTER TABLE listings ADD FOREIGN KEY (agent_id) REFERENCES agents (id);

ALTER TABLE viewings ADD FOREIGN KEY (listing_id) REFERENCES listings (id) ON DELETE CASCADE;

ALTER TABLE offers ADD FOREIGN KEY (listing_id) REFERENCES listings (id) ON DELETE CASCADE;