/* eslint-disable @typescript-eslint/no-unsafe-member-access */
/* eslint-disable @typescript-eslint/no-unsafe-assignment */
/* eslint-disable @typescript-eslint/no-empty-object-type */
/* eslint-disable @typescript-eslint/no-explicit-any */
/* eslint-disable @typescript-eslint/no-unsafe-argument */
/* eslint-disable @typescript-eslint/no-unsafe-return */
/**
 * @since 1.0.0
 */
import crypto from "crypto" // TODO
import type { Brand } from "effect/Brand"
import * as DateTime from "effect/DateTime"
import type { Input } from "effect/Duration"
import * as Effect from "effect/Effect"
import { identity } from "effect/Function"
import * as Option from "effect/Option"
import * as Predicate from "effect/Predicate"
import * as RequestResolver from "effect/RequestResolver"
import * as Schema from "effect/Schema"
import * as Getter from "effect/SchemaGetter"
import * as Transformation from "effect/SchemaTransformation"
import type { Scope } from "effect/Scope"
import * as VariantSchema from "effect/unstable/schema/VariantSchema"
import { SqlClient } from "effect/unstable/sql/SqlClient"
import * as SqlResolver from "effect/unstable/sql/SqlResolver"
import * as SqlSchema from "effect/unstable/sql/SqlSchema"

const {
  Class,
  Field,
  FieldExcept,
  FieldOnly,
  Struct,
  Union,
  extract,
  fieldEvolve
} = VariantSchema.make({
  variants: ["select", "insert", "update", "json", "jsonCreate", "jsonUpdate"],
  defaultVariant: "select"
})

/**
 * @since 1.0.0
 * @category models
 */
export type Any = Schema.Top & {
  readonly fields: Schema.Struct.Fields
  readonly insert: Schema.Top
  readonly update: Schema.Top
  readonly json: Schema.Top
  readonly jsonCreate: Schema.Top
  readonly jsonUpdate: Schema.Top
}

/**
 * @since 1.0.0
 * @category models
 */
export type VariantsDatabase = "select" | "insert" | "update"

/**
 * @since 1.0.0
 * @category models
 */
export type VariantsJson = "json" | "jsonCreate" | "jsonUpdate"

export {
  /**
   * A base class used for creating domain model schemas.
   *
   * It supports common variants for database and JSON apis.
   *
   * @since 1.0.0
   * @category constructors
   * @example
   * import { Schema } from "effect"
   * import { Model } from "effect/unstable/schema"
   *
   * export const GroupId = Schema.Number.pipe(Schema.brand("GroupId"))
   *
   * export class Group extends Model.Class<Group>("Group")({
   *   id: Model.Generated(GroupId),
   *   name: Schema.String,
   *   createdAt: Model.DateTimeInsertFromDate,
   *   updatedAt: Model.DateTimeUpdateFromDate
   * }) {}
   *
   * // schema used for selects
   * Group
   *
   * // schema used for inserts
   * Group.insert
   *
   * // schema used for updates
   * Group.update
   *
   * // schema used for json api
   * Group.json
   * Group.jsonCreate
   * Group.jsonUpdate
   *
   * // you can also turn them into classes
   * class GroupJson extends Schema.Class<GroupJson>("GroupJson")(Group.json) {
   *   get upperName() {
   *     return this.name.toUpperCase()
   *   }
   * }
   */
  Class,
  /**
   * @since 1.0.0
   * @category extraction
   */
  extract,
  /**
   * @since 1.0.0
   * @category fields
   */
  Field,
  /**
   * @since 1.0.0
   * @category fields
   */
  fieldEvolve,
  /**
   * @since 1.0.0
   * @category fields
   */
  FieldExcept,
  /**
   * @since 1.0.0
   * @category fields
   */
  FieldOnly,
  /**
   * @since 1.0.0
   * @category constructors
   */
  Struct,
  /**
   * @since 1.0.0
   * @category constructors
   */
  Union
}

/**
 * @since 1.0.0
 * @category fields
 */
export const fields: <A extends VariantSchema.Struct<any>>(self: A) => A[typeof VariantSchema.TypeId] =
  VariantSchema.fields

