· 8 years ago · Mar 13, 2018, 09:22 AM
1CREATE TABLE IF NOT EXISTS `matunda_db`.`Buses` (
2 `regno` VARCHAR(20) NOT NULL,
3 `leftseats` INT NOT NULL,
4 `rightseats` INT NOT NULL,
5 `backseats` VARCHAR(45) NOT NULL,
6 `doorside` VARCHAR(1) NOT NULL DEFAULT 'r',
7 `driver` INT NOT NULL,
8 `total_seats` INT NOT NULL,
9 `created_at` INT NOT NULL,
10 `created_by` INT NOT NULL,
11 `updated_at` INT NOT NULL,
12 `updated_by` INT NOT NULL,
13 PRIMARY KEY (`regno`),
14 INDEX `BUS_AUTHOR_idx` (`created_by` ASC),
15 INDEX `BUS_EDITOR_idx` (`updated_by` ASC),
16 CONSTRAINT `BUS_AUTHOR`
17 FOREIGN KEY (`created_by`)
18 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
19 ON DELETE NO ACTION
20 ON UPDATE NO ACTION,
21 CONSTRAINT `BUS_EDITOR`
22 FOREIGN KEY (`updated_by`)
23 REFERENCES `volcano_shared`.`Staffs` (`mobile`)
24 ON DELETE NO ACTION
25 ON UPDATE NO ACTION)
26ENGINE = InnoDB;
27
28CREATE TABLE IF NOT EXISTS `matunda_db`.`Stops` (
29 `name` VARCHAR(100) NOT NULL,
30 `country` VARCHAR(45) NOT NULL DEFAULT 'RW',
31 `created_at` INT NOT NULL,
32 `created_by` INT NOT NULL,
33 `updated_at` INT NOT NULL,
34 `updated_by` INT NOT NULL,
35 PRIMARY KEY (`name`),
36 INDEX `STOPS_AUTHOR_idx` (`created_by` ASC),
37 INDEX `STOPS_EDITOR_idx` (`updated_by` ASC),
38 CONSTRAINT `STOPS_AUTHOR`
39 FOREIGN KEY (`created_by`)
40 REFERENCES `volcano_shared`.`Staffs
41CREATE TABLE IF NOT EXISTS `matunda_db`.`Routes` (
42 `id` INT NOT NULL AUTO_INCREMENT,
43 `start` VARCHAR(100) NOT NULL,
44 `end` VARCHAR(100) NOT NULL,
45 `parent` INT NULL,
46 `idx` INT NOT NULL,
47 `name` VARCHAR(45) NULL,
48 `send_sms` INT NOT NULL DEFAULT 0,
49 `created_at` INT NOT NULL,
50 `created_by` INT NOT NULL,
51 `updated_at` INT NOT NULL,
52 `updated_by` INT NOT NULL,
53 `has_promotion` INT NOT NULL DEFAULT 0,
54 `is_intl` INT NOT NULL DEFAULT 0,
55 `customer_care` VARCHAR(45) NOT NULL DEFAULT 'No Number',
56 `return` INT NULL,
57 `is_return` INT NOT NULL DEFAULT 0,
58
59CREATE TABLE IF NOT EXISTS `matunda_db`.`Points` (
60 `customer` VARCHAR(255) NOT NULL,
61 `start` VARCHAR(100) NOT NULL,
62 `end` VARCHAR(100) NOT NULL,
63 `points` INT NULL,
64 `created_at` INT NULL,
65 `updated_at` INT NULL,
66 PRIMARY KEY (`customer`, `start`, `end`),
67
68CREATE TABLE IF NOT EXISTS `matunda_db`.`Tickets` (
69 `id` INT NOT NULL AUTO_INCREMENT,
70 `ticket` VARCHAR(100) NOT NULL,
71 `route` INT NOT NULL,
72 `bus` VARCHAR(20) NOT NULL,
73 `start` VARCHAR(100) NOT NULL,
74 `end` VARCHAR(100) NOT NULL,
75 `dept_date` DATE NOT NULL,
76 `dept_time` VARCHAR(45) NOT NULL,
77 `customer` VARCHAR(255) NOT NULL,
78 `issued_on` INT NOT NULL,
79 `machine_serial` VARCHAR(100) NOT NULL,
80 `price` INT NOT NULL,
81 `currency` VARCHAR(5) NOT NULL,
82 `discount` INT NOT NULL DEFAULT 0,
83 `seat` INT NOT NULL,
84 `is_deleted` INT NULL DEFAULT 0,
85 `is_promo` INT NOT NULL DEFAULT 0,
86 `is_staff` INT NOT NULL DEFAULT 0,
87 `status` VARCHAR(2) NULL COMMENT 'BO - Booked\nCA - Cancelled\nCO - Confirmed\n',
88 `expired_in` INT NOT NULL,
89 `created_at` INT NOT NULL,
90 `created_by` INT NOT NULL,
91 `updated_at` INT NOT NULL,
92 `updated_by` INT NOT NULL,
93 `mobile_money` VARCHAR(100) NULL,
94 PRIMARY KEY (`id`),
95
96CREATE TABLE IF NOT EXISTS `matunda_db`.`PlannedRoutes` (
97 `route` INT NOT NULL,
98 `dept_date` DATE NOT NULL,
99 `dept_time` VARCHAR(5) NOT NULL,
100 `bus` VARCHAR(20) NOT NULL,
101 `priority` INT NOT NULL DEFAULT 10,
102 `capacity` INT NULL DEFAULT -1,
103 `created_at` INT NOT NULL,
104 `created_by` INT NOT NULL,
105 `updated_at` INT NOT NULL,
106 `updated_by` INT NOT NULL,
107 `is_active` INT NOT NULL DEFAULT 1,
108 PRIMARY KEY (`route`, `dept_date`, `dept_time`, `bus`),
109
110CREATE TABLE IF NOT EXISTS `matunda_db`.`SMS` (
111 `id` INT NOT NULL AUTO_INCREMENT,
112 `route` INT NOT NULL,
113 `dept_date` DATE NOT NULL,
114 `dept_time` VARCHAR(45) NOT NULL,
115 `message` VARCHAR(160) NOT NULL,
116 `ticket` INT NOT NULL,
117 `status` VARCHAR(45) NULL DEFAULT 'pending',
118 `customer` VARCHAR(45) NOT NULL,
119 `created_at` INT NOT NULL,
120 `created_by` INT NOT NULL,
121 `updated_at` INT NOT NULL,
122 `updated_by` INT NOT NULL,
123 PRIMARY KEY (`id`))
124ENGINE = InnoDB;
125
126CREATE TABLE IF NOT EXISTS `matunda_db`.`ReservedSeats` (
127 `ticket` INT NOT NULL COMMENT 'Additional table to extend Tickets table\nAll Duplicate tables because of Long routes e.g.\nA-E will reserve AB-BC-CD-DE so duplicating \nthese on Tickets are unacceptable. So we just\ncreate this table to show that the seat is reserved\nfor those sub routes and ticket remains one',
128 `start` VARCHAR(100) NOT NULL,
129 `end` VARCHAR(100) NOT NULL,
130 `route` INT NOT NULL,
131 `seat` INT NOT NULL,
132 `dept_date` DATE NOT NULL,
133 `dept_time` VARCHAR(5) NOT NULL,
134 `bus` VARCHAR(20) NOT NULL,
135 `issued_on` INT NOT NULL,
136 `expires_in` INT NOT NULL,
137 `status` VARCHAR(2) NOT NULL,
138 PRIMARY KEY (`start`, `end`, `route`, `seat`, `dept_date`, `dept_time`, `bus`),
139
140CREATE TABLE IF NOT EXISTS `matunda_db`.`SellableRoutes` (
141 `staff` INT NOT NULL,
142 `route` INT NOT NULL,
143 `created_at` INT NOT NULL,
144 `created_by` INT NOT NULL,
145 `updated_at` INT NOT NULL,
146 `updated_by` INT NOT NULL,
147 PRIMARY KEY (`staff`, `route`),
148 INDEX `fk_Staffs_has_Routes_Routes1_idx` (`route` ASC),
149CREATE TABLE IF NOT EXISTS `matunda_db`.`Pricing` (
150 `start` VARCHAR(100) NOT NULL,
151 `end` VARCHAR(100) NOT NULL,
152 `route` INT NOT NULL,
153 `price` INT NOT NULL,
154 `currency` VARCHAR(5) NOT NULL,
155 `created_at` INT NOT NULL,
156 `created_by` INT NOT NULL,
157 `updated_at` INT NOT NULL,
158 `updated_by` INT NOT NULL,
159 PRIMARY KEY (`start`, `end`, `route`, `currency`),
160
161CREATE TABLE IF NOT EXISTS `matunda_db`.`RouteCards` (
162 `card` INT NOT NULL,
163 `start` VARCHAR(100) NOT NULL,
164 `end` VARCHAR(100) NOT NULL,
165 `price` INT NOT NULL,
166 `currency` VARCHAR(4) NOT NULL DEFAULT 'RWF',
167 `remaining_trips` INT NOT NULL,
168 `total_trips` INT NOT NULL,
169 `owner` VARCHAR(255) NULL,
170 `is_sold` INT NOT NULL DEFAULT 0,
171 `sold_at` INT NULL,
172 `sold_by` INT NULL,
173 `created_at` INT NOT NULL,
174 `created_by` INT NOT NULL,
175 `updated_at` INT NOT NULL,
176 `updated_by` INT NOT NULL,
177 `pos` VARCHAR(100) NULL,
178 PRIMARY KEY (`card`),
179
180CREATE TABLE IF NOT EXISTS `matunda_db`.`BoardingTimes` (
181 `route` INT NOT NULL,
182 `start` VARCHAR(100) NOT NULL,
183 `end` VARCHAR(100) NOT NULL,
184 `offset` INT NOT NULL,
185 `created_at` INT NOT NULL,
186 `created_by` INT NOT NULL,
187 `updated_at` INT NOT NULL,
188 `updated_by` INT NOT NULL,
189 PRIMARY KEY (`route`, `start`, `end`),
190
191CREATE TABLE IF NOT EXISTS `matunda_db`.`CardLogs` (
192 `id` INT NOT NULL,
193 `card` INT NOT NULL,
194 `created_at` INT NOT NULL,
195 `created_by` INT NOT NULL,
196 `updated_at` INT NOT NULL,
197 `updated_by` INT NOT NULL,
198 `remained_trips` INT NOT NULL,
199 PRIMARY KEY (`id`),