init.sql

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(180) COLLATE utf8mb4_unicode_ci NOT NULL,
  `roles` json NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ethereum_address` varchar(42) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `application`;
CREATE TABLE `application` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `api_token` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quota_id` int(11) DEFAULT NULL,
  `referer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_A45BDDC17BA2F5EB` (`api_token`),
  UNIQUE KEY `UNIQ_A45BDDC154E2C62F` (`quota_id`),
  KEY `IDX_A45BDDC1A76ED395` (`user_id`),
  CONSTRAINT `FK_A45BDDC1A76ED395` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `quota`;
CREATE TABLE `quota` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `application_id` int(11) NOT NULL,
  `increment` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_6C1C0FED3E030ACD` (`application_id`),
  CONSTRAINT `FK_6C1C0FED3E030ACD` FOREIGN KEY (`application_id`) REFERENCES `application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `quota_history`;
CREATE TABLE `quota_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `quota_id` int(11) NOT NULL,
  `access_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_95B989C354E2C62F` (`quota_id`),
  KEY `idx_access_time` (`access_time`),
  KEY `idx_quota_id` (`quota_id`),
  KEY `idx_access_time_quota_id` (`access_time`,`quota_id`),
  KEY `idx_quota_id_access_time` (`quota_id`,`access_time`),
  CONSTRAINT `FK_95B989C354E2C62F` FOREIGN KEY (`quota_id`) REFERENCES `quota` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `application`
  ADD CONSTRAINT `FK_A45BDDC154E2C62F` FOREIGN KEY (`quota_id`) REFERENCES `quota` (`id`);

-- Fixtures
-- === Insérer les utilisateurs ===
INSERT INTO `user` (`email`, `roles`, `password`, `username`, `ethereum_address`) VALUES
('alice@example.com', '["ROLE_USER"]', 'hashed_pwd_1', 'alice', '0x1111111111111111111111111111111111111111'),
('bob@example.com', '["ROLE_USER"]', 'hashed_pwd_2', 'bob', '0x2222222222222222222222222222222222222222'),
('carol@example.com', '["ROLE_ADMIN"]', 'hashed_pwd_3', 'carol', '0x3333333333333333333333333333333333333333');

-- === Insérer les applications ===
-- Utilisateur 1 : 2 applications
INSERT INTO `application` (`user_id`, `name`, `api_token`, `quota_id`, `referer`) VALUES
(1, 'App Alpha', 'token_alpha', NULL, 'https://alpha.example.com'),
(1, 'App Beta', 'token_beta', NULL, 'https://beta.example.com');

-- Utilisateur 2 : 1 application
INSERT INTO `application` (`user_id`, `name`, `api_token`, `quota_id`, `referer`) VALUES
(2, 'App Gamma', 'token_gamma', NULL, 'https://gamma.example.com');

-- Utilisateur 3 : 2 applications
INSERT INTO `application` (`user_id`, `name`, `api_token`, `quota_id`, `referer`) VALUES
(3, 'App Delta', 'token_delta', NULL, 'https://delta.example.com'),
(3, 'App Epsilon', 'token_epsilon', NULL, 'https://epsilon.example.com');

-- === Insérer les quotas ===
-- Chaque quota fait référence à une application
-- Chaque application aura son quota attribué via `quota_id`

INSERT INTO `quota` (`application_id`, `increment`) VALUES
(1, 10), -- App Alpha
(2, 15), -- App Beta
(3, 5),  -- App Gamma
(4, 20), -- App Delta
(5, 25); -- App Epsilon

-- === Mettre à jour les applications avec quota_id (relation circulaire)
UPDATE `application` SET `quota_id` = 1 WHERE `id` = 1;
UPDATE `application` SET `quota_id` = 2 WHERE `id` = 2;
UPDATE `application` SET `quota_id` = 3 WHERE `id` = 3;
UPDATE `application` SET `quota_id` = 4 WHERE `id` = 4;
UPDATE `application` SET `quota_id` = 5 WHERE `id` = 5;

-- === Historique de quotas ===
INSERT INTO `quota_history` (`quota_id`, `access_time`) VALUES
(1, NOW() - INTERVAL 28 HOUR),
(1, NOW() - INTERVAL 4 DAY),
(1, NOW() - INTERVAL 2 HOUR),
(1, NOW() - INTERVAL 19 HOUR),
(1, NOW() - INTERVAL 6 DAY),
(1, NOW() - INTERVAL 32 HOUR),
(1, NOW() - INTERVAL 1 DAY),
(1, NOW() - INTERVAL 18 HOUR),
(1, NOW() - INTERVAL 1 HOUR),
(1, NOW() - INTERVAL 8 DAY),
(1, NOW() - INTERVAL 12 HOUR),
(1, NOW()),
(2, NOW() - INTERVAL 2 HOUR),
(2, NOW() - INTERVAL 19 HOUR),
(2, NOW() - INTERVAL 48 HOUR),
(2, NOW() - INTERVAL 12 HOUR),
(2, NOW() - INTERVAL 1 HOUR),
(2, NOW() - INTERVAL 29 HOUR),
(2, NOW() - INTERVAL 4 HOUR),
(3, NOW() - INTERVAL 4 DAY),
(3, NOW() - INTERVAL 30 MINUTE),
(3, NOW() - INTERVAL 3 DAY),
(3, NOW() - INTERVAL 28 MINUTE),
(3, NOW() - INTERVAL 10 MINUTE),
(3, NOW() - INTERVAL 10 HOUR),
(3, NOW() - INTERVAL 14 HOUR),
(3, NOW() - INTERVAL 23 HOUR),
(3, NOW() - INTERVAL 13 HOUR),
(3, NOW() - INTERVAL 30 MINUTE),
(4, NOW() - INTERVAL 1 HOUR),
(4, NOW() - INTERVAL 2 DAY),
(4, NOW() - INTERVAL 13 HOUR),
(5, NOW() - INTERVAL 12 MINUTE),
(5, NOW() - INTERVAL 48 HOUR),
(5, NOW() - INTERVAL 14 HOUR),
(5, NOW() - INTERVAL 18 MINUTE),
(5, NOW() - INTERVAL 45 MINUTE);