/**
 * @since 1.0.0
 * @category overrideable
 */
export const Override: <A>(value: A) => A & Brand<"Override"> = VariantSchema.Override

/**
 * @since 1.0.0
 * @category generated
 */
export interface Generated<S extends Schema.Top> extends
  VariantSchema.Field<{
    readonly select: S
    readonly update: S
    readonly json: S
  }>
{}

/**
 * A field that represents a column that is generated by the database.
 *
 * It is available for selection and update, but not for insertion.
 *
 * @since 1.0.0
 * @category generated
 */
export const Generated = <S extends Schema.Top>(
  schema: S
): Generated<S> =>
  Field({
    select: schema,
    update: schema,
    json: schema
  })

/**
 * @since 1.0.0
 * @category generated
 */
export interface GeneratedByApp<S extends Schema.Top> extends
  VariantSchema.Field<{
    readonly select: S
    readonly insert: S
    readonly update: S
    readonly json: S
  }>
{}

/**
 * A field that represents a column that is generated by the application.
 *
 * It is required by the database, but not by the JSON variants.
 *
 * @since 1.0.0
 * @category generated
 */
export const GeneratedByApp = <S extends Schema.Top>(
  schema: S
): GeneratedByApp<S> =>
  Field({
    select: schema,
    insert: schema,
    update: schema,
    json: schema
  })

/**
 * @since 1.0.0
 * @category sensitive
 */
export interface Sensitive<S extends Schema.Top> extends
  VariantSchema.Field<{
    readonly select: S
    readonly insert: S
    readonly update: S
  }>
{}

/**
 * A field that represents a sensitive value that should not be exposed in the
 * JSON variants.
 *
 * @since 1.0.0
 * @category sensitive
 */
export const Sensitive = <S extends Schema.Top>(
  schema: S
): Sensitive<S> =>
  Field({
    select: schema,
    insert: schema,
    update: schema
  })

/**
 * @since 1.0.0
 * @category optional
 */
export interface optionalOption<S extends Schema.Top>
  extends Schema.decodeTo<Schema.Option<Schema.toType<S>>, Schema.optionalKey<Schema.NullOr<S>>>
{}

/**
 * @since 1.0.0
 * @category optional
 */
export const optionalOption = <S extends Schema.Top>(schema: S): optionalOption<S> =>
  Schema.optionalKey(Schema.NullOr(schema)).pipe(
    Schema.decodeTo(
      Schema.Option(Schema.toType(schema)),
      Transformation.transformOptional<Option.Option<S["Type"]>, S["Type"] | null>({
        decode: (oe) => oe.pipe(Option.filter(Predicate.isNotNull), Option.some),
        encode: Option.flatten
      }) as any
    )
  )

/**
 * Convert a field to one that is optional for all variants.
 *
 * For the database variants, it will accept `null`able values.
 * For the JSON variants, it will also accept missing keys.
 *
 * @since 1.0.0
 * @category optional
 */
export interface FieldOption<S extends Schema.Top> extends
  VariantSchema.Field<{
    readonly select: Schema.OptionFromNullOr<S>
    readonly insert: Schema.OptionFromNullOr<S>
    readonly update: Schema.OptionFromNullOr<S>
    readonly json: optionalOption<S>
    readonly jsonCreate: optionalOption<S>
    readonly jsonUpdate: optionalOption<S>
  }>
{}

/**
 * Convert a field to one that is optional for all variants.
 *
 * For the database variants, it will accept `null`able values.
 * For the JSON variants, it will also accept missing keys.
 *
 * @since 1.0.0
 * @category optional
 */
export const FieldOption: <Field extends VariantSchema.Field<any> | Schema.Top>(
  self: Field
) => Field extends Schema.Top ? FieldOption<Field>
  : Field extends VariantSchema.Field<infer S> ? VariantSchema.Field<
      {
        readonly [K in keyof S]: S[K] extends Schema.Top ? K extends VariantsDatabase ? Schema.OptionFromNullOr<S[K]>
          : optionalOption<S[K]>
          : never
      }
    >
  : never = fieldEvolve({
    select: Schema.OptionFromNullOr,
    insert: Schema.OptionFromNullOr,
    update: Schema.OptionFromNullOr,
    json: optionalOption,
    jsonCreate: optionalOption,
    jsonUpdate: optionalOption
  }) as any

