-- Meshkee CMS — addresses owned by exactly one user or business

CREATE TABLE addresses (
  id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  user_id      BIGINT,
  business_id  BIGINT,
  province     VARCHAR(100) NOT NULL,
  city         VARCHAR(100) NOT NULL,
  address      TEXT NOT NULL,
  postal_code  VARCHAR(20) NOT NULL,
  landline     VARCHAR(30),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT addresses_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT addresses_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT addresses_exactly_one_owner CHECK (
    (user_id IS NOT NULL AND business_id IS NULL)
    OR (user_id IS NULL AND business_id IS NOT NULL)
  ),
  CONSTRAINT addresses_province_nonempty CHECK (char_length(trim(province)) > 0),
  CONSTRAINT addresses_city_nonempty CHECK (char_length(trim(city)) > 0),
  CONSTRAINT addresses_address_nonempty CHECK (char_length(trim(address)) > 0),
  CONSTRAINT addresses_postal_code_nonempty CHECK (char_length(trim(postal_code)) > 0),
  CONSTRAINT addresses_landline_nonempty_optional CHECK (
    landline IS NULL OR char_length(trim(landline)) > 0
  )
);

CREATE INDEX idx_addresses_user_id ON addresses (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX idx_addresses_business_id ON addresses (business_id) WHERE business_id IS NOT NULL;

CREATE TRIGGER addresses_set_updated_at
  BEFORE UPDATE ON addresses
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();