CRM

/** @jsxImportSource @kovojs/server */
import { component } from '@kovojs/core';
import { mutationFormAttributes } from '@kovojs/server';
import { Button } from '@kovojs/ui/button';
import { Card } from '@kovojs/ui/card';
import { tokens } from '@kovojs/style';
import * as style from '@kovojs/style';
import {
  Table,
  TableBody,
  TableCell,
  TableHead,
  TableHeaderCell,
  TableRow,
} from '@kovojs/ui/table';

import { createDeal, type CrmRequest } from '../mutations.js';
import {
  contactListQuery,
  openDealsQuery,
  pipelineByStageQuery,
  type ContactListResult,
  type ContactRow,
  type DealRow,
  type OpenDealsResult,
  type PipelineByStageResult,
  type PipelineStageBucket,
} from '../queries.js';
import { freshId, money, stageBadge } from '../components/chrome.js';

// Pipeline dashboard for `/`. A new deal refreshes the stage totals and open
// deals table.

// A new deal starts in one of these stages; closing moves it to `won`.
const NEW_DEAL_STAGES = ['lead', 'qualified', 'open', 'proposal'] as const;

const pipelineStyles = style.create({
  backLink: {
    alignItems: 'center',
    color: tokens.sys.color.onSurfaceVariant,
    display: 'inline-flex',
    fontSize: 14,
    gap: 4,
    textDecoration: 'none',
    ':hover': {
      color: tokens.sys.color.onSurface,
    },
  },
  formGrid: {
    display: 'grid',
    gap: 8,
    '@media (min-width: 640px)': {
      alignItems: 'start',
      gridTemplateColumns: '1fr auto 1fr auto',
    },
  },
  formPanel: {
    backgroundColor: tokens.sys.color.surfaceContainerLowest,
    borderColor: tokens.sys.color.outlineVariant,
    borderRadius: tokens.sys.shape.cornerMedium,
    borderStyle: 'solid',
    borderWidth: 1,
    padding: 16,
  },
  heading: {
    color: tokens.sys.color.onSurface,
    fontSize: 24,
    fontWeight: 700,
    letterSpacing: 0,
    lineHeight: 1.25,
    margin: 0,
  },
  input: {
    backgroundColor: tokens.sys.color.surfaceContainerLowest,
    borderColor: tokens.sys.color.outline,
    borderRadius: tokens.sys.shape.cornerSmall,
    borderStyle: 'solid',
    borderWidth: 1,
    boxSizing: 'border-box',
    color: tokens.sys.color.onSurface,
    fontSize: 14,
    paddingBlock: 8,
    paddingInline: 12,
    width: '100%',
  },
  muted: {
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 14,
  },
  sectionLabel: {
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 12,
    fontWeight: 600,
    letterSpacing: '0.025em',
    marginBlockEnd: 12,
    textTransform: 'uppercase',
  },
  stackLg: {
    display: 'grid',
    gap: 32,
  },
  stackSm: {
    display: 'grid',
    gap: 4,
  },
  stageGrid: {
    display: 'grid',
    gap: 12,
    gridTemplateColumns: 'repeat(2, minmax(0, 1fr))',
    '@media (min-width: 640px)': {
      gridTemplateColumns: 'repeat(3, minmax(0, 1fr))',
    },
    '@media (min-width: 1024px)': {
      gridTemplateColumns: 'repeat(6, minmax(0, 1fr))',
    },
  },
  stageText: {
    textTransform: 'capitalize',
  },
  tabular: {
    fontVariantNumeric: 'tabular-nums',
  },
  tabularStrong: {
    fontVariantNumeric: 'tabular-nums',
    fontWeight: 600,
  },
});

export const pipelineStyleCss = style.emitAtomicCss(
  Object.values(pipelineStyles).flatMap((entry) => entry.__rules ?? []),
);

interface PipelineRenderSlots {
  request?: CrmRequest | undefined;
}

function renderStageCard(bucket: PipelineStageBucket): string {
  return Card.definition.render({
    children: (
      <div style={pipelineStyles.stackSm}>
        <div>{stageBadge(bucket.stage)}</div>
        <p style={pipelineStyles.tabularStrong}>{money(bucket.total)}</p>
      </div>
    ),
  });
}

function renderOpenDealsTable(openDeals: DealRow[], contactsById: Map<string, ContactRow>): string {
  const head = TableHead.definition.render({
    children: TableRow.definition.render({
      children:
        TableHeaderCell.definition.render({ children: 'Deal' }) +
        TableHeaderCell.definition.render({ children: 'Contact' }) +
        TableHeaderCell.definition.render({ children: 'Amount' }),
    }),
  });

  const rows = openDeals
    .map((deal) =>
      TableRow.definition.render({
        children:
          TableCell.definition.render({
            children: (
              <a style={pipelineStyles.backLink} href={`/deals/${deal.id}`}>
                {deal.id.toUpperCase()}
              </a>
            ),
          }) +
          TableCell.definition.render({
            children: contactsById.get(deal.contactId)?.name ?? deal.contactId,
          }) +
          TableCell.definition.render({
            children: <span style={pipelineStyles.tabular}>{money(deal.amount)}</span>,
          }),
      }),
    )
    .join('');

  return Table.definition.render({
    children: head + TableBody.definition.render({ children: rows }),
  });
}