/**
 * @since 1.0.0
 * @category date & time
 */
export interface Date extends Schema.decodeTo<Schema.instanceOf<DateTime.Utc>, Schema.String> {}

/**
 * A schema for a `DateTime.Utc` that is serialized as a date string in the
 * format `YYYY-MM-DD`.
 *
 * @since 1.0.0
 * @category date & time
 */
export const Date: Date = Schema.String.pipe(
  Schema.decodeTo(Schema.DateTimeUtc, {
    decode: Getter.dateTimeUtcFromInput().map(DateTime.removeTime),
    encode: Getter.transform(DateTime.formatIsoDate)
  })
)

/**
 * @since 1.0.0
 * @category date & time
 */
export const DateWithNow = VariantSchema.Overrideable(Date, {
  defaultValue: Effect.map(DateTime.now, DateTime.removeTime)
})

/**
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeWithNow = VariantSchema.Overrideable(Schema.DateTimeUtcFromString, {
  defaultValue: DateTime.now
})

/**
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeFromDateWithNow = VariantSchema.Overrideable(Schema.DateTimeUtcFromDate, {
  defaultValue: DateTime.now
})

/**
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeFromNumberWithNow = VariantSchema.Overrideable(Schema.DateTimeUtcFromMillis, {
  defaultValue: DateTime.now
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeInsert extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromString
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromString>
    readonly json: Schema.DateTimeUtcFromString
  }>
{}

/**
 * A field that represents a date-time value that is inserted as the current
 * `DateTime.Utc`. It is serialized as a string for the database.
 *
 * It is omitted from updates and is available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeInsert: DateTimeInsert = Field({
  select: Schema.DateTimeUtcFromString,
  insert: DateTimeWithNow,
  json: Schema.DateTimeUtcFromString
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeInsertFromDate extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromDate
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromDate>
    readonly json: Schema.DateTimeUtcFromString
  }>
{}

/**
 * A field that represents a date-time value that is inserted as the current
 * `DateTime.Utc`. It is serialized as a `Date` for the database.
 *
 * It is omitted from updates and is available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeInsertFromDate: DateTimeInsertFromDate = Field({
  select: Schema.DateTimeUtcFromDate,
  insert: DateTimeFromDateWithNow,
  json: Schema.DateTimeUtcFromString
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeInsertFromNumber extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromMillis
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromMillis>
    readonly json: Schema.DateTimeUtcFromMillis
  }>
{}

/**
 * A field that represents a date-time value that is inserted as the current
 * `DateTime.Utc`. It is serialized as a `number`.
 *
 * It is omitted from updates and is available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeInsertFromNumber: DateTimeInsertFromNumber = Field({
  select: Schema.DateTimeUtcFromMillis,
  insert: DateTimeFromNumberWithNow,
  json: Schema.DateTimeUtcFromMillis
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeUpdate extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromString
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromString>
    readonly update: VariantSchema.Overrideable<Schema.DateTimeUtcFromString>
    readonly json: Schema.DateTimeUtcFromString
  }>
{}

/**
 * A field that represents a date-time value that is updated as the current
 * `DateTime.Utc`. It is serialized as a string for the database.
 *
 * It is set to the current `DateTime.Utc` on updates and inserts and is
 * available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeUpdate: DateTimeUpdate = Field({
  select: Schema.DateTimeUtcFromString,
  insert: DateTimeWithNow,
  update: DateTimeWithNow,
  json: Schema.DateTimeUtcFromString
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeUpdateFromDate extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromDate
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromDate>
    readonly update: VariantSchema.Overrideable<Schema.DateTimeUtcFromDate>
    readonly json: Schema.DateTimeUtcFromString
  }>
{}

/**
 * A field that represents a date-time value that is updated as the current
 * `DateTime.Utc`. It is serialized as a `Date` for the database.
 *
 * It is set to the current `DateTime.Utc` on updates and inserts and is
 * available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeUpdateFromDate: DateTimeUpdateFromDate = Field({
  select: Schema.DateTimeUtcFromDate,
  insert: DateTimeFromDateWithNow,
  update: DateTimeFromDateWithNow,
  json: Schema.DateTimeUtcFromString
})

/**
 * @since 1.0.0
 * @category date & time
 */
