diff options
| author | sadbeast <sadbeast@sadbeast.com> | 2024-06-23 15:36:59 -0700 |
|---|---|---|
| committer | sadbeast <sadbeast@sadbeast.com> | 2024-07-13 21:58:23 -0700 |
| commit | 8d018d996c1eddb882dc64ebbd228bb0135944f3 (patch) | |
| tree | d01956546a77dbae33357c9a5d174f511ac9b282 | |
| download | teamdraft-main.tar.gz teamdraft-main.tar.bz2 | |
39 files changed, 2728 insertions, 0 deletions
@@ -0,0 +1,2 @@ +DATABASE_URL=postgres://teamdraft:teamdraft@localhost/teamdraft +PGHOST=/tmp/pg1 @@ -0,0 +1,2 @@ +use flake +dotenv diff --git a/.gitattributes b/.gitattributes new file mode 100644 index 0000000..6313b56 --- /dev/null +++ b/.gitattributes @@ -0,0 +1 @@ +* text=auto eol=lf diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..dec53ea --- /dev/null +++ b/.gitignore @@ -0,0 +1,6 @@ +.zig-cache/ +zig-out/ +result/ +.direnv/ +data/ +docs/ diff --git a/.proverc b/.proverc new file mode 100644 index 0000000..bd25867 --- /dev/null +++ b/.proverc @@ -0,0 +1,5 @@ +test/ +--ext .sql +-h localhost +-d teamdraft +-U teamdraft diff --git a/README.md b/README.md new file mode 100644 index 0000000..61ca9dd --- /dev/null +++ b/README.md @@ -0,0 +1,26 @@ +# Team Draft Website + +The website for [teamdraft.net](https://teamdraft.net). + +## Building & Running + +It's a Zig project: + +`zig build run` + +### Nix Flake + +It's got one! + +```bash +nix develop +``` +or + +```bash +direnv allow +``` + +## Acknowledgements + +Most of the structure of the code was taken from [aolium-api](https://github.com/karlseguin/aolium-api). diff --git a/build.zig b/build.zig new file mode 100644 index 0000000..00de4cf --- /dev/null +++ b/build.zig @@ -0,0 +1,151 @@ +const std = @import("std"); + +// Although this function looks imperative, note that its job is to +// declaratively construct a build graph that will be executed by an external +// runner. +pub fn build(b: *std.Build) void { + // Standard target options allows the person running `zig build` to choose + // what target to build for. Here we do not override the defaults, which + // means any target is allowed, and the default is native. Other options + // for restricting supported target set are available. + const target = b.standardTargetOptions(.{}); + + // Standard optimization options allow the person running `zig build` to select + // between Debug, ReleaseSafe, ReleaseFast, and ReleaseSmall. Here we do not + // set a preferred release mode, allowing the user to decide how to optimize. + const optimize = b.standardOptimizeOption(.{}); + + const teamdraft = b.addExecutable(.{ + .name = "teamdraft", + .root_source_file = b.path("src/main.zig"), + .target = target, + .optimize = optimize, + }); + + const dev = b.option(bool, "dev", "Dev mode - automatically reload templates") orelse false; + const options = b.addOptions(); + options.addOption(bool, "dev", dev); + + var templates = std.ArrayList([]const u8).init(b.allocator); + defer templates.deinit(); + + var templates_dir = std.fs.cwd().openDir("src/views", .{ .iterate = true }) catch @panic("can't find views directory"); + defer templates_dir.close(); + + var it = templates_dir.iterate(); + while (it.next() catch @panic("can't get next view")) |file| { + if (file.kind != .file) { + continue; + } + var template_name: [50]u8 = undefined; + _ = std.mem.replace(u8, file.name, ".mustache", "", &template_name); + templates.append(b.dupe(template_name[0 .. file.name.len - 9])) catch @panic("unable to add view name to templates"); + } + + options.addOption([]const []const u8, "templates", templates.items); + + teamdraft.root_module.addOptions("config", options); + + const scorecorder = b.addExecutable(.{ + .name = "scorecorder", + .root_source_file = b.path("src/scorecorder.zig"), + .target = target, + .optimize = optimize, + }); + scorecorder.root_module.addImport("zul", b.dependency("zul", .{ + .target = target, + .optimize = optimize, + }).module("zul")); + + const logz = b.dependency("logz", .{ + .target = target, + .optimize = optimize, + }); + scorecorder.root_module.addImport("logz", logz.module("logz")); + teamdraft.root_module.addImport("logz", logz.module("logz")); + + const httpz = b.dependency("httpz", .{ + .target = target, + .optimize = optimize, + }); + teamdraft.root_module.addImport("httpz", httpz.module("httpz")); + + const mustache = b.dependency("mustache", .{ + .target = target, + .optimize = optimize, + }); + teamdraft.root_module.addImport("mustache", mustache.module("mustache")); + + const pg = b.dependency("pg", .{ + .target = target, + .optimize = optimize, + }); + teamdraft.root_module.addImport("pg", pg.module("pg")); + scorecorder.root_module.addImport("pg", pg.module("pg")); + + // This declares intent for the executable to be installed into the + // standard location when the user invokes the "install" step (the default + // step when running `zig build`). + b.installArtifact(teamdraft); + b.installArtifact(scorecorder); + + // This *creates* a Run step in the build graph, to be executed when another + // step is evaluated that depends on it. The next line below will establish + // such a dependency. + const run_cmd = b.addRunArtifact(teamdraft); + + // By making the run step depend on the install step, it will be run from the + // installation directory rather than directly from within the cache directory. + // This is not necessary, however, if the application depends on other installed + // files, this ensures they will be present and in the expected location. + run_cmd.step.dependOn(b.getInstallStep()); + + // This allows the user to pass arguments to the application in the build + // command itself, like this: `zig build run -- arg1 arg2 etc` + if (b.args) |args| { + run_cmd.addArgs(args); + } + + // This creates a build step. It will be visible in the `zig build --help` menu, + // and can be selected like this: `zig build run` + // This will evaluate the `run` step rather than the default, which is "install". + const run_step = b.step("run", "Run the app"); + run_step.dependOn(&run_cmd.step); + + // Creates a step for unit testing. This only builds the test executable + // but does not run it. + const exe_unit_tests = b.addTest(.{ + .root_source_file = b.path("src/main.zig"), + .target = target, + .optimize = optimize, + }); + + const run_exe_unit_tests = b.addRunArtifact(exe_unit_tests); + + // Similar to creating the run step earlier, this exposes a `test` step to + // the `zig build --help` menu, providing a way for the user to request + // running the unit tests. + const test_step = b.step("test", "Run unit tests"); + test_step.dependOn(&run_exe_unit_tests.step); + + const coverage_step = b.step("coverage", "Generate a coverage report with kcov"); + + const merge_step = std.Build.Step.Run.create(b, "merge coverage"); + merge_step.addArgs(&.{ "kcov", "--merge" }); + merge_step.rename_step_with_output_arg = false; + const merged_coverage_output = merge_step.addOutputFileArg("."); + + const kcov_collect = std.Build.Step.Run.create(b, "collect coverage"); + kcov_collect.addArgs(&.{ "kcov", "--collect-only" }); + kcov_collect.addPrefixedDirectoryArg("--include-pattern=", b.path("src")); + merge_step.addDirectoryArg(kcov_collect.addOutputFileArg(exe_unit_tests.name)); + kcov_collect.addArtifactArg(exe_unit_tests); + kcov_collect.enableTestRunnerMode(); + + const install_coverage = b.addInstallDirectory(.{ + .source_dir = merged_coverage_output, + .install_dir = .{ .custom = "coverage" }, + .install_subdir = "", + }); + coverage_step.dependOn(&install_coverage.step); +} diff --git a/build.zig.zon b/build.zig.zon new file mode 100644 index 0000000..afcc976 --- /dev/null +++ b/build.zig.zon @@ -0,0 +1,55 @@ +.{ + // This is the default name used by packages depending on this one. For + // example, when a user runs `zig fetch --save <url>`, this field is used + // as the key in the `dependencies` table. Although the user can choose a + // different name, most users will stick with this provided value. + // + // It is redundant to include "zig" in this name because it is already + // within the Zig package namespace. + .name = "teamdraft", + + // This is a [Semantic Version](https://semver.org/). + // In a future version of Zig it will be used for package deduplication. + .version = "0.1.0", + + // This field is optional. + // This is currently advisory only; Zig does not yet do anything + // with this value. + //.minimum_zig_version = "0.11.0", + + // This field is optional. + // Each dependency must either provide a `url` and `hash`, or a `path`. + // `zig build --fetch` can be used to fetch all dependencies of a package, recursively. + // Once all dependencies are fetched, `zig build` no longer requires + // internet connectivity. + .dependencies = .{ + .httpz = .{ + .url = "https://github.com/karlseguin/http.zig/archive/406fb00f8c43fe9b2da0f32f428675755c67d054.tar.gz", + .hash = "12209e87663712928c6ae1c690e65df15a796e970e5d18f5e4e05f0eb10404883d23", + }, + .mustache = .{ + .url = "https://github.com/batiati/mustache-zig/archive/ae5ecc1522da983dc39bb0d8b27f5d1b1d7956e3.tar.gz", + .hash = "1220ac9e3316ce71ad9cd66c7f215462bf5c187828b50bb3d386549bf6af004e3bb0", + }, + .pg = .{ + .url = "https://github.com/karlseguin/pg.zig/archive/495fb12eef648af0d5b213514714afab71652409.tar.gz", + .hash = "122080d94692ad28203c2d1f6301d074adef183a0f983af89ee8084aeead797f288c", + }, + .zul = .{ + .url = "https://github.com/karlseguin/zul/archive/2193133fc792ed5b040eff08aeb48a193eb57893.tar.gz", + .hash = "1220e4d73251b8266b8e8ec1bee1ce95edf409ca67b96707b8a7ec58d856b7c8a82a", + }, + .logz = .{ + .url = "https://github.com/karlseguin/log.zig/archive/0f42ca099fd69f9cbd09620a7f455db0fa0310cc.tar.gz", + .hash = "1220996bf8517c7785a577811f6ee39574c274151a350e2be1896359e878e1ef9aa9", + }, + }, + .paths = .{ + "build.zig", + "build.zig.zon", + "src", + // For example... + //"LICENSE", + //"README.md", + }, +} diff --git a/flake.lock b/flake.lock new file mode 100644 index 0000000..da26371 --- /dev/null +++ b/flake.lock @@ -0,0 +1,194 @@ +{ + "nodes": { + "devshell": { + "inputs": { + "flake-utils": "flake-utils", + "nixpkgs": "nixpkgs" + }, + "locked": { + "lastModified": 1717408969, + "narHash": "sha256-Q0OEFqe35fZbbRPPRdrjTUUChKVhhWXz3T9ZSKmaoVY=", + "owner": "numtide", + "repo": "devshell", + "rev": "1ebbe68d57457c8cae98145410b164b5477761f4", + "type": "github" + }, + "original": { + "owner": "numtide", + "repo": "devshell", + "type": "github" + } + }, + "flake-parts": { + "inputs": { + "nixpkgs-lib": "nixpkgs-lib" + }, + "locked": { + "lastModified": 1719994518, + "narHash": "sha256-pQMhCCHyQGRzdfAkdJ4cIWiw+JNuWsTX7f0ZYSyz0VY=", + "owner": "hercules-ci", + "repo": "flake-parts", + "rev": "9227223f6d922fee3c7b190b2cc238a99527bbb7", + "type": "github" + }, + "original": { + "owner": "hercules-ci", + "repo": "flake-parts", + "type": "github" + } + }, + "flake-utils": { + "inputs": { + "systems": "systems" + }, + "locked": { + "lastModified": 1701680307, + "narHash": "sha256-kAuep2h5ajznlPMD9rnQyffWG8EM/C73lejGofXvdM8=", + "owner": "numtide", + "repo": "flake-utils", + "rev": "4022d587cbbfd70fe950c1e2083a02621806a725", + "type": "github" + }, + "original": { + "owner": "numtide", + "repo": "flake-utils", + "type": "github" + } + }, + "nixpkgs": { + "locked": { + "lastModified": 1704161960, + "narHash": "sha256-QGua89Pmq+FBAro8NriTuoO/wNaUtugt29/qqA8zeeM=", + "owner": "NixOS", + "repo": "nixpkgs", + "rev": "63143ac2c9186be6d9da6035fa22620018c85932", + "type": "github" + }, + "original": { + "owner": "NixOS", + "ref": "nixpkgs-unstable", + "repo": "nixpkgs", + "type": "github" + } + }, + "nixpkgs-lib": { + "locked": { + "lastModified": 1719876945, + "narHash": "sha256-Fm2rDDs86sHy0/1jxTOKB1118Q0O3Uc7EC0iXvXKpbI=", + "type": "tarball", + "url": "https://github.com/NixOS/nixpkgs/archive/5daf0514482af3f97abaefc78a6606365c9108e2.tar.gz" + }, + "original": { + "type": "tarball", + "url": "https://github.com/NixOS/nixpkgs/archive/5daf0514482af3f97abaefc78a6606365c9108e2.tar.gz" + } + }, + "nixpkgs_2": { + "locked": { + "lastModified": 1720031269, + "narHash": "sha256-rwz8NJZV+387rnWpTYcXaRNvzUSnnF9aHONoJIYmiUQ=", + "owner": "NixOS", + "repo": "nixpkgs", + "rev": "9f4128e00b0ae8ec65918efeba59db998750ead6", + "type": "github" + }, + "original": { + "owner": "NixOS", + "ref": "nixos-unstable", + "repo": "nixpkgs", + "type": "github" + } + }, + "nixpkgs_3": { + "locked": { + "lastModified": 1719690277, + "narHash": "sha256-0xSej1g7eP2kaUF+JQp8jdyNmpmCJKRpO12mKl/36Kc=", + "owner": "nixos", + "repo": "nixpkgs", + "rev": "2741b4b489b55df32afac57bc4bfd220e8bf617e", + "type": "github" + }, + "original": { + "owner": "nixos", + "ref": "nixos-unstable", + "repo": "nixpkgs", + "type": "github" + } + }, + "process-compose-flake": { + "locked": { + "lastModified": 1718031437, + "narHash": "sha256-+RrlkAVZx0QhyeHAGFJnjST+/7Dc3zsDU3zAKXoDXaI=", + "owner": "Platonic-Systems", + "repo": "process-compose-flake", + "rev": "9344fac44edced4c686721686a6ad904d067c546", + "type": "github" + }, + "original": { + "owner": "Platonic-Systems", + "repo": "process-compose-flake", + "type": "github" + } + }, + "root": { + "inputs": { + "devshell": "devshell", + "flake-parts": "flake-parts", + "nixpkgs": "nixpkgs_2", + "process-compose-flake": "process-compose-flake", + "services-flake": "services-flake", + "treefmt-nix": "treefmt-nix" + } + }, + "services-flake": { + "locked": { + "lastModified": 1720279385, + "narHash": "sha256-vvVMtCXzCSlGnFV4pJlAsekCC72WRRM0Aa/tlkx4e4w=", + "owner": "juspay", + "repo": "services-flake", + "rev": "b02b6c802548cb7814cd4d03098d1414c99652b9", + "type": "github" + }, + "original": { + "owner": "juspay", + "repo": "services-flake", + "type": "github" + } + }, + "systems": { + "locked": { + "lastModified": 1681028828, + "narHash": "sha256-Vy1rq5AaRuLzOxct8nz4T6wlgyUR7zLU309k9mBC768=", + "owner": "nix-systems", + "repo": "default", + "rev": "da67096a3b9bf56a91d16901293e51ba5b49a27e", + "type": "github" + }, + "original": { + "owner": "nix-systems", + "repo": "default", + "type": "github" + } + }, + "treefmt-nix": { + "inputs": { + "nixpkgs": "nixpkgs_3" + }, + "locked": { + "lastModified": 1719887753, + "narHash": "sha256-p0B2r98UtZzRDM5miGRafL4h7TwGRC4DII+XXHDHqek=", + "owner": "numtide", + "repo": "treefmt-nix", + "rev": "bdb6355009562d8f9313d9460c0d3860f525bc6c", + "type": "github" + }, + "original": { + "owner": "numtide", + "repo": "treefmt-nix", + "type": "github" + } + } + }, + "root": "root", + "version": 7 +} diff --git a/flake.nix b/flake.nix new file mode 100644 index 0000000..7581a31 --- /dev/null +++ b/flake.nix @@ -0,0 +1,110 @@ +{ + description = "Team Draft"; + + inputs = { + devshell.url = "github:numtide/devshell"; + flake-parts.url = "github:hercules-ci/flake-parts"; + nixpkgs.url = "github:NixOS/nixpkgs/nixos-unstable"; + process-compose-flake.url = "github:Platonic-Systems/process-compose-flake"; + services-flake.url = "github:juspay/services-flake"; + treefmt-nix.url = "github:numtide/treefmt-nix"; + }; + + outputs = inputs @ { + flake-parts, + ... + }: + flake-parts.lib.mkFlake {inherit inputs;} { + imports = [ + inputs.devshell.flakeModule + inputs.process-compose-flake.flakeModule + inputs.treefmt-nix.flakeModule + ]; + systems = ["x86_64-linux" "aarch64-linux" "aarch64-darwin" "x86_64-darwin"]; + perSystem = { + config, + self', + inputs', + pkgs, + system, + ... + }: + with builtins; rec { + # Per-system attributes can be defined here. The self' and inputs' + # module parameters provide easy access to attributes of the same + # system. + process-compose."services" = { + imports = [ + inputs.services-flake.processComposeModules.default + ]; + services.postgres."pg1" = { + enable = true; + package = pkgs.postgresql_16; + extensions = extensions: [ + extensions.pgtap + extensions.pgsodium + ]; + + initialScript.before = replaceStrings ["$"] ["\\$"] (readFile ./src/db/schema.sql); + initialScript.after = replaceStrings ["$"] ["\\$"] (readFile ./src/db/seed.sql); + socketDir = "/tmp/pg1"; + }; + }; + + devshells.default = { + commands = [ + { + name = "docs"; + help = "generate auto docs"; + command = "zig test -femit-docs src/main.zig"; + } + { + name = "db"; + help = "run postgresql"; + command = "nix run .#services"; + } + { + name = "dbreset"; + help = "recreate db and run postgresql"; + command = "rm -rf data && nix run .#services"; + } + { + name = "ci"; + help = "run all tests"; + command = "zig build test && pg_prove"; + } + { + name = "deploy"; + help = "deploy to production lol"; + command = ''zig build -Doptimize=ReleaseFast && scp -r zig-out/bin/* teamdraft: && ssh teamdraft -C "sudo systemctl stop teamdraft && sudo cp ./{teamdraft,scorecorder} /home/teamdraft/ && sudo systemctl start teamdraft"''; + } + ]; + packages = with pkgs; [ + kcov + perl538Packages.TAPParserSourceHandlerpgTAP + pgcli + postgresql_16 + postgresql16Packages.pgtap + sops + zig + ]; + }; + + treefmt.config = { + projectRootFile = "flake.nix"; + + flakeCheck = true; + flakeFormatter = true; + + programs.zig.enable = true; + programs.alejandra.enable = true; + }; + }; + + flake = { + # The usual flake attributes can be defined here, including system- + # agnostic ones like nixosModule and system-enumerating ones, although + # those are more easily expressed in perSystem. + }; + }; +} diff --git a/src/User.zig b/src/User.zig new file mode 100644 index 0000000..5b78fec --- /dev/null +++ b/src/User.zig @@ -0,0 +1,14 @@ +const Allocator = @import("std").mem.Allocator; +id: i32, +username: []const u8, + +pub fn init(allocator: Allocator, id: i32, username: []const u8) !@This() { + return .{ + .id = id, + .username = try allocator.dupe(u8, username), + }; +} + +pub fn deinit(self: @This(), allocator: Allocator) void { + allocator.free(self.username); +} diff --git a/src/app.zig b/src/app.zig new file mode 100644 index 0000000..f6cc157 --- /dev/null +++ b/src/app.zig @@ -0,0 +1,39 @@ +pub const App = struct { + allocator: std.mem.Allocator, + + pool: *pg.Pool, + + // templates: []mustache.Template, + + pub fn init(allocator: std.mem.Allocator) !App { + var env_map = try std.process.getEnvMap(allocator); + defer env_map.deinit(); + + const pool = try pg.Pool.init(allocator, .{ + .size = 5, + .connect = .{ + // .port = 5432, + .unix_socket = env_map.get("PGSQL_SOCKET") orelse "/tmp/pg1/.s.PGSQL.5432", + }, + .auth = .{ + .username = "teamdraft_website", + .database = "teamdraft", + .password = env_map.get("PGSQL_PASSWORD") orelse "teamdraft", + .timeout = 10_000, + }, + }); + + return .{ + .allocator = allocator, + .pool = pool, + }; + } + + pub fn deinit(self: *App) void { + self.pool.deinit(); + } +}; + +const mustache = @import("mustache"); +const pg = @import("pg"); +const std = @import("std"); diff --git a/src/db/schema.sql b/src/db/schema.sql new file mode 100644 index 0000000..ed5e72c --- /dev/null +++ b/src/db/schema.sql @@ -0,0 +1,902 @@ +\set database_name teamdraft +\set schema :database_name +\set owner :database_name +\set rw_name :database_name '_website' +\set ro_name :database_name '_ro' + + +DROP ROLE IF EXISTS ddl; + +CREATE ROLE ddl WITH NOLOGIN; + +DROP ROLE IF EXISTS dml; + +CREATE ROLE dml WITH NOLOGIN; + +DROP ROLE IF EXISTS READ_ONLY; + +CREATE ROLE READ_ONLY WITH NOLOGIN; + +DROP SCHEMA IF EXISTS :schema CASCADE; + +DROP DATABASE IF EXISTS :database_name; + +DROP ROLE IF EXISTS :owner; + +CREATE ROLE :owner WITH LOGIN PASSWORD :'owner'; + +GRANT ddl TO :owner; + +DROP ROLE IF EXISTS :rw_name; + +CREATE ROLE :rw_name WITH LOGIN PASSWORD :'rw_name'; + +GRANT dml TO :rw_name; + +DROP ROLE IF EXISTS :ro_name; + +CREATE ROLE :ro_name WITH LOGIN PASSWORD :'ro_name'; + +GRANT READ_ONLY TO :ro_name; + +ALTER ROLE :owner SET se arch_path TO :schema, pgsodium, public; + +ALTER ROLE :rw_name SET search_path TO :schema, pgsodium, public; + +ALTER ROLE :ro_name SET search_path TO :schema, pgsodium, public; + +CREATE DATABASE :database_name OWNER :owner; + +REVOKE CREATE ON SCHEMA public FROM PUBLIC; + +REVOKE ALL ON DATABASE :database_name FROM PUBLIC; + +GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY; + +\c :database_name +CREATE EXTENSION pgsodium; +GRANT EXECUTE ON ROUTINE pgsodium.randombytes_random TO :owner, :rw_name, :ro_name; +SET ROLE :owner; + +CREATE SCHEMA :schema AUTHORIZATION :schema; + +GRANT USAGE, CREATE ON SCHEMA :schema TO ddl; + +GRANT USAGE ON SCHEMA :schema TO dml, READ_ONLY; + +GRANT ALL ON SCHEMA pgsodium TO dml; + +REVOKE ALL ON DATABASE :database_name FROM PUBLIC; + +GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY; + +ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT +SELECT +, INSERT, UPDATE, DELETE ON TABLES TO dml; + +ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT +SELECT + ON TABLES TO READ_ONLY; + +ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT USAGE, +SELECT + ON SEQUENCES TO dml, READ_ONLY; + +ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT UPDATE ON SEQUENCES TO dml; + +ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT EXECUTE ON ROUTINES TO dml, READ_ONLY; + + +CREATE TABLE :schema.users( + user_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + username text, + PASSWORD text, + name text, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + + CONSTRAINT users_pk PRIMARY KEY (user_id) +); + +CREATE TABLE sessions( + session_id text PRIMARY KEY DEFAULT to_hex(pgsodium.randombytes_random()), + user_id integer NOT NULL, + expires timestamptz NOT NULL DEFAULT now() + interval '1 year', + created timestamptz NOT NULL DEFAULT now(), + modified timestamptz NOT NULL DEFAULT now(), + + CONSTRAINT sessions_users_fk FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TYPE :schema.divisions AS ENUM( + 'nfcn', + 'nfce', + 'nfcs', + 'nfcw', + 'afcn', + 'afce', + 'afcs', + 'afcw' +); + +CREATE TABLE :schema.teams( + team_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY, + name text NOT NULL, + division :schema.divisions NOT NULL, + external_id text, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT teams_pk PRIMARY KEY (team_id), + CONSTRAINT teams_name_ck CHECK (char_length(name) <= 255) +); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Buffalo', 'afce', '134918'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Miami', 'afce', '134919'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('New England', 'afce', '134920'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('NYJ', 'afce', '134921'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Baltimore', 'afcn', '134922'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Cincinnati', 'afcn', '134923'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Cleveland', 'afcn', '134924'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Pittsburgh', 'afcn', '134925'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Houston', 'afcs', '134926'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Indianapolis', 'afcs', '134927'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Jacksonville', 'afcs', '134928'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Tennessee', 'afcs', '134929'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Denver', 'afcw', '134930'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Kansas City', 'afcw', '134931'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Las Vegas', 'afcw', '134932'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('LAC', 'afcw', '135908'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Dallas', 'nfce', '134934'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('NYG', 'nfce', '134935'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Philadelphia', 'nfce', '134936'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Washington', 'nfce', '134937'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Chicago', 'nfcn', '134938'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Detroit', 'nfcn', '134939'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Green Bay', 'nfcn', '134940'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Minnesota', 'nfcn', '134941'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Atlanta', 'nfcs', '134942'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Carolina', 'nfcs', '134943'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('New Orleans', 'nfcs', '134944'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Tampa Bay', 'nfcs', '134945'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Seattle', 'nfcw', '134949'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('San Francisco', 'nfcw', '134948'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('Arizona', 'nfcw', '134946'); + +INSERT INTO :schema.teams(name, division, external_id) + VALUES ('LAR', 'nfcw', '135907'); + +CREATE TABLE :schema.seasons( + season_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY, + started_at timestamptz NOT NULL, + ended_at timestamptz NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT seasons_pk PRIMARY KEY (season_id) +); + +CREATE TABLE :schema.leagues( + league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + name text, + user_id integer NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + + CONSTRAINT leagues_pk PRIMARY KEY (league_id), + CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 255), + CONSTRAINT leagues_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TABLE :schema.league_seasons( + league_season_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + season_id smallint NOT NULL, + league_id integer NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + + UNIQUE (season_id, league_id), + CONSTRAINT league_seasons_pk PRIMARY KEY (league_season_id), + CONSTRAINT league_seasons_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT league_seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES :schema.leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TABLE :schema.league_users( + league_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY, + league_season_id integer NOT NULL, + user_id integer NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + + CONSTRAINT league_users_pk PRIMARY KEY (league_user_id), + CONSTRAINT league_users_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT league_users_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TABLE :schema.rosters( + roster_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + league_user_id bigint NOT NULL, + team_id smallint NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT rosters_pk PRIMARY KEY (roster_id), + CONSTRAINT rosters_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT rosters_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TYPE :schema.draft_status AS ENUM( + 'created', + 'running', + 'complete' +); + +CREATE TABLE :schema.drafts( + draft_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + league_season_id integer NOT NULL, + status :schema.draft_status NOT NULL DEFAULT 'created', + started_at timestamptz NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT drafts_pk PRIMARY KEY (draft_id), + CONSTRAINT drafts_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TABLE :schema.picks( + pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + draft_id integer NOT NULL, + league_user_id integer NOT NULL, + team_id smallint NOT NULL, + auto boolean NOT NULL DEFAULT FALSE, + picked_at timestamptz NOT NULL DEFAULT now(), + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (draft_id, team_id), + CONSTRAINT picks_pk PRIMARY KEY (pick_id), + CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES :schema.drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT picks_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TABLE :schema.rankings( + ranking_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + season_id integer NOT NULL, + team_id integer NOT NULL, + rank smallint NOT NULL, + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + CONSTRAINT rankings_pk PRIMARY KEY (ranking_id), + CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +CREATE TYPE :schema.score_category AS ENUM( + 'win', + 'playoffs', + 'divisional', + 'conference', + 'superbowl', + 'champion' +); + +CREATE TABLE :schema.scores( + score_id integer NOT NULL GENERATED ALWAYS AS IDENTITY, + season_id integer NOT NULL, + team_id integer NOT NULL, + week smallint NOT NULL, + category :schema.score_category NOT NULL DEFAULT 'win', + scored_at timestamptz NOT NULL DEFAULT now(), + created_at timestamptz NOT NULL DEFAULT now(), + modified_at timestamptz NOT NULL DEFAULT now(), + UNIQUE (season_id, team_id, week, category), + CONSTRAINT scores_pk PRIMARY KEY (score_id), + CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION, + CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION +); + +-- functions + +CREATE OR REPLACE FUNCTION :schema.current_season() + RETURNS int + AS $$ + SELECT + season_id + FROM + seasons + WHERE + started_at <= now() + AND ended_at > now() + LIMIT 1; +$$ +LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION season_id_by_year(year text) + RETURNS int + AS $$ + SELECT + season_id + FROM + seasons + WHERE + year::integer >= date_part('year', started_at) + LIMIT 1; +$$ +LANGUAGE SQL; + +CREATE OR REPLACE FUNCTION category_score(category score_category) + RETURNS smallint + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +BEGIN + RETURN( + SELECT + CASE WHEN category = 'win' THEN + 1 + WHEN category IN('superbowl', 'conference') THEN + 10 + ELSE + 5 + END); +END; +$$; + +CREATE OR REPLACE FUNCTION :schema.current_week() + RETURNS smallint + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + current_week smallint; +BEGIN + SELECT + TRUNC(DATE_PART('day', now() - started_at) / 7)::smallint + 1 AS current_week + FROM + seasons + WHERE + season_id = current_season() INTO current_week; + RETURN (IFcurrent_week; +END; +$$; + +CREATE OR REPLACE PROCEDURE new_league_season(league_id integer) +LANGUAGE plpgsql +SECURITY INVOKER +AS $$ +BEGIN + CALL new_league_season(league_id, NULL); +END; +$$; + +CREATE OR REPLACE PROCEDURE new_league_season(league_id integer, draft_time timestamptz) +LANGUAGE plpgsql +SECURITY INVOKER +AS $$ +DECLARE + new_league_season_id integer; + new_draft_id integer; +BEGIN + INSERT INTO league_seasons(season_id, league_id) + VALUES (current_season(), league_id) + RETURNING + league_season_id INTO new_league_season_id; + + IF draft_time IS NOT NULL THEN + INSERT INTO drafts(league_season_id, started_at) + VALUES (new_league_season_id, draft_time) + RETURNING + draft_id INTO new_draft_id; + END IF; +END; +$$; + +CREATE OR REPLACE FUNCTION pick(_draft_id integer, team_id integer) + RETURNS boolean + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + pick_count smallint; + player_1 integer; + player_2 integer; +BEGIN + INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker(_draft_id), team_id); +END; +$$; + +CREATE OR REPLACE FUNCTION current_picker(_draft_id integer) + RETURNS integer + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + pick_count smallint; + player_1 integer; + player_2 integer; +BEGIN + SELECT + count(1) + FROM + picks + WHERE + draft_id = _draft_id INTO pick_count; + SELECT + league_user_id + FROM + league_users + WHERE + league_season_id =( + SELECT + league_season_id + FROM + drafts + WHERE + draft_id = _draft_id) + ORDER BY + league_user_id + LIMIT 1 INTO player_1; + SELECT + league_user_id + FROM + league_users + WHERE + league_season_id =( + SELECT + league_season_id + FROM + drafts + WHERE + draft_id = _draft_id) + ORDER BY + league_user_id DESC + LIMIT 1 INTO player_2; + IF pick_count % 2 = 0 THEN + RETURN player_1; + ELSE + RETURN player_2; + END IF; +END; +$$; + +-- create missing draft picks if any +CREATE OR REPLACE PROCEDURE auto_draft(_draft_id integer) +LANGUAGE plpgsql +SECURITY INVOKER +AS $$ +DECLARE + latest_pick_time timestamptz; + latest_pick picks%ROWTYPE; + pick_count integer; + next_auto_draft_team integer; + draft drafts%ROWTYPE; +BEGIN + SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft; + /* IF now() >= draft.started_at THEN */ + /* UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id; */ + /* END IF; */ + latest_pick_time = draft.started_at; + + /* SELECT */ + /* started_at */ + /* FROM */ + /* drafts */ + /* WHERE */ + /* draft_id = _draft_id INTO latest_pick_time; */ + + IF now() >= latest_pick_time AND draft.status = 'created' THEN + UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id; + END IF; + + LOOP + SELECT + count(1) + FROM + picks + WHERE + draft_id = _draft_id INTO pick_count; + + IF pick_count = 32 THEN + UPDATE drafts SET status = 'complete', modified_at = now() WHERE draft_id = _draft_id; + RETURN; + END IF; + + SELECT + * + FROM + picks + WHERE + draft_id = _draft_id + ORDER BY + picked_at DESC + LIMIT 1 INTO latest_pick; + IF latest_pick IS NOT NULL THEN + latest_pick_time = latest_pick.picked_at; + END IF; + IF (date_part('epoch', now())::integer - date_part('epoch', latest_pick_time)::integer) / 30 >= 1 THEN + SELECT + current_rankings.team_id + FROM + current_rankings + LEFT JOIN picks ON picks.team_id = current_rankings.team_id + WHERE + pick_id IS NULL + ORDER BY + rank ASC + LIMIT 1 INTO next_auto_draft_team; + INSERT INTO picks(draft_id, league_user_id, team_id, auto, picked_at) + VALUES (_draft_id, current_picker(_draft_id), +( + SELECT + current_rankings.team_id + FROM + current_rankings + LEFT JOIN picks ON picks.team_id = current_rankings.team_id + AND picks.draft_id = _draft_id + WHERE + pick_id IS NULL + ORDER BY + rank ASC + LIMIT 1), + TRUE, + latest_pick_time + interval '30 seconds'); + ELSE + EXIT; + END IF; + END LOOP; +END; +$$; + +CREATE OR REPLACE FUNCTION current_round(_draft_id integer) + RETURNS smallint + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + time_now timestamptz; + round smallint; + latest_pick picks%ROWTYPE; + pick_count integer; +BEGIN + time_now = now(); + SELECT + count(1) + FROM + picks + WHERE + draft_id = _draft_id INTO pick_count; + SELECT + * + FROM + picks + WHERE + draft_id = _draft_id + ORDER BY + created_at DESC + LIMIT 1 INTO latest_pick; + IF latest_pick IS NOT NULL THEN + time_now = latest_pick.created_at; + END IF; + SELECT + (date_part('epoch', time_now)::integer - date_part('epoch', started_at)::integer) / 30 + FROM + drafts + WHERE + draft_id = _draft_id INTO round; + IF round >= 32 THEN + RETURN 32; + ELSE + RETURN (round); + END IF; +END; +$$; + +CREATE OR REPLACE FUNCTION record_external_score_by_year(_external_id text, year int, week smallint, category score_category, scored_at timestamptz) + RETURNS int + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + team_id integer; + season_id integer; +BEGIN + SELECT seasons.season_id FROM seasons WHERE date_part('year', started_at) = year ORDER BY started_at DESC LIMIT 1 INTO season_id; + SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id; + RETURN (SELECT record_score(team_id, week, season_id, category, scored_at)); +END; +$$; + +CREATE OR REPLACE FUNCTION record_external_score(_external_id text, season_id int, week smallint, category score_category, scored_at timestamptz) + RETURNS int + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + team_id integer; +BEGIN + SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id; + RETURN (SELECT record_score(team_id, week, season_id, category, scored_at)); +END; +$$; + + +CREATE OR REPLACE FUNCTION record_score(team_id int, week smallint, season_id int, category score_category, scored_at timestamptz) + RETURNS int + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + new_score_id integer; +BEGIN + INSERT INTO scores(season_id, team_id, week, category, scored_at) + VALUES (season_id, team_id, week, category, scored_at) + ON CONFLICT ON CONSTRAINT scores_season_id_team_id_week_category_key + DO UPDATE SET modified_at = NOW(), scored_at = EXCLUDED.scored_at + RETURNING + score_id INTO new_score_id; + RETURN new_score_id; +END; +$$; + +CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int) + RETURNS TABLE (team_id smallint, + rank smallint, + name text, + division divisions, + pick_user text) + LANGUAGE plpgsql + SECURITY INVOKER + AS $$ +DECLARE + draft_season_id integer; +BEGIN + SELECT league_seasons.season_id FROM drafts JOIN league_seasons USING (league_season_id) WHERE draft_id = _draft_id INTO draft_season_id; +RETURN QUERY + WITH season_rankings AS ( + SELECT + teams.team_id, + rankings.rank, + teams.name, + teams.division + FROM + teams + LEFT JOIN rankings ON rankings.team_id = teams.team_id + AND rankings.season_id = draft_season_id + ORDER BY + rank + ) + SELECT r.*, users.name AS pick_user + FROM season_rankings r + LEFT JOIN picks ON r.team_id = picks.team_id AND picks.draft_id = _draft_id + LEFT JOIN league_users ON league_users.league_user_id = picks.league_user_id + LEFT JOIN users ON users.user_id = league_users.user_id; +END; +$$; + +-- views +CREATE OR REPLACE VIEW draft_info AS + WITH latest_draft_pick AS ( + SELECT draft_id, MAX(picked_at) AS picked_at FROM picks GROUP BY draft_id + ) + select draft_id, current_picker(draft_id), status, + leagues.name as league, + CASE WHEN status = 'complete' OR status = 'created' THEN 0 +ELSE date_part('epoch', (coalesce(latest_draft_pick.picked_at, started_at) + interval '30 seconds') - now())::integer + END AS round_time_remaining, + CASE + WHEN status = 'complete' THEN 'The draft is over, good luck!' + WHEN status = 'created' THEN 'The draft will start in ' || to_char(started_at - now(), 'MI:SS') + WHEN status = 'running' THEN 'It''s ' || + (SELECT u.name FROM league_users lu JOIN users u ON u.user_id = lu.user_id + WHERE lu.league_user_id = current_picker(draft_id)) || '''s turn!' + END as message, + status = 'running' as can_pick + from drafts + JOIN league_seasons USING(league_season_id) + JOIN leagues USING(league_id) + LEFT JOIN latest_draft_pick USING(draft_id); + +CREATE OR REPLACE VIEW league_picks AS +SELECT + league_id, + teams.team_id, + teams.name AS team, + users.name AS player +FROM + picks + JOIN teams USING (team_id) + JOIN league_users USING (league_user_id) + JOIN users USING (user_id) + JOIN league_seasons USING (league_season_id) +ORDER BY + picks.created_at; + +CREATE OR REPLACE VIEW season_player_scores AS +WITH season_scores AS ( + SELECT + season_id, + team_id, + sum(category_score(category))::integer AS points + FROM + scores + GROUP BY + season_id, + team_id +) +SELECT + season_id, + league_id, + player, + sum(coalesce(points, 0))::integer AS score +FROM + league_picks + LEFT OUTER JOIN season_scores USING (team_id) +GROUP BY + season_id, + league_id, + player +ORDER BY + score DESC; + +CREATE OR REPLACE VIEW current_player_scores AS +WITH current_scores AS ( + SELECT + team_id, + sum(category_score(category))::integer AS points + FROM + scores + WHERE + season_id = current_season() + GROUP BY + team_id +) +SELECT + league_id, + player, + sum(coalesce(points, 0))::integer AS score +FROM + league_picks + LEFT OUTER JOIN current_scores USING (team_id) +GROUP BY + league_id, + player +ORDER BY + score DESC; + +CREATE OR REPLACE VIEW league_scores AS +WITH season_scores AS ( + SELECT + season_id, + team_id, + SUM(CASE WHEN category = 'win' THEN + category_score('win') + END)::smallint AS win, + SUM(CASE WHEN category = 'playoffs' THEN + category_score('playoffs') + END)::smallint AS playoffs, + SUM(CASE WHEN category = 'divisional' THEN + category_score('divisional') + END)::smallint AS divisional, + SUM(CASE WHEN category = 'conference' THEN + category_score('conference') + END)::smallint AS conference, + SUM(CASE WHEN category = 'superbowl' THEN + category_score('superbowl') + END)::smallint AS superbowl, + SUM(CASE WHEN category = 'champion' THEN + category_score('champion') + END)::smallint AS champion, + coalesce(SUM(category_score(category)), 0)::smallint AS total + FROM + scores + GROUP BY + season_id, + team_id +) +SELECT + season_id, + league_id, + team, + player, + coalesce(win, 0) AS win, + playoffs, + divisional, + conference, + superbowl, + champion, + coalesce(total, 0) AS total +FROM + league_picks + LEFT OUTER JOIN season_scores USING (team_id); + + +CREATE OR REPLACE VIEW :schema.current_rankings AS +SELECT + teams.team_id, + rank, + name, + division +FROM + teams + LEFT JOIN rankings ON rankings.team_id = teams.team_id + AND rankings.season_id = current_season() + ORDER BY + rank; + +CREATE OR REPLACE FUNCTION random_invite() + RETURNS varchar + LANGUAGE plpgsql +AS $$ +DECLARE + base10 bigint; + base36 varchar := ''; + intval bigint; + char0z char[] := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', ''); +BEGIN + SELECT floor(random() * 78_364_164_095 + 2_176_782_336) INTO base10; + intval := abs(base10); + WHILE intval != 0 LOOP + base36 := char0z[(intval % 36)+1] || base36; + intval := intval / 36; + END LOOP; + + IF base10 = 0 THEN base36 := '0'; END IF; + RETURN base36; +END; +$$; diff --git a/src/db/seed.sql b/src/db/seed.sql new file mode 100644 index 0000000..37bb2f9 --- /dev/null +++ b/src/db/seed.sql @@ -0,0 +1,68 @@ +\set database_name teamdraft +\set schema :database_name +\set rw_name :database_name '_website' +\c :database_name +SET ROLE :rw_name; +SET search_path TO :schema, public; + +begin; +do +$$ +declare + player_1_user_id int; + player_2_user_id int; + new_league_id int; + new_draft_id int; + new_league_season_id int; +begin + +INSERT INTO seasons (started_at, ended_at) VALUES (NOW(), NOW() + interval '3 months'); + +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 1, 1); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 2, 2); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 3, 3); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 4, 4); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 5, 5); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 6, 6); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 7, 7); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 8, 8); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 9, 9); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 10, 10); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 11, 11); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 12, 12); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 13, 13); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 14, 14); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 15, 15); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 16, 16); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 17, 17); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 18, 18); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 19, 19); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 20, 20); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 21, 21); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 22, 22); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 23, 23); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 24, 24); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 25, 25); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 26, 26); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 27, 27); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 28, 28); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 29, 29); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 30, 30); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 31, 31); +INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 32, 32); + +INSERT INTO users (username, name) VALUES ('kent', 'Kent') RETURNING user_id INTO player_1_user_id; +INSERT INTO users (username, name) VALUES ('miranda', 'Miranda') RETURNING user_id INTO player_2_user_id; + +INSERT INTO leagues (name, user_id) VALUES ('my league', player_1_user_id) RETURNING league_id INTO new_league_id; +INSERT INTO league_seasons (season_id, league_id) VALUES (current_season(), new_league_id) RETURNING league_season_id INTO new_league_season_id; + +INSERT INTO league_users (league_season_id, user_id) VALUES (new_league_season_id, player_1_user_id); +INSERT INTO league_users (league_season_id, user_id) VALUES (new_league_season_id, player_2_user_id); + +INSERT INTO drafts (league_season_id, started_at) VALUES (new_league_season_id, NOW()) RETURNING draft_id INTO new_draft_id; + +end; + +$$; +commit; diff --git a/src/env.zig b/src/env.zig new file mode 100644 index 0000000..5796d51 --- /dev/null +++ b/src/env.zig @@ -0,0 +1,7 @@ +pub const Env = struct { + app: *App, + user: ?User = null, +}; + +const App = @import("app.zig").App; +const User = @import("User.zig"); diff --git a/src/main.zig b/src/main.zig new file mode 100644 index 0000000..e1f0928 --- /dev/null +++ b/src/main.zig @@ -0,0 +1,25 @@ +pub fn main() !void { + var gpa = std.heap.GeneralPurposeAllocator(.{}){}; + const allocator = gpa.allocator(); + + // initialize a logging pool + try logz.setup(allocator, .{ + .level = .Info, + .pool_size = 100, + .buffer_size = 4096, + .large_buffer_count = 8, + .large_buffer_size = 16384, + .output = .stdout, + .encoding = .logfmt, + }); + defer logz.deinit(); + + var app = try App.init(allocator); + defer app.deinit(); + + try @import("web/web.zig").start(&app); +} + +const logz = @import("logz"); +const std = @import("std"); +const App = @import("app.zig").App; diff --git a/src/root.zig b/src/root.zig new file mode 100644 index 0000000..ecfeade --- /dev/null +++ b/src/root.zig @@ -0,0 +1,10 @@ +const std = @import("std"); +const testing = std.testing; + +export fn add(a: i32, b: i32) i32 { + return a + b; +} + +test "basic add functionality" { + try testing.expect(add(3, 7) == 10); +} diff --git a/src/scorecorder.zig b/src/scorecorder.zig new file mode 100644 index 0000000..3d72dc1 --- /dev/null +++ b/src/scorecorder.zig @@ -0,0 +1,175 @@ +const Round = struct { + events: []Event, +}; + +const Event = struct { + strEvent: []u8, + idHomeTeam: []u8, + strHomeTeam: []u8, + idAwayTeam: []u8, + strAwayTeam: []u8, + intHomeScore: ?[]u8 = null, + intAwayScore: ?[]u8 = null, + strStatus: []u8, + dateEvent: []u8, + strTimestamp: []u8, +}; + +const Config = struct { + round: i16, + season: i16, + all_rounds: bool = false, +}; + +var app: App = undefined; +var config: Config = undefined; +var client: zul.http.Client = undefined; + +pub fn main() !void { + var gpa = std.heap.GeneralPurposeAllocator(.{}){}; + const allocator = gpa.allocator(); + + // initialize a logging pool + try logz.setup(allocator, .{ + .level = .Info, + .pool_size = 100, + .buffer_size = 4096, + .large_buffer_count = 8, + .large_buffer_size = 16384, + .output = .stdout, + .encoding = .logfmt, + }); + defer logz.deinit(); + + app = try App.init(allocator); + defer app.deinit(); + + var args = try zul.CommandLineArgs.parse(allocator); + defer args.deinit(); + + client = zul.http.Client.init(allocator); + defer client.deinit(); + + config = .{ + // .round = try std.fmt.parseInt(i16, args.get("round"), 10) orelse currentWeek(), + .round = try weekToRound(if (args.contains("round")) try std.fmt.parseInt(i16, args.get("round").?, 10) else currentRound()), + .season = if (args.contains("season")) try std.fmt.parseInt(i16, args.get("season").?, 10) else currentSeason(), + .all_rounds = args.contains("all"), + }; + logz.info() + .int("round", config.round) + .int("season", config.season) + .boolean("all_rounds", config.all_rounds) + .log(); + + if (config.all_rounds) { + for (1..23) |round| { + try recordRound(allocator, @intCast(round)); + std.posix.nanosleep(1, 0); + } + } else { + try recordRound(allocator, config.round); + } +} + +fn recordRound(allocator: std.mem.Allocator, round: i16) !void { + var req = try client.request("https://www.thesportsdb.com/api/v1/json/3/eventsround.php"); + defer req.deinit(); + // id is the league id, with 4391 == NFL + try req.query("id", "4391"); + // the round - which corresponds to the week or playoff id + try req.query("r", try std.fmt.allocPrint(allocator, "{d}", .{round})); + // the season year + try req.query("s", try std.fmt.allocPrint(allocator, "{d}", .{config.season})); + + const res = try req.getResponse(.{}); + if (res.status != 200) { + // TODO: handle error + return; + } + var managed = try res.json(Round, allocator, .{ .ignore_unknown_fields = true }); + defer managed.deinit(); + const parsed_round = managed.value; + for (parsed_round.events) |event| { + logz.info() + .fmt("event", "{s} {s} @ {s} {s}", .{ event.strAwayTeam, event.intAwayScore orelse "0", event.strHomeTeam, event.intHomeScore orelse "0" }) + .string("date", event.strTimestamp) + .string("status", event.strStatus) + .log(); + + if (!std.mem.eql(u8, "", event.strStatus) and !std.mem.eql(u8, "FT", event.strStatus) and !std.mem.eql(u8, "AOT", event.strStatus) and !std.mem.eql(u8, "Match Finished", event.strStatus)) { + logz.info().fmt("event", "{s} in progress or not started.", .{event.strEvent}).log(); + continue; + } + + const home_score = try std.fmt.parseInt(usize, event.intHomeScore orelse "0", 10); + const away_score = try std.fmt.parseInt(usize, event.intAwayScore orelse "0", 10); + + const winner_id: []u8 = if (home_score > away_score) event.idHomeTeam else event.idAwayTeam; + + logz.info().string("winner", winner_id).log(); + try insertScore(winner_id, "win", event.strTimestamp); + + if (config.round == 160 or config.round == 125) { + logz.info().string("playoffs", "Home Team made it to the playoffs").log(); + try insertScore(event.idHomeTeam, "playoffs", event.strTimestamp); + logz.info().string("playoffs", "Away Team made it to the playoffs").log(); + try insertScore(event.idAwayTeam, "playoffs", event.strTimestamp); + } + + const category = switch (config.round) { + 160 => "divisional", + 125 => "conference", + 150 => "superbowl", + 200 => "champion", + else => null, + }; + + if (category) |c| { + logz.info().fmt("category", "Inserting {s} score for winner", .{c}).log(); + try insertScore(winner_id, c, event.strTimestamp); + } + } +} + +pub fn insertScore(external_id: []u8, category: []const u8, timestamp: []const u8) !void { + _ = app.pool.exec("SELECT record_external_score_by_year($1, $2, $3, $4, $5)", .{ external_id, config.season, config.round, @constCast(category), timestamp }) catch @panic("failed to record score"); +} + +fn currentRound() i16 { + var current_week = (app.pool.row("SELECT current_week()", .{}) catch @panic("can't fetch current week")) orelse unreachable; + defer current_week.deinit() catch {}; + return current_week.get(i16, 0); +} + +fn currentSeason() i16 { + const season_query = + \\SELECT date_part('year', started_at)::smallint + \\FROM seasons WHERE season_id = current_season(); + ; + var current_season = (app.pool.row(season_query, .{}) catch @panic("can't fetch current season")) orelse unreachable; + defer current_season.deinit() catch {}; + return current_season.get(i16, 0); +} + +fn weekToRound(week: i16) !i16 { + return switch (week) { + 1...18 => |w| w, + 19 => 160, + 20 => 125, + 21 => 150, + 23 => 200, + else => return error.UnknownRound, + }; +} + +test "weekToRound" { + try std.testing.expect(try weekToRound(1) == 1); + try std.testing.expectError(error.UnknownRound, weekToRound(55)); +} + +const App = @import("app.zig").App; + +const logz = @import("logz"); +const std = @import("std"); +const zul = @import("zul"); diff --git a/src/views/about.mustache b/src/views/about.mustache new file mode 100644 index 0000000..f087c0f --- /dev/null +++ b/src/views/about.mustache @@ -0,0 +1,36 @@ +<p>Team Draft is a 2-person sports team fantasy scoring league, where you draft sports teams and score points throughout the season based on wins and playoff appearances. The idea was taken from Mina Kimes's yearly NFL Team Draft with Mike Golic, Jr. on the <a href="https://www.espn.com/radio/play/_/id/32078500">Mina Kimes Show podcast</a>.</p> +<h2><a id="scoring">Scoring</a></h2> +<h3>NFL</h3> +<table class="striped"> + <thead> + <tr> + <th scope="col">When your team:</th> + <th scope="col">You score:</th> + </tr> + </thead> + <tbody> + <tr> + <td>Wins</th> + <td>1</td> + </tr> + <tr> + <td>Makes the playoffs</th> + <td>5</td> + </tr> + <tr> + <td>Makes the divisional round</th> + <td>5</td> + </tr> + <tr> + <td>Makes the conference championship game</th> + <td>10</td> + </tr> + <tr> + <td>Wins the Super Bowl</th> + <td>5</td> + </tr> + </tbody> +</table> +<h2>Who made this?</h2> +<p>This was made by <a href="https://sadbeast.com">me</a>, and it's <a href="https://git.sadbeast.com/teamdraft">open source</a>.</p> +<p>If you have any issues, reach out at <a href="mailto:sadbeast@sadbeast.com">sadbeast@sadbeast.com</a>.</p> diff --git a/src/views/draft.mustache b/src/views/draft.mustache new file mode 100644 index 0000000..a7e52e8 --- /dev/null +++ b/src/views/draft.mustache @@ -0,0 +1,27 @@ +<div hx-get="/drafts/{{draft_id}}" hx-trigger="every 1s"> + <strong>{{user.id}}</strong> +<!-- <div hx-get="/drafts/{{draft_info.draft_id}}"> --> +{{#draft_info}} +<h1>{{league}}</h1> +<div class="sticky"> + <p>{{message}}</p> + {{#can_pick}}<p>Round Time Remaining: {{round_time_remaining}}</p>{{/can_pick}} +</div> +{{/draft_info}} + <table class="striped"> + <thead> + <tr> + <th>Team</th> + <th>Division</th> + <th colspan="2">Rank</th> + </tr> + </thead> + <tbody> + {{#teams}} + <tr id="team-{{team_id}}"> + {{> pick}} + </tr> + {{/teams}} + </tbody> + </table> +</div> diff --git a/src/views/index.mustache b/src/views/index.mustache new file mode 100644 index 0000000..29f8ed9 --- /dev/null +++ b/src/views/index.mustache @@ -0,0 +1,46 @@ + <div class="grid"> + <div> + <p>Welcome! Team Draft is a 2-person fantasy sports league, where you draft entire sports teams - not players - and score points throughout the season based on wins and playoff appearances. The idea was taken from the <a href="https://www.espn.com/radio/play/_/id/32078500">Mina Kimes Show's yearly NFL Team Draft</a>.</p> + <ul> + <li>Create a live draft to pick your teams!</li> + <li>Manage players' teams manually to support trades</li> + <li>Keeps track of <a hx-boost="false" href="/leagues/2?season=2023">game scores</a> automatically</li> + </ul> + <p>Create a league now, and schedule a live draft!</p> + </div> + <div> + <article> + <header>2014 NFL Season</header> + <form> + <fieldset> + <label> + League name + <input name="first_name" placeholder="Pat & Sam's League" required/> + </label> + </fieldset> + <fieldset class="grid"> + <label> + Your name + <input name="player1" placeholder="Pat" autocomplete="given-name" required /> + </label> + <label> + Opponent's name + <input name="player2" placeholder="Sam" required /> + </label> + </fieldset> + <fieldset class="grid"> + <label> + <input type="checkbox" name="draft" role="switch" checked /> + Schedule a draft + </label> + <label> + <input type="datetime-local" name="draft_time" aria-label="Draft start time" /> + </label> + </fieldset> + + <input type="submit" value="Create league" disabled/> + <small>Coming soon...</small> + </form> + </article> + </div> + </div> diff --git a/src/views/layout.mustache b/src/views/layout.mustache new file mode 100644 index 0000000..614b150 --- /dev/null +++ b/src/views/layout.mustache @@ -0,0 +1,70 @@ +<!doctype html> +<html lang="en"> + <head> + <meta charset="utf-8"> + <meta name="viewport" content="width=device-width, initial-scale=1"> + <meta name="color-scheme" content="light dark" /> + <link rel="preconnect" href="https://fonts.googleapis.com"> + <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin> + <link href="https://fonts.googleapis.com/css2?family=Atkinson+Hyperlegible:ital,wght@0,400;0,700;1,400;1,700&family=Teko:wght@300..700&display=swap" rel="stylesheet"> + + <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@picocss/pico@2/css/pico.min.css" /> + <script src="https://unpkg.com/htmx.org@2.0.0" integrity="sha384-wS5l5IKJBvK6sPTKa2WZ1js3d947pvWXbPJ1OmWfEuxLgeHcEbjUUA5i9V5ZkpCw" crossorigin="anonymous"></script> + <title>Team Draft{{#title}} - {{.}} {{/title}}</title> +<style> +:root { + --pico-font-family: "Atkinson Hyperlegible", sans-serif; + } +.logo { +font-family: "Teko", sans-serif; +font-optical-sizing: auto; +font-weight: 300; +font-style: normal; +font-size: 40px; +line-height: 1em; +color: inherit; +} + +.logo:hover { +color: inherit; +text-decoration: none; +} + +.logo span { +color: indianred; +} +body { + --pico-font-family: "Atkinson Hyperlegible", sans-serif; + --pico-font-weight: 400; +font-style: normal; +} +.sticky, th { + position: -webkit-sticky; /* Safari */ + position: sticky; +} +th { + top: 1.5em; +} +.sticky { + top: 0; + background-color: var(--pico-background-color); + color: var(--pico-color); +} +</style> + </head> + <body hx-boost="true" hx-target="#content"> + <main class="container"> + <nav> + <ul> + <li><a href="/" class="logo">team<span>draft</span></a></li> + </ul> + <ul> + <li><a href="/about">about</a></li> + </ul> + </nav> + <div id="content"> + {{#content}} {{{.}}} {{/content}} + </div> + </main> + </body> +</html> diff --git a/src/views/league.mustache b/src/views/league.mustache new file mode 100644 index 0000000..46af60a --- /dev/null +++ b/src/views/league.mustache @@ -0,0 +1,36 @@ +<h1>{{league.name}}</h1> +<div class="grid"> + {{#player_scores}} + <div><strong>{{player}} - {{score}}</strong></div> + {{/player_scores}} +</div> +<main class="container"> +<table class="striped"> + <thead> + <tr> + <th scope="col">Team</th> + <th scope="col">Player</th> + <th scope="col" style="width: 11em">Total</th> + </tr> + </thead> + <tbody> + {{#scores}} + <tr> + <td scope="row">{{team}}</td> + <td>{{player}}</td> + <td> + <a href="#">{{total}}</a> + <p> + <small><strong>Wins:</strong> {{win}} + {{#playoffs}}, <strong>Made the Playoffs:</strong> {{.}}{{/playoffs}} + {{#divisional}}, <strong>Made the Divisional Round:</strong> {{.}}{{/divisional}} + {{#conference}}, <strong>Made the Conference Championship:</strong> {{.}}{{/conference}} + {{#champion}}, <strong>Superbowl Winner:</strong> {{.}}{{/champion}} + </small> + </p> + </td> + </tr> + {{/scores}} + </tbody> +</table> +</main> diff --git a/src/views/pick.mustache b/src/views/pick.mustache new file mode 100644 index 0000000..d045f6f --- /dev/null +++ b/src/views/pick.mustache @@ -0,0 +1,4 @@ + <td>{{name}}</td> + <td>{{division}}</td> + <td>{{rank}}</td> + <td>{{pick_user}}{{^pick_user}}{{#draft_info}}{{#can_pick}}<button hx-get="/drafts/{{draft_id}}/pick?team_id={{team_id}}" hx-target="#team-{{team_id}}">Pick</button>{{/can_pick}}{{/draft_info}}{{/pick_user}}</td> diff --git a/src/web/dispatcher.zig b/src/web/dispatcher.zig new file mode 100644 index 0000000..77ea282 --- /dev/null +++ b/src/web/dispatcher.zig @@ -0,0 +1,52 @@ +pub const Dispatcher = struct { + app: *App, + // whether to try loading the user or not, this implies requires_user = false + load_user: bool = true, + + // whether a user is required. When false, if we have a token, the user is still + // loaded (unless load_user = false). + requires_user: bool = false, + + pub fn dispatch(self: *const Dispatcher, action: httpz.Action(*Env), req: *httpz.Request, res: *httpz.Response) !void { + const app = self.app; + + var env = Env{ + .app = app, + }; + // defer env.deinit(); + // + try self.doDispatch(action, req, res, &env); + } + + fn doDispatch(_: *const Dispatcher, action: httpz.Action(*Env), req: *httpz.Request, res: *httpz.Response, env: *Env) !void { + const query = try req.query(); + const user = try loadUser(env.app, query.get("user")); + + if (user) |u| { + env.user = u; + std.debug.print("user: {}, {s}\n", .{ u.id, u.username }); + } + try action(env, req, res); + } +}; + +fn loadUser(app: *App, optional_session_id: ?[]const u8) !?User { + const session_id = optional_session_id orelse return null; + + var user: User = undefined; + const row = try app.pool.row("SELECT user_id, username FROM users WHERE user_id = $1", .{session_id}); + if (row) |r| { + user = try r.to(User, .{}); + } else { + return null; + } + + return try User.init(app.allocator, user.id, user.username); +} + +const httpz = @import("httpz"); +const std = @import("std"); + +const App = @import("../app.zig").App; +const User = @import("../User.zig"); +const Env = @import("../env.zig").Env; diff --git a/src/web/drafts/pick.zig b/src/web/drafts/pick.zig new file mode 100644 index 0000000..f710845 --- /dev/null +++ b/src/web/drafts/pick.zig @@ -0,0 +1,41 @@ +const Team = struct { + team_id: i16, + rank: ?i16, + name: []const u8, + division: []const u8, + league_user_id: ?i32, + pick_user: ?[]const u8, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + const query = try req.query(); + if (query.get("team_id")) |team_id| { + if (req.params.get("id")) |draft_id| { + _ = try env.app.pool.exec("CALL auto_draft($1)", .{draft_id}); + _ = try env.app.pool.exec("INSERT INTO picks (league_user_id, draft_id, team_id) VALUES (current_picker($1), $1, $2)", .{ draft_id, team_id }); + var result = try env.app.pool.query("SELECT cr.*, picks.league_user_id, users.name AS pick_user FROM current_rankings cr LEFT JOIN picks ON cr.team_id = picks.team_id AND picks.draft_id = $1 LEFT JOIN league_users USING(league_user_id) LEFT JOIN users USING (user_id) WHERE cr.team_id = $2", .{ draft_id, team_id }); + defer result.deinit(); + + var team: Team = undefined; + while (try result.next()) |row| { + team = try row.to(Team, .{}); + } + try web.renderWithDataPartials("pick", .{}, .{ + .team_id = team.team_id, + .rank = team.rank, + .name = team.name, + .pick_user = team.pick_user, + .division = team.division, + .league_user_id = team.league_user_id, + .draft_id = draft_id, + }, req, res); + } + } else { + @panic("oh no"); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/drafts/show.zig b/src/web/drafts/show.zig new file mode 100644 index 0000000..cd73438 --- /dev/null +++ b/src/web/drafts/show.zig @@ -0,0 +1,55 @@ +const Team = struct { + team_id: i16, + rank: ?i16, + name: []const u8, + division: []const u8, + pick_user: ?[]const u8, +}; + +const DraftInfo = struct { + draft_id: i32, + current_picker: i32, + status: []const u8, + league: []const u8, + round_time_remaining: ?i32, + message: []const u8, + can_pick: bool, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + if (req.params.get("id")) |draft_id| { + _ = try env.app.pool.exec("CALL auto_draft($1)", .{draft_id}); + + const picks_query = "SELECT * FROM current_draft_picks($1)"; + var picks_result = try env.app.pool.query(picks_query, .{draft_id}); + defer picks_result.deinit(); + + var teams: [32]Team = undefined; + var i: u8 = 0; + while (try picks_result.next()) |row| : (i += 1) { + const team = try row.to(Team, .{}); + teams[i] = team; + } + + var draft_info: DraftInfo = undefined; + const current_picker_query = + \\SELECT * FROM draft_info WHERE draft_id = $1 + ; + var row = try env.app.pool.row(current_picker_query, .{draft_id}); + if (row) |r| { + draft_info = try r.to(DraftInfo, .{}); + } + + try web.renderWithDataPartials("draft", .{ "pick", web.templates.get("pick").? }, .{ + .teams = teams, + .draft_info = draft_info, + .draft_id = draft_info.draft_id, + }, req, res); + try row.?.deinit(); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/leagues/create.zig b/src/web/leagues/create.zig new file mode 100644 index 0000000..66352cc --- /dev/null +++ b/src/web/leagues/create.zig @@ -0,0 +1,78 @@ +const League = struct { + name: []const u8, +}; + +const LeaguePickScore = struct { + league_id: i32, + team: []const u8, + player: []const u8, + win: i32, + playoffs: ?i16, + divisional: ?i16, + conference: ?i16, + superbowl: ?i16, + champion: ?i16, + total: i32, +}; + +const PlayerScore = struct { + league_id: i32, + player: []const u8, + score: i32, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + if (req.params.get("id")) |league_id| { + var league: League = undefined; + const league_query = + \\SELECT name FROM leagues WHERE league_id = $1 + ; + { + const row = try env.app.pool.row(league_query, .{league_id}); + if (row) |r| { + league = try r.to(League, .{}); + } + } + + const scores_query = + \\SELECT * FROM league_pick_scores WHERE league_id = $1 + ; + var scores_result = try env.app.pool.query(scores_query, .{league_id}); + defer scores_result.deinit(); + + var scores: [32]LeaguePickScore = undefined; + { + var i: u8 = 0; + while (try scores_result.next()) |row| : (i += 1) { + const score = try row.to(LeaguePickScore, .{}); + scores[i] = score; + } + } + + const player_scores_query = + \\SELECT * FROM player_scores WHERE league_id = $1 + ; + var player_scores_result = try env.app.pool.query(player_scores_query, .{league_id}); + defer player_scores_result.deinit(); + + var player_scores: [2]PlayerScore = undefined; + { + var i: u8 = 0; + while (try player_scores_result.next()) |row| : (i += 1) { + const score = try row.to(PlayerScore, .{}); + player_scores[i] = score; + } + } + + try web.renderWithData("league", .{ + .league = league, + .scores = scores, + .player_scores = player_scores, + }, req, res); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/leagues/invite.zig b/src/web/leagues/invite.zig new file mode 100644 index 0000000..66352cc --- /dev/null +++ b/src/web/leagues/invite.zig @@ -0,0 +1,78 @@ +const League = struct { + name: []const u8, +}; + +const LeaguePickScore = struct { + league_id: i32, + team: []const u8, + player: []const u8, + win: i32, + playoffs: ?i16, + divisional: ?i16, + conference: ?i16, + superbowl: ?i16, + champion: ?i16, + total: i32, +}; + +const PlayerScore = struct { + league_id: i32, + player: []const u8, + score: i32, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + if (req.params.get("id")) |league_id| { + var league: League = undefined; + const league_query = + \\SELECT name FROM leagues WHERE league_id = $1 + ; + { + const row = try env.app.pool.row(league_query, .{league_id}); + if (row) |r| { + league = try r.to(League, .{}); + } + } + + const scores_query = + \\SELECT * FROM league_pick_scores WHERE league_id = $1 + ; + var scores_result = try env.app.pool.query(scores_query, .{league_id}); + defer scores_result.deinit(); + + var scores: [32]LeaguePickScore = undefined; + { + var i: u8 = 0; + while (try scores_result.next()) |row| : (i += 1) { + const score = try row.to(LeaguePickScore, .{}); + scores[i] = score; + } + } + + const player_scores_query = + \\SELECT * FROM player_scores WHERE league_id = $1 + ; + var player_scores_result = try env.app.pool.query(player_scores_query, .{league_id}); + defer player_scores_result.deinit(); + + var player_scores: [2]PlayerScore = undefined; + { + var i: u8 = 0; + while (try player_scores_result.next()) |row| : (i += 1) { + const score = try row.to(PlayerScore, .{}); + player_scores[i] = score; + } + } + + try web.renderWithData("league", .{ + .league = league, + .scores = scores, + .player_scores = player_scores, + }, req, res); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/leagues/show.zig b/src/web/leagues/show.zig new file mode 100644 index 0000000..2bbdabd --- /dev/null +++ b/src/web/leagues/show.zig @@ -0,0 +1,80 @@ +const League = struct { + name: []const u8, +}; + +const LeaguePickScore = struct { + season_id: i32, + league_id: i32, + team: []const u8, + player: []const u8, + win: i32, + playoffs: ?i16, + divisional: ?i16, + conference: ?i16, + superbowl: ?i16, + champion: ?i16, + total: i32, +}; + +const PlayerScore = struct { + player: []const u8, + score: i32, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + if (req.params.get("id")) |league_id| { + const query = try req.query(); + var league: League = undefined; + const league_query = + \\SELECT name FROM leagues WHERE league_id = $1 + ; + var league_result = (try env.app.pool.row(league_query, .{league_id})) orelse { + res.status = 404; + return; + }; + defer league_result.deinit() catch {}; + + league = try league_result.to(League, .{}); + + var team_scores_result: *pg.Result = undefined; + var player_scores_result: *pg.Result = undefined; + + if (query.get("season")) |season_id| { + player_scores_result = try env.app.pool.query("SELECT player, score FROM season_player_scores WHERE league_id = $1 AND season_id = season_id_by_year($2)", .{ league_id, season_id }); + team_scores_result = try env.app.pool.query("SELECT * FROM league_scores WHERE league_id = $1 AND season_id = season_id_by_year($2)", .{ league_id, season_id }); + } else { + player_scores_result = try env.app.pool.query("SELECT player, score FROM current_player_scores WHERE league_id = $1", .{league_id}); + team_scores_result = try env.app.pool.query("SELECT * FROM league_scores WHERE league_id = $1 AND season_id = current_season()", .{league_id}); + } + defer player_scores_result.deinit(); + defer team_scores_result.deinit(); + + var scores: [32]LeaguePickScore = undefined; + var score_count: u8 = 0; + while (try team_scores_result.next()) |row| : (score_count += 1) { + const score = try row.to(LeaguePickScore, .{ .dupe = true }); + scores[score_count] = score; + } + + var player_scores: [2]PlayerScore = undefined; + { + var i: u8 = 0; + while (try player_scores_result.next()) |row| : (i += 1) { + const score = try row.to(PlayerScore, .{}); + player_scores[i] = score; + } + } + + try web.renderWithData("league", .{ + .league = league, + .scores = scores[0..score_count], + .player_scores = player_scores, + }, req, res); + } +} + +const httpz = @import("httpz"); +const pg = @import("pg"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/picks/pick.zig b/src/web/picks/pick.zig new file mode 100644 index 0000000..51e05c4 --- /dev/null +++ b/src/web/picks/pick.zig @@ -0,0 +1,37 @@ +const Team = struct { + team_id: i16, + rank: ?i16, + name: []const u8, + division: []const u8, + league_user_id: ?i32, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + const query = try req.query(); + if (query.get("team_id")) |team_id| { + if (req.params.get("id")) |draft_id| { + var result = try env.app.pool.query("SELECT cr.*, picks.league_user_id from current_rankings cr left join picks on cr.team_id = picks.team_id AND picks.draft_id = $1 WHERE cr.team_id = $2", .{ draft_id, team_id }); + defer result.deinit(); + + var team: Team = undefined; + while (try result.next()) |row| { + team = try row.to(Team, .{}); + } + try web.renderWithDataPartials("pick", .{}, .{ + .team_id = team.team_id, + .rank = team.rank, + .name = team.name, + .division = team.division, + .league_user_id = team.league_user_id, + .draft_id = draft_id, + }, req, res); + } + } else { + @panic("oh no"); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/picks/show.zig b/src/web/picks/show.zig new file mode 100644 index 0000000..f802069 --- /dev/null +++ b/src/web/picks/show.zig @@ -0,0 +1,58 @@ +const Team = struct { + team_id: i16, + rank: ?i16, + name: []const u8, + division: []const u8, + username: ?[]const u8, +}; + +const DraftInfo = struct { + draft_id: i32, + current_picker: i32, + status: []const u8, + round_time_remaining: ?i32, + message: []const u8, +}; + +pub fn handler(env: *Env, req: *httpz.Request, res: *httpz.Response) !void { + if (req.params.get("id")) |draft_id| { + _ = try env.app.pool.exec("CALL auto_draft($1)", .{draft_id}); + + const picks_query = + \\SELECT cr.*, users.username from current_rankings cr + \\LEFT JOIN picks ON cr.team_id = picks.team_id AND picks.draft_id = $1 + \\LEFT JOIN league_users ON league_users.league_user_id = picks.league_user_id + \\LEFT JOIN users ON users.user_id = league_users.user_id + ; + var picks_result = try env.app.pool.query(picks_query, .{draft_id}); + defer picks_result.deinit(); + + var teams: [32]Team = undefined; + var i: u8 = 0; + while (try picks_result.next()) |row| : (i += 1) { + const team = try row.to(Team, .{}); + teams[i] = team; + } + + var draft_info: DraftInfo = undefined; + const current_picker_query = + \\SELECT * FROM draft_info WHERE draft_id = $1 + ; + var row = try env.app.pool.row(current_picker_query, .{draft_id}); + if (row) |r| { + draft_info = try r.to(DraftInfo, .{}); + } + + try web.renderWithDataPartials("draft", .{ "pick", web.pick_template }, .{ + .teams = teams, + .draft_info = draft_info, + .can_pick = if (draft_info.round_time_remaining) |_| true else false, + }, req, res); + try row.?.deinit(); + } +} + +const httpz = @import("httpz"); +const Env = @import("../../env.zig").Env; +const web = @import("../../web/web.zig"); +const std = @import("std"); diff --git a/src/web/web.zig b/src/web/web.zig new file mode 100644 index 0000000..184a1a3 --- /dev/null +++ b/src/web/web.zig @@ -0,0 +1,109 @@ +pub const Templates = std.StringHashMap(Template); + +pub var templates: Templates = undefined; + +pub fn start(app: *App) !void { + const allocator = app.allocator; + + var server = try httpz.ServerCtx(*const Dispatcher, *Env).init(allocator, .{ + .address = "0.0.0.0", + .port = 5882, + }, undefined); + defer server.deinit(); + + templates = Templates.init(allocator); + defer { + var it = templates.valueIterator(); + while (it.next()) |t| { + t.deinit(allocator); + } + templates.deinit(); + } + + inline for (config.templates) |template| { + try templates.put(template, try loadTemplate(allocator, template)); + } + + server.dispatcher(Dispatcher.dispatch); + + var router = server.router(); + { + var routes = router.group("", .{ .ctx = &Dispatcher{ .app = app } }); + routes.get("/", index); + routes.get("/about", about); + routes.get("/drafts/:id", @import("drafts/show.zig").handler); + routes.get("/drafts/:id/pick", @import("drafts/pick.zig").handler); + routes.get("/leagues/:id", @import("leagues/show.zig").handler); + routes.post("/leagues", @import("leagues/create.zig").handler); + routes.get("/invites/:id", @import("leagues/invite.zig").handler); + } + + // use get/post/put/head/patch/options/delete + // you can also use "all" to attach to all methods + + // start the server in the current thread, blocking. + try server.listen(); +} + +fn pick(_: *Env, req: *httpz.Request, res: *httpz.Response) !void { + try render("pick", req, res); +} + +fn about(_: *Env, req: *httpz.Request, res: *httpz.Response) !void { + // try render(templates.get("about").?, req, res); + try renderWithData("about", .{ .title = "foo" }, req, res); +} + +fn index(_: *Env, req: *httpz.Request, res: *httpz.Response) !void { + try render("index", req, res); +} + +fn loadTemplate(allocator: std.mem.Allocator, comptime template_name: []const u8) !Template { + return switch (try mustache.parseText(allocator, @embedFile("../views/" ++ template_name ++ ".mustache"), .{}, .{ .copy_strings = false })) { + .success => |t| t, + .parse_error => @panic("layout parse error"), + }; +} + +fn loadTemplateFile(allocator: std.mem.Allocator, template_name: []const u8) !Template { + const file = std.fs.cwd().openFile(try std.fmt.allocPrint(allocator, "src/views/{s}.mustache", .{template_name}), .{}) catch @panic("could not find template"); + defer file.close(); + const template_bytes = file.readToEndAlloc(allocator, std.math.maxInt(usize)) catch @panic("couldn't read template file "); + + return switch (try mustache.parseText(allocator, template_bytes, .{}, .{ .copy_strings = false })) { + .success => |t| t, + .parse_error => @panic("layout parse error"), + }; +} + +pub fn renderWithDataPartials(template_name: []const u8, partials: anytype, data: anytype, req: *httpz.Request, res: *httpz.Response) !void { + var template: Template = undefined; + if (config.dev) { + template = try loadTemplateFile(res.arena, template_name); + } else { + template = templates.get(template_name).?; + } + const content = try mustache.allocRenderPartials(res.arena, template, partials, data); + if (req.header("hx-request")) |_| { + res.body = content; + } else { + res.body = try mustache.allocRender(res.arena, templates.get("layout").?, .{ .content = content }); + } +} + +pub fn renderWithData(template_name: []const u8, data: anytype, req: *httpz.Request, res: *httpz.Response) !void { + try renderWithDataPartials(template_name, .{}, data, req, res); +} + +pub fn render(template_name: []const u8, req: *httpz.Request, res: *httpz.Response) !void { + try renderWithData(template_name, .{}, req, res); +} + +const config = @import("config"); +const App = @import("../app.zig").App; +const Env = @import("../env.zig").Env; +const httpz = @import("httpz"); +const mustache = @import("mustache"); +const Template = mustache.Template; +const std = @import("std"); +const Dispatcher = @import("dispatcher.zig").Dispatcher; diff --git a/test/00-setup.sql b/test/00-setup.sql new file mode 100644 index 0000000..93ceb03 --- /dev/null +++ b/test/00-setup.sql @@ -0,0 +1,10 @@ +SET client_min_messages TO WARNING; +CREATE EXTENSION IF NOT EXISTS pgtap; + +BEGIN; +SELECT plan(1); + +SELECT has_schema('teamdraft'); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/test/drafts.sql b/test/drafts.sql new file mode 100644 index 0000000..41d2b5b --- /dev/null +++ b/test/drafts.sql @@ -0,0 +1,21 @@ +BEGIN; +SELECT plan(4); + +SELECT has_table('drafts'); + +SELECT columns_are('leagues', ARRAY[ 'league_id', 'name', 'user_id', 'modified_at', 'created_at']); + +SELECT has_sequence('leagues_league_id_seq'); + +SELECT has_index('teamdraft', 'leagues', 'leagues_pk', 'league_id'); + +\set new_user_id 5 +INSERT INTO users(user_id, username) OVERRIDING SYSTEM VALUE VALUES (5, 'test'); +\set new_league_id 5 +INSERT INTO leagues(league_id, name, user_id) OVERRIDING SYSTEM VALUE VALUES (:new_league_id, 'test league', :new_user_id); + +CALL new_league_season(:new_league_id); + +SELECT * FROM finish(); +ROLLBACK; + diff --git a/test/functions.sql b/test/functions.sql new file mode 100644 index 0000000..e7dbce2 --- /dev/null +++ b/test/functions.sql @@ -0,0 +1,38 @@ +BEGIN; +SELECT plan(10); + +SELECT has_function('current_season'); +SELECT function_lang_is('current_season', 'sql'); +SELECT function_returns('current_season', 'integer'); + +INSERT INTO seasons (started_at, ended_at) VALUES (NOW(), NOW() + interval '3 months'); +SELECT results_eq( + 'SELECT current_season()', + 'SELECT 1', + 'should select a season' +); + +SELECT has_function('category_score'); +SELECT function_lang_is('category_score', 'plpgsql'); +SELECT function_returns('category_score', 'smallint'); + +SELECT results_eq( + $$SELECT category_score('win')$$, + 'SELECT 1::smallint' +); + +SELECT results_eq( + 'SELECT category_score(''superbowl''),' + || 'category_score(''conference'')', + 'SELECT 10::smallint, 10::smallint' +); + +SELECT results_eq( + 'SELECT category_score(''playoffs''), ' + || 'category_score(''divisional''),' + || 'category_score(''champion'')', + 'SELECT 5::smallint, 5::smallint, 5::smallint' +); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/test/leagues.sql b/test/leagues.sql new file mode 100644 index 0000000..fc7c465 --- /dev/null +++ b/test/leagues.sql @@ -0,0 +1,21 @@ +BEGIN; +SELECT plan(4); + +SELECT has_table('leagues'); + +SELECT columns_are('leagues', ARRAY[ 'league_id', 'name', 'user_id', 'modified_at', 'created_at']); + +SELECT has_sequence('leagues_league_id_seq'); + +SELECT has_index('teamdraft', 'leagues', 'leagues_pk', 'league_id'); + +\set new_user_id 5 +INSERT INTO users(user_id, username) OVERRIDING SYSTEM VALUE VALUES (5, 'test'); +\set new_league_id 5 +INSERT INTO leagues(league_id, name, user_id) OVERRIDING SYSTEM VALUE VALUES (:new_league_id, 'test league', :new_user_id); + +CALL new_league_season(:new_league_id); + +SELECT * FROM finish(); +ROLLBACK; + diff --git a/test/permissions.sql b/test/permissions.sql new file mode 100644 index 0000000..a848a74 --- /dev/null +++ b/test/permissions.sql @@ -0,0 +1,25 @@ +/* CREATE EXTENSION IF NOT EXISTS pgtap; */ +BEGIN; +SELECT plan(9); + +SELECT has_group('dml'); +SELECT has_group('ddl'); +SELECT has_group('read_only'); + +SELECT has_user('teamdraft'); +SELECT database_privs_are( + 'teamdraft', 'teamdraft', ARRAY['CONNECT', 'TEMPORARY', 'CREATE'] +); + +SELECT has_user('teamdraft_website'); +SELECT database_privs_are( + 'teamdraft', 'teamdraft_website', ARRAY['CONNECT', 'TEMPORARY'] +); + +SELECT has_user('teamdraft_ro'); +SELECT database_privs_are( + 'teamdraft', 'teamdraft_ro', ARRAY['CONNECT', 'TEMPORARY'] +); + +SELECT * FROM finish(); +ROLLBACK; diff --git a/test/users.sql b/test/users.sql new file mode 100644 index 0000000..ec6f046 --- /dev/null +++ b/test/users.sql @@ -0,0 +1,14 @@ +BEGIN; +SELECT plan(7); + +SELECT has_table('users'); +SELECT columns_are('users', ARRAY[ 'user_id', 'username', 'password', 'name', 'modified_at', 'created_at']); +SELECT has_sequence('users_user_id_seq'); +SELECT has_index('teamdraft', 'users', 'users_pk', 'user_id'); + +SELECT has_table('sessions'); +SELECT columns_are('sessions', ARRAY[ 'session_id', 'user_id', 'expires', 'modified', 'created']); +SELECT has_index('teamdraft', 'sessions', 'sessions_pkey', 'session_id'); + +SELECT * FROM finish(); +ROLLBACK; |