BEGIN;

/* ---------- 1. 確保歸檔表與索引存在 ---------- */ CREATE SCHEMA IF NOT EXISTS archive;

CREATE TABLE IF NOT EXISTS archive."QcOrder_archive" (LIKE public."QcOrder" INCLUDING ALL); -- 不包含分割設定

CREATE INDEX IF NOT EXISTS "QcOrder_archive_id_idx" ON archive."QcOrder_archive"(id);

CREATE INDEX IF NOT EXISTS "QcOrder_archive_buildingName_fulltext_idx" ON archive."QcOrder_archive" USING GIN (to_tsvector('simple', "buildingName"));

/* ---------- 2. 一步「搬 + 刪」：小於上個月的資料 ---------- */ WITH moved AS ( DELETE FROM public."QcOrder" WHERE "createdAt" < date_trunc('month', now()) - INTERVAL '1 month' RETURNING * -- 把被刪的列全部回傳 ) INSERT INTO archive."QcOrder_archive" AS arc SELECT * FROM moved ON CONFLICT DO NOTHING; -- 已存在 (同 PK) 就跳過

COMMIT;