import {
  bigint,
  bigserial,
  index,
  pgTable,
  primaryKey,
  text,
  timestamp,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { relations, sql } from "drizzle-orm";
import { portfolio } from "./portfolio";
import { property } from "./property";

// A Tag is a user-created, coloured label owned by a Portfolio, used to form an
// arbitrary group of its Properties for bulk actions (CONTEXT.md, ADR-0013).
// Flat (no hierarchy) and many-to-many with Property via `property_tag`.
export const portfolioTag = pgTable(
  "portfolio_tag",
  {
    id: bigserial("id", { mode: "bigint" }).primaryKey(),
    portfolioId: bigint("portfolio_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolio.id, { onDelete: "cascade" }),
    name: text("name").notNull(),
    // Freeform hex string (e.g. "#7C3AED"); format is validated in the app.
    colour: text("colour").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
      .notNull()
      .defaultNow()
      .$onUpdate(() => new Date()),
  },
  (table) => [
    // Names are unique within a Portfolio, case-insensitively ("Void" vs "void").
    uniqueIndex("uq_portfolio_tag_portfolio_lower_name").on(
      table.portfolioId,
      sql`lower(${table.name})`,
    ),
  ],
);

// The many-to-many membership: one row per (Property, Tag). Both FKs cascade —
// deleting a Tag drops its memberships (ADR-0013, hard delete), and deleting a
// Property drops its tags.
export const propertyTag = pgTable(
  "property_tag",
  {
    propertyId: bigint("property_id", { mode: "bigint" })
      .notNull()
      .references(() => property.id, { onDelete: "cascade" }),
    tagId: bigint("tag_id", { mode: "bigint" })
      .notNull()
      .references(() => portfolioTag.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at", { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (table) => [
    // A Property can't hold the same Tag twice.
    primaryKey({ columns: [table.propertyId, table.tagId] }),
    // The composite PK leads with property_id; reverse lookups ("all Properties
    // for this Tag" — chips, filter, run resolution) need a tag_id-leading index.
    index("ix_property_tag_tag").on(table.tagId),
  ],
);

export const portfolioTagRelations = relations(portfolioTag, ({ one, many }) => ({
  portfolio: one(portfolio, {
    fields: [portfolioTag.portfolioId],
    references: [portfolio.id],
  }),
  memberships: many(propertyTag),
}));

export const propertyTagRelations = relations(propertyTag, ({ one }) => ({
  tag: one(portfolioTag, {
    fields: [propertyTag.tagId],
    references: [portfolioTag.id],
  }),
  property: one(property, {
    fields: [propertyTag.propertyId],
    references: [property.id],
  }),
}));
