-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 23, 2026 at 05:07 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `flexifinger`
--

DELIMITER $$

--
-- Procedures
--

CREATE PROCEDURE `sp_get_patient_progress` (IN `p_patient_id` INT)
BEGIN
    SELECT 
        p.patient_id,
        p.full_name,
        p.streak,
        p.compliance_rate,
        p.pain_score AS current_pain,
        COUNT(DISTINCT DATE(det.exercise_date)) AS days_exercised,
        AVG(det.pain_score_after) AS avg_pain_after,
        (SELECT pain_score FROM pain_score_history ph 
         WHERE ph.patient_id = p_patient_id 
         ORDER BY ph.recorded_date DESC LIMIT 1) AS last_pain_score,
        (SELECT pain_score FROM pain_score_history ph 
         WHERE ph.patient_id = p_patient_id 
         AND ph.recorded_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
         ORDER BY ph.recorded_date ASC LIMIT 1) AS pain_score_7days_ago
    FROM patients p
    LEFT JOIN daily_exercise_tracking det ON p.patient_id = det.patient_id
    WHERE p.patient_id = p_patient_id
    GROUP BY p.patient_id;
END$$

CREATE PROCEDURE `sp_get_therapist_patients` (IN `p_therapist_id` INT)
BEGIN
    SELECT 
        p.patient_id,
        p.full_name,
        p.email,
        p.phone,
        p.finger_condition,
        p.pain_score,
        p.streak,
        p.compliance_rate,
        p.daily_status,
        p.status,
        p.program_start_date,
        es.set_name AS exercise_set_name,
        sch.frequency,
        sch.start_date,
        sch.end_date,
        (SELECT COUNT(*) FROM daily_exercise_tracking det 
         WHERE det.patient_id = p.patient_id AND det.status = 'completed') AS total_completed
    FROM patients p
    LEFT JOIN exercise_schedules sch ON p.patient_id = sch.patient_id AND sch.status = 'active'
    LEFT JOIN exercise_sets es ON sch.exercise_set_id = es.set_id
    WHERE p.therapist_id = p_therapist_id
    ORDER BY p.daily_status, p.full_name;
END$$

CREATE PROCEDURE `sp_update_patient_stats` (IN `p_patient_id` INT)
BEGIN
    DECLARE v_compliance DECIMAL(5,2);
    DECLARE v_streak INT;
    
    SELECT 
        COALESCE(
            ROUND(SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) / COUNT(*) * 100, 2),
            0
        ) INTO v_compliance
    FROM daily_exercise_tracking
    WHERE patient_id = p_patient_id 
    AND exercise_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);
    
    SELECT COUNT(*) INTO v_streak
    FROM (
        SELECT exercise_date,
               ROW_NUMBER() OVER (ORDER BY exercise_date DESC) as rn
        FROM daily_exercise_tracking
        WHERE patient_id = p_patient_id 
        AND status = 'completed'
        AND exercise_date <= CURDATE()
        GROUP BY exercise_date
    ) t
    WHERE DATEDIFF(CURDATE(), exercise_date) = rn - 1;
    
    UPDATE patients 
    SET compliance_rate = v_compliance,
        streak = COALESCE(v_streak, 0),
        daily_status = CASE 
            WHEN EXISTS (
                SELECT 1 FROM daily_exercise_tracking 
                WHERE patient_id = p_patient_id 
                AND exercise_date = CURDATE() 
                AND status = 'completed'
            ) THEN 'Completed today'
            WHEN EXISTS (
                SELECT 1 FROM daily_exercise_tracking 
                WHERE patient_id = p_patient_id 
                AND exercise_date = CURDATE() 
                AND status = 'pending'
            ) THEN 'Pending today'
            ELSE 'Missed today'
        END
    WHERE patient_id = p_patient_id;
END$$

DELIMITER ;

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

--
-- Table structure for table `activity_logs`
--

