-- 連接到目標數(shù)據(jù)庫后執(zhí)行
CREATE EXTENSION IF NOT EXISTS moddatetime;

-- 檢查已安裝擴展
SELECT * FROM pg_extension WHERE extname = 'moddatetime';

-- 查看擴展函數(shù)
\df moddatetime()

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    modified_at TIMESTAMP  -- 這個字段將由moddatetime自動維護
);

-- 設置modified_at字段自動更新
CREATE TRIGGER update_document_modtime
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION moddatetime(modified_at);

-- 如果需要同時維護created_at和modified_at
CREATE OR REPLACE FUNCTION update_timestamps()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.created_at = NOW();
        NEW.modified_at = NOW();
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.modified_at = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_timestamps
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_timestamps();

-- 只在特定列變更時更新時間戳
CREATE OR REPLACE FUNCTION conditional_moddatetime()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.content IS DISTINCT FROM OLD.content OR NEW.title IS DISTINCT FROM OLD.title THEN
        NEW.modified_at = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_conditional_modtime
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION conditional_moddatetime();

-- 結合審計表記錄完整修改歷史
CREATE TABLE document_audit (
    audit_id BIGSERIAL PRIMARY KEY,
    operation CHAR(1) NOT NULL,
    document_id INT NOT NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    old_data JSONB,
    new_data JSONB
);

CREATE OR REPLACE FUNCTION log_document_changes()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO document_audit(operation, document_id, old_data, new_data)
        VALUES ('U', OLD.id, to_jsonb(OLD), to_jsonb(NEW));
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO document_audit(operation, document_id, old_data)
        VALUES ('D', OLD.id, to_jsonb(OLD));
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO document_audit(operation, document_id, new_data)
        VALUES ('I', NEW.id, to_jsonb(NEW));
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_document_audit
AFTER INSERT OR UPDATE OR DELETE ON documents
FOR EACH ROW
EXECUTE FUNCTION log_document_changes();

CREATE TABLE tenant_records (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    record_data JSONB NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by INT,
    updated_at TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE OR REPLACE FUNCTION update_tenant_record_meta()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.created_at = NOW();
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.updated_at = NOW();
        NEW.updated_by = current_setting('app.current_user_id')::INT;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_tenant_record_meta
BEFORE INSERT OR UPDATE ON tenant_records
FOR EACH ROW
EXECUTE FUNCTION update_tenant_record_meta();

-- 批量更新時臨時禁用觸發(fā)器
ALTER TABLE documents DISABLE TRIGGER update_document_modtime;

-- 執(zhí)行批量更新操作
UPDATE documents SET content = content || '\nUpdated' 
WHERE id BETWEEN 1000 AND 2000;

-- 手動設置修改時間并重新啟用觸發(fā)器
UPDATE documents SET modified_at = NOW() 
WHERE id BETWEEN 1000 AND 2000 AND modified_at IS NULL;

ALTER TABLE documents ENABLE TRIGGER update_document_modtime;