export interface DateTimeUpdateFromNumber extends
  VariantSchema.Field<{
    readonly select: Schema.DateTimeUtcFromMillis
    readonly insert: VariantSchema.Overrideable<Schema.DateTimeUtcFromMillis>
    readonly update: VariantSchema.Overrideable<Schema.DateTimeUtcFromMillis>
    readonly json: Schema.DateTimeUtcFromMillis
  }>
{}

/**
 * A field that represents a date-time value that is updated as the current
 * `DateTime.Utc`. It is serialized as a `number`.
 *
 * It is set to the current `DateTime.Utc` on updates and inserts and is
 * available for selection.
 *
 * @since 1.0.0
 * @category date & time
 */
export const DateTimeUpdateFromNumber: DateTimeUpdateFromNumber = Field({
  select: Schema.DateTimeUtcFromMillis,
  insert: DateTimeFromNumberWithNow,
  update: DateTimeFromNumberWithNow,
  json: Schema.DateTimeUtcFromMillis
})

/**
 * @since 1.0.0
 * @category json
 */
export interface JsonFromString<S extends Schema.Top> extends
  VariantSchema.Field<{
    readonly select: Schema.fromJsonString<
      Schema.Codec<S["Type"], Schema.Json, S["DecodingServices"], S["EncodingServices"]>
    >
    readonly insert: Schema.fromJsonString<
      Schema.Codec<S["Type"], Schema.Json, S["DecodingServices"], S["EncodingServices"]>
    >
    readonly update: Schema.fromJsonString<
      Schema.Codec<S["Type"], Schema.Json, S["DecodingServices"], S["EncodingServices"]>
    >
    readonly json: S
    readonly jsonCreate: S
    readonly jsonUpdate: S
  }>
{}

/**
 * A field that represents a JSON value stored as text in the database.
 *
 * The "json" variants will use the object schema directly.
 *
 * @since 1.0.0
 * @category json
 */
export const JsonFromString = <S extends Schema.Top>(
  schema: S
): JsonFromString<S> => {
  const parsed = Schema.fromJsonString(Schema.toCodecJson(schema))
  return Field({
    select: parsed,
    insert: parsed,
    update: parsed,
    json: schema,
    jsonCreate: schema,
    jsonUpdate: schema
  })
}

/**
 * Create a simple CRUD repository from a model.
 *
 * @since 1.0.0
 * @category repository
 */
export const makeRepository = <
  S extends Any,
  Id extends (keyof S["Type"]) & (keyof S["update"]["Type"]) & (keyof S["fields"])
>(Model: S, options: {
  readonly tableName: string
  readonly spanPrefix: string
  readonly idColumn: Id
  readonly versionColumn?: string | undefined
}): Effect.Effect<
  {
    readonly insert: (
      insert: S["insert"]["Type"]
    ) => Effect.Effect<S["Type"], Schema.SchemaError, S["DecodingServices"] | S["insert"]["EncodingServices"]>
    readonly insertVoid: (
      insert: S["insert"]["Type"]
    ) => Effect.Effect<void, Schema.SchemaError, S["insert"]["EncodingServices"]>
    readonly update: (
      update: S["update"]["Type"]
    ) => Effect.Effect<S["Type"], Schema.SchemaError, S["DecodingServices"] | S["update"]["EncodingServices"]>
    readonly updateVoid: (
      update: S["update"]["Type"]
    ) => Effect.Effect<void, Schema.SchemaError, S["update"]["EncodingServices"]>
    readonly findById: (
      id: S["fields"][Id]["Type"]
    ) => Effect.Effect<
      Option.Option<S["Type"]>,
      Schema.SchemaError,
      S["DecodingServices"] | S["fields"][Id]["EncodingServices"]
    >
    readonly delete: (
      id: S["fields"][Id]["Type"]
    ) => Effect.Effect<void, Schema.SchemaError, S["fields"][Id]["EncodingServices"]>
  },
  never,
  SqlClient
