Skip to content

Instantly share code, notes, and snippets.

@anartzdev

anartzdev/db.sql

Created August 26, 2024 04:06
Show Gist options
  • Save anartzdev/1da8fbd741e65f421e11aa9c7eb28965 to your computer and use it in GitHub Desktop.
Save anartzdev/1da8fbd741e65f421e11aa9c7eb28965 to your computer and use it in GitHub Desktop.
Download ZIP
online-sport-platform.sql
Raw
db.sql
--
-- User basic and more profile data
--
CREATE TABLE `role` (
id int (255) auto_increment not null,
`name` varchar(100) NOT NULL,
`description` varchar(200) NOT NULL,
`access_type` int(2) NOT NULL,
`created_at` varchar(60) NOT NULL,
CONSTRAINT pk_role PRIMARY KEY(id),
CONSTRAINT user_uniques_fields UNIQUE (name)
) ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `role` (`id`, `name`, `description`, `access_type`, `created_at`) VALUES
(1, 'ROLE_SUPER', '', 0, ''),
(2, 'ROLE_ADMIN', '', 0, ''),
(3, 'ROLE_TRAINER', '', 0, ''),
(4, 'ROLE_CLIENT', '', 0, ''),
(5, 'ROLE_USER', '', 0, '');
CREATE TABLE `user`(
id int(255) auto_increment not null,
role varchar(100) COMMENT 'Access privileges in platform',
email varchar(140),
name varchar(140),
lastname varchar(255),
nick varchar(150),
password varchar(255),
locale VARCHAR(10) NULL DEFAULT NULL COMMENT 'Para almacenar el locale seleccionado por el usuario',
status varchar(255),
bio varchar(255),
active varchar(2) DEFAULT 0,
photo varchar(255),
gender int(2) COMMENT 'Sexo del usuario / Masculino o femenino' DEFAULT 0,
birthday varchar(255) COMMENT 'Fecha de nacimiento',
CONSTRAINT user_uniques_fields UNIQUE (email, nick),
CONSTRAINT fk_role_user FOREIGN KEY(role) references role(name),
CONSTRAINT pk_user PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `user` (`id`, `role`, `email`, `name`, `lastname`, `nick`, `password`, `locale`, `status`, `bio`, `active`, `photo`, `gender`, `birthday`) VALUES
(1, 'ROLE_SUPER', 'mugan86@gmail.com', 'Anartz', 'Mugika Ledo', 'anartz', '$2y$04$lC.rqu2OPJD.JA8O9Hy.levg5JnXiYH.r2/8L5dGNQ/hWnYU6N74i', 'es_EU', NULL, NULL, '1', 'anartz.png', 1, '1986-01-10'),
(2, 'ROLE_ADMIN', 'iloizaga@hotmail.com', 'Imanol', 'Loizaga', 'imanol', '$2y$04$ANhUTsYx0FD/h0R7/uobCOMUwbKXAHTgXcaOHVe.MQFzLDb15YCrq', 'es', NULL, NULL, '1', NULL, 1, NULL),
(3, 'ROLE_USER', 'prueba@gmail.com', 'prueba', 'prueba', 'prueba', '$2y$04$4OHp9s0sfaHEnZFfUJRKU.3n3Z01TRSQcZcCCtx1WgkSujsGzE8im', 'es', NULL, NULL, '0', NULL, 1, NULL);
CREATE TABLE suggest(
id int(255) auto_increment not null,
funcionality varchar(100) COMMENT 'Nueva o mejora a una existente',
details text COMMENT "Detalles sobre la sugerencia",
url varchar(255) COMMENT "Referencia para mostrar un ejemplo",
image varchar(100),
video varchar(255),
add_by int(255) COMMENT "Quien hace la sugerencia",
created_at varchar(100) COMMENT "Cuando se hace la sugerencia",
CONSTRAINT fk_suggest_user FOREIGN KEY(add_by) references user(id),
CONSTRAINT pk_suggest PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE trainer_asign_user(
id int(255) auto_increment not null,
trainer int(255) COMMENT "Entrenador al que se asigna un usuario",
user int(255) COMMENT "Usuario el que se asigna a un entrenador",
add_data varchar(100) COMMENT "Fecha en la que se ha añadido",
CONSTRAINT trainer_asign_user_uniques_fields UNIQUE (trainer, user),
CONSTRAINT fk_trainer_asign_user FOREIGN KEY(trainer) references user(id),
CONSTRAINT fk_trainer_asign_user_second FOREIGN KEY(user) references user(id),
CONSTRAINT pk_trainer_asign_user PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `trainer_asign_user` (`id`, `trainer`, `user`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 1),
(5, 2, 2),
(6, 2, 3);
CREATE TABLE contact_info(
id int(255) auto_increment not null,
user_id int(255),
principal_phone varchar(25) COMMENT "Numero principal",
second_phone varchar(25) COMMENT "Numero secundario",
website varchar(255) COMMENT "Website",
twitter varchar(150),
facebook varchar(150),
instagram varchar(150),
strava varchar(150),
endomondo varchar(150),
nike_plus varchar(150),
polar varchar(150),
CONSTRAINT pk_contact_info PRIMARY KEY(id),
CONSTRAINT fk_contact_info_user FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `contact_info` (`id`, `user_id`, `principal_phone`, `second_phone`, `website`, `twitter`, `facebook`, `instagram`, `strava`, `endomondo`, `nike_plus`, `polar`) VALUES
(1, 1, '943752472', '617778616', 'https://mugan86.com', 'mugan86', NULL, NULL, '1675787', '841653', NULL, NULL),
(2, 2, NULL, NULL, 'https://www.imanoloizaga.com/', 'imanolloizaga', NULL, 'imanoloizaga', NULL, NULL, NULL, NULL);
CREATE TABLE week_day(
id int(255) auto_increment not null,
es varchar(100),
eu varchar(100),
en varchar(100),
ca varchar(100),
pt varchar(100),
it varchar(100),
de varchar(100),
ru varchar(100),
CONSTRAINT pk_week_day PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `week_day` ADD UNIQUE( `es`);
ALTER TABLE `week_day` ADD `multi_code` VARCHAR(100) NOT NULL AFTER `ru`;
INSERT IGNORE INTO `week_day` (`id`, `es`, `eu`, `en`, `ca`, `pt`, `it`, `de`, `ru`, `multi_code`) VALUES
(1, 'Lunes', 'Astelehena', 'Monday', 'Dilluns', 'Segunda-feira', 'Lunedì', 'Montag', NULL, 'monday'),
(2, 'Martes', 'Asteartea', 'Tuesday', 'Dimarts', 'Terça-Feira', 'Martedi', 'Dienstag', NULL, 'tuesday'),
(3, 'Miércoles', 'Asteazkena', 'Wednesday', 'Dimecres', 'Quarta-Feira', 'Mercoledi', 'Mittwoch', NULL, 'wednesday'),
(4, 'Jueves', 'Osteguna', 'Thursday', 'Dijous', 'Quinta-Feira', 'Giovedì', 'Donnerstag', NULL, 'thursday'),
(5, 'Viernes', 'Ostirala', 'Friday', 'Divendres', 'Sexta-Feira', 'Venerdì', 'Freitag', NULL, 'friday'),
(6, 'Sábado', 'Larunbata', 'Saturday', 'Dissabte', 'Sábado', 'Sabato', 'Samstag', NULL, 'saturday'),
(7, 'Domingo', 'Igandea', 'Sunday', 'Diumenge', 'Domingo', 'Domenica', 'Sonntag', NULL, 'sunday');
CREATE TABLE hour_available(
id int(255) auto_increment not null,
day int(255) COMMENT "Día de la semana",
total int(2) COMMENT "24 disponible",
start_time varchar(255) COMMENT "Hora de inicio",
finish_time varchar(255) COMMENT "Hora de finalización",
CONSTRAINT pk_hour_available PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `hour_available` (`id`, `day`, `total`, `start_time`, `finish_time`) VALUES
(1, 1, 0, '10:00:00', '12:00:00'),
(2, 1, 1, '', ''),
(3, 2, 1, '', ''),
(4, 3, 1, '', ''),
(5, 4, 1, '', ''),
(6, 5, 1, '', ''),
(7, 6, 1, '', ''),
(8, 7, 1, '', '');
CREATE TABLE hour_available_to_user(
id int(255) auto_increment not null,
type varchar(60) COMMENT "default, plan, week",
type_id int(255) COMMENT "Id del tipo. Default -1, y los demás su ID",
user_id int(255) COMMENT "Usuario",
hour_available_id int(255) COMMENT "Disponibilidad horaria",
CONSTRAINT pk_hour_available_to_user PRIMARY KEY(id),
CONSTRAINT fk_hour_available_to_user_user FOREIGN KEY(user_id) references user(id),
CONSTRAINT fk_hour_available_to_use_hour FOREIGN KEY(hour_available_id) references hour_available(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `town` (
id int(255) auto_increment NOT NULL,
town varchar(150) DEFAULT NULL COMMENT 'Town name',
province varchar(150) DEFAULT NULL,
country varchar(150) DEFAULT NULL,
continent varchar(150) NOT NULL,
lat varchar(60) NOT NULL COMMENT 'Latitud de la ubicación',
lng varchar(60) NOT NULL COMMENT 'Longitud de la ubicación',
CONSTRAINT pk_town PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE personal_info(
id int(255) auto_increment not null,
user_id int(255),
town_id int(255),
CONSTRAINT pk_personal_info PRIMARY KEY(id),
CONSTRAINT fk_personal_info_user FOREIGN KEY(user_id) references user(id),
CONSTRAINT fk_personal_info_town FOREIGN KEY(town_id) references town(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE section(
id int(255) auto_increment not null,
reference varchar(100),
es varchar(100),
eu varchar(100),
en varchar(100),
ca varchar(100),
pt varchar(100),
it varchar(100),
de varchar(100),
ru varchar(100),
CONSTRAINT pk_section PRIMARY KEY(id),
CONSTRAINT section_uniques_fields UNIQUE (reference)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT INTO section(id,reference,es,eu,en,ca,pt,it,de,ru) VALUES (1,'habits','Hábitos','Ohiturak','Habits',NULL,NULL,NULL,NULL,NULL);
INSERT INTO section(id,reference,es,eu,en,ca,pt,it,de,ru) VALUES (2,'cardio','Cardio','Kardioa','Cardio',NULL,NULL,NULL,NULL,NULL);
INSERT INTO section(id,reference,es,eu,en,ca,pt,it,de,ru) VALUES (3,'metabolism','Metabolismo','Metabolismoa','Metabolism',NULL,NULL,NULL,NULL,NULL);
CREATE TABLE type(
id int(255) auto_increment not null,
es varchar(100),
eu varchar(100),
en varchar(100),
ca varchar(100),
pt varchar(100),
it varchar(100),
de varchar(100),
ru varchar(100),
CONSTRAINT pk_type PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (1,'Tabaco','Tabakoa','Tobacco',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (2,'Dieta','Dieta','Diet',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (3,'Alcohol','Alkohola','Alcohol',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (4,'Café','Kafea','Coffee',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (5,'Ejercicio Físico','Ariketa fisikoa','Physical exercise',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (6,'Dormir','Lo egin','Sleep',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (7,'Altura (cm)','Altuera (zm)','Height (cm)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (8,'Peso (kg)','Pisua (kg)','Weight (kg)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (9,'Peso Competición (kg)','Lehiaketa pisua (kg)','Competition weight (kg)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (10,'Colesterol total','Kolesterol osoa','Total cholesterol',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (11,'Colesterol HDL','HDL kolesterola','HDL cholesterol',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (12,'Tensión alta (sistélica)','Tentsio altua (sistolikoa)','High tension (systemic)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (13,'Tensión baja (diastólica)','Tentsio baxua (diastolikoa)','Low tension (diastolic)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (14,'Glucosa en sangre','Odol glukosa','Blood glucose',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (15,'Diabetes','Diabetes','Diabetes',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (16,'Perimetro Abdominal','Sabeleko perimetroa','Abdominal perimeter',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (17,'Hematocrito','Hematokritoa','Hematocrit',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (18,'IMC','IMC','IMC',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (19,'Frecuencia cardiaca en reposo','Bihotz-taupa minimoa','Resting heart rate',NULL,NULL,NULL,NULL,NULL);
INSERT INTO type(id,es,eu,en,ca,pt,it,de,ru) VALUES (20,'Frecuencia cardiaca máxima','Gehiengo bihotz-taupada','Maximum heart rate',NULL,NULL,NULL,NULL,NULL);
CREATE TABLE options_config(
id int(255) auto_increment not null,
type int(255),
es varchar(100),
eu varchar(100),
en varchar(100),
ca varchar(100),
pt varchar(100),
it varchar(100),
de varchar(100),
ru varchar(100),
CONSTRAINT pk_options_config PRIMARY KEY(id),
CONSTRAINT fk_options_config_type FOREIGN KEY(type) references type(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (1,1,'No fumador','Ez-erretzailea','Non smoker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (2,1,'Ex-fumador','Erretzaile ohia','Former smoker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (3,1,'< 5 cig / día','< 5 cig / eguneko','< 5 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (4,1,'5-10 cig / día','5-10 cig / eguneko','5-10 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (5,1,'11-20 cig / día','11-20 cig / eguneko','11-20 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (6,1,'21-30 cig / día','21-30 cig / eguneko','21-30 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (7,1,'31-40 cig / día','31-40 cig / eguneko','31-40 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (8,1,'> 40 cig / día','> 40 cig / eguneko','> 40 cig / day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (9,1,'Fumo puros','Puroak erretzen ditut','I smoke cigars',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (10,1,'Fumo Pipa','Pipa erretzen dut','I smoke pipe',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (11,1,'Fumador esporádico','Noizbehinka erretzen dut','Sporadic smoker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (12,1,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (13,1,'Fumador','Erretzailea','Smoker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (14,3,'No bebedor','Ez dut edaten','Non-drinker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (15,3,'Consumidor esporádico','Noizbehinka edaten dut','Sporadic-drinker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (16,3,'1 vaso vino / cerveza al día','Ardo / garagardo bat egunean','1 glass wine / beer per day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (17,3,'2-3 vasos vino / cerveza al día','2-3 ardo / garagardo eguneko','2-3 glass wine / beer per day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (18,3,'> 3 vaso vino / cerveza al día','Eguneko ardo / garagardo kopurua 3 edo gehiago','> 3 glass wine / beer per day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (19,3,'Consumidor habitual','Kontsumitzaile ohikoa','Habitual consumer',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (20,3,'Consumidor fin de semana','Asteburuko kontsumitzailea','Weekend consumer',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (21,3,'Solo fin de semana < 8','Asteburuan bakarrik < 8','Only in weekend < 8',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (22,3,'Solo fin de semana > 8','Asteburuan bakarrik > 8','Only in weekend > 8',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (23,3,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (24,3,'Ex-bebedor','Edale ohia','Ex-drinker',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (25,4,'No consume','Ez dut konsumitzen','Does not consume',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (26,4,'1 taza al día','Kafe kopa bat eguneko','1 cup a day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (27,4,'2 tazas al día','2 kafe kopa eguneko','2 cups a day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (28,4,'3 ó más al día','3 edo kafe kopa gehiago eguneko','3 or more cups a day',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (29,4,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (30,4,'Diario','Egunero','Daily',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (31,4,'Ocasional','Noizbehinka','Occasional',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (32,2,'Variada y equilibrada','Denetarik eta orekatua','Varied and balanced',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (33,2,'Vegetariana CON suplementos','Begetarianoak osagarriekin','Vegetarian WITH supplements',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (34,2,'Vegetariana SIN suplementos','Begetarianoak osagarri gabe','Vegetarian WITHOUT supplements',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (35,2,'Supervisada por su médico','Zure medikuak gainbegiratuta','Supervised by your doctor',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (36,2,'Baja en calorías','Kaloria gutxitan oinarritua','Low in calories',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (37,2,'Alta en proteinas','Proteina askotan oinarritua','High in proteins',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (38,2,'Alta en calorías','Kaloria askotan oinarritua','High in calories',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (39,2,'No equilibrada','Ez-orekatu','Not balanced',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (40,2,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (41,2,'Rica en grasa','Koipe ugari','Rich in fat',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (42,2,'Rica en hidratos de carbono','Karbohidrato ugari','Rich in carbohydrates',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (43,2,'Comida rápida (fast-food)','Janari Azkarra (fast-food)','Fast food',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (44,2,'Hiposódica','Sodio gutxikoa','Low sodium',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (45,2,'Pobre en grasa','Koipe gutxikoa','Poor in fat',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (46,2,'Vegetariana','Begetarianoa','Vegetarian',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (47,2,'Excesiva','Gehiegizkoa','Excesive',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (48,2,'Hipoglicemiante','Hypoglycemic','Hypoglycemic',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (49,5,'No realizo ejercicio','Ez dut ariketarik egiten','I do not exercise',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (50,5,'Esporádico','Noizbehinka','Sporadic',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (51,5,'1 vez por semana','Astean behin','1 time per week',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (52,5,'2 veces por semana','2 bider asteko','2 times per week',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (53,5,'Caminar TODOS los días','Ibili EGUNERO','Walk EVERYDAY',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (54,5,'3 veces o más por semana','Asteko 3 edo gehiago','3 times or more per week',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (55,5,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (56,5,'Competición','Lehiaketa','Competition',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (57,5,'Regular','Erregularra','Regular',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (58,5,'Irregular','Irregularra','Irregular',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (59,5,'< 2h por semana','< 2 ordu asteko','< 2h per week',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (60,5,'> 2h por semana','> 2 ordu asteko','> 2h per week',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (61,6,'Normal (7-8 horas)','Normala (7-8 ordu)','Normal (7-8 hours)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (62,6,'Insuficiente (6-7 horas)','Ez da nahikoa (6-7 ordu)','Insufficient (6-7 hours)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (63,6,'Deficiente ( < 6 horas)','Oso gutxi (<6 ordu)','Deficient (< 6 hours)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (64,6,'Insonmio','Insonmioa','Insomnia',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (65,6,'Trastornos del sueño','Lo egiteko arazoak','Sleep disorders',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (66,6,'Sin información','Informazio gabe','Without informing',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (67,6,'Excesivo (> 9 horas)','Gehiegizkoa (> 9 ordu)','Excesive (> 9 hour)',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (68,15,'Si','Bai','Yes',NULL,NULL,NULL,NULL,NULL);
INSERT INTO options_config(id,type,es,eu,en,ca,pt,it,de,ru) VALUES (69,15,'No','Ez','No',NULL,NULL,NULL,NULL,NULL);
CREATE TABLE values_config(
id int(255) auto_increment not null,
type int(255),
unit VARCHAR(30) NOT NULL,
min int(255),
max int(255),
CONSTRAINT pk_values_config PRIMARY KEY(id),
CONSTRAINT fk_values_config_type FOREIGN KEY(type) references type(id)
);
INSERT INTO values_config(id,type,unit,min,max) VALUES (1,7,'cm',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (2,8,'kg',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (3,9,'kg',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (4,10,'mg/DL',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (5,11,'mg/DL',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (6,12,'mm (Hg)',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (7,13,'mm (Hg)',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (8,14,'mg/DL',65,110);
INSERT INTO values_config(id,type,unit,min,max) VALUES (9,16,'cm',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (10,17,'%',35,47);
INSERT INTO values_config(id,type,unit,min,max) VALUES (11,18,'kg/m^2',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (12,19,'ppm',NULL,NULL);
INSERT INTO values_config(id,type,unit,min,max) VALUES (13,20,'ppm',NULL,NULL);
CREATE TABLE control(
id int(255) auto_increment not null,
created_at varchar(50) NOT NULL,
created_by int(255) NOT NULL,
config_type VARCHAR(10) NOT NULL,
config_type_id int(255),
type int(255),
value varchar(10),
extras VARCHAR(100),
CONSTRAINT pk_control PRIMARY KEY(id),
CONSTRAINT fk_control_type FOREIGN KEY(type) references type(id),
CONSTRAINT fk_control_created_at FOREIGN KEY(created_by) references user(id)
);
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (1,'4/25/2018 7:00:00',1,'v',12,19,'38','PPM reposo');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (2,'4/25/2018 7:00:00',1,'v',13,20,'180','PPM Max');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (3,'4/26/2018 7:00:00',1,'v',1,7,'186','Altura');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (4,'4/27/2018 7:00:00',1,'v',2,8,'70','Peso actual');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (5,'4/28/2018 7:00:00',1,'v',3,9,'71.5','Peso competición');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (6,'4/29/2018 7:00:00',1,'o',1,1,NULL,'Fumador');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (7,'4/30/2018 7:00:00',1,'o',14,3,NULL,'Alcohol');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (8,'5/1/2018 7:00:00',1,'o',25,4,NULL,'Cafe');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (9,'5/2/2018 7:00:00',1,'o',32,2,NULL,'Dieta');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (10,'5/3/2018 7:00:00',1,'o',56,5,NULL,'Ejercicio');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (11,'5/4/2018 7:00:00',1,'o',62,6,NULL,'Dormir');
INSERT INTO control(id,created_at,created_by,config_type,config_type_id,type,value,extras) VALUES (12,'5/5/2018 7:00:00',1,'o',69,15,NULL,'Diabetes');
CREATE TABLE section_type(
id int(255) auto_increment not null,
section int(255),
type int(255),
CONSTRAINT section_type_uniques_fields UNIQUE (section, type),
CONSTRAINT pk_section_type PRIMARY KEY(id),
CONSTRAINT fk_section_type FOREIGN KEY(type) references type(id),
CONSTRAINT fk_type_section FOREIGN KEY(section) references section(id)
);
INSERT INTO section_type(id,section,type) VALUES (1,1,1);
INSERT INTO section_type(id,section,type) VALUES (2,1,2);
INSERT INTO section_type(id,section,type) VALUES (3,1,3);
INSERT INTO section_type(id,section,type) VALUES (4,1,4);
INSERT INTO section_type(id,section,type) VALUES (5,1,5);
INSERT INTO section_type(id,section,type) VALUES (6,1,6);
INSERT INTO section_type(id,section,type) VALUES (7,3,7);
INSERT INTO section_type(id,section,type) VALUES (8,3,8);
INSERT INTO section_type(id,section,type) VALUES (9,3,9);
INSERT INTO section_type(id,section,type) VALUES (10,3,18);
CREATE TABLE sport(
id int(255) auto_increment not null,
resistance int(255),
es varchar(100),
eu varchar(100),
en varchar(100),
ca varchar(100),
pt varchar(100),
it varchar(100),
de varchar(100),
ru varchar(100),
CONSTRAINT pk_sport PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `sport` ADD UNIQUE( `es`);
INSERT IGNORE INTO `sport` (`id`, `es`, `eu`, `en`, `ca`, `pt`, `it`, `de`, `ru`) VALUES ('0', 'Sin seleccionar', 'Aukeratu gabe', 'Unselected', 'Ja no està seleccionat', NULL, NULL, NULL, NULL);
INSERT IGNORE INTO `sport` (`id`, `es`, `eu`, `en`, `ca`, `pt`, `it`, `de`, `ru`) VALUES
(1, 'Atletismo - Ruta', 'Atletismo - Asfaltoa', 'Running - Road', NULL, NULL, NULL, NULL, NULL),
(2, 'Natación', 'Igeriketa', 'Swimming', NULL, NULL, NULL, NULL, NULL),
(3, 'Ciclismo de carretera', 'Errepideko txirrindularitza', 'Road cycling', NULL, NULL, NULL, NULL, NULL),
(4, 'Mountain Bike', 'Mountain Bike', 'Mountain Bike', NULL, NULL, NULL, NULL, NULL),
(5, 'Triathlon', 'Triathlon', 'Triathlon', NULL, NULL, NULL, NULL, NULL),
(6, 'Crossfit', 'Crossfit', 'Crossfit', NULL, NULL, NULL, NULL, NULL),
(7, 'Fitness', 'Fitness', 'Fitness', NULL, NULL, NULL, NULL, NULL),
(8, 'Spinning', 'Spinning', 'Spinning', NULL, NULL, NULL, NULL, NULL),
(9, 'Rugby', 'Rugby', 'Rugby', 'Rugbi', 'Rúgbi', 'Rugby', 'Rugby', 'Pегби'),
(11, 'Baloncesto', 'Saskibaloia', 'Basketball', 'Bà squet', 'Basquete', 'Pallacanestro', 'Basketball', 'баскетбол'),
(12, 'Pelota mano', 'Esku pilota', 'Hands ball', 'Pilota de mans', 'Bola de mãos', 'Palla a mano', 'Hände Ball', 'Ручной шар'),
(21, 'Halterofilia', 'Halterofilia', 'Weightlifting', NULL, NULL, NULL, NULL, NULL),
(22, 'Fútbol', 'Futbola', 'Football', NULL, NULL, NULL, NULL, NULL),
(24, 'Nordic walking', 'Nordic walking', 'Nordic Walking', NULL, NULL, NULL, NULL, NULL),
(25, 'Escalada', 'Eskalatzea', 'Climbing', NULL, NULL, NULL, NULL, NULL),
(26, 'Balonmano', 'Eskubaloia', 'Handball', NULL, NULL, NULL, NULL, NULL),
(27, 'Squash', 'Squash', 'Squash', NULL, NULL, NULL, NULL, NULL),
(28, 'Waterpolo', 'Waterpolo', 'Water polo', NULL, NULL, NULL, NULL, NULL),
(29, 'Tenis', 'Tenisa', 'Tennis', 'Tennis', 'Ténis', 'Tennis', 'Tennis', 'Теннис'),
(30, 'Remo', 'Arraunketa', 'Rowing', 'Rem', 'Remo', 'Canottaggio', 'Rudern', 'Академическая гребля'),
(31, 'Boxeo', 'Boxeoa', 'Boxing', 'Boxa', 'Boxe', 'Pugilato', 'Boxen', 'Бокс'),
(32, 'Carreras por montaña', 'Mendi Lasterketak', 'Trail Running', NULL, NULL, NULL, NULL, NULL),
(33, 'Atletismo - Pista', 'Atletismoa - Pista', 'Athletics - Track', NULL, NULL, NULL, NULL, NULL),
(34, 'Atletismo - Cross', 'Atletismoa - Krosa', 'Athletics - Cross', NULL, NULL, NULL, NULL, NULL);
CREATE TABLE personal_best(
id int(255) auto_increment not null,
user_id int(255) COMMENT "Usuario",
sport_id int(255) COMMENT "Deporte seleccionado",
distance varchar(255) COMMENT "kms - Valor decimal",
elevation INT NOT NULL DEFAULT '0',
time varchar(100),
data varchar(40),
town int(255),
CONSTRAINT pk_personal_best PRIMARY KEY(id),
CONSTRAINT fk_personal_best_user FOREIGN KEY(user_id) references user(id),
CONSTRAINT fk_personal_best_sport FOREIGN KEY(sport_id) references sport(id),
CONSTRAINT fk_personal_best_town FOREIGN KEY (town) references town(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `personal_best` (`id`, `user_id`, `sport_id`, `distance`, `elevation`, `time`, `data`, `town`) VALUES
(1, 1, 1, '10', 0, '00:32:50', '2016-11-26', 968),
(2, 1, 1, '5', 65, '00:15:40', '2017-10-28', 2783),
(3, 1, 1, '7.5', 120, '00:24:29', '2017-09-16', 2783),
(4, 1, 1, '21.1', 110, '01:15:23', '2017-04-01', 968),
(5, 1, 32, '20', 400, '01:12:42', '2016-04-01', 5996),
(6, 1, 32, '9.4', 350, '00:34:01', '2016-08-14', 8569),
(7, 1, 32, '10', 410, '00:38:15', '2016-06-19', 1404),
(8, 1, 1, '10', 120, '00:32:54', '2016-11-05', 7228),
(9, 1, 1, '10.1', 0, '00:34:54', '2011-03-20', 2752),
(10, 1, 1, '10', 0, '00:35:08', '2010-09-26', 5348),
(11, 1, 32, '22.3', 1050, '01:49:38', '2011-06-05', 5035),
(12, 1, 1, '10.1', 0, '00:36:02', '2010-03-21', 2752),
(13, 1, 1, '20.2', 190, '01:12:42', '2015-10-03', 8800),
(14, 1, 1, '7.5', 85, '00:24:00', '2017-10-28', 2783),
(15, 1, 32, '41', 2000, '04:07:55', '2010-09-05', 4611),
(16, 1, 1, '21.1', 0, '01:19:41', '2010-05-16', 2752);
--
-- Objetive
--
CREATE TABLE objetive_type(
id int(255) auto_increment not null,
name_es varchar(255) DEFAULT NULL COMMENT 'Tipo de objetivo',
description_es varchar(255) DEFAULT NULL COMMENT 'Proposito de ese objectivo mas detallado',
name_eu varchar(255) DEFAULT NULL,
description_eu varchar(255) DEFAULT NULL,
name_en varchar(255) DEFAULT NULL,
description_en varchar(255) DEFAULT NULL,
photo varchar(255) DEFAULT NULL,
add_by int(255),
CONSTRAINT pk_objective_type PRIMARY KEY(id),
CONSTRAINT fk_objective_type_add_by_user FOREIGN KEY(add_by) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `objetive_type` (`id`, `name_es`, `description_es`, `name_eu`, `description_eu`, `name_en`, `description_en`, `photo`, `add_by`) VALUES
(1, 'Descanso', 'Sesión de descanso o descanso activo con una actividad sin impacto', 'Atsedena', 'Atseden saioa edo saio arin bat inpakto gabeko kirolekin (Ibili, igeriketa,...)', 'Rest', 'Rest or active rest session with an activity without impact', NULL, 1),
(4, 'Series cortas', 'Sesión de series cortas (menos de 1000m)', 'Serie laburrak', 'Serie laburrak (1000 metro baino gutxiago).', 'Short series', 'Session of short series (less than 1000m)', NULL, 1),
(5, 'Cambios de ritmo', 'Cambios de ritmo con fartleks', 'Erritmo aldaketak', 'Fartlek bidezko erritmo aldaketak', 'Changes of rhythm', 'Changes of rhythm with fartleks', NULL, 1),
(6, 'Rodaje fuerte', 'Rodaje a ritmo alto durante 60-90 min', 'Irteera errimo fuertean', '60-90 minutu bitarteko korrikaldi erritmo nahiko altuan', 'Strong shooting', 'High-speed shooting for 60-90 min', NULL, 1);
CREATE TABLE objetive(
id int(255) auto_increment not null,
user_id int(255),
objetive_type int (255),
name varchar(255) COMMENT "Que se quiere conseguir",
description varchar(255) COMMENT "Mas detalles",
data varchar(255),
ok int (2) COMMENT "Conseguido o no. Por defecto 0",
CONSTRAINT pk_objective PRIMARY KEY(id),
CONSTRAINT fk_objective_user FOREIGN KEY(user_id) references user(id),
CONSTRAINT fk_objective_objective_type FOREIGN KEY(objetive_type) references objetive_type(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
--
-- Videos
--
CREATE TABLE video(
id int(255) auto_increment not null,
video_id varchar(100) NOT NULL,
add_by_user int(255) COMMENT "Añadido por el usuario",
name varchar(255),
description varchar(255),
source varchar(100) COMMENT "Youtube, Vimeo,...",
photo varchar(255) COMMENT "Avatar del video",
time varchar(100) COMMENT "HH:MM:SS",
CONSTRAINT pk_video PRIMARY KEY(id),
CONSTRAINT fk_video_user FOREIGN KEY(add_by_user) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE video
ADD UNIQUE KEY video_id (video_id);
CREATE TABLE playlist(
id int(255) auto_increment not null,
add_by_user int(255) COMMENT "Añadido por el usuario",
name varchar(255),
description varchar(255),
photo varchar(255) COMMENT "Avatar del canal",
time varchar(100) COMMENT "HH:MM:SS",
created_at varchar(255) COMMENT "Fecha de creación",
updated_at varchar(255) COMMENT "Fecha de actualización",
CONSTRAINT pk_playlist PRIMARY KEY(id),
CONSTRAINT fk_playlist_user FOREIGN KEY(add_by_user) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `playlist` ADD `playlist_id` VARCHAR(100) NOT NULL AFTER `id`, ADD UNIQUE (`playlist_id`);
INSERT IGNORE INTO `playlist` (`id`, `playlist_id`, `add_by_user`, `name`, `description`, `photo`, `time`, `created_at`, `updated_at`) VALUES
(0, 'NOTHING', 1, NULL, NULL, NULL, NULL, NULL, NULL),
(1, 'PLIY27hyIDRZCRa28fkDWeSft1ZdG-XnTj', 2, NULL, NULL, NULL, NULL, NULL, NULL),
(2, 'PLIY27hyIDRZCcMHqCD8FC9jHvXhjDPIyc', 2, NULL, NULL, NULL, NULL, NULL, NULL),
(3, 'PLIY27hyIDRZAf_qWOrykp3eZrnE3RU5yQ', 2, NULL, NULL, NULL, NULL, NULL, NULL);
CREATE TABLE video_playlist(
id int(255) auto_increment not null,
playlist_id int(255),
video_id int(255),
CONSTRAINT pk_video_playlist PRIMARY KEY(id),
CONSTRAINT fk_video_playlist_playlist FOREIGN KEY(playlist_id) references playlist(id),
CONSTRAINT fk_video_playlist_video FOREIGN KEY(video_id) references video(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
--
-- Trainer plan
--
CREATE TABLE trainer_plan(
id int(255) auto_increment not null,
name varchar(255),
description varchar(255),
description_eu varchar(255) COMMENT "Descripción en euskera",
description_en varchar(255) COMMENT "Descripción en Inglés",
photo varchar(255) COMMENT "Foto",
url VARCHAR(255) NULL DEFAULT NULL COMMENT 'URL info de la carrera u objectivo',
CONSTRAINT pk_trainer_plan PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `trainer_plan` (`id`, `name`, `description`, `description_eu`, `description_en`, `photo`, `url`) VALUES
(1, 'Maratón Donostia', 'Plan de entrenamiento para el maratón de Donostia SS', 'Donostiako maratoiarentzat entrenamendu plana', 'Training plan for the Donostia SS marathon', NULL, 'http://www.zurichmaratondonostia.com/maratondonostia/portada.asp'),
(2, 'Plan 10 kms de Castro Urdiales', 'Plan para la carrera de los 10kms de Castro Urdiales que se celebra en septiembre.', 'Irailean ospatzen den Castro Urdiales-eko 10km-tarako entrenamendu plana.', 'Plan for the race of the 10kms of Castro Urdiales that is celebrated in September.', NULL, 'http://www.10kmcastrourdiales.es/'),
(3, '10kms Azkoitia', 'Carrera de 10kms celebrada el último sábado de Noviembre en Azkoitia.', 'Azkoitian azaroko azken larunbatean ospatzen den 10km-ko lasterketa.', '10kms race held on the last Saturday of November in Azkoitia.', NULL, 'https://www.rakamaraton.com/herri-lasterketa/'),
(4, 'Jabali Trail Corta', 'Preparación para la Jabalí Trail corta, carrera que se celebra en Villasuso de Mena (Burgos) a mediados de agosto y que consta de 9,2kms y 350m+', 'Jabali Trail motzeko lasterketa prestatzeko entrenamendu plana. Lasterketa hau 9.2-ko distantzia eta 350m+-ko malda du. Abuztu erdialdean ospatzen da Burgos-eko Villasuso de Mena herrian', 'Preparation for the Jabalí Trail short, race that takes place in Villasuso de Mena (Burgos) in mid-August and that consists of 9,2kms and 350m +', NULL, 'http://jabalitrail.blogspot.com.es/'),
(5, 'Jabali Trail Larga (MM)', 'Preparación para la Jabalí Trail larga, carrera que se celebra en Villasuso de Mena (Burgos) a mediados de agosto y que consta de 21kms y 900m+', 'Jabali Trail luzeko lasterketa prestatzeko entrenamendu plana. Lasterketa hau 21km-ko distantzia eta 900m+-ko malda du. Abuztu erdialdean ospatzen da Burgos-eko Villasuso de Mena herrian', 'Preparation for the Jabalí Trail half marathon, race that takes place in Villasuso de Mena (Burgos) in mid-August and that consists of 21kms and 900m +', NULL, 'http://jabalitrail.blogspot.com.es/'),
(6, 'Behobia - San Sebastian', 'La Behobia-San Sebastián es más que una carrera. La Behobia-San Sebastián es ante todo un sentimiento. Un sentimiento forjado desde hace muchos años en la ilusión, en las sensaciones y en el esfuerzo de muchos miles de atletas como tú y en la acogida...', 'Behobia-Donostia lasterketa bat baino gehiago da. Behobia-Donostia batez ere sentimendu bat da. Urte askotan zu bezalako milaka lasterkariren ilusio, sentsazio eta esfortzuek sortutako sentimendua eta, beste gutxik bezala, kirola ulertzen,...', 'The Behobia-San Sebastián is more than just a race. Above all, the Behobia-San Sebastián is a feeling, which has been built up over many years thanks to the effort, sensations and enthusiasm of thousands of athletes like you and the hosting of a land...', NULL, 'https://www.behobia-sansebastian.com/index.php'),
(7, 'Maratón Valencia', 'La primera maratón que se celebró en Valencia fue el 29 de marzo de 1981.\n\nLa Historia del maratón en nuestra ciudad no es otra que la del Maratón Popular de Valencia cuya primera edición se celebró el 29 de marzo de 1981, a la que siguieron,...', '1981 urtean lehenbiziz ospatu zen Valentziako maratoia Espainiako ranking-ean lehena izanik eta 2016ko urtarriletik IAAF-eko urrezko label-a duena.', 'The Valencia Marathon (Spanish: Maratón de Valencia) also known as Marathon Valencia Trinidad Alfonso EDP for sponsorship reasons, is an annual road running event over the marathon distance (42.195 km or 26.219 miles) that takes place in the city...', NULL, 'https://www.valenciaciudaddelrunning.com/maraton/maraton/'),
(8, 'Bizkaia Kopa Trail', 'Liga popular de carreras de trail (corto/largo), MTB y canicross que consta de 8 etapas en 8 municipios diferentes de Bizkaia.', 'Trail (motza/luzea), MTB eta kanikroseko txapelketa herrikoia 8 lasterketarekin Bizkaiko 8 udalerri desberdinetan ospatzen dena.', 'Popular league of trail races (short / long), MTB and canicross that consists of 8 stages in 8 different municipalities of Bizkaia.', NULL, 'http://www.bizkaiakopatrail.com/'),
(9, '15 kms Donostia - Clásica de San Sebastian', 'Carrera que se suele celebrar anualmente a mediados de octubre en Donostia - San Sebastian (Gipuzkoa) cuya distancia es de 15kms completamente llanos', 'Donostian (Gipuzkoa) urtero urriaren erdialdean ospatzen den lauak diren 15km-ko lasterketa.', 'Race that is usually held annually in mid-October in Donostia - San Sebastian (Gipuzkoa) whose distance is 15kms completely flat', NULL, 'http://www.15kmsansebastian.com/'),
(10, 'Berlin Marathon', 'Uno de los maratones más rápidos del mundo celebrado anualmente en la capital Alemana sobre mediados de septiembre', 'Alemaniako hiriburuan irailaren erdialdean urtero ospatzen den munduko maratoirik azkarrenetakoa', 'One of the fastest marathons in the world held annually in the German capital about mid-September', NULL, 'https://www.bmw-berlin-marathon.com/en/'),
(11, 'Ehun Milak', '100 millas (168m+) y 11000m+ de aventura partiendo desde Beasain y llegando a Beasain. Carrera celebrada a principios de julio anualmente desde el 2010', 'Beasainen irten eta Beasainen amaitzen den 100 milako (168km) eta 11000m+-ko malda duen lasterketa. Lasterketa 2010etik urtero uztailaren hasieran ospatzen da.', '100 miles (168m+) and 11000m+ of adventure starting from Beasain and arriving in Beasain. Race held at the beginning of July annually since 2010', NULL, 'http://www.ehunmilak.com/es/');
CREATE TABLE trainer_plan_sport(
id int(255) auto_increment not null,
trainer_plan int(255) COMMENT "Plan de entrenamiento",
sport int(255) COMMENT "Sport id.",
principal int(2),
CONSTRAINT pk_trainer_plan_sport PRIMARY KEY(id),
CONSTRAINT fk_trainer_plan_with_sport FOREIGN KEY(trainer_plan) references trainer_plan(id),
CONSTRAINT fk_sport_trainer_plan FOREIGN KEY(sport) references sport(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE trainer_plan_user(
id int(255) auto_increment not null,
trainer_plan int(255) COMMENT "Plan de entrenamiento",
trainer_id int(255) COMMENT "User id. Responsible to plan",
created_at varchar(255) COMMENT "Create datatime",
client_id int(255) COMMENT "User id. Client. Receive trainer by Trainer",
start_data varchar(255),
finish_data varchar(255),
active int(2),
CONSTRAINT pk_trainer_plan PRIMARY KEY(id),
CONSTRAINT fk_trainer_plan_to_user FOREIGN KEY(trainer_plan) references trainer_plan(id),
CONSTRAINT fk_plan_responsible_id FOREIGN KEY(trainer_id) references user(id),
CONSTRAINT fk_plant_client_id FOREIGN KEY(client_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO `trainer_plan_user` (`id`, `trainer_plan`, `trainer_id`, `created_at`, `client_id`, `start_data`, `finish_data`, `active`) VALUES
(4, 4, 2, '2018-04-04 18:20:50', 1, '2018-06-04', '2018-08-12', 0),
(5, 7, 1, '2018-04-04 19:01:48', 2, '2018-04-02', '2018-05-20', 0),
(6, 1, 1, '2018-04-04 19:04:51', 3, '2018-04-16', '2018-08-26', 0),
(7, 2, 2, '2018-04-04 19:08:52', 3, '2018-03-25', '2018-04-08', 0),
(8, 1, 1, '2018-04-04 19:09:15', 3, '2018-04-04', '2018-04-29', 0),
(9, 6, 2, '2018-04-04 19:17:42', 1, '2018-07-02', '2018-11-11', 0),
(10, 7, 1, '2018-04-04 19:21:37', 1, '2018-04-04', '2018-04-08', 0),
(11, 1, 1, '2018-04-04 19:26:55', 1, '2018-04-04', '2018-04-08', 0),
(12, 1, 1, '2018-04-04 19:28:27', 2, '2018-04-04', '2018-04-08', 1),
(13, 1, 1, '2018-04-04 19:37:15', 2, '2018-04-04', '2018-04-08', 0),
(14, 1, 2, '2018-04-04 19:38:21', 1, '2018-04-04', '2018-04-08', 1),
(15, 8, 1, '2018-04-04 19:45:16', 2, '2018-04-04', '2018-04-08', 0),
(16, 1, 1, '2018-04-04 20:01:57', 1, '2018-04-04', '2018-04-08', 0),
(17, 1, 1, '2018-04-05 17:38:47', 3, '2018-04-05', '2018-04-09', 1);
CREATE TABLE trainer_week(
id int(255) auto_increment not null,
trainer_plan_user int(255) COMMENT "Plan de entrenamiento asignado al usuario",
week int(255) COMMENT "Semana",
start_data varchar(255),
finish_data varchar(255),
CONSTRAINT pk_trainer_week PRIMARY KEY(id),
CONSTRAINT fk_trainer_week_in_plan FOREIGN KEY(trainer_plan_user) references trainer_plan_user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `trainer_week` ADD `active` INT(255) NOT NULL DEFAULT '0' AFTER `finish_data`;
INSERT IGNORE INTO `trainer_week` (`id`, `trainer_plan_user`, `week`, `start_data`, `finish_data`) VALUES
(1, 12, 1, '2018-04-02', '2018-04-09'),
(2, 14, 1, '2018-04-02', '2018-04-09'),
(3, 12, 2, '2018-04-06', '2018-04-10'),
(4, 12, 3, '2018-04-06', '2018-04-10'),
(5, 17, 5, '2018-04-10', '2018-04-14'),
(6, 17, 10, '2018-04-15', '2018-04-23');
CREATE TABLE trainer_session(
id int(255) auto_increment not null,
train_week int(255),
day int(255) COMMENT "Día de la semana, desde day_week",
data varchar(255),
break int(2) COMMENT "Descanso : 1 / No descanso = 0",
sport int(255) COMMENT "Deporte que se practica en la sesion",
warming varchar(255) COMMENT "Calentamiento",
tc int(2) COMMENT "Tecnica de carrera, si o no",
principal mediumtext COMMENT "Explicacion del entrenamiento",
objetive_type int(255) COMMENT "Objetivo que se persigue en la sesion",
kms int(255) COMMENT "Kms totales",
d_plus int(255) DEFAULT 0,
time VARCHAR(30),
cooling varchar(255) COMMENT "Enfriamiento",
type_video varchar(255) COMMENT "video o una lista de videos",
playlist_id int(255) COMMENT "Identificador de tabla video_playlist",
video_id int(255) COMMENT "Identificador de tabla video",
CONSTRAINT pk_trainer_session PRIMARY KEY(id),
CONSTRAINT fk_trainer_session_sport FOREIGN KEY(sport) references sport(id),
CONSTRAINT fk_trainer_session_day FOREIGN KEY(day) references week_day(id),
CONSTRAINT fk_trainer_session_objective_type FOREIGN KEY(objetive_type) references objetive_type(id),
CONSTRAINT fk_trainer_session_playlist FOREIGN KEY (playlist_id) REFERENCES playlist(id),
CONSTRAINT fk_trainer_session_video FOREIGN KEY (video_id) REFERENCES video(id),
CONSTRAINT fk_trainer_week_day FOREIGN KEY(train_week) references trainer_week(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `trainer_session` ADD `comments` INT NOT NULL DEFAULT NULL COMMENT 'Añadir notas / comentarios acerca de la sesión' AFTER `cooling`;
ALTER TABLE `trainer_session` ADD `hard_estimate` INT NOT NULL DEFAULT '5' COMMENT 'Para asignar la estimación del esfuerzo que haría o facilidad objetiva que debería de tener para completar el entrenamiento' AFTER `data`;
CREATE TABLE log_file(
id int(255) auto_increment not null,
name varchar(255) COMMENT "Nombre del fichero",
add_data varchar(255) COMMENT "Cuando se ha añadido",
CONSTRAINT pk_log_file PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE train_session_result(
id int(255) auto_increment not null,
train_session_id int(255),
weather varchar(255),
feelings varchar(255),
log_file_id int(255) COMMENT "Ficheros de la actividad",
CONSTRAINT pk_trainer_session_result PRIMARY KEY(id),
CONSTRAINT fk_trainer_session_result_session FOREIGN KEY(train_session_id) references trainer_session(id),
CONSTRAINT fk_trainer_session_result_log_files FOREIGN KEY(log_file_id) references log_file(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `train_session_result` ADD `kms` DOUBLE NOT NULL AFTER `train_session_id`, ADD `d_plus` INT NOT NULL AFTER `kms`, ADD `time` VARCHAR(50) NOT NULL AFTER `d_plus`, ADD `heart_rate_avg` INT NOT NULL AFTER `time`;
ALTER TABLE `train_session_result` ADD `ground` VARCHAR(255) NOT NULL AFTER `heart_rate_avg`;
CREATE TABLE trainer_period_objetive(
id int(255) auto_increment not null,
period varchar(80) COMMENT 'ALL, WEEK, SESSION',
t_plan_week_session_id int(255) COMMENT 'Plan, Week or session ID (plan)',
type varchar(80) COMMENT 'ALL, WEEK, SESSION, RACE',
objetive int(255),
add_by int(255),
CONSTRAINT fk_trainer_period_objetive_add_by FOREIGN KEY(add_by) references user(id),
CONSTRAINT pk_trainer_period_objective PRIMARY KEY(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `trainer_period_objetive` CHANGE `objetive` `objetive` INT(255) NULL DEFAULT NULL COMMENT 'el id lo cogemos desde la tabla objetive cuando es un objetivo definido como hacer una marca en una carrera o un tipo de objetivo como coger resistencia trabajando esa semana';
CREATE TABLE feedback(
id int(255) auto_increment not null,
trainer_type varchar(255) COMMENT "Plan, week, session",
type_id int(255),
emitter_user_id int(255) COMMENT "Usuario que envia",
receiver_user_id int(255) COMMENT "Usuario que recibe",
message mediumtext,
sleep_hours int(2) COMMENT 'Horas dormidas',
feel int(255) COMMENT '0: Horrorosas - 10: Cojonudas',
created_at varchar(255) COMMENT "Cuando se escribe",
readed int(2),
readed_at varchar(255) COMMENT "Cuando se ha leido",
extras varchar(255) COMMENT "Más datos",
CONSTRAINT pk_feedback PRIMARY KEY(id),
CONSTRAINT fk_emmiter_feedback FOREIGN KEY(emitter_user_id) references user(id),
CONSTRAINT fk_receiver_feedback FOREIGN KEY(receiver_user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
--
-- Events: Races, meetings, travells,...
--
CREATE TABLE event(
id int(255) auto_increment not null,
name varchar(255),
description varchar(255),
data varchar(100) COMMENT "AA/MM/DD",
time varchar(100) COMMENT "HH:MM:SS",
source varchar(100) COMMENT "Url to info",
photo varchar(255) COMMENT "Foto",
created_at varchar(255) COMMENT "Fecha de creación",
updated_at varchar(255) COMMENT "Fecha de actualización",
town_id int(255),
contact_email varchar(255),
telephone varchar(255),
paid int(2),
price varchar(255),
CONSTRAINT pk_event PRIMARY KEY(id),
CONSTRAINT fk_event_town FOREIGN KEY(town_id) references town(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE meeting(
id int(255) auto_increment not null,
event_id int(255),
requisites mediumtext,
CONSTRAINT pk_meeting PRIMARY KEY(id),
CONSTRAINT fk_meeting_event FOREIGN KEY(event_id) references event(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE talk(
id int(255) auto_increment not null,
event_id int(255),
topics mediumtext,
spectators_limit int(255) COMMENT "Aforo máximo",
CONSTRAINT pk_talks PRIMARY KEY(id),
CONSTRAINT fk_talks_event FOREIGN KEY(event_id) references event(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE race(
id int(255) auto_increment not null,
event_id int(255),
sport_id int(255) COMMENT "Deporte seleccionado",
distance varchar(255) COMMENT "kms",
elevation_plus varchar(30) COMMENT "desnivel +",
route_id varchar(50) COMMENT "Wikiloc / GPSies / Ibilbideak route id",
route_source varchar(255) COMMENT "Source of route",
CONSTRAINT pk_race PRIMARY KEY(id),
CONSTRAINT fk_race_event FOREIGN KEY(event_id) references event(id),
CONSTRAINT fk_race_sport FOREIGN KEY(sport_id) references sport(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE objetive_race(
id int(255) auto_increment not null,
objetive_id int(255),
race_id int(255) COMMENT "Si tiene carrera asociada",
days int(2) COMMENT "Días del viaje",
planning_details mediumtext COMMENT "Detalles sobre el viaje",
CONSTRAINT pk_objective_race PRIMARY KEY(id),
CONSTRAINT fk_objective_id_in_race FOREIGN KEY(objetive_id) references objetive(id),
CONSTRAINT fk_objective_in_race FOREIGN KEY(race_id) references race(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE travel(
id int(255) auto_increment not null,
event_id int(255),
race_id int(255) COMMENT "Si tiene carrera asociada",
days int(2) COMMENT "Días del viaje",
planning_details mediumtext COMMENT "Detalles sobre el viaje",
CONSTRAINT pk_travel PRIMARY KEY(id),
CONSTRAINT fk_travel_event FOREIGN KEY(event_id) references event(id),
CONSTRAINT fk_travel_race FOREIGN KEY(race_id) references race(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE user_in_event(
id int(255) auto_increment not null,
event_id int(255),
user_id int(255),
add_data varchar(255) COMMENT 'Cuando se ha añadido',
CONSTRAINT pk_meeting PRIMARY KEY(id),
CONSTRAINT fk_user_in_event FOREIGN KEY(event_id) references event(id),
CONSTRAINT fk_event_in_user FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
--
-- Material: Shoes, Clock, Bike,...
--
CREATE TABLE clock(
id int(255) auto_increment not null,
brand varchar(150),
model varchar(150),
description mediumtext,
url varchar(255) COMMENT "URL info del producto",
photo varchar(255),
gps int(2),
tracking_route int(2),
heart int(2),
add_by int(255),
CONSTRAINT pk_clock PRIMARY KEY(id),
CONSTRAINT clock_uniques_fields UNIQUE (brand, model),
CONSTRAINT fk_add_user_clock FOREIGN KEY(add_by) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `clock` ADD `created_at` DATETIME NOT NULL AFTER `add_by`;
INSERT IGNORE INTO `clock` (`id`, `model`, `description`, `url`, `photo`, `gps`, `tracking_route`, `heart`, `add_by`, `created_at`) VALUES
(1, 'Suunto Ambit2 Run', 'GPS integrado con monitorización de la frecuencia cardíaca, funciones de clima y Suunto Apps para todos los deportes al aire libre', 'http://www.suunto.com/es-ES/Productos/Relojes-deportivos/Suunto-Ambit2/Suunto-Ambit2-Black/', 'suunto_ambit_run_2.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(2, 'Garmin Fenix 3', 'El nuevo reloj multideporte con GPS diseñado para resistir', 'https://buy.garmin.com/es-ES/ES/p/160512', 'garmin_fenix_3.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(3, 'Garmin Forerunner 305', 'La combinación perfecta entre diseño y funcionalidad está presente en la gama de entrenadores personales de alto rendimiento de Garmin: los modelos Forerunner 205, 305 y 405. El Forerunner 305 incluye un receptor GPS de alta sensibilidad,...', 'https://buy.garmin.com/es-ES/ES/p/349', 'garmin_forerunner_305.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(4, 'Garmin Forerunner 235', 'Reloj de running GPS que mide la frecuencia cardiaca a través de la muñeca', 'https://buy.garmin.com/es-ES/ES/p/529988', 'garmin_forerunner_235.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(5, 'Suunto Ambit3 Run', 'El reloj GPS para correr con monitorización de la frecuencia cardíaca y conexión móvil', 'http://www.suunto.com/es-ES/Productos/Relojes-deportivos/Suunto-Ambit3-Run/Suunto-Ambit3-Run-Black/', 'suunto_ambit3_run.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(7, 'Garmin Forerunner 310XT', 'Dispositivo multideportivo GPS para agua y tierra', 'https://buy.garmin.com/es-ES/ES/p/27335', 'garmin_forerunner_310xt.png', 1, 1, 1, 1, '2018-04-01 10:00:00'),
(8, 'iPhone 5s', 'El iPhone 5s es un teléfono inteligente de gama alta, desarrollado por la compañía estadounidense Apple. Fue presentado como la séptima generación de iPhone junto al iPhone 5c el 10 de septiembre de 2013, siendo el sucesor del iPhone 5.', 'https://es.m.wikipedia.org/wiki/IPhone_5s', 'iphone_5s.png', 1, 1, 0, 1, '2018-04-01 17:35:34'),
(9, 'Suunto Spartan Ultra', 'El reloj GPS con pantalla táctil en color y monitorización de frecuencia cardíaca para multideporte atlético y de aventura', 'http://www.suunto.com/es-ES/Productos/Relojes-deportivos/Suunto-Spartan-Ultra/Suunto-Spartan-Ultra-All-Black-Titanium/', 'suunto_spartan_ultra.png', 1, 1, 1, 1, '2018-04-01 18:11:39'),
(10, 'Suunto Spartan Trainer', 'Reloj GPS deportivo delgado y ligero para el entrenamiento versátil y el estilo de vida activo', 'http://www.suunto.com/es-ES/Productos/Relojes-deportivos/suunto-spartan-trainer-wrist-hr/suunto-spartan-trainer-wrist-hr-black/', 'suunto_spartan_trainer.png', 1, 1, 1, 1, '2018-04-01 18:05:42'),
(11, 'Suunto Core', 'El reloj para outdoor con altímetro, barómetro y brújula, en una resistente carcasa de material compuesto.', 'https://www.suunto.com/es-ES/Productos/Relojes-deportivos/Suunto-Core/Suunto-Core-All-Black/', 'suunto_core.png', 1, 0, 0, 1, '2018-04-01 18:47:26'),
(12, 'Polar M400', 'Reloj de entrenamiento con GPS integrado y registro de actividad con sensor de frecuencia cardíaca H7', 'https://support.polar.com/es/ayuda/M400', 'polar_m400.png', 1, 0, 1, 1, '2018-04-10 18:44:16'),
(13, 'Garmin Forerunner 935', 'Reloj para carrera/triatlón con GPS y sensor de frecuencia cardiaca en la muñeca', 'https://buy.garmin.com/es-ES/ES/p/564291', 'garmin_forerunner_935.png', 1, 1, 1, 1, '2018-04-15 17:37:12'),
(14, 'Garmin Forerunner 735XT', 'Reloj multisport con GPS tecnología pulsómetro integrado Garmin Elevate', 'https://buy.garmin.com/es-ES/ES/p/541225', 'garmin_forerunner_735xt.png', 1, 1, 1, 1, '2018-04-15 17:45:48'),
(16, 'Garmin Vivo Sport', 'Monitor de actividad inteligente con sensor de frecuencia cardiaca en la muñeca y GPS', 'https://buy.garmin.com/es-ES/ES/p/574602', 'garmin_vivo_sport.png', 1, 0, 1, 1, '2018-04-15 18:57:00'),
(18, 'Garmin Forerunner 25', 'Reloj de carrera con GPS fácil de usar con notificaciones inteligentes', 'https://buy.garmin.com/es-ES/ES/p/164366', 'garmin_forerunner_25.png', 1, 1, 1, 1, '2018-04-15 19:53:35'),
(19, 'Garmin Forerunner 35', 'Reloj GPS con monitor de frecuencia cardiaca en la muñeca, monitor de actividad y notificaciones inteligentes', 'https://buy.garmin.com/es-ES/ES/p/552962', 'garmin_forerunner_35.png', 1, 1, 1, 1, '2018-04-15 19:55:02');
CREATE TABLE shoes(
id int(255) auto_increment not null,
brand VARCHAR(150) COMMENT "marca de las zapatillas",
model varchar(150) COMMENT "Modelo",
description mediumtext,
url varchar(255) COMMENT "URL info del producto",
photo varchar(255),
surface varchar(255),
footprint varchar(255),
add_by int(255),
CONSTRAINT pk_shoes PRIMARY KEY(id),
CONSTRAINT shoes_uniques_fields UNIQUE (brand, model),
CONSTRAINT fk_add_user_shoes FOREIGN KEY(add_by) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `shoes` ADD `created_at` DATETIME NOT NULL AFTER `add_by`;
INSERT IGNORE INTO `shoes` (`id`, `model`, `description`, `url`, `photo`, `surface`, `footprint`, `add_by`, `created_at`) VALUES
(1, 'Adidas Energy Boost', 'La Energy Boost es idónea para afrontar carreras de larga distancia por su rendimiento y diseño...', 'https://www.adidas.es/zapatilla-energy-boost/CP9539.html', 'adidas_energy_boost.png', 'trainner', 'neutral', 1, '2018-04-01 10:00:00'),
(2, 'Adidas Kanadia TR8', 'Su gran relación calidad-precio han convertido a la saga de las Kanadia en uno de los modelos superventas de adidas running para correr por la montaña. Especialmente para un nicho concreto como el de iniciación en el trail running, o bien para todos...', 'https://www.runnea.com/zapatillas-running/adidas/kanadia-tr-8/1710/', 'adidas_kanadia_tr8.png', 'trail', 'neutral', 1, '2018-04-01 18:09:14'),
(3, 'Asics Gel Fuji Trabuco 5', 'Cuando se trata de trail running, la colección Gel Fuji Trabuco de ASICS Running es uno de los grandes referentes para el corredor de montaña...', 'https://www.runnea.com/zapatillas-running/asics/gel-fuji-trabuco-5/1665/', 'asics_gel_fuji_trabuco_5.png', 'trail', 'neutral', 1, '2018-04-01 18:25:51'),
(4, 'Mizuno Wave Hayate 4', 'Las Mizuno Wave Hayate 4 están definidas como la zapatilla de trail running más ligera y dinámica, como la propia marca Mizuno indica. Con dichas características las Wave Hayate 4 han sido concebidas como un modelo mixto para correr por el monte,...', 'https://www.runnea.com/zapatillas-running/mizuno/wave-hayate-4/3515/', 'mizuno_wave_hayate_4.png', 'trainner', 'neutral', 1, '2018-04-07 08:08:32'),
(5, 'Dynafit Alpine Ultra', 'Alpine Running shoe for the core trail runner, with Vibram® Megagrip sole and rocker construction for more grip and dynamicity', 'https://blogs.forumsport.com/running/2018/04/10/test-dynafit-alpine-pro-respuesta-perfecta-en-los-terrenos-mas-tecnicos/', 'dynafit_alpine_ultra.png', 'trail', 'neutral', 1, '2018-04-15 08:14:42'),
(6, 'Adidas Adizero Boston 6', 'Precisión japonesa para correr con total comodidad', 'https://www.adidas.es/zapatilla-adizero-boston-6/BB6415.html', 'adidas_adizero_boston_6.png', 'trainner', 'neutral', 1, '2018-04-15 19:06:00'),
(7, 'Adidas Adizero Takumi Ren 3', 'Consigue tu mejor marca con el estilo de Omori y Mimura', 'https://www.adidas.es/zapatilla-adizero-takumi-ren-3/BB6428.html', 'adidas_adizero_takumi_ren_3.png', 'competition', 'neutral', 1, '2018-04-15 19:08:25'),
(8, 'Adidas Terrex trail Cross', 'Adidas Terrex Trail Cross SL, unas zapatillas de trail para conquistar los terrenos montañosos tanto a pie como en bicicleta. Este modelo de Adidas se ha convertido en un aliado perfecto para aquellos que necesitan un calzado transpirable, duradero ...', 'https://www.adidas.es/zapatilla-adidas-terrex-trail-cross-sl/CM7562.html', 'adidas_terrex_trail_cross.png', 'trail', 'neutral', 1, '2018-04-21 17:16:23'),
(9, 'Salomon Slab Sense Ultra', 'La zapatilla de los ultrafondistas. Desarrollada por y para la élite. La S/LAB SENSE ULTRA logra un equilibrio entre sensibilidad del terreno y protección.', 'https://www.salomon.com/sp/product/s-lab-sense-ultra.html', 'salomon_slab_sense_ultra.png', 'trail', 'neutral', 1, '2018-04-21 17:20:15'),
(10, 'Salomon Slab Sense 6', 'La cantidad justa de protección, sin peso innecesario.', 'https://www.salomon.com/sp/product/s-lab-sense-6.html', 'salomon_slab_sense_6.png', 'trail', 'neutral', 1, '2018-04-21 17:31:19'),
(11, 'Salomon Sense Ride', 'Cualquier día, cualquier sendero: la zapatilla ideal para rendir más.', 'https://www.salomon.com/sp/product/sense-ride.html?article=394743', 'salomon_sense_ride.png', 'trail', 'neutral', 1, '2018-04-21 17:32:59');
CREATE TABLE bike(
id int(255) auto_increment not null,
brand varchar(150),
model varchar(150),
description mediumtext,
url varchar(255) COMMENT "URL info del producto",
photo varchar(255),
surface varchar(255) COMMENT "Si es de carretera, MTB,...",
add_by int(255),
CONSTRAINT pk_bike PRIMARY KEY(id),
CONSTRAINT bike_uniques_fields UNIQUE (brand, model),
CONSTRAINT fk_add_user_bike FOREIGN KEY(add_by) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `bike` ADD `created_at` DATETIME NOT NULL AFTER `add_by`;
INSERT IGNORE INTO `bike` (`id`, `model`, `description`, `url`, `photo`, `surface`, `add_by`, `created_at`) VALUES
(1, 'KTM Strada 1000', 'Bicicleta de carretera de aluminio con Shimano 105 5701 GS, ruedas Shimano WH-R501.', 'https://www.ktm-bikes.at/en/bikes/bikedetail.html?action=bike_details&bike_id=132&cHash=daa45f32238e988ef69864dc732cc1b9', 'ktm_strada_1000.png', 'road', 1, '2018-04-01 10:00:00'),
(2, 'Orbea Ordu M35 2017', 'Un modelo con cuadro de carbono perfecto para inciarse en elste mundillo con las máximas garantías. Esta todo terreno será la envidia en tus competiciones de triatlón!', 'https://www.orbea.com/es-es/bicicletas/outlet/ordu-m35', 'orbea_ordu_m35_2017.png', 'road', 1, '2018-04-15 07:53:18'),
(3, 'Orbea Orca M30', 'Bicicleta de carretera Orca M30.', 'https://www.orbea.com/es-es/bicicletas/orca-m30-18', 'orbea_orca_m30.png', 'road', 1, '2018-04-22 06:39:41');
CREATE TABLE user_material(
id int(255) auto_increment not null,
type varchar(255) COMMENT 'Que material es, reloj, bici, zapatillas,...',
type_id int(255) COMMENT 'Id del material que se ha añadido',
user_id int(255),
add_data varchar(255) COMMENT 'Cuando se ha añadido',
CONSTRAINT pk_user_material PRIMARY KEY(id),
CONSTRAINT fk_material_user FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `user_material` ADD `extras` VARCHAR(255) NOT NULL COMMENT 'Para añadir información del material añadido como kms recorridos con las zapatillas' AFTER `add_data`;
--
-- Social Network
--
CREATE TABLE publication(
id int(255) auto_increment not null,
user_id int(255),
text mediumtext,
file varchar(100),
photo varchar(255),
status varchar(30),
created_at datetime,
CONSTRAINT pk_publication PRIMARY KEY(id),
CONSTRAINT fk_publication_user FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE friend(
id int(255) auto_increment not null,
user int(255),
followed int(255),
add_data VARCHAR(100),
CONSTRAINT pk_following PRIMARY KEY(id),
CONSTRAINT fk_following_user FOREIGN KEY(user) references user(id),
CONSTRAINT fk_followed FOREIGN KEY(followed) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE private_message(
id int(255) auto_increment not null,
subject varchar(100),
message longtext,
emitter int(255),
receiver int(255),
file varchar(255),
photo varchar(255),
readed varchar(3),
created_at datetime,
CONSTRAINT pk_private_message PRIMARY KEY(id),
CONSTRAINT fk_emmiter_private FOREIGN KEY(emitter) references user(id),
CONSTRAINT fk_receiver_private FOREIGN KEY(receiver) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE `like`(
id int(255) auto_increment not null,
`user` int(255),
`publication` int(255),
CONSTRAINT pk_like PRIMARY KEY(id),
CONSTRAINT fk_like_user FOREIGN KEY(user) references user(id),
CONSTRAINT fk_like_publication FOREIGN KEY(publication) references publication(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE notification(
id int(255) auto_increment not null,
user_id int(255),
type varchar(255),
type_id int(255),
readed varchar(3),
created_at datetime,
extra varchar(100),
CONSTRAINT pk_notification PRIMARY KEY(id),
CONSTRAINT fk_notification_user FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE app_info(
id int(255) auto_increment not null,
name varchar(255),
description varchar(255),
languages varchar(100) COMMENT 'Aquí se añaden los idiomas de los que dispondrá la aplicación web',
created_at varchar(100) COMMENT 'Fecha de inicio del proyecto',
updated_at varchar(100) COMMENT 'Fecha de la actualización de la información',
CONSTRAINT pk_app_info PRIMARY KEY(id))ENGINE = InnoDb COMMENT = 'Para guardar los datos generales de la aplicación' DEFAULT CHARSET=utf8mb4;
CREATE TABLE telegram_bot(
id int(255) auto_increment not null,
user_id int(255),
chat_id varchar(255),
user_tg int(255),
CONSTRAINT pk_telegram_bot PRIMARY KEY(id),
CONSTRAINT fk_telegram_bot FOREIGN KEY(user_id) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
CREATE TABLE faq(
id int(255) auto_increment not null,
title varchar(255) COMMENT "título del contenido",
content mediumtext COMMENT "Contenido de la información",
order_number int(2),
add_by int(255),
CONSTRAINT pk_faq PRIMARY KEY(id),
CONSTRAINT fk_faq_add_by_user FOREIGN KEY(add_by) references user(id)
)ENGINE = InnoDb DEFAULT CHARSET=utf8mb4;
ALTER TABLE `faq` ADD `type` VARCHAR(100) NULL DEFAULT NULL AFTER `title`;
INSERT IGNORE INTO `faq` (`id`, `title`, `content`, `order_number`, `add_by`) VALUES
(1, '¿Puede entrar al contenido de los artículos, videos,... sin ser cliente?', 'Si', 1, 1),
(2, '¿Puedo registrarme como entrenador y ser mi propio entrenador?', 'Si', 2, 1),
(3, '¿Hay periodo de prueba?', 'Si', 4, 1),
(4, '¿Puedo darme de baja cuando quiera?', 'Si', 5, 1);
CREATE TABLE enrollment(
id INTEGER NOT NULL PRIMARY KEY
,enrollment VARCHAR(2) NOT NULL
,province VARCHAR(21) NOT NULL,
CONSTRAINT pk_enrollment PRIMARY KEY(id))ENGINE = InnoDb DEFAULT CHARSET=utf8mb4 COMMENT = 'Para guardar los datos de los códigos de matrícula (antiguo)' DEFAULT CHARSET=utf8mb4;
);
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (1,'VI','Álava');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (2,'AB','Albacete (antes ALB)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (3,'A','Alicante');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (4,'AL','Almería');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (5,'AV','Ávila');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (6,'BA','Badajoz');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (7,'IB','Baleares (antes PM)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (8,'B','Barcelona');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (9,'BU','Burgos');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (10,'CC','Cáceres (antes CAC)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (11,'CA','Cádiz');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (12,'CE','Ceuta');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (13,'CS','Castellón (antes CAS)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (14,'CR','Ciudad Real');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (15,'CO','Córdoba');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (16,'C','La Coruña');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (17,'CU','Cuenca');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (18,'GI','Gerona (antes GE)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (19,'GR','Granada');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (20,'GU','Guadalajara');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (21,'SS','Guipúzcoa');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (22,'H','Huelva');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (23,'HU','Huesca');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (24,'J','Jaén');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (25,'LE','León');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (26,'L','Lérida');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (27,'LO','Logroño');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (28,'LU','Lugo');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (29,'M','Madrid');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (30,'MA','Málaga');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (31,'ML','Melilla');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (32,'MU','Murcia');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (33,'NA','Navarra (antes PA)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (34,'OU','Orense (antes OR)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (35,'O','Oviedo');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (36,'P','Palencia');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (37,'GC','Las Palmas');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (38,'PO','Pontevedra');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (39,'SA','Salamanca');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (40,'TF','Tenerife');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (41,'S','Santander');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (42,'SG','Segovia (antes SEG)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (43,'SE','Sevilla');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (44,'SO','Soria');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (45,'T','Tarragona');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (46,'TE','Teruel (antes TER)');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (47,'TO','Toledo');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (48,'V','Valencia');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (49,'VA','Valladolid');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (50,'BI','Vizcaya');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (51,'Z','Zaragoza');
INSERT IGNORE INTO enrollment(id,enrollment,province) VALUES (52,'ZA','Zamora');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment