-- out with the old
DROP TABLE IF EXISTS CONTACT_ACTIVITY;
DROP TABLE IF EXISTS GROUND_STATION_ANTENNA;

-- in with the new
CREATE TABLE GROUND_STATION_ANTENNA
(
    ANTENNAID  INT NOT NULL,
    LOCATION   VARCHAR(16) NOT NULL,
    NETWORK    ENUM('DSN','GN') NOT NULL,
    PRIMARY KEY ( ANTENNAID )
);

CREATE TABLE CONTACT_ACTIVITY
(
    ID             INT NOT NULL AUTO_INCREMENT,
    SATELLITEID    VARCHAR(3) NOT NULL,
    ANTENNAID      INT,
    AOS_TIMESTAMP  DATETIME NOT NULL,
    TEL_TIMESTAMP  DATETIME,
    LOS_TIMESTAMP  DATETIME,
    FOREIGN KEY (ANTENNAID) REFERENCES GROUND_STATION_ANTENNA( ANTENNAID ),
    PRIMARY KEY ( ID )
);

-- Populate the Ground Station Antenna table
INSERT INTO GROUND_STATION_ANTENNA VALUES (0,'McMurdo','GN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (24,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (25,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (26,'Goldstone','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (34,'Canberra','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (54,'Madrid','DSN');
INSERT INTO GROUND_STATION_ANTENNA VALUES (55,'Madrid','DSN');

-- Populate the Contact Activity table
-- DAY 24
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-24 00:00:00','2005-07-24 00:02:00','2005-07-24 00:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','24','2005-07-24 03:00:00','2005-07-24 03:02:30','2005-07-24 03:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','34','2005-07-24 09:00:00','2005-07-24 09:00:30','2005-07-24 09:15:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','54','2005-07-24 12:00:00','2005-07-24 12:01:00','2005-07-24 12:33:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','25','2005-07-24 15:00:00','2005-07-24 15:02:00','2005-07-24 15:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-24 17:00:00','2005-07-24 17:02:12','2005-07-24 17:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','24','2005-07-24 19:00:00','2005-07-24 19:02:00','2005-07-24 19:27:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','34','2005-07-24 21:00:00','2005-07-24 21:02:33','2005-07-24 21:22:55');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','55','2005-07-24 23:00:00','2005-07-24 23:00:45','2005-07-24 23:22:23');

-- DAY 25
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-25 00:00:00','2005-07-25 00:02:00','2005-07-25 00:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','24','2005-07-25 03:00:00','2005-07-25 03:02:30','2005-07-25 03:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','55','2005-07-25 09:00:00','2005-07-25 09:00:30','2005-07-25 09:15:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','0','2005-07-25 12:00:00','2005-07-25 12:01:00','2005-07-25 12:33:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','0','2005-07-25 15:00:00','2005-07-25 15:02:00','2005-07-25 15:22:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-25 17:00:00','2005-07-25 17:02:12','2005-07-25 17:20:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'224','55','2005-07-25 19:00:00','2005-07-25 19:02:00','2005-07-25 19:27:00');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'155','34','2005-07-25 21:00:00','2005-07-25 21:02:33','2005-07-25 21:22:55');
INSERT INTO CONTACT_ACTIVITY VALUES (NULL,'094','0','2005-07-25 23:00:00','2005-07-25 23:00:45','2005-07-25 23:22:23');