ALTER TABLE teams
    ADD COLUMN IF NOT EXISTS parent_team_id BIGINT UNSIGNED NULL AFTER tenant_id;
SET @fk_teams_parent_sql := IF(
    @fk_teams_parent_exists = 0,
    'ALTER TABLE teams ADD CONSTRAINT fk_teams_parent FOREIGN KEY (parent_team_id) REFERENCES teams(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE fk_teams_parent_stmt FROM @fk_teams_parent_sql;
EXECUTE fk_teams_parent_stmt;
DEALLOCATE PREPARE fk_teams_parent_stmt;

ALTER TABLE plans
    ADD COLUMN IF NOT EXISTS max_teams INT UNSIGNED NOT NULL DEFAULT 5 AFTER max_users,
    ADD COLUMN IF NOT EXISTS max_custom_roles INT UNSIGNED NOT NULL DEFAULT 10 AFTER max_teams;

CREATE TABLE IF NOT EXISTS user_data_scopes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    scope_type ENUM('own', 'team', 'all') NOT NULL DEFAULT 'own',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_data_scopes (tenant_id, user_id, module_name),
    CONSTRAINT fk_user_data_scopes_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_data_scopes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS field_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'admin', 'manager', 'agent', 'viewer') NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    field_name VARCHAR(80) NOT NULL,
    visibility ENUM('visible', 'hidden') NOT NULL DEFAULT 'visible',
    editable TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_field_permissions (tenant_id, role, module_name, field_name),
    CONSTRAINT fk_field_permissions_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 permission_approvals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    requested_by BIGINT UNSIGNED NOT NULL,
    approval_type VARCHAR(60) NOT NULL,
    target_type VARCHAR(60) NOT NULL,
    target_id BIGINT UNSIGNED NULL,
    payload JSON NULL,
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    reason VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permission_approvals_tenant_status (tenant_id, status, created_at),
    CONSTRAINT fk_permission_approvals_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_approvals_requested_by FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_approvals_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permission_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_permission_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_versions_created_by 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 permission_version_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    version_id BIGINT UNSIGNED NOT NULL,
    permission_type ENUM('role', 'user', 'field', 'scope') NOT NULL,
    role VARCHAR(50) NULL,
    user_id BIGINT UNSIGNED NULL,
    module_name VARCHAR(80) NOT NULL,
    action_name VARCHAR(50) NULL,
    field_name VARCHAR(80) NULL,
    value VARCHAR(80) NULL,
    is_allowed TINYINT(1) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_permission_version_items_version FOREIGN KEY (version_id) REFERENCES permission_versions(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_version_items_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 security_policies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'admin', 'manager', 'agent', 'viewer') NOT NULL,
    require_mfa TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_security_policies (tenant_id, role),
    CONSTRAINT fk_security_policies_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 login_events (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    event_type ENUM('login_success', 'login_failed', 'logout', 'session_revoked') NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_events_tenant_user (tenant_id, user_id, created_at),
    CONSTRAINT fk_login_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_login_events_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 permission_delegations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED NOT NULL,
    module_name VARCHAR(80) NOT NULL,
    action_name VARCHAR(50) NOT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_permission_delegations_active (tenant_id, to_user_id, is_active, starts_at, ends_at),
    CONSTRAINT fk_permission_delegations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_delegations_from_user FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_permission_delegations_to_user FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO security_policies (tenant_id, role, require_mfa)
SELECT t.id, 'owner', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'admin', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'manager', 0 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'agent', 0 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'viewer', 0 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE require_mfa = VALUES(require_mfa);