// Rendered as both the full page region and the pipeline fragment payload.
export const PipelineRegion = component({
  queries: {
    contactList: contactListQuery,
    openDeals: openDealsQuery,
    pipelineByStage: pipelineByStageQuery,
  },
  render: (
    {
      contactList,
      openDeals,
      pipelineByStage,
    }: {
      contactList: ContactListResult;
      openDeals: OpenDealsResult;
      pipelineByStage: PipelineByStageResult;
    },
    _state,
    _slots: PipelineRenderSlots = {},
  ) => {
    const contacts = contactList.items;
    const buckets = pipelineByStage.buckets;
    const contactsById = new Map(contacts.map((contact) => [contact.id, contact]));
    const total = buckets.reduce((sum, bucket) => sum + bucket.total, 0);

    return (
      <div style={pipelineStyles.stackLg}>
        <div>
          <h1 style={pipelineStyles.heading}>Sales pipeline</h1>
          <p style={pipelineStyles.muted}>
            {money(total)} across {buckets.length} stages, <span>{openDeals.items.length}</span>{' '}
            deals open now.
          </p>
        </div>

        <section>
          <h2 style={pipelineStyles.sectionLabel}>By stage</h2>
          <div style={pipelineStyles.stageGrid}>
            {buckets.map((bucket) => renderStageCard(bucket))}
          </div>
        </section>

        {/* The refreshed fragment resets the form with a fresh deal id. */}
        <section>
          <h2 style={pipelineStyles.sectionLabel}>New deal</h2>
          <form {...mutationFormAttributes(createDeal)} style={pipelineStyles.formPanel}>
            <input type="hidden" name="id" value={freshId('d')} />
            <input type="hidden" name="ownerId" value="u1" />
            <div style={pipelineStyles.formGrid}>
              <select name="contactId" required style={pipelineStyles.input}>
                {contacts.map((contact) => (
                  <option value={contact.id}>{contact.name}</option>
                ))}
              </select>
              <select name="stage" style={[pipelineStyles.input, pipelineStyles.stageText]}>
                {NEW_DEAL_STAGES.map((stage) => (
                  <option value={stage}>{stage}</option>
                ))}
              </select>
              <input
                name="amount"
                type="number"
                min="0"
                required
                placeholder="Amount"
                style={pipelineStyles.input}
              />
              {Button.definition.render({
                children: 'Create deal',
                type: 'submit',
                variant: 'primary',
              })}
            </div>
          </form>
        </section>

        <section>
          <h2 style={pipelineStyles.sectionLabel}>Open deals</h2>
          {renderOpenDealsTable(openDeals.items, contactsById)}
        </section>
      </div>
    );
  },
});

/** @jsxImportSource @kovojs/server */
import { component, FormError, type ComponentRenderSlots } from '@kovojs/core';
import { mutationFormAttributes } from '@kovojs/server';
import { Avatar, AvatarFallback } from '@kovojs/ui/avatar';
import { Badge } from '@kovojs/ui/badge';
import { Button } from '@kovojs/ui/button';
import { Card } from '@kovojs/ui/card';
import { tokens } from '@kovojs/style';
import * as style from '@kovojs/style';

import { addContact, type CrmRequest } from '../mutations.js';
import { addContactForm } from '../model.js';
import { contactListQuery, type ContactListResult, type ContactRow } from '../queries.js';
import { freshId } from '../components/chrome.js';

// Contact book for `/contacts`. The add-contact form posts back to this region
// so the list refreshes with the new person.

function initials(name: string): string {
  return name
    .split(/\s+/)
    .slice(0, 2)
    .map((part) => part[0]?.toUpperCase() ?? '')
    .join('');
}

