CREATE TABLE IF NOT EXISTS prefix_site (
    id bigint(20) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    url char(255) NOT NULL UNIQUE KEY,
    title char(255) NOT NULL,
    description TEXT,
    founder bigint(20) UNSIGNED DEFAULT NULL,
    installed BOOLEAN DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_users (
    id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(128) NOT NULL,
    password VARCHAR(40) NOT NULL,
    salt VARCHAR(10) NOT NULL,
    first_name VARCHAR(255) DEFAULT '',
    last_name VARCHAR(255) DEFAULT '',
    email VARCHAR(255) DEFAULT NULL,
    language VARCHAR(6) NOT NULL DEFAULT 'et',
    registered DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    approved TINYINT(1) NOT NULL DEFAULT 0,
    role INT(1) NOT NULL DEFAULT 1,
    last_login DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    facebook_id BIGINT(20) DEFAULT NULL,
    activation_code VARCHAR(40) DEFAULT NULL,
    types TEXT,
    PRIMARY KEY (id),
    UNIQUE INDEX username (username)
) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_images (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED default NULL,
    name char(255) DEFAULT "",
    type char(255) DEFAULT "",
    size INT DEFAULT 0,
    width INT DEFAULT 0,
    height INT DEFAULT 0,
    location char(255) DEFAULT "",
    created TIMESTAMP DEFAULT NOW(),
    locked BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_user_trainings (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    title varchar(255),
    training_date DATE,
    beginning TIME,
    ending TIME,
    sleeping_time_in_min int(11),
    warmup_device varchar(255),
    warmup_duration_in_sec int(11),
    cardio_device varchar(255),
    cardio_duration_in_sec int(11),
    rating int(1),
    notes TEXT,
    series_count int(2) DEFAULT 1,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_user_nutritions (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    training bigint(20) UNSIGNED NOT NULL,
    food varchar(255),
    nutrition_time TIME,
    kcal DOUBLE(10,2) DEFAULT 0,
    proteins DOUBLE(10,2) DEFAULT 0,
    carbohydrates DOUBLE(10,2) DEFAULT 0,
    fat DOUBLE(10,2) DEFAULT 0,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE,
    FOREIGN KEY (training) REFERENCES prefix_user_trainings (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_user_exercises (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    training bigint(20) UNSIGNED NOT NULL,
    muscle_group int(2),
    exercise varchar(255),
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE,
    FOREIGN KEY (training) REFERENCES prefix_user_trainings (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_user_exercise_series (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    exercise bigint(20) UNSIGNED NOT NULL,
    number int(2),
    repetitions int(4) DEFAULT 0,
    kg double(4,1) DEFAULT 0,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE,
    FOREIGN KEY (exercise) REFERENCES prefix_user_exercises (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_training_plans (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    title varchar(255),
    keywords varchar(255),
    duration int(3),
    frequency int(1),
    notes TEXT,
    status ENUM('draft', 'final') DEFAULT 'draft',
    security ENUM('public', 'private') DEFAULT 'private',
    type varchar(24) DEFAULT '',
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_training_plan_days (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    training_plan bigint(20) UNSIGNED NOT NULL,
    day int(1),
    position int(2),
    muscle_group int(2),
    exercise varchar(255),
    series int(2) DEFAULT 0,
    repetitions varchar(64),
    break_length int(4) DEFAULT 0,
    break_unit ENUM('sec', 'min') DEFAULT 'sec',
    FOREIGN KEY(training_plan) REFERENCES prefix_training_plans (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_activated_training_plans (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    training_plan bigint(20) UNSIGNED NOT NULL,
    beginning DATE,
    ending DATE,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE,
    FOREIGN KEY (training_plan) REFERENCES prefix_training_plans (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_training_goals (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    title varchar(255),
    beginning_date DATE,
    ending_date DATE,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_user_goals (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED NOT NULL,
    training_goal bigint(20) UNSIGNED NOT NULL,
    body_part int(2),
    initial_value DOUBLE(4,1) DEFAULT 0,
    current_value DOUBLE(4,1) DEFAULT 0,
    final_goal DOUBLE(4,1) DEFAULT 0,
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE CASCADE,
    FOREIGN KEY (training_goal) REFERENCES prefix_training_goals (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_suggestions (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    creator bigint(20) UNSIGNED DEFAULT NULL,
    created TIMESTAMP DEFAULT NOW(),
    modified TIMESTAMP,
    link varchar(255),
    image bigint(20) UNSIGNED DEFAULT NULL,
    metadata TEXT,
    to_all int(1) default 0,
    visibility ENUM('visible', 'hidden') DEFAULT 'visible',
    clicked_count int(4) default 0, 
    FOREIGN KEY (creator) REFERENCES prefix_users (id) ON DELETE SET NULL,
    FOREIGN KEY (image) REFERENCES prefix_images (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE IF NOT EXISTS prefix_suggestion_relations (
    suggestion_id bigint(20) UNSIGNED NOT NULL,
    user_type_id bigint(20) UNSIGNED NOT NULL,
    FOREIGN KEY (suggestion_id) REFERENCES prefix_suggestions (id) ON DELETE CASCADE,
    PRIMARY KEY suggestion_rel (suggestion_id, user_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

