-- Migration to create symptoms table
-- Execute this SQL script to create the symptoms table and populate it with data

CREATE TABLE IF NOT EXISTS `tbl_symptoms` (
    `symptoms_id` int(11) NOT NULL AUTO_INCREMENT,
    `concern_id` int(11) NOT NULL,
    `symptoms_name` varchar(255) NOT NULL,
    `description` text DEFAULT NULL,
    `is_active` tinyint(1) DEFAULT 1,
    `created_date` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_date` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`symptoms_id`),
    KEY `idx_symptoms_concern` (`concern_id`),
    KEY `idx_symptoms_active` (`is_active`),
    KEY `idx_symptoms_name` (`symptoms_name`),
    CONSTRAINT `fk_symptoms_concern` FOREIGN KEY (`concern_id`) REFERENCES `tbl_concern` (`concern_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample symptoms data
INSERT INTO `tbl_symptoms` (`concern_id`, `symptoms_name`, `description`) VALUES
-- Food Intake (concern_id: 1)
(1, 'Not eating at all', 'Complete loss of appetite, refusing all food'),
(1, 'Eating less than usual', 'Reduced food intake compared to normal portion sizes'),
(1, 'Eating too much', 'Excessive food consumption, overeating'),
(1, 'Vomiting after eating', 'Regurgitation or vomiting shortly after meals'),
(1, 'Difficulty swallowing food', 'Problems with swallowing, choking while eating'),
(1, 'Picky eating behavior', 'Selective eating, refusing certain foods'),
(1, 'Rapid eating', 'Eating too fast, gulping food'),

-- Water Consumption (concern_id: 2)
(2, 'Not drinking water', 'Refusing to drink water or reduced water intake'),
(2, 'Excessive thirst', 'Drinking much more water than usual'),
(2, 'Frequent urination', 'Increased frequency of urination'),
(2, 'Dark colored urine', 'Urine appears darker than normal'),
(2, 'Dry mouth and gums', 'Signs of dehydration in mouth area'),

-- Behavioral Changes (concern_id: 3)
(3, 'Aggressive behavior', 'Unusual aggression towards people or other pets'),
(3, 'Excessive hiding', 'Withdrawing, hiding more than usual'),
(3, 'Restlessness', 'Unable to settle, constantly moving around'),
(3, 'Loss of interest in activities', 'No longer interested in toys, play, or walks'),
(3, 'Excessive vocalization', 'Unusual barking, meowing, or other vocalizations'),
(3, 'Destructive behavior', 'Chewing, scratching, or destroying household items'),

-- Physical Activity (concern_id: 4)
(4, 'Limping or favoring a leg', 'Difficulty walking, favoring one leg over others'),
(4, 'Reluctance to move', 'Unwillingness to walk, jump, or be active'),
(4, 'Excessive panting during activity', 'Heavy breathing during light exercise'),
(4, 'Stiffness after rest', 'Difficulty moving after lying down or sleeping'),
(4, 'Loss of coordination', 'Unsteady movement, difficulty with balance'),

-- Digestive Issues (concern_id: 5)
(5, 'Diarrhea', 'Loose or watery stools'),
(5, 'Constipation', 'Difficulty passing stools or infrequent bowel movements'),
(5, 'Blood in stool', 'Visible blood in feces'),
(5, 'Excessive gas', 'Increased flatulence'),
(5, 'Abdominal pain', 'Signs of stomach discomfort, hunched posture'),
(5, 'Bloated stomach', 'Swollen or distended abdomen'),

-- Respiratory Problems (concern_id: 6)
(6, 'Difficulty breathing', 'Labored breathing, shortness of breath'),
(6, 'Persistent coughing', 'Ongoing cough that does not resolve'),
(6, 'Wheezing sounds', 'Audible wheezing when breathing'),
(6, 'Nasal discharge', 'Runny nose or unusual nasal secretions'),
(6, 'Open mouth breathing', 'Breathing through mouth instead of nose'),

-- Skin & Coat Issues (concern_id: 7)
(7, 'Excessive scratching', 'Constant itching and scratching'),
(7, 'Hair loss', 'Patches of missing fur or thinning coat'),
(7, 'Red or irritated skin', 'Inflamed, red, or irritated skin areas'),
(7, 'Dry or flaky skin', 'Dandruff-like flakes, dry skin'),
(7, 'Hot spots', 'Localized areas of inflamed, moist skin'),
(7, 'Unusual odor', 'Strong or unusual smell from skin or coat'),

-- Sleep Patterns (concern_id: 8)
(8, 'Difficulty falling asleep', 'Takes longer than usual to fall asleep'),
(8, 'Frequent waking during sleep', 'Waking up multiple times during rest periods'),
(8, 'Sleeping more than usual', 'Excessive sleeping, lethargy'),
(8, 'Restless sleep', 'Tossing, turning, or uncomfortable positioning'),
(8, 'Night time activity', 'Being active during normal sleeping hours')
ON DUPLICATE KEY UPDATE 
`updated_date` = CURRENT_TIMESTAMP;
