Difyで生成AIアプリケーション入門　前編：生成AIアプリケーションをノーコードで開発

-- Project Name : retail
-- Date/Time    : 2024/03/22 金 22:25:09
-- Author       : 
-- RDBMS Type   : PostgreSQL
-- Application  : A5:SQL Mk-2

/*
  << 注意！！ >>
  BackupToTempTable, RestoreFromTempTable疑似命令が付加されています。
  これにより、drop table, create table 後もデータが残ります。
  この機能は一時的に $$TableName のような一時テーブルを作成します。
  この機能は A5:SQL Mk-2でのみ有効であることに注意してください。
*/

-- 売上
-- * BackupToTempTable
drop table if exists sale cascade;

-- * RestoreFromTempTable
create table sale (
  id integer not null
  , palce_id integer not null
  , product_id integer not null
  , selling_price_ID integer not null
  , business_date date not null
  , sales_at timestamp not null
  , transfer_id integer not null
  , discount_amount integer
  , sales_amount integer not null
  , constraint sale_PKC primary key (id)
) ;

-- 移動
-- * BackupToTempTable
drop table if exists transfer cascade;

-- * RestoreFromTempTable
create table transfer (
  id integer not null
  , place_id integer not null
  , product_id integer not null
  , business_date date not null
  , transfer_at timestamp
  , transfer_classification character varying not null
  , transfer_quantity integer not null
  , transfer_destination_classification character varying not null
  , transfer_destination_place_id integer
  , constraint transfer_PKC primary key (id)
) ;

-- 在庫
-- * BackupToTempTable
drop table if exists stock cascade;

-- * RestoreFromTempTable
create table stock (
  place_id integer not null
  , product_id integer not null
  , business_date date not null
  , stock_quantity integer not null
  , constraint stock_PKC primary key (place_id,product_id,business_date)
) ;

create index stock_IX1
  on stock(product_id,business_date);

-- 売価
-- * BackupToTempTable
drop table if exists selling_price cascade;

-- * RestoreFromTempTable
create table selling_price (
  id integer not null
  , product_id integer not null
  , selling_price_classification character varying not null
  , start_date date not null
  , end_date date not null
  , selling_price integer not null
  , constraint selling_price_PKC primary key (id)
) ;

create unique index selling_price_IX1
  on selling_price(product_id,selling_price_classification);

-- 場所
-- * BackupToTempTable
drop table if exists place cascade;

-- * RestoreFromTempTable
create table place (
  id integer not null
  , place_name character varying not null
  , selling_price_classification character varying not null
  , constraint place_PKC primary key (id)
) ;

-- 商品
-- * BackupToTempTable
drop table if exists product cascade;

-- * RestoreFromTempTable
create table product (
  id integer not null
  , product_name character varying not null
  , constraint product_PKC primary key (id)
) ;

comment on table sale is '売上';
comment on column sale.id is 'ID';
comment on column sale.palce_id is '場所ID';
comment on column sale.product_id is '商品ID';
comment on column sale.selling_price_ID is '売価ID';
comment on column sale.business_date is '業務日付';
comment on column sale.sales_at is '売上日時';
comment on column sale.transfer_id is '入出荷ID';
comment on column sale.discount_amount is '値引額';
comment on column sale.sales_amount is '売上金額';

comment on table transfer is '移動';
comment on column transfer.id is 'ID';
comment on column transfer.place_id is '場所ID';
comment on column transfer.product_id is '商品ID';
comment on column transfer.business_date is '業務日付';
comment on column transfer.transfer_at is '移動日時';
comment on column transfer.transfer_classification is '入出荷区分:1:入荷、2:出荷';
comment on column transfer.transfer_quantity is '移動数';
comment on column transfer.transfer_destination_classification is '移動先区分:1:場所間移動、2:売上、3:仕入';
comment on column transfer.transfer_destination_place_id is '移動先場所ID';

comment on table stock is '在庫';
comment on column stock.place_id is '場所ID';
comment on column stock.product_id is '商品ID';
comment on column stock.business_date is '業務日付';
comment on column stock.stock_quantity is '在庫数';

comment on table selling_price is '売価';
comment on column selling_price.id is 'ID';
comment on column selling_price.product_id is '商品ID';
comment on column selling_price.selling_price_classification is '売価区分:区分値： 1:プロパー, 2:B品, 3:アウトレット, 4:催事';
comment on column selling_price.start_date is '適用開始日';
comment on column selling_price.end_date is '適用終了日';
comment on column selling_price.selling_price is '売価';

comment on table place is '場所';
comment on column place.id is 'ID';
comment on column place.place_name is '場所名';
comment on column place.selling_price_classification is '売価区分:区分値： 1:プロパー, 2:B品, 3:アウトレット, 4:催事';

comment on table product is '商品';
comment on column product.id is 'ID';
comment on column product.product_name is '商品名';