-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `flexknee_ai`
--

-- --------------------------------------------------------

--
-- Table structure for table `exercises`
--

CREATE TABLE `exercises` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `difficulty` varchar(20) NOT NULL,
  `duration_seconds` int(11) NOT NULL,
  `target_reps` int(11) NOT NULL,
  `sets` int(11) NOT NULL,
  `video_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `exercises`
--

INSERT INTO `exercises` (`id`, `name`, `difficulty`, `duration_seconds`, `target_reps`, `sets`, `video_url`, `created_at`) VALUES
(1, 'Heel Slides', 'Easy', 120, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/Heel%20slides.mp4', '2026-02-21 19:13:26'),
(2, 'Quad Sets', 'Easy', 120, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/Quad%20Sets.mp4', '2026-02-21 19:13:26'),
(3, 'Straight Leg Raises', 'Medium', 180, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/Straight%20leg%20raises.mp4', '2026-02-21 19:13:26'),
(4, 'Ankle Pumps', 'Easy', 120, 15, 3, 'http://localhost/flexknee_ai/backend_php/videos/Ankle%20pumps.mp4', '2026-02-21 19:50:13'),
(5, 'Seated Knee Extension', 'Easy', 180, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/seated%20knee%20extension.mp4', '2026-02-21 19:50:14'),
(6, 'Standing Hamstring Curls', 'Medium', 180, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/harmstring%20curls.mp4', '2026-02-21 19:50:14'),
(7, 'Mini Squats (Supported)', 'Medium', 240, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/Mini%20squats.mp4', '2026-02-21 19:50:14'),
(8, 'Step-Ups', 'Hard', 300, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/step-ups.mp4', '2026-02-21 19:50:14'),
(9, 'Heel Raises', 'Easy', 120, 15, 3, 'http://localhost/flexknee_ai/backend_php/videos/heel%20raises.mp4', '2026-02-21 19:50:14'),
(11, 'Single Leg Balance', 'Medium', 300, 10, 3, 'http://localhost/flexknee_ai/backend_php/videos/Single%20leg%20balance.mp4', '2026-02-21 20:26:35'),
(12, 'Walking', 'Medium', 600, 1, 1, 'http://localhost/flexknee_ai/backend_php/videos/Walking.mp4', '2026-02-21 20:26:35'),
(13, 'Sit and Stand Exercise', 'Medium', 300, 12, 3, 'http://localhost/flexknee_ai/backend_php/videos/sit%20and%20stand%20exercise.mp4', '2026-02-21 20:26:35');

-- --------------------------------------------------------

--
-- Table structure for table `exercise_instructions`
--

CREATE TABLE `exercise_instructions` (
  `id` int(11) NOT NULL,
  `exercise_id` int(11) NOT NULL,
  `step_number` int(11) NOT NULL,
  `instruction_text` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `exercise_instructions`
--

INSERT INTO `exercise_instructions` (`id`, `exercise_id`, `step_number`, `instruction_text`) VALUES
(1, 1, 1, 'Lie on your back with legs straight.'),
(2, 1, 2, 'Slowly slide your heel towards your buttocks.'),
(3, 1, 3, 'Hold for 5 seconds.'),
(4, 1, 4, 'Slide heel back down.'),
(5, 2, 1, 'Lie on your back with legs straight.'),
(6, 2, 2, 'Tighten your thigh muscle pushing knee down.'),
(7, 2, 3, 'Hold for 5 seconds.'),
(8, 2, 4, 'Relax.'),
(9, 3, 1, 'Lie on your back, one leg bent, one straight.'),
(10, 3, 2, 'Tighten thigh of straight leg and lift to height of bent knee.'),
(11, 3, 3, 'Hold for 3 seconds.'),
(12, 3, 4, 'Slowly lower.'),
(13, 4, 1, 'Lie on your back with legs straight.'),
(14, 4, 2, 'Point your toes away from you.'),
(15, 4, 3, 'Pull your toes toward your body.'),
(16, 4, 4, 'Repeat slowly 15-20 times.'),
(17, 5, 1, 'Sit on a chair with feet flat on the floor.'),
(18, 5, 2, 'Slowly straighten your operated leg.'),
(19, 5, 3, 'Lift until the knee is fully straight.'),
(20, 5, 4, 'Hold for 5 seconds, then lower slowly.'),
(21, 6, 1, 'Stand upright holding a chair for support.'),
(22, 6, 2, 'Slowly bend your knee backward.'),
(23, 6, 3, 'Lift your heel toward your buttocks.'),
(24, 6, 4, 'Lower slowly to the starting position.'),
(25, 7, 1, 'Stand holding a stable surface for support.'),
(26, 7, 2, 'Keep feet shoulder-width apart.'),
(27, 7, 3, 'Bend knees slightly (do not squat deeply).'),
(28, 7, 4, 'Slowly return to standing position.'),
(29, 8, 1, 'Stand in front of a low step.'),
(30, 8, 2, 'Step up with the operated leg first.'),
(31, 8, 3, 'Bring the other leg up to stand fully.'),
(32, 8, 4, 'Step down slowly and repeat.'),
(33, 9, 1, 'Stand holding a chair for balance.'),
(34, 9, 2, 'Slowly rise onto your toes.'),
(35, 9, 3, 'Keep knees straight while lifting.'),
(36, 9, 4, 'Lower heels slowly back down.'),
(41, 11, 1, 'Stand near a sturdy chair or wall for support.'),
(42, 11, 2, 'Lift one foot off the ground, balancing on the other leg.'),
(43, 11, 3, 'Keep your standing knee slightly bent (not locked).'),
(44, 11, 4, 'Hold for 30 seconds, then switch legs.'),
(45, 11, 5, 'Repeat 10 times for each leg.'),
(46, 12, 1, 'Wear comfortable, supportive walking shoes.'),
(47, 12, 2, 'Walk at a steady, comfortable pace on a flat surface.'),
(48, 12, 3, 'Maintain upright posture and look ahead.'),
(49, 12, 4, 'Continue for 10 minutes or as directed by your PT.'),
(50, 12, 5, 'If you feel sharp pain, stop and rest.'),
(51, 13, 1, 'Sit on a sturdy chair with feet flat on the floor.'),
(52, 13, 2, 'Lean forward slightly and stand up using your legs.'),
(53, 13, 3, 'Try not to use your hands for support if possible.'),
(54, 13, 4, 'Slowly sit back down with control.'),
(55, 13, 5, 'Complete 12 repetitions.');

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `sender_id` varchar(20) NOT NULL,
  `receiver_id` varchar(20) NOT NULL,
  `message_text` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `performance_records`
--

CREATE TABLE `performance_records` (
  `id` int(11) NOT NULL,
  `user_id` varchar(20) NOT NULL,
  `record_date` date NOT NULL,
  `rom` int(11) NOT NULL,
  `pain_level` int(11) NOT NULL,
  `swelling` varchar(20) DEFAULT NULL,
  `gait` varchar(50) DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `performance_records`
--

INSERT INTO `performance_records` (`id`, `user_id`, `record_date`, `rom`, `pain_level`, `swelling`, `gait`, `color`, `created_at`) VALUES
(1, 'PT-6883', '2026-01-20', 90, 1, 'Low', 'Normal', '#22C55E', '2026-01-28 09:05:33'),
(2, 'PT-6883', '2026-01-22', 88, 1, 'Medium', 'Mild Limp', '#F59E0B', '2026-01-28 09:05:33'),
(3, 'PT-6883', '2026-01-24', 74, 3, 'Medium', 'Mild Limp', '#F59E0B', '2026-01-28 09:05:33'),
(4, 'PT-6883', '2026-01-26', 66, 7, 'High', 'Severe Limp', '#EF4444', '2026-01-28 09:05:33'),
(5, 'PT-6883', '2026-01-28', 59, 8, 'High', 'Severe Limp', '#EF4444', '2026-01-28 09:05:33'),
(6, 'PT-5773', '2026-01-23', 106, 0, 'Low', 'Normal', '#22C55E', '2026-01-31 12:07:18'),
(7, 'PT-5773', '2026-01-25', 99, 1, 'Medium', 'Mild Limp', '#F59E0B', '2026-01-31 12:07:18'),
(8, 'PT-5773', '2026-01-27', 90, 3, 'Medium', 'Mild Limp', '#F59E0B', '2026-01-31 12:07:18'),
(9, 'PT-5773', '2026-01-29', 75, 7, 'High', 'Severe Limp', '#EF4444', '2026-01-31 12:07:18'),
(10, 'PT-5773', '2026-01-31', 66, 8, 'High', 'Severe Limp', '#EF4444', '2026-01-31 12:07:18'),
(16, 'PT-6194', '2026-02-07', 102, 0, 'Low', 'Normal', '#22C55E', '2026-02-15 12:36:06'),
(17, 'PT-6194', '2026-02-09', 95, 2, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-15 12:36:06'),
(18, 'PT-6194', '2026-02-11', 84, 5, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-15 12:36:06'),
(19, 'PT-6194', '2026-02-13', 74, 6, 'High', 'Severe Limp', '#EF4444', '2026-02-15 12:36:06'),
(20, 'PT-6194', '2026-02-15', 60, 7, 'High', 'Severe Limp', '#EF4444', '2026-02-15 12:36:06'),
(21, 'PT-3796', '2026-02-13', 100, 0, 'Low', 'Normal', '#22C55E', '2026-02-21 17:24:47'),
(22, 'PT-3796', '2026-02-15', 100, 3, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-21 17:24:47'),
(23, 'PT-3796', '2026-02-17', 81, 4, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-21 17:24:47'),
(24, 'PT-3796', '2026-02-19', 73, 7, 'High', 'Severe Limp', '#EF4444', '2026-02-21 17:24:47'),
(25, 'PT-3796', '2026-02-21', 60, 7, 'High', 'Severe Limp', '#EF4444', '2026-02-21 17:24:47'),
(26, 'PT-9045', '2026-02-13', 94, 1, 'Low', 'Normal', '#22C55E', '2026-02-21 19:02:08'),
(27, 'PT-9045', '2026-02-15', 94, 2, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-21 19:02:08'),
(28, 'PT-9045', '2026-02-17', 75, 5, 'Medium', 'Mild Limp', '#F59E0B', '2026-02-21 19:02:08'),
(29, 'PT-9045', '2026-02-19', 71, 6, 'High', 'Severe Limp', '#EF4444', '2026-02-21 19:02:08'),
(30, 'PT-9045', '2026-02-21', 64, 7, 'High', 'Severe Limp', '#EF4444', '2026-02-21 19:02:08'),
(31, 'PT-5113', '2026-03-30', 91, 0, 'Low', 'Normal', '#22C55E', '2026-04-07 18:00:31'),
(32, 'PT-5113', '2026-04-01', 88, 1, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-07 18:00:31'),
(33, 'PT-5113', '2026-04-03', 73, 5, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-07 18:00:31'),
(34, 'PT-5113', '2026-04-05', 67, 5, 'High', 'Severe Limp', '#EF4444', '2026-04-07 18:00:31'),
(35, 'PT-5113', '2026-04-07', 58, 7, 'High', 'Severe Limp', '#EF4444', '2026-04-07 18:00:31'),
(36, 'PT-6438', '2026-03-31', 98, 1, 'Low', 'Normal', '#22C55E', '2026-04-08 03:15:37'),
(37, 'PT-6438', '2026-04-02', 94, 1, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-08 03:15:37'),
(38, 'PT-6438', '2026-04-04', 80, 4, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-08 03:15:37'),
(39, 'PT-6438', '2026-04-06', 75, 6, 'High', 'Severe Limp', '#EF4444', '2026-04-08 03:15:37'),
(40, 'PT-6438', '2026-04-08', 59, 8, 'High', 'Severe Limp', '#EF4444', '2026-04-08 03:15:37'),
(41, 'PT-2266', '2026-03-31', 85, 1, 'Low', 'Normal', '#22C55E', '2026-04-08 04:10:16'),
(42, 'PT-2266', '2026-04-02', 80, 3, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-08 04:10:16'),
(43, 'PT-2266', '2026-04-04', 67, 3, 'Medium', 'Mild Limp', '#F59E0B', '2026-04-08 04:10:16'),
(44, 'PT-2266', '2026-04-06', 59, 6, 'High', 'Severe Limp', '#EF4444', '2026-04-08 04:10:16'),
(45, 'PT-2266', '2026-04-08', 47, 8, 'High', 'Severe Limp', '#EF4444', '2026-04-08 04:10:16');

-- --------------------------------------------------------

--
-- Table structure for table `therapist_patients`
--

CREATE TABLE `therapist_patients` (
  `id` int(11) NOT NULL,
  `therapist_id` varchar(20) NOT NULL,
  `patient_id` varchar(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `therapist_patients`
--

INSERT INTO `therapist_patients` (`id`, `therapist_id`, `patient_id`, `created_at`) VALUES
(1, 'DR-9727', 'PT-9045', '2026-02-21 19:32:57'),
(3, 'DR-9727', 'PT-3796', '2026-02-21 20:45:19'),
(8, 'DR-9727', 'PT-2266', '2026-04-08 04:58:27');

-- --------------------------------------------------------

--
-- Table structure for table `user_security_answers`
--

CREATE TABLE `user_security_answers` (
  `user_id` varchar(20) NOT NULL,
  `question1` varchar(255) NOT NULL,
  `answer1` varchar(255) NOT NULL,
  `question2` varchar(255) NOT NULL,
  `answer2` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `user_security_answers`
--

INSERT INTO `user_security_answers` (`user_id`, `question1`, `answer1`, `question2`, `answer2`) VALUES
('PT-2266', 'What was your childhood nickname?', 'pet', 'What is the name of your favorite childhood friend?', 'pet'),
('PT-5113', 'What was your childhood nickname?', 'babu', 'What is the name of your favorite childhood friend?', 'vamsi'),
('PT-6438', 'What was your childhood nickname?', 'kittu', 'What is the name of your favorite childhood friend?', 'bittu');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `exercises`
--
ALTER TABLE `exercises`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `exercise_instructions`
--
ALTER TABLE `exercise_instructions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `exercise_id` (`exercise_id`);

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender_id` (`sender_id`),
  ADD KEY `receiver_id` (`receiver_id`);

--
-- Indexes for table `performance_records`
--
ALTER TABLE `performance_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `therapist_patients`
--
ALTER TABLE `therapist_patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `therapist_id` (`therapist_id`,`patient_id`),
  ADD KEY `patient_id` (`patient_id`);

--
-- Indexes for table `user_security_answers`
--
ALTER TABLE `user_security_answers`
  ADD PRIMARY KEY (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `exercises`
--
ALTER TABLE `exercises`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `exercise_instructions`
--
ALTER TABLE `exercise_instructions`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=56;

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `performance_records`
--
ALTER TABLE `performance_records`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

--
-- AUTO_INCREMENT for table `therapist_patients`
--
ALTER TABLE `therapist_patients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `exercise_instructions`
--
ALTER TABLE `exercise_instructions`
  ADD CONSTRAINT `exercise_instructions_ibfk_1` FOREIGN KEY (`exercise_id`) REFERENCES `exercises` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `performance_records`
--
ALTER TABLE `performance_records`
  ADD CONSTRAINT `performance_records_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `therapist_patients`
--
ALTER TABLE `therapist_patients`
  ADD CONSTRAINT `therapist_patients_ibfk_1` FOREIGN KEY (`therapist_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `therapist_patients_ibfk_2` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `user_security_answers`
--
ALTER TABLE `user_security_answers`
  ADD CONSTRAINT `user_security_answers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