> =>
  Effect.gen(function*() {
    const sql = yield* SqlClient
    const idSchema = Model.fields[options.idColumn] as Schema.Top
    const idColumn = options.idColumn as string
    const versionColumn = options.versionColumn

    const insertSchema = SqlSchema.findOne({
      Request: Model.insert,
      Result: Model,
      execute: (request) =>
        sql.onDialectOrElse({
          mysql: () =>
            sql`insert into ${sql(options.tableName)} ${sql.insert(request as any)};
select * from ${sql(options.tableName)} where ${sql(idColumn)} = LAST_INSERT_ID();`
              .unprepared
              .pipe(
                Effect.map(([, results]) => results as any)
              ),
          orElse: () => sql`insert into ${sql(options.tableName)} ${sql.insert(request as any).returning("*")}`
        })
    })
    const insert = (
      insert: S["insert"]["Type"]
    ): Effect.Effect<S["Type"], Schema.SchemaError, S["DecodingServices"] | S["insert"]["EncodingServices"]> =>
      insertSchema(insert).pipe(
        Effect.catchTag("NoSuchElementError", Effect.die),
        Effect.withSpan(`${options.spanPrefix}.insert`, {}, {
          captureStackTrace: false
        })
      ) as any

    const insertVoidSchema = SqlSchema.void({
      Request: Model.insert,
      execute: (request) => sql`insert into ${sql(options.tableName)} ${sql.insert(request as any)}`
    })
    const insertVoid = (
      insert: S["insert"]["Type"]
    ): Effect.Effect<void, Schema.SchemaError, S["insert"]["EncodingServices"]> =>
      insertVoidSchema(insert).pipe(
        Effect.withSpan(`${options.spanPrefix}.insertVoid`, {}, {
          captureStackTrace: false
        })
      ) as any

    const updateSchema = SqlSchema.findOne({
      Request: Model.update,
      Result: Model,
      execute: versionColumn
        ? (request: any) =>
          sql.onDialectOrElse({
            mysql: () =>
              sql`update ${sql(options.tableName)} set ${
                sql.update({ ...request, [versionColumn]: crypto.randomUUID() }, [idColumn])
              } where ${sql(idColumn)} = ${request[idColumn]} and ${sql(versionColumn)} = ${request[versionColumn]};
select * from ${sql(options.tableName)} where ${sql(idColumn)} = ${request[idColumn]};`
                .unprepared
                .pipe(
                  Effect.map(([, results]) => results as any)
                ),
            orElse: () =>
              sql`update ${sql(options.tableName)} set ${
                sql.update({ ...request, [versionColumn]: crypto.randomUUID() }, [idColumn])
              } where ${sql(idColumn)} = ${request[idColumn]} and ${sql(versionColumn)} = ${
                request[versionColumn]
              } returning *`
          })
        : (request: any) =>
          sql.onDialectOrElse({
            mysql: () =>
              sql`update ${sql(options.tableName)} set ${sql.update(request, [idColumn])} where ${sql(idColumn)} = ${
                request[idColumn]
              };
select * from ${sql(options.tableName)} where ${sql(idColumn)} = ${request[idColumn]};`
                .unprepared
                .pipe(
                  Effect.map(([, results]) => results as any)
                ),
            orElse: () =>
              sql`update ${sql(options.tableName)} set ${sql.update(request, [idColumn])} where ${sql(idColumn)} = ${
                request[idColumn]
              } returning *`
          })
    })
    const update = (
      update: S["update"]["Type"]
    ): Effect.Effect<S["Type"], Schema.SchemaError, S["DecodingServices"] | S["update"]["EncodingServices"]> =>
      updateSchema(update).pipe(
        Effect.catchTag("NoSuchElementError", Effect.die),
        Effect.withSpan(`${options.spanPrefix}.update`, {
          attributes: { id: (update as any)[idColumn] }
        }, {
          captureStackTrace: false
        })
      ) as any

    const updateVoidSchema = SqlSchema.void({
      Request: Model.update,
      execute: versionColumn
        ? (request: any) =>
          sql`update ${sql(options.tableName)} set ${
            sql.update({ ...request, [versionColumn]: crypto.randomUUID() }, [idColumn])
          } where ${sql(idColumn)} = ${request[idColumn]} and ${sql(versionColumn)} = ${request[versionColumn]}`
        : (request: any) =>
          sql`update ${sql(options.tableName)} set ${sql.update(request, [idColumn])} where ${sql(idColumn)} = ${
            request[idColumn]
          }`
    })
    const updateVoid = (
      update: S["update"]["Type"]
    ): Effect.Effect<void, Schema.SchemaError, S["update"]["EncodingServices"]> =>
      updateVoidSchema(update).pipe(
        Effect.withSpan(`${options.spanPrefix}.updateVoid`, {
          attributes: { id: (update as any)[idColumn] }
        }, {
          captureStackTrace: false
        })
      ) as any

    const findByIdSchema = SqlSchema.findOneOption({
      Request: idSchema,
      Result: Model,
      execute: (id: any) => sql`select * from ${sql(options.tableName)} where ${sql(idColumn)} = ${id}`
    })
    const findById = (
      id: S["fields"][Id]["Type"]
    ): Effect.Effect<
      Option.Option<S["Type"]>,
      Schema.SchemaError,
      S["DecodingServices"] | S["fields"][Id]["EncodingServices"]
    > =>
      findByIdSchema(id).pipe(
        Effect.withSpan(`${options.spanPrefix}.findById`, { attributes: { id } }, {
          captureStackTrace: false
        })
      ) as any

    const deleteSchema = SqlSchema.void({
      Request: idSchema,
      execute: (id: any) => sql`delete from ${sql(options.tableName)} where ${sql(idColumn)} = ${id}`
    })
    const delete_ = (
      id: S["fields"][Id]["Type"]
    ): Effect.Effect<void, Schema.SchemaError, S["fields"][Id]["EncodingServices"]> =>
      deleteSchema(id).pipe(
        Effect.withSpan(`${options.spanPrefix}.delete`, {
          attributes: { id }
        }, {
          captureStackTrace: false
        })
      ) as any

    return { insert, insertVoid, update, updateVoid, findById, delete: delete_ } as const
  })

