549 lines
19 KiB
SQL
549 lines
19 KiB
SQL
-- =============================================================================
|
||
-- Migration 035: core business tables baseline
|
||
-- 日期: 2026-05-12 台北
|
||
-- =============================================================================
|
||
-- 背景:
|
||
-- 歷史業務表長期依賴 SQLAlchemy Base.metadata.create_all() 起表；若新環境只跑
|
||
-- migrations/，會缺少商品、匯入、使用者、趨勢、供應商、PPT cache 等表。
|
||
--
|
||
-- 設計:
|
||
-- 1. 僅 CREATE TABLE IF NOT EXISTS / CREATE INDEX IF NOT EXISTS，不 drop、不 replace。
|
||
-- 2. 欄位對齊目前 ORM metadata，做為 migration-only 新環境的 baseline。
|
||
-- 3. 依 FK dependency 排序，確保被參照表先建立。
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS ai_generation_history (
|
||
id SERIAL NOT NULL,
|
||
generation_type VARCHAR(50) NOT NULL,
|
||
product_name VARCHAR(200),
|
||
input_keywords TEXT,
|
||
input_trend_topic VARCHAR(500),
|
||
output_content TEXT,
|
||
generation_duration FLOAT,
|
||
is_favorite BOOLEAN,
|
||
is_used BOOLEAN,
|
||
created_by VARCHAR(100),
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS ai_prompt_templates (
|
||
id SERIAL NOT NULL,
|
||
name VARCHAR(200) NOT NULL,
|
||
description TEXT,
|
||
template_type VARCHAR(50) NOT NULL,
|
||
prompt_content TEXT NOT NULL,
|
||
variables TEXT,
|
||
is_active BOOLEAN,
|
||
is_system BOOLEAN,
|
||
created_by VARCHAR(100),
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS ai_usage_tracking (
|
||
id SERIAL NOT NULL,
|
||
user_id VARCHAR(100),
|
||
session_id VARCHAR(100),
|
||
service_type VARCHAR(50) NOT NULL,
|
||
model_name VARCHAR(100),
|
||
prompt_tokens INTEGER,
|
||
completion_tokens INTEGER,
|
||
total_tokens INTEGER,
|
||
cost_usd FLOAT,
|
||
request_type VARCHAR(50),
|
||
status VARCHAR(20),
|
||
error_message TEXT,
|
||
duration_ms FLOAT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS categories (
|
||
id SERIAL NOT NULL,
|
||
name VARCHAR(50) NOT NULL,
|
||
PRIMARY KEY (id),
|
||
UNIQUE (name)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS import_config (
|
||
id SERIAL NOT NULL,
|
||
config_key VARCHAR(100) NOT NULL,
|
||
config_value TEXT,
|
||
config_type VARCHAR(50),
|
||
description VARCHAR(500),
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
UNIQUE (config_key)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS import_jobs (
|
||
id SERIAL NOT NULL,
|
||
job_type VARCHAR(50) NOT NULL,
|
||
status VARCHAR(20) NOT NULL,
|
||
drive_file_id VARCHAR(200),
|
||
drive_file_name VARCHAR(500),
|
||
drive_file_size INTEGER,
|
||
local_file_path VARCHAR(500),
|
||
progress_percent FLOAT,
|
||
current_step VARCHAR(200),
|
||
total_rows INTEGER,
|
||
processed_rows INTEGER,
|
||
success_rows INTEGER,
|
||
error_rows INTEGER,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
started_at TIMESTAMP WITHOUT TIME ZONE,
|
||
completed_at TIMESTAMP WITHOUT TIME ZONE,
|
||
error_message TEXT,
|
||
import_summary TEXT,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS monthly_summary_analysis (
|
||
id SERIAL NOT NULL,
|
||
year INTEGER NOT NULL,
|
||
month INTEGER NOT NULL,
|
||
department VARCHAR(100),
|
||
category_3c VARCHAR(100),
|
||
division VARCHAR(100),
|
||
section VARCHAR(100),
|
||
area_id VARCHAR(50),
|
||
area_name VARCHAR(100),
|
||
pm_name VARCHAR(100),
|
||
brand_name VARCHAR(200),
|
||
vendor_id INTEGER,
|
||
vendor_name VARCHAR(200),
|
||
trade_type VARCHAR(20),
|
||
unit_price FLOAT,
|
||
sales_amt_curr INTEGER,
|
||
sales_amt_prev INTEGER,
|
||
sales_amt_yoa INTEGER,
|
||
profit_amt_curr INTEGER,
|
||
profit_amt_prev INTEGER,
|
||
profit_amt_yoa INTEGER,
|
||
discount_amt_curr INTEGER,
|
||
discount_amt_prev INTEGER,
|
||
discount_amt_yoa INTEGER,
|
||
coupon_amt_curr INTEGER,
|
||
coupon_amt_prev INTEGER,
|
||
coupon_amt_yoa INTEGER,
|
||
other_mkt_curr INTEGER,
|
||
other_mkt_prev INTEGER,
|
||
other_mkt_yoa INTEGER,
|
||
spot_disc_curr INTEGER,
|
||
spot_disc_prev INTEGER,
|
||
spot_disc_yoa INTEGER,
|
||
point_disc_curr INTEGER,
|
||
point_disc_prev INTEGER,
|
||
point_disc_yoa INTEGER,
|
||
sales_vol_curr INTEGER,
|
||
sales_vol_prev INTEGER,
|
||
sales_vol_yoa INTEGER,
|
||
conv_rate FLOAT,
|
||
views_curr INTEGER,
|
||
views_prev INTEGER,
|
||
views_yoa INTEGER,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT _monthly_summary_uc UNIQUE (
|
||
year, month, department, category_3c, division, section, area_id,
|
||
pm_name, brand_name, vendor_id, trade_type
|
||
)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_year ON monthly_summary_analysis (year);
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_month ON monthly_summary_analysis (month);
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_division ON monthly_summary_analysis (division);
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_pm_name ON monthly_summary_analysis (pm_name);
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_brand_name ON monthly_summary_analysis (brand_name);
|
||
CREATE INDEX IF NOT EXISTS ix_monthly_summary_analysis_vendor_id ON monthly_summary_analysis (vendor_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS notification_templates (
|
||
id SERIAL NOT NULL,
|
||
code VARCHAR(50) NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
category VARCHAR(50) NOT NULL,
|
||
channel VARCHAR(20),
|
||
title VARCHAR(200),
|
||
body TEXT NOT NULL,
|
||
emoji_prefix VARCHAR(10),
|
||
is_active BOOLEAN,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
UNIQUE (code)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS permissions (
|
||
id SERIAL NOT NULL,
|
||
code VARCHAR(50) NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
category VARCHAR(50) NOT NULL,
|
||
description VARCHAR(200),
|
||
sort_order INTEGER,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_permissions_code ON permissions (code);
|
||
|
||
CREATE TABLE IF NOT EXISTS ppt_reports (
|
||
id SERIAL NOT NULL,
|
||
report_type VARCHAR(50) NOT NULL,
|
||
parameters TEXT,
|
||
file_path VARCHAR(500),
|
||
file_size INTEGER,
|
||
generated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
expires_at TIMESTAMP WITHOUT TIME ZONE,
|
||
cached_data TEXT,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_ppt_reports_report_type ON ppt_reports (report_type);
|
||
CREATE INDEX IF NOT EXISTS ix_ppt_reports_generated_at ON ppt_reports (generated_at);
|
||
CREATE INDEX IF NOT EXISTS ix_ppt_reports_expires_at ON ppt_reports (expires_at);
|
||
|
||
CREATE TABLE IF NOT EXISTS promo_products (
|
||
id SERIAL NOT NULL,
|
||
batch_id VARCHAR(64),
|
||
crawled_at TIMESTAMP WITHOUT TIME ZONE,
|
||
time_slot VARCHAR(20),
|
||
activity_time_text VARCHAR(100),
|
||
session_time_text VARCHAR(100),
|
||
i_code VARCHAR(50),
|
||
name VARCHAR(255),
|
||
price INTEGER,
|
||
discount_text VARCHAR(20),
|
||
url TEXT,
|
||
image_url TEXT,
|
||
previous_price INTEGER,
|
||
remain_qty INTEGER,
|
||
status_change VARCHAR(20),
|
||
page_type VARCHAR(50),
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_promo_products_batch_id ON promo_products (batch_id);
|
||
CREATE INDEX IF NOT EXISTS ix_promo_products_i_code ON promo_products (i_code);
|
||
CREATE INDEX IF NOT EXISTS ix_promo_products_page_type ON promo_products (page_type);
|
||
|
||
CREATE TABLE IF NOT EXISTS realtime_sales_monthly (
|
||
id SERIAL NOT NULL,
|
||
"日期" DATE,
|
||
"訂單編號" VARCHAR(50),
|
||
"商品ID" VARCHAR(100),
|
||
"商品編號" VARCHAR(100),
|
||
"商品名稱" TEXT,
|
||
"數量" INTEGER,
|
||
"總業績" NUMERIC(15, 2),
|
||
"總成本" NUMERIC(15, 2),
|
||
"毛利" NUMERIC(15, 2),
|
||
"退貨數量" INTEGER,
|
||
"商品單位售價" NUMERIC(15, 2),
|
||
"廠商名稱" VARCHAR(255),
|
||
"分類名稱" VARCHAR(255),
|
||
"商品館" VARCHAR(255),
|
||
"品牌名稱" VARCHAR(255),
|
||
"時間" VARCHAR(50),
|
||
"付款方式" VARCHAR(100),
|
||
"折扣活動名稱" VARCHAR(255),
|
||
"折價券折扣金額" NUMERIC(15, 2),
|
||
"折扣金額" NUMERIC(15, 2),
|
||
"滿額再折扣金額" NUMERIC(15, 2),
|
||
"分期手續費" NUMERIC(15, 2),
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_日期" ON realtime_sales_monthly ("日期");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_訂單編號" ON realtime_sales_monthly ("訂單編號");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_商品ID" ON realtime_sales_monthly ("商品ID");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_商品編號" ON realtime_sales_monthly ("商品編號");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_廠商名稱" ON realtime_sales_monthly ("廠商名稱");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_分類名稱" ON realtime_sales_monthly ("分類名稱");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_商品館" ON realtime_sales_monthly ("商品館");
|
||
CREATE INDEX IF NOT EXISTS "ix_realtime_sales_monthly_品牌名稱" ON realtime_sales_monthly ("品牌名稱");
|
||
|
||
CREATE TABLE IF NOT EXISTS trend_analysis (
|
||
id SERIAL NOT NULL,
|
||
analysis_date DATE NOT NULL,
|
||
category VARCHAR(100),
|
||
analysis_type VARCHAR(50) NOT NULL,
|
||
summary TEXT NOT NULL,
|
||
hot_keywords TEXT,
|
||
hot_topics TEXT,
|
||
consumer_insights TEXT,
|
||
marketing_suggestions TEXT,
|
||
copywriting_hints TEXT,
|
||
source_stats TEXT,
|
||
record_count INTEGER,
|
||
model_used VARCHAR(50),
|
||
generation_time FLOAT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uq_analysis UNIQUE (analysis_date, category, analysis_type)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_trend_analysis_analysis_date ON trend_analysis (analysis_date);
|
||
CREATE INDEX IF NOT EXISTS ix_trend_analysis_category ON trend_analysis (category);
|
||
|
||
CREATE TABLE IF NOT EXISTS trend_keywords (
|
||
id SERIAL NOT NULL,
|
||
keyword VARCHAR(100) NOT NULL,
|
||
keyword_type VARCHAR(50),
|
||
source VARCHAR(50) NOT NULL,
|
||
category VARCHAR(100),
|
||
mention_count INTEGER,
|
||
trend_date DATE NOT NULL,
|
||
sentiment_avg FLOAT,
|
||
related_keywords TEXT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uq_keyword_source_date UNIQUE (keyword, source, trend_date)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_trend_keywords_keyword ON trend_keywords (keyword);
|
||
CREATE INDEX IF NOT EXISTS ix_trend_keywords_category ON trend_keywords (category);
|
||
CREATE INDEX IF NOT EXISTS ix_trend_keywords_trend_date ON trend_keywords (trend_date);
|
||
CREATE INDEX IF NOT EXISTS idx_keyword_date_count ON trend_keywords (trend_date, mention_count);
|
||
|
||
CREATE TABLE IF NOT EXISTS trend_records (
|
||
id SERIAL NOT NULL,
|
||
source VARCHAR(50) NOT NULL,
|
||
source_board VARCHAR(100),
|
||
source_url VARCHAR(500),
|
||
source_id VARCHAR(100),
|
||
title VARCHAR(500) NOT NULL,
|
||
content TEXT,
|
||
author VARCHAR(100),
|
||
popularity_score INTEGER,
|
||
comment_count INTEGER,
|
||
category VARCHAR(100),
|
||
tags TEXT,
|
||
published_at TIMESTAMP WITHOUT TIME ZONE,
|
||
trend_date DATE NOT NULL,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
sentiment VARCHAR(20),
|
||
ai_summary TEXT,
|
||
relevance_score FLOAT,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uq_source_record UNIQUE (source, source_id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_trend_records_source ON trend_records (source);
|
||
CREATE INDEX IF NOT EXISTS ix_trend_records_category ON trend_records (category);
|
||
CREATE INDEX IF NOT EXISTS ix_trend_records_trend_date ON trend_records (trend_date);
|
||
CREATE INDEX IF NOT EXISTS idx_trend_source_date ON trend_records (source, trend_date);
|
||
CREATE INDEX IF NOT EXISTS idx_trend_category_date ON trend_records (category, trend_date);
|
||
CREATE INDEX IF NOT EXISTS idx_trend_popularity ON trend_records (popularity_score, trend_date);
|
||
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id SERIAL NOT NULL,
|
||
username VARCHAR(50) NOT NULL,
|
||
email VARCHAR(120),
|
||
password_hash VARCHAR(256) NOT NULL,
|
||
role VARCHAR(20),
|
||
display_name VARCHAR(100),
|
||
is_active BOOLEAN,
|
||
password_changed_at TIMESTAMP WITHOUT TIME ZONE,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
created_by INTEGER,
|
||
PRIMARY KEY (id),
|
||
UNIQUE (email),
|
||
FOREIGN KEY(created_by) REFERENCES users (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_users_username ON users (username);
|
||
CREATE INDEX IF NOT EXISTS ix_users_role ON users (role);
|
||
|
||
CREATE TABLE IF NOT EXISTS vendor_list (
|
||
id SERIAL NOT NULL,
|
||
vendor_code VARCHAR(100) NOT NULL,
|
||
vendor_name VARCHAR(200) NOT NULL,
|
||
is_active BOOLEAN NOT NULL,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_vendor_list_vendor_code ON vendor_list (vendor_code);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_list_is_active ON vendor_list (is_active);
|
||
|
||
CREATE TABLE IF NOT EXISTS vendor_stockout (
|
||
id SERIAL NOT NULL,
|
||
batch_id VARCHAR(50) NOT NULL,
|
||
import_date DATE NOT NULL,
|
||
import_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
department VARCHAR(100),
|
||
section VARCHAR(100),
|
||
pm_name VARCHAR(100),
|
||
zone_id VARCHAR(100),
|
||
zone_name VARCHAR(200),
|
||
product_code VARCHAR(100) NOT NULL,
|
||
product_name VARCHAR(500) NOT NULL,
|
||
product_spec TEXT,
|
||
borrow_transfer VARCHAR(100),
|
||
sale_price NUMERIC(10, 2),
|
||
cost_price NUMERIC(10, 2),
|
||
vendor_code VARCHAR(100) NOT NULL,
|
||
vendor_name VARCHAR(200) NOT NULL,
|
||
monthly_sales_qty INTEGER,
|
||
monthly_sales_amount NUMERIC(12, 2),
|
||
daily_avg_sales NUMERIC(10, 2),
|
||
current_stock INTEGER,
|
||
stockout_date DATE,
|
||
stockout_days INTEGER,
|
||
safe_stock_days INTEGER,
|
||
status VARCHAR(20) NOT NULL,
|
||
is_duplicate BOOLEAN,
|
||
duplicate_count INTEGER,
|
||
sent_date TIMESTAMP WITHOUT TIME ZONE,
|
||
sent_by VARCHAR(100),
|
||
error_message TEXT,
|
||
notes TEXT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_batch_id ON vendor_stockout (batch_id);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_import_date ON vendor_stockout (import_date);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_pm_name ON vendor_stockout (pm_name);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_product_code ON vendor_stockout (product_code);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_vendor_code ON vendor_stockout (vendor_code);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_vendor_name ON vendor_stockout (vendor_name);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_status ON vendor_stockout (status);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_stockout_is_duplicate ON vendor_stockout (is_duplicate);
|
||
|
||
CREATE TABLE IF NOT EXISTS web_search_cache (
|
||
id SERIAL NOT NULL,
|
||
query_hash VARCHAR(64) NOT NULL,
|
||
query VARCHAR(500) NOT NULL,
|
||
search_type VARCHAR(50),
|
||
result_json TEXT NOT NULL,
|
||
summary TEXT,
|
||
result_count INTEGER,
|
||
model_used VARCHAR(50),
|
||
generation_time FLOAT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
expires_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_web_search_cache_query_hash ON web_search_cache (query_hash);
|
||
CREATE INDEX IF NOT EXISTS ix_web_search_cache_created_at ON web_search_cache (created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_cache_query_type ON web_search_cache (query, search_type);
|
||
CREATE INDEX IF NOT EXISTS idx_cache_expires ON web_search_cache (expires_at);
|
||
|
||
CREATE TABLE IF NOT EXISTS email_send_log (
|
||
id SERIAL NOT NULL,
|
||
vendor_id INTEGER NOT NULL,
|
||
stockout_id INTEGER,
|
||
batch_id VARCHAR(50) NOT NULL,
|
||
sender_email VARCHAR(255) NOT NULL,
|
||
recipient_email VARCHAR(255) NOT NULL,
|
||
cc_emails TEXT,
|
||
bcc_emails TEXT,
|
||
subject VARCHAR(500) NOT NULL,
|
||
product_count INTEGER NOT NULL,
|
||
attachment_filename VARCHAR(255),
|
||
attachment_size INTEGER,
|
||
status VARCHAR(20) NOT NULL,
|
||
error_message TEXT,
|
||
retry_count INTEGER,
|
||
sent_at TIMESTAMP WITHOUT TIME ZONE,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(vendor_id) REFERENCES vendor_list (id),
|
||
FOREIGN KEY(stockout_id) REFERENCES vendor_stockout (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_email_send_log_vendor_id ON email_send_log (vendor_id);
|
||
CREATE INDEX IF NOT EXISTS ix_email_send_log_stockout_id ON email_send_log (stockout_id);
|
||
CREATE INDEX IF NOT EXISTS ix_email_send_log_batch_id ON email_send_log (batch_id);
|
||
CREATE INDEX IF NOT EXISTS ix_email_send_log_status ON email_send_log (status);
|
||
|
||
CREATE TABLE IF NOT EXISTS login_history (
|
||
id SERIAL NOT NULL,
|
||
user_id INTEGER,
|
||
username_attempted VARCHAR(50),
|
||
login_time TIMESTAMP WITHOUT TIME ZONE,
|
||
ip_address VARCHAR(45),
|
||
user_agent VARCHAR(256),
|
||
status VARCHAR(20),
|
||
failure_reason VARCHAR(100),
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(user_id) REFERENCES users (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_login_history_login_time ON login_history (login_time);
|
||
CREATE INDEX IF NOT EXISTS ix_login_history_status ON login_history (status);
|
||
|
||
CREATE TABLE IF NOT EXISTS products (
|
||
id SERIAL NOT NULL,
|
||
i_code VARCHAR(50) NOT NULL,
|
||
name VARCHAR(255) NOT NULL,
|
||
url VARCHAR(500),
|
||
image_url TEXT,
|
||
category VARCHAR(100),
|
||
status VARCHAR(20),
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE,
|
||
category_id INTEGER,
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(category_id) REFERENCES categories (id)
|
||
);
|
||
|
||
CREATE UNIQUE INDEX IF NOT EXISTS ix_products_i_code ON products (i_code);
|
||
|
||
CREATE TABLE IF NOT EXISTS user_permissions (
|
||
id SERIAL NOT NULL,
|
||
user_id INTEGER NOT NULL,
|
||
permission_code VARCHAR(50) NOT NULL,
|
||
granted_by INTEGER,
|
||
granted_at TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
CONSTRAINT uq_user_permission UNIQUE (user_id, permission_code),
|
||
FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE,
|
||
FOREIGN KEY(granted_by) REFERENCES users (id) ON DELETE SET NULL
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_user_permissions_user_id ON user_permissions (user_id);
|
||
CREATE INDEX IF NOT EXISTS ix_user_permissions_permission_code ON user_permissions (permission_code);
|
||
|
||
CREATE TABLE IF NOT EXISTS vendor_emails (
|
||
id SERIAL NOT NULL,
|
||
vendor_id INTEGER NOT NULL,
|
||
email VARCHAR(255) NOT NULL,
|
||
contact_name VARCHAR(100),
|
||
email_type VARCHAR(20) NOT NULL,
|
||
is_active BOOLEAN NOT NULL,
|
||
notes TEXT,
|
||
created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
updated_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(vendor_id) REFERENCES vendor_list (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_emails_vendor_id ON vendor_emails (vendor_id);
|
||
CREATE INDEX IF NOT EXISTS ix_vendor_emails_is_active ON vendor_emails (is_active);
|
||
|
||
CREATE TABLE IF NOT EXISTS price_records (
|
||
id SERIAL NOT NULL,
|
||
product_id INTEGER NOT NULL,
|
||
price FLOAT NOT NULL,
|
||
timestamp TIMESTAMP WITHOUT TIME ZONE,
|
||
PRIMARY KEY (id),
|
||
FOREIGN KEY(product_id) REFERENCES products (id)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS ix_price_records_timestamp ON price_records (timestamp);
|
||
|
||
DO $$
|
||
BEGIN
|
||
RAISE NOTICE 'Migration 035 done: core business metadata-only tables now have migration baseline';
|
||
END $$;
|