Database schema brainstorm

cvenigalla commented 1 month ago

Schema design
familiarize with Google firebase firestone DB

Database: User Info First Name Last Name Email

Weight
One rep
Volume

achbogga commented 1 month ago

Here's a schema for the key-value pairs you can store in Firestore for each workout video:

Firestore Schema:

Field Name	Type	Description
`user_id`	`string`	The unique ID of the user who uploaded the video.
`exercise_name`	`string`	Name of the exercise (e.g., squat, bench press).
`reps`	`int`	Number of repetitions performed.
`one_rep_max`	`float`	The maximum weight lifted for one repetition (default from last session).
`time_start`	`timestamp`	The timestamp when the exercise starts.
`time_end`	`timestamp`	The timestamp when the exercise ends.
`location`	`string`	Location where the exercise was performed (e.g., gym, home).
`video_url`	`string`	URL to the uploaded video in Google Cloud Storage (GCS).
`audio_url`	`string`	URL to the extracted audio file in GCS.
`upload_time`	`timestamp`	Timestamp when the video was uploaded.
`analysis_results`	`dict`	Results from the video analysis (e.g., actions detected).
`labels`	`array`	Labels used for filtering actions during video analysis.

Example Metadata (Firestore Document):

{
  "user_id": "user_123",
  "exercise_name": "squat",
  "reps": 10,
  "one_rep_max": 200.0,
  "time_start": "2024-09-22T10:00:00Z",
  "time_end": "2024-09-22T10:10:00Z",
  "location": "Gym XYZ",
  "video_url": "gs://your-bucket/videos/user_123/squat_video.mp4",
  "audio_url": "gs://your-bucket/audios/user_123/squat_audio.mp3",
  "upload_time": "2024-09-22T10:15:00Z",
  "analysis_results": {
    "0-5": ["squat"],
    "5-10": ["rest"]
  },
  "labels": ["squat", "rest"]
}

Code to Split Video into Audio and Video:

To extract the audio from a video file, you can use ffmpeg. Here’s a Python function to split the video and audio using ffmpeg-python:

Install the ffmpeg-python library:
```
pip install ffmpeg-python
```
Add the following code:

import ffmpeg
import os

def split_video_audio(video_input_path, output_video_path, output_audio_path):
    """
    Split the input video into separate video and audio files using ffmpeg.

    Args:
    - video_input_path (str): Path to the input video file.
    - output_video_path (str): Path to save the extracted video (without audio).
    - output_audio_path (str): Path to save the extracted audio.
    """
    try:
        # Extract video without audio
        ffmpeg.input(video_input_path).output(output_video_path, an=None).run()

        # Extract audio only
        ffmpeg.input(video_input_path).output(output_audio_path, vn=None).run()

        print(f"Video saved to {output_video_path}")
        print(f"Audio saved to {output_audio_path}")
    except ffmpeg.Error as e:
        print(f"Error occurred while processing the video: {e}")
        raise

# Example usage
video_input = "input_video.mp4"
output_video = "output_video_no_audio.mp4"
output_audio = "output_audio.mp3"

split_video_audio(video_input, output_video, output_audio)

How It Works:

Extract Video: The output(output_video_path, an=None) command removes the audio stream from the video file.
Extract Audio: The output(output_audio_path, vn=None) command removes the video stream, saving only the audio.

You can upload the resulting output_video_no_audio.mp4 and output_audio.mp3 to your Google Cloud Storage bucket, then store their URLs in Firestore as video_url and audio_url.

chanduvkp commented 1 month ago

Database Schema:

Routine : Video upload: Submit Button

Schema: Exercise: name Equipment Primary Muscle Secondary Muscle Statistics

Equipment:
    name
    Notes/Description

Muscle Group:
    name
    Notes/Description

Statistics:
    Reps:
    Weight:
    Set volume

Workout Program: Warmup Exercise Main Core Exercise Final closeup Exercise

achbogga / helio