apiVersion: v1
kind: Namespace
metadata:
  name: chat

---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: database
  namespace: chat
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
  storageClassName: zfs-nfs

---
kind: ConfigMap
apiVersion: v1
metadata:
  name: database-config
  namespace: chat
data:
  POSTGRES_USER: admin
  POSTGRES_PASSWORD: admin
  POSTGRES_DB: chat
  PGDATA: /var/lib/postgresql/data/pgdata

---
kind: ConfigMap
apiVersion: v1
metadata:
  name: database-init
  namespace: chat
data:
  10-init.sql: |-
    create table if not exists "user" (
      "id" serial primary key,
      "username" varchar(32) not null unique,
      "password_hash" varchar(72) not null,
      "status" varchar(200) not null default '',
      "picture" varchar(300) not null default '',
      "bio" varchar(200) not null default ''
    );

    create table if not exists "role" (
      "id" serial primary key,
      "name" varchar(32) not null unique
    );

    create table if not exists "role_binding" (
      "user_id" integer not null references "user"("id") on delete cascade,
      "role_id" integer not null references "role"("id") on delete cascade,
      primary key ("user_id", "role_id"),
      unique ("user_id", "role_id")
    );

    create table if not exists "channel" (
      "id" serial primary key,
      "name" varchar(32) not null unique,
      "description" varchar(200) default null
    );

    create type "right_t" as enum ('R', 'W', 'RW', 'A');

    create table if not exists "right" (
      "role_id" integer not null references "role"("id"),
      "channel_id" integer not null references "channel"("id"),
      "rights" right_t not null,
      primary key ("role_id", "channel_id"),
      unique ("role_id", "channel_id")
    );

    create table if not exists "message" (
      "id" serial primary key,
      "sender_id" integer not null references "user"("id") on delete cascade,
      "channel_id" integer not null references "channel"("id") on delete cascade,
      "time" timestamp without time zone default (now() at time zone 'utc'),
      "content" varchar(2000) not null
    );
  20-procedures.sql: |-
    create or replace procedure add_user(
        in in_username varchar(32),
        in in_password_hash varchar(72)
      )
    language plpgsql
    as $$
    begin
      insert into "user" ("username", "password_hash")
      values (in_username, in_password_hash);

      insert into "role_binding" ("user_id", "role_id")
      values (
        (
          select "id"
          from "user"
          where "username" = in_username
        ),
        (
          select "id"
          from "role"
          where "name" = 'member'
        )
      );
    end;
    $$;

    create or replace procedure add_channel(
        in channel_name varchar(32),
        in channel_desc varchar(200)
      )
    language plpgsql
    as $$
    declare
      channel_id integer;
    begin
      insert into "channel" ("name", "description")
      values (channel_name, channel_desc)
      returning "id" into channel_id;

      insert into "right" ("role_id", "channel_id", "rights")
      values (
        (
          select "id"
          from "role"
          where "name" = 'admin'
        ),
        channel_id,
        'A'
      );
    end;
    $$
  30-triggers.sql: |-
    -- prevent deleting or changing default roles
    create rule protect_special_roles_update as
    on update to "role"
    where old."name" = 'admin' or old."name" = 'member'
    do instead nothing;

    create rule protect_special_roles_delete as
    on delete to "role"
    where old."name" = 'admin' or old."name" = 'member'
    do instead nothing;
  40-views.sql: |-
    create or replace view "user_rigths_per_channel" as
    select distinct
      "u"."id" as "user_id",
      "c"."id" as "channel_id",
      "c"."name" as "channel_name",
      "c"."description" as "channel_description",
      "r"."rights"
    from
      "user" "u"
    join
      "role_binding" "rb" on "u"."id" = "rb"."user_id"
    join
      "role" "ro" on "rb"."role_id" = "ro"."id"
    join
      "right" "r" on "ro"."id" = "r"."role_id"
    join
      "channel" "c" on "r"."channel_id" = "c"."id"
    order by "user_id";
  50-default.sql: |-
    -- admin account and its role and right
    insert into "user" ("username", "password_hash")
    values (
        'admin',
        '$2a$12$FChbwNEIH9imtkTAkNq35eqMb.1C.1BP3bbuFZwOr7rOrs5luwCzq'
      );

    insert into "role" ("name")
    values ('admin');

    insert into "role_binding" ("user_id", "role_id")
    values (
        (
          select "id"
          from "user"
          where "username" = 'admin'
        ),
        (
          select "id"
          from "role"
          where "name" = 'admin'
        )
      );

    -- default channel
    call add_channel('default', 'default channel');

    -- member role
    insert into "role" ("name")
    values ('member');

    insert into "right" ("role_id", "channel_id", "rights")
    values (
        (
          select "id"
          from "role"
          where "name" = 'member'
        ),
        (
          select "id"
          from "channel"
          where "name" = 'default'
        ),
        'RW'
      );
  99-sample.sql: |-
    -- More users
    insert into "user" ("username", "password_hash", "status", "picture", "bio")
    values
        ('alice', '$2a$12$FChbwNEIH9imtkTAkNq35eqMb.1C.1BP3bbuFZwOr7rOrs5luwCzq', 'Online', '', 'Loves coding and coffee.'),
        ('bob', '$2a$12$FChbwNEIH9imtkTAkNq35eqMb.1C.1BP3bbuFZwOr7rOrs5luwCzq', 'Away', '', 'Gamer and tech enthusiast.'),
        ('charlie', '$2a$12$FChbwNEIH9imtkTAkNq35eqMb.1C.1BP3bbuFZwOr7rOrs5luwCzq', 'Busy', '', 'Database aficionado.'),
        ('diana', '$2a$12$FChbwNEIH9imtkTAkNq35eqMb.1C.1BP3bbuFZwOr7rOrs5luwCzq', 'Online', '', 'Enjoys reading and hiking.');

    -- More roles
    insert into "role" ("name")
    values
        ('moderator'),
        ('guest');

    -- More channels
    call add_channel('general', 'General discussion channel');
    call add_channel('development', 'Channel for development topics');
    call add_channel('random', 'Anything goes in here');

    -- Role bindings for new users
    insert into "role_binding" ("user_id", "role_id")
    values
        ((select "id" from "user" where "username" = 'alice'), (select "id" from "role" where "name" = 'member')),
        ((select "id" from "user" where "username" = 'bob'), (select "id" from "role" where "name" = 'member')),
        ((select "id" from "user" where "username" = 'charlie'), (select "id" from "role" where "name" = 'moderator')),
        ((select "id" from "user" where "username" = 'diana'), (select "id" from "role" where "name" = 'guest'));

    -- Rights for new roles and channels
    insert into "right" ("role_id", "channel_id", "rights")
    values
        ((select "id" from "role" where "name" = 'moderator'), (select "id" from "channel" where "name" = 'general'), 'RW'),
        ((select "id" from "role" where "name" = 'moderator'), (select "id" from "channel" where "name" = 'development'), 'RW'),
        ((select "id" from "role" where "name" = 'guest'), (select "id" from "channel" where "name" = 'general'), 'R'),
        ((select "id" from "role" where "name" = 'member'), (select "id" from "channel" where "name" = 'general'), 'RW'),
        ((select "id" from "role" where "name" = 'member'), (select "id" from "channel" where "name" = 'development'), 'RW'),
        ((select "id" from "role" where "name" = 'member'), (select "id" from "channel" where "name" = 'random'), 'RW');


    -- Sample messages
    insert into "message" ("sender_id", "channel_id", "content")
    values
        ((select "id" from "user" where "username" = 'alice'), (select "id" from "channel" where "name" = 'general'), 'Hey everyone, glad to be here!'),
        ((select "id" from "user" where "username" = 'bob'), (select "id" from "channel" where "name" = 'default'), 'Good morning!'),
        ((select "id" from "user" where "username" = 'charlie'), (select "id" from "channel" where "name" = 'development'), 'Working on a new feature, updates coming soon.'),
        ((select "id" from "user" where "username" = 'alice'), (select "id" from "channel" where "name" = 'general'), 'Does anyone have experience with PostgreSQL?'),
        ((select "id" from "user" where "username" = 'diana'), (select "id" from "channel" where "name" = 'general'), 'Hello! Just joined the server.'),
        ((select "id" from "user" where "username" = 'bob'), (select "id" from "channel" where "name" = 'random'), 'Anyone up for some gaming later?'),
        ((select "id" from "user" where "username" = 'admin'), (select "id" from "channel" where "name" = 'default'), 'Welcome all new users!');

