SET NAMES utf8mb4;

ALTER TABLE users
    ADD COLUMN created_by_user_id BIGINT UNSIGNED NULL AFTER phone,
    ADD CONSTRAINT fk_users_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(id);

CREATE INDEX idx_users_created_by ON users (created_by_user_id);

CREATE TABLE user_platform_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_code VARCHAR(100) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_platform_role (user_id, role_code),
    INDEX idx_user_platform_roles_role (role_code),
    CONSTRAINT fk_user_platform_roles_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_network_memberships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    network_id BIGINT UNSIGNED NOT NULL,
    role_code VARCHAR(100) NOT NULL,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_network_role (user_id, network_id, role_code),
    INDEX idx_user_network_memberships_network (network_id),
    CONSTRAINT fk_user_network_memberships_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_user_network_memberships_network FOREIGN KEY (network_id) REFERENCES networks(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roles (code, name)
SELECT 'super_admin', 'Super-admin'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'super_admin');

INSERT INTO roles (code, name)
SELECT 'admin', 'Admin'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'admin');

INSERT INTO roles (code, name)
SELECT 'network_manager', 'Gestionnaire réseau'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'network_manager');

INSERT INTO roles (code, name)
SELECT 'agency_manager', 'Chef d''agence'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'agency_manager');

INSERT INTO roles (code, name)
SELECT 'agency_agent', 'Agent d''agence'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'agency_agent');

INSERT INTO roles (code, name)
SELECT 'independent_agent', 'Mandataire indépendant'
WHERE NOT EXISTS (SELECT 1 FROM roles WHERE code = 'independent_agent');

INSERT INTO permissions (code, name)
SELECT 'backoffice.access', 'Accéder au back-office'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'backoffice.access');

INSERT INTO permissions (code, name)
SELECT 'admins.manage', 'Gérer les comptes admins'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'admins.manage');

INSERT INTO permissions (code, name)
SELECT 'catalog.master.manage', 'Gérer le catalogue maître'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'catalog.master.manage');

INSERT INTO permissions (code, name)
SELECT 'catalog.local.manage', 'Gérer le catalogue réseau / boutique'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'catalog.local.manage');

INSERT INTO permissions (code, name)
SELECT 'pricing.master.manage', 'Gérer les tarifs maître'
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE code = 'pricing.master.manage');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'backoffice.access'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'admins.manage'
WHERE r.code = 'super_admin'
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'shops.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'users.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'catalog.master.manage'
WHERE r.code IN ('super_admin', 'admin', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'catalog.local.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'pricing.master.manage'
WHERE r.code IN ('super_admin', 'admin', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'credits.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'orders.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
INNER JOIN permissions p ON p.code = 'sso.manage'
WHERE r.code IN ('super_admin', 'admin', 'network_manager', 'master_admin')
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );

INSERT INTO user_platform_roles (user_id, role_code, is_active)
SELECT DISTINCT usm.user_id, 'super_admin', 1
FROM user_shop_memberships usm
WHERE usm.role_code = 'master_admin'
  AND NOT EXISTS (
      SELECT 1
      FROM user_platform_roles upr
      WHERE upr.user_id = usm.user_id
        AND upr.role_code = 'super_admin'
  );