const contactStyles = style.create({
  cardBody: {
    flex: '1 1 0%',
    minWidth: 0,
  },
  cardBadge: {
    flexShrink: 0,
  },
  formGrid: {
    display: 'grid',
    gap: 8,
    '@media (min-width: 640px)': {
      alignItems: 'start',
      gridTemplateColumns: '1fr 1fr auto',
    },
  },
  formPanel: {
    backgroundColor: tokens.sys.color.surfaceContainerLowest,
    borderColor: tokens.sys.color.outlineVariant,
    borderRadius: tokens.sys.shape.cornerMedium,
    borderStyle: 'solid',
    borderWidth: 1,
    padding: 16,
  },
  heading: {
    color: tokens.sys.color.onSurface,
    fontSize: 24,
    fontWeight: 700,
    letterSpacing: 0,
    lineHeight: 1.25,
    margin: 0,
  },
  input: {
    backgroundColor: tokens.sys.color.surfaceContainerLowest,
    borderColor: tokens.sys.color.outline,
    borderRadius: tokens.sys.shape.cornerSmall,
    borderStyle: 'solid',
    borderWidth: 1,
    boxSizing: 'border-box',
    color: tokens.sys.color.onSurface,
    fontSize: 14,
    paddingBlock: 8,
    paddingInline: 12,
    width: '100%',
  },
  list: {
    display: 'grid',
    gap: 12,
    listStyle: 'none',
    margin: 0,
    padding: 0,
    '@media (min-width: 640px)': {
      gridTemplateColumns: 'repeat(2, minmax(0, 1fr))',
    },
  },
  muted: {
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 14,
  },
  row: {
    alignItems: 'center',
    display: 'flex',
    gap: 12,
  },
  stack: {
    display: 'grid',
    gap: 24,
  },
  tabularStrong: {
    fontVariantNumeric: 'tabular-nums',
    fontWeight: 600,
  },
});

export const contactStyleCss = style.emitAtomicCss(
  Object.values(contactStyles).flatMap((entry) => entry.__rules ?? []),
);

function renderContactCard(contact: ContactRow): string {
  return Card.definition.render({
    children: (
      <div style={contactStyles.row}>
        {Avatar.definition.render({
          children: AvatarFallback.definition.render({ children: initials(contact.name) }),
        })}
        <div style={contactStyles.cardBody}>
          <p style={contactStyles.tabularStrong}>{contact.name}</p>
          <p style={contactStyles.muted}>{contact.email}</p>
        </div>
        <span style={contactStyles.cardBadge}>
          {Badge.definition.render({
            variant: contact.dealCount > 0 ? 'success' : 'neutral',
            children: `${contact.dealCount} ${contact.dealCount === 1 ? 'deal' : 'deals'}`,
          })}
        </span>
      </div>
    ),
  });
}

type ContactsRenderSlots = ComponentRenderSlots<{ addContact: typeof addContactForm }> & {
  request?: CrmRequest | undefined;
};
interface DuplicateEmailFailure {
  code: 'DUPLICATE_EMAIL';
  payload: { email: string };
}

const defaultContactsRenderSlots: ContactsRenderSlots = {
  forms: { addContact: { failure: null } },
};

// Rendered as both the full page region and the add-contact fragment payload.
export const ContactsRegion = component({
  mutations: { addContact: addContactForm },
  queries: { contactList: contactListQuery },
  render: (
    { contactList }: { contactList: ContactListResult },
    _state,
    _slots: ContactsRenderSlots = defaultContactsRenderSlots,
  ) => {
    const contacts = contactList.items;

    return (
      <div style={contactStyles.stack}>
        <div>
          <h1 style={contactStyles.heading}>Contacts</h1>
          <p style={contactStyles.muted}>{contacts.length} people in the book.</p>
        </div>

        {/* The refreshed fragment resets the form with a fresh contact id. */}
        <form {...mutationFormAttributes(addContact)} style={contactStyles.formPanel}>
          <input type="hidden" name="id" value={freshId('c')} />
          <input type="hidden" name="ownerId" value="u1" />
          <div style={contactStyles.formGrid}>
            <input name="name" required placeholder="Full name" style={contactStyles.input} />
            <input
              name="email"
              required
              type="email"
              placeholder="name@example.com"
              style={contactStyles.input}
            />
            {Button.definition.render({
              children: 'Add contact',
              type: 'submit',
              variant: 'primary',
            })}
          </div>
          <FormError
            code="DUPLICATE_EMAIL"
            style={contactStyles.muted}
            message={(failure: DuplicateEmailFailure) =>
              `${failure.payload.email} is already in the contact book.`
            }
          />
        </form>

        <ul style={contactStyles.list}>
          {contacts.map((contact) => (
            <li>{renderContactCard(contact)}</li>
          ))}
        </ul>
      </div>
    );
  },
});

/** @jsxImportSource @kovojs/server */
import { component } from '@kovojs/core';
import { mutationFormAttributes } from '@kovojs/server';
import { tokens } from '@kovojs/style';
import * as style from '@kovojs/style';

import { closeDeal, moveDeal, type CrmRequest } from '../mutations.js';
import {
  activityListQuery,
  contactListQuery,
  dealListQuery,
  type ActivityListResult,
  type ContactListResult,
  type DealListResult,
} from '../queries.js';
import { money, stageBadge } from '../components/chrome.js';

// Deal detail for `/deals/:id`. Moving or closing the deal refreshes this region
// with the server-updated stage and amount.

// `won` is reached through the close action because it applies commission.
const MOVE_STAGES = ['lead', 'qualified', 'open', 'proposal', 'lost'] as const;

