 'Myself', '-', '-', 'ACCEPTED', '2020-05-15 08:00', '3d2n hike with friends', FALSE, FALSE, FALSE, '2020-05-05 08:00'),
(13, 12, '2020-05-29 07:00', '2020-05-30 17:00', 2, 'Myself', '-', '-', 'ACCEPTED', '2020-05-15 08:00', '2d1n hike with friends', TRUE, FALSE, FALSE, '2020-05-05 08:00'),
(14, 13, '2020-05-29 07:00', '2020-05-30 17:00', 2, 'Myself', '-', '-', 'ACCEPTED', '2020-05-15 08:00', '2d1n hike with friends', TRUE, FALSE, FALSE, '2020-05-05 08:00');

CREATE TABLE IF NOT EXISTS hike_destination (
    hike_id INT UNSIGNED,
    trail_id INT UNSIGNED,
    FOREIGN KEY (trail_id) REFERENCES trails(id),
    FOREIGN KEY (hike_id) REFERENCES hikes(id)
);

INSERT INTO hike_destination VALUES
(1,7), (1,8), (1,9),
(2,1), (2,2), (2,3),
(3,1), (3,2), (3,3),
(4,1), (4,2),
(5,1), (5,2), (5,3),
(6,1), (6,2), (6,3),
(7,1), (7,2), (7,3),
(8,1), (8,2), (8,3),
(9,4), (9,5), (9,6),
(10,4), (10,5), (10,6),
(11,4), (11,5), (11,6),
(12,4), (12,5), (12,6),
(13,4), (13,5),
(14,4), (14,5);

CREATE TABLE IF NOT EXISTS checkin (
    hiker_id INT UNSIGNED,
    hike_id INT UNSIGNED UNIQUE,
    checkin_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hike_id, hiker_id), 
    FOREIGN KEY (hiker_id) REFERENCES account(id),
    FOREIGN KEY (hike_id) REFERENCES hikes(id)
);

INSERT INTO checkin VALUES
(2, 5, '2020-05-22 05:00'),
(3, 6, '2020-05-22 05:00'),
(5, 1, '2020-05-29 10:00'),
(6, 3, '2020-05-29 10:00'),
(7, 4, '2020-05-29 10:00'),
(8, 7, '2020-05-29 10:00'),
(12, 13, '2020-05-29 06:00'),
(13, 14, '2020-05-29 06:00');

-- track history hold history data of each tracker entry
-- used for statistic data along with hikes table
CREATE TABLE IF NOT EXISTS track_history (
    hiker_id INT UNSIGNED,
    hike_id INT UNSIGNED,
    record_time DATETIME,
    latpt DECIMAL(10, 8),
    lngpt DECIMAL(11, 8),
    elevation DECIMAL(8, 3),
    battery TINYINT,
    network SMALLINT,
    elapsed_time TIME,
    logtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hike_id, hiker_id, record_time),
    FOREIGN KEY (hiker_id) REFERENCES account(id),
    FOREIGN KEY (hike_id) REFERENCES hikes(id),
    INDEX(latpt, lngpt)
);

-- tracker holds current location of every hiker
-- create trigger to insert to track_history after inserting to tracker
CREATE TABLE IF NOT EXISTS tracker (
    hiker_id INT UNSIGNED,
    hike_id INT UNSIGNED,
    record_time DATETIME, -- time created on mobile app
    latpt DECIMAL(10, 8),
    lngpt DECIMAL(11, 8),
    elevation DECIMAL(8, 3),
    battery TINYINT,
    network SMALLINT,
    elapsed_time TIME,
    logtime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, -- time this record is inserted to db
    PRIMARY KEY (hiker_id),
    FOREIGN KEY (hiker_id) REFERENCES account(id),
    FOREIGN KEY (hike_id) REFERENCES hikes(id),
    INDEX(latpt, lngpt)
);

DROP TRIGGER IF EXISTS be4_ins_tracker;
DELIMITER //
CREATE TRIGGER be4_ins_tracker BEFORE INSERT ON tracker FOR EACH ROW
BEGIN
    INSERT INTO track_history(hiker_id, hike_id, record_time, latpt, lngpt, elevation, battery, network, elapsed_time, logtime)
    VALUES(NEW.hiker_id, NEW.hike_id, NEW.record_time, NEW.latpt, NEW.lngpt, NEW.elevation, NEW.battery, NEW.network, NEW.elapsed_time, NEW.logtime);
END//
DELIMITER ;


INSERT INTO tracker(hike_id, hiker_id, record_time, latpt, lngpt, elevation, battery, network, elapsed_time, logtime) VALUES
    (2, 5, '2020-05-22 05:00', 23.4739179,120.9001783, 2609.0, 95, -67, '00:00:05', '2020-05-22 05:00:01'), -- tataka
    (3, 6, '2020-05-22 05:00', 23.4739177,120.9001780, 2609.0, 95, -67, '00:00:05', '2020-05-22 05:00:01'), -- tataka
    (2, 5, '2020-05-22 09:05', 23.46739,120.9381274, 3415.0, 86, -70, '04:05:07', '2020-05-22 09:05:01'), -- front
    (3, 6, '2020-05-22 09:05', 23.46739,120.9381274, 3415.0, 86, -70, '04:05:07', '2020-05-22 09:05:01'), -- front
    (2, 5, '2020-05-22 12:05', 23.4668906,120.9465227, 3420.0, 80, -61, '06:05:07', '2020-05-22 12:05:01'), -- paiyun
    (3, 6, '2020-05-22 12:05', 23.4668907,120.9465228, 3420.0, 80, -61, '06:05:07', '2020-05-22 12:05:01'), -- paiyun
    (1, 1, '2020-05-23 11:05', 23.4877932,120.9570499, 3822.0, 84, -61, '27:05:07', '2020-05-23 11:05:01'), -- north
    (1, 1, '2020-05-23 11:05', 23.4877932,120.9570499, 3822.0, 84, -61, '27:05:07', '2020-05-23 11:05:01'), -- north
    (1, 1, '2020-05-24 05:05', 23.4700202,120.9551856, 3917.0, 65, -68, '09:05:07', '2020-05-24 05:05:01'), -- main
    (1, 1, '2020-05-24 05:05', 23.4700202,120.9551856, 3917.0, 65, -68, '09:05:07', '2020-05-24 05:05:01'), -- main
    (1, 1, '2020-05-24 12:00', 23.4739179,120.9001783, 2609.0, 27, -67, '39:05:05', '2020-05-24 12:00:01'), -- tataka
    (5, 5, '