---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: database
  namespace: chat
spec:
  strategy:
    type: Recreate
  replicas: 1
  selector:
    matchLabels:
      app: database
  template:
    metadata:
      labels:
        app: database
    spec:
      containers:
        - name: database
          image: postgres:17-alpine
          ports:
            - containerPort: 5432
              protocol: TCP
          envFrom:
            - configMapRef:
                name: database-config
          resources:
            requests:
              cpu: "10m"
              memory: "10Mi"
            limits:
              cpu: "2000m"
              memory: "1024Mi"
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: database
            - mountPath: /docker-entrypoint-initdb.d
              name: database-init
      volumes:
        - name: database
          persistentVolumeClaim:
            claimName: database
        - name: database-init
          configMap:
            name: database-init

---
kind: Service
apiVersion: v1
metadata:
  name: database
  namespace: chat
spec:
  ports:
    - port: 5432
      targetPort: 5432
  selector:
    app: database

---
kind: ConfigMap
apiVersion: v1
metadata:
  name: keystore-config
  namespace: chat
data:
  valkey.conf: |-
    port 6379
    databases 1

    aclfile /usr/local/etc/valkey/users.acl
  users.acl: |-
    user default off

    user admin on >admin allcommands allkeys &*
    user readonly on >readonly ~* +@read &*

