// deno-lint-ignore-file ban-types

import { Entity, run_on_worker } from "@infinity-beyond/classes/data_types/entity/entity.ts";
import { SupplyPermissions } from "@supply/supply.permissions.ts";
import { SupplyRest } from "@supply/supply.rest.ts";
import { SupplyMeta } from "@supply/supply.meta.ts";
import State from "@infinity-beyond/modules/state.ts";
import { AddSupplyItem } from "@supply/db/queries/add_item.supply.query.ts";
import { AllotSupplyItem } from "@supply/db/queries/allot_item.supply.query.ts";
import { CreateAllotSupplyItemFunction } from "@supply/db/functions/allot_item/supply.allot_item.function.ts";
import { ClaimReservedSupplyItem } from "@supply/db/queries/claim_reserved_item.supply.query.ts";
import { DeleteReservedSupplyItem } from "@supply/db/queries/delete_reserved_item.supply.query.ts";
import { GetSupplyUserData } from "@supply/db/queries/user_data.supply.ts";
import { CreateDeleteReservedSupplyItemFunction } from "@supply/db/functions/delete_reserved_item/supply.delete_reserved_item.function.ts";
import { CreateClaimReservedSupplyItemFunction } from "@supply/db/functions/claim_reserved_item/supply.claim_reserved_item.function.ts";
import { CreateSupplyUserDataFunction } from "@supply/db/functions/user_data/supply.user_data.function.ts";

export class Supply<
  EntityName extends string,
  PEMC extends Entity.Meta.Config = {},
> extends Entity<
  Supply.Events,
  EntityName,
  Supply.Permissions<EntityName>,
  Supply.Meta,
  PEMC,
  Supply.Options<PEMC>
