-- ACTIVE MIGRATION
-- Master platform users, roles and permissions.

CREATE TABLE IF NOT EXISTS platform_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 1,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_platform_roles_role_key (role_key),
    KEY idx_platform_roles_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(150) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    permission_group VARCHAR(100) NOT NULL DEFAULT 'general',
    is_system TINYINT(1) NOT NULL DEFAULT 1,
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_platform_permissions_permission_key (permission_key),
    KEY idx_platform_permissions_group (permission_group),
    KEY idx_platform_permissions_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(100) NOT NULL DEFAULT 'platform_admin',
    status VARCHAR(50) NOT NULL DEFAULT 'active',
    last_login_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_platform_users_email (email),
    KEY idx_platform_users_role (role),
    KEY idx_platform_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    KEY idx_platform_user_roles_role_id (role_id),
    CONSTRAINT fk_platform_user_roles_user
        FOREIGN KEY (user_id) REFERENCES platform_users (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_platform_user_roles_role
        FOREIGN KEY (role_id) REFERENCES platform_roles (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    KEY idx_platform_role_permissions_permission_id (permission_id),
    CONSTRAINT fk_platform_role_permissions_role
        FOREIGN KEY (role_id) REFERENCES platform_roles (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT fk_platform_role_permissions_permission
        FOREIGN KEY (permission_id) REFERENCES platform_permissions (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO platform_roles (role_key, name, description, is_system, status)
SELECT 'super_admin', 'Super Admin', 'Tum master panel yetkilerine sahip platform yoneticisi.', 1, 'active'
WHERE NOT EXISTS (SELECT 1 FROM platform_roles WHERE role_key = 'super_admin');

INSERT INTO platform_roles (role_key, name, description, is_system, status)
SELECT 'platform_admin', 'Platform Admin', 'Tenant ve temel platform operasyonlarini yoneten kullanici.', 1, 'active'
WHERE NOT EXISTS (SELECT 1 FROM platform_roles WHERE role_key = 'platform_admin');

INSERT INTO platform_roles (role_key, name, description, is_system, status)
SELECT 'support_admin', 'Destek Admin', 'Destek ve izleme odakli sinirli platform kullanicisi.', 1, 'active'
WHERE NOT EXISTS (SELECT 1 FROM platform_roles WHERE role_key = 'support_admin');

INSERT INTO platform_permissions (permission_key, name, description, permission_group, is_system, status)
SELECT permission_key, name, description, permission_group, 1, 'active'
FROM (
    SELECT 'platform.dashboard.view' AS permission_key, 'Kontrol Paneli Goruntule' AS name, 'Master kontrol paneline erisim.' AS description, 'dashboard' AS permission_group
    UNION ALL SELECT 'platform.tenants.manage', 'Tenant Yonet', 'Isletme kayitlarini olusturma ve guncelleme.', 'tenant'
    UNION ALL SELECT 'platform.tenants.delete', 'Tenant Sil', 'Isletme ve tenant verilerini kalici silme.', 'tenant'
    UNION ALL SELECT 'platform.users.manage', 'Platform Kullanici Yonet', 'Master panel kullanicilarini yonetme.', 'platform_user'
    UNION ALL SELECT 'platform.roles.manage', 'Rol ve Yetki Yonet', 'Master panel rol ve yetkilerini yonetme.', 'platform_user'
    UNION ALL SELECT 'platform.templates.manage', 'Template Yonet', 'Master template kutuphanesini yonetme.', 'template'
    UNION ALL SELECT 'platform.packages.manage', 'Paket Yonet', 'Paket, lisans ve abonelikleri yonetme.', 'billing'
    UNION ALL SELECT 'platform.settings.manage', 'Sistem Ayarlari Yonet', 'Global sistem ayarlarini yonetme.', 'system'
    UNION ALL SELECT 'platform.monitoring.view', 'Izleme Goruntule', 'Monitoring ve telemetry ekranlarini goruntuleme.', 'monitoring'
) AS seed_permissions
WHERE NOT EXISTS (
    SELECT 1
    FROM platform_permissions existing_permissions
    WHERE existing_permissions.permission_key = seed_permissions.permission_key
);

INSERT INTO platform_role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM platform_roles roles
CROSS JOIN platform_permissions permissions
WHERE roles.role_key = 'super_admin'
AND NOT EXISTS (
    SELECT 1
    FROM platform_role_permissions existing_role_permissions
    WHERE existing_role_permissions.role_id = roles.id
    AND existing_role_permissions.permission_id = permissions.id
);

INSERT INTO platform_role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM platform_roles roles
INNER JOIN platform_permissions permissions
    ON permissions.permission_key IN ('platform.dashboard.view', 'platform.tenants.manage', 'platform.packages.manage', 'platform.monitoring.view')
WHERE roles.role_key = 'platform_admin'
AND NOT EXISTS (
    SELECT 1
    FROM platform_role_permissions existing_role_permissions
    WHERE existing_role_permissions.role_id = roles.id
    AND existing_role_permissions.permission_id = permissions.id
);

INSERT INTO platform_role_permissions (role_id, permission_id)
SELECT roles.id, permissions.id
FROM platform_roles roles
INNER JOIN platform_permissions permissions
    ON permissions.permission_key IN ('platform.dashboard.view', 'platform.monitoring.view')
WHERE roles.role_key = 'support_admin'
AND NOT EXISTS (
    SELECT 1
    FROM platform_role_permissions existing_role_permissions
    WHERE existing_role_permissions.role_id = roles.id
    AND existing_role_permissions.permission_id = permissions.id
);

INSERT INTO platform_users (name, email, password, role, status)
SELECT 'Bes Soft Admin', 'admin@bessoft.local', '$2y$10$pYibOJO85LjLBOmD/Q37S.X5FmdbtsA4Qy5RZ6F10.i18If92gQo2', 'super_admin', 'active'
WHERE NOT EXISTS (SELECT 1 FROM platform_users WHERE email = 'admin@bessoft.local');

INSERT INTO platform_user_roles (user_id, role_id)
SELECT users.id, roles.id
FROM platform_users users
INNER JOIN platform_roles roles ON roles.role_key = 'super_admin'
WHERE users.email = 'admin@bessoft.local'
AND NOT EXISTS (
    SELECT 1
    FROM platform_user_roles existing_user_roles
    WHERE existing_user_roles.user_id = users.id
    AND existing_user_roles.role_id = roles.id
);