CREATE TABLE IF NOT EXISTS custom_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(80) NOT NULL,
    name VARCHAR(120) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_custom_roles_tenant_code (tenant_id, code),
    CONSTRAINT fk_custom_roles_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_custom_roles_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_custom_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    custom_role_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_custom_roles (tenant_id, user_id, custom_role_id),
    CONSTRAINT fk_user_custom_roles_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_custom_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_custom_roles_role FOREIGN KEY (custom_role_id) REFERENCES custom_roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS custom_role_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    custom_role_id BIGINT UNSIGNED NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    action_name VARCHAR(50) NOT NULL,
    field_name VARCHAR(80) NULL,
    is_allowed TINYINT(1) NOT NULL DEFAULT 1,
    condition_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_custom_role_permissions (custom_role_id, module_name, action_name, field_name),
    CONSTRAINT fk_custom_role_permissions_role FOREIGN KEY (custom_role_id) REFERENCES custom_roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS security_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    alert_type VARCHAR(60) NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    message VARCHAR(255) NOT NULL,
    metadata JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_security_alerts_tenant_created (tenant_id, created_at),
    CONSTRAINT fk_security_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_security_alerts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS approval_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    approval_type VARCHAR(60) NOT NULL,
    min_approvers TINYINT UNSIGNED NOT NULL DEFAULT 1,
    expires_hours SMALLINT UNSIGNED NOT NULL DEFAULT 48,
    sla_hours SMALLINT UNSIGNED NOT NULL DEFAULT 12,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_approval_rules_tenant_type (tenant_id, approval_type),
    CONSTRAINT fk_approval_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS approval_reviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    approval_id BIGINT UNSIGNED NOT NULL,
    reviewed_by BIGINT UNSIGNED NOT NULL,
    status ENUM('approved', 'rejected') NOT NULL,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_approval_reviews (approval_id, reviewed_by),
    CONSTRAINT fk_approval_reviews_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_approval_reviews_approval FOREIGN KEY (approval_id) REFERENCES permission_approvals(id) ON DELETE CASCADE,
    CONSTRAINT fk_approval_reviews_user FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE permission_approvals
    ADD COLUMN IF NOT EXISTS expires_at DATETIME NULL AFTER status,
    ADD COLUMN IF NOT EXISTS required_approvers TINYINT UNSIGNED NOT NULL DEFAULT 1 AFTER expires_at,
    ADD COLUMN IF NOT EXISTS approvals_count TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER required_approvers;

CREATE TABLE IF NOT EXISTS audit_log_signatures (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    audit_log_id BIGINT UNSIGNED NOT NULL,
    signature CHAR(64) NOT NULL,
    chain_hash CHAR(64) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_audit_signature_log (audit_log_id),
    INDEX idx_audit_signature_tenant (tenant_id, audit_log_id),
    CONSTRAINT fk_audit_log_signatures_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_audit_log_signatures_log FOREIGN KEY (audit_log_id) REFERENCES audit_logs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO approval_rules (tenant_id, approval_type, min_approvers, expires_hours, sla_hours)
SELECT t.id, 'sensitive_user_role_change', 2, 48, 8
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE min_approvers = VALUES(min_approvers), expires_hours = VALUES(expires_hours), sla_hours = VALUES(sla_hours);