---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: keystore
  namespace: chat
spec:
  strategy:
    type: Recreate
  replicas: 1
  selector:
    matchLabels:
      app: keystore
  template:
    metadata:
      labels:
        app: keystore
    spec:
      containers:
        - name: keystore
          image: valkey/valkey:8-alpine
          ports:
            - containerPort: 6379
              protocol: TCP
          command: ["valkey-server"]
          args: ["/usr/local/etc/valkey/valkey.conf"]
          resources:
            requests:
              cpu: "10m"
              memory: "10Mi"
            limits:
              cpu: "2000m"
              memory: "1024Mi"
          volumeMounts:
            - mountPath: /usr/local/etc/valkey/valkey.conf
              name: keystore-config
              subPath: valkey.conf
            - mountPath: /usr/local/etc/valkey/users.acl
              name: keystore-config
              subPath: users.acl
      volumes:
        - name: keystore-config
          configMap:
            name: keystore-config

---
kind: Service
apiVersion: v1
metadata:
  name: keystore
  namespace: chat
spec:
  ports:
    - port: 6379
      targetPort: 6379
  selector:
    app: keystore

---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: ui
  namespace: chat
spec:
  strategy:
    type: Recreate
  replicas: 1
  selector:
    matchLabels:
      app: ui
  template:
    metadata:
      labels:
        app: ui
    spec:
      containers:
        - name: ui
          image: registry.tek.govt.hu/chat/ui:debug
          ports:
            - containerPort: 80
              protocol: TCP
          resources:
            requests:
              cpu: "10m"
              memory: "10Mi"
            limits:
              cpu: "2000m"
              memory: "1024Mi"
---
kind: Service
apiVersion: v1
metadata:
  name: ui
  namespace: chat
spec:
  ports:
    - port: 80
      targetPort: 80
  selector:
    app: ui

---
kind: ConfigMap
apiVersion: v1
metadata:
  name: api-config
  namespace: chat
data:
  API_ADDRESS: :5000
  API_BASE: api
  API_TOKEN_LIFE: "600"
  DB_HOST: database
  DB_PORT: "5432"
  DB_USER: admin
  DB_PASSWORD: admin
  DB_NAME: chat
  VALKEY_ADDRESS: keystore:6379
  VALKEY_USERNAME: admin
  VALKEY_PASSWORD: admin
  VALKEY_DB: "0"

---
kind: Deployment
apiVersion: apps/v1
metadata:
  name: api
  namespace: chat
spec:
  strategy:
    type: Recreate
  replicas: 1
  selector:
    matchLabels:
      app: api
  template:
    metadata:
      labels:
        app: api
    spec:
      containers:
        - name: api
          image: registry.tek.govt.hu/chat/api:latest
          ports:
            - containerPort: 5000
              protocol: TCP
          envFrom:
            - configMapRef:
                name: api-config
          resources:
            requests:
              cpu: "10m"
              memory: "10Mi"
            limits:
              cpu: "2000m"
              memory: "1024Mi"
---
kind: Service
apiVersion: v1
metadata:
  name: api
  namespace: chat
spec:
  ports:
    - port: 5000
      targetPort: 5000
  selector:
    app: api

---
apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: ingress
  namespace: chat
  annotations:
    cert-manager.io/cluster-issuer: letsencrypt-production
    traefik.ingress.kubernetes.io/router.entrypoints: web, websecure
    traefik.ingress.kubernetes.io/router.middlewares: >-
      traefik-secure@kubernetescrd,traefik-ws@kubernetescrd
spec:
  ingressClassName: traefik
  tls:
    - hosts:
        - chat.tek.govt.hu
      secretName: chat-tls
  rules:
    - host: chat.tek.govt.hu
      http:
        paths:
          - path: /api
            pathType: Prefix
            backend:
              service:
                name: api
                port:
                  number: 5000
          - path: /
            pathType: Prefix
            backend:
              service:
                name: ui
                port:
                  number: 80