
ALTER TABLE users
    ADD COLUMN external_identifier VARCHAR(190) NULL AFTER email,
    ADD COLUMN network_id BIGINT UNSIGNED NULL AFTER external_identifier,
    ADD COLUMN phone VARCHAR(40) NULL AFTER last_name;

ALTER TABLE users
    ADD CONSTRAINT fk_users_network FOREIGN KEY (network_id) REFERENCES networks(id);

CREATE INDEX idx_users_network ON users (network_id);
CREATE UNIQUE INDEX uq_users_external_identifier ON users (external_identifier);

ALTER TABLE orders
    ADD COLUMN order_number VARCHAR(50) NULL AFTER id,
    ADD COLUMN submitted_at DATETIME NULL AFTER updated_at,
    ADD COLUMN fulfilled_at DATETIME NULL AFTER submitted_at;

UPDATE orders
SET order_number = CONCAT('ORD-', LPAD(id, 6, '0'))
WHERE order_number IS NULL OR order_number = '';

ALTER TABLE orders
    MODIFY order_number VARCHAR(50) NOT NULL,
    ADD UNIQUE KEY uq_orders_order_number (order_number);

CREATE TABLE order_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT UNSIGNED NOT NULL,
    from_status VARCHAR(50) NULL,
    to_status VARCHAR(50) NOT NULL,
    comment TEXT NULL,
    created_by_user_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order_status_history_order (order_id),
    CONSTRAINT fk_order_status_history_order FOREIGN KEY (order_id) REFERENCES orders(id),
    CONSTRAINT fk_order_status_history_user FOREIGN KEY (created_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE oauth_providers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(190) NOT NULL,
    client_id VARCHAR(255) NOT NULL,
    client_secret VARCHAR(255) NOT NULL,
    authorize_url VARCHAR(255) NOT NULL,
    token_url VARCHAR(255) NOT NULL,
    userinfo_url VARCHAR(255) NOT NULL,
    scopes VARCHAR(255) NULL,
    user_id_field VARCHAR(120) NOT NULL DEFAULT 'sub',
    email_field VARCHAR(120) NOT NULL DEFAULT 'email',
    first_name_field VARCHAR(120) NOT NULL DEFAULT 'given_name',
    last_name_field VARCHAR(120) NOT NULL DEFAULT 'family_name',
    auto_link_by_email TINYINT NOT NULL DEFAULT 1,
    is_enabled TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE oauth_user_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    external_subject VARCHAR(190) NOT NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_oauth_user_link_subject (provider_id, external_subject),
    UNIQUE KEY uq_oauth_user_link_user (provider_id, user_id),
    INDEX idx_oauth_user_links_user (user_id),
    CONSTRAINT fk_oauth_user_links_provider FOREIGN KEY (provider_id) REFERENCES oauth_providers(id),
    CONSTRAINT fk_oauth_user_links_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

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

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

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

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 = '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 = '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 = '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 = 'master_admin'
  AND NOT EXISTS (
      SELECT 1
      FROM role_permissions rp
      WHERE rp.role_id = r.id AND rp.permission_id = p.id
  );
