-- Migration: Create feedback and patrols tables
-- Date: 2024
-- Description: Adds legacy feedback table and patrols table

-- =====================================================
-- FEEDBACK TABLE
-- =====================================================

DROP TABLE IF EXISTS `feedback`;

CREATE TABLE IF NOT EXISTS `feedback` (
  `FeedID` int(11) NOT NULL AUTO_INCREMENT,
  `Client` int(11) DEFAULT NULL COMMENT 'User ID of the client submitting feedback',
  `Date` date DEFAULT NULL COMMENT 'Date of feedback',
  `Month` varchar(2) DEFAULT NULL COMMENT 'Month (01-12)',
  `Year` varchar(4) DEFAULT NULL COMMENT 'Year (YYYY)',
  `Subject` varchar(255) DEFAULT NULL COMMENT 'Feedback subject/title',
  
  -- Guard Criteria (8 fields)
  `Punctuality` int(1) DEFAULT 3 COMMENT 'Guard punctuality score (1-5)',
  `Attendance` int(1) DEFAULT 3 COMMENT 'Guard attendance score (1-5)',
  `Neatness` int(1) DEFAULT 3 COMMENT 'Guard neatness score (1-5)',
  `Patrolling` int(1) DEFAULT 3 COMMENT 'Guard patrolling score (1-5)',
  `Communication` int(1) DEFAULT 3 COMMENT 'Guard communication score (1-5)',
  `Respect` int(1) DEFAULT 3 COMMENT 'Guard respect score (1-5)',
  `Job_Mastery` int(1) DEFAULT 3 COMMENT 'Guard job mastery score (1-5)',
  `Vigilance` int(1) DEFAULT 3 COMMENT 'Guard vigilance score (1-5)',
  
  -- Controller Criteria (3 fields)
  `Frequency` int(1) DEFAULT 3 COMMENT 'Controller frequency score (1-5)',
  `Response` int(1) DEFAULT 3 COMMENT 'Controller response score (1-5)',
  `Collaboration` int(1) DEFAULT 3 COMMENT 'Controller collaboration score (1-5)',
  
  -- Management Criteria (4 fields)
  `Service_Delivery` int(1) DEFAULT 3 COMMENT 'Service delivery score (1-5)',
  `Timeliness` int(1) DEFAULT 3 COMMENT 'Timeliness score (1-5)',
  `Innovation` int(1) DEFAULT 3 COMMENT 'Innovation score (1-5)',
  `Documentation` int(1) DEFAULT 3 COMMENT 'Documentation score (1-5)',
  
  -- Additional
  `Equipping` int(1) DEFAULT 3 COMMENT 'Equipping score (1-5)',
  
  -- Calculated Scores
  `Guard_Score` decimal(3,2) DEFAULT NULL COMMENT 'Average of guard criteria',
  `Controller_Score` decimal(3,2) DEFAULT NULL COMMENT 'Average of controller criteria',
  `Management_Score` decimal(3,2) DEFAULT NULL COMMENT 'Average of management criteria',
  `Overall_Score` decimal(3,2) DEFAULT NULL COMMENT 'Weighted overall score',
  
  -- Notes
  `Remarks` text DEFAULT NULL COMMENT 'Additional remarks or notes',
  
  PRIMARY KEY (`FeedID`),
  KEY `Client` (`Client`),
  KEY `Date` (`Date`),
  KEY `Year` (`Year`),
  KEY `Month` (`Month`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- =====================================================
-- PATROLS TABLE
-- =====================================================

DROP TABLE IF EXISTS `patrols`;

CREATE TABLE IF NOT EXISTS `patrols` (
  `PatID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Patrol ID',
  `Post` varchar(50) DEFAULT NULL COMMENT 'Post/Facility name',
  `PatrolPoint` varchar(50) DEFAULT NULL COMMENT 'Patrol point/location',
  `PatrolTime` datetime DEFAULT NULL COMMENT 'Patrol timestamp',
  `Status` varchar(500) DEFAULT NULL COMMENT 'Patrol status',
  `Guard` varchar(50) DEFAULT NULL COMMENT 'Guard name or ID',
  `Location` varchar(50) DEFAULT NULL COMMENT 'Detailed location',
  `Validation` varchar(100) DEFAULT NULL COMMENT 'Validation status',
  PRIMARY KEY (`PatID`),
  KEY `Guard` (`Guard`),
  KEY `Post` (`Post`),
  KEY `PatrolTime` (`PatrolTime`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

-- =====================================================
-- END MIGRATION
-- =====================================================