const dealDetailStyles = style.create({
  activityList: {
    display: 'grid',
    gap: 8,
    listStyle: 'none',
    margin: 0,
    padding: 0,
  },
  backLink: {
    alignItems: 'center',
    color: tokens.sys.color.onSurfaceVariant,
    display: 'inline-flex',
    fontSize: 14,
    gap: 4,
    textDecoration: 'none',
    ':hover': {
      color: tokens.sys.color.onSurface,
    },
  },
  card: {
    backgroundColor: tokens.sys.color.surfaceContainerLowest,
    borderColor: tokens.sys.color.outlineVariant,
    borderRadius: tokens.sys.shape.cornerMedium,
    borderStyle: 'solid',
    borderWidth: 1,
    padding: 24,
  },
  dividerTop: {
    borderColor: tokens.sys.color.outlineVariant,
    borderTopStyle: 'solid',
    borderTopWidth: 1,
    paddingTop: 16,
  },
  heading: {
    color: tokens.sys.color.onSurface,
    fontSize: 24,
    fontWeight: 700,
    letterSpacing: 0,
    lineHeight: 1.25,
    margin: 0,
  },
  muted: {
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 14,
  },
  rowBetween: {
    alignItems: 'flex-start',
    display: 'flex',
    gap: 16,
    justifyContent: 'space-between',
  },
  sectionLabel: {
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 12,
    fontWeight: 600,
    letterSpacing: '0.025em',
    marginBlockEnd: 12,
    textTransform: 'uppercase',
  },
  stack: {
    display: 'grid',
    gap: 24,
  },
  stageMeta: {
    marginTop: 4,
  },
  stageSummary: {
    textAlign: 'right',
  },
  stageWrap: {
    display: 'flex',
    flexWrap: 'wrap',
    gap: 8,
  },
  stageButton: {
    borderColor: tokens.sys.color.outline,
    borderRadius: tokens.sys.shape.cornerSmall,
    borderStyle: 'solid',
    borderWidth: 1,
    color: tokens.sys.color.onSurfaceVariant,
    fontSize: 14,
    fontWeight: 500,
    paddingBlock: 6,
    paddingInline: 12,
    textTransform: 'capitalize',
    ':hover': {
      backgroundColor: tokens.sys.color.surfaceContainer,
    },
    ':disabled': {
      cursor: 'not-allowed',
      opacity: 0.4,
    },
  },
  stageButtonActive: {
    backgroundColor: tokens.sys.color.primary,
    borderColor: tokens.sys.color.primary,
    color: tokens.sys.color.onPrimary,
    cursor: 'default',
  },
  tabularStrong: {
    fontVariantNumeric: 'tabular-nums',
    fontWeight: 600,
  },
});

export const dealDetailStyleCss = style.emitAtomicCss(
  Object.values(dealDetailStyles).flatMap((entry) => entry.__rules ?? []),
);

interface DealDetailRenderSlots {
  request?: CrmRequest | undefined;
}

