// oxlint-disable max-lines
// oxlint-disable import/no-relative-parent-imports
import { sql } from 'drizzle-orm'
import { boolean, check, index, integer, jsonb, numeric, pgTable, serial, text, timestamp, unique, uuid, varchar } from 'drizzle-orm/pg-core'
// FIXME: drizzle-kit can't handle #shared/constants, so we have to import it with a relative path
import { limits } from '../../shared/constants'

// ─── Auth ───────────────────────────────────────────────────────────

/**
 * Guest and registered user accounts.
 *
 * Created automatically on Guest or OAuth login.
 */
const users = pgTable('users', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  name: varchar({
    length: limits.maxUserNameLength
  }),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow(),

  isAdmin:
    boolean()
    .notNull()
    .default(false)
})

/**
 * Supported OAuth providers (e.g. Twitch).
 *
 * Seeded on deployment, not user-managed.
 */
const oauthProviders = pgTable('oauth_providers', {
  id:
    serial()
    .primaryKey(),

  type:
    varchar({
      length: limits.maxOAuthProviderTypeLength
    })
    .notNull()
    .unique(),

  name:
    varchar({
      length: limits.maxOAuthProviderNameLength
    })
    .notNull(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
})

/**
 * Links between users and their OAuth provider accounts.
 *
 * A user can have multiple OAuth accounts across different providers.
 */
const oauthAccounts = pgTable('oauth_accounts', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  userId:
    uuid()
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  providerId:
    integer()
    .notNull()
    .references(() => oauthProviders.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  accountId:
    varchar()
    .notNull(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
}, (table) => [
  unique().on(table.providerId, table.accountId)
])

// ─── Equipment catalog ──────────────────────────────────────────────

/**
 * Top-level functional groupings (e.g. Sleep, Shelter, Cooking).
 *
 * Used for navigation and organizing categories.
 */
const equipmentGroups = pgTable('equipment_groups', {
  id:
    serial()
    .primaryKey(),

  name:
    varchar({ length: limits.maxEquipmentGroupNameLength })
    .notNull(),

  slug:
    varchar({ length: limits.maxEquipmentGroupSlugLength })
    .notNull()
    .unique(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
})

/**
 * Specific equipment types (e.g. Sleeping Bags, Sleeping Pads).
 *
 * Each category defines its own set of properties via `category_properties`.
 */
const equipmentCategories = pgTable('equipment_categories', {
  id:
    serial()
    .primaryKey(),

  name:
    varchar({ length: limits.maxEquipmentCategoryNameLength })
    .notNull(),

  slug:
    varchar({ length: limits.maxEquipmentCategorySlugLength })
    .notNull()
    .unique(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
})

/**
 * Equipment manufacturers (e.g. Therm-a-Rest, Nemo).
 *
 * Each item belongs to exactly one brand.
 */
const brands = pgTable('brands', {
  id:
    serial()
    .primaryKey(),

  name:
    varchar({ length: limits.maxBrandNameLength })
    .notNull()
    .unique(),

  slug:
    varchar({ length: limits.maxBrandSlugLength })
    .notNull()
    .unique(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
})

/**
 * Individual equipment entries in the global catalog.
 *
 * Each size/variant is a separate item. Status controls visibility (approved/pending/rejected).
 */
const equipmentItems = pgTable('equipment_items', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  categoryId:
    integer()
    .notNull()
    .references(() => equipmentCategories.id, {
      onDelete: 'restrict',
      onUpdate: 'cascade'
    }),

  brandId:
    integer()
    .notNull()
    .references(() => brands.id, {
      onDelete: 'restrict',
      onUpdate: 'cascade'
    }),

  name:
    varchar({ length: 256 })
    .notNull(),

  status:
    varchar({ length: 16 })
    .notNull()
    .default('approved'),

  rejectionReason:
    varchar({ length: limits.maxEquipmentItemRejectionReasonLength }),

  createdBy:
    uuid()
    .references(() => users.id, {
      onDelete: 'set null',
      onUpdate: 'cascade'
    }),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow(),

  updatedAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`now()`)
}, (table) => [
  index('equipment_items_approved_category_brand_index')
    .on(table.categoryId, table.brandId)
    .where(sql`${table.status} = 'approved'`)
])

const equipmentItemImageDisplayOrderConstraintName = 'equipment_item_images_itemId_displayOrder_unique'

/**
 * Ordered Cloudflare-hosted images for equipment items.
 *
 * The first image (`displayOrder = 0`) is the item's primary image.
 */
const equipmentItemImages = pgTable('equipment_item_images', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  itemId:
    uuid()
    .notNull()
    .references(() => equipmentItems.id, {
      onDelete: 'restrict',
      onUpdate: 'cascade'
    }),

  cloudflareImageId:
    text()
    .notNull()
    .unique(),

  displayOrder:
    integer()
    .notNull(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow(),

  updatedAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`now()`)
}, (table) => [
  unique(equipmentItemImageDisplayOrderConstraintName).on(table.itemId, table.displayOrder),
  check('equipment_item_images_displayOrder_check', sql`${table.displayOrder} >= 0`)
])

const categoryPropertyDisplayOrderConstraintName = 'category_properties_categoryId_displayOrder_unique'

/**
 * Property definitions per category (EAV schema layer).
 *
 * Defines what properties a category has (e.g. R-Value for sleeping bags). Supports number, text, boolean, and enum data types.
 */
const categoryProperties = pgTable('category_properties', {
  id:
    serial()
    .primaryKey(),

  categoryId:
    integer()
    .notNull()
    .references(() => equipmentCategories.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  name:
    varchar({ length: limits.maxCategoryPropertyNameLength })
    .notNull(),

  slug:
    varchar({ length: limits.maxCategoryPropertySlugLength })
    .notNull(),

  dataType:
    varchar({ length: 16 })
    .notNull(),

  allowsNegativeValues:
    boolean()
    .notNull()
    .default(false),

  displayOrder:
    integer()
    .notNull(),

  unit:
    varchar({ length: limits.maxCategoryPropertyUnitLength }),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
}, (table) => [
  unique().on(table.categoryId, table.slug),
  unique(categoryPropertyDisplayOrderConstraintName).on(table.categoryId, table.displayOrder)
])

/**
 * Allowed values for enum-type properties.
 *
 * E.g. Fill Type property might have options: "Down", "Synthetic".
 */
const propertyEnumOptions = pgTable('property_enum_options', {
  id:
    serial()
    .primaryKey(),

  propertyId:
    integer()
    .notNull()
    .references(() => categoryProperties.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  name:
    varchar({ length: limits.maxPropertyEnumOptionNameLength })
    .notNull(),

  slug:
    varchar({ length: limits.maxPropertyEnumOptionSlugLength })
    .notNull()
}, (table) => [
  unique().on(table.propertyId, table.slug)
])

/**
 * Actual property values for items (EAV value layer).
 *
 * Stores one value per item–property pair. Only one of valueText/valueNumber/valueBoolean is set depending on the property's dataType.
 */
const itemPropertyValues = pgTable('item_property_values', {
  id:
    serial()
    .primaryKey(),

  itemId:
    uuid()
    .notNull()
    .references(() => equipmentItems.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  propertyId:
    integer()
    .notNull()
    .references(() => categoryProperties.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  valueText:
    varchar(),

  valueNumber:
    numeric(),

  valueBoolean:
    boolean()
}, (table) => [
  unique().on(table.itemId, table.propertyId),
  index('item_property_values_property_number_index').on(table.propertyId, table.valueNumber, table.itemId),
  index('item_property_values_property_text_index').on(table.propertyId, table.valueText, table.itemId),
  index('item_property_values_property_boolean_index').on(table.propertyId, table.valueBoolean, table.itemId)
])

// ─── User data ──────────────────────────────────────────────────────

/**
 * User's saved gear rows.
 *
 * Tracks which equipment reference items a user saved to My gear.
 */
const userEquipment = pgTable('user_equipment', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  userId:
    uuid()
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  itemId:
    uuid()
    .notNull()
    .references(() => equipmentItems.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
}, (table) => [
  unique().on(table.userId, table.itemId)
])

/**
 * User-owned packing list shell.
 */
const packingLists = pgTable('packing_lists', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  userId:
    uuid()
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  name:
    varchar({ length: limits.maxPackingListNameLength })
    .notNull(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow(),

  updatedAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`now()`)
})

/**
 * Checklist entries scoped to one user-owned packing list.
 *
 * An entry is either a custom text row or a link to one saved gear row.
 */
const packingListEntries = pgTable('packing_list_entries', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  packingListId:
    uuid()
    .notNull()
    .references(() => packingLists.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  customName:
    varchar({ length: limits.maxPackingListEntryCustomNameLength }),

  userEquipmentId:
    uuid()
    .references(() => userEquipment.id, {
      onDelete: 'restrict',
      onUpdate: 'cascade'
    }),

  isPacked:
    boolean()
    .notNull()
    .default(false),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow(),

  updatedAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`now()`)
}, (table) => [
  check(
    'packing_list_entries_source_check',
    sql`(("packing_list_entries"."customName" IS NOT NULL) <> ("packing_list_entries"."userEquipmentId" IS NOT NULL))`
  ),
  unique().on(table.packingListId, table.userEquipmentId)
])

/**
 * Activity log for tracking user contributions.
 *
 * Records every catalog write operation (create/update/delete). Used for future gamification and reputation system.
 */
const contributions = pgTable('contributions', {
  id:
    uuid()
    .notNull()
    .default(sql`uuidv7()`)
    .primaryKey(),

  userId:
    uuid()
    .notNull()
    .references(() => users.id, {
      onDelete: 'cascade',
      onUpdate: 'cascade'
    }),

  action:
    varchar({ length: 32 })
    .notNull(),

  /** Stringified primary key of the changed catalog entity. Stores either a serial ID or a UUID string. */
  targetId:
    text()
    .notNull(),

  metadata:
    jsonb(),

  createdAt:
    timestamp({
      withTimezone: true
    })
    .notNull()
    .defaultNow()
})

export {
  users,
  oauthProviders,
  oauthAccounts,
  equipmentGroups,
  equipmentCategories,
  brands,
  equipmentItems,
  equipmentItemImages,
  categoryProperties,
  propertyEnumOptions,
  itemPropertyValues,
  userEquipment,
  packingLists,
  packingListEntries,
  contributions,
  categoryPropertyDisplayOrderConstraintName,
  equipmentItemImageDisplayOrderConstraintName
}
