-- Meshkee CMS — curated store specials (e.g. special sale, best sellers)

CREATE TABLE store_specials (
  id          BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id BIGINT NOT NULL,
  title       VARCHAR(255) NOT NULL,
  sort_order  INTEGER NOT NULL DEFAULT 0,
  is_active   BOOLEAN NOT NULL DEFAULT TRUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT store_specials_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE
);

CREATE INDEX idx_store_specials_business_id ON store_specials (business_id);

CREATE TRIGGER store_specials_set_updated_at
  BEFORE UPDATE ON store_specials
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

CREATE TABLE store_special_items (
  id            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  special_id    BIGINT NOT NULL,
  store_item_id BIGINT NOT NULL,
  sort_order    INTEGER NOT NULL DEFAULT 0,

  CONSTRAINT store_special_items_special_id_fkey
    FOREIGN KEY (special_id) REFERENCES store_specials (id) ON DELETE CASCADE,
  CONSTRAINT store_special_items_store_item_id_fkey
    FOREIGN KEY (store_item_id) REFERENCES store_items (id) ON DELETE CASCADE,
  CONSTRAINT store_special_items_unique UNIQUE (special_id, store_item_id)
);

CREATE INDEX idx_store_special_items_special_id ON store_special_items (special_id);
CREATE INDEX idx_store_special_items_store_item_id ON store_special_items (store_item_id);