database_backup_bus.sql

-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3310
-- Generation Time: Oct 31, 2024 at 02:48 PM
-- Server version: 8.0.30
-- PHP Version: 8.1.10

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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: `bus_ticketing`
--

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

--
-- Table structure for table `buses`
--

CREATE TABLE `buses` (
  `bus_id` int NOT NULL,
  `bus_name` varchar(255) DEFAULT NULL,
  `route_id` int DEFAULT NULL,
  `departure_time` datetime NOT NULL,
  `price` int NOT NULL,
  `seat_capacity` int NOT NULL,
  `available_seats` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `buses`
--

INSERT INTO `buses` (`bus_id`, `bus_name`, `route_id`, `departure_time`, `price`, `seat_capacity`, `available_seats`) VALUES
(1, 'Damri', 32, '2024-09-23 08:15:00', 67268, 40, 40),
(2, 'Gunung Harta', 25, '2024-09-22 12:45:00', 60485, 40, 40),
(3, 'Safari Dharma Raya', 11, '2024-09-22 11:35:00', 30000, 40, 39),
(4, 'Safari Dharma Raya', 8, '2024-09-24 09:30:00', 60000, 40, 40),
(5, 'Harapan Jaya', 14, '2024-09-24 09:20:00', 40000, 40, 40),
(6, 'Gunung Harta', 27, '2024-09-22 08:55:00', 30000, 40, 40),
(7, 'Gunung Harta', 31, '2024-09-22 12:00:00', 60000, 40, 40),
(8, 'Harapan Jaya', 36, '2024-09-23 11:05:00', 70000, 40, 40),
(9, 'Lorena', 34, '2024-09-24 14:05:00', 50000, 40, 40),
(10, 'Pahala Kencana', 24, '2024-09-24 11:10:00', 30000, 40, 40),
(11, 'Lorena', 17, '2024-09-22 14:55:00', 50000, 40, 40),
(12, 'Pahala Kencana', 32, '2024-09-23 12:15:00', 30000, 40, 40),
(13, 'Rosalia Indah', 20, '2024-09-24 08:45:00', 30000, 40, 40),
(14, 'Lorena', 15, '2024-09-22 10:40:00', 30000, 40, 40),
(15, 'Lorena', 3, '2024-09-22 10:50:00', 30000, 40, 40),
(16, 'Damri', 5, '2024-09-23 08:45:00', 70000, 40, 40),
(17, 'Safari Dharma Raya', 28, '2024-09-23 09:30:00', 30000, 40, 40),
(18, 'Safari Dharma Raya', 30, '2024-09-23 10:00:00', 50000, 40, 40),
(19, 'Harapan Jaya', 33, '2024-09-22 11:45:00', 40000, 40, 40),
(20, 'Damri', 25, '2024-09-24 11:35:00', 40000, 40, 40),
(21, 'Sinar Jaya', 12, '2024-09-22 12:20:00', 60000, 40, 40),
(22, 'Safari Dharma Raya', 10, '2024-09-22 08:25:00', 40000, 40, 40),
(23, 'Mitra Rahayu', 7, '2024-09-22 12:10:00', 70000, 40, 40),
(24, 'Damri', 23, '2024-09-24 08:50:00', 30000, 40, 40),
(25, 'Mitra Rahayu', 16, '2024-09-23 08:15:00', 30000, 40, 39),
(26, 'Lorena', 26, '2024-09-22 12:00:00', 50000, 40, 40),
(27, 'Sinar Jaya', 9, '2024-09-23 13:45:00', 50000, 40, 40),
(28, 'Gunung Harta', 6, '2024-09-22 10:20:00', 30000, 40, 40),
(29, 'Harapan Jaya', 1, '2024-09-22 12:45:00', 30000, 40, 28),
(30, 'Pahala Kencana', 2, '2024-09-24 11:45:00', 30000, 40, 40),
(31, 'Lorena', 35, '2024-09-24 14:35:00', 60000, 40, 40),
(32, 'Mitra Rahayu', 19, '2024-09-24 08:15:00', 30000, 40, 40),
(33, 'Pahala Kencana', 21, '2024-09-23 12:30:00', 40000, 40, 39),
(34, 'Harapan Jaya', 13, '2024-09-23 12:50:00', 50000, 40, 40),
(35, 'Safari Dharma Raya', 29, '2024-09-24 10:15:00', 40000, 40, 40),
(36, 'Harapan Jaya', 4, '2024-09-23 14:40:00', 60000, 40, 40),
(37, 'Sinar Jaya', 18, '2024-09-23 12:30:00', 40000, 40, 40),
(38, 'Mitra Rahayu', 22, '2024-09-23 10:15:00', 40000, 40, 40),
(66, 'Safari Dharma Raya', 14, '2024-09-22 09:45:00', 40000, 40, 40),
(67, 'Mitra Rahayu', 34, '2024-09-22 12:35:00', 50000, 40, 40),
(68, 'Harapan Jaya', 11, '2024-09-24 14:05:00', 30000, 40, 40),
(69, 'Gunung Harta', 36, '2024-09-22 08:15:00', 70000, 40, 40),
(70, 'Gunung Harta', 25, '2024-09-24 14:05:00', 40000, 40, 40),
(71, 'Primajasa', 13, '2024-09-22 08:00:00', 50000, 40, 40),
(72, 'Damri', 31, '2024-09-22 11:20:00', 60000, 40, 40),
(73, 'Harapan Jaya', 17, '2024-09-23 12:30:00', 50000, 40, 40),
(74, 'Lorena', 29, '2024-09-24 13:20:00', 40000, 40, 40),
(75, 'Primajasa', 9, '2024-09-24 10:30:00', 50000, 40, 40),
(76, 'Gunung Harta', 28, '2024-09-22 09:45:00', 30000, 40, 40),
(77, 'Gunung Harta', 20, '2024-09-22 11:00:00', 30000, 40, 40),
(78, 'Damri', 15, '2024-09-23 09:10:00', 30000, 40, 40),
(79, 'Harapan Jaya', 23, '2024-09-22 09:45:00', 30000, 40, 40),
(80, 'Primajasa', 27, '2024-09-24 12:00:00', 30000, 40, 40),
(81, 'Pahala Kencana', 10, '2024-09-23 12:45:00', 40000, 40, 40),
(82, 'Rosalia Indah', 24, '2024-09-23 14:20:00', 30000, 40, 40),
(83, 'Lorena', 16, '2024-09-24 13:55:00', 30000, 40, 40),
(84, 'Damri', 8, '2024-09-22 09:05:00', 60000, 40, 40),
(85, 'Safari Dharma Raya', 19, '2024-09-24 10:40:00', 30000, 40, 40),
(86, 'Rosalia Indah', 2, '2024-09-22 09:50:00', 30000, 40, 40),
(87, 'Lorena', 32, '2024-09-24 13:35:00', 30000, 40, 40),
(88, 'Damri', 22, '2024-09-22 11:55:00', 40000, 40, 40),
(89, 'Harapan Jaya', 4, '2024-09-23 08:45:00', 60000, 40, 40),
(90, 'Damri', 26, '2024-09-23 08:20:00', 50000, 40, 39),
(91, 'Harapan Jaya', 3, '2024-09-24 08:45:00', 30000, 40, 40),
(92, 'Lorena', 5, '2024-09-24 08:45:00', 70000, 40, 40),
(93, 'Mitra Rahayu', 33, '2024-09-23 08:35:00', 40000, 40, 40),
(94, 'Safari Dharma Raya', 6, '2024-09-24 14:30:00', 30000, 40, 40),
(95, 'Harapan Jaya', 21, '2024-09-24 08:30:00', 40000, 40, 40),
(96, 'Pahala Kencana', 1, '2024-09-22 10:20:00', 30000, 40, 40),
(97, 'Sinar Jaya', 30, '2024-09-22 09:45:00', 50000, 40, 40),
(98, 'Harapan Jaya', 12, '2024-09-24 12:30:00', 60000, 40, 40),
(99, 'Rosalia Indah', 18, '2024-09-22 11:10:00', 40000, 40, 40),
(100, 'Rosalia Indah', 35, '2024-09-24 10:00:00', 60000, 40, 40),
(101, 'Mitra Rahayu', 7, '2024-09-22 11:00:00', 70000, 40, 40),
(129, 'Lorena', 10, '2024-09-22 08:40:00', 40000, 40, 40),
(130, 'Pahala Kencana', 34, '2024-09-22 14:35:00', 50000, 40, 40),
(131, 'Rosalia Indah', 22, '2024-09-22 09:00:00', 40000, 40, 40),
(132, 'Rosalia Indah', 14, '2024-09-23 12:50:00', 40000, 40, 40),
(133, 'Sinar Jaya', 5, '2024-09-24 09:15:00', 70000, 40, 40),
(134, 'Rosalia Indah', 31, '2024-09-24 10:55:00', 60000, 40, 40),
(135, 'Gunung Harta', 13, '2024-09-23 10:00:00', 50000, 40, 40),
(136, 'Damri', 29, '2024-09-22 09:00:00', 40000, 40, 40),
(137, 'Gunung Harta', 19, '2024-09-23 12:55:00', 30000, 40, 40),
(138, 'Rosalia Indah', 4, '2024-09-24 12:55:00', 60000, 40, 40),
(139, 'Sinar Jaya', 21, '2024-09-23 10:00:00', 40000, 40, 40),
(140, 'Gunung Harta', 3, '2024-09-22 13:50:00', 30000, 40, 40),
(141, 'Primajasa', 16, '2024-09-22 12:15:00', 30000, 40, 40),
(142, 'Lorena', 8, '2024-09-24 09:35:00', 60000, 40, 40),
(143, 'Gunung Harta', 36, '2024-09-24 14:55:00', 70000, 40, 40),
(144, 'Damri', 35, '2024-09-22 14:15:00', 60000, 40, 40),
(145, 'Lorena', 15, '2024-09-22 10:40:00', 30000, 40, 40),
(146, 'Sinar Jaya', 30, '2024-09-23 12:45:00', 50000, 40, 40),
(147, 'Pahala Kencana', 17, '2024-09-23 08:20:00', 50000, 40, 40),
(148, 'Damri', 11, '2024-09-23 11:40:00', 30000, 40, 40),
(149, 'Rosalia Indah', 9, '2024-09-22 12:45:00', 50000, 40, 40),
(150, 'Gunung Harta', 7, '2024-09-23 11:05:00', 70000, 40, 40),
(151, 'Lorena', 32, '2024-09-22 11:40:00', 30000, 40, 40),
(152, 'Safari Dharma Raya', 6, '2024-09-22 08:55:00', 30000, 40, 40),
(153, 'Safari Dharma Raya', 23, '2024-09-24 08:00:00', 30000, 40, 40),
(154, 'Primajasa', 25, '2024-09-23 09:40:00', 40000, 40, 40),
(155, 'Mitra Rahayu', 33, '2024-09-24 08:05:00', 40000, 40, 40),
(156, 'Pahala Kencana', 20, '2024-09-22 11:30:00', 30000, 40, 40),
(157, 'Damri', 18, '2024-09-24 11:55:00', 40000, 40, 40),
(158, 'Rosalia Indah', 28, '2024-09-22 12:35:00', 30000, 40, 40),
(159, 'Pahala Kencana', 12, '2024-09-24 11:55:00', 60000, 40, 40),
(160, 'Harapan Jaya', 2, '2024-09-23 09:50:00', 30000, 40, 40),
(161, 'Safari Dharma Raya', 24, '2024-09-22 10:20:00', 30000, 40, 40),
(162, 'Safari Dharma Raya', 27, '2024-09-22 12:30:00', 30000, 40, 40),
(163, 'Rosalia Indah', 26, '2024-09-24 10:00:00', 50000, 40, 40),
(164, 'Harapan Jaya', 1, '2024-09-23 13:50:00', 30000, 40, 40),
(192, 'Mitra Rahayu', 23, '2024-09-22 13:20:00', 30000, 40, 40),
(193, 'Safari Dharma Raya', 11, '2024-09-24 13:35:00', 30000, 40, 40),
(194, 'Rosalia Indah', 36, '2024-09-23 14:00:00', 70000, 40, 40),
(195, 'Pahala Kencana', 6, '2024-09-22 13:20:00', 30000, 40, 40),
(196, 'Rosalia Indah', 32, '2024-09-23 12:00:00', 30000, 40, 40),
(197, 'Gunung Harta', 13, '2024-09-22 10:55:00', 50000, 40, 40),
(198, 'Mitra Rahayu', 7, '2024-09-23 13:35:00', 70000, 40, 40),
(199, 'Pahala Kencana', 1, '2024-09-24 09:00:00', 30000, 40, 40),
(200, 'Sinar Jaya', 35, '2024-09-23 10:10:00', 60000, 40, 40),
(201, 'Pahala Kencana', 33, '2024-09-24 12:20:00', 40000, 40, 40),
(202, 'Harapan Jaya', 28, '2024-09-22 08:20:00', 30000, 40, 40),
(203, 'Rosalia Indah', 5, '2024-09-24 11:20:00', 70000, 40, 40),
(204, 'Damri', 30, '2024-09-23 14:15:00', 50000, 40, 40),
(205, 'Pahala Kencana', 16, '2024-09-22 14:30:00', 30000, 40, 40),
(206, 'Lorena', 14, '2024-09-24 08:10:00', 40000, 40, 40),
(207, 'Mitra Rahayu', 2, '2024-09-24 11:00:00', 30000, 40, 40),
(208, 'Primajasa', 15, '2024-09-22 08:55:00', 30000, 40, 40),
(209, 'Mitra Rahayu', 18, '2024-09-23 13:25:00', 40000, 40, 40),
(210, 'Rosalia Indah', 8, '2024-09-22 09:15:00', 60000, 40, 40),
(211, 'Pahala Kencana', 29, '2024-09-22 14:10:00', 40000, 40, 40),
(212, 'Primajasa', 3, '2024-09-24 09:40:00', 30000, 40, 40),
(213, 'Lorena', 27, '2024-09-23 10:15:00', 30000, 40, 40),
(214, 'Pahala Kencana', 12, '2024-09-24 14:35:00', 60000, 40, 40),
(215, 'Lorena', 9, '2024-09-22 13:40:00', 50000, 40, 40),
(216, 'Gunung Harta', 26, '2024-09-24 12:05:00', 50000, 40, 40),
(217, 'Lorena', 24, '2024-09-24 14:40:00', 30000, 40, 40),
(218, 'Lorena', 19, '2024-09-24 11:50:00', 30000, 40, 40),
(219, 'Mitra Rahayu', 25, '2024-09-23 10:45:00', 40000, 40, 40),
(220, 'Gunung Harta', 10, '2024-09-24 08:25:00', 40000, 40, 40),
(221, 'Lorena', 31, '2024-09-22 13:35:00', 60000, 40, 40),
(222, 'Safari Dharma Raya', 21, '2024-09-23 10:45:00', 40000, 40, 40),
(223, 'Sinar Jaya', 22, '2024-09-24 14:55:00', 40000, 40, 40),
(224, 'Safari Dharma Raya', 17, '2024-09-24 14:25:00', 50000, 40, 40),
(225, 'Lorena', 4, '2024-09-22 08:40:00', 60000, 40, 40),
(226, 'Sinar Jaya', 20, '2024-09-24 09:50:00', 30000, 40, 40),
(227, 'Safari Dharma Raya', 34, '2024-09-22 11:10:00', 50000, 40, 40),
(255, 'Damri', 3, '2024-09-24 11:35:00', 30000, 40, 40),
(256, 'Harapan Jaya', 35, '2024-09-22 12:35:00', 60000, 40, 40),
(257, 'Damri', 8, '2024-09-24 10:25:00', 60000, 40, 40),
(258, 'Gunung Harta', 16, '2024-09-24 08:35:00', 30000, 40, 40),
(259, 'Mitra Rahayu', 6, '2024-09-24 09:50:00', 30000, 40, 40),
(260, 'Lorena', 17, '2024-09-24 14:35:00', 50000, 40, 40),
(261, 'Lorena', 29, '2024-09-23 10:10:00', 40000, 40, 40),
(262, 'Harapan Jaya', 36, '2024-09-24 09:25:00', 70000, 40, 40),
(263, 'Pahala Kencana', 24, '2024-09-22 14:20:00', 30000, 40, 40),
(264, 'Primajasa', 34, '2024-09-22 10:00:00', 50000, 40, 40),
(265, 'Primajasa', 9, '2024-09-24 08:15:00', 50000, 40, 40),
(266, 'Primajasa', 33, '2024-09-24 11:40:00', 40000, 40, 40),
(267, 'Sinar Jaya', 22, '2024-09-24 12:55:00', 40000, 40, 40),
(268, 'Lorena', 10, '2024-09-23 10:05:00', 40000, 40, 40),
(269, 'Rosalia Indah', 18, '2024-09-24 08:40:00', 40000, 40, 40),
(270, 'Lorena', 31, '2024-09-23 09:20:00', 60000, 40, 40),
(271, 'Lorena', 5, '2024-09-22 11:05:00', 70000, 40, 40),
(272, 'Harapan Jaya', 25, '2024-09-24 08:55:00', 40000, 40, 40),
(273, 'Mitra Rahayu', 1, '2024-09-24 14:15:00', 30000, 40, 40),
(274, 'Mitra Rahayu', 19, '2024-09-24 11:35:00', 30000, 40, 40),
(275, 'Pahala Kencana', 14, '2024-09-22 09:10:00', 40000, 40, 40),
(276, 'Rosalia Indah', 32, '2024-09-22 13:10:00', 30000, 40, 40),
(277, 'Mitra Rahayu', 4, '2024-09-23 11:20:00', 60000, 40, 40),
(278, 'Pahala Kencana', 30, '2024-09-22 10:40:00', 50000, 40, 40),
(279, 'Gunung Harta', 21, '2024-09-23 11:05:00', 40000, 40, 40),
(280, 'Mitra Rahayu', 26, '2024-09-23 10:20:00', 50000, 40, 40),
(281, 'Primajasa', 12, '2024-09-24 14:00:00', 60000, 40, 40),
(282, 'Sinar Jaya', 11, '2024-09-23 09:25:00', 30000, 40, 40),
(283, 'Sinar Jaya', 15, '2024-09-22 08:40:00', 30000, 40, 40),
(284, 'Safari Dharma Raya', 28, '2024-09-24 08:40:00', 30000, 40, 40),
(285, 'Sinar Jaya', 2, '2024-09-22 11:50:00', 30000, 40, 40),
(286, 'Safari Dharma Raya', 13, '2024-09-23 11:00:00', 50000, 40, 40),
(287, 'Pahala Kencana', 7, '2024-09-22 13:15:00', 70000, 40, 40),
(288, 'Damri', 20, '2024-09-23 13:30:00', 30000, 40, 40),
(289, 'Damri', 23, '2024-09-23 11:45:00', 30000, 40, 40),
(290, 'Damri', 27, '2024-09-22 10:20:00', 30000, 40, 40),
(318, 'Mitra Rahayu', 10, '2024-09-22 14:55:00', 40000, 40, 40),
(319, 'Safari Dharma Raya', 35, '2024-09-22 13:45:00', 60000, 40, 40),
(320, 'Sinar Jaya', 29, '2024-09-23 10:55:00', 40000, 40, 40),
(321, 'Safari Dharma Raya', 9, '2024-09-22 13:40:00', 50000, 40, 40),
(322, 'Primajasa', 23, '2024-09-23 12:45:00', 30000, 40, 40),
(323, 'Mitra Rahayu', 20, '2024-09-24 13:50:00', 30000, 40, 40),
(324, 'Sinar Jaya', 18, '2024-09-22 10:05:00', 40000, 40, 40),
(325, 'Damri', 11, '2024-09-24 11:45:00', 30000, 40, 40),
(326, 'Sinar Jaya', 6, '2024-09-22 11:55:00', 30000, 40, 40),
(327, 'Primajasa', 16, '2024-09-24 09:55:00', 30000, 40, 40),
(328, 'Rosalia Indah', 7, '2024-09-23 14:30:00', 70000, 40, 40),
(329, 'Pahala Kencana', 28, '2024-09-23 09:05:00', 30000, 40, 40),
(330, 'Safari Dharma Raya', 33, '2024-09-23 08:25:00', 40000, 40, 40),
(331, 'Harapan Jaya', 21, '2024-09-22 10:40:00', 40000, 40, 40),
(332, 'Mitra Rahayu', 1, '2024-09-24 13:25:00', 30000, 40, 40),
(333, 'Gunung Harta', 22, '2024-09-24 12:35:00', 40000, 40, 40),
(334, 'Mitra Rahayu', 19, '2024-09-24 13:50:00', 30000, 40, 40),
(335, 'Damri', 36, '2024-09-22 12:40:00', 70000, 40, 40),
(336, 'Rosalia Indah', 3, '2024-09-22 14:30:00', 30000, 40, 40),
(337, 'Lorena', 12, '2024-09-22 09:40:00', 60000, 40, 40),
(338, 'Safari Dharma Raya', 5, '2024-09-22 10:00:00', 70000, 40, 40),
(339, 'Rosalia Indah', 32, '2024-09-24 08:15:00', 30000, 40, 40),
(340, 'Pahala Kencana', 24, '2024-09-23 10:20:00', 30000, 40, 40),
(341, 'Pahala Kencana', 26, '2024-09-23 09:30:00', 50000, 40, 40),
(342, 'Gunung Harta', 14, '2024-09-24 08:40:00', 40000, 40, 40),
(343, 'Rosalia Indah', 2, '2024-09-23 11:25:00', 30000, 40, 40),
(344, 'Damri', 17, '2024-09-23 09:20:00', 50000, 40, 40),
(345, 'Mitra Rahayu', 30, '2024-09-24 12:35:00', 50000, 40, 40),
(346, 'Harapan Jaya', 13, '2024-09-23 11:50:00', 50000, 40, 40),
(347, 'Rosalia Indah', 27, '2024-09-22 12:25:00', 30000, 40, 40),
(348, 'Harapan Jaya', 34, '2024-09-24 10:50:00', 50000, 40, 40),
(349, 'Pahala Kencana', 25, '2024-09-24 12:40:00', 40000, 40, 40),
(350, 'Rosalia Indah', 15, '2024-09-24 11:05:00', 30000, 40, 40),
(351, 'Mitra Rahayu', 8, '2024-09-22 10:10:00', 60000, 40, 40),
(352, 'Harapan Jaya', 4, '2024-09-23 10:05:00', 60000, 40, 40),
(353, 'Sinar Jaya', 31, '2024-09-24 14:55:00', 60000, 40, 39);

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

--
-- Table structure for table `cities`
--

CREATE TABLE `cities` (
  `city_id` int NOT NULL,
  `city_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `cities`
--

INSERT INTO `cities` (`city_id`, `city_name`) VALUES
(1, 'Surabaya'),
(2, 'Malang'),
(3, 'Jember'),
(4, 'Banyuwangi'),
(5, 'Kediri'),
(6, 'Madiun');

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

--
-- Table structure for table `routes`
--

CREATE TABLE `routes` (
  `route_id` int NOT NULL,
  `departure_city_id` int DEFAULT NULL,
  `arrival_city_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `routes`
--

INSERT INTO `routes` (`route_id`, `departure_city_id`, `arrival_city_id`) VALUES
(1, 1, 2),
(2, 2, 3),
(3, 3, 4),
(4, 5, 1),
(5, 1, 6),
(6, 6, 5),
(7, 6, 1),
(8, 5, 1),
(9, 4, 1),
(10, 3, 1),
(11, 2, 1),
(12, 6, 2),
(13, 5, 2),
(14, 4, 2),
(15, 3, 2),
(16, 1, 2),
(17, 6, 3),
(18, 5, 3),
(19, 4, 3),
(20, 2, 3),
(21, 1, 3),
(22, 6, 4),
(23, 5, 4),
(24, 3, 4),
(25, 2, 4),
(26, 1, 4),
(27, 6, 5),
(28, 4, 5),
(29, 3, 5),
(30, 2, 5),
(31, 1, 5),
(32, 5, 6),
(33, 4, 6),
(34, 3, 6),
(35, 2, 6),
(36, 1, 6);

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

--
-- Table structure for table `tickets`
--

CREATE TABLE `tickets` (
  `ticket_id` int NOT NULL,
  `user_id` int NOT NULL,
  `bus_id` int NOT NULL,
  `no_seat` varchar(10) NOT NULL,
  `total_price` int NOT NULL,
  `ticket_code` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `tickets`
--

INSERT INTO `tickets` (`ticket_id`, `user_id`, `bus_id`, `no_seat`, `total_price`, `ticket_code`, `created_at`) VALUES
(11, 1, 29, 'B8', 50000, 'LUX-KT91051', '2024-09-28 18:18:15'),
(12, 1, 90, 'C8', 102500, 'LUX-EC33681', '2024-09-28 18:52:47'),
(13, 1, 353, 'C9', 60000, 'LUX-HS51950', '2024-09-28 18:53:27'),
(18, 1, 33, 'B9', 47500, 'LUX-SV87121', '2024-10-12 16:33:01'),
(19, 1, 29, 'B9', 50000, 'LUX-KT65371', '2024-10-12 17:27:55'),
(20, 1, 29, 'B7', 40000, 'LUX-KF64071', '2024-10-13 02:40:37'),
(21, 1, 29, 'A8', 40000, 'LUX-VA35511', '2024-10-13 02:59:33'),
(22, 1, 29, 'A10', 40000, 'LUX-BF47341', '2024-10-13 02:59:47'),
(23, 1, 29, 'C8', 40000, 'LUX-HL70811', '2024-10-13 03:00:39'),
(24, 1, 29, 'A5', 37500, 'LUX-NA94981', '2024-10-13 03:02:11'),
(25, 1, 29, 'C6', 30000, 'LUX-JK24510', '2024-10-13 03:02:49'),
(26, 1, 29, 'C5', 30000, 'LUX-HB87830', '2024-10-13 03:03:31'),
(27, 1, 29, 'B6', 30000, 'LUX-VW67920', '2024-10-13 03:04:53'),
(28, 1, 29, 'A6', 30000, 'LUX-RG68880', '2024-10-13 03:06:52'),
(29, 1, 29, 'B4', 30000, 'LUX-SK23260', '2024-10-13 03:08:23'),
(30, 1, 3, 'B9', 50000, 'LUX-CV33561', '2024-10-13 03:08:43');

--
-- Triggers `tickets`
--
DELIMITER $$
CREATE TRIGGER `decrease_available_seats` AFTER INSERT ON `tickets` FOR EACH ROW BEGIN
    UPDATE Buses
    SET available_seats = available_seats - 1
    WHERE bus_id = NEW.bus_id;
END
$$
DELIMITER ;

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `name`, `gender`, `email`, `password`, `created_at`) VALUES
(1, 'John Doe', NULL, 'john@example.com', '$2a$10$Xw6Oob3HXVOuxIIBihlAUu1Or/i81K6pCObluTVTpzeQ/71bzaxIS', '2024-09-26 04:24:40'),
(5, 'Muhammad Mumtaz', NULL, 'riedyriedy283@gmail.com', '$2a$10$KDxtz2HLZTFpYIAmjPQ5vOYT3lQwC2aUFNyP1C3r3kDAk67DUCZTm', '2024-10-13 05:27:08'),
(6, 'Muhammad Mumtaz', NULL, 'riedyriedy28334@gmail.com', '$2a$10$C/f9YU1UnLwv4iM2Nfd1mO0yO.gMDMJcsIA3Bb55iXIsvwawNuoDS', '2024-10-13 05:40:16');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `buses`
--
ALTER TABLE `buses`
  ADD PRIMARY KEY (`bus_id`),
  ADD KEY `route_id` (`route_id`);

--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
  ADD PRIMARY KEY (`city_id`);

--
-- Indexes for table `routes`
--
ALTER TABLE `routes`
  ADD PRIMARY KEY (`route_id`),
  ADD KEY `departure_city_id` (`departure_city_id`),
  ADD KEY `arrival_city_id` (`arrival_city_id`);

--
-- Indexes for table `tickets`
--
ALTER TABLE `tickets`
  ADD PRIMARY KEY (`ticket_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `bus_id` (`bus_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `buses`
--
ALTER TABLE `buses`
  MODIFY `bus_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=354;

--
-- AUTO_INCREMENT for table `cities`
--
ALTER TABLE `cities`
  MODIFY `city_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `routes`
--
ALTER TABLE `routes`
  MODIFY `route_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;

--
-- AUTO_INCREMENT for table `tickets`
--
ALTER TABLE `tickets`
  MODIFY `ticket_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `buses`
--
ALTER TABLE `buses`
  ADD CONSTRAINT `buses_ibfk_1` FOREIGN KEY (`route_id`) REFERENCES `routes` (`route_id`);

--
-- Constraints for table `routes`
--
ALTER TABLE `routes`
  ADD CONSTRAINT `routes_ibfk_1` FOREIGN KEY (`departure_city_id`) REFERENCES `cities` (`city_id`),
  ADD CONSTRAINT `routes_ibfk_2` FOREIGN KEY (`arrival_city_id`) REFERENCES `cities` (`city_id`);

--
-- Constraints for table `tickets`
--
ALTER TABLE `tickets`
  ADD CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  ADD CONSTRAINT `tickets_ibfk_2` FOREIGN KEY (`bus_id`) REFERENCES `buses` (`bus_id`);
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 */;