// Rendered as both the detail page region and the deal-action fragment payload.
export const DealDetailRegion = component({
  props: { dealId: String },
  queries: {
    activityList: activityListQuery,
    contactList: contactListQuery,
    dealList: dealListQuery,
  },
  render: (
    {
      activityList,
      contactList,
      dealId,
      dealList,
    }: {
      activityList: ActivityListResult;
      contactList: ContactListResult;
      dealId: string;
      dealList: DealListResult;
    },
    _state,
    _slots: DealDetailRenderSlots = {},
  ) => {
    const deal = dealList.items.find((item) => item.id === dealId);
    const contact = contactList.items.find((item) => item.id === deal?.contactId);
    const activities = activityList.items.filter((item) => item.dealId === dealId);
    const closed = deal?.stage === 'won' || deal?.stage === 'lost';

    if (!deal) {
      return (
        <div style={dealDetailStyles.stack}>
          <a style={dealDetailStyles.backLink} href="/">
            &larr; Pipeline
          </a>
          <div style={dealDetailStyles.card}>
            <h1 style={dealDetailStyles.heading}>Unknown deal</h1>
            <p style={dealDetailStyles.muted}>
              Deal {dealId.toUpperCase()} does not exist in this demo database.
            </p>
          </div>
        </div>
      );
    }

    return (
      <div style={dealDetailStyles.stack}>
        <a style={dealDetailStyles.backLink} href="/">
          &larr; Pipeline
        </a>

        <div style={dealDetailStyles.card}>
          <div style={dealDetailStyles.rowBetween}>
            <div>
              <h1 style={dealDetailStyles.heading}>Deal {deal.id.toUpperCase()}</h1>
              <p style={dealDetailStyles.muted}>
                {contact ? contact.name : deal.contactId} · owner {deal.ownerId}
              </p>
            </div>
            <div style={dealDetailStyles.stageSummary}>
              <p style={dealDetailStyles.tabularStrong}>{money(deal.amount)}</p>
              <div style={dealDetailStyles.stageMeta}>{stageBadge(deal.stage)}</div>
            </div>
          </div>
          {contact ? (
            <p style={[dealDetailStyles.dividerTop, dealDetailStyles.muted]}>
              <span style={dealDetailStyles.tabularStrong}>{contact.name}</span> · {contact.email}
            </p>
          ) : (
            ''
          )}
        </div>

        {/* Each stage button posts a tiny form and refreshes this region. */}
        <div style={dealDetailStyles.card}>
          <h2 style={dealDetailStyles.sectionLabel}>Move stage</h2>
          <div style={dealDetailStyles.stageWrap}>
            {MOVE_STAGES.map((stage) => (
              <form key={`${deal.id}:${stage}`} {...mutationFormAttributes(moveDeal)}>
                <input type="hidden" name="dealId" value={deal.id} />
                <input type="hidden" name="stage" value={stage} />
                {deal.stage === stage ? (
                  <button
                    type="submit"
                    disabled
                    style={[dealDetailStyles.stageButton, dealDetailStyles.stageButtonActive]}
                  >
                    {stage}
                  </button>
                ) : (
                  <button type="submit" disabled={closed} style={dealDetailStyles.stageButton}>
                    {stage}
                  </button>
                )}
              </form>
            ))}
          </div>
          <div style={dealDetailStyles.dividerTop}>
            {closed ? (
              <p style={dealDetailStyles.muted}>
                This deal is closed ({deal.stage}). Commission is final.
              </p>
            ) : (
              <form key={`${deal.id}:close`} {...mutationFormAttributes(closeDeal)}>
                <input type="hidden" name="dealId" value={deal.id} />
                <button
                  type="submit"
                  style={[dealDetailStyles.stageButton, dealDetailStyles.stageButtonActive]}
                >
                  Close won
                </button>
              </form>
            )}
          </div>
        </div>

        <section>
          <h2 style={dealDetailStyles.sectionLabel}>Activity</h2>
          {activities.length === 0 ? (
            <p style={[dealDetailStyles.card, dealDetailStyles.muted]}>No activity logged yet.</p>
          ) : (
            <ol style={dealDetailStyles.activityList}>
              {activities.map((activity) => (
                <li style={dealDetailStyles.card}>
                  <p style={dealDetailStyles.sectionLabel}>{activity.kind}</p>
                  <p style={dealDetailStyles.muted}>{activity.note}</p>
                </li>
              ))}
            </ol>
          )}
        </section>
      </div>
    );
  },
});

import { count, eq, sql } from 'drizzle-orm';

import type { CrmDb } from './db.js';
import type { Domain } from '@kovojs/server';
import { activity, contact, deal } from './model.js';
import { activities, contacts, deals } from './schema.js';

// Small query factory for the demo. Each query names the domains it reads and
// exposes a loader that can run against either the app request context or a test db.
type CrmQueryLoadContext = CrmDb | { db?: CrmDb; request?: { db?: CrmDb } };

interface QueryDefinition<Key extends string, Value> {
  key: Key;
  load: (input: unknown, context: CrmQueryLoadContext) => Promise<Value>;
  reads: readonly Domain<string>[];
}

function query<const Key extends string, Value>(
  key: Key,
  definition: {
    load: (input: unknown, db: CrmDb) => Promise<Value>;
    reads: readonly Domain<string>[];
  },
): QueryDefinition<Key, Value> {
  return {
    key,
    reads: definition.reads,
    load(input, context) {
      return definition.load(input, crmQueryDb(context));
    },
  };
}

// Keep the Drizzle selects inline so the graph emitter can read the same source
// the app runs.

export interface ContactRow {
  id: string;
  name: string;
  email: string;
  ownerId: string;
  dealCount: number;
}

export interface DealRow {
  id: string;
  contactId: string;
  stage: string;
  amount: number;
  ownerId: string;
}

export interface ContactListResult {
  items: ContactRow[];
}

export interface DealListResult {
  items: DealRow[];
}

export interface ContactDealCountResult {
  count: number;
}

export interface OpenDealsResult {
  items: DealRow[];
}

export interface PipelineStageBucket {
  stage: string;
  total: number;
}

export interface PipelineByStageResult {
  buckets: PipelineStageBucket[];
}

export interface ActivityRow {
  id: number;
  dealId: string;
  kind: string;
  note: string;
}

export interface ActivityListResult {
  items: ActivityRow[];
}

/** AGG(contacts) — the full contact book, ordered by id (a derivable rowset). */
export const contactListQuery = query('contactList', {
  reads: [contact],
  load: async (_input: unknown, db: CrmDb): Promise<ContactListResult> => {
    const items = await db
      .select({
        id: contacts.id,
        name: contacts.name,
        email: contacts.email,
        ownerId: contacts.ownerId,
        dealCount: contacts.dealCount,
      })
      .from(contacts)
      .orderBy(contacts.id);
    return { items: items };
  },
});

