-- 图片生成模板结构，保存用户的生成配置与参考图关系。
CREATE TABLE product_image_templates (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id uuid NOT NULL REFERENCES web_users(id) ON DELETE CASCADE,
    name varchar(120) NOT NULL,
    prompt text NOT NULL DEFAULT '',
    model_id uuid REFERENCES models(id),
    aspect_ratio varchar(12) NOT NULL DEFAULT '1:1',
    resolution varchar(12) NOT NULL DEFAULT '1K',
    image_count smallint NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamptz,
    CONSTRAINT chk_product_image_template_ratio CHECK (aspect_ratio IN ('1:1', '3:4', '4:3', '9:16', '16:9')),
    CONSTRAINT chk_product_image_template_resolution CHECK (resolution IN ('1K', '2K', '4K')),
    CONSTRAINT chk_product_image_template_count CHECK (image_count BETWEEN 1 AND 4)
);

CREATE INDEX idx_product_image_templates_user_updated
    ON product_image_templates(user_id, updated_at DESC)
    WHERE deleted_at IS NULL;

-- 图片生成参考图关系，使用顺序字段稳定传递多张参考图。
CREATE TABLE product_image_references (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    template_id uuid NOT NULL REFERENCES product_image_templates(id) ON DELETE CASCADE,
    media_asset_id uuid NOT NULL REFERENCES media_assets(id),
    sort_order smallint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_product_image_reference_media UNIQUE (template_id, media_asset_id),
    CONSTRAINT uq_product_image_reference_order UNIQUE (template_id, sort_order),
    CONSTRAINT chk_product_image_reference_order CHECK (sort_order BETWEEN 1 AND 4)
);

CREATE INDEX idx_product_image_references_template
    ON product_image_references(template_id, sort_order);

COMMENT ON TABLE product_image_templates IS '用户保存的图片生成生成模板';
COMMENT ON COLUMN product_image_templates.id IS '图片生成模板主键';
COMMENT ON COLUMN product_image_templates.user_id IS '模板所属用户';
COMMENT ON COLUMN product_image_templates.name IS '模板名称';
COMMENT ON COLUMN product_image_templates.prompt IS '图片生成生成提示词';
COMMENT ON COLUMN product_image_templates.model_id IS '模板选用的图片模型';
COMMENT ON COLUMN product_image_templates.aspect_ratio IS '生成图片宽高比';
COMMENT ON COLUMN product_image_templates.resolution IS '生成图片分辨率档位';
COMMENT ON COLUMN product_image_templates.image_count IS '单次生成图片张数';
COMMENT ON COLUMN product_image_templates.created_at IS '模板创建时间';
COMMENT ON COLUMN product_image_templates.updated_at IS '模板最近更新时间';
COMMENT ON COLUMN product_image_templates.deleted_at IS '模板软删除时间';
COMMENT ON CONSTRAINT chk_product_image_template_ratio ON product_image_templates IS '限制图片生成支持的宽高比';
COMMENT ON CONSTRAINT chk_product_image_template_resolution ON product_image_templates IS '限制图片生成支持的分辨率档位';
COMMENT ON CONSTRAINT chk_product_image_template_count ON product_image_templates IS '限制单次生成一至四张图片';

COMMENT ON TABLE product_image_references IS '图片生成模板引用的参考图片';
COMMENT ON COLUMN product_image_references.id IS '参考图关系主键';
COMMENT ON COLUMN product_image_references.template_id IS '所属图片生成模板';
COMMENT ON COLUMN product_image_references.media_asset_id IS '引用的媒体文件';
COMMENT ON COLUMN product_image_references.sort_order IS '参考图传递顺序';
COMMENT ON COLUMN product_image_references.created_at IS '参考图添加时间';
COMMENT ON CONSTRAINT uq_product_image_reference_media ON product_image_references IS '同一模板不能重复引用同一媒体';
COMMENT ON CONSTRAINT uq_product_image_reference_order ON product_image_references IS '同一模板的参考图顺序不可重复';
COMMENT ON CONSTRAINT chk_product_image_reference_order ON product_image_references IS '每个模板最多保存四张参考图';