/**
* Entity Tools - Query specific entities (Company, User, Plan, etc.)
*/
import { z } from 'zod'
import { query } from '../db.js'
// ============================================
// SCHEMAS
// ============================================
export const getCompaniesSchema = z.object({
subscriptionStatus: z.enum(['active', 'inactive', 'past_due']).optional(),
limit: z.number().optional().describe('Limit results (default: 50)'),
})
export const getUsersSchema = z.object({
companyId: z.string().optional(),
role: z.number().optional().describe('RoleEnum value (3=ASSISTANT, 7=AGENT, 15=ADMIN)'),
limit: z.number().optional().describe('Limit results (default: 50)'),
})
export const getPlansSchema = z.object({
isActive: z.boolean().optional(),
})
export const getSubscriptionsSchema = z.object({
companyId: z.string().optional(),
status: z.enum(['active', 'past_due', 'canceled', 'incomplete']).optional(),
limit: z.number().optional().describe('Limit results (default: 50)'),
})
// ============================================
// TOOLS
// ============================================
export async function getCompanies(
params: z.infer<typeof getCompaniesSchema>
): Promise<string> {
const { subscriptionStatus, limit = 50 } = params
try {
let sql = `
SELECT
id,
name,
"subscriptionStatus",
"subscriptionExpiresAt",
"planId",
"createdAt"
FROM companies
`
const conditions: string[] = []
const values: any[] = []
if (subscriptionStatus) {
conditions.push(`"subscriptionStatus" = $${values.length + 1}`)
values.push(subscriptionStatus)
}
if (conditions.length > 0) {
sql += ` WHERE ${conditions.join(' AND ')}`
}
sql += ` ORDER BY "createdAt" DESC LIMIT ${limit}`
const result = await query(sql, values)
return JSON.stringify(
{
companies: result.rows,
count: result.rowCount,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
export async function getUsers(
params: z.infer<typeof getUsersSchema>
): Promise<string> {
const { companyId, role, limit = 50 } = params
try {
let sql = `
SELECT
id,
email,
username,
role,
"companyId",
"createdAt"
FROM users
`
const conditions: string[] = []
const values: any[] = []
if (companyId) {
conditions.push(`"companyId" = $${values.length + 1}`)
values.push(companyId)
}
if (role !== undefined) {
conditions.push(`role = $${values.length + 1}`)
values.push(role)
}
if (conditions.length > 0) {
sql += ` WHERE ${conditions.join(' AND ')}`
}
sql += ` ORDER BY "createdAt" DESC LIMIT ${limit}`
const result = await query(sql, values)
return JSON.stringify(
{
users: result.rows,
count: result.rowCount,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
export async function getPlans(
params: z.infer<typeof getPlansSchema>
): Promise<string> {
const { isActive } = params
try {
let sql = `
SELECT
id,
name,
price,
"isActive",
max_active_raffles,
max_tickets_per_raffle,
user_limit
FROM plans
`
if (isActive !== undefined) {
sql += ` WHERE "isActive" = $1`
}
sql += ` ORDER BY price ASC`
const result = await query(sql, isActive !== undefined ? [isActive] : [])
return JSON.stringify(
{
plans: result.rows,
count: result.rowCount,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
export async function getSubscriptions(
params: z.infer<typeof getSubscriptionsSchema>
): Promise<string> {
const { companyId, status, limit = 50 } = params
try {
// Check if subscriptions table exists
const tableCheck = await query(
`SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'subscriptions'
)`
)
if (!tableCheck.rows[0].exists) {
return JSON.stringify(
{
error: 'Subscriptions table does not exist yet',
note: 'This table will be created in Fase 1 of SaaS Core implementation',
},
null,
2
)
}
let sql = `
SELECT
id,
"companyId",
"planId",
status,
gateway,
"currentPeriodStart",
"currentPeriodEnd"
FROM subscriptions
`
const conditions: string[] = []
const values: any[] = []
if (companyId) {
conditions.push(`"companyId" = $${values.length + 1}`)
values.push(companyId)
}
if (status) {
conditions.push(`status = $${values.length + 1}`)
values.push(status)
}
if (conditions.length > 0) {
sql += ` WHERE ${conditions.join(' AND ')}`
}
sql += ` ORDER BY "currentPeriodStart" DESC LIMIT ${limit}`
const result = await query(sql, values)
return JSON.stringify(
{
subscriptions: result.rows,
count: result.rowCount,
},
null,
2
)
} catch (error) {
const message = error instanceof Error ? error.message : 'Unknown error'
return JSON.stringify({ error: message }, null, 2)
}
}
