strava_workouts_schema.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 17.0 (Homebrew)
-- Dumped by pg_dump version 17.0 (Homebrew)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: public; Type: SCHEMA; Schema: -; Owner: tim
--

-- *not* creating schema, since initdb creates it


ALTER SCHEMA public OWNER TO tim;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: workout_data_points; Type: TABLE; Schema: public; Owner: tim
--

CREATE TABLE public.workout_data_points (
    id integer NOT NULL,
    workout_id bigint,
    "timestamp" timestamp without time zone NOT NULL,
    elapsed_time double precision,
    distance double precision,
    speed double precision,
    heartrate double precision,
    cadence double precision,
    altitude double precision,
    latitude double precision,
    longitude double precision,
    power integer
);


ALTER TABLE public.workout_data_points OWNER TO tim;

--
-- Name: workouts; Type: TABLE; Schema: public; Owner: tim
--

CREATE TABLE public.workouts (
    id integer NOT NULL,
    workout_id bigint,
    start_time timestamp without time zone,
    end_time timestamp without time zone,
    duration double precision,
    distance double precision,
    average_speed double precision,
    max_speed double precision,
    average_heart_rate double precision,
    max_heart_rate double precision,
    cadence double precision,
    elevation_gain double precision,
    calories double precision,
    sport_type character varying(50),
    raw_data jsonb,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    name character varying(255)
);


ALTER TABLE public.workouts OWNER TO tim;

--
-- Name: full_workout_data; Type: VIEW; Schema: public; Owner: tim
--

CREATE VIEW public.full_workout_data AS
 SELECT dp.id AS data_point_id,
    dp.workout_id,
    w.start_time,
    w.end_time,
    w.duration AS total_duration,
    w.distance AS total_distance,
    w.name AS workout_name,
    w.average_speed AS avg_speed,
    w.max_speed,
    w.average_heart_rate AS avg_heart_rate,
    w.max_heart_rate,
    w.cadence AS avg_cadence,
    w.elevation_gain,
    w.calories,
    w.sport_type,
    dp."timestamp" AS data_point_time,
    dp.elapsed_time,
    dp.distance AS point_distance,
    dp.speed AS point_speed,
    dp.heartrate AS point_heartrate,
    dp.cadence AS point_cadence,
    dp.altitude AS point_altitude,
    dp.latitude,
    dp.longitude,
    (dp.speed * (2.23694)::double precision) AS speed_mph,
        CASE
            WHEN (dp.speed >= (0.5)::double precision) THEN ((1609.34)::double precision / (dp.speed * (60)::double precision))
            ELSE NULL::double precision
        END AS pace_min_per_mile
   FROM (public.workout_data_points dp
     JOIN public.workouts w ON ((dp.workout_id = w.workout_id)));


ALTER VIEW public.full_workout_data OWNER TO tim;

--
-- Name: trainingschedule; Type: TABLE; Schema: public; Owner: tim
--

CREATE TABLE public.trainingschedule (
    date date NOT NULL,
    activity character varying(50) NOT NULL,
    distancemeters double precision,
    timeminutes double precision,
    measurement character varying(50),
    description character varying(255),
    category character varying(50),
    timecompminutes double precision,
    distancecompmeters double precision,
    distancereference character varying(50),
    distancecompmiles double precision
);


ALTER TABLE public.trainingschedule OWNER TO tim;

--
-- Name: progress_comparison; Type: VIEW; Schema: public; Owner: tim
--

