Evandro Fonseca Junior
d8e7d5d7e6
- Added MongoDB chat/message integration for seller responses - Improved seller response validation and default messaging - Integrated automatic chat creation/update when responding to intentions - Added highest custom offer fields to listing queries and models - Enhanced listing data with top offer information for buyers
376 lines
13 KiB
Plaintext
376 lines
13 KiB
Plaintext
generator client {
|
|
provider = "prisma-client-js"
|
|
}
|
|
|
|
datasource db {
|
|
provider = "postgresql"
|
|
url = env("DATABASE_URL_APP")
|
|
}
|
|
|
|
model SubscriptionTier {
|
|
subscription_tier_id Int @id @default(autoincrement())
|
|
name String @db.VarChar(100)
|
|
slug String @unique @db.VarChar(50)
|
|
description String? @db.Text
|
|
level Int @default(0)
|
|
price_monthly Decimal? @db.Decimal(10, 2)
|
|
price_yearly Decimal? @db.Decimal(10, 2)
|
|
is_active Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
users User[]
|
|
features SubscriptionTierFeature[]
|
|
|
|
@@index([slug])
|
|
@@index([level])
|
|
@@map("subscription_tiers")
|
|
}
|
|
|
|
model SubscriptionTierFeature {
|
|
feature_id Int @id @default(autoincrement())
|
|
subscription_tier_id Int
|
|
feature_key String @db.VarChar(100)
|
|
label String @db.VarChar(255)
|
|
description String? @db.Text
|
|
sort_order Int @default(0)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
tier SubscriptionTier @relation(fields: [subscription_tier_id], references: [subscription_tier_id], onDelete: Cascade)
|
|
|
|
@@unique([subscription_tier_id, feature_key], map: "subscription_tier_features_unique")
|
|
@@index([subscription_tier_id, sort_order], map: "subscription_tier_features_sort_idx")
|
|
@@map("subscription_tier_features")
|
|
}
|
|
|
|
model User {
|
|
user_id Int @id @default(autoincrement())
|
|
username String @unique @db.VarChar(50)
|
|
email String @unique @db.VarChar(255)
|
|
password_hash String @db.VarChar(255)
|
|
first_name String @db.VarChar(100)
|
|
last_name String @db.VarChar(100)
|
|
phone String? @db.VarChar(20)
|
|
cpf String? @unique @db.VarChar(14)
|
|
account_level String @default("free") @db.VarChar(20)
|
|
has_donated Boolean @default(false)
|
|
whatsapp_notifications Boolean @default(true)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
last_login DateTime?
|
|
is_active Boolean @default(true)
|
|
is_verified Boolean @default(false)
|
|
|
|
characters UserCharacter[]
|
|
listings Listing[]
|
|
bids Bid[]
|
|
transactions_as_buyer Transaction[] @relation("Buyer")
|
|
transactions_as_seller Transaction[] @relation("Seller")
|
|
notifications Notification[]
|
|
inventory UserInventory[]
|
|
favorites UserFavorite[]
|
|
telemetry_events TelemetryEvent[]
|
|
purchaseIntentions PurchaseIntention[] @relation("BuyerIntentions")
|
|
subscription_tier_id Int?
|
|
subscription_tier SubscriptionTier? @relation(fields: [subscription_tier_id], references: [subscription_tier_id], onDelete: SetNull)
|
|
|
|
@@index([email])
|
|
@@index([username])
|
|
@@index([cpf])
|
|
@@index([subscription_tier_id])
|
|
@@map("users")
|
|
}
|
|
|
|
model UserCharacter {
|
|
character_id Int @id @default(autoincrement())
|
|
user_id Int
|
|
character_name String @db.VarChar(100)
|
|
server_name String @db.VarChar(50)
|
|
is_main Boolean @default(false)
|
|
verified Boolean @default(false)
|
|
created_at DateTime @default(now())
|
|
|
|
user User @relation(fields: [user_id], references: [user_id], onDelete: Cascade)
|
|
|
|
@@unique([character_name, server_name])
|
|
@@index([user_id])
|
|
@@map("user_characters")
|
|
}
|
|
|
|
model Server {
|
|
server_id Int @id @default(autoincrement())
|
|
server_name String @unique @db.VarChar(50)
|
|
server_type String? @db.VarChar(20)
|
|
location String? @db.VarChar(10)
|
|
is_active Boolean @default(true)
|
|
total_listings Int @default(0)
|
|
total_users Int @default(0)
|
|
total_subscribers Int @default(0)
|
|
total_transactions Int @default(0)
|
|
total_volume Decimal @default(0) @db.Decimal(15, 2)
|
|
average_listing_price Decimal? @db.Decimal(10, 2)
|
|
last_activity_at DateTime?
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
|
|
@@index([server_name])
|
|
@@index([is_active])
|
|
@@index([server_type])
|
|
@@map("servers")
|
|
}
|
|
|
|
model Item {
|
|
item_id Int @id @default(autoincrement())
|
|
source_table_id Int
|
|
source_table_name String @db.VarChar(50)
|
|
item_name String @db.VarChar(255)
|
|
category String @db.VarChar(50)
|
|
image_local_path String? @db.Text
|
|
image_url String? @db.Text
|
|
rarity String? @db.VarChar(20)
|
|
level Int?
|
|
weight Decimal? @db.Decimal(10, 2)
|
|
slots Int?
|
|
skill String? @db.VarChar(50)
|
|
metadata Json? @default("{}")
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
|
|
listings Listing[] @relation("ListingItem")
|
|
|
|
@@unique([source_table_name, source_table_id])
|
|
@@index([item_name])
|
|
@@index([category])
|
|
@@index([rarity])
|
|
@@map("items")
|
|
}
|
|
|
|
model Listing {
|
|
listing_id Int @id @default(autoincrement())
|
|
seller_id Int
|
|
item_id Int // Legacy - manter para compatibilidade
|
|
item_id_unified Int?
|
|
item_name String @db.VarChar(255)
|
|
quantity Int @default(1)
|
|
server_name String @db.VarChar(50)
|
|
price_pix Decimal? @db.Decimal(10, 2)
|
|
price_tibia_coins Int?
|
|
price_gold_coins BigInt?
|
|
is_auction Boolean @default(false)
|
|
current_bid_amount Decimal? @db.Decimal(10, 2)
|
|
current_bidder_id Int?
|
|
auction_ends_at DateTime?
|
|
description String? @db.Text
|
|
listing_status String @default("active") @db.VarChar(20)
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @default(now()) @updatedAt
|
|
sold_at DateTime?
|
|
views_count Int @default(0)
|
|
is_featured Boolean @default(false)
|
|
favorites_count Int @default(0)
|
|
inquiries_count Int @default(0)
|
|
price_history Json?
|
|
expires_at DateTime?
|
|
trade_item_id Int?
|
|
trade_item_name String?
|
|
trade_item_quantity Int?
|
|
min_bid_increment Decimal? @db.Decimal(10, 2)
|
|
initial_price_pix Decimal? @db.Decimal(10, 2)
|
|
initial_price_tibia_coins Int?
|
|
initial_price_gold_coins BigInt?
|
|
|
|
seller User @relation(fields: [seller_id], references: [user_id], onDelete: Cascade)
|
|
bids Bid[]
|
|
transactions Transaction[]
|
|
analytics AdAnalytics?
|
|
telemetry_events TelemetryEvent[]
|
|
favorites UserFavorite[]
|
|
purchaseIntentions PurchaseIntention[]
|
|
item Item? @relation("ListingItem", fields: [item_id_unified], references: [item_id], onDelete: SetNull)
|
|
|
|
@@index([listing_status])
|
|
@@index([item_id])
|
|
@@index([seller_id])
|
|
@@index([server_name])
|
|
@@index([created_at(sort: Desc)])
|
|
@@index([is_auction, auction_ends_at])
|
|
@@index([is_featured])
|
|
@@index([expires_at])
|
|
@@index([favorites_count(sort: Desc)])
|
|
@@map("listings")
|
|
}
|
|
|
|
model Bid {
|
|
bid_id Int @id @default(autoincrement())
|
|
listing_id Int
|
|
bidder_id Int
|
|
bid_amount Decimal @db.Decimal(10, 2)
|
|
bid_type String? @db.VarChar(20)
|
|
created_at DateTime @default(now())
|
|
is_active Boolean @default(true)
|
|
|
|
listing Listing @relation(fields: [listing_id], references: [listing_id], onDelete: Cascade)
|
|
bidder User @relation(fields: [bidder_id], references: [user_id], onDelete: Cascade)
|
|
|
|
@@index([listing_id])
|
|
@@index([bidder_id])
|
|
@@index([is_active])
|
|
@@index([listing_id, bid_amount(sort: Desc)])
|
|
@@map("bids")
|
|
}
|
|
|
|
model Transaction {
|
|
transaction_id Int @id @default(autoincrement())
|
|
listing_id Int?
|
|
buyer_id Int
|
|
seller_id Int
|
|
item_name String @db.VarChar(255)
|
|
quantity Int
|
|
server_name String @db.VarChar(50)
|
|
amount Decimal @db.Decimal(10, 2)
|
|
payment_method String? @db.VarChar(20)
|
|
escrow_fee Decimal? @db.Decimal(10, 2)
|
|
transaction_status String @default("pending") @db.VarChar(30)
|
|
created_at DateTime @default(now())
|
|
deposited_at DateTime?
|
|
delivered_at DateTime?
|
|
completed_at DateTime?
|
|
buyer_confirmed Boolean @default(false)
|
|
seller_confirmed Boolean @default(false)
|
|
buyer_notes String? @db.Text
|
|
seller_notes String? @db.Text
|
|
|
|
listing Listing? @relation(fields: [listing_id], references: [listing_id])
|
|
buyer User @relation("Buyer", fields: [buyer_id], references: [user_id])
|
|
seller User @relation("Seller", fields: [seller_id], references: [user_id])
|
|
|
|
@@index([buyer_id])
|
|
@@index([seller_id])
|
|
@@index([transaction_status])
|
|
@@index([listing_id])
|
|
@@index([created_at(sort: Desc)])
|
|
@@map("transactions")
|
|
}
|
|
|
|
model Notification {
|
|
notification_id Int @id @default(autoincrement())
|
|
user_id Int
|
|
notification_type String @db.VarChar(30)
|
|
title String @db.VarChar(255)
|
|
message String @db.Text
|
|
related_id Int?
|
|
is_read Boolean @default(false)
|
|
send_whatsapp Boolean @default(false)
|
|
whatsapp_sent Boolean @default(false)
|
|
created_at DateTime @default(now())
|
|
|
|
user User @relation(fields: [user_id], references: [user_id], onDelete: Cascade)
|
|
|
|
@@index([user_id, is_read])
|
|
@@index([notification_type])
|
|
@@index([created_at(sort: Desc)])
|
|
@@map("notifications")
|
|
}
|
|
|
|
model TelemetryEvent {
|
|
event_id Int @id @default(autoincrement())
|
|
event_type String @db.VarChar(50)
|
|
ad_id Int?
|
|
user_id Int?
|
|
session_id String? @db.VarChar(255)
|
|
metadata Json? @default("{}")
|
|
timestamp DateTime @default(now())
|
|
|
|
listing Listing? @relation(fields: [ad_id], references: [listing_id], onDelete: Cascade)
|
|
user User? @relation(fields: [user_id], references: [user_id], onDelete: SetNull)
|
|
|
|
@@index([ad_id])
|
|
@@index([user_id])
|
|
@@index([timestamp(sort: Desc)])
|
|
@@index([event_type])
|
|
@@index([session_id])
|
|
@@index([ad_id, event_type, timestamp(sort: Desc)])
|
|
@@map("telemetry_events")
|
|
}
|
|
|
|
model AdAnalytics {
|
|
ad_id Int @id
|
|
impressions Int @default(0)
|
|
clicks Int @default(0)
|
|
ctr Decimal @default(0) @db.Decimal(5, 2)
|
|
avg_view_duration Decimal @default(0) @db.Decimal(10, 2)
|
|
bounce_rate Decimal @default(0) @db.Decimal(5, 2)
|
|
inquiries Int @default(0)
|
|
favorites Int @default(0)
|
|
conversion_rate Decimal @default(0) @db.Decimal(5, 2)
|
|
time_to_sell Int?
|
|
last_updated DateTime @default(now()) @updatedAt
|
|
|
|
listing Listing @relation(fields: [ad_id], references: [listing_id], onDelete: Cascade)
|
|
|
|
@@map("ad_analytics")
|
|
}
|
|
|
|
model UserInventory {
|
|
inventory_id Int @id @default(autoincrement())
|
|
user_id Int
|
|
item_id Int
|
|
item_name String @db.VarChar(255)
|
|
quantity Int @default(1)
|
|
acquired_at DateTime @default(now())
|
|
|
|
user User @relation(fields: [user_id], references: [user_id], onDelete: Cascade)
|
|
|
|
@@unique([user_id, item_id])
|
|
@@index([user_id])
|
|
@@index([item_id])
|
|
@@map("user_inventory")
|
|
}
|
|
|
|
model UserFavorite {
|
|
user_id Int
|
|
ad_id Int
|
|
created_at DateTime @default(now())
|
|
|
|
user User @relation(fields: [user_id], references: [user_id], onDelete: Cascade)
|
|
listing Listing @relation(fields: [ad_id], references: [listing_id], onDelete: Cascade)
|
|
|
|
@@id([user_id, ad_id])
|
|
@@index([user_id])
|
|
@@index([ad_id])
|
|
@@map("user_favorites")
|
|
}
|
|
|
|
model PurchaseIntention {
|
|
intention_id Int @id @default(autoincrement())
|
|
listing_id Int
|
|
buyer_id Int
|
|
payment_method String @db.VarChar(20)
|
|
payment_method_change_count Int @default(0)
|
|
custom_offer_amount Decimal? @db.Decimal(12, 2)
|
|
custom_offer_currency String? @db.VarChar(20)
|
|
custom_offer_created_at DateTime?
|
|
status String @default("pending") @db.VarChar(20)
|
|
buyer_message String? @db.Text
|
|
seller_response String? @db.Text
|
|
created_at DateTime @default(now())
|
|
updated_at DateTime @updatedAt
|
|
expires_at DateTime?
|
|
accepted_at DateTime?
|
|
rejected_at DateTime?
|
|
|
|
listing Listing @relation(fields: [listing_id], references: [listing_id], onDelete: Cascade)
|
|
buyer User @relation("BuyerIntentions", fields: [buyer_id], references: [user_id], onDelete: Cascade)
|
|
|
|
@@index([listing_id])
|
|
@@index([buyer_id])
|
|
@@index([status])
|
|
@@index([created_at(sort: Desc)])
|
|
@@index([buyer_id, listing_id, status])
|
|
@@map("purchase_intentions")
|
|
}
|
|
|
|
|
|
|
|
|
|
|