-- =====================================================================
-- Migration: Appeals + AppealEvidence
-- Date: 2026-08-22
-- Description: Adds the dispute/appeal tables used by the Order <-> Appeal
--              workflow. Appeal rows are created when an order enters the
--              DISPUTED state and are resolved by a Super Admin.
--
-- NOTE: These tables are consumed through Prisma `$queryRaw`/`$executeRaw`
--       in AppealsService (no Prisma client model regeneration required).
-- =====================================================================

CREATE TABLE IF NOT EXISTS `Appeal` (
  `id`               VARCHAR(191) NOT NULL PRIMARY KEY,
  `order_id`         VARCHAR(191) NOT NULL,
  `trade_id`         VARCHAR(191) NULL,
  `requester_id`     VARCHAR(191) NOT NULL,
  `requester_role`   VARCHAR(20)  NOT NULL, -- BUYER | SELLER | ADMIN
  `reason`           TEXT         NOT NULL,
  `status`           VARCHAR(20)   NOT NULL DEFAULT 'OPEN', -- OPEN | REQUEST_MORE_PROOF | RESOLVED_COMPLETED | RESOLVED_REJECTED | DISMISSED
  `admin_id`         VARCHAR(191) NULL,
  `admin_action`     VARCHAR(20)  NULL,    -- REQUEST_MORE_PROOF | COMPLETE | REJECT | DISMISS
  `admin_message`    TEXT          NULL,
  `created_at`       DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`       DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  INDEX `idx_appeal_order_id`    (`order_id`),
  INDEX `idx_appeal_status`      (`status`),
  INDEX `idx_appeal_requester`   (`requester_id`),
  INDEX `idx_appeal_created_at`  (`created_at`)
);

CREATE TABLE IF NOT EXISTS `AppealEvidence` (
  `id`          VARCHAR(191) NOT NULL PRIMARY KEY,
  `appeal_id`   VARCHAR(191) NOT NULL,
  `file_id`     VARCHAR(191) NOT NULL,
  `uploaded_by` VARCHAR(191) NOT NULL,
  `description` TEXT          NULL,
  `uploaded_at` DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  INDEX `idx_evidence_appeal` (`appeal_id`),
  INDEX `idx_evidence_file`   (`file_id`),
  INDEX `idx_evidence_uploaded_by` (`uploaded_by`)
);

-- Link existing tables. If an Order/Trade FK graph is added later, these
-- constraints document intent; they are intentionally soft (no FK) so the
-- migration is re-runnable and does not fail on partial data.
CREATE INDEX idx_appeal_trade_id ON Appeal(trade_id);