/**
 * Project templates schema — project-template-gallery.
 *
 * - project_templates:       reusable project skeletons (system + tenant-owned)
 * - project_template_tasks:  ordered task blueprints per template
 *
 * System templates: tenant_id IS NULL — global, read-only.
 * Tenant templates: tenant_id set — scoped to that tenant.
 */
import {
  pgTable,
  uuid,
  text,
  integer,
  boolean,
  jsonb,
  numeric,
  timestamp,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── project_templates ─────────────────────────────────────────────────────────

export const projectTemplates = pgTable(
  'project_templates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    // NULL = system template (global, read-only)
    tenantId: uuid('tenant_id').references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    description: text('description'),
    // 'design' | 'development' | 'marketing' | 'consulting' | 'other'
    category: text('category'),
    estimatedDays: integer('estimated_days'),
    // 'fixed' | 'hourly' | 'retainer' — pre-fill on project creation
    billingType: text('billing_type'),
    thumbnailEmoji: text('thumbnail_emoji').notNull().default('📋'),
    isPublic: boolean('is_public').notNull().default(false),
    usageCount: integer('usage_count').notNull().default(0),
    createdBy: uuid('created_by').references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_project_templates_tenant').on(t.tenantId),
    categoryCheck: check(
      'project_templates_category_check',
      sql`${t.category} IS NULL OR ${t.category} IN ('design','development','marketing','consulting','other')`,
    ),
    billingTypeCheck: check(
      'project_templates_billing_type_check',
      sql`${t.billingType} IS NULL OR ${t.billingType} IN ('fixed','hourly','retainer')`,
    ),
  }),
)

export type ProjectTemplateRow = typeof projectTemplates.$inferSelect
export type NewProjectTemplate = typeof projectTemplates.$inferInsert

// ── project_template_tasks ────────────────────────────────────────────────────

export const projectTemplateTasks = pgTable(
  'project_template_tasks',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    templateId: uuid('template_id')
      .notNull()
      .references(() => projectTemplates.id, { onDelete: 'cascade' }),
    title: text('title').notNull(),
    description: text('description'),
    phase: text('phase'),
    position: integer('position').notNull().default(0),
    relativeStartDays: integer('relative_start_days'),
    relativeDueDays: integer('relative_due_days'),
    isMilestone: boolean('is_milestone').notNull().default(false),
    // [{ "text": "...", "required": true }]
    checklistItems: jsonb('checklist_items').notNull().default(sql`'[]'::jsonb`),
    estimatedHours: numeric('estimated_hours', { precision: 6, scale: 2 }),
    // 'MEMBER' | 'ADMIN' | 'CONTRACTOR'
    assignedRole: text('assigned_role'),
    tags: text('tags').array(),
  },
  (t) => ({
    templatePositionIdx: index('idx_template_tasks_template').on(t.templateId, t.position),
    assignedRoleCheck: check(
      'project_template_tasks_assigned_role_check',
      sql`${t.assignedRole} IS NULL OR ${t.assignedRole} IN ('MEMBER','ADMIN','CONTRACTOR')`,
    ),
  }),
)

export type ProjectTemplateTaskRow = typeof projectTemplateTasks.$inferSelect
export type NewProjectTemplateTask = typeof projectTemplateTasks.$inferInsert