import {
  pgTable,
  uuid,
  text,
  timestamp,
  jsonb,
  index,
  unique,
} from "drizzle-orm/pg-core";
import { companies } from "./companies.js";
import { plugins } from "./plugins.js";
import type { PluginStateScopeKind } from "@paperclipai/shared";

/**
 * `plugin_entities` table — persistent high-level mapping between Paperclip
 * objects and external plugin-defined entities.
 *
 * This table is used by plugins (e.g. `linear`, `github`) to store pointers
 * to their respective external IDs for projects, issues, etc. and to store
 * their custom data.
 *
 * Unlike `plugin_state`, which is for raw K-V persistence, `plugin_entities`
 * is intended for structured object mappings that the host can understand
 * and query for cross-plugin UI integration.
 *
 * @see PLUGIN_SPEC.md §21.3
 */
export const pluginEntities = pgTable(
  "plugin_entities",
  {
    id: uuid("id").primaryKey().defaultRandom(),
    pluginId: uuid("plugin_id")
      .notNull()
      .references(() => plugins.id, { onDelete: "cascade" }),
    /** Company scope — NULL for instance-level entities. */
    companyId: uuid("company_id").references(() => companies.id, { onDelete: "cascade" }),
    entityType: text("entity_type").notNull(),
    scopeKind: text("scope_kind").$type<PluginStateScopeKind>().notNull(),
    scopeId: text("scope_id"), // NULL for global scope (text to match plugin_state.scope_id)
    externalId: text("external_id"), // ID in the external system
    title: text("title"),
    status: text("status"),
    data: jsonb("data").$type<Record<string, unknown>>().notNull().default({}),
    createdAt: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
  },
  (table) => ({
    pluginIdx: index("plugin_entities_plugin_idx").on(table.pluginId),
    companyIdx: index("plugin_entities_company_idx").on(table.companyId),
    typeIdx: index("plugin_entities_type_idx").on(table.entityType),
    scopeIdx: index("plugin_entities_scope_idx").on(table.scopeKind, table.scopeId),
    /**
     * Per-tenant uniqueness on (companyId, pluginId, entityType, externalId).
     * `.nullsNotDistinct()` is required because companyId is nullable for
     * instance-scope entities (cron jobs, public webhooks): without it,
     * postgres treats two NULL company_ids as distinct and a tuple like
     * `(NULL, pluginId, entityType, externalId)` can be inserted multiple
     * times, losing the dedup guarantee. Same pattern as plugin_state.ts.
     * Requires PostgreSQL 15+.
     */
    externalIdx: unique("plugin_entities_external_idx")
      .on(
        table.companyId,
        table.pluginId,
        table.entityType,
        table.externalId,
      )
      .nullsNotDistinct(),
  }),
);