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 ]

-- Create essential tables for lavocat.quebec

-- Users table
CREATE TABLE IF NOT EXISTS `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
CREATE TABLE IF NOT EXISTS `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
CREATE TABLE IF NOT EXISTS `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
CREATE TABLE IF NOT EXISTS `ChatRoom` (
  `id` VARCHAR(191) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `description` VARCHAR(191) NULL,
  `createdById` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `isPrivate` BOOLEAN NOT NULL DEFAULT false,
  `maxParticipants` INT NULL,
  `status` VARCHAR(191) NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Messages
CREATE TABLE IF NOT EXISTS `Message` (
  `id` VARCHAR(191) NOT NULL,
  `content` TEXT NOT NULL,
  `chatRoomId` VARCHAR(191) NOT NULL,
  `senderId` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `messageType` VARCHAR(191) NOT NULL DEFAULT 'TEXT',
  `isEdited` BOOLEAN NOT NULL DEFAULT false,
  `isDeleted` BOOLEAN NOT NULL DEFAULT false,
  `parentMessageId` VARCHAR(191) NULL,
  `attachments` JSON NULL,
  `metadata` JSON NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Add indexes
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 `Message_chatRoomId_idx` ON `Message`(`chatRoomId`);
CREATE INDEX `Message_senderId_idx` ON `Message`(`senderId`);