/**
 * Create some simple data loaders from a model.
 *
 * @since 1.0.0
 * @category repository
 */
export const makeDataLoaders = <
  S extends Any,
  Id extends (keyof S["Type"]) & (keyof S["update"]["Type"]) & (keyof S["fields"])
>(
  Model: S,
  options: {
    readonly tableName: string
    readonly spanPrefix: string
    readonly idColumn: Id
    readonly window: Input
    readonly maxBatchSize?: number | undefined
  }
): Effect.Effect<
  {
    readonly insert: (
      insert: S["insert"]["Type"]
    ) => Effect.Effect<
      S["Type"],
      Schema.SchemaError,
      S["DecodingServices"] | S["insert"]["EncodingServices"]
    >
    readonly insertVoid: (
      insert: S["insert"]["Type"]
    ) => Effect.Effect<void, Schema.SchemaError, S["insert"]["EncodingServices"]>
    readonly findById: (
      id: S["fields"][Id]["Type"]
    ) => Effect.Effect<
      S["Type"],
      Schema.SchemaError,
      S["DecodingServices"] | S["fields"][Id]["EncodingServices"]
    >
    readonly delete: (
      id: S["fields"][Id]["Type"]
    ) => Effect.Effect<void, Schema.SchemaError, S["fields"][Id]["EncodingServices"]>
  },
  never,
  SqlClient | Scope
