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

START TRANSACTION;



--
-- Database: `orthorecoverai`
--

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

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

CREATE TABLE `exercises` (
  `id` int(11) NOT NULL,
  `plan_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `video_url` varchar(500) DEFAULT NULL,
  `repetitions` varchar(50) DEFAULT NULL,
  `instructions` text DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `exercise_logs`
--

CREATE TABLE `exercise_logs` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `exercise_id` int(11) NOT NULL,
  `completed` tinyint(1) NOT NULL DEFAULT 0,
  `completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `exercise_plans`
--

CREATE TABLE `exercise_plans` (
  `id` int(11) NOT NULL,
  `physiotherapist_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT '',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `patients`
--

CREATE TABLE `patients` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `surgery_type` varchar(255) DEFAULT '',
  `physiotherapist_id` int(11) DEFAULT NULL,
  `connection_code` varchar(10) DEFAULT NULL,
  `connected` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `patients`
--

INSERT INTO `patients` (`id`, `user_id`, `age`, `gender`, `surgery_type`, `physiotherapist_id`, `connection_code`, `connected`, `created_at`) VALUES
(1, 1, 21, 'Male', '', NULL, '73XI64KD', 0, '2026-04-13 10:34:21'),
(2, 2, 22, 'Male', 'Rotator Cuff Repair', NULL, 'GWTOL1YH', 0, '2026-04-16 07:08:05'),
(3, 4, 22, 'Male', 'Shoulder Arthroscopy', NULL, '3YVOJL7H', 0, '2026-04-16 08:09:55');

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

--
-- Table structure for table `physiotherapists`
--

CREATE TABLE `physiotherapists` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `qualification` varchar(255) DEFAULT '',
  `specialization` varchar(255) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `physiotherapists`
--

INSERT INTO `physiotherapists` (`id`, `user_id`, `qualification`, `specialization`, `created_at`) VALUES
(1, 3, 'BPT, MPT', 'Orthopedics', '2026-04-16 07:34:23');

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

--
-- Table structure for table `physiotherapist_notes`
--

CREATE TABLE `physiotherapist_notes` (
  `id` int(11) NOT NULL,
  `physiotherapist_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `note` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `rom_sessions`
--

CREATE TABLE `rom_sessions` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `joint_name` varchar(100) NOT NULL,
  `angle_value` float NOT NULL,
  `session_time` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `rom_sessions`
--

INSERT INTO `rom_sessions` (`id`, `patient_id`, `joint_name`, `angle_value`, `session_time`) VALUES
(1, 2, 'Exercise Detail', 0, '2026-04-16 07:12:02'),
(2, 2, 'Exercise Detail', 0, '2026-04-16 07:12:04'),
(3, 2, 'Exercise Detail', 0, '2026-04-16 07:12:04'),
(4, 2, 'Exercise Detail', 58, '2026-04-16 07:12:08'),
(5, 2, 'Exercise Detail', 58, '2026-04-16 07:12:11'),
(6, 2, 'Exercise Detail', 58, '2026-04-16 07:12:12'),
(7, 2, 'Exercise Detail', 0, '2026-04-16 07:39:05'),
(8, 2, 'Exercise Detail', 0, '2026-04-16 07:39:06'),
(9, 2, 'Exercise Detail', 0, '2026-04-16 07:39:07'),
(10, 2, 'Exercise Detail', 0, '2026-04-16 07:39:07'),
(11, 2, 'Exercise Detail', 0, '2026-04-16 07:39:08'),
(12, 2, 'Exercise Detail', 0, '2026-04-16 07:39:09');

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

--
-- Table structure for table `swelling_uploads`
--

CREATE TABLE `swelling_uploads` (
  `id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `image_path` varchar(500) NOT NULL,
  `physiotherapist_note` text DEFAULT NULL,
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `exercise_logs`
--
ALTER TABLE `exercise_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_log_patient` (`patient_id`),
  ADD KEY `fk_log_exercise` (`exercise_id`);

--
-- Indexes for table `exercise_plans`
--
ALTER TABLE `exercise_plans`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_plan_physio` (`physiotherapist_id`),
  ADD KEY `fk_plan_patient` (`patient_id`);

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

--
-- Indexes for table `patients`
--
ALTER TABLE `patients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_id` (`user_id`),
  ADD KEY `fk_patient_user` (`user_id`),
  ADD KEY `fk_patient_physio` (`physiotherapist_id`);

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

--
-- Indexes for table `physiotherapist_notes`
--
ALTER TABLE `physiotherapist_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_note_physio` (`physiotherapist_id`),
  ADD KEY `fk_note_patient` (`patient_id`);

--
-- Indexes for table `rom_sessions`
--
ALTER TABLE `rom_sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_rom_patient` (`patient_id`);

--
-- Indexes for table `swelling_uploads`
--
ALTER TABLE `swelling_uploads`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_swelling_patient` (`patient_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `exercises`
--
ALTER TABLE `exercises`
  ADD CONSTRAINT `fk_exercise_plan` FOREIGN KEY (`plan_id`) REFERENCES `exercise_plans` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `exercise_logs`
--
ALTER TABLE `exercise_logs`
  ADD CONSTRAINT `fk_log_exercise` FOREIGN KEY (`exercise_id`) REFERENCES `exercises` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_log_patient` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `exercise_plans`
--
ALTER TABLE `exercise_plans`
  ADD CONSTRAINT `fk_plan_patient` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_plan_physio` FOREIGN KEY (`physiotherapist_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `patients`
--
ALTER TABLE `patients`
  ADD CONSTRAINT `fk_patient_physio` FOREIGN KEY (`physiotherapist_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_patient_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `physiotherapist_notes`
--
ALTER TABLE `physiotherapist_notes`
  ADD CONSTRAINT `fk_note_patient` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_note_physio` FOREIGN KEY (`physiotherapist_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `rom_sessions`
--
ALTER TABLE `rom_sessions`
  ADD CONSTRAINT `fk_rom_patient` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `swelling_uploads`
--
ALTER TABLE `swelling_uploads`
  ADD CONSTRAINT `fk_swelling_patient` FOREIGN KEY (`patient_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

