-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 27, 2019 at 12:40 AM
-- Server version: 5.7.25-0ubuntu0.18.04.2
-- PHP Version: 7.2.15-0ubuntu0.18.04.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `ist`
--

-- --------------------------------------------------------

--
-- Table structure for table `api`
--

CREATE TABLE `api` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `domain` varchar(350) NOT NULL,
  `description` varchar(140) NOT NULL,
  `status` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
  `creation_time` time NOT NULL,
  `creation_date` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `api`
--

INSERT INTO `api` (`id`, `name`, `domain`, `description`, `status`, `creation_time`, `creation_date`, `timestamp`) VALUES
(2, 'session', 'fiu.edu', 'Session handling API', 'enabled', '00:00:00', '2019-04-09', '2019-04-09 19:15:44');

-- --------------------------------------------------------

--
-- Table structure for table `api_endpoint`
--

CREATE TABLE `api_endpoint` (
  `id` int(11) NOT NULL,
  `api_id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `description` varchar(140) NOT NULL,
  `creation_time` time NOT NULL,
  `creation_date` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `api_endpoint`
--

INSERT INTO `api_endpoint` (`id`, `api_id`, `name`, `description`, `creation_time`, `creation_date`, `timestamp`) VALUES
(2, 2, 'progress', 'Get session\'s progress', '00:00:00', '2019-04-09', '2019-04-09 19:16:49'),
(3, 2, 'peers', 'Get session peers', '00:00:00', '2019-04-09', '2019-04-10 03:41:48');

-- --------------------------------------------------------

--
-- Table structure for table `api_endpoint_access`
--

CREATE TABLE `api_endpoint_access` (
  `id` int(11) NOT NULL,
  `api_endpoint_id` int(11) NOT NULL,
  `api_key_id` int(11) NOT NULL,
  `creation_time` time NOT NULL,
  `creation_date` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `api_key`
--

CREATE TABLE `api_key` (
  `id` int(11) NOT NULL,
  `api_id` int(11) NOT NULL,
  `api_user_id` int(11) NOT NULL,
  `passkey` varchar(64) NOT NULL,
  `stability` enum('dev','prod','test') NOT NULL DEFAULT 'dev',
  `status` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
  `creation_time` time NOT NULL,
  `creation_date` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `api_key`
--

INSERT INTO `api_key` (`id`, `api_id`, `api_user_id`, `passkey`, `stability`, `status`, `creation_time`, `creation_date`, `timestamp`) VALUES
(2, 2, 2, 'cc78112b8c6cfcb49cac8d4e43ce38c99bb018c2691ac5c9c29c64ea3ccdf32d', 'dev', 'enabled', '00:00:00', '2019-04-09', '2019-04-09 19:20:34');

-- --------------------------------------------------------

--
-- Table structure for table `api_user`
--

CREATE TABLE `api_user` (
  `id` int(11) NOT NULL,
  `domain` varchar(350) NOT NULL,
  `username` varchar(350) NOT NULL,
  `password` varchar(64) NOT NULL,
  `status` enum('enabled','disabled') NOT NULL DEFAULT 'enabled',
  `creation_time` time NOT NULL,
  `creation_date` date NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `api_user`
--

INSERT INTO `api_user` (`id`, `domain`, `username`, `password`, `status`, `creation_time`, `creation_date`, `timestamp`) VALUES
(2, 'fiu.edu', 'fiu', '7c19a6a93d8f9fcffd37db42aa078761323fd1cb59908f9a52c9188037633f19', 'enabled', '00:00:00', '2019-04-09', '2019-04-10 05:20:49');

-- --------------------------------------------------------

--
-- Table structure for table `ist_hub`
--

CREATE TABLE `ist_hub` (
  `id` int(11) NOT NULL,
  `type_id` int(11) NOT NULL,
  `domain` varchar(256) NOT NULL,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ist_hub`
--

INSERT INTO `ist_hub` (`id`, `type_id`, `domain`, `created_on`) VALUES
(1, 1, 'meetings.welogy.com', '2019-04-09 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `ist_hub_type`
--

CREATE TABLE `ist_hub_type` (
  `id` int(11) NOT NULL,
  `type` varchar(28) NOT NULL,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ist_hub_type`
--

INSERT INTO `ist_hub_type` (`id`, `type`, `created_on`) VALUES
(1, 'table', '2019-04-09 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `ist_peers`
--

CREATE TABLE `ist_peers` (
  `id` int(11) NOT NULL,
  `hub_id` int(11) NOT NULL,
  `pid` varchar(28) NOT NULL,
  `addr` varchar(64) NOT NULL,
  `status` enum('online','offline') NOT NULL DEFAULT 'offline',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ist_peers`
--

INSERT INTO `ist_peers` (`id`, `hub_id`, `pid`, `addr`, `status`, `timestamp`, `created_on`) VALUES
(1, 1, 'tbl1p1', 'localhost', 'offline', '2019-04-10 03:11:06', '2019-04-09 00:00:00'),
(2, 1, 'tbl1p2', 'localhost', 'offline', '2019-04-10 03:11:21', '2019-04-09 00:00:00'),
(3, 1, 'tbl1p3', 'localhost', 'offline', '2019-04-10 03:11:40', '2019-04-09 00:00:00');

-- --------------------------------------------------------

--
-- Table structure for table `ist_session`
--

CREATE TABLE `ist_session` (
  `id` int(11) NOT NULL,
  `hub_id` int(11) NOT NULL,
  `sid` varchar(46) NOT NULL,
  `status` enum('started','ended','canceled') NOT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime DEFAULT NULL,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ist_session`
--

INSERT INTO `ist_session` (`id`, `hub_id`, `sid`, `status`, `start_datetime`, `end_datetime`, `created_on`) VALUES
(1, 1, 'tbl1ses1', 'ended', '2019-04-09 00:00:00', NULL, '2019-04-09 00:00:00'),
(6, 1, '506cb6', 'started', '2019-04-18 06:30:15', NULL, '2019-04-18 06:30:15'),
(7, 1, '547b32', 'started', '2019-04-18 06:30:41', NULL, '2019-04-18 06:30:41');

-- --------------------------------------------------------

--
-- Table structure for table `ist_session_peers`
--

CREATE TABLE `ist_session_peers` (
  `id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `peer_id` int(11) NOT NULL,
  `peer_type` enum('initiator','member') NOT NULL,
  `peer_state` enum('active','idle') NOT NULL DEFAULT 'idle',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ist_session_peers`
--

INSERT INTO `ist_session_peers` (`id`, `session_id`, `peer_id`, `peer_type`, `peer_state`, `timestamp`, `created_on`) VALUES
(1, 1, 1, 'initiator', 'active', '2019-04-10 03:12:39', '2019-04-09 00:00:00'),
(2, 1, 2, 'member', 'idle', '2019-04-10 03:12:53', '2019-04-09 00:00:00'),
(3, 1, 3, 'member', 'idle', '2019-04-10 03:13:07', '2019-04-09 00:00:00'),
(5, 6, 1, 'initiator', 'active', '2019-04-18 10:30:15', '2019-04-18 06:30:15'),
(6, 7, 1, 'initiator', 'active', '2019-04-18 10:30:41', '2019-04-18 06:30:41');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `api`
--
ALTER TABLE `api`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `api_UNIQUE` (`name`,`domain`);

--
-- Indexes for table `api_endpoint`
--
ALTER TABLE `api_endpoint`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `api_endpoint_UNIQUE` (`api_id`,`name`),
  ADD KEY `fk_api_endpoint_api1_idx` (`api_id`);

--
-- Indexes for table `api_endpoint_access`
--
ALTER TABLE `api_endpoint_access`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_api_endpoint_access_api_endpoint1_idx` (`api_endpoint_id`),
  ADD KEY `fk_api_endpoint_access_api_key1_idx` (`api_key_id`);

--
-- Indexes for table `api_key`
--
ALTER TABLE `api_key`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `api_key_UNIQUE` (`api_id`,`api_user_id`,`stability`),
  ADD KEY `fk_api_key_api_idx` (`api_id`),
  ADD KEY `fk_api_key_api_user1_idx` (`api_user_id`);

--
-- Indexes for table `api_user`
--
ALTER TABLE `api_user`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username_UNIQUE` (`username`);

--
-- Indexes for table `ist_hub`
--
ALTER TABLE `ist_hub`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ist_hub_ist_hub_type1_idx` (`type_id`);

--
-- Indexes for table `ist_hub_type`
--
ALTER TABLE `ist_hub_type`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ist_peers`
--
ALTER TABLE `ist_peers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ist_peers_ist_hub1_idx` (`hub_id`);

--
-- Indexes for table `ist_session`
--
ALTER TABLE `ist_session`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ist_session_ist_hub1_idx` (`hub_id`);

--
-- Indexes for table `ist_session_peers`
--
ALTER TABLE `ist_session_peers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ist_session_peers_ist_session1_idx` (`session_id`),
  ADD KEY `fk_ist_session_peers_ist_peers1_idx` (`peer_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `api`
--
ALTER TABLE `api`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `api_endpoint`
--
ALTER TABLE `api_endpoint`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `api_endpoint_access`
--
ALTER TABLE `api_endpoint_access`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `api_key`
--
ALTER TABLE `api_key`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `api_user`
--
ALTER TABLE `api_user`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `ist_hub`
--
ALTER TABLE `ist_hub`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `ist_hub_type`
--
ALTER TABLE `ist_hub_type`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `ist_peers`
--
ALTER TABLE `ist_peers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `ist_session`
--
ALTER TABLE `ist_session`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
--
-- AUTO_INCREMENT for table `ist_session_peers`
--
ALTER TABLE `ist_session_peers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `api_endpoint`
--
ALTER TABLE `api_endpoint`
  ADD CONSTRAINT `fk_api_endpoint_api1` FOREIGN KEY (`api_id`) REFERENCES `api` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `api_endpoint_access`
--
ALTER TABLE `api_endpoint_access`
  ADD CONSTRAINT `fk_api_endpoint_access_api_endpoint1` FOREIGN KEY (`api_endpoint_id`) REFERENCES `api_endpoint` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_api_endpoint_access_api_key1` FOREIGN KEY (`api_key_id`) REFERENCES `api_key` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `api_key`
--
ALTER TABLE `api_key`
  ADD CONSTRAINT `fk_api_key_api` FOREIGN KEY (`api_id`) REFERENCES `api` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_api_key_api_user1` FOREIGN KEY (`api_user_id`) REFERENCES `api_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `ist_hub`
--
ALTER TABLE `ist_hub`
  ADD CONSTRAINT `fk_ist_hub_ist_hub_type1` FOREIGN KEY (`type_id`) REFERENCES `ist_hub_type` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `ist_peers`
--
ALTER TABLE `ist_peers`
  ADD CONSTRAINT `fk_ist_peers_ist_hub1` FOREIGN KEY (`hub_id`) REFERENCES `ist_hub` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `ist_session`
--
ALTER TABLE `ist_session`
  ADD CONSTRAINT `fk_ist_session_ist_hub1` FOREIGN KEY (`hub_id`) REFERENCES `ist_hub` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `ist_session_peers`
--
ALTER TABLE `ist_session_peers`
  ADD CONSTRAINT `fk_ist_session_peers_ist_peers1` FOREIGN KEY (`peer_id`) REFERENCES `ist_peers` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_ist_session_peers_ist_session1` FOREIGN KEY (`session_id`) REFERENCES `ist_session` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