CREATE VIEW public.progress_comparison AS
 SELECT COALESCE(t.day, w.day) AS day,
    COALESCE(t.sport_type, w.sport_type) AS sport_type,
    COALESCE(t.total_hours, (0)::double precision) AS planned_hours,
    COALESCE(t.total_distance_meters, (0)::double precision) AS planned_distance_meters,
    COALESCE(t.total_distance_miles, (0)::double precision) AS planned_distance_miles,
    COALESCE(w.total_hours, (0)::double precision) AS actual_hours,
    COALESCE(w.total_distance_meters, (0)::double precision) AS actual_distance_meters,
    COALESCE(w.total_distance_miles, (0)::double precision) AS actual_distance_miles
   FROM (( SELECT trainingschedule.date AS day,
            trainingschedule.activity AS sport_type,
            (sum(trainingschedule.timecompminutes) / (60)::double precision) AS total_hours,
            sum(trainingschedule.distancecompmeters) AS total_distance_meters,
            (sum(trainingschedule.distancecompmeters) * (0.000621371)::double precision) AS total_distance_miles
           FROM public.trainingschedule
          WHERE ((trainingschedule.activity)::text = ANY ((ARRAY['Run'::character varying, 'Bike'::character varying, 'Swim'::character varying])::text[]))
          GROUP BY trainingschedule.date, trainingschedule.activity) t
     FULL JOIN ( SELECT date(workouts.start_time) AS day,
                CASE
                    WHEN ((workouts.sport_type)::text = ANY ((ARRAY['Ride'::character varying, 'VirtualRide'::character varying])::text[])) THEN 'Bike'::character varying
                    ELSE workouts.sport_type
                END AS sport_type,
            (sum(workouts.duration) / (60)::double precision) AS total_hours,
            sum(workouts.distance) AS total_distance_meters,
            (sum(workouts.distance) * (0.000621371)::double precision) AS total_distance_miles
           FROM public.workouts
          WHERE ((workouts.sport_type)::text = ANY ((ARRAY['Run'::character varying, 'Ride'::character varying, 'VirtualRide'::character varying, 'Swim'::character varying])::text[]))
          GROUP BY (date(workouts.start_time)),
                CASE
                    WHEN ((workouts.sport_type)::text = ANY ((ARRAY['Ride'::character varying, 'VirtualRide'::character varying])::text[])) THEN 'Bike'::character varying
                    ELSE workouts.sport_type
                END) w ON (((t.day = w.day) AND ((t.sport_type)::text = (w.sport_type)::text))))
  ORDER BY COALESCE(t.day, w.day), COALESCE(t.sport_type, w.sport_type);


ALTER VIEW public.progress_comparison OWNER TO tim;

--
-- Name: workout_analysis; Type: VIEW; Schema: public; Owner: tim
--

CREATE VIEW public.workout_analysis AS
 SELECT id,
    workout_id,
    "timestamp",
    elapsed_time,
    distance,
    speed AS speed_mps,
    (speed * (2.23694)::double precision) AS speed_mph,
        CASE
            WHEN (speed > (0)::double precision) THEN ((1609.34)::double precision / (speed * (60)::double precision))
            ELSE NULL::double precision
        END AS pace_min_per_mile
   FROM public.workout_data_points;


ALTER VIEW public.workout_analysis OWNER TO tim;

--
-- Name: workout_data_points_id_seq; Type: SEQUENCE; Schema: public; Owner: tim
--

CREATE SEQUENCE public.workout_data_points_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.workout_data_points_id_seq OWNER TO tim;

--
-- Name: workout_data_points_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: tim
--

ALTER SEQUENCE public.workout_data_points_id_seq OWNED BY public.workout_data_points.id;


--
-- Name: workouts_id_seq; Type: SEQUENCE; Schema: public; Owner: tim
--

CREATE SEQUENCE public.workouts_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.workouts_id_seq OWNER TO tim;

--
-- Name: workouts_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: tim
--

ALTER SEQUENCE public.workouts_id_seq OWNED BY public.workouts.id;


--
-- Name: workout_data_points id; Type: DEFAULT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workout_data_points ALTER COLUMN id SET DEFAULT nextval('public.workout_data_points_id_seq'::regclass);


--
-- Name: workouts id; Type: DEFAULT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workouts ALTER COLUMN id SET DEFAULT nextval('public.workouts_id_seq'::regclass);


--
-- Name: workout_data_points workout_data_points_pkey; Type: CONSTRAINT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workout_data_points
    ADD CONSTRAINT workout_data_points_pkey PRIMARY KEY (id);


--
-- Name: workouts workouts_pkey; Type: CONSTRAINT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workouts
    ADD CONSTRAINT workouts_pkey PRIMARY KEY (id);


--
-- Name: workouts workouts_workout_id_key; Type: CONSTRAINT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workouts
    ADD CONSTRAINT workouts_workout_id_key UNIQUE (workout_id);


--
-- Name: workout_data_points workout_data_points_workout_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: tim
--

ALTER TABLE ONLY public.workout_data_points
    ADD CONSTRAINT workout_data_points_workout_id_fkey FOREIGN KEY (workout_id) REFERENCES public.workouts(workout_id) ON DELETE CASCADE;


--
-- Name: SCHEMA public; Type: ACL; Schema: -; Owner: tim
--

REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--