/** AGG(deals) ordered by id — the full pipeline list (a derivable rowset). */
export const dealListQuery = query('dealList', {
  reads: [deal],
  load: async (_input: unknown, db: CrmDb): Promise<DealListResult> => {
    const items = await db
      .select({
        id: deals.id,
        contactId: deals.contactId,
        stage: deals.stage,
        amount: deals.amount,
        ownerId: deals.ownerId,
      })
      .from(deals)
      .orderBy(deals.id);
    return { items: items };
  },
});

/** COUNT(deals) — the scalar count of deals across the pipeline (derivable). */
export const contactDealCountQuery = query('contactDealCount', {
  reads: [deal],
  load: async (_input: unknown, db: CrmDb): Promise<ContactDealCountResult> => {
    const rows = await db.select({ value: count() }).from(deals);
    return { count: Number(rows[0]?.value ?? 0) };
  },
});

/** AGG(deals WHERE stage = 'open') — the open pipeline (a filtered rowset). */
export const openDealsQuery = query('openDeals', {
  reads: [deal],
  load: async (_input: unknown, db: CrmDb): Promise<OpenDealsResult> => {
    const items = await db
      .select({
        id: deals.id,
        contactId: deals.contactId,
        stage: deals.stage,
        amount: deals.amount,
        ownerId: deals.ownerId,
      })
      .from(deals)
      .where(eq(deals.stage, 'open'))
      .orderBy(deals.id);
    return { items: items };
  },
});

/**
 * SUM(amount) GROUP BY stage — the pipeline value per stage.
 */
export const pipelineByStageQuery = query('pipelineByStage', {
  reads: [deal],
  load: async (_input: unknown, db: CrmDb): Promise<PipelineByStageResult> => {
    const buckets = await db
      .select({ stage: deals.stage, total: sql<number>`coalesce(sum(${deals.amount}), 0)::int` })
      .from(deals)
      .groupBy(deals.stage)
      .orderBy(deals.stage);
    return { buckets: buckets };
  },
});

/** AGG(activities) ordered by id — timeline rows for deal-detail regions. */
export const activityListQuery = query('activityList', {
  reads: [activity],
  load: async (_input: unknown, db: CrmDb): Promise<ActivityListResult> => {
    const items = await db
      .select({
        id: activities.id,
        dealId: activities.dealId,
        kind: activities.kind,
        note: activities.note,
      })
      .from(activities)
      .orderBy(activities.id);
    return { items: items };
  },
});

export const crmQueries = [
  contactListQuery,
  dealListQuery,
  contactDealCountQuery,
  openDealsQuery,
  pipelineByStageQuery,
  activityListQuery,
];

function crmQueryDb(context: CrmQueryLoadContext): CrmDb {
  if ('select' in context) return context;

  const db = context.db ?? context.request?.db;
  if (!db) {
    throw new Error('CRM query loaders require a CrmDb or context.db/request.db');
  }
  return db;
}

import { guards, mutation, s, type MutationContext } from '@kovojs/server';
import { eq, sql } from 'drizzle-orm';
import type { OptimisticFor } from '@kovojs/runtime';

import type { CrmDb } from './db.js';
import {
  contact,
  deal,
  addContactForm,
  closeDealForm,
  createDealForm,
  moveDealForm,
  type AddContactInput,
  type CloseDealInput,
  type CreateDealInput,
  type MoveDealInput,
} from './model.js';
import type { CrmDerivedSubset } from './optimistic-merge.js';
import { contacts, deals } from './schema.js';

import type { ContactListResult, OpenDealsResult, PipelineByStageResult } from './queries.js';

/**
 * The per-request value handed to every CRM mutation: a Drizzle/PGlite db plus
 * the fixed demo session used by the interactive app.
 */
export interface CrmRequest {
  db: CrmDb;
  session?: {
    id?: string;
    user?: { id?: string; roles?: readonly string[] } | null;
  } | null;
}

export interface CrmCsrfRequest {
  session?: { id?: string } | null;
}

export const EXAMPLE_ONLY_CRM_CSRF_SECRET = 'crm-reference-demo-csrf-secret';

export const crmCsrf = {
  field: 'csrf',
  secret: EXAMPLE_ONLY_CRM_CSRF_SECRET,
  sessionId(request: CrmCsrfRequest) {
    return request.session?.id;
  },
};

const authed = guards.authed<CrmRequest>();

const duplicateEmailError = s.object({ email: s.string() });