CREATE TABLE `activity_logs` (
  `log_id` int(11) NOT NULL,
  `admin_id` int(11) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `activity_logs` (`log_id`, `admin_id`, `action`, `details`, `ip_address`, `created_at`) VALUES
(1, 1, 'Admin login', 'Admin logged in from IP: ::1', '::1', '2026-02-10 12:36:03'),
(2, 1, 'Admin logout', 'Admin logged out', '::1', '2026-02-10 12:37:23'),
(3, 1, 'Admin login', 'Admin logged in from IP: ::1', '::1', '2026-02-10 12:38:13'),
(4, 1, 'Patient approved', 'Approved patient ID: 1', '::1', '2026-02-10 12:38:45'),
(5, 1, 'Admin logout', 'Admin logged out', '::1', '2026-02-10 12:39:28'),
(6, 1, 'Admin login', 'Admin logged in from IP: ::1', '::1', '2026-02-10 12:43:59'),
(7, 1, 'Therapist approved', 'Approved therapist ID: 1', '::1', '2026-02-10 13:03:35');

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

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `admin_id` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `centre_name` varchar(255) DEFAULT NULL,
  `centre_address` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `admins` (`admin_id`, `full_name`, `email`, `password_hash`, `phone`, `centre_name`, `centre_address`, `created_at`) VALUES
(1, 'System Administrator', 'admin@flexifinger.com', '$2y$10$K.3CbL/V9I8h5Jb5K0xY6e2qY6L7a9B8c0D1e2F3g4H5i6J7k8L9m0N1o2P', '0192791425', 'UMPSA Health Centre', '', '2026-02-10 12:31:43');

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

--
-- Table structure for table `api_tokens`
--

CREATE TABLE `api_tokens` (
  `token_id` int(11) NOT NULL,
  `user_type` enum('patient','therapist','admin') NOT NULL,
  `user_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `refresh_token` varchar(255) DEFAULT NULL,
  `device_info` text DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `last_used` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `daily_exercise_tracking`
--

CREATE TABLE `daily_exercise_tracking` (
  `tracking_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `schedule_id` int(11) DEFAULT NULL,
  `exercise_date` date NOT NULL,
  `status` enum('pending','completed','missed','skipped') DEFAULT 'pending',
  `completed_at` datetime DEFAULT NULL,
  `pain_score_before` int(11) DEFAULT NULL,
  `pain_score_after` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `exercises` (
  `exercise_id` int(11) NOT NULL,
  `exercise_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `instructions` text DEFAULT NULL,
  `difficulty` enum('beginner','intermediate','advanced') DEFAULT 'beginner',
  `reps_default` int(11) DEFAULT 10,
  `sets_default` int(11) DEFAULT 3,
  `hold_duration_sec` int(11) DEFAULT 5,
  `video_url` varchar(500) DEFAULT NULL,
  `thumbnail_path` varchar(500) DEFAULT NULL,
  `video_path` varchar(500) DEFAULT NULL,
  `media_type` enum('local','youtube','vimeo','external') DEFAULT 'local',
  `media_asset_id` int(11) DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `exercises` (`exercise_id`, `exercise_name`, `description`, `instructions`, `difficulty`, `reps_default`, `sets_default`, `hold_duration_sec`, `video_url`, `thumbnail_path`, `video_path`, `media_type`, `media_asset_id`, `duration_seconds`, `created_at`) VALUES
(1, 'Finger Taps', 'This exercise is designed to train each finger to move independently, enhancing coordination and fine motor control.', '1. Sit at a table and rest your forearm on the tabletop.\n2. Move your fingers slowly, one finger at a time, with your hand relaxed, making a wave with your fingers.', 'beginner', 5, 5, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:12:34'),
(2, 'Finger Abduction and Adduction', 'This exercise helps improve finger mobility and strength.', '1. Sit comfortably with your hand resting on a table\n2. Slowly spread your fingers apart as far as comfortable\n3. Hold the position for 3 seconds\n4. Slowly bring your fingers back together\n5. Repeat 5 times', 'beginner', 3, 5, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:14:16'),
(3, 'Knuckle bend', 'Knuckle bending exercises improve flexibility, strengthen muscles, prevent stiffness, enhance hand coordination.', '1. Sit or stand comfortably with your hand resting on a table or in the air\n2. Keep your palm facing up and fingers fully straight\n3. Without moving your wrist, bend all fingers at the knuckle joints\n4. Keep the middle and tip joints straight\n5. Hold the bent position for 1 second\n6. Slowly straighten your fingers back to the starting position\n7. Repeat 5 times', 'beginner', 3, 5, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:15:53'),
(4, 'Thumb and Little Finger Abduction/Adduction', 'The Thumb and Little Finger Abduction-Adduction Exercise strengthens and increases mobility of the thumb and little finger.', '1. Keep your fingers together and wrist straight\n2. Keeping the palm flat, slide your thumb slowly outward\n3. Slide the thumb back so it touches the side of your index finger\n4. With your thumb now in place, slide your little finger slowly outward\n5. Slide the pinky back so it touches the ring finger\n6. Perform 7 cycles in a row', 'beginner', 7, 3, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:17:24'),
(5, 'Middle joint finger flexion with open palm', 'This exercise focuses on bending the fingers at the middle joints while keeping the palm flat.', '1. Sit comfortably at a table with your forearm resting on the surface\n2. Place your hand palm-down on the table, fingers extended and relaxed\n3. Slowly bend each finger at the middle joint\n4. Hold the bend for 1 second, then return the finger to a flat, extended position.\n5. Repeat 5 times', 'beginner', 3, 5, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:18:27'),
(6, 'Thumb Flexion to Each Finger', 'Place your hand in a comfortable position. Bring your thumb towards the tip of the index finger and apply pressure.', '1. Touch the tip of the index finger with your thumb and gently slide down\n2. Touch the tip of the middle finger with your thumb and gently slide down\n3. Continue the same movements with the other fingers\n4. Repeat 10 times', 'beginner', 4, 10, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:19:52'),
(7, 'Thumb Flexion', 'Bend thumb across the palm', '1. Place your hand palm-up or flat on a table\n2. Slowly bend your thumb across your palm toward the base of your little finger\n3. Hold the position for 1 second\n4. Straighten your thumb back to the starting position\n5. Repeat 5 times', 'beginner', 5, 3, 1, NULL, NULL, NULL, 'local', NULL, 0, '2026-04-22 02:22:04');

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

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

CREATE TABLE `exercise_logs` (
  `log_id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `exercise_id` int(11) DEFAULT NULL,
  `date_completed` date DEFAULT NULL,
  `reps_completed` int(11) DEFAULT NULL,
  `sets_completed` int(11) DEFAULT NULL,
  `pain_score` int(11) DEFAULT NULL,
  `duration_minutes` int(11) DEFAULT NULL,
  `logged_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `exercise_schedules`
--

CREATE TABLE `exercise_schedules` (
  `schedule_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `exercise_set_id` int(11) NOT NULL,
  `therapist_id` int(11) NOT NULL,
  `frequency` enum('Daily','2x Weekly','3x Weekly','Weekly','Custom') DEFAULT 'Daily',
  `custom_days` varchar(50) DEFAULT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `reminder_time` time DEFAULT '21:00:00',
  `send_reminder` tinyint(1) DEFAULT 1,
  `alert_on_missed` tinyint(1) DEFAULT 1,
  `total_sessions` int(11) DEFAULT 30,
  `completed_sessions` int(11) DEFAULT 0,
  `status` enum('active','paused','completed','cancelled') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `exercise_sets`
--

CREATE TABLE `exercise_sets` (
  `set_id` int(11) NOT NULL,
  `set_name` varchar(255) NOT NULL,
  `condition_target` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `exercise_sets` (`set_id`, `set_name`, `condition_target`, `description`, `created_by`, `created_at`) VALUES
(6, 'Gamer\'s Hand Relief', 'Gaming-related strain', 'Exercises for gamers and heavy mouse users', NULL, '2026-02-10 13:02:32'),
(9, 'Stiff Finger', 'Finger stiffness, reduced flexibility', 'This set focuses on improving finger mobility and flexibility.', 1, '2026-04-22 02:28:05'),
(10, 'Muscle Fatigue', 'Hand fatigue, muscle tiredness', 'Designed to reduce muscle fatigue and improve endurance in the hand.', 1, '2026-04-22 02:29:23'),
(11, 'Joint Stiffness', 'Stiff joints, limited range of motion', 'This set targets joint flexibility and helps loosen stiff finger joints.', 1, '2026-04-22 02:30:11'),
(12, 'Paresthesia (Numbness / Tingling)', 'Numbness, tingling sensation', 'These exercises help stimulate nerves and improve blood circulation in the hand.', 1, '2026-04-22 02:31:08'),
(13, 'Hand Weakness', 'Weak grip, reduced hand strength', 'This set strengthens the muscles of the hand and fingers to improve grip strength.', 1, '2026-04-22 02:32:02'),
(14, 'Trigger Finger', 'Finger locking, clicking sensation', 'A gentle exercise set designed to improve tendon movement and reduce stiffness.', 1, '2026-04-22 02:33:20'),
(15, 'Typing Recovery', 'Strain from typing, keyboard overuse', 'Ideal for individuals who spend long hours typing.', 1, '2026-04-22 02:34:03'),
(16, 'Phone / Scrolling Strain', 'Thumb strain, excessive phone use', 'Targets thumb fatigue and stiffness caused by prolonged phone usage.', 1, '2026-04-22 02:35:35'),
(17, 'Heavy Lifting / Grip Strength', 'Post-workout recovery, grip strengthening', 'Designed for individuals who frequently lift heavy objects.', 1, '2026-04-22 02:36:31');

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

--
-- Table structure for table `media_assets`
--

CREATE TABLE `media_assets` (
  `asset_id` int(11) NOT NULL,
  `asset_name` varchar(255) NOT NULL,
  `asset_type` enum('image','video','document','audio') NOT NULL,
  `category` enum('exercise','profile','logo','banner','other') DEFAULT 'exercise',
  `file_path` varchar(500) NOT NULL,
  `file_size` int(11) DEFAULT 0,
  `mime_type` varchar(100) DEFAULT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  `thumbnail_path` varchar(500) DEFAULT NULL,
  `external_url` varchar(500) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `uploaded_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `media_assets` (`asset_id`, `asset_name`, `asset_type`, `category`, `file_path`) VALUES
(1, 'App Logo', 'image', 'logo', 'assets/images/logo.png'),
(2, 'Finger Flexion Video', 'video', 'exercise', 'assets/videos/finger_flexion.mp4'),
(3, 'Grip Strength Video', 'video', 'exercise', 'assets/videos/grip_strength.mp4'),
(4, 'Thumb Stretch Video', 'video', 'exercise', 'assets/videos/thumb_stretch.mp4'),
(5, 'Finger Abduction Video', 'video', 'exercise', 'assets/videos/finger_abduction.mp4'),
(6, 'Default Avatar', 'image', 'profile', 'assets/images/profiles/default_avatar.png'),
(7, 'Onboarding Image 1', 'image', 'banner', 'assets/images/display1.png'),
(8, 'Onboarding Image 2', 'image', 'banner', 'assets/images/display2.png'),
(9, 'Onboarding Image 3', 'image', 'banner', 'assets/images/display3.png'),
(10, 'Onboarding Image 4', 'image', 'banner', 'assets/images/display4.png');

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

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

CREATE TABLE `notifications` (
  `notification_id` int(11) NOT NULL,
  `user_type` enum('patient','therapist','admin') NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('reminder','alert','info','success','warning') DEFAULT 'info',
  `is_read` tinyint(1) DEFAULT 0,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `read_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `pain_score_history`
--

CREATE TABLE `pain_score_history` (
  `pain_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `pain_score` int(11) NOT NULL,
  `recorded_date` date NOT NULL,
  `recorded_time` time DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `session_type` enum('pre-exercise','post-exercise','daily-check') DEFAULT 'post-exercise',
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `password_resets`
--

CREATE TABLE `password_resets` (
  `reset_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `token` varchar(100) NOT NULL,
  `otp_code` varchar(6) DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `patients` (
  `patient_id` int(11) NOT NULL,
  `patient_ic` varchar(20) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `profile_picture` varchar(500) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `finger_condition` varchar(255) DEFAULT NULL,
  `therapist_id` int(11) DEFAULT NULL,
  `program_start_date` date DEFAULT NULL,
  `exercise_set_id` int(11) DEFAULT NULL,
  `status` enum('pending','active','inactive') DEFAULT 'pending',
  `pain_score` int(11) DEFAULT 0,
  `compliance_rate` decimal(5,2) DEFAULT 0.00,
  `streak` int(11) DEFAULT 0,
  `last_exercise` datetime DEFAULT NULL,
  `daily_status` enum('Completed today','Pending today','Missed today') DEFAULT 'Pending today',
  `created_at` datetime DEFAULT current_timestamp(),
  `validated_at` datetime DEFAULT NULL,
  `validated_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `patients` (`patient_id`, `patient_ic`, `full_name`, `email`, `phone`, `password_hash`, `finger_condition`, `therapist_id`, `program_start_date`, `status`) VALUES
(1, '900101-10-1234', 'Test Patient', 'patient@test.com', '012-3456789', '123456', 'Trigger Finger', 2, '2026-04-22', 'active');

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

--
-- Table structure for table `patient_exercises`
--

CREATE TABLE `patient_exercises` (
  `assignment_id` int(11) NOT NULL,
  `patient_id` int(11) DEFAULT NULL,
  `set_id` int(11) DEFAULT NULL,
  `therapist_id` int(11) DEFAULT NULL,
  `frequency` varchar(50) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reminder_enabled` tinyint(1) DEFAULT 1,
  `reminder_time` time DEFAULT '09:00:00',
  `alert_on_missed` tinyint(1) DEFAULT 1,
  `status` enum('active','completed','cancelled') DEFAULT 'active',
  `total_sessions` int(11) DEFAULT 0,
  `completed_sessions` int(11) DEFAULT 0,
  `assigned_date` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `patient_progress_summary`
--

CREATE TABLE `patient_progress_summary` (
  `summary_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `week_start_date` date NOT NULL,
  `total_sessions_scheduled` int(11) DEFAULT 0,
  `sessions_completed` int(11) DEFAULT 0,
  `sessions_missed` int(11) DEFAULT 0,
  `avg_pain_score` decimal(3,1) DEFAULT 0.0,
  `compliance_rate` decimal(5,2) DEFAULT 0.00,
  `streak_days` int(11) DEFAULT 0,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `patient_sessions`
--

CREATE TABLE `patient_sessions` (
  `session_id` int(11) NOT NULL,
  `patient_id` int(11) NOT NULL,
  `login_time` datetime DEFAULT current_timestamp(),
  `logout_time` datetime DEFAULT NULL,
  `device_token` varchar(255) DEFAULT NULL,
  `device_type` enum('android','ios','web') DEFAULT 'android',
  `app_version` varchar(20) DEFAULT NULL,
  `last_active` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `set_exercises`
--

CREATE TABLE `set_exercises` (
  `id` int(11) NOT NULL,
  `set_id` int(11) DEFAULT NULL,
  `exercise_id` int(11) DEFAULT NULL,
  `order_in_set` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `set_exercises` (`id`, `set_id`, `exercise_id`, `order_in_set`) VALUES
(1, 9, 2, 1),
(2, 9, 1, 2),
(3, 9, 3, 3),
(4, 9, 5, 4),
(5, 10, 1, 1),
(6, 10, 7, 2),
(7, 10, 6, 3),
(8, 10, 3, 4),
(9, 11, 3, 1),
(10, 11, 5, 2),
(11, 11, 2, 3),
(12, 11, 4, 4),
(13, 12, 1, 1),
(14, 12, 2, 2),
(15, 12, 7, 3),
(16, 12, 6, 4),
(17, 13, 4, 1),
(18, 13, 3, 2),
(19, 13, 5, 3),
(20, 13, 7, 4),
(21, 14, 1, 1),
(22, 14, 3, 2),
(23, 14, 5, 3),
(24, 14, 7, 4),
(25, 15, 1, 1),
(26, 15, 3, 2),
(27, 15, 6, 3),
(28, 15, 2, 4),
(29, 6, 1, 1),
(30, 6, 3, 2),
(31, 6, 7, 3),
(32, 16, 2, 1),
(33, 16, 7, 2),
(34, 16, 6, 3),
(35, 16, 1, 4),
(36, 17, 2, 1),
(37, 17, 1, 2),
(38, 17, 5, 5),
(39, 17, 4, 6),
(40, 17, 6, 7),
(41, 17, 7, 8),
(42, 17, 3, 9);

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

--
-- Table structure for table `support_messages`
--

CREATE TABLE `support_messages` (
  `message_id` int(11) NOT NULL,
  `sender_type` enum('patient','therapist','admin') NOT NULL,
  `sender_id` int(11) NOT NULL,
  `receiver_type` enum('patient','therapist','admin') NOT NULL,
  `receiver_id` int(11) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `parent_message_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `read_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `setting_id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_type` enum('string','integer','boolean','json') DEFAULT 'string',
  `description` varchar(255) DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `system_settings` (`setting_id`, `setting_key`, `setting_value`, `setting_type`, `description`) VALUES
(1, 'app_version_android', '1.0.0', 'string', 'Current Android app version'),
(2, 'app_version_ios', '1.0.0', 'string', 'Current iOS app version'),
(3, 'min_app_version_android', '1.0.0', 'string', 'Minimum supported Android version'),
(4, 'min_app_version_ios', '1.0.0', 'string', 'Minimum supported iOS version'),
(5, 'maintenance_mode', 'false', 'boolean', 'System maintenance mode'),
(6, 'default_reminder_time', '21:00', 'string', 'Default daily reminder time'),
(7, 'max_pain_score', '10', 'integer', 'Maximum pain score value'),
(8, 'session_timeout_minutes', '30', 'integer', 'User session timeout in minutes');

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

--
-- Table structure for table `therapists`
--

CREATE TABLE `therapists` (
  `therapist_id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `profile_picture` varchar(500) DEFAULT NULL,
  `whatsapp` varchar(20) DEFAULT NULL,
  `password_hash` varchar(255) DEFAULT NULL,
  `centre_name` varchar(255) NOT NULL,
  `centre_type` enum('hospital','clinic','centre') DEFAULT 'clinic',
  `status` enum('pending','active','inactive') DEFAULT 'pending',
  `patient_count` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `validated_at` datetime DEFAULT NULL,
  `validated_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `therapists` (`therapist_id`, `staff_id`, `full_name`, `email`, `phone`, `password_hash`, `centre_name`, `centre_type`, `status`, `patient_count`) VALUES
(2, 'TH002', 'Dr. Lee Wei', 'therapist@test.com', '012-3456789', '123456', 'Kuantan Physio Clinic', 'clinic', 'active', 1);

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

--
-- Table structure for table `therapist_sessions`
--

CREATE TABLE `therapist_sessions` (
  `session_id` int(11) NOT NULL,
  `therapist_id` int(11) NOT NULL,
  `login_time` datetime DEFAULT current_timestamp(),
  `logout_time` datetime DEFAULT NULL,
  `device_token` varchar(255) DEFAULT NULL,
  `device_type` enum('android','ios','web') DEFAULT 'android',
  `app_version` varchar(20) DEFAULT NULL,
  `last_active` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Indexes for all tables
--

ALTER TABLE `activity_logs` ADD PRIMARY KEY (`log_id`);
ALTER TABLE `admins` ADD PRIMARY KEY (`admin_id`), ADD UNIQUE KEY `email` (`email`);
ALTER TABLE `api_tokens` ADD PRIMARY KEY (`token_id`), ADD UNIQUE KEY `token` (`token`);
ALTER TABLE `daily_exercise_tracking` ADD PRIMARY KEY (`tracking_id`), ADD UNIQUE KEY `unique_patient_date` (`patient_id`,`exercise_date`);
ALTER TABLE `exercises` ADD PRIMARY KEY (`exercise_id`);
ALTER TABLE `exercise_logs` ADD PRIMARY KEY (`log_id`);
ALTER TABLE `exercise_schedules` ADD PRIMARY KEY (`schedule_id`);
ALTER TABLE `exercise_sets` ADD PRIMARY KEY (`set_id`);
ALTER TABLE `media_assets` ADD PRIMARY KEY (`asset_id`);
ALTER TABLE `notifications` ADD PRIMARY KEY (`notification_id`);
ALTER TABLE `pain_score_history` ADD PRIMARY KEY (`pain_id`);
ALTER TABLE `password_resets` ADD PRIMARY KEY (`reset_id`);
ALTER TABLE `patients` ADD PRIMARY KEY (`patient_id`), ADD UNIQUE KEY `patient_ic` (`patient_ic`), ADD UNIQUE KEY `email` (`email`);
ALTER TABLE `patient_exercises` ADD PRIMARY KEY (`assignment_id`);
ALTER TABLE `patient_progress_summary` ADD PRIMARY KEY (`summary_id`), ADD UNIQUE KEY `unique_patient_week` (`patient_id`,`week_start_date`);
ALTER TABLE `patient_sessions` ADD PRIMARY KEY (`session_id`);
ALTER TABLE `set_exercises` ADD PRIMARY KEY (`id`);
ALTER TABLE `support_messages` ADD PRIMARY KEY (`message_id`);
ALTER TABLE `system_settings` ADD PRIMARY KEY (`setting_id`), ADD UNIQUE KEY `setting_key` (`setting_key`);
ALTER TABLE `therapists` ADD PRIMARY KEY (`therapist_id`), ADD UNIQUE KEY `staff_id` (`staff_id`), ADD UNIQUE KEY `email` (`email`);
ALTER TABLE `therapist_sessions` ADD PRIMARY KEY (`session_id`);

--
-- AUTO_INCREMENT for tables
--

ALTER TABLE `activity_logs` MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `admins` MODIFY `admin_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `api_tokens` MODIFY `token_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `daily_exercise_tracking` MODIFY `tracking_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `exercises` MODIFY `exercise_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `exercise_logs` MODIFY `log_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `exercise_schedules` MODIFY `schedule_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `exercise_sets` MODIFY `set_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;
ALTER TABLE `media_assets` MODIFY `asset_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
ALTER TABLE `notifications` MODIFY `notification_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `pain_score_history` MODIFY `pain_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `password_resets` MODIFY `reset_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `patients` MODIFY `patient_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `patient_exercises` MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `patient_progress_summary` MODIFY `summary_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `patient_sessions` MODIFY `session_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `set_exercises` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;
ALTER TABLE `support_messages` MODIFY `message_id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `system_settings` MODIFY `setting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
ALTER TABLE `therapists` MODIFY `therapist_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
ALTER TABLE `therapist_sessions` MODIFY `session_id` int(11) NOT NULL AUTO_INCREMENT;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;