-- Patient Photos Table
-- This table stores patient photos with before/after comparison functionality
-- Supports image compression - stores both original and compressed versions

CREATE TABLE IF NOT EXISTS `patient_photos` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `PatientId` int(11) NOT NULL,
  `CentreId` int(11) NOT NULL,
  `Area` varchar(100) NOT NULL COMMENT 'Body area (e.g., face-cheeks, scalp, arms-upper)',
  `PhotoType` enum('before','after') NOT NULL COMMENT 'Before or After photo',
  `FileName` varchar(255) NOT NULL,
  `Path` varchar(500) NOT NULL COMMENT 'Path to original image',
  `compressPath` varchar(500) DEFAULT NULL COMMENT 'Path to compressed image',
  `IsCompressImageAvailable` tinyint(1) DEFAULT 0 COMMENT '1 if compressed image exists, 0 otherwise',
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UpdatedAt` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `IsDeleted` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`Id`),
  KEY `idx_patient` (`PatientId`),
  KEY `idx_centre` (`CentreId`),
  KEY `idx_area_type` (`Area`,`PhotoType`),
  KEY `idx_created` (`CreatedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Patient photos for before/after comparison';

