import {sql} from 'drizzle-orm'
import {
  check,
  doublePrecision,
  index,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  type AnyPgColumn,
} from 'drizzle-orm/pg-core'

import type {MentionFacet} from '../mentions'
import type {
  CrumbSource,
  CrumbStatus,
  EventStatus,
  ReminderKind,
  SourceKind,
} from '../schemas/crumbs'
import type {
  ReportReason,
  ReportSubjectType,
} from '../schemas/moderation'
import type {PlaceStatus} from '../schemas/places'

type StoredFriendshipStatus = 'pending' | 'accepted'
type StoredCollaboratorStatus = 'pending' | 'accepted'

export const users = pgTable(
  'users',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    email: text('email').notNull(),
    handle: text('handle').notNull(),
    avatarRef: text('avatar_ref'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    // case-insensitivity is enforced at the write boundaries (emails are
    // lowercased in upsertUserByEmail, handles by their schema), so these
    // index the stored — already-lowercase — values directly
    uniqueIndex('users_email_unique_idx').on(t.email),
    uniqueIndex('users_handle_unique_idx').on(t.handle),
  ],
)

export const sessions = pgTable(
  'sessions',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    tokenHash: text('token_hash').notNull(),
    expiresAt: timestamp('expires_at', {
      withTimezone: true,
    }).notNull(),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('sessions_token_hash_idx').on(t.tokenHash),
    index('sessions_user_id_idx').on(t.userId),
  ],
)

export const pushTokens = pgTable(
  'push_tokens',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    token: text('token').notNull(),
    platform: text('platform').notNull().default('ios'),
    timezone: text('timezone'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('push_tokens_token_idx').on(t.token),
    index('push_tokens_user_id_idx').on(t.userId),
  ],
)

export const reminders = pgTable(
  'reminders',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    crumbId: uuid('crumb_id')
      .notNull()
      .references(() => crumbs.id, {onDelete: 'cascade'}),
    remindAt: timestamp('remind_at', {withTimezone: true}).notNull(),
    kind: text('kind')
      .$type<ReminderKind>()
      .notNull()
      .default('manual'),
    sentAt: timestamp('sent_at', {withTimezone: true}),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('reminders_due_idx')
      .on(t.remindAt)
      .where(sql`${t.sentAt} is null`),
    // one auto reminder per user/crumb/kind; manual reminders unlimited
    uniqueIndex('reminders_auto_idx')
      .on(t.userId, t.crumbId, t.kind)
      .where(sql`${t.kind} <> 'manual'`),
    index('reminders_crumb_id_idx').on(t.crumbId),
  ],
)

// canonical place entities — never deleted, only merged (tombstone + redirect)
export const places = pgTable(
  'places',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    latitude: doublePrecision('latitude'),
    longitude: doublePrecision('longitude'),
    address: text('address'),
    category: text('category'),
    status: text('status')
      .$type<PlaceStatus>()
      .notNull()
      .default('provisional'),
    mergedIntoId: uuid('merged_into_id').references(
      (): AnyPgColumn => places.id,
    ),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('places_lat_lng_idx').on(t.latitude, t.longitude),
    index('places_status_idx')
      .on(t.status)
      .where(sql`${t.status} = 'provisional'`),
    check(
      'places_merged_iff_redirect',
      sql`(${t.status} = 'merged') = (${t.mergedIntoId} is not null)`,
    ),
  ],
)

// immutable provider evidence; identity is places.id, never a provider id
export const placeSources = pgTable(
  'place_sources',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    placeId: uuid('place_id')
      .notNull()
      .references(() => places.id, {onDelete: 'cascade'}),
    provider: text('provider').notNull(),
    osmType: text('osm_type'),
    osmId: text('osm_id'),
    raw: jsonb('raw').notNull(),
    queryText: text('query_text'),
    fetchedAt: timestamp('fetched_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('place_sources_provider_osm_idx')
      .on(t.provider, t.osmType, t.osmId)
      .where(sql`${t.osmId} is not null`),
    index('place_sources_place_id_idx').on(t.placeId),
  ],
)

// merge evidence enabling unmerge
export const placeMerges = pgTable('place_merges', {
  id: uuid('id').primaryKey().defaultRandom(),
  loserId: uuid('loser_id')
    .notNull()
    .references(() => places.id),
  winnerId: uuid('winner_id')
    .notNull()
    .references(() => places.id),
  reason: text('reason'),
  createdAt: timestamp('created_at', {withTimezone: true})
    .notNull()
    .defaultNow(),
})

// shared provenance: one imported link/screengrab can yield many crumbs
export const sources = pgTable(
  'sources',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    kind: text('kind').$type<SourceKind>().notNull(),
    url: text('url'),
    // display image auto-found for the source (a link's og:image)
    imageRef: text('image_ref'),
    // the original generation input, kept for provenance (a screengrab)
    assetRef: text('asset_ref'),
    sharedById: uuid('shared_by_id').references(() => users.id, {
      onDelete: 'set null',
    }),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('sources_url_idx')
      .on(t.url)
      .where(sql`${t.url} is not null`),
  ],
)

