Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory :  /home/gositeme/domains/lavocat.quebec/private_html/

Upload File : current_dir [ Writeable ] document_root [ Writeable ]

-- Database Schema for lavocat.quebec
-- Generated from Prisma schema

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS `gositeme_avocat`;
USE `gositeme_avocat`;

-- Users table (main user table)
CREATE TABLE `User` (
  `id` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NULL,
  `password` VARCHAR(191) NOT NULL,
  `role` VARCHAR(191) NOT NULL DEFAULT 'USER',
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `resetPasswordToken` VARCHAR(191) NULL,
  `resetPasswordTokenExpiry` DATETIME(3) NULL,
  `reminders` VARCHAR(191) NULL,
  `username` VARCHAR(191) NULL,
  `profilePicture` VARCHAR(191) NULL,
  `bio` VARCHAR(191) NULL,
  `title` VARCHAR(191) NULL,
  `specialization` VARCHAR(191) NULL,
  `barNumber` VARCHAR(191) NULL,
  `yearsOfExperience` INT NULL,
  `education` VARCHAR(191) NULL,
  `certifications` VARCHAR(191) NULL,
  `officeLocation` VARCHAR(191) NULL,
  `workPhone` VARCHAR(191) NULL,
  `linkedinUrl` VARCHAR(191) NULL,
  `websiteUrl` VARCHAR(191) NULL,
  `availability` VARCHAR(191) NULL,
  `timezone` VARCHAR(191) NULL,
  `pronouns` VARCHAR(191) NULL,
  `isProfilePublic` BOOLEAN NOT NULL DEFAULT false,
  `lastActive` DATETIME(3) NULL,
  `hourlyRate` DOUBLE PRECISION NULL,
  `proBono` BOOLEAN NOT NULL DEFAULT false,
  `boldnessRating` DOUBLE PRECISION NULL,
  `transparencyRating` DOUBLE PRECISION NULL,
  `winRate` DOUBLE PRECISION NULL,
  `totalCases` INT NOT NULL DEFAULT 0,
  `wonCases` INT NOT NULL DEFAULT 0,
  `lostCases` INT NOT NULL DEFAULT 0,
  `averageRating` DOUBLE PRECISION NULL,
  `isVerified` BOOLEAN NOT NULL DEFAULT false,
  `xpPoints` INT NOT NULL DEFAULT 0,
  `level` INT NOT NULL DEFAULT 1,
  `currentStreak` INT NOT NULL DEFAULT 0,
  `totalBadges` INT NOT NULL DEFAULT 0,
  `reviewsWritten` INT NOT NULL DEFAULT 0,
  `forumPosts` INT NOT NULL DEFAULT 0,
  `helpedOthers` INT NOT NULL DEFAULT 0,
  `totalEndorsements` INT NOT NULL DEFAULT 0,
  `profileViews` INT NOT NULL DEFAULT 0,
  `observationHours` DOUBLE PRECISION NOT NULL DEFAULT 0,
  `reformProposals` INT NOT NULL DEFAULT 0,
  `wisdomScore` DOUBLE PRECISION NOT NULL DEFAULT 0,
  `civicEngagement` DOUBLE PRECISION NOT NULL DEFAULT 0,
  `accountBalance` DOUBLE PRECISION NOT NULL DEFAULT 0.0,
  `isPaymentVerified` BOOLEAN NOT NULL DEFAULT false,
  `donationTotal` DOUBLE PRECISION NOT NULL DEFAULT 0.0,
  `subscriptionTier` VARCHAR(191) NULL,
  `subscriptionExpiry` DATETIME(3) NULL,
  `theme` VARCHAR(191) NOT NULL DEFAULT 'light',
  `gender` VARCHAR(191) NULL,
  `phone` VARCHAR(191) NULL,
  `address` VARCHAR(191) NULL,
  `emergencyContact` VARCHAR(191) NULL,
  `emergencyPhone` VARCHAR(191) NULL,
  `dateOfBirth` DATETIME(3) NULL,
  `occupation` VARCHAR(191) NULL,
  `language` VARCHAR(191) NOT NULL DEFAULT 'en',
  `notifications` BOOLEAN NOT NULL DEFAULT true,
  `lawFirmId` VARCHAR(191) NULL,
  `isActive` BOOLEAN NOT NULL DEFAULT true,
  `status` VARCHAR(191) NOT NULL DEFAULT 'ACTIVE',
  `experience` INT NULL,
  `rating` DOUBLE PRECISION NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Sessions table (for NextAuth)
CREATE TABLE `Session` (
  `id` VARCHAR(191) NOT NULL,
  `sessionToken` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  `expires` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Verification tokens table (for NextAuth)
CREATE TABLE `VerificationToken` (
  `identifier` VARCHAR(191) NOT NULL,
  `token` VARCHAR(191) NOT NULL,
  `expires` DATETIME(3) NOT NULL,
  PRIMARY KEY (`identifier`, `token`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Chat rooms table
CREATE TABLE `ChatRoom` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NULL,
  `type` VARCHAR(191) NOT NULL DEFAULT 'PRIVATE',
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `createdById` VARCHAR(191) NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Chat participants table
CREATE TABLE `ChatParticipant` (
  `id` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  `chatRoomId` VARCHAR(191) NOT NULL,
  `role` VARCHAR(191) NOT NULL DEFAULT 'MEMBER',
  `joinedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Messages table
CREATE TABLE `Message` (
  `id` VARCHAR(191) NOT NULL,
  `content` TEXT NOT NULL,
  `type` VARCHAR(191) NOT NULL DEFAULT 'TEXT',
  `chatRoomId` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `isEdited` BOOLEAN NOT NULL DEFAULT false,
  `isDeleted` BOOLEAN NOT NULL DEFAULT false,
  `replyToId` VARCHAR(191) NULL,
  `fileUrl` VARCHAR(191) NULL,
  `fileName` VARCHAR(191) NULL,
  `fileSize` INT NULL,
  `fileType` VARCHAR(191) NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Law firms table
CREATE TABLE `LawFirm` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `address` VARCHAR(191) NULL,
  `phone` VARCHAR(191) NULL,
  `email` VARCHAR(191) NULL,
  `website` VARCHAR(191) NULL,
  `logo` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `ownerId` VARCHAR(191) NOT NULL,
  `isVerified` BOOLEAN NOT NULL DEFAULT false,
  `specialization` VARCHAR(191) NULL,
  `size` VARCHAR(191) NULL,
  `foundedYear` INT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Legal cases table
CREATE TABLE `LegalCase` (
  `id` VARCHAR(191) NOT NULL,
  `title` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `status` VARCHAR(191) NOT NULL DEFAULT 'OPEN',
  `priority` VARCHAR(191) NOT NULL DEFAULT 'MEDIUM',
  `category` VARCHAR(191) NULL,
  `clientId` VARCHAR(191) NOT NULL,
  `lawyerId` VARCHAR(191) NULL,
  `lawFirmId` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `dueDate` DATETIME(3) NULL,
  `estimatedHours` INT NULL,
  `actualHours` INT NULL,
  `hourlyRate` DOUBLE PRECISION NULL,
  `totalCost` DOUBLE PRECISION NULL,
  `caseNumber` VARCHAR(191) NULL,
  `court` VARCHAR(191) NULL,
  `judge` VARCHAR(191) NULL,
  `opposingParty` VARCHAR(191) NULL,
  `opposingLawyer` VARCHAR(191) NULL,
  `caseType` VARCHAR(191) NULL,
  `jurisdiction` VARCHAR(191) NULL,
  `filingDate` DATETIME(3) NULL,
  `hearingDate` DATETIME(3) NULL,
  `settlementAmount` DOUBLE PRECISION NULL,
  `outcome` VARCHAR(191) NULL,
  `notes` TEXT NULL,
  `documents` JSON NULL,
  `tags` JSON NULL,
  `isPublic` BOOLEAN NOT NULL DEFAULT false,
  `isArchived` BOOLEAN NOT NULL DEFAULT false,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Documents table
CREATE TABLE `Document` (
  `id` VARCHAR(191) NOT NULL,
  `title` VARCHAR(191) NOT NULL,
  `description` TEXT NULL,
  `fileUrl` VARCHAR(191) NOT NULL,
  `fileName` VARCHAR(191) NOT NULL,
  `fileSize` INT NOT NULL,
  `fileType` VARCHAR(191) NOT NULL,
  `userId` VARCHAR(191) NOT NULL,
  `caseId` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `isPublic` BOOLEAN NOT NULL DEFAULT false,
  `version` INT NOT NULL DEFAULT 1,
  `tags` JSON NULL,
  `category` VARCHAR(191) NULL,
  `status` VARCHAR(191) NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Add indexes for better performance
CREATE UNIQUE INDEX `User_email_key` ON `User`(`email`);
CREATE UNIQUE INDEX `User_username_key` ON `User`(`username`);
CREATE UNIQUE INDEX `Session_sessionToken_key` ON `Session`(`sessionToken`);
CREATE INDEX `Session_userId_idx` ON `Session`(`userId`);
CREATE INDEX `ChatParticipant_userId_idx` ON `ChatParticipant`(`userId`);
CREATE INDEX `ChatParticipant_chatRoomId_idx` ON `ChatParticipant`(`chatRoomId`);
CREATE INDEX `Message_chatRoomId_idx` ON `Message`(`chatRoomId`);
CREATE INDEX `Message_userId_idx` ON `Message`(`userId`);
CREATE INDEX `LegalCase_clientId_idx` ON `LegalCase`(`clientId`);
CREATE INDEX `LegalCase_lawyerId_idx` ON `LegalCase`(`lawyerId`);
CREATE INDEX `Document_userId_idx` ON `Document`(`userId`);
CREATE INDEX `Document_caseId_idx` ON `Document`(`caseId`);

-- Add foreign key constraints
ALTER TABLE `Session` ADD CONSTRAINT `Session_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ChatRoom` ADD CONSTRAINT `ChatRoom_createdById_fkey` FOREIGN KEY (`createdById`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `ChatParticipant` ADD CONSTRAINT `ChatParticipant_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `ChatParticipant` ADD CONSTRAINT `ChatParticipant_chatRoomId_fkey` FOREIGN KEY (`chatRoomId`) REFERENCES `ChatRoom`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Message` ADD CONSTRAINT `Message_chatRoomId_fkey` FOREIGN KEY (`chatRoomId`) REFERENCES `ChatRoom`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Message` ADD CONSTRAINT `Message_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `LawFirm` ADD CONSTRAINT `LawFirm_ownerId_fkey` FOREIGN KEY (`ownerId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `LegalCase` ADD CONSTRAINT `LegalCase_clientId_fkey` FOREIGN KEY (`clientId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `LegalCase` ADD CONSTRAINT `LegalCase_lawyerId_fkey` FOREIGN KEY (`lawyerId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `LegalCase` ADD CONSTRAINT `LegalCase_lawFirmId_fkey` FOREIGN KEY (`lawFirmId`) REFERENCES `LawFirm`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE `Document` ADD CONSTRAINT `Document_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;