-- ============================================================================
-- nbo_master — Migration 001: initial schema
-- ============================================================================
-- Creates the tenant registry + global auth + master audit log.
-- Nothing business-related (invoices/payroll/employees) lives here — that's
-- all per-tenant.
--
-- Apply with:  php bin/migrate.php master
-- ============================================================================

-- Tenant registry. One row per company. Source of truth for "who exists".
CREATE TABLE IF NOT EXISTS tenants (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug            VARCHAR(26)  NOT NULL UNIQUE,       -- URL label, lowercase a-z0-9-. Max 26 to fit cPanel MySQL username cap.
    company_name    VARCHAR(255) NOT NULL,
    legal_name      VARCHAR(255) DEFAULT NULL,
    tin             VARCHAR(30)  DEFAULT NULL,
    vrn             VARCHAR(30)  DEFAULT NULL,
    db_name         VARCHAR(64)  NOT NULL,              -- e.g. nbo_t_acme
    db_user         VARCHAR(32)  NOT NULL,              -- e.g. nbo_u_acme
    db_pass_enc     VARBINARY(512) NOT NULL,            -- AES-256-GCM with APP_SECRET
    uploads_dir     VARCHAR(255) NOT NULL,              -- /home/nbo/tenants/<slug>/uploads
    status          ENUM('migrating','active','suspended','archived') NOT NULL DEFAULT 'migrating',
    schema_version  VARCHAR(20)  NOT NULL DEFAULT '0',  -- highest tenant migration applied
    whmcs_enabled   TINYINT(1)   NOT NULL DEFAULT 0,    -- only Netpoa Ltd = 1
    whmcs_db_host   VARCHAR(120) DEFAULT NULL,
    whmcs_db_name   VARCHAR(120) DEFAULT NULL,
    whmcs_db_user   VARCHAR(120) DEFAULT NULL,
    whmcs_db_pass_enc VARBINARY(512) DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    activated_at    DATETIME     DEFAULT NULL,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global users: one row per PERSON regardless of how many tenants they access.
-- Password lives here only. Per-tenant role lives in <tenant>.users.
CREATE TABLE IF NOT EXISTS global_users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email           VARCHAR(191) NOT NULL UNIQUE,
    name            VARCHAR(150) NOT NULL,
    password_hash   VARCHAR(255) DEFAULT NULL,          -- NULL while invited-but-not-yet-set
    is_super_admin  TINYINT(1)   NOT NULL DEFAULT 0,    -- can access admin.nbo.co.tz
    is_active       TINYINT(1)   NOT NULL DEFAULT 1,
    last_login      DATETIME     DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_gu_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which tenants a global user can access. Many-to-many.
-- Role within each tenant lives in that tenant's own `users` table, NOT here.
CREATE TABLE IF NOT EXISTS global_user_tenants (
    global_user_id  INT UNSIGNED NOT NULL,
    tenant_id       INT UNSIGNED NOT NULL,
    granted_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    granted_by      INT UNSIGNED DEFAULT NULL,          -- global_users.id of the grantor
    PRIMARY KEY (global_user_id, tenant_id),
    INDEX idx_gut_tenant (tenant_id),
    FOREIGN KEY (global_user_id) REFERENCES global_users(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id)      REFERENCES tenants(id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Master-level audit log. Tenant-scoped actions go in <tenant>.audit_log.
CREATE TABLE IF NOT EXISTS master_audit_log (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id   INT UNSIGNED DEFAULT NULL,          -- global_users.id; NULL for system actions
    action          VARCHAR(64)  NOT NULL,              -- 'tenant.create','tenant.suspend','user.grant','user.revoke'
    target_type     VARCHAR(32)  NOT NULL,              -- 'tenant','global_user','grant'
    target_id       INT UNSIGNED DEFAULT NULL,
    ip_address      VARCHAR(45)  DEFAULT NULL,
    payload_json    JSON         DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_mal_action (action),
    INDEX idx_mal_target (target_type, target_id),
    INDEX idx_mal_actor  (actor_user_id),
    INDEX idx_mal_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Password reset tokens for global_users. One row per reset request.
CREATE TABLE IF NOT EXISTS password_resets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    global_user_id  INT UNSIGNED NOT NULL,
    token_hash      VARCHAR(80)  NOT NULL,
    expires_at      DATETIME     NOT NULL,
    used_at         DATETIME     DEFAULT NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_pwr_user  (global_user_id),
    INDEX idx_pwr_token (token_hash),
    FOREIGN KEY (global_user_id) REFERENCES global_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration-runner bookkeeping for the master stream.
CREATE TABLE IF NOT EXISTS schema_migrations_master (
    version    VARCHAR(20) PRIMARY KEY,
    applied_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    checksum   CHAR(64)    NOT NULL                     -- sha256 of the migration file at apply time
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;