CREATE TABLE "Route" (
  "route_code" NUMERIC NOT NULL,
  "route_name" VARCHAR(20) NOT NULL,
  PRIMARY KEY ("route_code")
);

CREATE TABLE "Bus" (
  "Bus_code" NUMERIC NOT NULL,
  "Bus_plate" VARCHAR(10) NOT NULL,
  "Seats" NUMERIC NOT NULL,
  PRIMARY KEY ("Bus_code")
);

CREATE TABLE "Member" (
  "U_Name" VARCHAR(20) NOT NULL,
  "U_pass" VARCHAR(255) NOT NULL,
  "Email" VARCHAR(20) NOT NULL,
  "Sname" VARCHAR(50) NOT NULL,
  "Lname" VARCHAR(50) NOT NULL,
  "Address" VARCHAR(50),
  "Role_name" VARCHAR(10),
  "U_phone" VARCHAR(10),
  PRIMARY KEY ("U_Name")
);

CREATE TABLE "Station" (
  "St_code" NUMERIC NOT NULL,
  "St_name" VARCHAR(20) NOT NULL,
  PRIMARY KEY ("St_code")
);


CREATE TABLE "Route_stop" (
  "stop_no" NUMERIC NOT NULL,
  "route_code" NUMERIC,
  "St_code" NUMERIC,
  "seq_no" NUMERIC,
  PRIMARY KEY ("stop_no")
);
ALTER TABLE "Route_stop" ADD CONSTRAINT "route_code" FOREIGN KEY ("route_code") REFERENCES "Route" ("route_code");
ALTER TABLE "Route_stop" ADD CONSTRAINT "St_code" FOREIGN KEY ("St_code") REFERENCES "Station" ("St_code");

CREATE TABLE "Schedule" (
  "Sch_code" NUMERIC NOT NULL,
  "Time" TIMESTAMP NOT NULL,
  "driver" VARCHAR(20) NOT NULL,
  "bus" NUMERIC NOT NULL,
  "route_code" NUMERIC NOT NULL,
  PRIMARY KEY ("Sch_code")
);
ALTER TABLE "Schedule" ADD CONSTRAINT "Bus_code" FOREIGN KEY ("bus") REFERENCES "Bus" ("Bus_code");
ALTER TABLE "Schedule" ADD CONSTRAINT "Bus_driver" FOREIGN KEY ("driver") REFERENCES "Member" ("U_Name");
ALTER TABLE "Schedule" ADD CONSTRAINT "sch_route_code" FOREIGN KEY ("route_code") REFERENCES "Route" ("route_code");

CREATE TABLE "Ticket" (
  "Ticket_id" NUMERIC NOT NULL,
  "sch_code" NUMERIC NOT NULL,
  "tick_status" VARCHAR(20) NOT NULL,
  "U_Name" VARCHAR(20) NOT NULL,
  "seat_no" NUMERIC NOT NULL,
  "br_station" NUMERIC NOT NULL,
  "de_station" NUMERIC NOT NULL,
  "booking_time" TIMESTAMPTZ NOT NULL,
  "expire_time" TIMESTAMPTZ NOT NULL,
  PRIMARY KEY ("Ticket_id")
);
ALTER TABLE "Ticket" ADD CONSTRAINT "sch_code" FOREIGN KEY ("sch_code") REFERENCES "Schedule" ("Sch_code");
ALTER TABLE "Ticket" ADD CONSTRAINT "Username" FOREIGN KEY ("U_Name") REFERENCES "Member" ("U_Name");
ALTER TABLE "Ticket" ADD CONSTRAINT "Boarding" FOREIGN KEY ("br_station") REFERENCES "Station" ("St_code");
ALTER TABLE "Ticket" ADD CONSTRAINT "Destination" FOREIGN KEY ("de_station") REFERENCES "Station" ("St_code");
ALTER TABLE "Member" 
ADD CONSTRAINT check_uname_english 
CHECK ("U_Name" REGEXP '^[a-zA-Z0-9_]+$');




