24 lines
1.5 KiB
PL/PgSQL
24 lines
1.5 KiB
PL/PgSQL
BEGIN;
|
|
|
|
ALTER TABLE ai_insight_snapshots ADD COLUMN IF NOT EXISTS period_id text;
|
|
ALTER TABLE ai_insight_snapshots ADD COLUMN IF NOT EXISTS period_type text;
|
|
ALTER TABLE ai_insight_snapshots ADD COLUMN IF NOT EXISTS period_start_at timestamptz;
|
|
ALTER TABLE ai_insight_snapshots ADD COLUMN IF NOT EXISTS period_end_at timestamptz;
|
|
ALTER TABLE ai_insight_snapshots ADD COLUMN IF NOT EXISTS period_label_fa text;
|
|
ALTER TABLE ai_insight_refresh_attempts ADD COLUMN IF NOT EXISTS period_id text;
|
|
|
|
ALTER TABLE ai_insight_snapshots DROP CONSTRAINT IF EXISTS ai_insight_snapshots_period_scope_check;
|
|
ALTER TABLE ai_insight_snapshots ADD CONSTRAINT ai_insight_snapshots_period_scope_check CHECK (
|
|
(period_id IS NULL AND period_type IS NULL AND period_start_at IS NULL AND period_end_at IS NULL AND period_label_fa IS NULL)
|
|
OR
|
|
(period_id IS NOT NULL AND period_type IN ('DAY','WEEK','JALALI_MONTH','PERSIAN_SEASON') AND period_start_at IS NOT NULL AND period_end_at IS NOT NULL AND period_label_fa IS NOT NULL AND period_end_at > period_start_at)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS ai_insight_latest_period_scope_idx
|
|
ON ai_insight_snapshots (merchant_id,terminal_source_key,module,period_id,revision DESC,created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS ai_insight_refresh_period_scope_idx
|
|
ON ai_insight_refresh_attempts (merchant_id,coalesce(terminal_source_key,''),module,coalesce(period_id,''),section_id,tehran_refresh_day,accepted_at DESC);
|
|
|
|
INSERT INTO schema_migrations(version) VALUES (10) ON CONFLICT DO NOTHING;
|
|
COMMIT;
|