import { relations } from 'drizzle-orm';
import {
  boolean,
  index,
  integer,
  jsonb,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
  uuid,
  varchar,
} from 'drizzle-orm/pg-core';
import { users } from './auth';
import { channelVisibilityEnum, postStatusEnum, postVariantEnum } from './enums';

/**
 * Community & Research — the first genuinely shared data in CK Terminal.
 *
 * Every other module stores one JSON blob per user in `app_state`, which is
 * correct for a private trading journal and fatal for a community: two accounts
 * could never see the same post. These tables belong to the organisation, not
 * to a user, so `owner_id` deliberately does not appear. Who may read or write
 * them is decided by the `community` entitlement in `user_module_access`.
 *
 * Authors are stored twice on purpose: `author_id` links to the account when
 * one exists, `author_name` preserves the name that was displayed when the
 * content was written. Imported legacy content has no account behind it, and
 * losing the byline would rewrite history.
 */

export const communityChannels = pgTable(
  'community_channels',
  {
    // The legacy slugs ('ck-daily', 'ck-macro') stay the primary key: existing
    // posts, deep links and shared URLs all reference them.
    id: varchar('id', { length: 64 }).primaryKey(),
    name: varchar('name', { length: 160 }).notNull(),
    icon: varchar('icon', { length: 16 }),
    description: text('description'),
    visibility: channelVisibilityEnum('visibility').notNull().default('MEMBERS'),
    position: integer('position').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('community_channels_position_idx').on(table.position)],
);

export const communityPosts = pgTable(
  'community_posts',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    /**
     * The id this post carried inside the legacy blob. Present only on imported
     * rows and unique, which is what makes the import repeatable: a second run
     * recognises what it already inserted instead of duplicating it.
     */
    legacyId: varchar('legacy_id', { length: 64 }),
    channelId: varchar('channel_id', { length: 64 })
      .notNull()
      .references(() => communityChannels.id, { onDelete: 'restrict' }),
    status: postStatusEnum('status').notNull().default('DRAFT'),
    /**
     * Long or short read of the same analysis. Both are real posts a member can
     * open; the variant only tells them which one they are looking at.
     */
    variant: postVariantEnum('variant').notNull().default('FULL'),
    title: text('title').notNull(),
    excerpt: text('excerpt'),
    /** Post body as HTML. Rendered sandboxed — see the reader view. */
    body: text('body').notNull().default(''),
    /** Cover image: a data URI for imported posts, a URL for uploaded ones. */
    cover: text('cover'),
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    authorName: varchar('author_name', { length: 160 }),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    /** Free-text date from the legacy data ("11. Mai 2026"), kept verbatim. */
    legacyDate: varchar('legacy_date', { length: 64 }),
    /**
     * Where an imported post came from in the analysis hub, relative to the
     * `beitraege/` root — the identity that survives a re-export. Posts written
     * in the app have none.
     */
    sourcePath: varchar('source_path', { length: 400 }),
    /**
     * SHA-256 of the source file at import time. The importer compares it to
     * decide between "already have this" and "the analysis was revised", so a
     * re-run is a sync rather than either a duplicate or a blind overwrite.
     */
    sourceHash: varchar('source_hash', { length: 64 }),
    views: integer('views').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('community_posts_legacy_id_unique').on(table.legacyId),
    uniqueIndex('community_posts_source_path_unique').on(table.sourcePath),
    index('community_posts_channel_idx').on(table.channelId),
    index('community_posts_status_published_idx').on(table.status, table.publishedAt),
  ],
);

export const communityPostComments = pgTable(
  'community_post_comments',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    legacyId: varchar('legacy_id', { length: 64 }),
    postId: uuid('post_id')
      .notNull()
      .references(() => communityPosts.id, { onDelete: 'cascade' }),
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    authorName: varchar('author_name', { length: 160 }),
    body: text('body').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('community_post_comments_legacy_id_unique').on(table.legacyId),
    index('community_post_comments_post_idx').on(table.postId, table.createdAt),
  ],
);

/**
 * One row per (post, account). The legacy data had a single `liked` flag shared
 * by everyone who opened the blob — with real accounts a like has to belong to
 * somebody, and the count is derived rather than stored.
 */
