-- Schema patch — adds columns that may be missing if tables were created
-- before the full migration was applied. Safe to run multiple times.

-- tenants: add missing columns
ALTER TABLE tenants
  ADD COLUMN IF NOT EXISTS slug         VARCHAR(100)                                     AFTER name,
  ADD COLUMN IF NOT EXISTS plan         ENUM('starter','professional','enterprise')
                                        DEFAULT 'starter'                                AFTER slug,
  ADD COLUMN IF NOT EXISTS region       VARCHAR(100)                                     AFTER plan,
  ADD COLUMN IF NOT EXISTS industry     VARCHAR(100)                                     AFTER region,
  ADD COLUMN IF NOT EXISTS logo_url     TEXT                                             AFTER industry,
  ADD COLUMN IF NOT EXISTS settings     JSON                                             AFTER logo_url,
  ADD COLUMN IF NOT EXISTS status       ENUM('active','suspended','trial')
                                        DEFAULT 'trial'                                  AFTER settings,
  ADD COLUMN IF NOT EXISTS trial_ends_at DATETIME                                        AFTER status,
  ADD COLUMN IF NOT EXISTS is_active    BOOLEAN DEFAULT TRUE                             AFTER trial_ends_at,
  ADD COLUMN IF NOT EXISTS updated_at   DATETIME DEFAULT NOW() ON UPDATE NOW()           AFTER created_at;

-- Back-fill slug for any rows that have none (use id as fallback slug)
UPDATE tenants SET slug = LOWER(REPLACE(id, '-', '')) WHERE slug IS NULL OR slug = '';

-- Now enforce NOT NULL + UNIQUE on slug
ALTER TABLE tenants
  MODIFY COLUMN slug VARCHAR(100) NOT NULL,
  ADD UNIQUE INDEX IF NOT EXISTS uq_tenant_slug (slug);

-- users: add missing columns
ALTER TABLE users
  ADD COLUMN IF NOT EXISTS mfa_secret            VARCHAR(100)                           AFTER role,
  ADD COLUMN IF NOT EXISTS mfa_enabled           BOOLEAN DEFAULT FALSE                  AFTER mfa_secret,
  ADD COLUMN IF NOT EXISTS failed_login_attempts TINYINT UNSIGNED DEFAULT 0             AFTER mfa_enabled,
  ADD COLUMN IF NOT EXISTS locked_until          DATETIME                               AFTER failed_login_attempts,
  ADD COLUMN IF NOT EXISTS last_login_at         DATETIME                               AFTER locked_until,
  ADD COLUMN IF NOT EXISTS updated_at            DATETIME DEFAULT NOW() ON UPDATE NOW() AFTER created_at;

-- sessions: add missing columns
ALTER TABLE sessions
  ADD COLUMN IF NOT EXISTS tenant_id  CHAR(36)     AFTER user_id,
  ADD COLUMN IF NOT EXISTS ip_address VARCHAR(45)  AFTER token_hash,
  ADD COLUMN IF NOT EXISTS user_agent TEXT          AFTER ip_address;

-- audit_logs: create if missing
CREATE TABLE IF NOT EXISTS audit_logs (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id     CHAR(36) NOT NULL,
  user_id       CHAR(36),
  action        VARCHAR(100) NOT NULL,
  resource_type VARCHAR(100),
  resource_id   CHAR(36),
  old_values    JSON,
  new_values    JSON,
  ip_address    VARCHAR(45),
  created_at    DATETIME DEFAULT NOW(),
  INDEX idx_tenant_created (tenant_id, created_at),
  INDEX idx_resource (resource_type, resource_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