> {
  override readonly REST = new SupplyRest(this);

  protected static override Permissions: readonly string[] = SupplyPermissions;

  constructor(name: EntityName, options: Supply.Options.Optional<PEMC> = {}) {
    super(name, options, SupplyMeta as Supply.Meta);
  }

  // #region Users

  @run_on_worker
  async User(key: string): Promise<Supply.User.WithPools | null> {
    const { rows: [ row ] } = await State.PostgresClient.query<Supply.User.WithPools>(
      `select users.*, jsonb_agg(pool_users.name) as pools
      from ${this.users_table} users left join ${this.pool_users_table} pool_users
      on users.key = pool_users.userkey
      where users.key = $1
      group by users.id`,
      [ key ]
    );

    return row || null;
  }

  @run_on_worker
  async UserData(
    key: string,
    count = 10,
    page = 1,
  ): Promise<Supply.User.Response> {
    key = this.format_key(key);
    if (!this.validate_key(key).valid) {
      return null;
    }

    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    let { rows: [{ allotments, item_count } = {}] } =
      await GetSupplyUserData.populate({
        slug: this.slug,
      }).execute(State.PostgresClient, key, count, count * (page - 1));

    allotments ??= [];
    item_count ??= 0;

    return { allotments, item_count };
  }

  @run_on_worker
  async Users(count = 10, page = 1) {
    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    const { rows } = await State.PostgresClient.query<Supply.User.WithPools>(
      `select users.*, jsonb_agg(pool_users.pool_name) as pools
      from ${this.users_table} users left join ${this.pool_users_table} pool_users
      on users.key = pool_users.user_key
      group by users.id
      limit $1 offset $2`,
      [count, count * (page - 1)]
    );

    return rows;
  }

  @run_on_worker
  async UserCount() {
    const { rows: [{ count }] } = await State.PostgresClient.query<{ count: number }>(
      `select count(*)::int as count from ${this.users_table}`
    )

    return count;
  }

  // #region Items

  @run_on_worker
  async Item(key: string): Promise<Supply.Item.WithPools | null> {
    const { rows: [ row ] } = await State.PostgresClient.query<Supply.Item.WithPools>(
      `select items.*, jsonb_agg(pool_items.pool_name) as pools
      from ${this.items_table} items left join ${this.pool_items_table} pool_items
      on items.key = pool_items.item_key
      where items.key = $1
      group by items.id`,
      [ key ]
    );

    return row || null;
  }

  @run_on_worker
  async Items(count = 10, page = 1) {
    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    const { rows } = await State.PostgresClient.query<Supply.Item.WithPools>(
      `select items.*, jsonb_agg(pool_items.pool_name) as pools
      from ${this.items_table} items left join ${this.pool_items_table} pool_items
      on items.key = pool_items.item_key
      group by items.id
      limit $1 offset $2`,
      [count, count * (page - 1)]
    );

    return rows;
  }

  @run_on_worker
  async ItemCount() {
    const { rows: [{ count }] } = await State.PostgresClient.query<{ count: number }>(
      `select count(*)::int as count from ${this.items_table}`
    )

    return count;
  }

  @run_on_worker
  async NextAvailableItem(_pool?: string) {
    const { rows: [ item ] } = await State.PostgresClient.query<Supply.Item | null>(
      `WITH items AS (
        SELECT *,
                FLOOR(EXTRACT(
                  EPOCH FROM (now() - available_from_date)
                ) / EXTRACT(
                  EPOCH FROM (available_until_date - available_from_date)
                ) * stock_capacity - stock_allotted) as available
              FROM ${this.items_table}
        )
        SELECT * from items
            where items.available > 0
            order by items.available DESC
            limit 1
      `
    );

    return item;
  }

  @run_on_worker
  async AddItem(params: Supply.Item.Unentered): Promise<Supply.Item.Response> {
    params.key = this.format_key(params.key);
    let { valid, reason } = this.validate_key(params.key);
    if(!params.name) { valid = false; reason = "No name provided" };

    if(!valid) {
      this.emit('item_creation_failed', reason!, params);
  
      return {
        added: false,
        reason: reason!
      }
    }

    const { rows: [{ new_item_id }] } = await AddSupplyItem.populate({ items_table: this.items_table }).execute(
      State.PostgresClient,
      params.key,
      params.name,
      params.description,
      params.provider,
      params.reference,
      !!params.uncapped,
      params.stock_capacity,
      params.stock_allotted || 0,
      params.weight || 100,
      params.monetary_value,
      !!params.allotments_expire,
      params.allotment_expiration_days,
      params.available_from_date,
      params.available_until_date
    )

    if(!new_item_id) {
      this.emit('item_creation_failed', 'Unspecified error', params);
      return {
        added: false,
        reason: 'Unspecified error'
      }
    }

    return {
      added: true
    }
  }

  // #region Allotments

  @run_on_worker
  async AllotItem(params: Supply.Allotment.Unentered): Promise<Supply.Allotment.Response> {
    try {
      const { item_key, user_key, reference, source } = params;

      await AllotSupplyItem.populate({ slug: this.slug }).execute(
        State.PostgresClient,
        item_key,
        user_key,
        reference,
        source,
        !!this.options.reservation_expiration
      );

      if(this.options.reservation_expiration) {
        this.emit('item_reserved', params);
      } else {
        this.emit('item_allotted', params);
      }

      return { allotted: true };
    } catch(e: any) {
      const reason = e.message || 'Unspecified during allotment query';

      this.emit('item_allotment_failed', reason, params);

      return { allotted: false, reason }
    }
  }

  @run_on_worker
  async ClaimReservedItem(user_key: string): Promise<Supply.Allotment.Function.ClaimResponse> {
    try {
      const { rows: [{ allotment }] } = await ClaimReservedSupplyItem.populate({ slug: this.slug }).execute(
        State.PostgresClient,
        user_key,
      );

      return {
        claimed: true,
        allotment
      };
    } catch(e: any) {
      const reason = e.message || 'Unspecified during allotment query';

      this.emit('reservation_claim_failed', user_key, reason);

      return { claimed: false, reason }
    }
  }

  @run_on_worker
  async DeleteReservedItem(user_key: string): Promise<Supply.Allotment.Function.DeleteResponse> {
    try {
      const { rows: [{ allotment }] } = await DeleteReservedSupplyItem.populate({ slug: this.slug }).execute(
        State.PostgresClient,
        user_key,
      );

      return {
        deleted: true,
        allotment
      };
    } catch(e: any) {
      const reason = e.message || 'Unspecified during allotment query';

      this.emit('reservation_claim_failed', user_key, reason);

      return { deleted: false, reason }
    }
  }

  // #region Pools

  @run_on_worker
  async Pools(count = 10, page = 1) {
    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    const { rows } = await State.PostgresClient.query<Supply.Pool>(
      `select * from ${this.pools_table}
      limit $1 offset $2`,
      [count, count * (page - 1)]
    )

    return rows;
  }

  @run_on_worker
  async PoolCount() {
    const { rows: [{ count }] } = await State.PostgresClient.query<{ count: number }>(`select count(*)::int from ${this.pools_table}`)

    return count;
  }

  @run_on_worker
  async ItemsInPool(pool: string, count = 10, page = 1) {
    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    const { rows } = await State.PostgresClient.query<Supply.Item.WithPools>(
      `select items.*, jsonb_agg(pool_items.pool_name) as pools
      from ${this.items_table} items left join ${this.pool_items_table} pool_items
      on items.key = pool_items.item_key
      where pool_items.pool_name = $1
      group by items.id
      limit $2 offset $3`,
      [pool, count, count * (page - 1)]
    );

    return rows;
  }

  @run_on_worker
  async CountItemsInPool(pool: string) {
    const { rows: [{ count }] } = await State.PostgresClient.query<{ count: number }>(
      `select count(distinct(item_key))::int
      from ${this.pool_items_table}
      where pool_name = $1`,
      [ pool ]
    );

    return count ?? 0;
  }

  @run_on_worker
  async UsersInPool(pool: string, count = 10, page = 1) {
    if (!Number.isSafeInteger(count)) count = 10;
    count = Math.max(1, count);

    const { rows } = await State.PostgresClient.query<Supply.User.WithPools>(
      `select users.*, jsonb_agg(pool_users.pool_name) as pools
      from ${this.users_table} users left join ${this.pool_users_table} pool_users
      on users.key = pool_users.user_key
      where pool_users.pool_name = $1
      group by users.id
      limit $2 offset $3`,
      [pool, count, count * (page - 1)]
    );

    return rows;
  }

  @run_on_worker
  async CountUsersInPool(pool: string) {
    const { rows: [{ count }] } = await State.PostgresClient.query<{ count: number }>(
      `select count(distinct(user_key))::int
      from ${this.pool_users_table}
      where pool_name = $1`,
      [ pool ]
    );

    return count ?? 0;
  }

  // #region Pool Modifiers

  @run_on_worker
  async AddItemToPools(item_key: string, ...pool_names: string[]) {
    await State.PostgresClient.query(`insert into ${ this.pool_items_table } (item_key, pool_name) select $1 as item_key, pool_name from unnest($2::text[]) as pool_name`, [item_key, pool_names]);

    return true;
  }

  @run_on_worker
  async RemoveItemFromPools(item_key: string, ...pool_names: string[]) {
    await State.PostgresClient.query(`delete from ${ this.pool_items_table } where item_key=$1 and pool_name = any($2)`, [item_key, pool_names]);

    return true;
  }

  @run_on_worker
  async AddItemsToPool(pool_name: string, ...item_keys: string[]) {
    await State.PostgresClient.query(`insert into ${ this.pool_items_table } (pool_name, item_key) select $1 as pool_name, item_key from unnest($2::text[]) as item_key`, [pool_name, item_keys]);

    return true;
  }

  @run_on_worker
  async RemoveItemsFromPool(pool_name: string, ...item_keys: string[]) {
    await State.PostgresClient.query(`delete from ${ this.pool_items_table } where pool_name = 1 and item_key = any($2)`, [pool_name, item_keys]);

    return true;
  }

  @run_on_worker
  async AddUserToPools(user_key: string, ...pool_names: string[]) {
    await State.PostgresClient.query(`insert into ${ this.pool_users_table } (user_key, pool_name) select $1 as user_key, pool_name from unnest($2::text[]) as pool_name`, [user_key, pool_names]);

    return true;
  }

  @run_on_worker
  async RemoveUserFromPools(user_key: string, ...pool_names: string[]) {
    await State.PostgresClient.query(`delete from ${ this.pool_users_table } where user_key=$1 and pool_name = any($2)`, [user_key, pool_names]);

    return true;
  }

  @run_on_worker
  async AddUsersToPool(pool_name: string, ...user_keys: string[]) {
    await State.PostgresClient.query(`insert into ${ this.pool_items_table } (pool_name, user_key) select $1 as pool_name, user_key from unnest($2::text[]) as user_key`, [pool_name, user_keys]);

    return true;
  }

  @run_on_worker
  async RemoveUsersFromPool(pool_name: string, ...user_keys: string[]) {
    await State.PostgresClient.query(`delete from ${ this.pool_items_table } where pool_name = 1 and user_key = any($2)`, [pool_name, user_keys]);

    return true;
  }

  // #region Tables

  readonly allotments_table   = `${this.slug}_allotments`;
  readonly items_table        = `${this.slug}_items`;
  readonly pool_items_table   = `${this.slug}_pool_items`;
  readonly pool_users_table   = `${this.slug}_pool_users`;
  readonly pools_table        = `${this.slug}_pools`;
  readonly users_table        = `${this.slug}_users`;

  override async Setup() {
    await super.Setup();

    if (State.IS_BUILDING) return this;

    // #region Create DB Tables

    const key_max_length = this.options.key.max_length ?? 16;

    // #region - Pools Table

    const pools_table = this.pools_table;

    await State.PostgresClient.CreateTable<Supply.Pool>(pools_table, {
      id: "serial PRIMARY KEY",
      name: `varchar(32)`,
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE INDEX ${pools_table}_name_idx ON ${pools_table} using hash(name);
        `);
      },
    });

    // #region - Allotments Table

    const allotments_table = this.allotments_table;

    await State.PostgresClient.CreateTable<Supply.Allotment>(allotments_table, {
      id: "serial PRIMARY KEY",

      item_key: `varchar(${key_max_length}) NOT NULL`,
      user_key: `varchar NOT NULL`,

      source: `varchar`,
      reference: `varchar(64)`,

      expiry_timestamp: `timestamptz`,
      expired: `boolean`,
      timestamp: `timestamptz NOT NULL DEFAULT NOW()`,

      reserved: `boolean NOT NULL DEFAULT FALSE`
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE INDEX ${allotments_table}_item_key_idx ON ${allotments_table} using hash(item_key);
          CREATE INDEX ${allotments_table}_user_key_idx ON ${allotments_table} using hash(user_key);
        `);
      },
    });

    // #region - Items Table

    const items_table = this.items_table;

    await State.PostgresClient.CreateTable<Supply.Item>(items_table, {
      id: "serial PRIMARY KEY",

      key: `varchar(${key_max_length}) NOT NULL`,

      name: `varchar(32) NOT NULL`,
      description: `varchar(128)`,

      provider: `varchar(32) NOT NULL`,
      reference: `varchar(64)`,

      uncapped: `boolean NOT NULL DEFAULT FALSE`,

      stock_capacity: `int4 NOT NULL`,
      stock_allotted: `int4 NOT NULL default 0`,
      weight: `int2 NOT NULL default 100`,
      monetary_value: `int4`,

      allotments_expire: `boolean NOT NULL DEFAULT FALSE`,
      allotment_expiration_days: `int2`,

      timestamp_added: `timestamptz NOT NULL DEFAULT NOW()`,

      available_from_date: `timestamptz`,
      available_until_date: `timestamptz`,

      archived: `boolean NOT NULL DEFAULT FALSE`,
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE INDEX ${items_table}_key_idx ON ${items_table} using hash(key);
        `);
      },
    });

    // #region - Pool Items Table

    const pool_items_table = this.pool_items_table;

    await State.PostgresClient.CreateTable<Supply.Pool.Item>(pool_items_table, {
      id: "serial PRIMARY KEY",

      pool_name: `varchar NOT NULL`,
      item_key: `varchar NOT NULL`
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE INDEX ${pool_items_table}_item_key_idx ON ${pool_items_table} using hash(item_key);
          CREATE INDEX ${pool_items_table}_pool_name_idx ON ${pool_items_table} using hash(pool_name);
        `);
      },
    });

    // #region - Users Table

    const users_table = this.users_table;

    await State.PostgresClient.CreateTable<Supply.User>(users_table, {
      id: "serial PRIMARY KEY",

      key: `varchar NOT NULL`,

      first_item: `timestamptz NOT NULL DEFAULT NOW()`,
      last_item: `timestamptz NOT NULL DEFAULT NOW()`,

      item_count: `int2 NOT NULL DEFAULT 0`,
      value_awarded: `real NOT NULL DEFAULT 0`
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE UNIQUE INDEX ${users_table}_key_idx ON ${users_table} (key);
          CREATE INDEX ${users_table}_key_hash_idx ON ${users_table} using hash(key);
        `);
      },
    });

    // #region - Pool Users Table

    const pool_users_table = this.pool_users_table;

    await State.PostgresClient.CreateTable<Supply.Pool.User>(pool_users_table, {
      id: "serial PRIMARY KEY",

      pool_name: `varchar NOT NULL`,
      user_key: `varchar NOT NULL`
    }, {
      async onCreate() {
        await State.PostgresClient.query(`
          CREATE INDEX ${pool_users_table}_user_key_idx ON ${pool_users_table} using hash(user_key);
          CREATE INDEX ${pool_users_table}_pool_name_idx ON ${pool_users_table} using hash(pool_name);
        `);
      },
    });

    await CreateAllotSupplyItemFunction.populate({
      items_table: this.items_table,
      slug: this.slug,
      allotments_table: this.allotments_table,
      meta_table: this.meta_table,
      users_table: this.users_table,
    }).execute(State.PostgresClient);

    await CreateClaimReservedSupplyItemFunction.populate({
      slug: this.slug,
      allotments_table: this.allotments_table,
      users_table: this.users_table,
    }).execute(State.PostgresClient);

    await CreateDeleteReservedSupplyItemFunction.populate({
      slug: this.slug,
      allotments_table: this.allotments_table,
      items_table: this.items_table,
      meta_table: this.meta_table,
      users_table: this.users_table,
    }).execute(State.PostgresClient);

    await CreateSupplyUserDataFunction.populate({
      allotments_table: this.allotments_table,
      slug: this.slug,
      user_table: this.users_table,
    }).execute(State.PostgresClient);

    return this;
  }
}

Supply.on("supply_created", (instance) => {
  instance.entity_class.log(`Supply initialized [${instance.name}]`);

  if(instance.options.reservation_expiration) {
    const every = instance.options.reservation_expiration[0];
    const frequency_input = instance.options.reservation_expiration[1];
    let frequency: string = instance.options.reservation_expiration[1];
    if(frequency == 'day') frequency = 'dayOfMonth';

    Deno.cron(`expire_${instance.slug}_reservations`, {minute: { every: 5 }}, async () => {
      const { rows } = await State.PostgresClient.query<{ user_key: string }>(`SELECT user_key FROM ${instance.allotments_table} WHERE reserved AND timestamp < NOW() - interval '${every} ${frequency_input}'`);
      for(const { user_key } of rows) {
        instance.DeleteReservedItem(user_key).then((deletion) => {
          if(deletion.deleted) {
            instance.emit('reservation_deleted', deletion.allotment);
          } else {
            instance.emit('reservation_deletion_failed', user_key, deletion.reason);
          }
        })
      }
    })
  }
});