USE uptajcom_chatbot;

CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(120) NOT NULL,
  username VARCHAR(60) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE terminals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(120) NOT NULL UNIQUE,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  urutan INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE provinces (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL UNIQUE,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  urutan INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE cities (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  province_id BIGINT UNSIGNED NOT NULL,
  nama VARCHAR(120) NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  urutan INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_city_province FOREIGN KEY (province_id) REFERENCES provinces(id),
  UNIQUE KEY uq_city_province (province_id,nama)
) ENGINE=InnoDB;

CREATE TABLE bus_operators (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama_po VARCHAR(150) NOT NULL,
  terminal_id BIGINT UNSIGNED NOT NULL,
  city_id BIGINT UNSIGNED NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  urutan INT NOT NULL DEFAULT 0,
  catatan TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_po_terminal FOREIGN KEY (terminal_id) REFERENCES terminals(id),
  CONSTRAINT fk_po_city FOREIGN KEY (city_id) REFERENCES cities(id),
  KEY idx_po_route (terminal_id,city_id,aktif)
) ENGINE=InnoDB;

CREATE TABLE operator_contacts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  operator_id BIGINT UNSIGNED NOT NULL,
  nama_loket VARCHAR(150) NOT NULL,
  nomor_whatsapp VARCHAR(25) NOT NULL,
  keterangan VARCHAR(255) NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_contact_operator FOREIGN KEY (operator_id) REFERENCES bus_operators(id) ON DELETE CASCADE,
  KEY idx_contact_operator (operator_id,aktif)
) ENGINE=InnoDB;

CREATE TABLE chat_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nomor_whatsapp VARCHAR(25) NOT NULL UNIQUE,
  nama_pengguna VARCHAR(120) NULL,
  step VARCHAR(40) NOT NULL DEFAULT 'menu_terminal',
  terminal_id BIGINT UNSIGNED NULL,
  province_id BIGINT UNSIGNED NULL,
  city_id BIGINT UNSIGNED NULL,
  operator_id BIGINT UNSIGNED NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'aktif',
  last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_session_terminal FOREIGN KEY (terminal_id) REFERENCES terminals(id) ON DELETE SET NULL,
  CONSTRAINT fk_session_province FOREIGN KEY (province_id) REFERENCES provinces(id) ON DELETE SET NULL,
  CONSTRAINT fk_session_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL,
  CONSTRAINT fk_session_operator FOREIGN KEY (operator_id) REFERENCES bus_operators(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE chat_messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nomor_whatsapp VARCHAR(25) NOT NULL,
  arah ENUM('masuk','keluar') NOT NULL,
  pesan TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_message_number_date (nomor_whatsapp,created_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO terminals (nama,urutan) VALUES
('Terminal Pulogebang',1),('Terminal Kalideres',2),('Terminal Kampung Rambutan',3),
('Terminal Tanjung Priok',4),('Terminal Grogol',5),('Terminal Lebak Bulus',6),
('Terminal Muara Angke',7);

INSERT IGNORE INTO provinces (nama,urutan) VALUES
('Lampung',1),('Sumatera Selatan',2),('Sumatera Barat',3),('Jawa Barat',4),
('Jawa Tengah',5),('Jawa Timur',6),('Banten',7),('DI Yogyakarta',8),('Bali',9);
