-- =============================================================================
-- Approval action attachments (optional file per approve / reject / return)
-- Run against: dyu_vendor_payment_db
-- =============================================================================

CREATE TABLE IF NOT EXISTS payment_request_approval_attachments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payment_request_id BIGINT UNSIGNED NOT NULL,
  approval_id BIGINT UNSIGNED NOT NULL,
  approval_log_id BIGINT UNSIGNED NOT NULL,
  level_number INT UNSIGNED NOT NULL,
  approver_user_id BIGINT UNSIGNED NOT NULL,
  action ENUM('APPROVED','REJECTED','RETURNED_FOR_AMENDMENT') NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  original_file_name VARCHAR(255) NULL,
  file_path VARCHAR(600) NOT NULL,
  file_url VARCHAR(800) NULL,
  mime_type VARCHAR(100) NULL,
  file_size_bytes BIGINT UNSIGNED NULL,
  remarks TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_approval_attach_request FOREIGN KEY (payment_request_id) REFERENCES payment_requests(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_approval FOREIGN KEY (approval_id) REFERENCES payment_request_approvals(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_log FOREIGN KEY (approval_log_id) REFERENCES payment_request_approval_logs(id) ON DELETE CASCADE,
  CONSTRAINT fk_approval_attach_user FOREIGN KEY (approver_user_id) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_approval_attach_request (payment_request_id),
  INDEX idx_approval_attach_approval (approval_id),
  INDEX idx_approval_attach_log (approval_log_id),
  INDEX idx_approval_attach_level (payment_request_id, level_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Optional files uploaded by approvers when acting on a level.';