89 lines
4.3 KiB
SQL
89 lines
4.3 KiB
SQL
-- 045: Align disputes & audit_logs tables with current entity schema
|
|
-- Migration 021/022 created these tables with an older schema.
|
|
-- The entities (and migration 028/029) expect the updated schema.
|
|
-- This ALTER migration reconciles the actual DB with the entity definitions.
|
|
|
|
-- ============================================================
|
|
-- 1. disputes: 021 schema → 028 entity schema
|
|
-- ============================================================
|
|
|
|
-- Drop CHECK constraint on type (changing enum values)
|
|
ALTER TABLE disputes DROP CONSTRAINT IF EXISTS disputes_type_check;
|
|
|
|
-- Drop CHECK constraint on status (changing enum values)
|
|
ALTER TABLE disputes DROP CONSTRAINT IF EXISTS disputes_status_check;
|
|
|
|
-- Rename columns: buyer_id → plaintiff_id, seller_id → defendant_id
|
|
ALTER TABLE disputes RENAME COLUMN buyer_id TO plaintiff_id;
|
|
ALTER TABLE disputes RENAME COLUMN seller_id TO defendant_id;
|
|
|
|
-- Make defendant_id nullable (entity: nullable: true)
|
|
ALTER TABLE disputes ALTER COLUMN defendant_id DROP NOT NULL;
|
|
|
|
-- Add missing columns
|
|
ALTER TABLE disputes ADD COLUMN IF NOT EXISTS amount NUMERIC(18, 2) NOT NULL DEFAULT 0;
|
|
ALTER TABLE disputes ADD COLUMN IF NOT EXISTS version INT NOT NULL DEFAULT 1;
|
|
|
|
-- Drop columns not in entity
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS coupon_id;
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS evidence;
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS chain_evidence;
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS refund_approved;
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS sla_deadline;
|
|
ALTER TABLE disputes DROP COLUMN IF EXISTS resolved_by;
|
|
|
|
-- Re-add CHECK constraints with new enum values
|
|
-- Note: entity uses TypeORM enum type, but we add a CHECK to match the values
|
|
ALTER TABLE disputes ADD CONSTRAINT disputes_type_check
|
|
CHECK (type IN ('buyer_claim', 'seller_claim', 'refund_request'));
|
|
|
|
ALTER TABLE disputes ADD CONSTRAINT disputes_status_check
|
|
CHECK (status IN ('pending', 'processing', 'resolved', 'rejected'));
|
|
|
|
-- Update existing data to match new enum values
|
|
UPDATE disputes SET type = 'buyer_claim' WHERE type = 'buyer_complaint';
|
|
UPDATE disputes SET type = 'seller_claim' WHERE type = 'seller_complaint';
|
|
UPDATE disputes SET status = 'pending' WHERE status = 'submitted';
|
|
UPDATE disputes SET status = 'processing' WHERE status IN ('evidence_collection', 'arbitration');
|
|
UPDATE disputes SET status = 'rejected' WHERE status = 'escalated';
|
|
|
|
-- Fix indexes: rename buyer/seller indexes
|
|
DROP INDEX IF EXISTS idx_disputes_buyer_id;
|
|
DROP INDEX IF EXISTS idx_disputes_seller_id;
|
|
CREATE INDEX IF NOT EXISTS idx_disputes_plaintiff_id ON disputes(plaintiff_id);
|
|
CREATE INDEX IF NOT EXISTS idx_disputes_defendant_id ON disputes(defendant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_disputes_order_id ON disputes(order_id);
|
|
CREATE INDEX IF NOT EXISTS idx_disputes_created_at ON disputes(created_at DESC);
|
|
|
|
-- ============================================================
|
|
-- 2. audit_logs: 022 schema → 029 entity schema
|
|
-- ============================================================
|
|
|
|
-- Rename columns
|
|
ALTER TABLE audit_logs RENAME COLUMN actor_id TO admin_id;
|
|
ALTER TABLE audit_logs RENAME COLUMN resource_type TO resource;
|
|
|
|
-- Change resource_id from UUID to VARCHAR(100)
|
|
ALTER TABLE audit_logs ALTER COLUMN resource_id TYPE VARCHAR(100) USING resource_id::VARCHAR;
|
|
|
|
-- Change ip_address from INET to VARCHAR(45)
|
|
ALTER TABLE audit_logs ALTER COLUMN ip_address TYPE VARCHAR(45) USING ip_address::VARCHAR;
|
|
|
|
-- Add missing columns
|
|
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS admin_name VARCHAR(200) NOT NULL DEFAULT '';
|
|
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS result VARCHAR(20) NOT NULL DEFAULT 'success';
|
|
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
|
|
ALTER TABLE audit_logs ADD COLUMN IF NOT EXISTS version INT NOT NULL DEFAULT 1;
|
|
|
|
-- Drop columns not in entity
|
|
ALTER TABLE audit_logs DROP COLUMN IF EXISTS actor_role;
|
|
ALTER TABLE audit_logs DROP COLUMN IF EXISTS user_agent;
|
|
ALTER TABLE audit_logs DROP COLUMN IF EXISTS chain_hash;
|
|
ALTER TABLE audit_logs DROP COLUMN IF EXISTS previous_hash;
|
|
|
|
-- Fix indexes
|
|
DROP INDEX IF EXISTS idx_audit_logs_actor_id;
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_admin_id ON audit_logs(admin_id);
|
|
DROP INDEX IF EXISTS idx_audit_logs_resource;
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_resource ON audit_logs(resource, resource_id);
|