// one row per occurrence; venue nullable (tbd/virtual)
export const events = pgTable(
  'events',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    placeId: uuid('place_id').references(() => places.id),
    name: text('name').notNull(),
    startsAt: timestamp('starts_at', {withTimezone: true}),
    endsAt: timestamp('ends_at', {withTimezone: true}),
    url: text('url'),
    status: text('status')
      .$type<EventStatus>()
      .notNull()
      .default('scheduled'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('events_place_id_idx').on(t.placeId),
    index('events_starts_at_idx').on(t.startsAt),
  ],
)

// the user-owned save edge — all per-user state lives here, never on entities
export const crumbs = pgTable(
  'crumbs',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    authorId: uuid('author_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    name: text('name').notNull(),
    note: text('note'),
    // manual per-crumb image; wins over the source image when set
    imageRef: text('image_ref'),
    placeId: uuid('place_id').references(() => places.id, {
      onDelete: 'restrict',
    }),
    eventId: uuid('event_id').references(() => events.id, {
      onDelete: 'restrict',
    }),
    sourceId: uuid('source_id').references(() => sources.id, {
      onDelete: 'set null',
    }),
    source: text('source')
      .$type<CrumbSource>()
      .notNull()
      .default('manual'),
    status: text('status')
      .$type<CrumbStatus>()
      .notNull()
      .default('saved'),
    plannedFor: timestamp('planned_for', {withTimezone: true}),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('crumbs_author_id_idx').on(t.authorId),
    index('crumbs_created_at_idx').on(t.createdAt),
    index('crumbs_place_id_idx').on(t.placeId),
    index('crumbs_event_id_idx').on(t.eventId),
    index('crumbs_source_id_idx').on(t.sourceId),
    check(
      'crumbs_exclusive_target',
      sql`num_nonnulls(${t.placeId}, ${t.eventId}) <= 1`,
    ),
  ],
)

