-- phpMyAdmin SQL Dump
-- version 4.9.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 15, 2021 at 05:27 AM
-- Server version: 10.4.6-MariaDB
-- PHP Version: 7.3.9

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
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: `javis_lisensi_traffic`
--

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

--
-- Table structure for table `lisensi_client`
--

CREATE TABLE `lisensi_client` (
  `id_lisensi_client` int(23) NOT NULL,
  `lisensi` varchar(29) NOT NULL,
  `status_lisensi` enum('N','A') NOT NULL,
  `lisensi_owner` varchar(23) NOT NULL,
  `tgl_aktivasi` datetime DEFAULT NULL,
  `serial_traffic_id` int(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `lisensi_client`
--

INSERT INTO `lisensi_client` (`id_lisensi_client`, `lisensi`, `status_lisensi`, `lisensi_owner`, `tgl_aktivasi`, `serial_traffic_id`) VALUES
(4, '1234-1234-1234-1234-1234', 'N', '', NULL, NULL),
(5, '4321-4321-4321-1234-1234', 'N', '', '0000-00-00 00:00:00', 0),
(6, '2314-2345-2345-5675-1234', 'N', '', NULL, NULL);

--
-- Triggers `lisensi_client`
--
DELIMITER $$
CREATE TRIGGER `counter_activated_client` BEFORE UPDATE ON `lisensi_client` FOR EACH ROW BEGIN    
	INSERT INTO log_aktivasi
    set id_serial = OLD.id_lisensi_client,
		Tipe_serial ='Serial_Client',
        Tanggal = current_timestamp();
    
END
$$
DELIMITER ;

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

--
-- Table structure for table `lisensi_server`
--

CREATE TABLE `lisensi_server` (
  `id_lisensi` int(23) NOT NULL,
  `lisensi` varchar(29) NOT NULL,
  `status_lisensi` enum('A','N') DEFAULT NULL,
  `account_owner_id` int(23) DEFAULT NULL,
  `tgl_aktivasi` timestamp NULL DEFAULT current_timestamp(),
  `serial_traffic_id` int(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `lisensi_server`
--

INSERT INTO `lisensi_server` (`id_lisensi`, `lisensi`, `status_lisensi`, `account_owner_id`, `tgl_aktivasi`, `serial_traffic_id`) VALUES
(9, 'WE28-OP45-RA78-MITM', 'N', NULL, NULL, NULL),
(10, 'WRDF-BH9M-GR02-MLQJ', 'N', NULL, NULL, NULL),
(11, 'XPB9-O37V-9UJI-2LW9', 'N', NULL, NULL, NULL),
(12, '2VHJ-55SP-A8K5-IM5U', 'A', 1, '2021-09-07 07:14:41', 65),
(13, 'M8I4-EUBY-V83P-TYBT', 'N', NULL, NULL, NULL),
(14, '1E9S-GPUA-Q4P6-2S9T', 'N', NULL, NULL, NULL),
(15, 'Y1GD-E2CM-83NE-8QDS', 'N', 1, '2021-09-13 08:52:28', 79),
(16, 'XQC5-4KZN-KVZH-NN5S', 'A', 1, '2021-09-11 05:22:28', 68);

--
-- Triggers `lisensi_server`
--
DELIMITER $$
CREATE TRIGGER `log_aktivasi_serial_server` BEFORE UPDATE ON `lisensi_server` FOR EACH ROW BEGIN
	INSERT INTO log_aktivasi
    set id_serial = OLD.id_lisensi,
		Tipe_serial ='Serial_Server',
        Tanggal = current_timestamp();
END
$$
DELIMITER ;

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

--
-- Table structure for table `log_activity`
--

CREATE TABLE `log_activity` (
  `id_log_activity` int(23) NOT NULL,
  `id_user` int(23) DEFAULT NULL,
  `activity` varchar(23) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `log_activity`
--

INSERT INTO `log_activity` (`id_log_activity`, `id_user`, `activity`, `date`) VALUES
(1, 1, 'melakukan logout', '2021-08-18 08:59:04'),
(2, 1, 'melakukan login', '2021-08-18 09:02:13'),
(3, 1, 'melakukan login', '2021-08-19 05:14:11'),
(4, 1, 'melakukan login', '2021-08-20 09:58:34'),
(5, 1, 'melakukan login', '2021-08-23 04:13:03'),
(6, NULL, 'melakukan logout', '2021-08-23 04:16:23'),
(7, 1, 'melakukan login', '2021-08-23 10:00:08'),
(8, 1, 'melakukan login', '2021-08-24 05:08:34'),
(9, 1, 'melakukan login', '2021-08-25 01:37:17'),
(10, 1, 'melakukan logout', '2021-08-25 12:04:45'),
(11, 1, 'melakukan login', '2021-08-28 06:03:58'),
(12, 1, 'melakukan logout', '2021-08-28 06:55:53'),
(13, 1, 'mengirim email pemuliha', '2021-08-28 06:56:11'),
(14, 1, 'melakukan login', '2021-08-31 03:14:01'),
(15, NULL, 'melakukan logout', '2021-08-31 03:52:30'),
(16, 1, 'melakukan login', '2021-08-31 03:52:55'),
(17, NULL, 'melakukan logout', '2021-08-31 04:00:29'),
(18, 1, 'melakukan login', '2021-08-31 04:00:58'),
(19, 1, 'melakukan login', '2021-09-01 09:09:31'),
(20, 1, 'melakukan login', '2021-09-02 01:43:22'),
(21, 1, 'melakukan login', '2021-09-06 03:04:38'),
(22, 1, 'melakukan login', '2021-09-07 01:48:07'),
(23, NULL, 'melakukan logout', '2021-09-07 04:01:35'),
(24, 1, 'melakukan login', '2021-09-07 06:30:06'),
(25, NULL, 'melakukan logout', '2021-09-07 06:30:10'),
(26, 1, 'melakukan login', '2021-09-07 06:51:04'),
(27, NULL, 'melakukan logout', '2021-09-07 06:58:58'),
(28, 1, 'melakukan login', '2021-09-07 06:59:06'),
(29, NULL, 'melakukan logout', '2021-09-07 06:59:09'),
(30, 1, 'melakukan login', '2021-09-09 08:11:58'),
(31, 1, 'melakukan login', '2021-09-11 05:16:05'),
(32, 1, 'melakukan login', '2021-09-13 02:27:33');

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

--
-- Table structure for table `log_aktivasi`
--

CREATE TABLE `log_aktivasi` (
  `id_log_aktivasi` int(11) NOT NULL,
  `id_serial` int(29) NOT NULL,
  `Tipe_Serial` varchar(29) NOT NULL,
  `Tanggal` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `log_aktivasi`
--

INSERT INTO `log_aktivasi` (`id_log_aktivasi`, `id_serial`, `Tipe_Serial`, `Tanggal`) VALUES
(58, 12, 'Serial_Server', '2021-09-01 03:53:15'),
(59, 12, 'Serial_Server', '2021-09-01 04:03:21'),
(60, 12, 'Serial_Server', '2021-09-01 04:03:58'),
(61, 12, 'Serial_Server', '2021-09-01 04:05:34'),
(62, 12, 'Serial_Server', '2021-09-01 04:06:05'),
(63, 12, 'Serial_Server', '2021-09-01 04:06:28'),
(64, 12, 'Serial_Server', '2021-09-01 04:09:14'),
(65, 12, 'Serial_Server', '2021-09-01 04:09:48'),
(66, 12, 'Serial_Server', '2021-09-01 04:12:26'),
(67, 12, 'Serial_Server', '2021-09-01 04:13:16'),
(68, 12, 'Serial_Server', '2021-09-01 04:17:47'),
(69, 12, 'Serial_Server', '2021-09-01 04:29:59'),
(70, 12, 'Serial_Server', '2021-09-01 09:08:37'),
(71, 12, 'Serial_Server', '2021-09-01 09:18:47'),
(72, 12, 'Serial_Server', '2021-09-01 09:18:50'),
(73, 12, 'Serial_Server', '2021-09-01 09:18:54'),
(74, 12, 'Serial_Server', '2021-09-01 09:18:57'),
(75, 12, 'Serial_Server', '2021-09-01 09:20:34'),
(76, 12, 'Serial_Server', '2021-09-01 09:21:15'),
(77, 12, 'Serial_Server', '2021-09-01 09:28:23'),
(78, 12, 'Serial_Server', '2021-09-01 09:30:11'),
(79, 12, 'Serial_Server', '2021-09-01 09:31:52'),
(80, 12, 'Serial_Server', '2021-09-01 09:40:46'),
(81, 12, 'Serial_Server', '2021-09-01 09:41:30'),
(82, 12, 'Serial_Server', '2021-09-01 09:42:31'),
(83, 12, 'Serial_Server', '2021-09-01 09:43:05'),
(84, 12, 'Serial_Server', '2021-09-01 09:44:08'),
(85, 12, 'Serial_Server', '2021-09-01 09:59:28'),
(86, 12, 'Serial_Server', '2021-09-01 10:01:14'),
(87, 12, 'Serial_Server', '2021-09-04 02:22:43'),
(88, 12, 'Serial_Server', '2021-09-04 02:23:08'),
(89, 4, 'Serial_Client', '2021-09-07 02:46:51'),
(90, 4, 'Serial_Client', '2021-09-07 02:47:03'),
(91, 4, 'Serial_Client', '2021-09-07 02:57:08'),
(92, 4, 'Serial_Client', '2021-09-07 02:57:22'),
(93, 12, 'Serial_Server', '2021-09-07 08:03:43'),
(94, 16, 'Serial_Server', '2021-09-11 05:23:57'),
(95, 4, 'Serial_Client', '2021-09-11 07:54:30'),
(96, 4, 'Serial_Client', '2021-09-11 07:54:36'),
(97, 5, 'Serial_Client', '2021-09-11 07:56:07'),
(98, 5, 'Serial_Client', '2021-09-13 04:00:41'),
(99, 5, 'Serial_Client', '2021-09-13 04:00:46'),
(100, 6, 'Serial_Client', '2021-09-13 04:01:00'),
(101, 15, 'Serial_Server', '2021-09-13 04:20:14'),
(102, 4, 'Serial_Client', '2021-09-13 04:21:39'),
(103, 15, 'Serial_Server', '2021-09-13 06:56:15'),
(104, 5, 'Serial_Client', '2021-09-13 06:57:52'),
(105, 15, 'Serial_Server', '2021-09-13 06:57:52'),
(106, 15, 'Serial_Server', '2021-09-13 07:51:18'),
(107, 5, 'Serial_Client', '2021-09-13 07:53:36'),
(108, 5, 'Serial_Client', '2021-09-13 07:53:39'),
(109, 5, 'Serial_Client', '2021-09-13 07:53:41'),
(110, 15, 'Serial_Server', '2021-09-13 07:56:46'),
(111, 5, 'Serial_Client', '2021-09-13 07:56:46'),
(112, 15, 'Serial_Server', '2021-09-13 08:00:09'),
(113, 5, 'Serial_Client', '2021-09-13 08:07:13'),
(114, 15, 'Serial_Server', '2021-09-13 08:07:13'),
(115, 5, 'Serial_Client', '2021-09-13 08:08:42'),
(116, 5, 'Serial_Client', '2021-09-13 08:08:42'),
(117, 5, 'Serial_Client', '2021-09-13 08:13:12'),
(118, 5, 'Serial_Client', '2021-09-13 08:13:31'),
(119, 5, 'Serial_Client', '2021-09-13 08:13:36'),
(120, 5, 'Serial_Client', '2021-09-13 08:19:09'),
(121, 15, 'Serial_Server', '2021-09-13 08:19:10'),
(122, 15, 'Serial_Server', '2021-09-13 08:21:07'),
(123, 5, 'Serial_Client', '2021-09-13 08:21:07'),
(124, 5, 'Serial_Client', '2021-09-13 08:22:16'),
(125, 15, 'Serial_Server', '2021-09-13 08:22:16'),
(126, 5, 'Serial_Client', '2021-09-13 08:24:01'),
(127, 5, 'Serial_Client', '2021-09-13 08:24:04'),
(128, 5, 'Serial_Client', '2021-09-13 08:25:24'),
(129, 15, 'Serial_Server', '2021-09-13 08:25:24'),
(130, 15, 'Serial_Server', '2021-09-13 08:26:27'),
(131, 15, 'Serial_Server', '2021-09-13 08:30:16'),
(132, 5, 'Serial_Client', '2021-09-13 08:30:16'),
(133, 15, 'Serial_Server', '2021-09-13 08:30:40'),
(134, 5, 'Serial_Client', '2021-09-13 08:32:32'),
(135, 15, 'Serial_Server', '2021-09-13 08:32:32'),
(136, 15, 'Serial_Server', '2021-09-13 08:32:50'),
(137, 15, 'Serial_Server', '2021-09-13 08:36:32'),
(138, 5, 'Serial_Client', '2021-09-13 08:36:32'),
(139, 15, 'Serial_Server', '2021-09-13 08:36:48'),
(140, 15, 'Serial_Server', '2021-09-13 08:44:53'),
(141, 5, 'Serial_Client', '2021-09-13 08:44:53'),
(142, 6, 'Serial_Client', '2021-09-13 08:45:40'),
(143, 6, 'Serial_Client', '2021-09-13 08:45:41'),
(144, 6, 'Serial_Client', '2021-09-13 08:45:42'),
(145, 6, 'Serial_Client', '2021-09-13 08:45:43'),
(146, 6, 'Serial_Client', '2021-09-13 08:45:50'),
(147, 6, 'Serial_Client', '2021-09-13 08:50:28'),
(148, 15, 'Serial_Server', '2021-09-13 08:50:29'),
(149, 6, 'Serial_Client', '2021-09-13 08:51:00'),
(150, 6, 'Serial_Client', '2021-09-13 08:51:04'),
(151, 5, 'Serial_Client', '2021-09-13 08:51:11'),
(152, 5, 'Serial_Client', '2021-09-13 08:51:15'),
(153, 5, 'Serial_Client', '2021-09-13 08:51:17'),
(154, 5, 'Serial_Client', '2021-09-13 08:51:23'),
(155, 15, 'Serial_Server', '2021-09-13 08:52:26'),
(156, 5, 'Serial_Client', '2021-09-13 08:52:26'),
(157, 15, 'Serial_Server', '2021-09-13 08:52:49'),
(158, 5, 'Serial_Client', '2021-09-13 08:52:50');

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

--
-- Table structure for table `log_user`
--

CREATE TABLE `log_user` (
  `id_log` int(23) NOT NULL,
  `ip` int(23) DEFAULT NULL,
  `id_user_log_user` int(23) NOT NULL,
  `browser` varchar(23) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `throttle`
--

CREATE TABLE `throttle` (
  `id_throttle` int(23) NOT NULL,
  `id_user` int(23) DEFAULT NULL,
  `ip` int(23) DEFAULT NULL,
  `date_throttle` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `incorrect` int(23) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
  `id_user` int(11) NOT NULL,
  `user_username` varchar(50) NOT NULL,
  `user_password` text NOT NULL,
  `user_nama` varchar(50) NOT NULL,
  `user_email` varchar(50) NOT NULL,
  `user_hak_akses` enum('admin') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id_user`, `user_username`, `user_password`, `user_nama`, `user_email`, `user_hak_akses`) VALUES
(1, 'admin', 'vjx5/GRHF540RGM77UOcS+22vxYYNlhVqWkhGfTh3wg=', 'admin', 'ghozifadilah97@gmail.com', 'admin');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `lisensi_client`
--
ALTER TABLE `lisensi_client`
  ADD PRIMARY KEY (`id_lisensi_client`),
  ADD UNIQUE KEY `lisensi` (`lisensi`);

--
-- Indexes for table `lisensi_server`
--
ALTER TABLE `lisensi_server`
  ADD PRIMARY KEY (`id_lisensi`),
  ADD UNIQUE KEY `lisensi` (`lisensi`);

--
-- Indexes for table `log_activity`
--
ALTER TABLE `log_activity`
  ADD PRIMARY KEY (`id_log_activity`),
  ADD KEY `id_user_activity` (`id_user`);

--
-- Indexes for table `log_aktivasi`
--
ALTER TABLE `log_aktivasi`
  ADD PRIMARY KEY (`id_log_aktivasi`);

--
-- Indexes for table `log_user`
--
ALTER TABLE `log_user`
  ADD PRIMARY KEY (`id_log`),
  ADD KEY `log_user_id` (`id_user_log_user`);

--
-- Indexes for table `throttle`
--
ALTER TABLE `throttle`
  ADD PRIMARY KEY (`id_throttle`),
  ADD KEY `id_user_throttle` (`id_user`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
  ADD PRIMARY KEY (`id_user`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `lisensi_client`
--
ALTER TABLE `lisensi_client`
  MODIFY `id_lisensi_client` int(23) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `lisensi_server`
--
ALTER TABLE `lisensi_server`
  MODIFY `id_lisensi` int(23) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `log_activity`
--
ALTER TABLE `log_activity`
  MODIFY `id_log_activity` int(23) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- AUTO_INCREMENT for table `log_aktivasi`
--
ALTER TABLE `log_aktivasi`
  MODIFY `id_log_aktivasi` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=159;

--
-- AUTO_INCREMENT for table `log_user`
--
ALTER TABLE `log_user`
  MODIFY `id_log` int(23) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `throttle`
--
ALTER TABLE `throttle`
  MODIFY `id_throttle` int(23) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
  MODIFY `id_user` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `log_activity`
--
ALTER TABLE `log_activity`
  ADD CONSTRAINT `id_user_activity` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `log_user`
--
ALTER TABLE `log_user`
  ADD CONSTRAINT `log_user_id` FOREIGN KEY (`id_user_log_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `throttle`
--
ALTER TABLE `throttle`
  ADD CONSTRAINT `id_user_throttle` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!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 */;