-- ===========================================================================
-- Migration: platform admin (multi-farm control)
-- Date: 2026-07-27
--
-- Adds the platform-operator tier and its supporting columns:
--   users.is_platform_admin  — the operator flag
--   farms.status             — suspend/reactivate a tenant
--   sessions.acting_farm_id  — which farm an operator is impersonating
--   admin_audit_log          — trail of operator actions
-- schema.sql carries all of these for fresh installs. Idempotent; safe to re-run.
--
-- AFTER running this, promote your first operator by hand (once):
--   UPDATE users SET is_platform_admin = 1 WHERE email = 'you@example.com';
-- ===========================================================================

-- users.is_platform_admin
SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'is_platform_admin');
SET @s = IF(@c = 0, 'ALTER TABLE users ADD COLUMN is_platform_admin TINYINT(1) NOT NULL DEFAULT 0 AFTER status', 'DO 0');
PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

-- farms.status
SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'farms' AND COLUMN_NAME = 'status');
SET @s = IF(@c = 0, "ALTER TABLE farms ADD COLUMN status ENUM('active','suspended') NOT NULL DEFAULT 'active' AFTER logo_path", 'DO 0');
PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

-- sessions.acting_farm_id (+ FK)
SET @c = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sessions' AND COLUMN_NAME = 'acting_farm_id');
SET @s = IF(@c = 0, 'ALTER TABLE sessions ADD COLUMN acting_farm_id INT UNSIGNED NULL AFTER user_id', 'DO 0');
PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

SET @c = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'sessions' AND CONSTRAINT_NAME = 'fk_sessions_acting_farm');
SET @s = IF(@c = 0, 'ALTER TABLE sessions ADD CONSTRAINT fk_sessions_acting_farm FOREIGN KEY (acting_farm_id) REFERENCES farms(id) ON DELETE SET NULL', 'DO 0');
PREPARE q FROM @s; EXECUTE q; DEALLOCATE PREPARE q;

-- admin_audit_log
CREATE TABLE IF NOT EXISTS admin_audit_log (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  admin_user_id INT UNSIGNED  NULL,
  action        VARCHAR(40)   NOT NULL,
  farm_id       INT UNSIGNED  NULL,
  detail        VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_created (created_at)
) ENGINE=InnoDB;