const addContactDerivedOptimistic = {
  queue: 'crm',
  transforms: {
    contactList: (current, $input) => {
      const next = structuredClone(current);
      const row = {
        dealCount: 0,
        email: $input.email,
        id: $input.id,
        name: $input.name,
        ownerId: $input.ownerId,
      };
      const index = next.items.findIndex((entry) => entry.id > row.id);
      if (index < 0) next.items.push(row);
      else next.items.splice(index, 0, row);
      return next;
    },
  },
} satisfies OptimisticFor<typeof addContactForm>;

const createDealDerivedOptimistic = {
  queue: 'crm',
  transforms: {
    contactDealCount: (current, _$input) => {
      const next = structuredClone(current);
      next.count = (next.count ?? 0) + 1;
      return next;
    },
    dealList: (current, $input) => {
      const next = structuredClone(current);
      const row = {
        amount: $input.amount,
        contactId: $input.contactId,
        id: $input.id,
        ownerId: $input.ownerId,
        stage: $input.stage,
      };
      const index = next.items.findIndex((entry) => entry.id > row.id);
      if (index < 0) next.items.push(row);
      else next.items.splice(index, 0, row);
      return next;
    },
    openDeals: (current, $input) => {
      const next = structuredClone(current);
      const row = {
        amount: $input.amount,
        contactId: $input.contactId,
        id: $input.id,
        ownerId: $input.ownerId,
        stage: $input.stage,
      };
      const index = next.items.findIndex((entry) => entry.id > row.id);
      if (index < 0) next.items.push(row);
      else next.items.splice(index, 0, row);
      return next;
    },
  },
} satisfies CrmDerivedSubset<typeof createDealForm, 'contactDealCount' | 'dealList' | 'openDeals'>;

const moveDealDerivedOptimistic = {
  queue: 'crm',
  transforms: {
    contactDealCount: (current, _$input) => structuredClone(current),
    dealList: (current, $input) => {
      const next = structuredClone(current);
      const target = next.items.find((entry) => entry.id === $input.dealId);
      if (target) target.stage = $input.stage;
      return next;
    },
  },
} satisfies CrmDerivedSubset<typeof moveDealForm, 'contactDealCount' | 'dealList'>;

const closeDealDerivedOptimistic = {
  queue: 'crm',
  transforms: {
    contactDealCount: (current, _$input) => structuredClone(current),
  },
} satisfies CrmDerivedSubset<typeof closeDealForm, 'contactDealCount'>;

export async function addContactHandler(
  { id, name, email, ownerId }: AddContactInput,
  request: CrmRequest,
  context: MutationContext<{ DUPLICATE_EMAIL: typeof duplicateEmailError }>,
) {
  const db = request.db;
  const [existing] = await db.select().from(contacts).where(eq(contacts.email, email)).limit(1);
  if (existing) {
    return context.fail('DUPLICATE_EMAIL', { email });
  }

  await db.insert(contacts).values({ id, name, email, ownerId, dealCount: 0 });
  return { id };
}

export const addContact = mutation('addContact', {
  csrf: crmCsrf,
  errors: {
    DUPLICATE_EMAIL: duplicateEmailError,
  },
  guard: authed,
  input: s.object({
    id: s.string(),
    name: s.string(),
    email: s.string(),
    ownerId: s.string(),
  }),
  registry: { touches: [contact] },
  handler: addContactHandler,
});

export const addContactOptimistic = addContactDerivedOptimistic;

export async function createDealHandler(
  { id, contactId, stage, amount, ownerId }: CreateDealInput,
  request: CrmRequest,
) {
  const db = request.db;
  await db.insert(deals).values({ id, contactId, stage, amount, ownerId });
  await db
    .update(contacts)
    .set({ dealCount: sql`${contacts.dealCount} + 1` })
    .where(eq(contacts.id, contactId));
  return { id };
}

export const createDeal = mutation('createDeal', {
  csrf: crmCsrf,
  guard: authed,
  input: s.object({
    id: s.string(),
    contactId: s.string(),
    stage: s.string(),
    amount: s.number().int().min(0),
    ownerId: s.string(),
  }),
  registry: { touches: [contact, deal] },
  handler: createDealHandler,
});

// Hand-written optimistic patches for UI values the generated plan cannot know:
// contactList needs the server-side dealCount increment, and pipelineByStage is a
// grouped summary.
export const createDealOptimistic = {
  ...createDealDerivedOptimistic,
  transforms: {
    ...createDealDerivedOptimistic.transforms,
    contactList: (current: ContactListResult, $input: CreateDealInput) => {
      const next = structuredClone(current);
      const target = next.items.find((item) => item.id === $input.contactId);
      if (target) target.dealCount += 1;
      return next;
    },
    pipelineByStage: (current: PipelineByStageResult, $input: CreateDealInput) => {
      const next = structuredClone(current);
      const bucket = next.buckets.find((entry) => entry.stage === $input.stage);
      if (bucket) bucket.total += $input.amount;
      else next.buckets.push({ stage: $input.stage, total: $input.amount });
      next.buckets.sort((left, right) => left.stage.localeCompare(right.stage));
      return next;
    },
  },
} satisfies OptimisticFor<typeof createDealForm>;

