-- Pharmacy POS — MySQL schema
-- Generated from the application models. Import via phpMyAdmin or:
--   mysql -u USER -p DBNAME < schema.sql
-- (Optional: the app also creates these tables automatically on startup.)

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `_direct_sale_populate_stub` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `accountpayables` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `id` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `name` TEXT NULL,
  `type` TEXT NULL,
  `phone` TEXT NULL,
  `email` TEXT NULL,
  `address` TEXT NULL,
  `opening_balance` DOUBLE NULL,
  `balance_type` TEXT NULL,
  `payment_method_preferred` TEXT NULL,
  `bank_name` TEXT NULL,
  `account_number` TEXT NULL,
  `status` TEXT NULL,
  `notes` TEXT NULL,
  `created_at` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_id` (`id`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_type` (`type`(100)),
  KEY `idx_status` (`status`(100)),
  KEY `idx_phone` (`phone`(100)),
  KEY `idx_email` (`email`(100)),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `advancepayments` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `entityId` VARCHAR(191) NULL,
  `entityType` VARCHAR(191) NULL,
  `amount` DOUBLE NULL,
  `paymentDate` DATETIME(3) NULL,
  `paymentType` VARCHAR(191) NULL,
  `reference` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `balanceAfterTransaction` DOUBLE NULL,
  `createdBy` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_entityId` (`entityId`),
  KEY `idx_entityType` (`entityType`),
  KEY `idx_paymentDate` (`paymentDate`),
  KEY `idx_paymentType` (`paymentType`),
  KEY `idx_entityId_entityType` (`entityId`, `entityType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attendances` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `employeeId` CHAR(24) NULL,
  `date` DATETIME(3) NULL,
  `status` VARCHAR(191) NULL,
  `note` TEXT NULL,
  `markedBy` CHAR(24) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_employeeId` (`employeeId`),
  KEY `idx_date` (`date`),
  KEY `idx_status` (`status`),
  UNIQUE KEY `uniq_employeeId_date` (`employeeId`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `auditlogs` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `action` VARCHAR(191) NULL,
  `status` VARCHAR(191) NULL,
  `module` VARCHAR(191) NULL,
  `entityType` VARCHAR(191) NULL,
  `entityId` VARCHAR(191) NULL,
  `entityTitle` VARCHAR(191) NULL,
  `message` TEXT NULL,
  `projectId` CHAR(24) NULL,
  `userId` CHAR(24) NULL,
  `userName` VARCHAR(191) NULL,
  `userEmail` VARCHAR(191) NULL,
  `ipAddress` TEXT NULL,
  `userAgent` TEXT NULL,
  `requestId` VARCHAR(191) NULL,
  `occurredAt` DATETIME(3) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_action` (`action`),
  KEY `idx_status` (`status`),
  KEY `idx_module` (`module`),
  KEY `idx_entityType` (`entityType`),
  KEY `idx_entityId` (`entityId`),
  KEY `idx_entityTitle` (`entityTitle`),
  KEY `idx_projectId` (`projectId`),
  KEY `idx_userId` (`userId`),
  KEY `idx_userName` (`userName`),
  KEY `idx_userEmail` (`userEmail`),
  KEY `idx_requestId` (`requestId`),
  KEY `idx_occurredAt` (`occurredAt`),
  KEY `idx_projectId_occurredAt` (`projectId`, `occurredAt`),
  KEY `idx_action_occurredAt` (`action`, `occurredAt`),
  KEY `idx_module_occurredAt` (`module`, `occurredAt`),
  KEY `idx_status_occurredAt` (`status`, `occurredAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bills` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `billNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `date` DATETIME(3) NULL,
  `discount` DOUBLE NULL,
  `total` DOUBLE NULL,
  `totalRevenue` DOUBLE NULL,
  `totalGrossProfit` DOUBLE NULL,
  `commissionAmount` DOUBLE NULL,
  `commissionRate` DOUBLE NULL,
  `commissionType` VARCHAR(191) NULL,
  `grossProfitBeforeCommission` DOUBLE NULL,
  `netProfitAfterCommission` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `decimalPlaces` DOUBLE NULL,
  `status` VARCHAR(191) NULL,
  `paymentMethod` TEXT NULL,
  `paymentStatus` VARCHAR(191) NULL,
  `totalRefunded` DOUBLE NULL,
  `cashier` TEXT NULL,
  `accountId` CHAR(24) NULL,
  `customerAccountId` CHAR(24) NULL,
  `accountName` TEXT NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_billNumber` (`billNumber`),
  KEY `idx_currency` (`currency`),
  KEY `idx_paymentStatus` (`paymentStatus`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_customerAccountId` (`customerAccountId`),
  KEY `idx_date` (`date`),
  KEY `idx_status` (`status`),
  KEY `idx_cashier` (`cashier`(100)),
  KEY `idx_paymentMethod` (`paymentMethod`(100)),
  KEY `idx_date_status` (`date`, `status`),
  KEY `idx_accountId_date` (`accountId`, `date`),
  KEY `idx_accountName` (`accountName`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cashaccounts` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `accountId` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `name` TEXT NULL,
  `phone` TEXT NULL,
  `description` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_accountId` (`accountId`),
  KEY `idx_name` (`name`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cashdailysnapshots` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `dateKey` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `date` DATETIME(3) NULL,
  `savedAt` DATETIME(3) NULL,
  `savedBy` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_dateKey` (`dateKey`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cashins` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `amount` DOUBLE NULL,
  `category` TEXT NULL,
  `description` TEXT NULL,
  `accountId` VARCHAR(191) NULL,
  `currency` TEXT NULL,
  `timestamp` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_accountId_timestamp` (`accountId`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cashouts` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `amount` DOUBLE NULL,
  `category` TEXT NULL,
  `description` TEXT NULL,
  `image` TEXT NULL,
  `accountId` VARCHAR(191) NULL,
  `currency` TEXT NULL,
  `isRefund` TINYINT(1) NULL,
  `timestamp` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_isRefund` (`isRefund`),
  KEY `idx_timestamp` (`timestamp`),
  KEY `idx_accountId_timestamp` (`accountId`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `categories` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  `code` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `color` TEXT NULL,
  `parent` CHAR(24) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_code` (`code`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chat_conversations` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `type` VARCHAR(191) NULL,
  `title` TEXT NULL,
  `participantPairKey` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `lastMessageAt` DATETIME(3) NULL,
  `lastMessagePreview` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_participantPairKey` (`participantPairKey`),
  KEY `idx_updatedAt` (`updatedAt`),
  KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `chat_messages` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `conversationId` CHAR(24) NULL,
  `senderId` CHAR(24) NULL,
  `text` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_conversationId` (`conversationId`),
  KEY `idx_senderId` (`senderId`),
  KEY `idx_conversationId_createdAt` (`conversationId`, `createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coastcategories` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `icon` TEXT NULL,
  `color` TEXT NULL,
  `accountId` CHAR(24) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coasts` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `title` TEXT NULL,
  `category` TEXT NULL,
  `amount` DOUBLE NULL,
  `date` DATETIME(3) NULL,
  `paymentMethod` VARCHAR(191) NULL,
  `status` VARCHAR(191) NULL,
  `description` TEXT NULL,
  `reference` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `accountId` CHAR(24) NULL,
  `recurring` TINYINT(1) NULL,
  `createdBy` TEXT NULL,
  `sourceType` TEXT NULL,
  `sourceMedicineId` CHAR(24) NULL,
  `sourceMedicineBatchId` CHAR(24) NULL,
  `sourceMedicineBatchNumber` TEXT NULL,
  `sourceProductId` CHAR(24) NULL,
  `sourceProductBatchId` CHAR(24) NULL,
  `sourceProductBatchNumber` TEXT NULL,
  `wastageType` VARCHAR(191) NULL,
  `wastageQty` DOUBLE NULL,
  `wastageUnitCost` DOUBLE NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_reference` (`reference`),
  KEY `idx_date` (`date`),
  KEY `idx_category` (`category`(100)),
  KEY `idx_status` (`status`),
  KEY `idx_accountId` (`accountId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `companies` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  `logo` TEXT NULL,
  `phoneNo` TEXT NULL,
  `phoneNo2` TEXT NULL,
  `email` TEXT NULL,
  `address` TEXT NULL,
  `city` TEXT NULL,
  `country` TEXT NULL,
  `website` TEXT NULL,
  `taxNumber` TEXT NULL,
  `registrationNumber` TEXT NULL,
  `description` TEXT NULL,
  `contactPerson` TEXT NULL,
  `postalCode` TEXT NULL,
  `isActive` TINYINT(1) NULL,
  `batchPricingMode` VARCHAR(191) NULL,
  `whatsappNumber` TEXT NULL,
  `telegramUsername` TEXT NULL,
  `profitShareSyncedNetProfit` DOUBLE NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_phoneNo` (`phoneNo`(100)),
  KEY `idx_email` (`email`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `counters` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` VARCHAR(191) COLLATE utf8mb4_bin NOT NULL,
  `doc` JSON NOT NULL,
  `seq` DOUBLE NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `drawersessions` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `sessionNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `accountId` VARCHAR(191) NULL,
  `cashier` VARCHAR(191) NULL,
  `currency` TEXT NULL,
  `openingFloat` DOUBLE NULL,
  `openedAt` DATETIME(3) NULL,
  `closedAt` DATETIME(3) NULL,
  `status` VARCHAR(191) NULL,
  `expectedCash` DOUBLE NULL,
  `countedCash` DOUBLE NULL,
  `variance` DOUBLE NULL,
  `closeNotes` TEXT NULL,
  `openedBy` TEXT NULL,
  `closedBy` TEXT NULL,
  `varianceRecorded` TINYINT(1) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_sessionNumber` (`sessionNumber`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_cashier` (`cashier`),
  KEY `idx_openedAt` (`openedAt`),
  KEY `idx_closedAt` (`closedAt`),
  KEY `idx_status` (`status`),
  KEY `idx_accountId_status` (`accountId`, `status`),
  KEY `idx_status_openedAt` (`status`, `openedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employees` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `fullName` VARCHAR(191) NULL,
  `phone` VARCHAR(191) NULL,
  `email` VARCHAR(191) NULL,
  `address` TEXT NULL,
  `afghanistanIdCardNumber` VARCHAR(191) NULL,
  `idCardScanUrl` TEXT NULL,
  `position` VARCHAR(191) NULL,
  `department` VARCHAR(191) NULL,
  `baseSalary` DOUBLE NULL,
  `salaryDay` DOUBLE NULL,
  `hireDate` DATETIME(3) NULL,
  `status` VARCHAR(191) NULL,
  `notes` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_fullName` (`fullName`),
  KEY `idx_phone` (`phone`),
  KEY `idx_email` (`email`),
  KEY `idx_afghanistanIdCardNumber` (`afghanistanIdCardNumber`),
  KEY `idx_position` (`position`),
  KEY `idx_department` (`department`),
  KEY `idx_salaryDay` (`salaryDay`),
  KEY `idx_hireDate` (`hireDate`),
  KEY `idx_status` (`status`),
  KEY `idx_fullName_status` (`fullName`, `status`),
  KEY `idx_department_status` (`department`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `expenseaccounts` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `code` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `type` VARCHAR(191) NULL,
  `affectsNetProfit` TINYINT(1) NULL,
  `description` TEXT NULL,
  `color` TEXT NULL,
  `icon` TEXT NULL,
  `isActive` TINYINT(1) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_name` (`name`),
  UNIQUE KEY `uniq_code` (`code`),
  KEY `idx_type` (`type`),
  KEY `idx_affectsNetProfit` (`affectsNetProfit`),
  KEY `idx_isActive` (`isActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `invoices` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `invoiceNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `invoiceDate` DATETIME(3) NULL,
  `dueDate` DATETIME(3) NULL,
  `deliveryDate` DATETIME(3) NULL,
  `invoiceType` VARCHAR(191) NULL,
  `paymentStatus` VARCHAR(191) NULL,
  `accountId` CHAR(24) NULL,
  `subtotal` DOUBLE NULL,
  `discount` DOUBLE NULL,
  `tax` DOUBLE NULL,
  `totalAmount` DOUBLE NULL,
  `totalRevenue` DOUBLE NULL,
  `totalGrossProfit` DOUBLE NULL,
  `commissionAmount` DOUBLE NULL,
  `commissionRate` DOUBLE NULL,
  `commissionType` VARCHAR(191) NULL,
  `grossProfitBeforeCommission` DOUBLE NULL,
  `netProfitAfterCommission` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `decimalPlaces` DOUBLE NULL,
  `amountPaid` DOUBLE NULL,
  `overpaymentAmount` DOUBLE NULL,
  `referenceNumber` TEXT NULL,
  `paymentMethod` TEXT NULL,
  `cashier` TEXT NULL,
  `purchaseOrderNumber` TEXT NULL,
  `salesPerson` TEXT NULL,
  `loanId` CHAR(24) NULL,
  `loanNumber` TEXT NULL,
  `terms` TEXT NULL,
  `notes` TEXT NULL,
  `source` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_invoiceNumber` (`invoiceNumber`),
  KEY `idx_invoiceDate` (`invoiceDate`),
  KEY `idx_dueDate` (`dueDate`),
  KEY `idx_deliveryDate` (`deliveryDate`),
  KEY `idx_invoiceType` (`invoiceType`),
  KEY `idx_paymentStatus` (`paymentStatus`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_totalAmount` (`totalAmount`),
  KEY `idx_currency` (`currency`),
  KEY `idx_loanId` (`loanId`),
  KEY `idx_paymentStatus_invoiceDate` (`paymentStatus`, `invoiceDate`),
  KEY `idx_accountId_invoiceDate` (`accountId`, `invoiceDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loanaccounts` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `accountCode` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `name` TEXT NULL,
  `professionalName` TEXT NULL,
  `company` TEXT NULL,
  `phone` TEXT NULL,
  `email` TEXT NULL,
  `address` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `photoUrl` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_accountCode` (`accountCode`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loanpayments` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `loanId` CHAR(24) NULL,
  `paymentDate` DATETIME(3) NULL,
  `amount` DOUBLE NULL,
  `paymentMethod` VARCHAR(191) NULL,
  `reference` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_loanId_paymentDate` (`loanId`, `paymentDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `loans` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `loanNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `accountId` CHAR(24) NULL,
  `loanDirection` VARCHAR(191) NULL,
  `principalAmount` DOUBLE NULL,
  `date` DATETIME(3) NULL,
  `note` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `receivableType` VARCHAR(191) NULL,
  `affectsRevenue` TINYINT(1) NULL,
  `affectsNetProfit` TINYINT(1) NULL,
  `isRefunded` TINYINT(1) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_loanNumber` (`loanNumber`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_status` (`status`),
  KEY `idx_receivableType` (`receivableType`),
  KEY `idx_isRefunded` (`isRefunded`),
  KEY `idx_accountId_status` (`accountId`, `status`),
  KEY `idx_date` (`date`),
  KEY `idx_receivableType_status` (`receivableType`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `manufacturers` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `medicines` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  `model` TEXT NULL,
  `medicineForm` TEXT NULL,
  `strength` TEXT NULL,
  `sku` TEXT NULL,
  `barcode` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `stock` DOUBLE NULL,
  `costPrice` DOUBLE NULL,
  `sellingPrice` DOUBLE NULL,
  `averageCostPrice` DOUBLE NULL,
  `averageSellingPrice` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `status` TINYINT(1) NULL,
  `description` TEXT NULL,
  `image` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_status` (`status`),
  KEY `idx_medicineForm_status` (`medicineForm`(100), `status`),
  KEY `idx_name_model` (`name`(100), `model`(100)),
  UNIQUE KEY `uniq_barcode` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `netprofitentries` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `title` TEXT NULL,
  `amount` DOUBLE NULL,
  `effect` VARCHAR(191) NULL,
  `date` DATETIME(3) NULL,
  `description` TEXT NULL,
  `reference` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `createdBy` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_date` (`date`),
  UNIQUE KEY `uniq_reference` (`reference`),
  KEY `idx_status` (`status`),
  KEY `idx_status_date` (`status`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `orderNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `editionalNotes` TEXT NULL,
  `date` DATETIME(3) NULL,
  `customerName` VARCHAR(191) NULL,
  `customerPhone` VARCHAR(191) NULL,
  `customerAddress` TEXT NULL,
  `patientCategory` VARCHAR(191) NULL,
  `prescriberName` TEXT NULL,
  `prescriptionRef` VARCHAR(191) NULL,
  `serialNumber` VARCHAR(191) NULL,
  `clinicalNotes` TEXT NULL,
  `assignedPharmacist` VARCHAR(191) NULL,
  `estimatedCompletionDate` DATETIME(3) NULL,
  `paymentStatus` VARCHAR(191) NULL,
  `advancePayment` DOUBLE NULL,
  `paymentMethod` TEXT NULL,
  `totalAmount` DOUBLE NULL,
  `condition` TEXT NULL,
  `status` TEXT NULL,
  `deliveryDate` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_orderNumber` (`orderNumber`),
  KEY `idx_customerName` (`customerName`),
  KEY `idx_customerPhone` (`customerPhone`),
  KEY `idx_patientCategory` (`patientCategory`),
  KEY `idx_prescriptionRef` (`prescriptionRef`),
  KEY `idx_serialNumber` (`serialNumber`),
  KEY `idx_assignedPharmacist` (`assignedPharmacist`),
  KEY `idx_paymentStatus` (`paymentStatus`),
  KEY `idx_date` (`date`),
  KEY `idx_status` (`status`(100)),
  KEY `idx_deliveryDate` (`deliveryDate`),
  KEY `idx_date_status` (`date`, `status`(100)),
  KEY `idx_customerPhone_date` (`customerPhone`, `date`),
  KEY `idx_serialNumber_date` (`serialNumber`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `partners` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `partnerId` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `name` TEXT NULL,
  `phone` TEXT NULL,
  `email` TEXT NULL,
  `ownershipPercentage` DOUBLE NULL,
  `capitalBalance` DOUBLE NULL,
  `profitBalance` DOUBLE NULL,
  `accountBalance` DOUBLE NULL,
  `initialCapital` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `status` VARCHAR(191) NULL,
  `joinedDate` DATETIME(3) NULL,
  `description` TEXT NULL,
  `createdBy` CHAR(24) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_partnerId` (`partnerId`),
  KEY `idx_status` (`status`),
  KEY `idx_name` (`name`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `partnertransactions` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `partnerId` VARCHAR(191) NULL,
  `type` VARCHAR(191) NULL,
  `amount` DOUBLE NULL,
  `direction` VARCHAR(191) NULL,
  `description` TEXT NULL,
  `reference` VARCHAR(191) NULL,
  `category` VARCHAR(191) NULL,
  `currency` VARCHAR(191) NULL,
  `status` VARCHAR(191) NULL,
  `transactionDate` DATETIME(3) NULL,
  `withdrawalMethod` VARCHAR(191) NULL,
  `receiptPhoto` TEXT NULL,
  `profitPeriod` TEXT NULL,
  `ownershipSnapshot` DOUBLE NULL,
  `netProfitBase` DOUBLE NULL,
  `calculatedProfit` DOUBLE NULL,
  `balanceAfter` DOUBLE NULL,
  `createdBy` CHAR(24) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_partnerId` (`partnerId`),
  KEY `idx_type` (`type`),
  KEY `idx_reference` (`reference`),
  KEY `idx_status` (`status`),
  KEY `idx_transactionDate` (`transactionDate`),
  KEY `idx_partnerId_transactionDate_createdAt` (`partnerId`, `transactionDate`, `createdAt`),
  KEY `idx_type_status` (`type`, `status`),
  KEY `idx_reference_partnerId` (`reference`, `partnerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payable_records` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `recordNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `accountId` CHAR(24) NULL,
  `direction` VARCHAR(191) NULL,
  `principalAmount` DOUBLE NULL,
  `date` DATETIME(3) NULL,
  `note` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_recordNumber` (`recordNumber`),
  KEY `idx_accountId` (`accountId`),
  KEY `idx_status` (`status`),
  KEY `idx_accountId_status` (`accountId`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payablepayments` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `accountPayableId` CHAR(24) NULL,
  `payableRecordId` CHAR(24) NULL,
  `paymentDate` DATETIME(3) NULL,
  `amount` DOUBLE NULL,
  `paymentMethod` VARCHAR(191) NULL,
  `reference` TEXT NULL,
  `note` TEXT NULL,
  `photo` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_accountPayableId` (`accountPayableId`),
  KEY `idx_payableRecordId` (`payableRecordId`),
  KEY `idx_accountPayableId_paymentDate` (`accountPayableId`, `paymentDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `payrolls` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `employeeId` CHAR(24) NULL,
  `userId` CHAR(24) NULL,
  `amount` DOUBLE NULL,
  `date` DATETIME(3) NULL,
  `method` VARCHAR(191) NULL,
  `note` TEXT NULL,
  `status` VARCHAR(191) NULL,
  `deductFromNetProfit` TINYINT(1) NULL,
  `currency` TEXT NULL,
  `createdBy` CHAR(24) NULL,
  `paymentType` VARCHAR(191) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_employeeId` (`employeeId`),
  KEY `idx_userId` (`userId`),
  KEY `idx_date` (`date`),
  KEY `idx_method` (`method`),
  KEY `idx_status` (`status`),
  KEY `idx_deductFromNetProfit` (`deductFromNetProfit`),
  KEY `idx_paymentType` (`paymentType`),
  KEY `idx_userId_date` (`userId`, `date`),
  KEY `idx_employeeId_date` (`employeeId`, `date`),
  KEY `idx_status_date` (`status`, `date`),
  KEY `idx_deductFromNetProfit_status_date` (`deductFromNetProfit`, `status`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `printer_configs` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `key` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `enabled` TINYINT(1) NULL,
  `name` TEXT NULL,
  `connectionType` VARCHAR(191) NULL,
  `host` TEXT NULL,
  `port` DOUBLE NULL,
  `vendorId` TEXT NULL,
  `productId` TEXT NULL,
  `usbDevicePath` TEXT NULL,
  `serialPath` TEXT NULL,
  `baudRate` DOUBLE NULL,
  `paperWidth` VARCHAR(191) NULL,
  `copies` DOUBLE NULL,
  `encoding` TEXT NULL,
  `autoCut` TINYINT(1) NULL,
  `openCashDrawer` TINYINT(1) NULL,
  `autoPrintAfterSale` TINYINT(1) NULL,
  `showPrintPreview` TINYINT(1) NULL,
  `library` TEXT NULL,
  `cupsQueueName` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `productbatches` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `productId` CHAR(24) NULL,
  `batchNumber` TEXT NULL,
  `expiryDate` DATETIME(3) NULL,
  `receivedDate` DATETIME(3) NULL,
  `qtyReceived` DOUBLE NULL,
  `qtyAvailable` DOUBLE NULL,
  `costPrice` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `decimalPlaces` DOUBLE NULL,
  `sellingPrice` DOUBLE NULL,
  `supplierRef` TEXT NULL,
  `note` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_productId` (`productId`),
  KEY `idx_expiryDate` (`expiryDate`),
  KEY `idx_receivedDate` (`receivedDate`),
  KEY `idx_qtyAvailable` (`qtyAvailable`),
  KEY `idx_currency` (`currency`),
  KEY `idx_productId_receivedDate_qtyAvailable` (`productId`, `receivedDate`, `qtyAvailable`),
  KEY `idx_productId_batchNumber` (`productId`, `batchNumber`(100))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `productName` TEXT NULL,
  `brand` TEXT NULL,
  `modelNumber` TEXT NULL,
  `serialNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `requiresColdChain` TINYINT(1) NULL,
  `activeIngredient` TEXT NULL,
  `manufacturer` TEXT NULL,
  `vendor` TEXT NULL,
  `vendorId` CHAR(24) NULL,
  `routeOfAdministration` TEXT NULL,
  `productionBatch` TEXT NULL,
  `genericName` TEXT NULL,
  `strength` TEXT NULL,
  `dosageForm` TEXT NULL,
  `packSize` TEXT NULL,
  `registrationNo` TEXT NULL,
  `requiresPrescription` TINYINT(1) NULL,
  `unitOfMeasure` TEXT NULL,
  `warrantyExpiry` DATETIME(3) NULL,
  `sku` TEXT NULL,
  `barcode` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `stock` DOUBLE NULL,
  `costPrice` DOUBLE NULL,
  `averageCostPrice` DOUBLE NULL,
  `sellingPrice` DOUBLE NULL,
  `averageSellingPrice` DOUBLE NULL,
  `category` CHAR(24) NULL,
  `description` TEXT NULL,
  `image` TEXT NULL,
  `status` TINYINT(1) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_serialNumber` (`serialNumber`),
  KEY `idx_productName` (`productName`(100)),
  KEY `idx_category` (`category`),
  KEY `idx_status` (`status`),
  KEY `idx_brand` (`brand`(100)),
  KEY `idx_warrantyExpiry` (`warrantyExpiry`),
  UNIQUE KEY `uniq_barcode` (`barcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `productserials` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `productId` CHAR(24) NULL,
  `serialNumber` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_productId_serialNumber` (`productId`, `serialNumber`),
  KEY `idx_productId_createdAt` (`productId`, `createdAt`),
  KEY `idx_serialNumber` (`serialNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `services` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  `description` TEXT NULL,
  `specifications` TEXT NULL,
  `price` DOUBLE NULL,
  `currency` VARCHAR(191) NULL,
  `decimalPlaces` DOUBLE NULL,
  `photo` TEXT NULL,
  `status` TINYINT(1) NULL,
  `createdBy` CHAR(24) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_status` (`status`),
  KEY `idx_createdAt` (`createdAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `userdetails` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `userId` CHAR(24) NULL,
  `phone` TEXT NULL,
  `address` TEXT NULL,
  `afghanistanIdCardNumber` VARCHAR(191) NULL,
  `idCardScanUrl` TEXT NULL,
  `notes` TEXT NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_userId` (`userId`),
  KEY `idx_afghanistanIdCardNumber` (`afghanistanIdCardNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `_seq` BIGINT NOT NULL AUTO_INCREMENT,
  `_id` CHAR(24) NOT NULL,
  `doc` JSON NOT NULL,
  `name` TEXT NULL,
  `email` VARCHAR(191) COLLATE utf8mb4_bin NULL,
  `password` TEXT NULL,
  `role` VARCHAR(191) NULL,
  `phone` TEXT NULL,
  `avatarStoredName` TEXT NULL,
  `isOnline` TINYINT(1) NULL,
  `passwordChangedAt` DATETIME(3) NULL,
  `createdAt` DATETIME(3) NULL,
  `updatedAt` DATETIME(3) NULL,
  PRIMARY KEY (`_id`),
  UNIQUE KEY `uniq__seq` (`_seq`),
  UNIQUE KEY `uniq_email` (`email`),
  KEY `idx_name` (`name`(100)),
  KEY `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;