/opt/canhelp/packages/db/src
Edit: /opt/canhelp/packages/db/src/schema.js (55343B)
"use strict";
Object.defineProperty(exports, "__esModule", { value: true });
exports.dailySpecialistStats = exports.missedOrders = exports.taskStats = exports.taskViews = exports.userStatusesRelations = exports.userStatuses = exports.googleCalendarTokensRelations = exports.googleCalendarTokens = exports.dailyUsage = exports.specialistFavoritesRelations = exports.specialistFavorites = exports.balanceTransactionsRelations = exports.favoritesRelations = exports.notificationsRelations = exports.reviewsRelations = exports.messagesRelations = exports.chatRoomsRelations = exports.offersRelations = exports.tasksRelations = exports.usersRelations = exports.referralRewards = exports.aiLogs = exports.balanceTransactions = exports.payments = exports.skillSuggestions = exports.emailTemplates = exports.settings = exports.portfolioItems = exports.categorySuggestions = exports.specialistCards = exports.activityLogs = exports.specialistAvailability = exports.plans = exports.reports = exports.favorites = exports.pushTokens = exports.notifications = exports.reviews = exports.messages = exports.chatRooms = exports.offers = exports.tasks = exports.locationsRelations = exports.locations = exports.categoriesRelations = exports.categories = exports.verifications = exports.accounts = exports.sessions = exports.users = void 0;
exports.planHistory = exports.priceListItems = exports.priceListGroups = exports.siteContactLogs = exports.supportTickets = exports.sitePageViews = exports.autoResponseSettings = void 0;
const pg_core_1 = require("drizzle-orm/pg-core");
const drizzle_orm_1 = require("drizzle-orm");
// ─── Auth tables (Better Auth compatible) ────────────────────────────────────
exports.users = (0, pg_core_1.pgTable)('users', {
id: (0, pg_core_1.text)('id').primaryKey(),
name: (0, pg_core_1.text)('name').notNull(),
email: (0, pg_core_1.text)('email').notNull().unique(),
emailVerified: (0, pg_core_1.boolean)('email_verified').notNull().default(false),
image: (0, pg_core_1.text)('image'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
// Marketplace-specific fields
firstName: (0, pg_core_1.text)('first_name').notNull().default(''),
lastName: (0, pg_core_1.text)('last_name').notNull().default(''),
role: (0, pg_core_1.text)('role', { enum: ['user', 'admin', 'customer', 'specialist'] })
.notNull()
.default('user'),
phone: (0, pg_core_1.text)('phone'),
bio: (0, pg_core_1.text)('bio'),
bioEl: (0, pg_core_1.text)('bio_el'),
bioEn: (0, pg_core_1.text)('bio_en'),
bioRu: (0, pg_core_1.text)('bio_ru'),
bioUk: (0, pg_core_1.text)('bio_uk'),
skills: (0, pg_core_1.text)('skills').array(),
specialistCategories: (0, pg_core_1.text)('specialist_categories').array(),
specialistLocations: (0, pg_core_1.text)('specialist_locations').array(),
planId: (0, pg_core_1.text)('plan_id'),
locale: (0, pg_core_1.varchar)('locale', { length: 5 }).notNull().default('el'),
languages: (0, pg_core_1.text)('languages').array().default([]),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(true),
lastSeenAt: (0, pg_core_1.timestamp)('last_seen_at'),
notifyNewTasks: (0, pg_core_1.boolean)('notify_new_tasks').notNull().default(false),
notifMessages: (0, pg_core_1.boolean)('notif_messages').notNull().default(true),
balance: (0, pg_core_1.numeric)('balance', { precision: 12, scale: 2 }).notNull().default('0'),
planExpiresAt: (0, pg_core_1.timestamp)('plan_expires_at'),
pendingPlanId: (0, pg_core_1.text)('pending_plan_id'),
renewalReminderSentAt: (0, pg_core_1.timestamp)('renewal_reminder_sent_at'),
referralCode: (0, pg_core_1.varchar)('referral_code', { length: 16 }).unique(),
referredBy: (0, pg_core_1.text)('referred_by'),
showContactInfo: (0, pg_core_1.boolean)('show_contact_info').notNull().default(false),
phoneVerified: (0, pg_core_1.boolean)('phone_verified').notNull().default(false),
personalSiteSlug: (0, pg_core_1.varchar)('personal_site_slug', { length: 50 }).unique(),
siteSettings: (0, pg_core_1.jsonb)('site_settings').$type(),
});
exports.sessions = (0, pg_core_1.pgTable)('sessions', {
id: (0, pg_core_1.text)('id').primaryKey(),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
token: (0, pg_core_1.text)('token').notNull().unique(),
expiresAt: (0, pg_core_1.timestamp)('expires_at').notNull(),
ipAddress: (0, pg_core_1.text)('ip_address'),
userAgent: (0, pg_core_1.text)('user_agent'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.accounts = (0, pg_core_1.pgTable)('accounts', {
id: (0, pg_core_1.text)('id').primaryKey(),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
accountId: (0, pg_core_1.text)('account_id').notNull(),
providerId: (0, pg_core_1.text)('provider_id').notNull(),
accessToken: (0, pg_core_1.text)('access_token'),
refreshToken: (0, pg_core_1.text)('refresh_token'),
accessTokenExpiresAt: (0, pg_core_1.timestamp)('access_token_expires_at'),
refreshTokenExpiresAt: (0, pg_core_1.timestamp)('refresh_token_expires_at'),
scope: (0, pg_core_1.text)('scope'),
idToken: (0, pg_core_1.text)('id_token'),
password: (0, pg_core_1.text)('password'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
}, (t) => ({
userProviderIdx: (0, pg_core_1.index)('accounts_user_provider_idx').on(t.userId, t.providerId),
userIdx: (0, pg_core_1.index)('accounts_user_idx').on(t.userId),
}));
exports.verifications = (0, pg_core_1.pgTable)('verifications', {
id: (0, pg_core_1.text)('id').primaryKey(),
identifier: (0, pg_core_1.text)('identifier').notNull(),
value: (0, pg_core_1.text)('value').notNull(),
expiresAt: (0, pg_core_1.timestamp)('expires_at').notNull(),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Categories ────────────────────────────────────────────────────────────
exports.categories = (0, pg_core_1.pgTable)('categories', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
slug: (0, pg_core_1.varchar)('slug', { length: 100 }).notNull().unique(),
icon: (0, pg_core_1.text)('icon').notNull().default('📦'),
namesEl: (0, pg_core_1.text)('names_el').notNull(),
namesEn: (0, pg_core_1.text)('names_en').notNull(),
namesRu: (0, pg_core_1.text)('names_ru').notNull(),
namesUk: (0, pg_core_1.text)('names_uk'),
parentId: (0, pg_core_1.text)('parent_id').references(() => exports.categories.id, { onDelete: 'cascade' }),
order: (0, pg_core_1.integer)('order').notNull().default(0),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(true),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.categoriesRelations = (0, drizzle_orm_1.relations)(exports.categories, ({ one, many }) => ({
parent: one(exports.categories, { fields: [exports.categories.parentId], references: [exports.categories.id], relationName: 'children' }),
children: many(exports.categories, { relationName: 'children' }),
}));
// ─── Locations ─────────────────────────────────────────────────────────────
exports.locations = (0, pg_core_1.pgTable)('locations', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
slug: (0, pg_core_1.varchar)('slug', { length: 100 }).notNull().unique(),
nameEl: (0, pg_core_1.text)('name_el').notNull(),
nameEn: (0, pg_core_1.text)('name_en').notNull(),
nameRu: (0, pg_core_1.text)('name_ru').notNull(),
nameUk: (0, pg_core_1.text)('name_uk'),
parentId: (0, pg_core_1.text)('parent_id').references(() => exports.locations.id, { onDelete: 'cascade' }),
order: (0, pg_core_1.integer)('order').notNull().default(0),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(true),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.locationsRelations = (0, drizzle_orm_1.relations)(exports.locations, ({ one, many }) => ({
parent: one(exports.locations, { fields: [exports.locations.parentId], references: [exports.locations.id], relationName: 'districts' }),
children: many(exports.locations, { relationName: 'districts' }),
}));
// ─── Marketplace tables ────────────────────────────────────────────────────
exports.tasks = (0, pg_core_1.pgTable)('tasks', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
title: (0, pg_core_1.text)('title').notNull(),
description: (0, pg_core_1.text)('description').notNull(),
originalLocale: (0, pg_core_1.varchar)('original_locale', { length: 5 }).notNull().default('el'),
titleEl: (0, pg_core_1.text)('title_el'),
titleEn: (0, pg_core_1.text)('title_en'),
titleRu: (0, pg_core_1.text)('title_ru'),
titleUk: (0, pg_core_1.text)('title_uk'),
descriptionEl: (0, pg_core_1.text)('description_el'),
descriptionEn: (0, pg_core_1.text)('description_en'),
descriptionRu: (0, pg_core_1.text)('description_ru'),
descriptionUk: (0, pg_core_1.text)('description_uk'),
status: (0, pg_core_1.text)('status', {
enum: ['draft', 'open', 'in_progress', 'completed', 'cancelled'],
})
.notNull()
.default('open'),
budget: (0, pg_core_1.numeric)('budget', { precision: 10, scale: 2 }),
budgetNegotiable: (0, pg_core_1.boolean)('budget_negotiable').notNull().default(false),
currency: (0, pg_core_1.varchar)('currency', { length: 3 }).notNull().default('EUR'),
category: (0, pg_core_1.text)('category'),
location: (0, pg_core_1.text)('location'),
district: (0, pg_core_1.text)('district'),
street: (0, pg_core_1.text)('street'),
houseNumber: (0, pg_core_1.text)('house_number'),
timeSlot: (0, pg_core_1.text)('time_slot'),
confidentialNote: (0, pg_core_1.text)('confidential_note'),
images: (0, pg_core_1.text)('images').array(),
deadline: (0, pg_core_1.timestamp)('deadline'),
expiresAt: (0, pg_core_1.timestamp)('expires_at'),
customerId: (0, pg_core_1.text)('customer_id')
.notNull()
.references(() => exports.users.id),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.offers = (0, pg_core_1.pgTable)('offers', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id),
price: (0, pg_core_1.numeric)('price', { precision: 10, scale: 2 }).notNull(),
currency: (0, pg_core_1.varchar)('currency', { length: 3 }).notNull().default('EUR'),
message: (0, pg_core_1.text)('message'),
counterPrice: (0, pg_core_1.numeric)('counter_price', { precision: 10, scale: 2 }),
status: (0, pg_core_1.text)('status', {
enum: ['pending', 'accepted', 'declined', 'withdrawn', 'other_accepted'],
})
.notNull()
.default('pending'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.chatRooms = (0, pg_core_1.pgTable)('chat_rooms', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: (0, pg_core_1.text)('task_id')
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
customerId: (0, pg_core_1.text)('customer_id')
.notNull()
.references(() => exports.users.id),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
exports.messages = (0, pg_core_1.pgTable)('messages', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
roomId: (0, pg_core_1.text)('room_id')
.notNull()
.references(() => exports.chatRooms.id, { onDelete: 'cascade' }),
senderId: (0, pg_core_1.text)('sender_id')
.notNull()
.references(() => exports.users.id),
body: (0, pg_core_1.text)('body').notNull(),
imageUrl: (0, pg_core_1.text)('image_url'),
isRead: (0, pg_core_1.boolean)('is_read').notNull().default(false),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
translations: (0, pg_core_1.jsonb)('translations').$type(),
});
exports.reviews = (0, pg_core_1.pgTable)('reviews', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id),
authorId: (0, pg_core_1.text)('author_id')
.notNull()
.references(() => exports.users.id),
targetId: (0, pg_core_1.text)('target_id')
.notNull()
.references(() => exports.users.id),
rating: (0, pg_core_1.smallint)('rating').notNull(),
comment: (0, pg_core_1.text)('comment'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
exports.notifications = (0, pg_core_1.pgTable)('notifications', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
recipientId: (0, pg_core_1.text)('recipient_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
type: (0, pg_core_1.text)('type', {
enum: [
'new_offer',
'offer_accepted',
'offer_declined',
'offer_other_accepted',
'task_completed',
'task_cancelled',
'task_updated',
'new_message',
'new_review',
'task_invite',
'task_deadline_reminder',
'task_archived',
'referral_reward',
'support_ticket_reply',
'specialist_card_submitted',
'specialist_card_approved',
'specialist_card_rejected',
],
}).notNull(),
title: (0, pg_core_1.text)('title').notNull(),
body: (0, pg_core_1.text)('body'),
referenceId: (0, pg_core_1.text)('reference_id'),
isRead: (0, pg_core_1.boolean)('is_read').notNull().default(false),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
exports.pushTokens = (0, pg_core_1.pgTable)('push_tokens', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
token: (0, pg_core_1.text)('token').notNull(),
platform: (0, pg_core_1.text)('platform', { enum: ['android', 'ios', 'web', 'unknown'] })
.notNull()
.default('unknown'),
locale: (0, pg_core_1.varchar)('locale', { length: 5 }),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
}, (t) => ({
tokenUnique: (0, pg_core_1.uniqueIndex)('push_tokens_token_uq').on(t.token),
}));
exports.favorites = (0, pg_core_1.pgTable)('favorites', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('favorites_user_task_idx').on(t.userId, t.taskId)]);
exports.reports = (0, pg_core_1.pgTable)('reports', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
reporterId: (0, pg_core_1.text)('reporter_id')
.notNull()
.references(() => exports.users.id),
targetType: (0, pg_core_1.text)('target_type', { enum: ['task', 'user'] }).notNull(),
targetId: (0, pg_core_1.text)('target_id').notNull(),
reason: (0, pg_core_1.text)('reason', {
enum: ['spam', 'inappropriate', 'fraud', 'duplicate', 'other'],
}).notNull(),
description: (0, pg_core_1.text)('description'),
status: (0, pg_core_1.text)('status', { enum: ['pending', 'reviewed', 'dismissed'] })
.notNull()
.default('pending'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Plans (tariff packages) ───────────────────────────────────────────────
exports.plans = (0, pg_core_1.pgTable)('plans', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
name: (0, pg_core_1.text)('name').notNull(),
description: (0, pg_core_1.text)('description'),
role: (0, pg_core_1.text)('role', { enum: ['requester', 'helper', 'customer', 'specialist'] }).notNull().default('customer'),
tier: (0, pg_core_1.text)('tier', { enum: ['free', 'pro', 'ultimate'] }).notNull().default('free'),
price: (0, pg_core_1.numeric)('price', { precision: 10, scale: 2 }).notNull().default('0'),
oldPrice: (0, pg_core_1.numeric)('old_price', { precision: 10, scale: 2 }),
currency: (0, pg_core_1.varchar)('currency', { length: 3 }).notNull().default('EUR'),
maxTasks: (0, pg_core_1.integer)('max_tasks'),
maxOffers: (0, pg_core_1.integer)('max_offers'),
maxCards: (0, pg_core_1.integer)('max_cards'),
maxMessagesPerDay: (0, pg_core_1.integer)('max_messages_per_day'),
maxOrdersPerDay: (0, pg_core_1.integer)('max_orders_per_day'),
maxSkills: (0, pg_core_1.integer)('max_skills'),
maxProfiles: (0, pg_core_1.integer)('max_profiles'),
maxPortfolioItems: (0, pg_core_1.integer)('max_portfolio_items'),
notifyNewTasks: (0, pg_core_1.boolean)('notify_new_tasks').notNull().default(false),
canContactFreePlan: (0, pg_core_1.boolean)('can_contact_free_plan').notNull().default(false),
canContactProPlan: (0, pg_core_1.boolean)('can_contact_pro_plan').notNull().default(true),
canContactAll: (0, pg_core_1.boolean)('can_contact_all').notNull().default(false),
canShowContactInfo: (0, pg_core_1.boolean)('can_show_contact_info').notNull().default(false),
canViewPhone: (0, pg_core_1.boolean)('can_view_phone').notNull().default(false),
canUploadVideo: (0, pg_core_1.boolean)('can_upload_video').notNull().default(false),
hasFavorites: (0, pg_core_1.boolean)('has_favorites').notNull().default(false),
hasGoogleCalendar: (0, pg_core_1.boolean)('has_google_calendar').notNull().default(false),
hasVerifiedBadge: (0, pg_core_1.boolean)('has_verified_badge').notNull().default(false),
highlightedReviews: (0, pg_core_1.boolean)('highlighted_reviews').notNull().default(false),
searchBoost: (0, pg_core_1.integer)('search_boost').notNull().default(0),
hasPersonalSite: (0, pg_core_1.boolean)('has_personal_site').notNull().default(false),
hasCanHelpNowStatus: (0, pg_core_1.boolean)('has_canhelp_now_status').notNull().default(false),
hasNeedHelpStatus: (0, pg_core_1.boolean)('has_need_help_status').notNull().default(false),
hasAutoResponse: (0, pg_core_1.boolean)('has_auto_response').notNull().default(false),
hasAutoMatch: (0, pg_core_1.boolean)('has_auto_match').notNull().default(false),
hasPriceList: (0, pg_core_1.boolean)('has_price_list').notNull().default(false),
offersMultiplier: (0, pg_core_1.numeric)('offers_multiplier', { precision: 4, scale: 2 }).notNull().default('1.00'),
receiveInstantDispatchFree: (0, pg_core_1.boolean)('receive_instant_dispatch_free').notNull().default(false),
receiveInstantDispatchPro: (0, pg_core_1.boolean)('receive_instant_dispatch_pro').notNull().default(false),
features: (0, pg_core_1.text)('features').array(),
durationDays: (0, pg_core_1.integer)('duration_days'),
isDefault: (0, pg_core_1.boolean)('is_default').notNull().default(false),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(true),
order: (0, pg_core_1.integer)('order').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Specialist Availability ────────────────────────────────────────────────
exports.specialistAvailability = (0, pg_core_1.pgTable)('specialist_availability', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
// ISO date string 'YYYY-MM-DD'
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(),
// 'available' = free, 'busy' = occupied
status: (0, pg_core_1.text)('status', { enum: ['available', 'busy'] }).notNull(),
note: (0, pg_core_1.text)('note'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Activity Logs ─────────────────────────────────────────────────────────
exports.activityLogs = (0, pg_core_1.pgTable)('activity_logs', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id').references(() => exports.users.id, { onDelete: 'set null' }),
userEmail: (0, pg_core_1.text)('user_email'),
userName: (0, pg_core_1.text)('user_name'),
// e.g. 'user.login', 'user.register', 'admin.user.deactivate', 'task.created'
event: (0, pg_core_1.varchar)('event', { length: 100 }).notNull(),
details: (0, pg_core_1.text)('details'), // JSON string
ipAddress: (0, pg_core_1.text)('ip_address'),
userAgent: (0, pg_core_1.text)('user_agent'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Specialist Cards ──────────────────────────────────────────────────────
exports.specialistCards = (0, pg_core_1.pgTable)('specialist_cards', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
title: (0, pg_core_1.varchar)('title', { length: 200 }).notNull(),
description: (0, pg_core_1.text)('description'),
descriptionEl: (0, pg_core_1.text)('description_el'),
descriptionEn: (0, pg_core_1.text)('description_en'),
descriptionRu: (0, pg_core_1.text)('description_ru'),
descriptionUk: (0, pg_core_1.text)('description_uk'),
originalLocale: (0, pg_core_1.varchar)('original_locale', { length: 5 }).notNull().default('el'),
skills: (0, pg_core_1.text)('skills').array(),
categories: (0, pg_core_1.text)('categories').array(),
locations: (0, pg_core_1.text)('locations').array(),
publicationStatus: (0, pg_core_1.text)('publication_status', { enum: ['pending', 'active', 'inactive'] })
.notNull()
.default('pending'),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(true),
order: (0, pg_core_1.integer)('order').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.categorySuggestions = (0, pg_core_1.pgTable)('category_suggestions', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
cardId: (0, pg_core_1.text)('card_id')
.notNull()
.references(() => exports.specialistCards.id, { onDelete: 'cascade' }),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
name: (0, pg_core_1.varchar)('name', { length: 100 }).notNull(),
locale: (0, pg_core_1.varchar)('locale', { length: 5 }).notNull().default('el'),
status: (0, pg_core_1.text)('status', { enum: ['pending', 'mapped', 'created', 'rejected'] })
.notNull()
.default('pending'),
mappedCategoryId: (0, pg_core_1.text)('mapped_category_id').references(() => exports.categories.id, { onDelete: 'set null' }),
reviewedByAdminId: (0, pg_core_1.text)('reviewed_by_admin_id').references(() => exports.users.id, { onDelete: 'set null' }),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
}, (t) => ({
cardIdx: (0, pg_core_1.index)('category_suggestions_card_idx').on(t.cardId),
statusIdx: (0, pg_core_1.index)('category_suggestions_status_idx').on(t.status),
}));
// ─── Portfolio Items ───────────────────────────────────────────────────────
exports.portfolioItems = (0, pg_core_1.pgTable)('portfolio_items', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
cardId: (0, pg_core_1.text)('card_id')
.references(() => exports.specialistCards.id, { onDelete: 'cascade' }),
imageUrl: (0, pg_core_1.text)('image_url').notNull(),
title: (0, pg_core_1.varchar)('title', { length: 200 }),
description: (0, pg_core_1.text)('description'),
order: (0, pg_core_1.integer)('order').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Settings (admin key-value store) ─────────────────────────────────────
exports.settings = (0, pg_core_1.pgTable)('settings', {
key: (0, pg_core_1.varchar)('key', { length: 100 }).primaryKey(),
value: (0, pg_core_1.text)('value').notNull(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Email Templates ──────────────────────────────────────────────────────
exports.emailTemplates = (0, pg_core_1.pgTable)('email_templates', {
key: (0, pg_core_1.varchar)('key', { length: 100 }).primaryKey(),
subject: (0, pg_core_1.text)('subject').notNull().default(''),
body: (0, pg_core_1.text)('body').notNull().default(''),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Skill Suggestions ────────────────────────────────────────────────────
exports.skillSuggestions = (0, pg_core_1.pgTable)('skill_suggestions', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
categorySlug: (0, pg_core_1.varchar)('category_slug', { length: 100 }).notNull(),
nameEl: (0, pg_core_1.text)('name_el').notNull(),
nameEn: (0, pg_core_1.text)('name_en').notNull(),
nameRu: (0, pg_core_1.text)('name_ru').notNull(),
nameUk: (0, pg_core_1.text)('name_uk'),
order: (0, pg_core_1.integer)('order').notNull().default(0),
});
// ─── AI Logs ───────────────────────────────────────────────────────────────
// ─── Payments ───────────────────────────────────────────────────────────────
exports.payments = (0, pg_core_1.pgTable)('payments', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id').notNull(),
orderRef: (0, pg_core_1.text)('order_ref').notNull().unique(),
amount: (0, pg_core_1.numeric)('amount', { precision: 12, scale: 2 }).notNull(),
currency: (0, pg_core_1.varchar)('currency', { length: 3 }).notNull().default('EUR'),
// type: 'balance' = just top up wallet; 'plan' = purchase plan
type: (0, pg_core_1.text)('type', { enum: ['balance', 'plan'] }).notNull().default('balance'),
status: (0, pg_core_1.text)('status', { enum: ['pending', 'success', 'failed', 'cancelled'] }).notNull().default('pending'),
description: (0, pg_core_1.text)('description'),
transactionId: (0, pg_core_1.text)('transaction_id'),
ePayResponse: (0, pg_core_1.jsonb)('epay_response'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.balanceTransactions = (0, pg_core_1.pgTable)('balance_transactions', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
adminUserId: (0, pg_core_1.text)('admin_user_id').references(() => exports.users.id, { onDelete: 'set null' }),
paymentId: (0, pg_core_1.text)('payment_id').references(() => exports.payments.id, { onDelete: 'set null' }),
kind: (0, pg_core_1.text)('kind', {
enum: ['topup', 'plan_purchase', 'admin_topup', 'admin_adjustment'],
}).notNull(),
direction: (0, pg_core_1.text)('direction', { enum: ['credit', 'debit'] }).notNull(),
amount: (0, pg_core_1.numeric)('amount', { precision: 12, scale: 2 }).notNull(),
balanceBefore: (0, pg_core_1.numeric)('balance_before', { precision: 12, scale: 2 }).notNull(),
balanceAfter: (0, pg_core_1.numeric)('balance_after', { precision: 12, scale: 2 }).notNull(),
currency: (0, pg_core_1.varchar)('currency', { length: 3 }).notNull().default('EUR'),
description: (0, pg_core_1.text)('description'),
metadata: (0, pg_core_1.jsonb)('metadata'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── AI Logs ─────────────────────────────────────────────────────────────────
exports.aiLogs = (0, pg_core_1.pgTable)('ai_logs', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
provider: (0, pg_core_1.varchar)('provider', { length: 20 }).notNull(),
model: (0, pg_core_1.varchar)('model', { length: 100 }).notNull(),
action: (0, pg_core_1.varchar)('action', { length: 50 }).notNull(),
fromLocale: (0, pg_core_1.varchar)('from_locale', { length: 5 }),
toLocale: (0, pg_core_1.varchar)('to_locale', { length: 5 }),
inputTokens: (0, pg_core_1.integer)('input_tokens'),
outputTokens: (0, pg_core_1.integer)('output_tokens'),
totalTokens: (0, pg_core_1.integer)('total_tokens'),
durationMs: (0, pg_core_1.integer)('duration_ms'),
costUsd: (0, pg_core_1.real)('cost_usd'),
success: (0, pg_core_1.boolean)('success').notNull().default(true),
error: (0, pg_core_1.text)('error'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Referral Rewards ─────────────────────────────────────────────────────
exports.referralRewards = (0, pg_core_1.pgTable)('referral_rewards', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
referrerId: (0, pg_core_1.text)('referrer_id').notNull().references(() => exports.users.id, { onDelete: 'cascade' }),
refereeId: (0, pg_core_1.text)('referee_id').notNull().references(() => exports.users.id, { onDelete: 'cascade' }),
planId: (0, pg_core_1.text)('plan_id').notNull(),
daysAdded: (0, pg_core_1.integer)('days_added').notNull(),
expiresAt: (0, pg_core_1.timestamp)('expires_at').notNull(),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Relations ─────────────────────────────────────────────────────────────
exports.usersRelations = (0, drizzle_orm_1.relations)(exports.users, ({ many }) => ({
tasks: many(exports.tasks),
offers: many(exports.offers),
sentMessages: many(exports.messages),
reviewsGiven: many(exports.reviews, { relationName: 'author' }),
reviewsReceived: many(exports.reviews, { relationName: 'target' }),
notifications: many(exports.notifications),
favorites: many(exports.favorites),
balanceTransactions: many(exports.balanceTransactions, { relationName: 'userBalanceTransactions' }),
adminBalanceTransactions: many(exports.balanceTransactions, { relationName: 'adminBalanceTransactions' }),
}));
exports.tasksRelations = (0, drizzle_orm_1.relations)(exports.tasks, ({ one, many }) => ({
customer: one(exports.users, { fields: [exports.tasks.customerId], references: [exports.users.id] }),
offers: many(exports.offers),
chatRooms: many(exports.chatRooms),
reviews: many(exports.reviews),
favorites: many(exports.favorites),
}));
exports.offersRelations = (0, drizzle_orm_1.relations)(exports.offers, ({ one }) => ({
task: one(exports.tasks, { fields: [exports.offers.taskId], references: [exports.tasks.id] }),
specialist: one(exports.users, { fields: [exports.offers.specialistId], references: [exports.users.id] }),
}));
exports.chatRoomsRelations = (0, drizzle_orm_1.relations)(exports.chatRooms, ({ one, many }) => ({
task: one(exports.tasks, { fields: [exports.chatRooms.taskId], references: [exports.tasks.id] }),
customer: one(exports.users, { fields: [exports.chatRooms.customerId], references: [exports.users.id] }),
specialist: one(exports.users, { fields: [exports.chatRooms.specialistId], references: [exports.users.id] }),
messages: many(exports.messages),
}));
exports.messagesRelations = (0, drizzle_orm_1.relations)(exports.messages, ({ one }) => ({
room: one(exports.chatRooms, { fields: [exports.messages.roomId], references: [exports.chatRooms.id] }),
sender: one(exports.users, { fields: [exports.messages.senderId], references: [exports.users.id] }),
}));
exports.reviewsRelations = (0, drizzle_orm_1.relations)(exports.reviews, ({ one }) => ({
task: one(exports.tasks, { fields: [exports.reviews.taskId], references: [exports.tasks.id] }),
author: one(exports.users, { fields: [exports.reviews.authorId], references: [exports.users.id], relationName: 'author' }),
target: one(exports.users, { fields: [exports.reviews.targetId], references: [exports.users.id], relationName: 'target' }),
}));
exports.notificationsRelations = (0, drizzle_orm_1.relations)(exports.notifications, ({ one }) => ({
recipient: one(exports.users, { fields: [exports.notifications.recipientId], references: [exports.users.id] }),
}));
exports.favoritesRelations = (0, drizzle_orm_1.relations)(exports.favorites, ({ one }) => ({
user: one(exports.users, { fields: [exports.favorites.userId], references: [exports.users.id] }),
task: one(exports.tasks, { fields: [exports.favorites.taskId], references: [exports.tasks.id] }),
}));
exports.balanceTransactionsRelations = (0, drizzle_orm_1.relations)(exports.balanceTransactions, ({ one }) => ({
user: one(exports.users, {
fields: [exports.balanceTransactions.userId],
references: [exports.users.id],
relationName: 'userBalanceTransactions',
}),
adminUser: one(exports.users, {
fields: [exports.balanceTransactions.adminUserId],
references: [exports.users.id],
relationName: 'adminBalanceTransactions',
}),
payment: one(exports.payments, {
fields: [exports.balanceTransactions.paymentId],
references: [exports.payments.id],
}),
}));
exports.specialistFavorites = (0, pg_core_1.pgTable)('specialist_favorites', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('specialist_favorites_user_specialist_idx').on(t.userId, t.specialistId)]);
exports.specialistFavoritesRelations = (0, drizzle_orm_1.relations)(exports.specialistFavorites, ({ one }) => ({
user: one(exports.users, { fields: [exports.specialistFavorites.userId], references: [exports.users.id] }),
specialist: one(exports.users, { fields: [exports.specialistFavorites.specialistId], references: [exports.users.id] }),
}));
// ─── Daily Usage (rate limiting per plan) ──────────────────────────────────
exports.dailyUsage = (0, pg_core_1.pgTable)('daily_usage', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(),
messagesSent: (0, pg_core_1.integer)('messages_sent').notNull().default(0),
ordersMade: (0, pg_core_1.integer)('orders_made').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('daily_usage_user_date_idx').on(t.userId, t.date)]);
// ─── Google OAuth Tokens ───────────────────────────────────────────────────
// Used for both Google Calendar access and future Google Sign-In linking.
// TODO: reuse for Google Sign-In — link via googleUserId (sub claim)
exports.googleCalendarTokens = (0, pg_core_1.pgTable)('google_calendar_tokens', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' })
.unique(),
// Google OAuth sub claim — reuse for Sign-In account linking in future
googleUserId: (0, pg_core_1.text)('google_user_id').unique(),
accessToken: (0, pg_core_1.text)('access_token').notNull(),
refreshToken: (0, pg_core_1.text)('refresh_token'),
expiresAt: (0, pg_core_1.timestamp)('expires_at'),
calendarEmail: (0, pg_core_1.text)('calendar_email'),
// Comma-separated scopes granted by the user
scopes: (0, pg_core_1.text)('scopes'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
exports.googleCalendarTokensRelations = (0, drizzle_orm_1.relations)(exports.googleCalendarTokens, ({ one }) => ({
user: one(exports.users, { fields: [exports.googleCalendarTokens.userId], references: [exports.users.id] }),
}));
// ─── User Statuses (CanHelp Now / Need Help) ──────────────────────────────
exports.userStatuses = (0, pg_core_1.pgTable)('user_statuses', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
statusType: (0, pg_core_1.text)('status_type', { enum: ['canhelp_now', 'need_help'] }).notNull(),
isActive: (0, pg_core_1.boolean)('is_active').notNull().default(false),
activatedAt: (0, pg_core_1.timestamp)('activated_at'),
expiresAt: (0, pg_core_1.timestamp)('expires_at'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('user_statuses_user_type_idx').on(t.userId, t.statusType)]);
exports.userStatusesRelations = (0, drizzle_orm_1.relations)(exports.userStatuses, ({ one }) => ({
user: one(exports.users, { fields: [exports.userStatuses.userId], references: [exports.users.id] }),
}));
// ─── FOMO: Task Views ──────────────────────────────────────────────────────
exports.taskViews = (0, pg_core_1.pgTable)('task_views', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
userPlanTier: (0, pg_core_1.text)('user_plan_tier'),
viewedAt: (0, pg_core_1.timestamp)('viewed_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('task_views_task_user_idx').on(t.taskId, t.userId)]);
// ─── FOMO: Task Stats (aggregated per task) ────────────────────────────────
exports.taskStats = (0, pg_core_1.pgTable)('task_stats', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
totalViews: (0, pg_core_1.integer)('total_views').notNull().default(0),
proViews: (0, pg_core_1.integer)('pro_views').notNull().default(0),
totalOffers: (0, pg_core_1.integer)('total_offers').notNull().default(0),
avgResponseMin: (0, pg_core_1.integer)('avg_response_min'),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('task_stats_task_idx').on(t.taskId)]);
// ─── FOMO: Missed Orders (per specialist per task) ─────────────────────────
exports.missedOrders = (0, pg_core_1.pgTable)('missed_orders', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
taskId: (0, pg_core_1.text)('task_id')
.notNull()
.references(() => exports.tasks.id, { onDelete: 'cascade' }),
reason: (0, pg_core_1.text)('reason'),
estimatedValue: (0, pg_core_1.numeric)('estimated_value', { precision: 10, scale: 2 }),
takenByPro: (0, pg_core_1.boolean)('taken_by_pro').notNull().default(false),
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── FOMO: Daily Specialist Stats (aggregated per day) ─────────────────────
exports.dailySpecialistStats = (0, pg_core_1.pgTable)('daily_specialist_stats', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(),
missedTotal: (0, pg_core_1.integer)('missed_total').notNull().default(0),
missedByPro: (0, pg_core_1.integer)('missed_by_pro').notNull().default(0),
missedRevenue: (0, pg_core_1.numeric)('missed_revenue', { precision: 10, scale: 2 }).notNull().default('0'),
receivedOrders: (0, pg_core_1.integer)('received_orders').notNull().default(0),
totalAvailable: (0, pg_core_1.integer)('total_available').notNull().default(0),
hotOrdersMissed: (0, pg_core_1.integer)('hot_orders_missed').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
}, (t) => [(0, pg_core_1.uniqueIndex)('daily_spec_stats_idx').on(t.specialistId, t.date)]);
// ─── Auto-Response Settings (Ultimate specialists) ─────────────────────────
exports.autoResponseSettings = (0, pg_core_1.pgTable)('auto_response_settings', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' })
.unique(),
isEnabled: (0, pg_core_1.boolean)('is_enabled').notNull().default(false),
categories: (0, pg_core_1.text)('categories').array(), // filter: only respond to these categories (null = all from cards)
locations: (0, pg_core_1.text)('locations').array(), // filter: only respond to these locations (null = all from cards)
minBudget: (0, pg_core_1.numeric)('min_budget', { precision: 10, scale: 2 }), // minimum task budget to respond
maxBudget: (0, pg_core_1.numeric)('max_budget', { precision: 10, scale: 2 }), // maximum task budget to respond
defaultPrice: (0, pg_core_1.numeric)('default_price', { precision: 10, scale: 2 }), // price in auto-generated offer
defaultMessage: (0, pg_core_1.text)('default_message'), // message in auto-generated offer
maxAutoOffersPerDay: (0, pg_core_1.integer)('max_auto_offers_per_day').notNull().default(10),
todayAutoOffers: (0, pg_core_1.integer)('today_auto_offers').notNull().default(0),
todayDate: (0, pg_core_1.varchar)('today_date', { length: 10 }), // for resetting daily counter
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Personal Site: Page Views ─────────────────────────────────────────────
exports.sitePageViews = (0, pg_core_1.pgTable)('site_page_views', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
siteOwnerId: (0, pg_core_1.text)('site_owner_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
visitorFingerprint: (0, pg_core_1.text)('visitor_fingerprint').notNull(), // privacy-safe hash(IP+UA)[0..16]
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(), // YYYY-MM-DD
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Support Tickets ────────────────────────────────────────────────────────
exports.supportTickets = (0, pg_core_1.pgTable)('support_tickets', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
type: (0, pg_core_1.varchar)('type', { length: 20 }).notNull().default('bug'), // 'bug' | 'suggestion' | 'other'
subject: (0, pg_core_1.text)('subject').notNull(),
body: (0, pg_core_1.text)('body').notNull(),
status: (0, pg_core_1.varchar)('status', { length: 20 }).notNull().default('open'), // 'open' | 'in_progress' | 'resolved' | 'closed'
adminReply: (0, pg_core_1.text)('admin_reply'),
adminId: (0, pg_core_1.text)('admin_id').references(() => exports.users.id, { onDelete: 'set null' }),
repliedAt: (0, pg_core_1.timestamp)('replied_at'),
bodyTranslations: (0, pg_core_1.jsonb)('body_translations').$type(),
adminReplyTranslations: (0, pg_core_1.jsonb)('admin_reply_translations').$type(),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
updatedAt: (0, pg_core_1.timestamp)('updated_at').notNull().defaultNow(),
});
// ─── Personal Site: Contact Message Logs ───────────────────────────────────
exports.siteContactLogs = (0, pg_core_1.pgTable)('site_contact_logs', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
siteOwnerId: (0, pg_core_1.text)('site_owner_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
date: (0, pg_core_1.varchar)('date', { length: 10 }).notNull(), // YYYY-MM-DD
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Price List Groups (groups within a specialist card) ────────────────────
exports.priceListGroups = (0, pg_core_1.pgTable)('price_list_groups', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
cardId: (0, pg_core_1.text)('card_id')
.notNull()
.references(() => exports.specialistCards.id, { onDelete: 'cascade' }),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
title: (0, pg_core_1.varchar)('title', { length: 200 }).notNull(),
titleEl: (0, pg_core_1.text)('title_el'),
titleEn: (0, pg_core_1.text)('title_en'),
titleRu: (0, pg_core_1.text)('title_ru'),
titleUk: (0, pg_core_1.text)('title_uk'),
originalLocale: (0, pg_core_1.varchar)('original_locale', { length: 5 }).notNull().default('el'),
order: (0, pg_core_1.integer)('order').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Price List Items (rows within a price list group) ─────────────────────
exports.priceListItems = (0, pg_core_1.pgTable)('price_list_items', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
groupId: (0, pg_core_1.text)('group_id')
.notNull()
.references(() => exports.priceListGroups.id, { onDelete: 'cascade' }),
cardId: (0, pg_core_1.text)('card_id')
.notNull()
.references(() => exports.specialistCards.id, { onDelete: 'cascade' }),
specialistId: (0, pg_core_1.text)('specialist_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
name: (0, pg_core_1.varchar)('name', { length: 200 }).notNull(),
nameEl: (0, pg_core_1.text)('name_el'),
nameEn: (0, pg_core_1.text)('name_en'),
nameRu: (0, pg_core_1.text)('name_ru'),
nameUk: (0, pg_core_1.text)('name_uk'),
description: (0, pg_core_1.text)('description'),
descriptionEl: (0, pg_core_1.text)('description_el'),
descriptionEn: (0, pg_core_1.text)('description_en'),
descriptionRu: (0, pg_core_1.text)('description_ru'),
descriptionUk: (0, pg_core_1.text)('description_uk'),
price: (0, pg_core_1.numeric)('price', { precision: 10, scale: 2 }),
unit: (0, pg_core_1.varchar)('unit', { length: 100 }),
unitEl: (0, pg_core_1.text)('unit_el'),
unitEn: (0, pg_core_1.text)('unit_en'),
unitRu: (0, pg_core_1.text)('unit_ru'),
unitUk: (0, pg_core_1.text)('unit_uk'),
originalLocale: (0, pg_core_1.varchar)('original_locale', { length: 5 }).notNull().default('el'),
order: (0, pg_core_1.integer)('order').notNull().default(0),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});
// ─── Plan History ──────────────────────────────────────────────────────────
exports.planHistory = (0, pg_core_1.pgTable)('plan_history', {
id: (0, pg_core_1.text)('id')
.primaryKey()
.$defaultFn(() => crypto.randomUUID()),
userId: (0, pg_core_1.text)('user_id')
.notNull()
.references(() => exports.users.id, { onDelete: 'cascade' }),
event: (0, pg_core_1.varchar)('event', { length: 30 }).notNull(),
// 'activated' | 'renewed' | 'downgraded' | 'referral_reward' | 'admin_change' | 'expired'
planId: (0, pg_core_1.text)('plan_id').notNull(),
planName: (0, pg_core_1.varchar)('plan_name', { length: 100 }).notNull(),
previousPlanId: (0, pg_core_1.text)('previous_plan_id'),
previousPlanName: (0, pg_core_1.varchar)('previous_plan_name', { length: 100 }),
expiresAt: (0, pg_core_1.timestamp)('expires_at'),
daysAdded: (0, pg_core_1.integer)('days_added'),
referralRewardId: (0, pg_core_1.text)('referral_reward_id'),
note: (0, pg_core_1.text)('note'),
createdAt: (0, pg_core_1.timestamp)('created_at').notNull().defaultNow(),
});