import Database from 'better-sqlite3';
import { generateId } from '../utils.js';
// ─── Relationship Type Map ──────────────────────────────────────
/**
* Maps each relationship type to its inverse type.
* When a relationship is created from A → B with type X,
* an inverse relationship B → A is automatically created with type inverse(X).
*/
const INVERSE_MAP: Record<string, string> = {
// Love
significant_other: 'significant_other',
spouse: 'spouse',
date: 'date',
lover: 'lover',
in_love_with: 'in_love_with',
secret_lover: 'secret_lover',
ex_boyfriend_girlfriend: 'ex_boyfriend_girlfriend',
ex_husband_wife: 'ex_husband_wife',
// Family
parent: 'child',
child: 'parent',
sibling: 'sibling',
grandparent: 'grandchild',
grandchild: 'grandparent',
uncle_aunt: 'nephew_niece',
nephew_niece: 'uncle_aunt',
cousin: 'cousin',
godparent: 'godchild',
godchild: 'godparent',
step_parent: 'step_child',
step_child: 'step_parent',
// Friend
friend: 'friend',
best_friend: 'best_friend',
// Work
colleague: 'colleague',
boss: 'subordinate',
subordinate: 'boss',
mentor: 'protege',
protege: 'mentor',
};
/**
* Get the inverse of a relationship type.
* For custom/unknown types, returns the same type (symmetric).
*/
export function getInverseType(type: string): string {
return INVERSE_MAP[type] ?? type;
}
/**
* Get all valid relationship types.
*/
export function getRelationshipTypes(): string[] {
return Object.keys(INVERSE_MAP);
}
// ─── Types ──────────────────────────────────────────────────────
export interface Relationship {
id: string;
contact_id: string;
related_contact_id: string;
relationship_type: string;
notes: string | null;
created_at: string;
updated_at: string;
}
export interface RelationshipWithNames extends Relationship {
contact_name: string;
related_contact_name: string;
}
export interface CreateRelationshipInput {
contact_id: string;
related_contact_id: string;
relationship_type: string;
notes?: string;
}
export interface UpdateRelationshipInput {
relationship_type?: string;
notes?: string;
}
// ─── Service ────────────────────────────────────────────────────
export class RelationshipService {
constructor(private db: Database.Database) {}
/**
* Add a relationship between two contacts.
* Automatically creates the inverse relationship.
* Returns the forward relationship.
*/
add(input: CreateRelationshipInput): Relationship {
// Validate both contact IDs exist before attempting insert
const contact = this.db.prepare('SELECT id, first_name, last_name FROM contacts WHERE id = ?').get(input.contact_id) as { id: string; first_name: string; last_name: string | null } | undefined;
const related = this.db.prepare('SELECT id, first_name, last_name FROM contacts WHERE id = ?').get(input.related_contact_id) as { id: string; first_name: string; last_name: string | null } | undefined;
// Check if the invalid ID is actually the user's own ID (common mistake)
const isUserIdCheck = (id: string) => {
const user = this.db.prepare('SELECT id, name FROM users WHERE id = ?').get(id) as { id: string; name: string } | undefined;
return user ? ` This looks like your own user ID (${user.name}) — you cannot use your user ID as a contact_id. Use your self-contact ID instead (available from the \`me\` or \`prime\` tool).` : '';
};
if (!contact && !related) {
const hint1 = isUserIdCheck(input.contact_id);
const hint2 = input.contact_id !== input.related_contact_id ? isUserIdCheck(input.related_contact_id) : '';
throw new Error(`Neither contact exists — contact_id "${input.contact_id}" and related_contact_id "${input.related_contact_id}" were not found.${hint1}${hint2} Use contact_list or contact_search to find valid contact IDs.`);
}
if (!contact) {
const relatedName = [related!.first_name, related!.last_name].filter(Boolean).join(' ');
const hint = isUserIdCheck(input.contact_id);
throw new Error(`contact_id "${input.contact_id}" not found.${hint} The related_contact_id resolved to "${relatedName}" (${input.related_contact_id}). Use contact_list or contact_search to find the correct ID for the other contact.`);
}
if (!related) {
const contactName = [contact.first_name, contact.last_name].filter(Boolean).join(' ');
const hint = isUserIdCheck(input.related_contact_id);
throw new Error(`related_contact_id "${input.related_contact_id}" not found.${hint} The contact_id resolved to "${contactName}" (${input.contact_id}). Use contact_list or contact_search to find the correct ID for the related contact.`);
}
if (input.contact_id === input.related_contact_id) {
throw new Error('contact_id and related_contact_id cannot be the same — a contact cannot have a relationship with itself.');
}
const forwardId = generateId();
const inverseId = generateId();
const now = new Date().toISOString();
const inverseType = getInverseType(input.relationship_type);
const insertStmt = this.db.prepare(`
INSERT INTO relationships (id, contact_id, related_contact_id, relationship_type, notes, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?)
`);
try {
const transaction = this.db.transaction(() => {
// Forward: A → B
insertStmt.run(forwardId, input.contact_id, input.related_contact_id, input.relationship_type, input.notes ?? null, now, now);
// Inverse: B → A
insertStmt.run(inverseId, input.related_contact_id, input.contact_id, inverseType, input.notes ?? null, now, now);
});
transaction();
} catch (err: any) {
if (err.message?.includes('UNIQUE constraint failed')) {
const contactName = [contact.first_name, contact.last_name].filter(Boolean).join(' ');
const relatedName = [related.first_name, related.last_name].filter(Boolean).join(' ');
throw new Error(`A "${input.relationship_type}" relationship already exists between ${contactName} and ${relatedName}. Use relationship_update to modify it, or relationship_list to see existing relationships.`);
}
throw err;
}
return this.getById(forwardId)!;
}
/**
* Update a relationship. Also updates the inverse relationship's type if changed.
*/
update(id: string, input: UpdateRelationshipInput): Relationship | null {
const existing = this.getById(id);
if (!existing) return null;
const fields: string[] = [];
const values: any[] = [];
if (input.relationship_type !== undefined) {
fields.push('relationship_type = ?');
values.push(input.relationship_type);
}
if (input.notes !== undefined) {
fields.push('notes = ?');
values.push(input.notes);
}
if (fields.length === 0) return existing;
fields.push("updated_at = datetime('now')");
values.push(id);
const transaction = this.db.transaction(() => {
// Update the forward relationship
this.db.prepare(`UPDATE relationships SET ${fields.join(', ')} WHERE id = ?`).run(...values);
// Update the inverse relationship too
const inverseFields: string[] = [];
const inverseValues: any[] = [];
if (input.relationship_type !== undefined) {
inverseFields.push('relationship_type = ?');
inverseValues.push(getInverseType(input.relationship_type));
}
if (input.notes !== undefined) {
inverseFields.push('notes = ?');
inverseValues.push(input.notes);
}
if (inverseFields.length > 0) {
inverseFields.push("updated_at = datetime('now')");
inverseValues.push(existing.related_contact_id, existing.contact_id);
this.db.prepare(`
UPDATE relationships SET ${inverseFields.join(', ')}
WHERE contact_id = ? AND related_contact_id = ?
`).run(...inverseValues);
}
});
transaction();
return this.getById(id);
}
/**
* Remove a relationship. Also removes the inverse relationship.
*/
remove(id: string): boolean {
const existing = this.getById(id);
if (!existing) return false;
const transaction = this.db.transaction(() => {
// Remove forward
this.db.prepare('DELETE FROM relationships WHERE id = ?').run(id);
// Remove inverse
this.db.prepare(`
DELETE FROM relationships
WHERE contact_id = ? AND related_contact_id = ?
`).run(existing.related_contact_id, existing.contact_id);
});
transaction();
return true;
}
/**
* List all relationships for a contact, including names of both contacts.
*/
listByContact(contactId: string): RelationshipWithNames[] {
return this.db.prepare(`
SELECT r.*,
TRIM(c1.first_name || ' ' || COALESCE(c1.last_name, '')) AS contact_name,
TRIM(c2.first_name || ' ' || COALESCE(c2.last_name, '')) AS related_contact_name
FROM relationships r
JOIN contacts c1 ON r.contact_id = c1.id
JOIN contacts c2 ON r.related_contact_id = c2.id
WHERE r.contact_id = ?
ORDER BY r.relationship_type, r.created_at
`).all(contactId) as RelationshipWithNames[];
}
private getById(id: string): Relationship | null {
return this.db.prepare('SELECT * FROM relationships WHERE id = ?').get(id) as Relationship | undefined ?? null;
}
}
