-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------



-- Dumping database structure for mikrotik
CREATE DATABASE IF NOT EXISTS `mikrotik` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `mikrotik`;

-- Dumping structure for table mikrotik.customers
CREATE TABLE IF NOT EXISTS `customers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `mikrotik_id` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  `profile` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `service` varchar(100) DEFAULT NULL,
  `disabled` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `pop_id` int DEFAULT NULL,
  `olt_id` int DEFAULT NULL,
  `sfp_id` int DEFAULT NULL,
  `odc_id` int DEFAULT NULL,
  `odp_id` int DEFAULT NULL,
  `alamat` varchar(255) DEFAULT NULL,
  `no_tlp` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `command` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_pop` (`pop_id`) USING BTREE,
  KEY `name_olt` (`olt_id`) USING BTREE,
  KEY `name_sfp` (`sfp_id`) USING BTREE,
  KEY `name_odc` (`odc_id`) USING BTREE,
  KEY `name_odp` (`odp_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.customers: ~4 rows (approximately)
REPLACE INTO `customers` (`id`, `mikrotik_id`, `name`, `password`, `profile`, `service`, `disabled`, `pop_id`, `olt_id`, `sfp_id`, `odc_id`, `odp_id`, `alamat`, `no_tlp`, `created_at`, `command`) VALUES
	(10, '*73', 'Malvirino Hardian', '!@#2024', '30 Mbps', 'pppoe', 'true', 0, 0, 0, 0, 0, 'pesona', '08968759962', '2024-12-02 02:35:55', 'samnfkjw'),
	(14, '*78', 'rafli', '!@#2024', '30 Mbps', 'pppoe', 'false', 0, 0, 0, 0, 0, 'shushf', '08968759962', '2024-12-02 07:41:27', 'sdgjis'),
	(15, '*79', 'ompong', '!@#2024', '20 Mbps', 'pppoe', 'true', 0, 0, 0, 0, 0, 'ma sdk', '0892421234', '2024-12-02 07:44:39', 'mkaska'),
	(16, '*7B', 'rafli akbar', '!@#2023', '30 Mbps', 'pppoe', 'false', 0, 0, 0, 0, 0, 'pkdpocs', '08968759962', '2024-12-06 07:21:53', 's,dvbjhwe'),
	(17, '*1E', 'rafli MF', '!@#2023', '30 Mbps', 'pppoe', 'false', 0, 0, 0, 0, 0, 'pkdpocs', '08968759962', '2024-12-06 07:24:00', 's,dvbjhwe');

-- Dumping structure for table mikrotik.odc
CREATE TABLE IF NOT EXISTS `odc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `odc` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `pop_id` int DEFAULT NULL,
  `olt_id` int DEFAULT NULL,
  `sfp_id` int DEFAULT NULL,
  `redaman` varchar(100) DEFAULT NULL,
  `last_check` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pop_id` (`pop_id`),
  KEY `sfp_id` (`sfp_id`),
  KEY `olt_id` (`olt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.odc: ~1 rows (approximately)
REPLACE INTO `odc` (`id`, `odc`, `pop_id`, `olt_id`, `sfp_id`, `redaman`, `last_check`) VALUES
	(7, 'ODC 02', 7, 6, 13, '-13.9dbm', '2024-11-15');

-- Dumping structure for table mikrotik.odp
CREATE TABLE IF NOT EXISTS `odp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `odp` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `pop_id` int DEFAULT NULL,
  `olt_id` int DEFAULT NULL,
  `sfp_id` int DEFAULT NULL,
  `odc_id` int DEFAULT NULL,
  `redaman` varchar(100) DEFAULT NULL,
  `last_check` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pop_id` (`pop_id`),
  KEY `odc_id` (`odc_id`),
  KEY `sfp_id` (`sfp_id`),
  KEY `olt_id` (`olt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.odp: ~0 rows (approximately)

-- Dumping structure for table mikrotik.olt
CREATE TABLE IF NOT EXISTS `olt` (
  `id` int NOT NULL AUTO_INCREMENT,
  `olt` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `pop_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pop_id` (`pop_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.olt: ~1 rows (approximately)
REPLACE INTO `olt` (`id`, `olt`, `pop_id`) VALUES
	(5, 'OLT 2', 8),
	(6, 'OLT 3', 7),
	(7, 'OLT 1', 10),
	(8, 'OLT 2', 7);

-- Dumping structure for table mikrotik.pop
CREATE TABLE IF NOT EXISTS `pop` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pop` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `router_ip` varchar(100) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `password` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.pop: ~3 rows (approximately)
REPLACE INTO `pop` (`id`, `pop`, `router_ip`, `username`, `password`) VALUES
	(7, 'CLOUDLINK', '103.113.170.51', 'dev1', 'Dev!@#2024'),
	(8, 'FIBREE', '103.113.170.53', 'dev1', 'Dev!@#2024'),
	(10, 'PATRIOT', NULL, NULL, NULL);

-- Dumping structure for table mikrotik.sfp
CREATE TABLE IF NOT EXISTS `sfp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `sfp` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `redaman` varchar(255) DEFAULT NULL,
  `pop_id` int NOT NULL,
  `olt_id` int NOT NULL,
  `last_check` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_pop` (`pop_id`) USING BTREE,
  KEY `olt_id` (`olt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table mikrotik.sfp: ~2 rows (approximately)
REPLACE INTO `sfp` (`id`, `sfp`, `redaman`, `pop_id`, `olt_id`, `last_check`) VALUES
	(13, 'SFP 01', '-13.9dbm', 7, 8, '2024-11-11'),
	(14, 'SFP 02', '-13.7dbm', 8, 5, '2024-11-14');

