import {
  pgTable,
  text,
  integer,
  numeric,
  timestamp,
  boolean,
  primaryKey,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { nanoid } from "nanoid";

// ============ CLIENTS ============
export const clients = pgTable("clients", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  brand_name: text("brand_name").notNull(),
  brief: text("brief").notNull(),
  // token is SEPARATE from id — rotatable secret for client link access
  token: text("token")
    .notNull()
    .unique()
    .$defaultFn(() => nanoid()),
  // slug è opzionale, univoco, URL-safe (es. mario-rossi) — se assente, il link usa il token
  slug: text("slug").unique(),
  // accepted_total rimane per compatibilità — il valore authoritative si sposta su projects
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }).default(
    "0"
  ),
  archived: boolean("archived").notNull().default(false),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ PROJECTS ============
export const projects = pgTable("projects", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  client_id: text("client_id")
    .notNull()
    .references(() => clients.id, { onDelete: "cascade" }),
  name: text("name").notNull(), // brand/project name
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }).default("0"),
  archived: boolean("archived").notNull().default(false),
  offer_id: text("offer_id")
    .references(() => offer_micros.id, { onDelete: "set null" }),
  created_from_lead_id: text("created_from_lead_id"),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// ============ PHASES ============
export const phases = pgTable("phases", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  sort_order: integer("sort_order").notNull().default(0),
  status: text("status").notNull().default("upcoming"), // upcoming | active | done
  offer_phase_id: text("offer_phase_id")
    .references(() => offer_phases.id, { onDelete: "set null" }),
});

// ============ TASKS ============
export const tasks = pgTable("tasks", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  phase_id: text("phase_id")
    .notNull()
    .references(() => phases.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  description: text("description"),
  status: text("status").notNull().default("todo"), // todo | in_progress | done
  sort_order: integer("sort_order").notNull().default(0),
});

// ============ DELIVERABLES ============
export const deliverables = pgTable("deliverables", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  task_id: text("task_id")
    .notNull()
    .references(() => tasks.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  url: text("url"), // external link only — no file hosting in v1
  status: text("status").notNull().default("pending"), // pending | submitted | approved
  // approved_at is IMMUTABLE once set — audit trail, cannot be unset by client
  approved_at: timestamp("approved_at", { withTimezone: true }),
});

// ============ COMMENTS ============
export const comments = pgTable("comments", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  entity_type: text("entity_type").notNull(), // task | deliverable
  entity_id: text("entity_id").notNull(),
  author: text("author").notNull(), // client | admin
  body: text("body").notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ PAYMENTS ============
export const payments = pgTable("payments", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  label: text("label").notNull(), // "Acconto 50%" | "Saldo 50%"
  amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
  status: text("status").notNull().default("da_saldare"), // da_saldare | inviata | saldato
  paid_at: timestamp("paid_at", { withTimezone: true }),
});

// ============ DOCUMENTS ============
export const documents = pgTable("documents", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  label: text("label").notNull(),
  url: text("url").notNull(), // external URL only — no file hosting in v1
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ NOTES (Decision Log — admin writes, client reads) ============
export const notes = pgTable("notes", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  body: text("body").notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ TIME ENTRIES (admin time tracking per project) ============
export const time_entries = pgTable("time_entries", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  started_at: timestamp("started_at", { withTimezone: true }).notNull().defaultNow(),
  ended_at: timestamp("ended_at", { withTimezone: true }),
  duration_seconds: integer("duration_seconds"), // set on stop
});

// ============ SERVICE CATALOG (admin-only, used for quote generation) ============
export const service_catalog = pgTable("service_catalog", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  description: text("description"),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
  active: boolean("active").notNull().default(true),
});

