added admin module et get enseignement

test: add tests for fixtures, mock fetch, mock db, and happy-dom

- Add comprehensive fixture shape tests.
- Expand mockFetch to support methods, status codes, and body tracking.
- Introduce getFetchCalls to inspect intercepted requests.
- Add mockDb helper for in-memory DB operations.
- Reorganize tests for clarity and coverage.
- Ensure happy-dom setup/cleanup works correctly.

bun.lock

@@ -0,0 +1,233 @@
+{
+  "lockfileVersion": 1,
+  "configVersion": 1,
+  "workspaces": {
+    "": {
+      "dependencies": {
+        "dotenv": "^17.4.0",
+        "drizzle-orm": "^0.45.2",
+        "pg": "^8.20.0",
+      },
+      "devDependencies": {
+        "@types/pg": "^8.20.0",
+        "drizzle-kit": "^0.31.10",
+        "tsx": "^4.21.0",
+      },
+    },
+  },
+  "packages": {
+    "@drizzle-team/brocli": ["@drizzle-team/brocli@0.10.2", "", {}, "sha512-z33Il7l5dKjUgGULTqBsQBQwckHh5AbIuxhdsIxDDiZAzBOrZO6q9ogcWC65kU382AfynTfgNumVcNIjuIua6w=="],
+
+    "@esbuild-kit/core-utils": ["@esbuild-kit/core-utils@3.3.2", "", { "dependencies": { "esbuild": "~0.18.20", "source-map-support": "^0.5.21" } }, "sha512-sPRAnw9CdSsRmEtnsl2WXWdyquogVpB3yZ3dgwJfe8zrOzTsV7cJvmwrKVa+0ma5BoiGJ+BoqkMvawbayKUsqQ=="],
+
+    "@esbuild-kit/esm-loader": ["@esbuild-kit/esm-loader@2.6.5", "", { "dependencies": { "@esbuild-kit/core-utils": "^3.3.2", "get-tsconfig": "^4.7.0" } }, "sha512-FxEMIkJKnodyA1OaCUoEvbYRkoZlLZ4d/eXFu9Fh8CbBBgP5EmZxrfTRyN0qpXZ4vOvqnE5YdRdcrmUUXuU+dA=="],
+
+    "@esbuild/aix-ppc64": ["@esbuild/aix-ppc64@0.25.12", "", { "os": "aix", "cpu": "ppc64" }, "sha512-Hhmwd6CInZ3dwpuGTF8fJG6yoWmsToE+vYgD4nytZVxcu1ulHpUQRAB1UJ8+N1Am3Mz4+xOByoQoSZf4D+CpkA=="],
+
+    "@esbuild/android-arm": ["@esbuild/android-arm@0.25.12", "", { "os": "android", "cpu": "arm" }, "sha512-VJ+sKvNA/GE7Ccacc9Cha7bpS8nyzVv0jdVgwNDaR4gDMC/2TTRc33Ip8qrNYUcpkOHUT5OZ0bUcNNVZQ9RLlg=="],
+
+    "@esbuild/android-arm64": ["@esbuild/android-arm64@0.25.12", "", { "os": "android", "cpu": "arm64" }, "sha512-6AAmLG7zwD1Z159jCKPvAxZd4y/VTO0VkprYy+3N2FtJ8+BQWFXU+OxARIwA46c5tdD9SsKGZ/1ocqBS/gAKHg=="],
+
+    "@esbuild/android-x64": ["@esbuild/android-x64@0.25.12", "", { "os": "android", "cpu": "x64" }, "sha512-5jbb+2hhDHx5phYR2By8GTWEzn6I9UqR11Kwf22iKbNpYrsmRB18aX/9ivc5cabcUiAT/wM+YIZ6SG9QO6a8kg=="],
+
+    "@esbuild/darwin-arm64": ["@esbuild/darwin-arm64@0.25.12", "", { "os": "darwin", "cpu": "arm64" }, "sha512-N3zl+lxHCifgIlcMUP5016ESkeQjLj/959RxxNYIthIg+CQHInujFuXeWbWMgnTo4cp5XVHqFPmpyu9J65C1Yg=="],
+
+    "@esbuild/darwin-x64": ["@esbuild/darwin-x64@0.25.12", "", { "os": "darwin", "cpu": "x64" }, "sha512-HQ9ka4Kx21qHXwtlTUVbKJOAnmG1ipXhdWTmNXiPzPfWKpXqASVcWdnf2bnL73wgjNrFXAa3yYvBSd9pzfEIpA=="],
+
+    "@esbuild/freebsd-arm64": ["@esbuild/freebsd-arm64@0.25.12", "", { "os": "freebsd", "cpu": "arm64" }, "sha512-gA0Bx759+7Jve03K1S0vkOu5Lg/85dou3EseOGUes8flVOGxbhDDh/iZaoek11Y8mtyKPGF3vP8XhnkDEAmzeg=="],
+
+    "@esbuild/freebsd-x64": ["@esbuild/freebsd-x64@0.25.12", "", { "os": "freebsd", "cpu": "x64" }, "sha512-TGbO26Yw2xsHzxtbVFGEXBFH0FRAP7gtcPE7P5yP7wGy7cXK2oO7RyOhL5NLiqTlBh47XhmIUXuGciXEqYFfBQ=="],
+
+    "@esbuild/linux-arm": ["@esbuild/linux-arm@0.25.12", "", { "os": "linux", "cpu": "arm" }, "sha512-lPDGyC1JPDou8kGcywY0YILzWlhhnRjdof3UlcoqYmS9El818LLfJJc3PXXgZHrHCAKs/Z2SeZtDJr5MrkxtOw=="],
+
+    "@esbuild/linux-arm64": ["@esbuild/linux-arm64@0.25.12", "", { "os": "linux", "cpu": "arm64" }, "sha512-8bwX7a8FghIgrupcxb4aUmYDLp8pX06rGh5HqDT7bB+8Rdells6mHvrFHHW2JAOPZUbnjUpKTLg6ECyzvas2AQ=="],
+
+    "@esbuild/linux-ia32": ["@esbuild/linux-ia32@0.25.12", "", { "os": "linux", "cpu": "ia32" }, "sha512-0y9KrdVnbMM2/vG8KfU0byhUN+EFCny9+8g202gYqSSVMonbsCfLjUO+rCci7pM0WBEtz+oK/PIwHkzxkyharA=="],
+
+    "@esbuild/linux-loong64": ["@esbuild/linux-loong64@0.25.12", "", { "os": "linux", "cpu": "none" }, "sha512-h///Lr5a9rib/v1GGqXVGzjL4TMvVTv+s1DPoxQdz7l/AYv6LDSxdIwzxkrPW438oUXiDtwM10o9PmwS/6Z0Ng=="],
+
+    "@esbuild/linux-mips64el": ["@esbuild/linux-mips64el@0.25.12", "", { "os": "linux", "cpu": "none" }, "sha512-iyRrM1Pzy9GFMDLsXn1iHUm18nhKnNMWscjmp4+hpafcZjrr2WbT//d20xaGljXDBYHqRcl8HnxbX6uaA/eGVw=="],
+
+    "@esbuild/linux-ppc64": ["@esbuild/linux-ppc64@0.25.12", "", { "os": "linux", "cpu": "ppc64" }, "sha512-9meM/lRXxMi5PSUqEXRCtVjEZBGwB7P/D4yT8UG/mwIdze2aV4Vo6U5gD3+RsoHXKkHCfSxZKzmDssVlRj1QQA=="],
+
+    "@esbuild/linux-riscv64": ["@esbuild/linux-riscv64@0.25.12", "", { "os": "linux", "cpu": "none" }, "sha512-Zr7KR4hgKUpWAwb1f3o5ygT04MzqVrGEGXGLnj15YQDJErYu/BGg+wmFlIDOdJp0PmB0lLvxFIOXZgFRrdjR0w=="],
+
+    "@esbuild/linux-s390x": ["@esbuild/linux-s390x@0.25.12", "", { "os": "linux", "cpu": "s390x" }, "sha512-MsKncOcgTNvdtiISc/jZs/Zf8d0cl/t3gYWX8J9ubBnVOwlk65UIEEvgBORTiljloIWnBzLs4qhzPkJcitIzIg=="],
+
+    "@esbuild/linux-x64": ["@esbuild/linux-x64@0.25.12", "", { "os": "linux", "cpu": "x64" }, "sha512-uqZMTLr/zR/ed4jIGnwSLkaHmPjOjJvnm6TVVitAa08SLS9Z0VM8wIRx7gWbJB5/J54YuIMInDquWyYvQLZkgw=="],
+
+    "@esbuild/netbsd-arm64": ["@esbuild/netbsd-arm64@0.25.12", "", { "os": "none", "cpu": "arm64" }, "sha512-xXwcTq4GhRM7J9A8Gv5boanHhRa/Q9KLVmcyXHCTaM4wKfIpWkdXiMog/KsnxzJ0A1+nD+zoecuzqPmCRyBGjg=="],
+
+    "@esbuild/netbsd-x64": ["@esbuild/netbsd-x64@0.25.12", "", { "os": "none", "cpu": "x64" }, "sha512-Ld5pTlzPy3YwGec4OuHh1aCVCRvOXdH8DgRjfDy/oumVovmuSzWfnSJg+VtakB9Cm0gxNO9BzWkj6mtO1FMXkQ=="],
+
+    "@esbuild/openbsd-arm64": ["@esbuild/openbsd-arm64@0.25.12", "", { "os": "openbsd", "cpu": "arm64" }, "sha512-fF96T6KsBo/pkQI950FARU9apGNTSlZGsv1jZBAlcLL1MLjLNIWPBkj5NlSz8aAzYKg+eNqknrUJ24QBybeR5A=="],
+
+    "@esbuild/openbsd-x64": ["@esbuild/openbsd-x64@0.25.12", "", { "os": "openbsd", "cpu": "x64" }, "sha512-MZyXUkZHjQxUvzK7rN8DJ3SRmrVrke8ZyRusHlP+kuwqTcfWLyqMOE3sScPPyeIXN/mDJIfGXvcMqCgYKekoQw=="],
+
+    "@esbuild/openharmony-arm64": ["@esbuild/openharmony-arm64@0.25.12", "", { "os": "none", "cpu": "arm64" }, "sha512-rm0YWsqUSRrjncSXGA7Zv78Nbnw4XL6/dzr20cyrQf7ZmRcsovpcRBdhD43Nuk3y7XIoW2OxMVvwuRvk9XdASg=="],
+
+    "@esbuild/sunos-x64": ["@esbuild/sunos-x64@0.25.12", "", { "os": "sunos", "cpu": "x64" }, "sha512-3wGSCDyuTHQUzt0nV7bocDy72r2lI33QL3gkDNGkod22EsYl04sMf0qLb8luNKTOmgF/eDEDP5BFNwoBKH441w=="],
+
+    "@esbuild/win32-arm64": ["@esbuild/win32-arm64@0.25.12", "", { "os": "win32", "cpu": "arm64" }, "sha512-rMmLrur64A7+DKlnSuwqUdRKyd3UE7oPJZmnljqEptesKM8wx9J8gx5u0+9Pq0fQQW8vqeKebwNXdfOyP+8Bsg=="],
+
+    "@esbuild/win32-ia32": ["@esbuild/win32-ia32@0.25.12", "", { "os": "win32", "cpu": "ia32" }, "sha512-HkqnmmBoCbCwxUKKNPBixiWDGCpQGVsrQfJoVGYLPT41XWF8lHuE5N6WhVia2n4o5QK5M4tYr21827fNhi4byQ=="],
+
+    "@esbuild/win32-x64": ["@esbuild/win32-x64@0.25.12", "", { "os": "win32", "cpu": "x64" }, "sha512-alJC0uCZpTFrSL0CCDjcgleBXPnCrEAhTBILpeAp7M/OFgoqtAetfBzX0xM00MUsVVPpVjlPuMbREqnZCXaTnA=="],
+
+    "@types/node": ["@types/node@25.5.0", "", { "dependencies": { "undici-types": "~7.18.0" } }, "sha512-jp2P3tQMSxWugkCUKLRPVUpGaL5MVFwF8RDuSRztfwgN1wmqJeMSbKlnEtQqU8UrhTmzEmZdu2I6v2dpp7XIxw=="],
+
+    "@types/pg": ["@types/pg@8.20.0", "", { "dependencies": { "@types/node": "*", "pg-protocol": "*", "pg-types": "^2.2.0" } }, "sha512-bEPFOaMAHTEP1EzpvHTbmwR8UsFyHSKsRisLIHVMXnpNefSbGA1bD6CVy+qKjGSqmZqNqBDV2azOBo8TgkcVow=="],
+
+    "buffer-from": ["buffer-from@1.1.2", "", {}, "sha512-E+XQCRwSbaaiChtv6k6Dwgc+bx+Bs6vuKJHHl5kox/BaKbhiXzqQOwK4cO22yElGp2OCmjwVhT3HmxgyPGnJfQ=="],
+
+    "dotenv": ["dotenv@17.4.0", "", {}, "sha512-kCKF62fwtzwYm0IGBNjRUjtJgMfGapII+FslMHIjMR5KTnwEmBmWLDRSnc3XSNP8bNy34tekgQyDT0hr7pERRQ=="],
+
+    "drizzle-kit": ["drizzle-kit@0.31.10", "", { "dependencies": { "@drizzle-team/brocli": "^0.10.2", "@esbuild-kit/esm-loader": "^2.5.5", "esbuild": "^0.25.4", "tsx": "^4.21.0" }, "bin": { "drizzle-kit": "bin.cjs" } }, "sha512-7OZcmQUrdGI+DUNNsKBn1aW8qSoKuTH7d0mYgSP8bAzdFzKoovxEFnoGQp2dVs82EOJeYycqRtciopszwUf8bw=="],
+
+    "drizzle-orm": ["drizzle-orm@0.45.2", "", { "peerDependencies": { "@aws-sdk/client-rds-data": ">=3", "@cloudflare/workers-types": ">=4", "@electric-sql/pglite": ">=0.2.0", "@libsql/client": ">=0.10.0", "@libsql/client-wasm": ">=0.10.0", "@neondatabase/serverless": ">=0.10.0", "@op-engineering/op-sqlite": ">=2", "@opentelemetry/api": "^1.4.1", "@planetscale/database": ">=1.13", "@prisma/client": "*", "@tidbcloud/serverless": "*", "@types/better-sqlite3": "*", "@types/pg": "*", "@types/sql.js": "*", "@upstash/redis": ">=1.34.7", "@vercel/postgres": ">=0.8.0", "@xata.io/client": "*", "better-sqlite3": ">=7", "bun-types": "*", "expo-sqlite": ">=14.0.0", "gel": ">=2", "knex": "*", "kysely": "*", "mysql2": ">=2", "pg": ">=8", "postgres": ">=3", "sql.js": ">=1", "sqlite3": ">=5" }, "optionalPeers": ["@aws-sdk/client-rds-data", "@cloudflare/workers-types", "@electric-sql/pglite", "@libsql/client", "@libsql/client-wasm", "@neondatabase/serverless", "@op-engineering/op-sqlite", "@opentelemetry/api", "@planetscale/database", "@prisma/client", "@tidbcloud/serverless", "@types/better-sqlite3", "@types/pg", "@types/sql.js", "@upstash/redis", "@vercel/postgres", "@xata.io/client", "better-sqlite3", "bun-types", "expo-sqlite", "gel", "knex", "kysely", "mysql2", "pg", "postgres", "sql.js", "sqlite3"] }, "sha512-kY0BSaTNYWnoDMVoyY8uxmyHjpJW1geOmBMdSSicKo9CIIWkSxMIj2rkeSR51b8KAPB7m+qysjuHme5nKP+E5Q=="],
+
+    "esbuild": ["esbuild@0.25.12", "", { "optionalDependencies": { "@esbuild/aix-ppc64": "0.25.12", "@esbuild/android-arm": "0.25.12", "@esbuild/android-arm64": "0.25.12", "@esbuild/android-x64": "0.25.12", "@esbuild/darwin-arm64": "0.25.12", "@esbuild/darwin-x64": "0.25.12", "@esbuild/freebsd-arm64": "0.25.12", "@esbuild/freebsd-x64": "0.25.12", "@esbuild/linux-arm": "0.25.12", "@esbuild/linux-arm64": "0.25.12", "@esbuild/linux-ia32": "0.25.12", "@esbuild/linux-loong64": "0.25.12", "@esbuild/linux-mips64el": "0.25.12", "@esbuild/linux-ppc64": "0.25.12", "@esbuild/linux-riscv64": "0.25.12", "@esbuild/linux-s390x": "0.25.12", "@esbuild/linux-x64": "0.25.12", "@esbuild/netbsd-arm64": "0.25.12", "@esbuild/netbsd-x64": "0.25.12", "@esbuild/openbsd-arm64": "0.25.12", "@esbuild/openbsd-x64": "0.25.12", "@esbuild/openharmony-arm64": "0.25.12", "@esbuild/sunos-x64": "0.25.12", "@esbuild/win32-arm64": "0.25.12", "@esbuild/win32-ia32": "0.25.12", "@esbuild/win32-x64": "0.25.12" }, "bin": { "esbuild": "bin/esbuild" } }, "sha512-bbPBYYrtZbkt6Os6FiTLCTFxvq4tt3JKall1vRwshA3fdVztsLAatFaZobhkBC8/BrPetoa0oksYoKXoG4ryJg=="],
+
+    "fsevents": ["fsevents@2.3.3", "", { "os": "darwin" }, "sha512-5xoDfX+fL7faATnagmWPpbFtwh/R77WmMMqqHGS65C3vvB0YHrgF+B1YmZ3441tMj5n63k0212XNoJwzlhffQw=="],
+
+    "get-tsconfig": ["get-tsconfig@4.13.7", "", { "dependencies": { "resolve-pkg-maps": "^1.0.0" } }, "sha512-7tN6rFgBlMgpBML5j8typ92BKFi2sFQvIdpAqLA2beia5avZDrMs0FLZiM5etShWq5irVyGcGMEA1jcDaK7A/Q=="],
+
+    "pg": ["pg@8.20.0", "", { "dependencies": { "pg-connection-string": "^2.12.0", "pg-pool": "^3.13.0", "pg-protocol": "^1.13.0", "pg-types": "2.2.0", "pgpass": "1.0.5" }, "optionalDependencies": { "pg-cloudflare": "^1.3.0" }, "peerDependencies": { "pg-native": ">=3.0.1" }, "optionalPeers": ["pg-native"] }, "sha512-ldhMxz2r8fl/6QkXnBD3CR9/xg694oT6DZQ2s6c/RI28OjtSOpxnPrUCGOBJ46RCUxcWdx3p6kw/xnDHjKvaRA=="],
+
+    "pg-cloudflare": ["pg-cloudflare@1.3.0", "", {}, "sha512-6lswVVSztmHiRtD6I8hw4qP/nDm1EJbKMRhf3HCYaqud7frGysPv7FYJ5noZQdhQtN2xJnimfMtvQq21pdbzyQ=="],
+
+    "pg-connection-string": ["pg-connection-string@2.12.0", "", {}, "sha512-U7qg+bpswf3Cs5xLzRqbXbQl85ng0mfSV/J0nnA31MCLgvEaAo7CIhmeyrmJpOr7o+zm0rXK+hNnT5l9RHkCkQ=="],
+
+    "pg-int8": ["pg-int8@1.0.1", "", {}, "sha512-WCtabS6t3c8SkpDBUlb1kjOs7l66xsGdKpIPZsg4wR+B3+u9UAum2odSsF9tnvxg80h4ZxLWMy4pRjOsFIqQpw=="],
+
+    "pg-pool": ["pg-pool@3.13.0", "", { "peerDependencies": { "pg": ">=8.0" } }, "sha512-gB+R+Xud1gLFuRD/QgOIgGOBE2KCQPaPwkzBBGC9oG69pHTkhQeIuejVIk3/cnDyX39av2AxomQiyPT13WKHQA=="],
+
+    "pg-protocol": ["pg-protocol@1.13.0", "", {}, "sha512-zzdvXfS6v89r6v7OcFCHfHlyG/wvry1ALxZo4LqgUoy7W9xhBDMaqOuMiF3qEV45VqsN6rdlcehHrfDtlCPc8w=="],
+
+    "pg-types": ["pg-types@2.2.0", "", { "dependencies": { "pg-int8": "1.0.1", "postgres-array": "~2.0.0", "postgres-bytea": "~1.0.0", "postgres-date": "~1.0.4", "postgres-interval": "^1.1.0" } }, "sha512-qTAAlrEsl8s4OiEQY69wDvcMIdQN6wdz5ojQiOy6YRMuynxenON0O5oCpJI6lshc6scgAY8qvJ2On/p+CXY0GA=="],
+
+    "pgpass": ["pgpass@1.0.5", "", { "dependencies": { "split2": "^4.1.0" } }, "sha512-FdW9r/jQZhSeohs1Z3sI1yxFQNFvMcnmfuj4WBMUTxOrAyLMaTcE1aAMBiTlbMNaXvBCQuVi0R7hd8udDSP7ug=="],
+
+    "postgres-array": ["postgres-array@2.0.0", "", {}, "sha512-VpZrUqU5A69eQyW2c5CA1jtLecCsN2U/bD6VilrFDWq5+5UIEVO7nazS3TEcHf1zuPYO/sqGvUvW62g86RXZuA=="],
+
+    "postgres-bytea": ["postgres-bytea@1.0.1", "", {}, "sha512-5+5HqXnsZPE65IJZSMkZtURARZelel2oXUEO8rH83VS/hxH5vv1uHquPg5wZs8yMAfdv971IU+kcPUczi7NVBQ=="],
+
+    "postgres-date": ["postgres-date@1.0.7", "", {}, "sha512-suDmjLVQg78nMK2UZ454hAG+OAW+HQPZ6n++TNDUX+L0+uUlLywnoxJKDou51Zm+zTCjrCl0Nq6J9C5hP9vK/Q=="],
+
+    "postgres-interval": ["postgres-interval@1.2.0", "", { "dependencies": { "xtend": "^4.0.0" } }, "sha512-9ZhXKM/rw350N1ovuWHbGxnGh/SNJ4cnxHiM0rxE4VN41wsg8P8zWn9hv/buK00RP4WvlOyr/RBDiptyxVbkZQ=="],
+
+    "resolve-pkg-maps": ["resolve-pkg-maps@1.0.0", "", {}, "sha512-seS2Tj26TBVOC2NIc2rOe2y2ZO7efxITtLZcGSOnHHNOQ7CkiUBfw0Iw2ck6xkIhPwLhKNLS8BO+hEpngQlqzw=="],
+
+    "source-map": ["source-map@0.6.1", "", {}, "sha512-UjgapumWlbMhkBgzT7Ykc5YXUT46F0iKu8SGXq0bcwP5dz/h0Plj6enJqjz1Zbq2l5WaqYnrVbwWOWMyF3F47g=="],
+
+    "source-map-support": ["source-map-support@0.5.21", "", { "dependencies": { "buffer-from": "^1.0.0", "source-map": "^0.6.0" } }, "sha512-uBHU3L3czsIyYXKX88fdrGovxdSCoTGDRZ6SYXtSRxLZUzHg5P/66Ht6uoUlHu9EZod+inXhKo3qQgwXUT/y1w=="],
+
+    "split2": ["split2@4.2.0", "", {}, "sha512-UcjcJOWknrNkF6PLX83qcHM6KHgVKNkV62Y8a5uYDVv9ydGQVwAHMKqHdJje1VTWpljG0WYpCDhrCdAOYH4TWg=="],
+
+    "tsx": ["tsx@4.21.0", "", { "dependencies": { "esbuild": "~0.27.0", "get-tsconfig": "^4.7.5" }, "optionalDependencies": { "fsevents": "~2.3.3" }, "bin": { "tsx": "dist/cli.mjs" } }, "sha512-5C1sg4USs1lfG0GFb2RLXsdpXqBSEhAaA/0kPL01wxzpMqLILNxIxIOKiILz+cdg/pLnOUxFYOR5yhHU666wbw=="],
+
+    "undici-types": ["undici-types@7.18.2", "", {}, "sha512-AsuCzffGHJybSaRrmr5eHr81mwJU3kjw6M+uprWvCXiNeN9SOGwQ3Jn8jb8m3Z6izVgknn1R0FTCEAP2QrLY/w=="],
+
+    "xtend": ["xtend@4.0.2", "", {}, "sha512-LKYU1iAXJXUgAXn9URjiu+MWhyUXHsvfp7mcuYm9dSUKK0/CjtrUwFAxD82/mCWbtLsGjFIad0wIsod4zrTAEQ=="],
+
+    "@esbuild-kit/core-utils/esbuild": ["esbuild@0.18.20", "", { "optionalDependencies": { "@esbuild/android-arm": "0.18.20", "@esbuild/android-arm64": "0.18.20", "@esbuild/android-x64": "0.18.20", "@esbuild/darwin-arm64": "0.18.20", "@esbuild/darwin-x64": "0.18.20", "@esbuild/freebsd-arm64": "0.18.20", "@esbuild/freebsd-x64": "0.18.20", "@esbuild/linux-arm": "0.18.20", "@esbuild/linux-arm64": "0.18.20", "@esbuild/linux-ia32": "0.18.20", "@esbuild/linux-loong64": "0.18.20", "@esbuild/linux-mips64el": "0.18.20", "@esbuild/linux-ppc64": "0.18.20", "@esbuild/linux-riscv64": "0.18.20", "@esbuild/linux-s390x": "0.18.20", "@esbuild/linux-x64": "0.18.20", "@esbuild/netbsd-x64": "0.18.20", "@esbuild/openbsd-x64": "0.18.20", "@esbuild/sunos-x64": "0.18.20", "@esbuild/win32-arm64": "0.18.20", "@esbuild/win32-ia32": "0.18.20", "@esbuild/win32-x64": "0.18.20" }, "bin": { "esbuild": "bin/esbuild" } }, "sha512-ceqxoedUrcayh7Y7ZX6NdbbDzGROiyVBgC4PriJThBKSVPWnnFHZAkfI1lJT8QFkOwH4qOS2SJkS4wvpGl8BpA=="],
+
+    "tsx/esbuild": ["esbuild@0.27.7", "", { "optionalDependencies": { "@esbuild/aix-ppc64": "0.27.7", "@esbuild/android-arm": "0.27.7", "@esbuild/android-arm64": "0.27.7", "@esbuild/android-x64": "0.27.7", "@esbuild/darwin-arm64": "0.27.7", "@esbuild/darwin-x64": "0.27.7", "@esbuild/freebsd-arm64": "0.27.7", "@esbuild/freebsd-x64": "0.27.7", "@esbuild/linux-arm": "0.27.7", "@esbuild/linux-arm64": "0.27.7", "@esbuild/linux-ia32": "0.27.7", "@esbuild/linux-loong64": "0.27.7", "@esbuild/linux-mips64el": "0.27.7", "@esbuild/linux-ppc64": "0.27.7", "@esbuild/linux-riscv64": "0.27.7", "@esbuild/linux-s390x": "0.27.7", "@esbuild/linux-x64": "0.27.7", "@esbuild/netbsd-arm64": "0.27.7", "@esbuild/netbsd-x64": "0.27.7", "@esbuild/openbsd-arm64": "0.27.7", "@esbuild/openbsd-x64": "0.27.7", "@esbuild/openharmony-arm64": "0.27.7", "@esbuild/sunos-x64": "0.27.7", "@esbuild/win32-arm64": "0.27.7", "@esbuild/win32-ia32": "0.27.7", "@esbuild/win32-x64": "0.27.7" }, "bin": { "esbuild": "bin/esbuild" } }, "sha512-IxpibTjyVnmrIQo5aqNpCgoACA/dTKLTlhMHihVHhdkxKyPO1uBBthumT0rdHmcsk9uMonIWS0m4FljWzILh3w=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/android-arm": ["@esbuild/android-arm@0.18.20", "", { "os": "android", "cpu": "arm" }, "sha512-fyi7TDI/ijKKNZTUJAQqiG5T7YjJXgnzkURqmGj13C6dCqckZBLdl4h7bkhHt/t0WP+zO9/zwroDvANaOqO5Sw=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/android-arm64": ["@esbuild/android-arm64@0.18.20", "", { "os": "android", "cpu": "arm64" }, "sha512-Nz4rJcchGDtENV0eMKUNa6L12zz2zBDXuhj/Vjh18zGqB44Bi7MBMSXjgunJgjRhCmKOjnPuZp4Mb6OKqtMHLQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/android-x64": ["@esbuild/android-x64@0.18.20", "", { "os": "android", "cpu": "x64" }, "sha512-8GDdlePJA8D6zlZYJV/jnrRAi6rOiNaCC/JclcXpB+KIuvfBN4owLtgzY2bsxnx666XjJx2kDPUmnTtR8qKQUg=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/darwin-arm64": ["@esbuild/darwin-arm64@0.18.20", "", { "os": "darwin", "cpu": "arm64" }, "sha512-bxRHW5kHU38zS2lPTPOyuyTm+S+eobPUnTNkdJEfAddYgEcll4xkT8DB9d2008DtTbl7uJag2HuE5NZAZgnNEA=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/darwin-x64": ["@esbuild/darwin-x64@0.18.20", "", { "os": "darwin", "cpu": "x64" }, "sha512-pc5gxlMDxzm513qPGbCbDukOdsGtKhfxD1zJKXjCCcU7ju50O7MeAZ8c4krSJcOIJGFR+qx21yMMVYwiQvyTyQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/freebsd-arm64": ["@esbuild/freebsd-arm64@0.18.20", "", { "os": "freebsd", "cpu": "arm64" }, "sha512-yqDQHy4QHevpMAaxhhIwYPMv1NECwOvIpGCZkECn8w2WFHXjEwrBn3CeNIYsibZ/iZEUemj++M26W3cNR5h+Tw=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/freebsd-x64": ["@esbuild/freebsd-x64@0.18.20", "", { "os": "freebsd", "cpu": "x64" }, "sha512-tgWRPPuQsd3RmBZwarGVHZQvtzfEBOreNuxEMKFcd5DaDn2PbBxfwLcj4+aenoh7ctXcbXmOQIn8HI6mCSw5MQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-arm": ["@esbuild/linux-arm@0.18.20", "", { "os": "linux", "cpu": "arm" }, "sha512-/5bHkMWnq1EgKr1V+Ybz3s1hWXok7mDFUMQ4cG10AfW3wL02PSZi5kFpYKrptDsgb2WAJIvRcDm+qIvXf/apvg=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-arm64": ["@esbuild/linux-arm64@0.18.20", "", { "os": "linux", "cpu": "arm64" }, "sha512-2YbscF+UL7SQAVIpnWvYwM+3LskyDmPhe31pE7/aoTMFKKzIc9lLbyGUpmmb8a8AixOL61sQ/mFh3jEjHYFvdA=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-ia32": ["@esbuild/linux-ia32@0.18.20", "", { "os": "linux", "cpu": "ia32" }, "sha512-P4etWwq6IsReT0E1KHU40bOnzMHoH73aXp96Fs8TIT6z9Hu8G6+0SHSw9i2isWrD2nbx2qo5yUqACgdfVGx7TA=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-loong64": ["@esbuild/linux-loong64@0.18.20", "", { "os": "linux", "cpu": "none" }, "sha512-nXW8nqBTrOpDLPgPY9uV+/1DjxoQ7DoB2N8eocyq8I9XuqJ7BiAMDMf9n1xZM9TgW0J8zrquIb/A7s3BJv7rjg=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-mips64el": ["@esbuild/linux-mips64el@0.18.20", "", { "os": "linux", "cpu": "none" }, "sha512-d5NeaXZcHp8PzYy5VnXV3VSd2D328Zb+9dEq5HE6bw6+N86JVPExrA6O68OPwobntbNJ0pzCpUFZTo3w0GyetQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-ppc64": ["@esbuild/linux-ppc64@0.18.20", "", { "os": "linux", "cpu": "ppc64" }, "sha512-WHPyeScRNcmANnLQkq6AfyXRFr5D6N2sKgkFo2FqguP44Nw2eyDlbTdZwd9GYk98DZG9QItIiTlFLHJHjxP3FA=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-riscv64": ["@esbuild/linux-riscv64@0.18.20", "", { "os": "linux", "cpu": "none" }, "sha512-WSxo6h5ecI5XH34KC7w5veNnKkju3zBRLEQNY7mv5mtBmrP/MjNBCAlsM2u5hDBlS3NGcTQpoBvRzqBcRtpq1A=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-s390x": ["@esbuild/linux-s390x@0.18.20", "", { "os": "linux", "cpu": "s390x" }, "sha512-+8231GMs3mAEth6Ja1iK0a1sQ3ohfcpzpRLH8uuc5/KVDFneH6jtAJLFGafpzpMRO6DzJ6AvXKze9LfFMrIHVQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/linux-x64": ["@esbuild/linux-x64@0.18.20", "", { "os": "linux", "cpu": "x64" }, "sha512-UYqiqemphJcNsFEskc73jQ7B9jgwjWrSayxawS6UVFZGWrAAtkzjxSqnoclCXxWtfwLdzU+vTpcNYhpn43uP1w=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/netbsd-x64": ["@esbuild/netbsd-x64@0.18.20", "", { "os": "none", "cpu": "x64" }, "sha512-iO1c++VP6xUBUmltHZoMtCUdPlnPGdBom6IrO4gyKPFFVBKioIImVooR5I83nTew5UOYrk3gIJhbZh8X44y06A=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/openbsd-x64": ["@esbuild/openbsd-x64@0.18.20", "", { "os": "openbsd", "cpu": "x64" }, "sha512-e5e4YSsuQfX4cxcygw/UCPIEP6wbIL+se3sxPdCiMbFLBWu0eiZOJ7WoD+ptCLrmjZBK1Wk7I6D/I3NglUGOxg=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/sunos-x64": ["@esbuild/sunos-x64@0.18.20", "", { "os": "sunos", "cpu": "x64" }, "sha512-kDbFRFp0YpTQVVrqUd5FTYmWo45zGaXe0X8E1G/LKFC0v8x0vWrhOWSLITcCn63lmZIxfOMXtCfti/RxN/0wnQ=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/win32-arm64": ["@esbuild/win32-arm64@0.18.20", "", { "os": "win32", "cpu": "arm64" }, "sha512-ddYFR6ItYgoaq4v4JmQQaAI5s7npztfV4Ag6NrhiaW0RrnOXqBkgwZLofVTlq1daVTQNhtI5oieTvkRPfZrePg=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/win32-ia32": ["@esbuild/win32-ia32@0.18.20", "", { "os": "win32", "cpu": "ia32" }, "sha512-Wv7QBi3ID/rROT08SABTS7eV4hX26sVduqDOTe1MvGMjNd3EjOz4b7zeexIR62GTIEKrfJXKL9LFxTYgkyeu7g=="],
+
+    "@esbuild-kit/core-utils/esbuild/@esbuild/win32-x64": ["@esbuild/win32-x64@0.18.20", "", { "os": "win32", "cpu": "x64" }, "sha512-kTdfRcSiDfQca/y9QIkng02avJ+NCaQvrMejlsB3RRv5sE9rRoeBPISaZpKxHELzRxZyLvNts1P27W3wV+8geQ=="],
+
+    "tsx/esbuild/@esbuild/aix-ppc64": ["@esbuild/aix-ppc64@0.27.7", "", { "os": "aix", "cpu": "ppc64" }, "sha512-EKX3Qwmhz1eMdEJokhALr0YiD0lhQNwDqkPYyPhiSwKrh7/4KRjQc04sZ8db+5DVVnZ1LmbNDI1uAMPEUBnQPg=="],
+
+    "tsx/esbuild/@esbuild/android-arm": ["@esbuild/android-arm@0.27.7", "", { "os": "android", "cpu": "arm" }, "sha512-jbPXvB4Yj2yBV7HUfE2KHe4GJX51QplCN1pGbYjvsyCZbQmies29EoJbkEc+vYuU5o45AfQn37vZlyXy4YJ8RQ=="],
+
+    "tsx/esbuild/@esbuild/android-arm64": ["@esbuild/android-arm64@0.27.7", "", { "os": "android", "cpu": "arm64" }, "sha512-62dPZHpIXzvChfvfLJow3q5dDtiNMkwiRzPylSCfriLvZeq0a1bWChrGx/BbUbPwOrsWKMn8idSllklzBy+dgQ=="],
+
+    "tsx/esbuild/@esbuild/android-x64": ["@esbuild/android-x64@0.27.7", "", { "os": "android", "cpu": "x64" }, "sha512-x5VpMODneVDb70PYV2VQOmIUUiBtY3D3mPBG8NxVk5CogneYhkR7MmM3yR/uMdITLrC1ml/NV1rj4bMJuy9MCg=="],
+
+    "tsx/esbuild/@esbuild/darwin-arm64": ["@esbuild/darwin-arm64@0.27.7", "", { "os": "darwin", "cpu": "arm64" }, "sha512-5lckdqeuBPlKUwvoCXIgI2D9/ABmPq3Rdp7IfL70393YgaASt7tbju3Ac+ePVi3KDH6N2RqePfHnXkaDtY9fkw=="],
+
+    "tsx/esbuild/@esbuild/darwin-x64": ["@esbuild/darwin-x64@0.27.7", "", { "os": "darwin", "cpu": "x64" }, "sha512-rYnXrKcXuT7Z+WL5K980jVFdvVKhCHhUwid+dDYQpH+qu+TefcomiMAJpIiC2EM3Rjtq0sO3StMV/+3w3MyyqQ=="],
+
+    "tsx/esbuild/@esbuild/freebsd-arm64": ["@esbuild/freebsd-arm64@0.27.7", "", { "os": "freebsd", "cpu": "arm64" }, "sha512-B48PqeCsEgOtzME2GbNM2roU29AMTuOIN91dsMO30t+Ydis3z/3Ngoj5hhnsOSSwNzS+6JppqWsuhTp6E82l2w=="],
+
+    "tsx/esbuild/@esbuild/freebsd-x64": ["@esbuild/freebsd-x64@0.27.7", "", { "os": "freebsd", "cpu": "x64" }, "sha512-jOBDK5XEjA4m5IJK3bpAQF9/Lelu/Z9ZcdhTRLf4cajlB+8VEhFFRjWgfy3M1O4rO2GQ/b2dLwCUGpiF/eATNQ=="],
+
+    "tsx/esbuild/@esbuild/linux-arm": ["@esbuild/linux-arm@0.27.7", "", { "os": "linux", "cpu": "arm" }, "sha512-RkT/YXYBTSULo3+af8Ib0ykH8u2MBh57o7q/DAs3lTJlyVQkgQvlrPTnjIzzRPQyavxtPtfg0EopvDyIt0j1rA=="],
+
+    "tsx/esbuild/@esbuild/linux-arm64": ["@esbuild/linux-arm64@0.27.7", "", { "os": "linux", "cpu": "arm64" }, "sha512-RZPHBoxXuNnPQO9rvjh5jdkRmVizktkT7TCDkDmQ0W2SwHInKCAV95GRuvdSvA7w4VMwfCjUiPwDi0ZO6Nfe9A=="],
+
+    "tsx/esbuild/@esbuild/linux-ia32": ["@esbuild/linux-ia32@0.27.7", "", { "os": "linux", "cpu": "ia32" }, "sha512-GA48aKNkyQDbd3KtkplYWT102C5sn/EZTY4XROkxONgruHPU72l+gW+FfF8tf2cFjeHaRbWpOYa/uRBz/Xq1Pg=="],
+
+    "tsx/esbuild/@esbuild/linux-loong64": ["@esbuild/linux-loong64@0.27.7", "", { "os": "linux", "cpu": "none" }, "sha512-a4POruNM2oWsD4WKvBSEKGIiWQF8fZOAsycHOt6JBpZ+JN2n2JH9WAv56SOyu9X5IqAjqSIPTaJkqN8F7XOQ5Q=="],
+
+    "tsx/esbuild/@esbuild/linux-mips64el": ["@esbuild/linux-mips64el@0.27.7", "", { "os": "linux", "cpu": "none" }, "sha512-KabT5I6StirGfIz0FMgl1I+R1H73Gp0ofL9A3nG3i/cYFJzKHhouBV5VWK1CSgKvVaG4q1RNpCTR2LuTVB3fIw=="],
+
+    "tsx/esbuild/@esbuild/linux-ppc64": ["@esbuild/linux-ppc64@0.27.7", "", { "os": "linux", "cpu": "ppc64" }, "sha512-gRsL4x6wsGHGRqhtI+ifpN/vpOFTQtnbsupUF5R5YTAg+y/lKelYR1hXbnBdzDjGbMYjVJLJTd2OFmMewAgwlQ=="],
+
+    "tsx/esbuild/@esbuild/linux-riscv64": ["@esbuild/linux-riscv64@0.27.7", "", { "os": "linux", "cpu": "none" }, "sha512-hL25LbxO1QOngGzu2U5xeXtxXcW+/GvMN3ejANqXkxZ/opySAZMrc+9LY/WyjAan41unrR3YrmtTsUpwT66InQ=="],
+
+    "tsx/esbuild/@esbuild/linux-s390x": ["@esbuild/linux-s390x@0.27.7", "", { "os": "linux", "cpu": "s390x" }, "sha512-2k8go8Ycu1Kb46vEelhu1vqEP+UeRVj2zY1pSuPdgvbd5ykAw82Lrro28vXUrRmzEsUV0NzCf54yARIK8r0fdw=="],
+
+    "tsx/esbuild/@esbuild/linux-x64": ["@esbuild/linux-x64@0.27.7", "", { "os": "linux", "cpu": "x64" }, "sha512-hzznmADPt+OmsYzw1EE33ccA+HPdIqiCRq7cQeL1Jlq2gb1+OyWBkMCrYGBJ+sxVzve2ZJEVeePbLM2iEIZSxA=="],
+
+    "tsx/esbuild/@esbuild/netbsd-arm64": ["@esbuild/netbsd-arm64@0.27.7", "", { "os": "none", "cpu": "arm64" }, "sha512-b6pqtrQdigZBwZxAn1UpazEisvwaIDvdbMbmrly7cDTMFnw/+3lVxxCTGOrkPVnsYIosJJXAsILG9XcQS+Yu6w=="],
+
+    "tsx/esbuild/@esbuild/netbsd-x64": ["@esbuild/netbsd-x64@0.27.7", "", { "os": "none", "cpu": "x64" }, "sha512-OfatkLojr6U+WN5EDYuoQhtM+1xco+/6FSzJJnuWiUw5eVcicbyK3dq5EeV/QHT1uy6GoDhGbFpprUiHUYggrw=="],
+
+    "tsx/esbuild/@esbuild/openbsd-arm64": ["@esbuild/openbsd-arm64@0.27.7", "", { "os": "openbsd", "cpu": "arm64" }, "sha512-AFuojMQTxAz75Fo8idVcqoQWEHIXFRbOc1TrVcFSgCZtQfSdc1RXgB3tjOn/krRHENUB4j00bfGjyl2mJrU37A=="],
+
+    "tsx/esbuild/@esbuild/openbsd-x64": ["@esbuild/openbsd-x64@0.27.7", "", { "os": "openbsd", "cpu": "x64" }, "sha512-+A1NJmfM8WNDv5CLVQYJ5PshuRm/4cI6WMZRg1by1GwPIQPCTs1GLEUHwiiQGT5zDdyLiRM/l1G0Pv54gvtKIg=="],
+
+    "tsx/esbuild/@esbuild/openharmony-arm64": ["@esbuild/openharmony-arm64@0.27.7", "", { "os": "none", "cpu": "arm64" }, "sha512-+KrvYb/C8zA9CU/g0sR6w2RBw7IGc5J2BPnc3dYc5VJxHCSF1yNMxTV5LQ7GuKteQXZtspjFbiuW5/dOj7H4Yw=="],
+
+    "tsx/esbuild/@esbuild/sunos-x64": ["@esbuild/sunos-x64@0.27.7", "", { "os": "sunos", "cpu": "x64" }, "sha512-ikktIhFBzQNt/QDyOL580ti9+5mL/YZeUPKU2ivGtGjdTYoqz6jObj6nOMfhASpS4GU4Q/Clh1QtxWAvcYKamA=="],
+
+    "tsx/esbuild/@esbuild/win32-arm64": ["@esbuild/win32-arm64@0.27.7", "", { "os": "win32", "cpu": "arm64" }, "sha512-7yRhbHvPqSpRUV7Q20VuDwbjW5kIMwTHpptuUzV+AA46kiPze5Z7qgt6CLCK3pWFrHeNfDd1VKgyP4O+ng17CA=="],
+
+    "tsx/esbuild/@esbuild/win32-ia32": ["@esbuild/win32-ia32@0.27.7", "", { "os": "win32", "cpu": "ia32" }, "sha512-SmwKXe6VHIyZYbBLJrhOoCJRB/Z1tckzmgTLfFYOfpMAx63BJEaL9ExI8x7v0oAO3Zh6D/Oi1gVxEYr5oUCFhw=="],
+
+    "tsx/esbuild/@esbuild/win32-x64": ["@esbuild/win32-x64@0.27.7", "", { "os": "win32", "cpu": "x64" }, "sha512-56hiAJPhwQ1R4i+21FVF7V8kSD5zZTdHcVuRFMW0hn753vVfQN8xlx4uOPT4xoGH0Z/oVATuR82AiqSTDIpaHg=="],
+  }
+}

compose.yml

@@ -1,10 +1,26 @@
 services:
   app:
-    container_name: deno_fresh_app
-    build: .
+    image: registry.docker.polytech.djalim.fr/polympr:latest
     ports:
-      - "80:80"
-      - "443:443"
+      - "8008:80"
+      - "4430:443"
     volumes:
-      - .:/app
+      - /home/kevin/PolyMPR/:/app
     command: deno run -A main.ts
+    deploy:
+      replicas: 1
+      placement:
+        constraints: [node.role == manager]
+
+  db:
+    image: postgres
+    restart: always
+    shm_size: 128mb
+    environment: 
+      POSTGRES_PASSWORD: ${POSTGRES_PASS}
+    deploy:
+      replicas: 1
+      placement:
+        constraints: [node.role == manager]
+
+

databases/db.ts

@@ -0,0 +1,14 @@
+import { drizzle } from "npm:drizzle-orm/node-postgres";
+import pg from "npm:pg";
+
+const { Pool } = pg;
+
+const pool = new Pool({
+  host: Deno.env.get("POSTGRES_HOST"),
+  port: Number(Deno.env.get("POSTGRES_PORT") ?? 5432),
+  user: Deno.env.get("POSTGRES_USER"),
+  password: Deno.env.get("POSTGRES_PASS"),
+  database: Deno.env.get("POSTGRES_DB"),
+});
+
+export const db = drizzle(pool);

databases/schema.ts

@@ -0,0 +1,32 @@
+import {
+  date,
+  integer,
+  pgTable,
+  serial,
+  text,
+} from "npm:drizzle-orm/pg-core";
+
+export const promotions = pgTable("promotions", {
+  id: serial("id").primaryKey(),
+  endyear: integer("endyear"),
+  current: integer("current"),
+});
+
+export const students = pgTable("students", {
+  userId: text("userId").primaryKey(),
+  firstName: text("firstName"),
+  lastName: text("lastName"),
+  mail: text("mail"),
+  promotionId: integer("promotionId").references(() => promotions.id),
+});
+
+export const mobility = pgTable("mobility", {
+  id: serial("id").primaryKey(),
+  studentId: text("studentId").references(() => students.userId),
+  startDate: date("startDate"),
+  endDate: date("endDate"),
+  weeksCount: integer("weeksCount"),
+  destinationCountry: text("destinationCountry"),
+  destinationName: text("destinationName"),
+  mobilityStatus: text("mobilityStatus").default("N/A"),
+});

drizzle.config.ts

@@ -0,0 +1,14 @@
+import { defineConfig } from "drizzle-kit";
+
+export default defineConfig({
+  dialect: "postgresql",
+  schema: "./databases/schema.ts",
+  out: "./databases/migrations",
+  dbCredentials: {
+    host: process.env.POSTGRES_HOST!,
+    port: Number(process.env.POSTGRES_PORT ?? 5432),
+    user: process.env.POSTGRES_USER!,
+    password: process.env.POSTGRES_PASS!,
+    database: process.env.POSTGRES_DB!,
+  },
+});

env.template

@@ -1,2 +1,8 @@
 #Local mode, set to true to access admin pages with any users
 LOCAL=false
+
+POSTGRES_HOST = db
+POSTGRES_PORT = 5432
+POSTGRES_PASS = astrongpass
+POSTGRES_USER = postgres
+POSTGRES_DB = polympr

package.json

@@ -0,0 +1,12 @@
+{
+  "dependencies": {
+    "dotenv": "^17.4.0",
+    "drizzle-orm": "^0.45.2",
+    "pg": "^8.20.0"
+  },
+  "devDependencies": {
+    "@types/pg": "^8.20.0",
+    "drizzle-kit": "^0.31.10",
+    "tsx": "^4.21.0"
+  }
+}

routes/(apps)/admin/(_islands)/ConsultMobility.tsx

@@ -0,0 +1,115 @@
+import { useEffect, useState } from "preact/hooks";
+
+interface Promotion {
+  id: number;
+  name: string;
+}
+
+interface Student {
+  id: string;
+  firstName: string;
+  lastName: string;
+  promotionId: number;
+}
+
+interface Mobility {
+  id: number;
+  studentId: string;
+  startDate: string | null;
+  endDate: string | null;
+  weeksCount: number | null;
+  destinationCountry: string | null;
+  destinationName: string | null;
+  mobilityStatus: string;
+}
+
+export default function ConsultMobility() {
+  const [data, setData] = useState<
+    | {
+      promotions?: Promotion[];
+      students?: Student[];
+      mobilities?: Mobility[];
+    }
+    | null
+  >(null);
+  const [error, setError] = useState<string | null>(null);
+
+  useEffect(() => {
+    const fetchData = async () => {
+      console.log("ConsultMobility: Fetching data from API...");
+      try {
+        const response = await fetch("/mobility/api/insert_mobility");
+        console.log("ConsultMobility: API response status:", response.status);
+
+        if (!response.ok) {
+          throw new Error(`Error fetching data: ${response.statusText}`);
+        }
+
+        const result = await response.json();
+        console.log("ConsultMobility: Data fetched successfully:", result);
+        setData(result);
+      } catch (err) {
+        console.error("ConsultMobility: Error fetching data:", err);
+        setError("Failed to load mobility data. Please try again later.");
+      }
+    };
+
+    fetchData();
+  }, []);
+
+  if (error) {
+    return <p className="error">{error}</p>;
+  }
+
+  if (!data?.promotions) {
+    return <p>No promotions found.</p>;
+  }
+
+  return (
+    <section>
+      <h2>Consult Mobility</h2>
+      {data.promotions.map((promo) => (
+        <div key={promo.id}>
+          <h3>Promotion: {promo.name}</h3>
+          <table>
+            <thead>
+              <tr>
+                <th>ID</th>
+                <th>First Name</th>
+                <th>Last Name</th>
+                <th>Start Date</th>
+                <th>End Date</th>
+                <th>Weeks Count</th>
+                <th>Destination Country</th>
+                <th>Destination Name</th>
+                <th>Status</th>
+              </tr>
+            </thead>
+            <tbody>
+              {data.students
+                ?.filter((student) => student.promotionId === promo.id)
+                .map((student) => {
+                  const mobility = data.mobilities?.find((mob) =>
+                    mob.studentId === student.id
+                  );
+                  return (
+                    <tr key={student.id}>
+                      <td>{student.id}</td>
+                      <td>{student.firstName}</td>
+                      <td>{student.lastName}</td>
+                      <td>{mobility?.startDate || "N/A"}</td>
+                      <td>{mobility?.endDate || "N/A"}</td>
+                      <td>{mobility?.weeksCount ?? "N/A"}</td>
+                      <td>{mobility?.destinationCountry || "N/A"}</td>
+                      <td>{mobility?.destinationName || "N/A"}</td>
+                      <td>{mobility?.mobilityStatus || "N/A"}</td>
+                    </tr>
+                  );
+                })}
+            </tbody>
+          </table>
+        </div>
+      ))}
+    </section>
+  );
+}

routes/(apps)/admin/(_islands)/ConsultStudents_test.tsx

@@ -0,0 +1,75 @@
+import { useEffect, useState } from "preact/hooks";
+
+interface Promotion {
+  id: number;
+  name: string;
+}
+
+interface Student {
+  id: number;
+  firstName: string;
+  lastName: string;
+  mail: string;
+  promotionId: number;
+  promotionName: string;
+}
+
+export default function ConsultStudents_test() {
+  const [data, setData] = useState<
+    { promotions: Promotion[]; students: Student[] } | null
+  >(null);
+  const [error, setError] = useState<string | null>(null);
+
+  useEffect(() => {
+    const fetchData = async () => {
+      try {
+        const response = await fetch("/students/api/insert_students");
+        if (!response.ok) {
+          throw new Error(`Error fetching data: ${response.statusText}`);
+        }
+
+        const result = await response.json();
+        setData(result);
+      } catch (err) {
+        console.error("Error fetching data:", err);
+        setError("Failed to load data. Please try again later.");
+      }
+    };
+
+    fetchData();
+  }, []);
+
+  return (
+    <section>
+      <h2>Consult Students</h2>
+      {error && <p className="error">{error}</p>}
+      {data?.promotions.map((promo) => (
+        <div key={promo.id}>
+          <h3>Promotion: {promo.id}</h3>
+          <table>
+            <thead>
+              <tr>
+                <th>ID</th>
+                <th>First Name</th>
+                <th>Last Name</th>
+                <th>Email</th>
+              </tr>
+            </thead>
+            <tbody>
+              {data.students
+                .filter((student) => student.promotionId === promo.id)
+                .map((student) => (
+                  <tr key={student.id}>
+                    <td>{student.id}</td>
+                    <td>{student.firstName}</td>
+                    <td>{student.lastName}</td>
+                    <td>{student.mail}</td>
+                  </tr>
+                ))}
+            </tbody>
+          </table>
+        </div>
+      ))}
+    </section>
+  );
+}

routes/(apps)/admin/(_islands)/EditMobility.tsx

@@ -0,0 +1,248 @@
+import { useEffect, useState } from "preact/hooks";
+
+interface Student {
+  id: string;
+  firstName: string;
+  lastName: string;
+  promotionId: number;
+}
+
+interface Promotion {
+  id: number;
+  name: string;
+}
+
+interface Mobility {
+  id: number | null;
+  studentId: string;
+  startDate: string | null;
+  endDate: string | null;
+  weeksCount: number | null;
+  destinationCountry: string | null;
+  destinationName: string | null;
+  mobilityStatus: string;
+}
+
+export default function EditMobility() {
+  const [data, setData] = useState<
+    | {
+      promotions?: Promotion[];
+      students?: Student[];
+      mobilities?: Mobility[];
+    }
+    | null
+  >(null);
+  const [error, setError] = useState<string | null>(null);
+  const [isSaving, setIsSaving] = useState(false);
+
+  useEffect(() => {
+    const fetchData = async () => {
+      console.log("EditMobility: Fetching data from API...");
+      try {
+        const response = await fetch("/mobility/api/insert_mobility");
+        console.log("EditMobility: API response status:", response.status);
+
+        if (!response.ok) {
+          throw new Error(`Error fetching data: ${response.statusText}`);
+        }
+
+        const result = await response.json();
+        console.log("EditMobility: Data fetched successfully:", result);
+        setData(result);
+      } catch (err) {
+        console.error("EditMobility: Error fetching data:", err);
+        setError("Failed to load mobility data. Please try again later.");
+      }
+    };
+
+    fetchData();
+  }, []);
+
+  const handleChange = (
+    studentId: string,
+    field: keyof Mobility,
+    value: string | number | null,
+  ) => {
+    if (!data) return;
+
+    setData((prevData) => {
+      if (!prevData) return null;
+
+      const updatedMobilities = prevData.mobilities?.map((mobility) => {
+        if (mobility.studentId === studentId) {
+          const updatedMobility = { ...mobility, [field]: value };
+
+          if (field === "startDate" || field === "endDate") {
+            const startDate = new Date(updatedMobility.startDate || "");
+            const endDate = new Date(updatedMobility.endDate || "");
+            if (startDate && endDate && startDate <= endDate) {
+              const weeks = Math.ceil(
+                (endDate.getTime() - startDate.getTime()) /
+                  (7 * 24 * 60 * 60 * 1000),
+              );
+              updatedMobility.weeksCount = weeks;
+            } else {
+              updatedMobility.weeksCount = null;
+            }
+          }
+
+          return updatedMobility;
+        }
+        return mobility;
+      }) || [];
+
+      return { ...prevData, mobilities: updatedMobilities };
+    });
+  };
+
+  const handleSave = async () => {
+    setIsSaving(true);
+
+    try {
+      const response = await fetch("/mobility/api/insert_mobility", {
+        method: "POST",
+        headers: { "Content-Type": "application/json" },
+        body: JSON.stringify({ data: data?.mobilities }),
+      });
+
+      console.log("EditMobility: Save response status:", response.status);
+
+      if (response.ok) {
+        alert("Data saved successfully!");
+        globalThis.location.reload();
+      } else {
+        throw new Error(`Failed to save data: ${response.statusText}`);
+      }
+    } catch (error) {
+      console.error("EditMobility: Error saving data:", error);
+      alert("An error occurred while saving data.");
+    } finally {
+      setIsSaving(false);
+    }
+  };
+
+  if (error) {
+    return <p className="error">{error}</p>;
+  }
+
+  if (!data?.promotions) {
+    return <p>Loading data...</p>;
+  }
+
+  return (
+    <section>
+      <h2>Edit Mobility</h2>
+      {data.promotions.map((promo) => (
+        <div key={promo.id}>
+          <h3>Promotion: {promo.name}</h3>
+          <table>
+            <thead>
+              <tr>
+                <th>ID</th>
+                <th>First Name</th>
+                <th>Last Name</th>
+                <th>Start Date</th>
+                <th>End Date</th>
+                <th>Weeks Count</th>
+                <th>Destination Country</th>
+                <th>Destination Name</th>
+                <th>Status</th>
+              </tr>
+            </thead>
+            <tbody>
+              {data.students
+                ?.filter((student) => student.promotionId === promo.id)
+                .map((student) => {
+                  const mobility = data.mobilities?.find((mob) =>
+                    mob.studentId === student.id
+                  ) || {
+                    id: null,
+                    studentId: student.id,
+                    startDate: null,
+                    endDate: null,
+                    weeksCount: null,
+                    destinationCountry: null,
+                    destinationName: null,
+                    mobilityStatus: "N/A",
+                  };
+
+                  return (
+                    <tr key={student.id}>
+                      <td>{student.id}</td>
+                      <td>{student.firstName}</td>
+                      <td>{student.lastName}</td>
+                      <td>
+                        <input
+                          type="date"
+                          value={mobility.startDate || ""}
+                          onChange={(e) =>
+                            handleChange(
+                              student.id,
+                              "startDate",
+                              e.target.value,
+                            )}
+                        />
+                      </td>
+                      <td>
+                        <input
+                          type="date"
+                          value={mobility.endDate || ""}
+                          onChange={(e) =>
+                            handleChange(student.id, "endDate", e.target.value)}
+                        />
+                      </td>
+                      <td>{mobility.weeksCount ?? "N/A"}</td>
+                      <td>
+                        <input
+                          type="text"
+                          value={mobility.destinationCountry || ""}
+                          onChange={(e) =>
+                            handleChange(
+                              student.id,
+                              "destinationCountry",
+                              e.target.value,
+                            )}
+                        />
+                      </td>
+                      <td>
+                        <input
+                          type="text"
+                          value={mobility.destinationName || ""}
+                          onChange={(e) =>
+                            handleChange(
+                              student.id,
+                              "destinationName",
+                              e.target.value,
+                            )}
+                        />
+                      </td>
+                      <td>
+                        <select
+                          value={mobility.mobilityStatus}
+                          onChange={(e) =>
+                            handleChange(
+                              student.id,
+                              "mobilityStatus",
+                              e.target.value,
+                            )}
+                        >
+                          <option value="N/A">N/A</option>
+                          <option value="Planned">Planned</option>
+                          <option value="In Progress">In Progress</option>
+                          <option value="Completed">Completed</option>
+                          <option value="Validated">Validated</option>
+                        </select>
+                      </td>
+                    </tr>
+                  );
+                })}
+            </tbody>
+          </table>
+        </div>
+      ))}
+      <button type="button" onClick={handleSave} disabled={isSaving}>
+        {isSaving ? "Saving..." : "Confirm"}
+      </button>
+    </section>
+  );
+}

routes/(apps)/admin/(_props)/props.ts

@@ -0,0 +1,16 @@
+import { AppProperties } from "$root/defaults/interfaces.ts";
+
+const properties: AppProperties = {
+  name: "PolyMobility",
+  icon: "flight_takeoff",
+  hint: "Student mobility management",
+  pages: {
+    index: "Homepage",
+    overview: "Mobility overview",
+    edit_mobility: "Mobility management",
+    consult_students_test: "Test consult students",
+  },
+  adminOnly: ["edit_mobility", "consult_students_test"],
+};
+
+export default properties;

routes/(apps)/admin/api/enseignement.ts

@@ -0,0 +1,18 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { mobility, promotions, students,ens } from "$root/databases/schema.ts";
+import { eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  async GET() {
+    try {
+      // recup les enseigne
+      
+      const rows = await 
+      db.select().from(students)
+
+      // en faire des json
+      // les retouner
+      return { students: rows as Student[], promos };
+    }
+  }

routes/(apps)/admin/api/insert_mobility.ts

@@ -0,0 +1,116 @@
+import { Handlers } from "$fresh/server.ts";
+import { db } from "$root/databases/db.ts";
+import { mobility, promotions, students } from "$root/databases/schema.ts";
+import { eq } from "npm:drizzle-orm";
+
+export const handler: Handlers = {
+  async GET() {
+    try {
+      const studentRows = await db
+        .select({
+          id: students.userId,
+          firstName: students.firstName,
+          lastName: students.lastName,
+          promotionId: students.promotionId,
+          endyear: promotions.endyear,
+          current: promotions.current,
+        })
+        .from(students)
+        .leftJoin(promotions, eq(students.promotionId, promotions.id));
+
+      const mobilityRows = await db.select().from(mobility);
+
+      const promotionRows = await db
+        .select({ id: promotions.id, endyear: promotions.endyear, current: promotions.current })
+        .from(promotions);
+
+      return new Response(
+        JSON.stringify({
+          mobilities: mobilityRows,
+          students: studentRows,
+          promotions: promotionRows,
+        }),
+        { status: 200, headers: { "Content-Type": "application/json" } },
+      );
+    } catch (error) {
+      console.error("Error fetching mobility data:", error);
+      return new Response("Failed to fetch data", { status: 500 });
+    }
+  },
+
+  async POST(request) {
+    try {
+      const body = await request.json();
+      const { data } = body;
+
+      if (!Array.isArray(data)) {
+        throw new Error("Invalid request body");
+      }
+
+      for (const entry of data) {
+        const {
+          id,
+          studentId,
+          startDate,
+          endDate,
+          weeksCount,
+          destinationCountry,
+          destinationName,
+          mobilityStatus = "N/A",
+        } = entry;
+
+        const studentExists = await db
+          .select({ userId: students.userId })
+          .from(students)
+          .where(eq(students.userId, studentId))
+          .limit(1)
+          .then((rows) => rows.length > 0);
+
+        if (!studentExists) {
+          console.warn(`Skipping mobility for unknown studentId: ${studentId}`);
+          continue;
+        }
+
+        let calculatedWeeksCount = weeksCount;
+        if (startDate && endDate) {
+          const start = new Date(startDate);
+          const end = new Date(endDate);
+          calculatedWeeksCount = start <= end
+            ? Math.ceil(
+              (end.getTime() - start.getTime()) / (7 * 24 * 60 * 60 * 1000),
+            )
+            : null;
+        }
+
+        await db
+          .insert(mobility)
+          .values({
+            id,
+            studentId,
+            startDate,
+            endDate,
+            weeksCount: calculatedWeeksCount,
+            destinationCountry,
+            destinationName,
+            mobilityStatus,
+          })
+          .onConflictDoUpdate({
+            target: mobility.id,
+            set: {
+              startDate,
+              endDate,
+              weeksCount: calculatedWeeksCount,
+              destinationCountry,
+              destinationName,
+              mobilityStatus,
+            },
+          });
+      }
+
+      return new Response("Data inserted/updated successfully", { status: 200 });
+    } catch (error) {
+      console.error("Error inserting mobility data:", error);
+      return new Response("Failed to insert/update data", { status: 500 });
+    }
+  },
+};

routes/(apps)/admin/index.tsx

@@ -0,0 +1,2 @@
+import makeIndex from "$root/defaults/makeIndex.ts";
+export default makeIndex(import.meta.dirname!);

routes/(apps)/admin/partials/(admin)/consult_students_test.tsx

@@ -0,0 +1,21 @@
+import ConsultStudents_test from "$root/routes/(apps)/mobility/(_islands)/ConsultStudents_test.tsx";
+import {
+  getPartialsConfig,
+  makePartials,
+} from "$root/defaults/makePartials.tsx";
+import { FreshContext } from "$fresh/server.ts";
+import { State } from "$root/routes/_middleware.ts";
+//import EditStudents from "../(_islands)/EditStudents.tsx";
+
+// deno-lint-ignore require-await
+async function Mobility(_request: Request, _context: FreshContext<State>) {
+  return (
+    <>
+      <h1>Test consult students</h1>
+      <ConsultStudents_test />
+    </>
+  );
+}
+
+export const config = getPartialsConfig();
+export default makePartials(Mobility);

routes/(apps)/admin/partials/(admin)/edit_mobility.tsx

@@ -0,0 +1,20 @@
+import EditMobility from "$root/routes/(apps)/mobility/(_islands)/EditMobility.tsx";
+import {
+  getPartialsConfig,
+  makePartials,
+} from "$root/defaults/makePartials.tsx";
+import { FreshContext } from "$fresh/server.ts";
+import { State } from "$root/routes/_middleware.ts";
+
+// deno-lint-ignore require-await
+async function Mobility(_request: Request, _context: FreshContext<State>) {
+  return (
+    <>
+      <h1>Edit mobility</h1>
+      <EditMobility />
+    </>
+  );
+}
+
+export const config = getPartialsConfig();
+export default makePartials(Mobility);

routes/(apps)/admin/partials/index.tsx

@@ -0,0 +1,14 @@
+import {
+  getPartialsConfig,
+  makePartials,
+} from "$root/defaults/makePartials.tsx";
+import { FreshContext } from "$fresh/server.ts";
+import { State } from "$root/routes/_middleware.ts";
+
+// deno-lint-ignore require-await
+export async function Index(_request: Request, context: FreshContext<State>) {
+  return <h2>Welcome to {context.state.session?.displayName}.</h2>;
+}
+
+export const config = getPartialsConfig();
+export default makePartials(Index);

routes/(apps)/admin/partials/overview.tsx

@@ -0,0 +1,20 @@
+import ConsultMobility from "$root/routes/(apps)/mobility/(_islands)/ConsultMobility.tsx";
+import {
+  getPartialsConfig,
+  makePartials,
+} from "$root/defaults/makePartials.tsx";
+import { FreshContext } from "$fresh/server.ts";
+import { State } from "$root/routes/_middleware.ts";
+
+// deno-lint-ignore require-await
+async function Mobility(_request: Request, _context: FreshContext<State>) {
+  return (
+    <>
+      <h1>Edit mobility</h1>
+      <ConsultMobility />
+    </>
+  );
+}
+
+export const config = getPartialsConfig();
+export default makePartials(Mobility);

routes/(apps)/mobility/api/insert_mobility.ts

@@ -1,55 +1,36 @@
 import { Handlers } from "$fresh/server.ts";
-import { Database } from "@db/sqlite";
+import { db } from "$root/databases/db.ts";
+import { mobility, promotions, students } from "$root/databases/schema.ts";
+import { eq } from "npm:drizzle-orm";
 
 export const handler: Handlers = {
-  // deno-lint-ignore require-await
   async GET() {
     try {
-      console.log("Connecting to mobility database...");
-      const connection = new Database("databases/data/mobility.db", {
-        create: false,
-      });
-      connection.run(
-        "ATTACH DATABASE 'databases/data/students.db' AS students",
-      );
-      console.log("Connected to databases.");
+      const studentRows = await db
+        .select({
+          id: students.userId,
+          firstName: students.firstName,
+          lastName: students.lastName,
+          promotionId: students.promotionId,
+          endyear: promotions.endyear,
+          current: promotions.current,
+        })
+        .from(students)
+        .leftJoin(promotions, eq(students.promotionId, promotions.id));
 
-      const students = connection.prepare(
-        `SELECT 
-          students.userId AS id, 
-          students.firstName, 
-          students.lastName, 
-          students.promotionId AS promotionId, 
-          promotions.name AS promotionName
-         FROM students.students
-         LEFT JOIN students.promotions ON students.promotionId = promotions.id`,
-      ).all();
+      const mobilityRows = await db.select().from(mobility);
 
-      const mobilities = connection.prepare(
-        `SELECT 
-          mobility.id, 
-          mobility.studentId, 
-          mobility.startDate, 
-          mobility.endDate, 
-          mobility.weeksCount, 
-          mobility.destinationCountry, 
-          mobility.destinationName, 
-          mobility.mobilityStatus 
-        FROM mobility`,
-      ).all();
-
-      const promotions = connection.prepare(
-        `SELECT id, name FROM students.promotions`,
-      ).all();
-
-      connection.close();
+      const promotionRows = await db
+        .select({ id: promotions.id, endyear: promotions.endyear, current: promotions.current })
+        .from(promotions);
 
       return new Response(
-        JSON.stringify({ mobilities, students, promotions }),
-        {
-          status: 200,
-          headers: { "Content-Type": "application/json" },
-        },
+        JSON.stringify({
+          mobilities: mobilityRows,
+          students: studentRows,
+          promotions: promotionRows,
+        }),
+        { status: 200, headers: { "Content-Type": "application/json" } },
       );
     } catch (error) {
       console.error("Error fetching mobility data:", error);
@@ -58,8 +39,6 @@ export const handler: Handlers = {
   },
 
   async POST(request) {
-    console.log("API /mobility/api/insert_mobility POST called");
-
     try {
       const body = await request.json();
       const { data } = body;
@@ -67,32 +46,8 @@ export const handler: Handlers = {
       if (!Array.isArray(data)) {
         throw new Error("Invalid request body");
       }
-      console.log("Connecting to mobility database...");
-      const connection = new Database("databases/data/mobility.db", {
-        create: false,
-      });
 
-      console.log("Attaching students database...");
-      connection.run(
-        "ATTACH DATABASE 'databases/data/students.db' AS students",
-      );
-      console.log("Students database attached successfully.");
-
-      const insertQuery = connection.prepare(
-        `INSERT INTO mobility (
-          id, studentId, startDate, endDate, weeksCount, destinationCountry, destinationName, mobilityStatus
-        )
-        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
-        ON CONFLICT(id) DO UPDATE SET
-          startDate = excluded.startDate,
-          endDate = excluded.endDate,
-          weeksCount = excluded.weeksCount,
-          destinationCountry = excluded.destinationCountry,
-          destinationName = excluded.destinationName,
-          mobilityStatus = excluded.mobilityStatus`,
-      );
-
-      for (const mobility of data) {
+      for (const entry of data) {
         const {
           id,
           studentId,
@@ -102,19 +57,16 @@ export const handler: Handlers = {
           destinationCountry,
           destinationName,
           mobilityStatus = "N/A",
-        } = mobility;
+        } = entry;
 
-        console.log("Processing mobility data:", mobility);
+        const studentExists = await db
+          .select({ userId: students.userId })
+          .from(students)
+          .where(eq(students.userId, studentId))
+          .limit(1)
+          .then((rows) => rows.length > 0);
 
-        const studentExists = connection
-          .prepare(
-            `SELECT COUNT(*) AS count FROM students.students WHERE userId = ?`,
-          )
-          .get(studentId);
-
-        console.log(`Student ${studentId} exists:`, studentExists.count > 0);
-
-        if (studentExists.count === 0) {
+        if (!studentExists) {
           console.warn(`Skipping mobility for unknown studentId: ${studentId}`);
           continue;
         }
@@ -123,43 +75,39 @@ export const handler: Handlers = {
         if (startDate && endDate) {
           const start = new Date(startDate);
           const end = new Date(endDate);
-          if (start <= end) {
-            calculatedWeeksCount = Math.ceil(
+          calculatedWeeksCount = start <= end
+            ? Math.ceil(
               (end.getTime() - start.getTime()) / (7 * 24 * 60 * 60 * 1000),
-            );
-          } else {
-            calculatedWeeksCount = null;
-          }
+            )
+            : null;
         }
 
-        console.log("Executing SQL insert/update query for:", {
-          id,
-          studentId,
-          startDate,
-          endDate,
-          calculatedWeeksCount,
-          destinationCountry,
-          destinationName,
-          mobilityStatus,
-        });
-
-        insertQuery.run(
-          id,
-          studentId,
-          startDate,
-          endDate,
-          calculatedWeeksCount,
-          destinationCountry,
-          destinationName,
-          mobilityStatus,
-        );
+        await db
+          .insert(mobility)
+          .values({
+            id,
+            studentId,
+            startDate,
+            endDate,
+            weeksCount: calculatedWeeksCount,
+            destinationCountry,
+            destinationName,
+            mobilityStatus,
+          })
+          .onConflictDoUpdate({
+            target: mobility.id,
+            set: {
+              startDate,
+              endDate,
+              weeksCount: calculatedWeeksCount,
+              destinationCountry,
+              destinationName,
+              mobilityStatus,
+            },
+          });
       }
 
-      connection.close();
-      console.log("Mobility data inserted/updated successfully.");
-      return new Response("Data inserted/updated successfully", {
-        status: 200,
-      });
+      return new Response("Data inserted/updated successfully", { status: 200 });
     } catch (error) {
       console.error("Error inserting mobility data:", error);
       return new Response("Failed to insert/update data", { status: 500 });

routes/(apps)/students/api/students.ts

@@ -1,150 +1,121 @@
 import { FreshContext, Handlers } from "$fresh/server.ts";
-import connect from "$root/databases/connect.ts";
+import { db } from "$root/databases/db.ts";
+import { promotions, students } from "$root/databases/schema.ts";
 import { AuthenticatedState } from "$root/defaults/interfaces.ts";
-import { Database } from "@db/sqlite";
+import { eq, lt } from "npm:drizzle-orm";
 
-/**
- * Gets itself from the database.
- * @param database The database connection
- * @param userId The user ID.
- * @returns Itself from the database.
- */
-function getItself(
-  database: Database,
+async function getItself(
   userId: string,
-): { student: Student | null; promo: Promotion | null } {
-  const studentQuery = "select * from students where userId = ?";
-  const student: Student | undefined = database.prepare(studentQuery).get(
-    userId,
-  );
+): Promise<{ student: Student | null; promo: Promotion | null }> {
+  const student = await db
+    .select()
+    .from(students)
+    .where(eq(students.userId, userId))
+    .limit(1)
+    .then((rows) => rows[0] ?? null);
 
   if (!student) {
     return { student: null, promo: null };
   }
 
-  const promoQuery = "select * from promotions where id = ?";
-  const promo: Promotion | undefined = database.prepare(promoQuery).get(
-    student.promotionId,
-  );
+  const promo = await db
+    .select()
+    .from(promotions)
+    .where(eq(promotions.id, student.promotionId!))
+    .limit(1)
+    .then((rows) => rows[0] ?? null);
 
-  return { student, promo: promo ?? null };
+  return { student, promo };
 }
 
-/**
- * Gets itself from the database.
- * @param database The database connexion
- * @param userId The user ID.
- * @returns Itself from the database.
- */
-function getAll(
-  database: Database,
-): { students: Student[]; promos: Promotion[] } {
-  const studentsQuery = `
-    select userId, firstName, lastName, mail, promotionId
-    from students inner join promotions
-    on students.promotionId = promotions.id
-    where promotions.current < 6`;
-  const students: Student[] = database.prepare(studentsQuery).all();
+async function getAll(): Promise<
+  { students: Student[]; promos: Promotion[] }
+> {
+  const rows = await db
+    .select({
+      userId: students.userId,
+      firstName: students.firstName,
+      lastName: students.lastName,
+      mail: students.mail,
+      promotionId: students.promotionId,
+    })
+    .from(students)
+    .innerJoin(promotions, eq(students.promotionId, promotions.id))
+    .where(lt(promotions.current, 6));
 
-  const promosQuery = "select * from promotions where promotions.current < 6";
-  const promos: Promotion[] | undefined = database.prepare(promosQuery).all();
+  const promos = await db
+    .select()
+    .from(promotions)
+    .where(lt(promotions.current, 6));
 
-  return { students, promos };
+  return { students: rows as Student[], promos };
 }
 
-/**
- * Add users to the database.
- * @param database The database connexion
- * @param students The students to add
- * @param promoId The promotion id.
- */
-function addStudents(database: Database, students: Student[], promoId: string) {
-  const query = `
-    INSERT INTO students 
-    (userId, firstName, lastName, mail, promotionId) 
-    VALUES (?, ?, ?, ?, ?)`;
-
-  const statement = database.prepare(query);
-
-  for (const student of students) {
-    statement.run(
-      student.userId,
-      student.firstName,
-      student.lastName,
-      student.mail,
-      promoId,
-    );
+async function addStudents(
+  studentList: Student[],
+  promoId: number,
+): Promise<void> {
+  for (const student of studentList) {
+    await db
+      .insert(students)
+      .values({
+        userId: student.userId,
+        firstName: student.firstName,
+        lastName: student.lastName,
+        mail: student.mail,
+        promotionId: promoId,
+      })
+      .onConflictDoNothing();
   }
 }
 
 export const handler: Handlers<null, AuthenticatedState> = {
-  /**
-   * The students the user can see.
-   * @param _request The HTTP request.
-   * @param _context The context with authenticated state.
-   * @returns All students our user can see.
-   */
-  // deno-lint-ignore require-await
   async GET(
     _request: Request,
     context: FreshContext<AuthenticatedState>,
   ): Promise<Response> {
-    using connection = connect("students");
-    const database = connection.database;
-
     if (context.state.session.eduPersonPrimaryAffiliation == "student") {
       return new Response(
-        JSON.stringify(getItself(database, context.state.session.uid)),
-        {
-          headers: {
-            "content-type": "application/json",
-          },
-        },
+        JSON.stringify(await getItself(context.state.session.uid)),
+        { headers: { "content-type": "application/json" } },
       );
     }
 
     return new Response(
-      JSON.stringify(getAll(database)),
-      {
-        headers: {
-          "content-type": "application/json",
-        },
-      },
+      JSON.stringify(await getAll()),
+      { headers: { "content-type": "application/json" } },
     );
   },
-  /**
-   * Add students in the database.
-   * @param request The HTTP request.
-   * @param _context The Fresh context.
-   * @returns HTTP 201 on successful insert.
-   */
+
   async POST(
     request: Request,
     _context: FreshContext<AuthenticatedState>,
   ): Promise<Response> {
-    const { students, promo }: { students: Student[]; promo: string } =
-      await request.json();
+    const { students: studentList, promo }: {
+      students: Student[];
+      promo: string;
+    } = await request.json();
 
-    if (!promo || !promo.match(/^\d{4}-\dA$/) || !Array.isArray(students)) {
+    if (!promo || !promo.match(/^\d{4}-\dA$/) || !Array.isArray(studentList)) {
       return new Response(null, { status: 400 });
     }
 
-    using connection = connect("students");
-    const database = connection.database;
-
     const { endyear, current } = promo.match(
       /^(?<endyear>\d{4})-(?<current>\d)A$/,
     )?.groups!;
 
-    database.prepare(
-      "insert or ignore into promotions (endyear, current) values (?, ?)",
-    ).run(endyear, current);
+    await db
+      .insert(promotions)
+      .values({ endyear: Number(endyear), current: Number(current) })
+      .onConflictDoNothing();
 
-    const { id: promoId }: { id: string } = database
-      .prepare("select id from promotions where endyear = ? and current = ?")
-      .get(endyear, current)!;
+    const promo_row = await db
+      .select()
+      .from(promotions)
+      .where(eq(promotions.endyear, Number(endyear)))
+      .then((rows) => rows.find((r) => r.current === Number(current))!);
 
-    addStudents(database, students, promoId);
+    await addStudents(studentList, promo_row.id);
 
     return new Response(null, { status: 201 });
   },