-- CostusWorx © 2026 — Feature 1: Admin Logon
-- Run FIRST — extends role ENUM and allows NULL tenant_id for super-admin users.

SET NAMES utf8mb4;

-- 1. Drop existing FK so we can relax the tenant_id column
ALTER TABLE users DROP FOREIGN KEY IF EXISTS users_ibfk_1;
ALTER TABLE users DROP FOREIGN KEY IF EXISTS fk_users_tenant;

-- 2. Allow NULL on tenant_id (superadmin rows have no tenant)
ALTER TABLE users MODIFY COLUMN tenant_id INT UNSIGNED DEFAULT NULL;

-- 3. Extend role ENUM
ALTER TABLE users MODIFY COLUMN role
    ENUM('admin','adviser','viewer','superadmin') NOT NULL DEFAULT 'adviser';

-- 4. Re-add FK (nullable FKs are valid in InnoDB)
ALTER TABLE users
    ADD CONSTRAINT fk_users_tenant
    FOREIGN KEY (tenant_id) REFERENCES tenants(id);