// ============ SERVICES (UNIFIED CATALOG — replaces service_catalog + offer_services) ============
// migrated_from/migrated_id provide audit trail for safe rollback during the
// expand-contract migration (Phase 7 expand; Phase 8 begins contract on offer side).
export const services = pgTable("services", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  description: text("description"),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(),
  category: text("category"),
  active: boolean("active").notNull().default(true),
  migrated_from: text("migrated_from"),   // "service_catalog" | "offer_services" | null (new rows after Phase 7)
  migrated_id: text("migrated_id"),       // original id from source table, null for new rows
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

export const servicesRelations = relations(services, (_) => ({
  // No FK relations yet in Phase 7 — quote_items and offer_micro_services
  // continue to reference service_catalog/offer_services until Phase 8.
}));

// ============ QUOTE ITEMS (admin-only — NEVER exposed via client API) ============
export const quote_items = pgTable("quote_items", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .references(() => projects.id, { onDelete: "cascade" }), // legacy: for old quote_items tied to projects (Phase 1-8)
  quote_id: text("quote_id")
    .references(() => quotes.id, { onDelete: "cascade" }), // which quote owns this item (nullable for legacy)
  offer_phase_id: text("offer_phase_id")
    .references(() => offer_phases.id, { onDelete: "restrict" }), // which phase this service is in (nullable for legacy)
  service_id: text("service_id")
    .references(() => services.id, { onDelete: "restrict" }), // nullable for custom items
  quantity: numeric("quantity", { precision: 10, scale: 2 }).notNull(),
  unit_price: numeric("unit_price", { precision: 10, scale: 2 }).notNull(), // snapshot at time of quote
  subtotal: numeric("subtotal", { precision: 10, scale: 2 }).notNull(),
  custom_label: text("custom_label"), // for custom items without catalog entry
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ SETTINGS (global admin settings — key-value store) ============
export const settings = pgTable("settings", {
  key: text("key").primaryKey(),
  value: text("value").notNull(),
  updated_at: timestamp("updated_at", { withTimezone: true }).notNull().defaultNow(),
});

// ============ OFFER MACROS ============
export const offer_macros = pgTable("offer_macros", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  sort_order: integer("sort_order").notNull().default(0),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// ============ OFFER MICROS ============
export const offer_micros = pgTable("offer_micros", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  macro_id: text("macro_id")
    .notNull()
    .references(() => offer_macros.id, { onDelete: "cascade" }),
  internal_name: text("internal_name").notNull(),
  public_name: text("public_name").notNull(),
  transformation_promise: text("transformation_promise"),
  duration_months: integer("duration_months").notNull().default(1),
  sort_order: integer("sort_order").notNull().default(0),
});

// ============ OFFER SERVICES (distinct from service_catalog — marketing/transformation semantics) ============
export const offer_services = pgTable("offer_services", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  price: numeric("price", { precision: 10, scale: 2 }).notNull(),
  transformation_description: text("transformation_description"),
  active: boolean("active").notNull().default(true),
});

// ============ OFFER MICRO SERVICES (junction: offer_micros <-> offer_services) ============
export const offer_micro_services = pgTable(
  "offer_micro_services",
  {
    micro_id: text("micro_id")
      .notNull()
      .references(() => offer_micros.id, { onDelete: "cascade" }),
    service_id: text("service_id")
      .notNull()
      .references(() => offer_services.id, { onDelete: "cascade" }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.micro_id, t.service_id] }),
  })
);

// ============ PROJECT OFFERS (assignment of micro-offer to project) ============
export const project_offers = pgTable("project_offers", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  project_id: text("project_id")
    .notNull()
    .references(() => projects.id, { onDelete: "cascade" }),
  micro_id: text("micro_id")
    .notNull()
    .references(() => offer_micros.id, { onDelete: "restrict" }),
  // NOT NULL with defaultNow — required for forecast computation (null start_date breaks forecast)
  start_date: timestamp("start_date", { withTimezone: true }).notNull().defaultNow(),
  // Offer-level accepted total — separate from projects.accepted_total (quote builder total)
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }),
  created_at: timestamp("created_at", { withTimezone: true }).notNull().defaultNow(),
});

// ============ OFFER PHASES (hierarchical breakdown of offer micros) ============
export const offer_phases = pgTable("offer_phases", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  micro_id: text("micro_id")
    .notNull()
    .references(() => offer_micros.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  description: text("description"),
  sort_order: integer("sort_order").notNull().default(0),
  duration_weeks: integer("duration_weeks"),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ OFFER PHASE SERVICES (junction: offer_phases <-> services) ============
export const offer_phase_services = pgTable(
  "offer_phase_services",
  {
    phase_id: text("phase_id")
      .notNull()
      .references(() => offer_phases.id, { onDelete: "cascade" }),
    service_id: text("service_id")
      .notNull()
      .references(() => services.id, { onDelete: "cascade" }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.phase_id, t.service_id] }),
  })
);

