soil-report-ai/database/migrations/002_consultant_roles.sql

-- ============================================================
-- Migration 002: Consultant roles + client assignment
-- Run once against the cropmonitor database
-- ============================================================

-- 1. Add user_type column to users table
--    Values: 'client' (default), 'consultant', 'admin'
ALTER TABLE `users`
    ADD COLUMN `user_type` ENUM('client','consultant','admin')
        NOT NULL DEFAULT 'client'
        AFTER `role`;

-- 2. Junction table: maps consultants → the client_records they manage
CREATE TABLE IF NOT EXISTS `consultant_clients` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `consultant_id` INT UNSIGNED NOT NULL,   -- users.id  (user_type = 'consultant')
    `client_id`     INT          NOT NULL,   -- client_records.id
    `assigned_at`   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `assigned_by`   INT UNSIGNED DEFAULT NULL, -- users.id of admin who made the assignment
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_consultant_client` (`consultant_id`, `client_id`),
    KEY `idx_cc_consultant` (`consultant_id`),
    KEY `idx_cc_client`     (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;