INSERT INTO "Member" (
  "U_Name", "U_pass", "Email", "Sname", "Lname", "Address", "Role_name", "U_phone"
) VALUES 
('somchai_j', '$2a$12$e8vX9YJ3p4Q9Z0L2K3M1OuN7P8Q9R0S1T2U3V4W5X6Y7Z8A9B0C1D', 'somchai@example.com', 'สมชาย', 'ใจดี', 'ระยอง', 'Driver', '0812345678'),
('sommai_j', '$2a$12$f9wY0ZK4q5R0A1M3L4N2PvO8Q9R0S1T2U3V4W5X6Y7Z8A9B0C1E', 'sommai@example.com', 'สมหมาย', 'ใจรัก', 'ระยอง', 'Member', '0823456789'),
('somkuan_j', '$2a$12$g0xZ1AL5r6S1B2N4M5O3QwP9R0S1T2U3V4W5X6Y7Z8A9B0C1F', 'somkuan@example.com', 'สมควร', 'ใจงาม', 'ระยอง', 'Driver', '0834567890'),
('areerat_s', '$2a$12$h1ya2BM6s7T2C3O5N6P4RxQ0S1T2U3V4W5X6Y7Z8A9B0C1G', 'areerat@example.com', 'อารีรัตน์', 'ศรีสุข', 'ระยอง', 'Member', '0845678901'),
('worapon_t', '$2a$12$i2zb3CN7t8U3D4P6O7Q5SyR1T2U3V4W5X6Y7Z8A9B0C1H', 'worapon@example.com', 'วรพล', 'เทพทอง', 'ระยอง', 'Driver', '0856789012'),
('nantana_j', '$2a$12$j3ac4DO8u9V4E5Q7P8R6TzS2U3V4W5X6Y7Z8A9B0C1I', 'nantana@example.com', 'นันทนา', 'ใจตรง', 'ระยอง', 'Member', '0867890123'),
('test_student', '$2a$12$k4bd5EP9v0W5F6R8Q9S7UAT3V4W5X6Y7Z8A9B0C1J', 'student@example.com', 'นักศึกษา', 'ทดสอบ', 'ระยอง', 'Admin', '0899999999');

INSERT INTO "Station" ("St_code", "St_name") 
VALUES 
(1, 'มหาวิทยาลัยเทคโนโลยีมหานคร'),
(2, 'โลตัส หนองจอก'),
(3, 'โรงพยาบาลหนองจอก'),
(4, 'Big C หนองจอก'),
(5, 'สวนสาธารณะหนองจอก'),
(6, 'ร้านส้มตำป้านาง');

INSERT INTO "Route" ("route_code", "route_name") 
VALUES 
(1, 'เส้นทางที่ 1 (ม.มหานคร - หนองจอก วนลูป)'),
(2, 'เส้นทางที่ 2 (ม.มหานคร - ร้านส้มตำป้านาง)'),
(3, 'เส้นทางที่ 3 (Big C หนองจอก - ม.มหานคร)');

INSERT INTO "Bus" ("Bus_code", "Bus_plate", "Seats") 
VALUES 
(1, 'สย 2591', 9),
(2, 'สย 2599', 9),
(3, 'ชย 7788', 9),
(4, 'บก 1130', 20),
(5, 'กข 4455', 20),
(6, 'นน 5566', 15),
(7, 'นม 8899', 15);

INSERT INTO "Route_stop" ("stop_no", "route_code", "St_code", "seq_no") 
VALUES
(1, 1, 1, 1),
(2, 1, 2, 2),
(3, 1, 3, 3),
(4, 1, 4, 4),
(5, 1, 3, 5),
(6, 1, 2, 6),
(7, 1, 1, 7),
(8, 2, 1, 1),
(9, 2, 2, 2),
(10, 2, 5, 3),
(11, 2, 6, 4),
(12, 3, 4, 1),
(13, 3, 2, 2),
(14, 3, 5, 3),
(15, 3, 6, 4),
(16, 3, 1, 5);

INSERT INTO "Schedule" ("Sch_code", "Time", "driver", "bus", "route_code") 
VALUES
(101, '2025-04-12 09:30:00', 'somchai_j', 1, 1),
(102, '2025-04-12 11:00:00', 'sommai_j', 1, 1),
(103, '2025-04-12 13:00:00', 'somchai_j', 4, 1),
(104, '2025-04-12 15:00:00', 'somchai_j', 4, 1),
(201, '2025-04-12 09:30:00', 'somkuan_j', 2, 2),
(202, '2025-04-12 11:00:00', 'somkuan_j', 2, 2),
(203, '2025-04-12 13:00:00', 'sommai_j', 2, 2),
(204, '2025-04-12 15:00:00', 'sommai_j', 2, 2);

INSERT INTO "Ticket" (
  "Ticket_id", "sch_code", "tick_status", "U_Name", "seat_no", 
  "br_station", "de_station", "booking_time", "expire_time"
) 
VALUES 
(1001, 101, 'Check-in', 'somchai_j', 1, 1, 4, '2025-04-11 10:00:00', '2025-04-12 09:30:00'),
(1002, 101, 'Check-in', 'sommai_j', 1, 1, 2, '2025-04-11 10:30:00', '2025-04-12 09:30:00'),
(1003, 102, 'Check-in', 'somkuan_j', 1, 1, 3, '2025-04-11 11:00:00', '2025-04-12 11:00:00'),
(1004, 102, 'Cancel', 'areerat_s', 1, 1, 4, '2025-04-11 11:30:00', '2025-04-12 11:00:00'),
(1005, 201, 'No Show', 'worapon_t', 1, 1, 6, '2025-04-11 12:00:00', '2025-04-12 09:30:00'),
(1006, 201, 'Booked', 'test_student', 2, 1, 2, '2025-04-11 12:30:00', '2025-04-12 09:30:00');