-- System Admins: dedicated table separate from tenant users
-- Initial password: SysAdmin@GRC2024! (change immediately after first login)
-- Generate new hash: node -e "const b=require('bcrypt');b.hash('YOUR_PASS',12).then(console.log)"

CREATE TABLE IF NOT EXISTS system_admins (
  id            CHAR(36)     NOT NULL PRIMARY KEY,
  email         VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name          VARCHAR(255) NOT NULL,
  is_active     BOOLEAN      NOT NULL DEFAULT TRUE,
  mfa_secret    VARCHAR(255)          DEFAULT NULL,
  mfa_enabled   BOOLEAN      NOT NULL DEFAULT FALSE,
  created_by    CHAR(36)              DEFAULT NULL,
  last_login_at TIMESTAMP             DEFAULT NULL,
  created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS sysadmin_refresh_tokens (
  id         CHAR(36)     NOT NULL PRIMARY KEY,
  admin_id   CHAR(36)     NOT NULL,
  token_hash VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMP    NOT NULL,
  created_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (admin_id) REFERENCES system_admins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed first system admin
-- Password: SysAdmin@GRC2024!
INSERT IGNORE INTO system_admins (id, email, password_hash, name) VALUES (
  'sa000001-0000-4000-8000-000000000001',
  'sysadmin@grcplatform.local',
  '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj4oU3OLG3O.',
  'System Administrator'
);
