backend/database/migrations/029_brands.sql

-- Meshkee CMS — product brands (per business)

CREATE TABLE brands (
  id              BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id     BIGINT NOT NULL,
  name_en         VARCHAR(255) NOT NULL,
  name_fa         VARCHAR(255),
  image_media_id  BIGINT,
  about           TEXT,
  slug            VARCHAR(255) NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT brands_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT brands_image_media_id_fkey
    FOREIGN KEY (image_media_id) REFERENCES media (id) ON DELETE SET NULL,
  CONSTRAINT brands_business_slug_unique UNIQUE (business_id, slug),
  CONSTRAINT brands_name_en_nonempty CHECK (char_length(trim(name_en)) > 0)
);

CREATE INDEX idx_brands_business_id ON brands (business_id);
CREATE INDEX idx_brands_image_media_id ON brands (image_media_id);

CREATE TRIGGER brands_set_updated_at
  BEFORE UPDATE ON brands
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

ALTER TABLE products
  ADD COLUMN brand_id BIGINT,
  ADD CONSTRAINT products_brand_id_fkey
    FOREIGN KEY (brand_id) REFERENCES brands (id) ON DELETE SET NULL;

CREATE INDEX idx_products_brand_id ON products (brand_id);

-- ---------------------------------------------------------------------------
-- permissions
-- ---------------------------------------------------------------------------
INSERT INTO permissions (name, slug, group_name, description) VALUES
  ('View brands',   'brands.read',   'brands', 'View product brands'),
  ('Create brands', 'brands.create', 'brands', 'Create product brands'),
  ('Update brands', 'brands.update', 'brands', 'Edit product brands'),
  ('Delete brands', 'brands.delete', 'brands', 'Delete product brands')
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 'brands.%'
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 ('brands.read', 'brands.create', 'brands.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 = 'brands.read'
WHERE r.slug = 'viewer'
ON CONFLICT DO NOTHING;