soil-report-ai/database/migrations/001_create_users.sql

-- ============================================================
-- Migration 001: User authentication tables
-- Run once against the cropmonitor database
-- ============================================================

-- Users table (replaces modX user management)
CREATE TABLE IF NOT EXISTS `users` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `fullname`    VARCHAR(255) NOT NULL,
    `email`       VARCHAR(255) NOT NULL,
    `password`    VARCHAR(255) NOT NULL,          -- bcrypt hash
    `company`     VARCHAR(255) DEFAULT '',
    `mobilephone` VARCHAR(50)  DEFAULT '',
    `industry`    VARCHAR(100) DEFAULT '',
    `role`        VARCHAR(100) DEFAULT '',
    `city`        VARCHAR(100) DEFAULT '',
    `state`       VARCHAR(100) DEFAULT '',
    `postcode`    VARCHAR(20)  DEFAULT '',
    `country`     VARCHAR(100) DEFAULT 'Australia',
    `active`      TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_users_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password reset tokens
CREATE TABLE IF NOT EXISTS `password_resets` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email`      VARCHAR(255) NOT NULL,
    `token`      VARCHAR(64)  NOT NULL,           -- bin2hex(random_bytes(32))
    `created_at` DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME     NOT NULL,            -- created_at + 1 hour
    PRIMARY KEY (`id`),
    KEY `idx_resets_email` (`email`),
    KEY `idx_resets_token` (`token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Link existing client_records rows to the new users table.
-- The old modx_user_id (always 1 in legacy data) is replaced by users.id.
-- No structural change needed: client_records.modx_user_id stays as the FK column;
-- just populate users first, then update client_records rows as users are created.