export async function moveDealHandler({ dealId, stage }: MoveDealInput, request: CrmRequest) {
  const db = request.db;
  await db.update(deals).set({ stage }).where(eq(deals.id, dealId));
  return { dealId };
}

export const moveDeal = mutation('moveDeal', {
  csrf: crmCsrf,
  guard: authed,
  input: s.object({
    dealId: s.string(),
    stage: s.string(),
  }),
  registry: { touches: [deal] },
  handler: moveDealHandler,
});

// Moving a deal can change filtered and grouped views in ways that need row
// context, so the demo waits for the server fragment for those regions.
export const moveDealOptimistic = {
  ...moveDealDerivedOptimistic,
  transforms: {
    ...moveDealDerivedOptimistic.transforms,
    openDeals: 'await-fragment',
    pipelineByStage: 'await-fragment',
  },
} satisfies OptimisticFor<typeof moveDealForm>;

/**
 * Row-carrying helper for updating pipelineByStage when the old stage and amount
 * are already known.
 */
export function applyMoveDealPipeline(
  current: { buckets: { stage: string; total: number }[] },
  deal: { amount: number; fromStage: string; toStage: string },
): { buckets: { stage: string; total: number }[] } {
  const next = structuredClone(current);
  const from = next.buckets.find((entry) => entry.stage === deal.fromStage);
  if (from) from.total -= deal.amount;
  const to = next.buckets.find((entry) => entry.stage === deal.toStage);
  if (to) to.total += deal.amount;
  else next.buckets.push({ stage: deal.toStage, total: deal.amount });
  // Empty buckets disappear and the surviving buckets stay stage-sorted.
  return {
    buckets: next.buckets
      .filter((entry) => entry.total !== 0)
      .sort((left, right) => left.stage.localeCompare(right.stage)),
  };
}

export async function closeDealHandler({ dealId }: CloseDealInput, request: CrmRequest) {
  const db = request.db;
  await db
    .update(deals)
    .set({ stage: 'won', amount: sql`compute_commission(${deals.amount})` })
    .where(eq(deals.id, dealId));
  return { dealId };
}

export const closeDeal = mutation('closeDeal', {
  csrf: crmCsrf,
  guard: authed,
  input: s.object({
    dealId: s.string(),
  }),
  registry: { touches: [deal] },
  handler: closeDealHandler,
});

// A closed deal leaves the open list immediately. Views that include the
// server-computed commission wait for the returned fragment.
export const closeDealOptimistic = {
  ...closeDealDerivedOptimistic,
  transforms: {
    ...closeDealDerivedOptimistic.transforms,
    openDeals: (current: OpenDealsResult, $input: CloseDealInput) => {
      const next = structuredClone(current);
      const index = next.items.findIndex((item) => item.id === $input.dealId);
      if (index >= 0) next.items.splice(index, 1);
      return next;
    },
    dealList: 'await-fragment',
    pipelineByStage: 'await-fragment',
  },
} satisfies OptimisticFor<typeof closeDealForm>;

export const crmMutations = [addContact, createDeal, moveDeal, closeDeal];

// DO NOT EDIT — generated by @kovojs/drizzle derived optimism (SPEC.md §10.5).
// Override a transform by declaring it in the mutation module; deleting the
// override lets derivation take this pair back over, pair by pair (SPEC.md §10.4).
import type { CrmDerivedSubset } from '../../optimistic-merge.js';

import type { createDealForm } from '../../model.js';
// Overridden in the mutation module (derivation suppressed): contactList, pipelineByStage.

export const createDealDerivedOptimistic = {
  queue: 'crm',
  transforms: {
    contactDealCount: (current, _$input) => {
      const next = structuredClone(current);
      next.count = (next.count ?? 0) + 1;
      return next;
    },
    dealList: (current, $input) => {
      const next = structuredClone(current);
      {
        const row = { id: $input.id, contactId: $input.contactId, stage: $input.stage, amount: $input.amount, ownerId: $input.ownerId };
        const index = next.items.findIndex((entry) => entry.id > row.id);
        if (index < 0) next.items.push(row);
        else next.items.splice(index, 0, row);
      }
      return next;
    },
    openDeals: (current, $input) => {
      const next = structuredClone(current);
      {
        const row = { id: $input.id, contactId: $input.contactId, stage: $input.stage, amount: $input.amount, ownerId: $input.ownerId };
        const index = next.items.findIndex((entry) => entry.id > row.id);
        if (index < 0) next.items.push(row);
        else next.items.splice(index, 0, row);
      }
      return next;
    },
  },
} satisfies CrmDerivedSubset<typeof createDealForm, "contactDealCount" | "dealList" | "openDeals">;