export const communityPostLikes = pgTable(
  'community_post_likes',
  {
    postId: uuid('post_id')
      .notNull()
      .references(() => communityPosts.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [primaryKey({ columns: [table.postId, table.userId] })],
);

export const communityChats = pgTable('community_chats', {
  id: varchar('id', { length: 64 }).primaryKey(),
  name: varchar('name', { length: 160 }).notNull(),
  description: text('description'),
  createdById: uuid('created_by_id').references(() => users.id, { onDelete: 'set null' }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
});

export const communityChatMessages = pgTable(
  'community_chat_messages',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    legacyId: varchar('legacy_id', { length: 64 }),
    chatId: varchar('chat_id', { length: 64 })
      .notNull()
      .references(() => communityChats.id, { onDelete: 'cascade' }),
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    authorName: varchar('author_name', { length: 160 }),
    body: text('body').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [
    uniqueIndex('community_chat_messages_legacy_id_unique').on(table.legacyId),
    index('community_chat_messages_chat_idx').on(table.chatId, table.createdAt),
  ],
);

/**
 * Announcements — the portal's own voice, above the channels.
 *
 * Not a channel with a different coat of paint: a channel is a publication a
 * member subscribes to, an announcement is CK addressing everyone at once. The
 * monthly outlook, a market event worth interrupting for, a change at CK. They
 * reach every member regardless of which channels they hold, which is exactly
 * why they cannot live inside one.
 */
export const communityAnnouncements = pgTable(
  'community_announcements',
  {
    id: uuid('id').defaultRandom().primaryKey(),
    status: postStatusEnum('status').notNull().default('DRAFT'),
    title: text('title').notNull(),
    /** One line under the headline, shown in the list and the banner. */
    summary: text('summary'),
    body: text('body').notNull().default(''),
    cover: text('cover'),
    /** Kept at the top of the list until an editor unpins it. */
    pinned: boolean('pinned').notNull().default(false),
    authorId: uuid('author_id').references(() => users.id, { onDelete: 'set null' }),
    authorName: varchar('author_name', { length: 160 }),
    publishedAt: timestamp('published_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [index('community_announcements_published_idx').on(table.status, table.publishedAt)],
);

/**
 * Who has seen an announcement. An announcement that goes to everyone is only
 * worth sending if you can tell whether it arrived, so reach is counted from
 * real reads rather than assumed.
 */
export const communityAnnouncementReads = pgTable(
  'community_announcement_reads',
  {
    announcementId: uuid('announcement_id')
      .notNull()
      .references(() => communityAnnouncements.id, { onDelete: 'cascade' }),
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    readAt: timestamp('read_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => [primaryKey({ columns: [table.announcementId, table.userId] })],
);

/**
 * Portal-wide settings (organisation name, tagline, whether comments and chat
 * are open). One row per key so a new setting is an insert, not a migration.
 */
export const communitySettings = pgTable('community_settings', {
  key: varchar('key', { length: 64 }).primaryKey(),
  value: jsonb('value'),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

export const communityChannelsRelations = relations(communityChannels, ({ many }) => ({
  posts: many(communityPosts),
}));

export const communityPostsRelations = relations(communityPosts, ({ one, many }) => ({
  channel: one(communityChannels, {
    fields: [communityPosts.channelId],
    references: [communityChannels.id],
  }),
  author: one(users, { fields: [communityPosts.authorId], references: [users.id] }),
  comments: many(communityPostComments),
  likes: many(communityPostLikes),
}));

export const communityPostCommentsRelations = relations(communityPostComments, ({ one }) => ({
  post: one(communityPosts, {
    fields: [communityPostComments.postId],
    references: [communityPosts.id],
  }),
}));

export const communityChatMessagesRelations = relations(communityChatMessages, ({ one }) => ({
  chat: one(communityChats, {
    fields: [communityChatMessages.chatId],
    references: [communityChats.id],
  }),
}));

export type CommunityChannel = typeof communityChannels.$inferSelect;
export type CommunityPost = typeof communityPosts.$inferSelect;
export type CommunityPostComment = typeof communityPostComments.$inferSelect;
export type CommunityChat = typeof communityChats.$inferSelect;
export type CommunityChatMessage = typeof communityChatMessages.$inferSelect;
export type CommunityAnnouncement = typeof communityAnnouncements.$inferSelect;