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

START TRANSACTION;



--
-- Database: `gym_management`
--

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

--
-- Table structure for table `bookings`
--

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `member_id` int(11) DEFAULT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `slot_id` int(11) DEFAULT NULL,
  `booking_date` date DEFAULT NULL,
  `status` enum('Booked','Cancelled','Completed') DEFAULT 'Booked'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` (`id`, `member_id`, `trainer_id`, `slot_id`, `booking_date`, `status`) VALUES
(1, 1, 1, 1, '2025-09-11', 'Booked'),
(2, 6, NULL, 1, '2025-09-15', ''),
(3, 6, 2, 3, '2025-09-15', 'Booked'),
(4, 6, NULL, 18, '2025-09-23', ''),
(5, 6, NULL, 25, '2025-09-20', '');

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

--
-- Table structure for table `diet_workout_assignments`
--

CREATE TABLE `diet_workout_assignments` (
  `id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `plan_id` int(11) NOT NULL,
  `assigned_date` date DEFAULT curdate(),
  `status` enum('Pending','Started','Completed') DEFAULT 'Pending'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `diet_workout_assignments`
--

INSERT INTO `diet_workout_assignments` (`id`, `member_id`, `trainer_id`, `plan_id`, `assigned_date`, `status`) VALUES
(1, 1, 1, 1, '2025-09-11', 'Pending'),
(2, 2, 2, 2, '2025-09-11', 'Pending'),
(3, 3, 3, 6, '2025-09-11', 'Pending'),
(4, 1, 1, 1, '2025-09-12', 'Pending'),
(5, 1, 1, 4, '2025-09-12', 'Pending'),
(6, 2, 2, 2, '2025-09-12', 'Pending'),
(7, 2, 2, 5, '2025-09-12', 'Pending'),
(8, 3, 3, 6, '2025-09-12', 'Pending'),
(9, 3, 1, 3, '2025-09-12', 'Pending'),
(10, 4, 2, 1, '2025-09-12', 'Pending'),
(11, 4, 3, 5, '2025-09-12', 'Pending'),
(12, 5, 1, 2, '2025-09-12', 'Pending'),
(13, 5, 2, 4, '2025-09-12', 'Pending'),
(14, 6, 3, 3, '2025-09-12', 'Pending'),
(15, 6, 1, 6, '2025-09-12', 'Pending'),
(16, 6, NULL, 8, '2025-09-12', '');

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

--
-- Table structure for table `membership_plans`
--

