backend/database/migrations/052_user_products.sql

-- Customer / user products (stock listings)
-- Like products, but: no variations/options; location (country/city/district);
-- categories come from main product categories via category_assignments;
-- technical data reuses category technical forms.

-- ---------------------------------------------------------------------------
-- entity type for assignments / media attachments
-- ---------------------------------------------------------------------------
ALTER TYPE media_entity_type ADD VALUE IF NOT EXISTS 'user_product';

-- ---------------------------------------------------------------------------
-- cities: add district under city (country → province → city → district)
-- ---------------------------------------------------------------------------
ALTER TYPE city_level ADD VALUE IF NOT EXISTS 'district';

CREATE OR REPLACE FUNCTION cities_validate_parent_level()
RETURNS TRIGGER AS $$
DECLARE
  parent_level city_level;
BEGIN
  IF NEW.level = 'country' THEN
    RETURN NEW;
  END IF;

  SELECT level INTO parent_level FROM cities WHERE id = NEW.parent_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'parent city not found';
  END IF;

  IF NEW.level = 'province' AND parent_level <> 'country' THEN
    RAISE EXCEPTION 'province parent must be a country';
  END IF;

  IF NEW.level = 'city' AND parent_level <> 'province' THEN
    RAISE EXCEPTION 'city parent must be a province';
  END IF;

  IF NEW.level = 'district' AND parent_level <> 'city' THEN
    RAISE EXCEPTION 'district parent must be a city';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ---------------------------------------------------------------------------
-- user_products
-- ---------------------------------------------------------------------------
CREATE TABLE user_products (
  id                BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id       BIGINT NOT NULL,
  user_id           BIGINT NOT NULL,
  title             VARCHAR(255) NOT NULL,
  slug              VARCHAR(255) NOT NULL,
  description       TEXT,
  content           JSONB NOT NULL DEFAULT '{}',
  price             NUMERIC(12, 2),
  compare_at_price  NUMERIC(12, 2),
  sku               VARCHAR(100),
  stock_quantity    INTEGER,
  status            content_status NOT NULL DEFAULT 'draft',
  featured_media_id BIGINT,
  brand_id          BIGINT,
  country_id        BIGINT NOT NULL,
  city_id           BIGINT NOT NULL,
  district_id       BIGINT,
  sort_order        INTEGER NOT NULL DEFAULT 0,
  published_at      TIMESTAMPTZ,
  metadata          JSONB NOT NULL DEFAULT '{}',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT user_products_business_slug_unique UNIQUE (business_id, slug),
  CONSTRAINT user_products_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT user_products_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT user_products_featured_media_id_fkey
    FOREIGN KEY (featured_media_id) REFERENCES media (id) ON DELETE SET NULL,
  CONSTRAINT user_products_brand_id_fkey
    FOREIGN KEY (brand_id) REFERENCES brands (id) ON DELETE SET NULL,
  CONSTRAINT user_products_country_id_fkey
    FOREIGN KEY (country_id) REFERENCES cities (id) ON DELETE RESTRICT,
  CONSTRAINT user_products_city_id_fkey
    FOREIGN KEY (city_id) REFERENCES cities (id) ON DELETE RESTRICT,
  CONSTRAINT user_products_district_id_fkey
    FOREIGN KEY (district_id) REFERENCES cities (id) ON DELETE SET NULL,
  CONSTRAINT user_products_price_non_negative CHECK (price IS NULL OR price >= 0),
  CONSTRAINT user_products_compare_price_non_negative CHECK (compare_at_price IS NULL OR compare_at_price >= 0),
  CONSTRAINT user_products_stock_non_negative CHECK (stock_quantity IS NULL OR stock_quantity >= 0)
);

CREATE INDEX idx_user_products_business_id ON user_products (business_id);
CREATE INDEX idx_user_products_user_id ON user_products (user_id);
CREATE INDEX idx_user_products_business_user ON user_products (business_id, user_id);
CREATE INDEX idx_user_products_business_status ON user_products (business_id, status);
CREATE INDEX idx_user_products_business_published ON user_products (business_id, published_at DESC);
CREATE INDEX idx_user_products_brand_id ON user_products (brand_id);
CREATE INDEX idx_user_products_country_id ON user_products (country_id);
CREATE INDEX idx_user_products_city_id ON user_products (city_id);
CREATE INDEX idx_user_products_district_id ON user_products (district_id);

CREATE TRIGGER user_products_set_updated_at
  BEFORE UPDATE ON user_products
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

-- ---------------------------------------------------------------------------
-- technical data (same shape as product technical field values)
-- ---------------------------------------------------------------------------
CREATE TABLE user_product_technical_field_values (
  id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id BIGINT NOT NULL,
  user_product_id BIGINT NOT NULL,
  field_id    BIGINT NOT NULL,
  text_value  TEXT,
  option_id   BIGINT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT user_product_technical_field_values_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT user_product_technical_field_values_user_product_id_fkey
    FOREIGN KEY (user_product_id) REFERENCES user_products (id) ON DELETE CASCADE,
  CONSTRAINT user_product_technical_field_values_field_id_fkey
    FOREIGN KEY (field_id) REFERENCES category_technical_form_fields (id) ON DELETE CASCADE,
  CONSTRAINT user_product_technical_field_values_option_id_fkey
    FOREIGN KEY (option_id) REFERENCES category_technical_form_field_options (id) ON DELETE SET NULL,
  CONSTRAINT user_product_technical_field_values_unique
    UNIQUE (user_product_id, field_id)
);

CREATE INDEX idx_user_product_technical_field_values_user_product_id
  ON user_product_technical_field_values (user_product_id);
CREATE INDEX idx_user_product_technical_field_values_business_id
  ON user_product_technical_field_values (business_id);

CREATE TRIGGER user_product_technical_field_values_set_updated_at
  BEFORE UPDATE ON user_product_technical_field_values
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

CREATE TABLE user_product_technical_field_value_options (
  field_value_id BIGINT NOT NULL,
  option_id      BIGINT NOT NULL,

  CONSTRAINT user_product_technical_field_value_options_pkey
    PRIMARY KEY (field_value_id, option_id),
  CONSTRAINT user_product_technical_field_value_options_field_value_id_fkey
    FOREIGN KEY (field_value_id) REFERENCES user_product_technical_field_values (id) ON DELETE CASCADE,
  CONSTRAINT user_product_technical_field_value_options_option_id_fkey
    FOREIGN KEY (option_id) REFERENCES category_technical_form_field_options (id) ON DELETE CASCADE
);

CREATE INDEX idx_user_product_technical_field_value_options_option_id
  ON user_product_technical_field_value_options (option_id);

-- ---------------------------------------------------------------------------
-- permissions (business dashboard moderation)
-- ---------------------------------------------------------------------------
INSERT INTO permissions (name, slug, group_name, description) VALUES
  ('View user products',   'user_products.read',   'user_products', 'View customer product listings'),
  ('Create user products', 'user_products.create', 'user_products', 'Create customer product listings'),
  ('Update user products', 'user_products.update', 'user_products', 'Edit customer product listings'),
  ('Delete user products', 'user_products.delete', 'user_products', 'Delete customer product listings')
ON CONFLICT (slug) DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug LIKE 'user_products.%'
WHERE r.slug IN ('business_owner', 'owner', 'admin')
ON CONFLICT DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug IN ('user_products.read', 'user_products.create', 'user_products.update')
WHERE r.slug = 'editor'
ON CONFLICT DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug = 'user_products.read'
WHERE r.slug = 'viewer'
ON CONFLICT DO NOTHING;