export const tags = pgTable(
  'tags',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    emoji: text('emoji').notNull().default('📍'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [uniqueIndex('tags_name_idx').on(t.name)],
)

export const crumbTags = pgTable(
  'crumb_tags',
  {
    crumbId: uuid('crumb_id')
      .notNull()
      .references(() => crumbs.id, {onDelete: 'cascade'}),
    tagId: uuid('tag_id')
      .notNull()
      .references(() => tags.id, {onDelete: 'restrict'}),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    primaryKey({columns: [t.crumbId, t.tagId]}),
    index('crumb_tags_tag_id_idx').on(t.tagId),
  ],
)

export const trails = pgTable(
  'trails',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    authorId: uuid('author_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    name: text('name').notNull(),
    description: text('description'),
    // manual trail cover; wins over the first crumb image when set
    coverImageRef: text('cover_image_ref'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('trails_author_id_idx').on(t.authorId),
    index('trails_created_at_idx').on(t.createdAt),
  ],
)

export const trailCrumbs = pgTable(
  'trail_crumbs',
  {
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    crumbId: uuid('crumb_id')
      .notNull()
      .references(() => crumbs.id, {onDelete: 'cascade'}),
    // who filed this crumb into the trail — may differ from the crumb's
    // author now that members can add others' crumbs. removal rights key off
    // this rather than authorship
    addedBy: uuid('added_by')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    position: integer('position').notNull(),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    primaryKey({columns: [t.trailId, t.crumbId]}),
    uniqueIndex('trail_crumbs_trail_position_idx').on(
      t.trailId,
      t.position,
    ),
    index('trail_crumbs_crumb_id_idx').on(t.crumbId),
  ],
)

export const friendships = pgTable(
  'friendships',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    requesterId: uuid('requester_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    addresseeId: uuid('addressee_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    status: text('status')
      .$type<StoredFriendshipStatus>()
      .notNull()
      .default('pending'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('friendships_pair_idx').on(
      t.requesterId,
      t.addresseeId,
    ),
    index('friendships_addressee_id_idx').on(t.addresseeId),
    index('friendships_requester_id_idx').on(t.requesterId),
    check(
      'friendships_no_self',
      sql`${t.requesterId} <> ${t.addresseeId}`,
    ),
  ],
)

// a directional block edge (blocker → blocked) whose effects are enforced
// bidirectionally: severs friendship, requests, invites, dms, search and
// content visibility either way. blocking removes friendship but is kept
// non-destructive to shared trails (their content is merely filtered)
export const userBlocks = pgTable(
  'user_blocks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    blockerId: uuid('blocker_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    blockedId: uuid('blocked_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('user_blocks_pair_idx').on(t.blockerId, t.blockedId),
    index('user_blocks_blocked_id_idx').on(t.blockedId),
    check(
      'user_blocks_no_self',
      sql`${t.blockerId} <> ${t.blockedId}`,
    ),
  ],
)

// abuse reports, persisted for later human review (no admin surface yet):
// a reporter, the subject they flag, a coarse reason and optional detail
export const reports = pgTable(
  'reports',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    reporterId: uuid('reporter_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    subjectType: text('subject_type')
      .$type<ReportSubjectType>()
      .notNull(),
    subjectId: uuid('subject_id').notNull(),
    reason: text('reason').$type<ReportReason>().notNull(),
    detail: text('detail'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('reports_subject_idx').on(t.subjectType, t.subjectId),
    index('reports_reporter_id_idx').on(t.reporterId),
  ],
)

export const trailCollaborators = pgTable(
  'trail_collaborators',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    inviterId: uuid('inviter_id').references(() => users.id, {
      onDelete: 'set null',
    }),
    status: text('status')
      .$type<StoredCollaboratorStatus>()
      .notNull()
      .default('pending'),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
    updatedAt: timestamp('updated_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('trail_collaborators_trail_user_idx').on(
      t.trailId,
      t.userId,
    ),
    index('trail_collaborators_user_id_idx').on(t.userId),
    check(
      'trail_collaborators_no_self_invite',
      sql`${t.userId} <> ${t.inviterId}`,
    ),
  ],
)

// shareable join links; revoked links stay as tombstones
export const trailShareLinks = pgTable(
  'trail_share_links',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    token: text('token').notNull(),
    createdBy: uuid('created_by').references(() => users.id, {
      onDelete: 'set null',
    }),
    revokedAt: timestamp('revoked_at', {withTimezone: true}),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('trail_share_links_token_idx').on(t.token),
    index('trail_share_links_trail_id_idx').on(t.trailId),
  ],
)

// lightweight group signal on crumbs inside a shared trail
export const crumbReactions = pgTable(
  'crumb_reactions',
  {
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    crumbId: uuid('crumb_id')
      .notNull()
      .references(() => crumbs.id, {onDelete: 'cascade'}),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    emoji: text('emoji').notNull(),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    primaryKey({columns: [t.trailId, t.crumbId, t.userId]}),
    index('crumb_reactions_crumb_id_idx').on(t.crumbId),
  ],
)

// per-user trail prefs + read state (covers owners and collaborators)
export const trailUserStates = pgTable(
  'trail_user_states',
  {
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    lastViewedAt: timestamp('last_viewed_at', {withTimezone: true}),
    pinnedAt: timestamp('pinned_at', {withTimezone: true}),
  },
  (t) => [
    primaryKey({columns: [t.trailId, t.userId]}),
    index('trail_user_states_user_id_idx').on(t.userId),
  ],
)

// short member-authored posts on a shared trail; facets hold resolved
// @mentions as utf-16 index ranges into text (see core/mentions.ts)
export const trailPosts = pgTable(
  'trail_posts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    trailId: uuid('trail_id')
      .notNull()
      .references(() => trails.id, {onDelete: 'cascade'}),
    authorId: uuid('author_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    text: text('text').notNull(),
    facets: jsonb('facets')
      .$type<MentionFacet[]>()
      .notNull()
      .default([]),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('trail_posts_trail_created_idx').on(t.trailId, t.createdAt),
    index('trail_posts_author_id_idx').on(t.authorId),
  ],
)

// 1:1 conversations; canonical pair (userAId < userBId) makes
// get-or-create a plain upsert on the unique index. read state as
// per-side columns since participants are fixed at two
export const conversations = pgTable(
  'conversations',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    userAId: uuid('user_a_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    userBId: uuid('user_b_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    lastMessageAt: timestamp('last_message_at', {withTimezone: true}),
    userALastReadAt: timestamp('user_a_last_read_at', {
      withTimezone: true,
    }),
    userBLastReadAt: timestamp('user_b_last_read_at', {
      withTimezone: true,
    }),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    uniqueIndex('conversations_pair_idx').on(t.userAId, t.userBId),
    index('conversations_user_b_id_idx').on(t.userBId),
    check(
      'conversations_pair_ordered',
      sql`${t.userAId} < ${t.userBId}`,
    ),
  ],
)

// plain-text direct messages; deliberately no mention facets
export const messages = pgTable(
  'messages',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    conversationId: uuid('conversation_id')
      .notNull()
      .references(() => conversations.id, {onDelete: 'cascade'}),
    senderId: uuid('sender_id')
      .notNull()
      .references(() => users.id, {onDelete: 'cascade'}),
    text: text('text').notNull(),
    createdAt: timestamp('created_at', {withTimezone: true})
      .notNull()
      .defaultNow(),
  },
  (t) => [
    index('messages_conversation_created_idx').on(
      t.conversationId,
      t.createdAt,
    ),
    index('messages_sender_id_idx').on(t.senderId),
  ],
)

export type User = typeof users.$inferSelect