CREATE TABLE `membership_plans` (
  `id` int(11) NOT NULL,
  `plan_name` varchar(50) NOT NULL,
  `duration_months` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `membership_plans`
--

INSERT INTO `membership_plans` (`id`, `plan_name`, `duration_months`, `price`, `created_at`) VALUES
(1, 'Monthly', 1, 1000.00, '2025-09-11 18:15:40'),
(2, 'Quarterly', 3, 2700.00, '2025-09-11 18:15:40'),
(3, 'Yearly', 12, 10000.00, '2025-09-11 18:15:40');

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `member_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `payment_date` date DEFAULT NULL,
  `payment_method` enum('Cash','Card','UPI','Online') DEFAULT 'Cash',
  `status` enum('Paid','Due','Overdue') DEFAULT 'Paid'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `member_id`, `amount`, `payment_date`, `payment_method`, `status`) VALUES
(1, 1, 1000.00, '2025-09-11', 'Cash', 'Paid');

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

--
-- Table structure for table `plans_library`
--

CREATE TABLE `plans_library` (
  `id` int(11) NOT NULL,
  `plan_name` varchar(100) NOT NULL,
  `goal` enum('Weight Loss','Muscle Gain','General Fitness','Strength','Cardio','Beginner') NOT NULL,
  `diet_plan` text NOT NULL,
  `workout_plan` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `plans_library`
--

INSERT INTO `plans_library` (`id`, `plan_name`, `goal`, `diet_plan`, `workout_plan`, `created_at`) VALUES
(1, 'Weight Loss Plan', 'Weight Loss', 'Breakfast: Oats + Milk + Fruits | Lunch: Grilled Chicken + Brown Rice + Salad | Dinner: Veggie Soup + Whole Wheat Toast', 'Cardio: 20 min HIIT | Strength: Push-ups 3x12, Squats 3x15, Plank 3x45s', '2025-09-11 18:15:42'),
(2, 'Muscle Gain Plan', 'Muscle Gain', 'Breakfast: 4 Eggs + Oatmeal + Peanut Butter | Lunch: Chicken Breast + Quinoa + Broccoli | Dinner: Paneer + Rice + Veggies', 'Strength: Bench Press 4x8, Deadlifts 4x6, Overhead Press 3x10 | Cardio: 10 min light jog', '2025-09-11 18:15:42'),
(3, 'General Fitness Plan', 'General Fitness', 'Breakfast: Smoothie (Banana, Milk, Protein) | Lunch: Mixed Veg Salad + Dal + Rice | Dinner: Light Paneer Curry + Roti', 'Circuit: Jumping Jacks 3x30s, Push-ups 3x10, Bodyweight Squats 3x15, Stretching 10 min', '2025-09-11 18:15:42'),
(4, 'Strength Plan', 'Strength', 'Breakfast: Greek Yogurt + Nuts | Lunch: Chicken Wrap + Veggies | Dinner: Fish + Sweet Potato', 'Strength: Barbell Squats 5x5, Bench Press 5x5, Pull-ups 3x8, Deadlifts 3x5', '2025-09-11 18:15:42'),
(5, 'Cardio Endurance Plan', 'Cardio', 'Breakfast: Fruits + Oats | Lunch: Tuna Sandwich + Salad | Dinner: Soup + Brown Bread', 'Cardio: 30 min Cycling | Running: 3km easy pace | Stretching: 15 min', '2025-09-11 18:15:42'),
(6, 'Beginner Plan', 'Beginner', 'Breakfast: Idli + Sambar | Lunch: Grilled Paneer + Rice | Dinner: Dal + Chapati + Salad', 'Day 1: Walk 20 min, Push-ups 2x10, Squats 2x10 | Day 2: Rest | Day 3: Repeat Routine', '2025-09-11 18:15:42'),
(7, 'Strength Plan', 'Strength', 'Breakfast: Greek Yogurt + Nuts | Lunch: Chicken Wrap + Veggies | Dinner: Fish + Sweet Potato', 'Barbell Squats 5x5, Bench Press 5x5, Pull-ups 3x8, Deadlifts 3x5', '2025-09-12 11:43:46'),
(8, 'Cardio Endurance Plan', 'Cardio', 'Breakfast: Fruits + Oats | Lunch: Tuna Sandwich + Salad | Dinner: Soup + Brown Bread', 'Running: 5km | Cycling: 30 min | Jump Rope: 10 min | Stretching: 15 min', '2025-09-12 11:43:46'),
(9, 'General Fitness Plan', 'General Fitness', 'Breakfast: Smoothie (Banana, Milk, Protein) | Lunch: Mixed Veg Salad + Dal + Rice | Dinner: Light Paneer Curry + Roti', 'Circuit: Jumping Jacks 3x30s, Push-ups 3x10, Bodyweight Squats 3x15, Stretching 10 min', '2025-09-12 11:43:46'),
(10, 'Beginner Plan 2', 'Beginner', 'Breakfast: Oatmeal + Banana | Lunch: Grilled Veg + Rice | Dinner: Lentil Soup + Chapati', 'Day 1: Walk 15 min, Push-ups 2x8, Squats 2x10 | Day 2: Rest | Day 3: Repeat Routine', '2025-09-12 11:43:46'),
(11, 'Fat Loss Advanced', 'Weight Loss', 'Breakfast: Egg Whites + Spinach | Lunch: Chicken Salad | Dinner: Fish + Veggies', 'HIIT: 25 min, Strength Circuit: Push-ups 3x15, Lunges 3x12, Plank 3x45s', '2025-09-12 11:43:46'),
(12, 'Muscle Gain Advanced', 'Muscle Gain', 'Breakfast: Eggs + Oatmeal + Peanut Butter | Lunch: Chicken Breast + Quinoa + Broccoli | Dinner: Salmon + Rice + Veggies', 'Strength: Squats 5x5, Deadlifts 4x6, Bench Press 4x8, Pull-ups 4x10, Overhead Press 3x10', '2025-09-12 11:43:46'),
(13, 'Weight Maintenance Plan', 'General Fitness', 'Breakfast: Smoothie + Oats | Lunch: Chicken/Paneer Salad | Dinner: Light Veg + Rice', 'Light Cardio: 20 min, Bodyweight Strength: Push-ups 3x10, Squats 3x12, Stretching 10 min', '2025-09-12 11:43:46');

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

--
-- Table structure for table `slots`
--

CREATE TABLE `slots` (
  `id` int(11) NOT NULL,
  `trainer_id` int(11) DEFAULT NULL,
  `slot_time` time DEFAULT NULL,
  `day` enum('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `slots`
--

INSERT INTO `slots` (`id`, `trainer_id`, `slot_time`, `day`) VALUES
(1, 1, '08:00:00', 'Monday'),
(2, 1, '11:00:00', 'Monday'),
(3, 2, '18:00:00', 'Monday'),
(4, 2, '22:00:00', 'Monday'),
(5, 1, '08:00:00', 'Tuesday'),
(6, 1, '09:00:00', 'Tuesday'),
(7, 1, '10:00:00', 'Wednesday'),
(8, 1, '11:00:00', 'Thursday'),
(9, 2, '18:00:00', 'Tuesday'),
(10, 2, '19:00:00', 'Wednesday'),
(11, 2, '20:00:00', 'Thursday'),
(12, 2, '21:00:00', 'Friday'),
(13, 3, '18:00:00', 'Saturday'),
(14, 3, '19:00:00', 'Saturday'),
(15, 4, '20:00:00', 'Saturday'),
(16, 4, '21:00:00', 'Saturday'),
(17, 5, '07:30:00', 'Monday'),
(18, 5, '08:30:00', 'Tuesday'),
(19, 5, '09:30:00', 'Wednesday'),
(20, 5, '10:30:00', 'Thursday'),
(21, 6, '17:00:00', 'Monday'),
(22, 6, '18:00:00', 'Tuesday'),
(23, 6, '19:00:00', 'Wednesday'),
(24, 6, '20:00:00', 'Thursday'),
(25, 7, '08:00:00', 'Saturday'),
(26, 7, '09:00:00', 'Sunday'),
(27, 7, '10:00:00', 'Sunday');

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

--
-- Table structure for table `trainers`
--

CREATE TABLE `trainers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `contact` varchar(15) DEFAULT NULL,
  `availability` 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 `trainers`
--

INSERT INTO `trainers` (`id`, `name`, `contact`, `availability`, `created_at`) VALUES
(1, 'Avimanyu Singh', '9876543210', 'Monday-Friday 8AM-11AM', '2025-09-11 18:15:40'),
(2, 'Rishabh Agarwal', '9876543211', 'Monday-Friday 8AM-11AM', '2025-09-11 18:15:40'),
(3, 'Aman Yadav', '9875554321', 'Monday-Friday 6PM-10PM', '2025-09-11 18:15:40'),
(4, 'Avishek Singh', '9876542311', 'Monday-Friday 6PM-10PM', '2025-09-11 18:15:40'),
(5, 'Ritika Sharma', '9876001234', 'Monday-Friday 7AM-12PM', '2025-09-12 11:17:42'),
(6, 'Aditya Mehta', '9876005678', 'Monday-Friday 5PM-9PM', '2025-09-12 11:17:42'),
(7, 'Kavya Iyer', '9876009876', 'Saturday-Sunday 8AM-2PM', '2025-09-12 11:17:42');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `bookings`
--
ALTER TABLE `bookings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `member_id` (`member_id`),
  ADD KEY `trainer_id` (`trainer_id`),
  ADD KEY `slot_id` (`slot_id`);

--
-- Indexes for table `diet_workout_assignments`
--
ALTER TABLE `diet_workout_assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `member_id` (`member_id`),
  ADD KEY `trainer_id` (`trainer_id`),
  ADD KEY `plan_id` (`plan_id`);

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

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `member_id` (`member_id`);

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

--
-- Indexes for table `slots`
--
ALTER TABLE `slots`
  ADD PRIMARY KEY (`id`),
  ADD KEY `trainer_id` (`trainer_id`);

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bookings`
--
ALTER TABLE `bookings`
  ADD CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  ADD CONSTRAINT `bookings_ibfk_2` FOREIGN KEY (`trainer_id`) REFERENCES `trainers` (`id`),
  ADD CONSTRAINT `bookings_ibfk_3` FOREIGN KEY (`slot_id`) REFERENCES `slots` (`id`);

--
-- Constraints for table `diet_workout_assignments`
--
ALTER TABLE `diet_workout_assignments`
  ADD CONSTRAINT `diet_workout_assignments_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`),
  ADD CONSTRAINT `diet_workout_assignments_ibfk_2` FOREIGN KEY (`trainer_id`) REFERENCES `trainers` (`id`),
  ADD CONSTRAINT `diet_workout_assignments_ibfk_3` FOREIGN KEY (`plan_id`) REFERENCES `plans_library` (`id`);

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`);

--
-- Constraints for table `slots`
--
ALTER TABLE `slots`
  ADD CONSTRAINT `slots_ibfk_1` FOREIGN KEY (`trainer_id`) REFERENCES `trainers` (`id`);
COMMIT;