// ============ QUOTES (separate header from line items) ============
export const quotes = pgTable("quotes", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  lead_id: text("lead_id")
    .references(() => leads.id, { onDelete: "cascade" }),
  client_id: text("client_id")
    .references(() => clients.id, { onDelete: "cascade" }),
  token: text("token")
    .notNull()
    .unique()
    .$defaultFn(() => nanoid()),
  offer_micro_id: text("offer_micro_id")
    .notNull()
    .references(() => offer_micros.id, { onDelete: "restrict" }),
  state: text("state").notNull().default("draft"), // draft | sent | viewed | accepted | rejected
  accepted_total: numeric("accepted_total", { precision: 10, scale: 2 }).notNull(),
  accepted_at: timestamp("accepted_at", { withTimezone: true }), // immutable once set; NULL means not yet accepted
  client_email: text("client_email"), // captured on accept
  client_notes: text("client_notes"), // captured on accept
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updated_at: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ LEADS TABLE (CRM — Phase 10) ============
export const leads = pgTable("leads", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  name: text("name").notNull(),
  email: text("email"),
  phone: text("phone"),
  company: text("company"),
  status: text("status")
    .notNull()
    .default("contacted"), // contacted | qualified | proposal_sent | negotiating | won | lost
  last_contact_date: timestamp("last_contact_date", { withTimezone: true }),
  next_action: text("next_action"),
  next_action_date: timestamp("next_action_date", { withTimezone: true }),
  notes: text("notes"),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
  updated_at: timestamp("updated_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ ACTIVITIES TABLE (CRM interaction history) ============
export const activities = pgTable("activities", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  lead_id: text("lead_id")
    .notNull()
    .references(() => leads.id, { onDelete: "cascade" }),
  type: text("type")
    .notNull(), // call | email | meeting | note
  duration_minutes: integer("duration_minutes"),
  notes: text("notes").notNull(),
  activity_date: timestamp("activity_date", { withTimezone: true })
    .notNull(),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ REMINDERS TABLE (CRM follow-up reminders) ============
export const reminders = pgTable("reminders", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => nanoid()),
  lead_id: text("lead_id")
    .notNull()
    .references(() => leads.id, { onDelete: "cascade" }),
  title: text("title").notNull(),
  description: text("description"),
  due_date: timestamp("due_date", { withTimezone: true })
    .notNull(),
  completed_at: timestamp("completed_at", { withTimezone: true }),
  created_at: timestamp("created_at", { withTimezone: true })
    .notNull()
    .defaultNow(),
});

// ============ RELATIONS ============

export const clientsRelations = relations(clients, ({ many }) => ({
  projects: many(projects),
}));

export const projectsRelations = relations(projects, ({ one, many }) => ({
  client: one(clients, { fields: [projects.client_id], references: [clients.id] }),
  offer: one(offer_micros, { fields: [projects.offer_id], references: [offer_micros.id] }),
  phases: many(phases),
  payments: many(payments),
  documents: many(documents),
  notes: many(notes),
  quote_items: many(quote_items),
  time_entries: many(time_entries),
  projectOffers: many(project_offers),
}));

export const phasesRelations = relations(phases, ({ one, many }) => ({
  project: one(projects, { fields: [phases.project_id], references: [projects.id] }),
  offerPhase: one(offer_phases, { fields: [phases.offer_phase_id], references: [offer_phases.id] }),
  tasks: many(tasks),
}));

export const tasksRelations = relations(tasks, ({ one, many }) => ({
  phase: one(phases, { fields: [tasks.phase_id], references: [phases.id] }),
  deliverables: many(deliverables),
}));

export const deliverablesRelations = relations(deliverables, ({ one }) => ({
  task: one(tasks, { fields: [deliverables.task_id], references: [tasks.id] }),
}));

export const commentsRelations = relations(comments, (_) => ({
  // Polymorphic: no direct FK relation — entity_type + entity_id used at query time
}));

export const paymentsRelations = relations(payments, ({ one }) => ({
  project: one(projects, {
    fields: [payments.project_id],
    references: [projects.id],
  }),
}));

export const documentsRelations = relations(documents, ({ one }) => ({
  project: one(projects, {
    fields: [documents.project_id],
    references: [projects.id],
  }),
}));

export const notesRelations = relations(notes, ({ one }) => ({
  project: one(projects, { fields: [notes.project_id], references: [projects.id] }),
}));

export const timeEntriesRelations = relations(time_entries, ({ one }) => ({
  project: one(projects, { fields: [time_entries.project_id], references: [projects.id] }),
}));

export const quoteItemsRelations = relations(quote_items, ({ one }) => ({
  project: one(projects, {
    fields: [quote_items.project_id],
    references: [projects.id],
  }),
  quote: one(quotes, {
    fields: [quote_items.quote_id],
    references: [quotes.id],
  }),
  service: one(services, {
    fields: [quote_items.service_id],
    references: [services.id],
  }),
  offerPhase: one(offer_phases, {
    fields: [quote_items.offer_phase_id],
    references: [offer_phases.id],
  }),
}));

export const serviceCatalogRelations = relations(
  service_catalog,
  ({ many }) => ({
    quote_items: many(quote_items),
  })
);

export const offerMacrosRelations = relations(offer_macros, ({ many }) => ({
  micros: many(offer_micros),
}));

export const offerMicrosRelations = relations(offer_micros, ({ one, many }) => ({
  macro: one(offer_macros, { fields: [offer_micros.macro_id], references: [offer_macros.id] }),
  services: many(offer_micro_services),
  projectOffers: many(project_offers),
}));

export const offerServicesRelations = relations(offer_services, ({ many }) => ({
  microAssignments: many(offer_micro_services),
}));

export const offerMicroServicesRelations = relations(offer_micro_services, ({ one }) => ({
  micro: one(offer_micros, { fields: [offer_micro_services.micro_id], references: [offer_micros.id] }),
  service: one(offer_services, { fields: [offer_micro_services.service_id], references: [offer_services.id] }),
}));

export const projectOffersRelations = relations(project_offers, ({ one }) => ({
  project: one(projects, { fields: [project_offers.project_id], references: [projects.id] }),
  micro: one(offer_micros, { fields: [project_offers.micro_id], references: [offer_micros.id] }),
}));

export const offerPhasesRelations = relations(offer_phases, ({ one, many }) => ({
  micro: one(offer_micros, { fields: [offer_phases.micro_id], references: [offer_micros.id] }),
  services: many(offer_phase_services),
  quoteItems: many(quote_items),
}));

export const offerPhaseServicesRelations = relations(offer_phase_services, ({ one }) => ({
  phase: one(offer_phases, { fields: [offer_phase_services.phase_id], references: [offer_phases.id] }),
  service: one(services, { fields: [offer_phase_services.service_id], references: [services.id] }),
}));

export const leadsRelations = relations(leads, ({ many }) => ({
  quotes: many(quotes),
  activities: many(activities),
  reminders: many(reminders),
}));

export const activitiesRelations = relations(activities, ({ one }) => ({
  lead: one(leads, { fields: [activities.lead_id], references: [leads.id] }),
}));

export const remindersRelations = relations(reminders, ({ one }) => ({
  lead: one(leads, { fields: [reminders.lead_id], references: [leads.id] }),
}));

export const quotesRelations = relations(quotes, ({ one, many }) => ({
  lead: one(leads, { fields: [quotes.lead_id], references: [leads.id] }),
  client: one(clients, { fields: [quotes.client_id], references: [clients.id] }),
  offerMicro: one(offer_micros, { fields: [quotes.offer_micro_id], references: [offer_micros.id] }),
  quoteItems: many(quote_items),
}));

// ============ TYPESCRIPT TYPES (for use in API routes and Server Components) ============

export type Client = typeof clients.$inferSelect;
export type NewClient = typeof clients.$inferInsert;
export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;
export type Phase = typeof phases.$inferSelect;
export type NewPhase = typeof phases.$inferInsert;
export type Task = typeof tasks.$inferSelect;
export type NewTask = typeof tasks.$inferInsert;
export type Deliverable = typeof deliverables.$inferSelect;
export type NewDeliverable = typeof deliverables.$inferInsert;
export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;
export type Payment = typeof payments.$inferSelect;
export type NewPayment = typeof payments.$inferInsert;
export type Document = typeof documents.$inferSelect;
export type NewDocument = typeof documents.$inferInsert;
export type Note = typeof notes.$inferSelect;
export type NewNote = typeof notes.$inferInsert;
export type Service = typeof services.$inferSelect;
export type NewService = typeof services.$inferInsert;
export type ServiceCatalog = typeof service_catalog.$inferSelect;
export type NewServiceCatalog = typeof service_catalog.$inferInsert;
export type QuoteItem = typeof quote_items.$inferSelect;
export type NewQuoteItem = typeof quote_items.$inferInsert;
export type TimeEntry = typeof time_entries.$inferSelect;
export type NewTimeEntry = typeof time_entries.$inferInsert;
export type Setting = typeof settings.$inferSelect;
export type NewSetting = typeof settings.$inferInsert;
export type OfferMacro = typeof offer_macros.$inferSelect;
export type NewOfferMacro = typeof offer_macros.$inferInsert;
export type OfferMicro = typeof offer_micros.$inferSelect;
export type NewOfferMicro = typeof offer_micros.$inferInsert;
export type OfferService = typeof offer_services.$inferSelect;
export type NewOfferService = typeof offer_services.$inferInsert;
export type OfferMicroService = typeof offer_micro_services.$inferSelect;
export type NewOfferMicroService = typeof offer_micro_services.$inferInsert;
export type ProjectOffer = typeof project_offers.$inferSelect;
export type NewProjectOffer = typeof project_offers.$inferInsert;
export type OfferPhase = typeof offer_phases.$inferSelect;
export type NewOfferPhase = typeof offer_phases.$inferInsert;
export type OfferPhaseService = typeof offer_phase_services.$inferSelect;
export type NewOfferPhaseService = typeof offer_phase_services.$inferInsert;
export type Quote = typeof quotes.$inferSelect;
export type NewQuote = typeof quotes.$inferInsert;
export type Lead = typeof leads.$inferSelect;
export type NewLead = typeof leads.$inferInsert;
export type Activity = typeof activities.$inferSelect;
export type NewActivity = typeof activities.$inferInsert;
export type Reminder = typeof reminders.$inferSelect;
export type NewReminder = typeof reminders.$inferInsert;