> =>
  Effect.gen(function*() {
    const sql = yield* SqlClient
    const idSchema = Model.fields[options.idColumn] as Schema.Top
    const idColumn = options.idColumn as string
    const setMaxBatchSize = options.maxBatchSize ? RequestResolver.batchN(options.maxBatchSize) : identity

    const insertResolver = SqlResolver
      .ordered({
        Request: Model.insert,
        Result: Model,
        execute: (request: any) =>
          sql.onDialectOrElse({
            mysql: () =>
              Effect.forEach(request, (request: any) =>
                sql`insert into ${sql(options.tableName)} ${sql.insert(request)};
select * from ${sql(options.tableName)} where ${sql(idColumn)} = LAST_INSERT_ID();`
                  .unprepared
                  .pipe(
                    Effect.map(([, results]) => results![0] as any)
                  ), { concurrency: 10 }),
            orElse: () => sql`insert into ${sql(options.tableName)} ${sql.insert(request).returning("*")}`
          })
      })
      .pipe(
        RequestResolver.setDelay(options.window),
        setMaxBatchSize,
        RequestResolver.withSpan(`${options.spanPrefix}.insertResolver`)
      )
    const insertExecute = SqlResolver.request(insertResolver)
    const insert = (
      insert: S["insert"]["Type"]
    ): Effect.Effect<
      S["Type"],
      Schema.SchemaError,
      S["DecodingServices"] | S["insert"]["EncodingServices"]
    > =>
      insertExecute(insert).pipe(
        Effect.catchTag("ResultLengthMismatch", Effect.die),
        Effect.withSpan(`${options.spanPrefix}.insert`, {}, {
          captureStackTrace: false
        })
      ) as any

    const insertVoidResolver = SqlResolver
      .void({
        Request: Model.insert,
        execute: (request: any) => sql`insert into ${sql(options.tableName)} ${sql.insert(request)}`
      })
      .pipe(
        RequestResolver.setDelay(options.window),
        setMaxBatchSize,
        RequestResolver.withSpan(`${options.spanPrefix}.insertVoidResolver`)
      )
    const insertVoidExecute = SqlResolver.request(insertVoidResolver)
    const insertVoid = (
      insert: S["insert"]["Type"]
    ): Effect.Effect<void, Schema.SchemaError, S["insert"]["EncodingServices"]> =>
      insertVoidExecute(insert).pipe(
        Effect.withSpan(`${options.spanPrefix}.insertVoid`, {}, {
          captureStackTrace: false
        })
      ) as any

    const findByIdResolver = SqlResolver
      .findById({
        Id: idSchema,
        Result: Model,
        ResultId(request: any) {
          return request[idColumn]
        },
        execute: (ids: any) => sql`select * from ${sql(options.tableName)} where ${sql.in(idColumn, ids)}`
      })
      .pipe(
        RequestResolver.setDelay(options.window),
        setMaxBatchSize,
        RequestResolver.withSpan(`${options.spanPrefix}.findByIdResolver`)
      )
    const findByIdExecute = SqlResolver.request(findByIdResolver)
    const findById = (
      id: S["fields"][Id]["Type"]
    ): Effect.Effect<
      S["Type"],
      Schema.SchemaError,
      S["DecodingServices"] | S["fields"][Id]["EncodingServices"]
    > =>
      findByIdExecute(id).pipe(
        Effect.withSpan(`${options.spanPrefix}.findById`, { attributes: { id } }, {
          captureStackTrace: false
        })
      ) as any

    const deleteResolver = SqlResolver
      .void({
        Request: idSchema,
        execute: (ids: any) => sql`delete from ${sql(options.tableName)} where ${sql.in(idColumn, ids)}`
      })
      .pipe(
        RequestResolver.setDelay(options.window),
        setMaxBatchSize,
        RequestResolver.withSpan(`${options.spanPrefix}.deleteResolver`)
      )
    const deleteExecute = SqlResolver.request(deleteResolver)
    const delete_ = (
      id: S["fields"][Id]["Type"]
    ): Effect.Effect<void, Schema.SchemaError, S["fields"][Id]["EncodingServices"]> =>
      deleteExecute(id).pipe(
        Effect.withSpan(`${options.spanPrefix}.delete`, { attributes: { id } }, {
          captureStackTrace: false
        })
      ) as any

    return { insert, insertVoid, findById, delete: delete_ } as const
  })