-- ============================================
-- MIGRATION: Vet Verification Workflow
-- Run this in phpMyAdmin on storism1_vet-net_db
-- ============================================

-- 1. Add verification_status to vet_profiles
ALTER TABLE `baitari_vet_profiles`
  ADD COLUMN `verification_status`
    ENUM('pending_docs','docs_submitted','under_review','approved','rejected','suspended')
    NOT NULL DEFAULT 'pending_docs'
    AFTER `is_verified`,
  ADD COLUMN `rejection_reason` TEXT NULL AFTER `verification_status`,
  ADD COLUMN `reviewed_by`      INT UNSIGNED NULL AFTER `rejection_reason`,
  ADD COLUMN `reviewed_at`      DATETIME NULL AFTER `reviewed_by`,
  ADD COLUMN `submitted_at`     DATETIME NULL AFTER `reviewed_at`;

-- 2. Add documents table for vet uploads
CREATE TABLE IF NOT EXISTS `baitari_vet_documents` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `vet_id`      INT UNSIGNED NOT NULL,
  `doc_type`    ENUM(
    'national_id',
    'diploma',
    'license',
    'professional_card',
    'other'
  ) NOT NULL,
  `doc_label`   VARCHAR(150) NOT NULL,
  `file_path`   VARCHAR(500) NOT NULL,
  `file_name`   VARCHAR(255) NOT NULL,
  `file_size`   INT UNSIGNED,
  `mime_type`   VARCHAR(100),
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `is_verified` TINYINT(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`vet_id`) REFERENCES `baitari_users`(`id`) ON DELETE CASCADE,
  INDEX idx_vet (`vet_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Add vet_id to notifications (already has user_id, but helpful for admin queries)
-- Already covered by baitari_notifications.user_id

-- 4. Update existing vet records to approved if already is_verified=1
UPDATE `baitari_vet_profiles`
SET `verification_status` = 'approved'
WHERE `is_verified` = 1;

-- 5. Update existing unverified vets to pending_docs
UPDATE `baitari_vet_profiles`
SET `verification_status` = 'pending_docs'
WHERE `is_verified` = 0 AND `verification_status` = 'pending_docs';
