-- Mitho Yatra: database schema (MySQL 5.7+/MariaDB 10.3+)
-- Import: mysql -u user -p mitho_yatra < schema.sql

CREATE DATABASE IF NOT EXISTS mitho_yatra CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mitho_yatra;

-- ---------- Admin users (dashboard) ----------
CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('superadmin','manager','staff') NOT NULL DEFAULT 'manager',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------- Customers (website + Android app) ----------
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(20) NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- API tokens for the Android app
CREATE TABLE api_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL UNIQUE,   -- sha256 of the token
  device_name VARCHAR(100) NULL,
  expires_at DATETIME NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Catalogue ----------
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  title VARCHAR(200) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  summary VARCHAR(300) NULL,
  description TEXT NULL,
  destination VARCHAR(150) NULL,
  duration_days INT NOT NULL,
  difficulty ENUM('easy','moderate','hard') DEFAULT 'easy',
  price_per_person DECIMAL(10,2) NOT NULL,        -- NPR
  advance_type ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
  advance_value DECIMAL(10,2) NOT NULL DEFAULT 30, -- 30% or fixed NPR amount
  inclusions TEXT NULL,                            -- one item per line
  exclusions TEXT NULL,
  cover_image VARCHAR(255) NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE package_images (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE itinerary_days (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  day_number INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description TEXT NULL,
  meals VARCHAR(100) NULL,
  stay VARCHAR(150) NULL,
  UNIQUE KEY uq_pkg_day (package_id, day_number),
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Date slots (managed by admin) ----------
-- A slot is one departure of a package. Group trip: confirmed only when
-- booked_seats >= min_seats before booking_deadline.
CREATE TABLE date_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  min_seats INT NOT NULL DEFAULT 6,
  max_seats INT NOT NULL DEFAULT 15,
  booked_seats INT NOT NULL DEFAULT 0,             -- seats held by pending/confirmed bookings
  price_override DECIMAL(10,2) NULL,               -- optional special price for this slot
  booking_deadline DATE NOT NULL,                  -- last day to book / decide go-or-cancel
  status ENUM('open','confirmed','full','cancelled','completed') NOT NULL DEFAULT 'open',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_slot_pkg_date (package_id, start_date),
  FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE,
  CHECK (min_seats <= max_seats)
) ENGINE=InnoDB;

-- ---------- Bookings ----------
CREATE TABLE bookings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_code VARCHAR(20) NOT NULL UNIQUE,        -- e.g. MY-2026-00042
  user_id INT NULL,
  package_id INT NOT NULL,
  slot_id INT NOT NULL,
  contact_name VARCHAR(100) NOT NULL,
  contact_email VARCHAR(150) NOT NULL,
  contact_phone VARCHAR(20) NOT NULL,
  seats INT NOT NULL DEFAULT 1,
  price_per_person DECIMAL(10,2) NOT NULL,         -- price locked at booking time
  total_amount DECIMAL(10,2) NOT NULL,
  advance_amount DECIMAL(10,2) NOT NULL,
  paid_amount DECIMAL(10,2) NOT NULL DEFAULT 0,
  status ENUM('pending_payment','reserved','confirmed','cancelled','refunded','completed')
         NOT NULL DEFAULT 'pending_payment',
  -- pending_payment: seats held briefly, advance not paid
  -- reserved: advance paid, waiting for min seats
  -- confirmed: min seats reached, trip is going
  hold_expires_at DATETIME NULL,                   -- release seats if advance not paid by then
  special_requests TEXT NULL,
  source ENUM('web','android','admin') DEFAULT 'web',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_book_slot (slot_id, status),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (package_id) REFERENCES packages(id),
  FOREIGN KEY (slot_id) REFERENCES date_slots(id)
) ENGINE=InnoDB;

CREATE TABLE travelers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id INT NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  age INT NULL,
  gender ENUM('male','female','other') NULL,
  id_type VARCHAR(30) NULL,                        -- citizenship / passport
  id_number VARCHAR(50) NULL,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Payments ----------
CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  booking_id INT NOT NULL,
  gateway ENUM('esewa','khalti','fonepay','bank_transfer','cash') NOT NULL,
  type ENUM('advance','balance','refund') NOT NULL DEFAULT 'advance',
  amount DECIMAL(10,2) NOT NULL,
  transaction_ref VARCHAR(100) NULL,               -- gateway transaction id
  status ENUM('initiated','success','failed','refunded') NOT NULL DEFAULT 'initiated',
  gateway_payload TEXT NULL,                       -- raw response for audit
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_pay_booking (booking_id),
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Misc ----------
CREATE TABLE settings (
  `key` VARCHAR(60) PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB;

CREATE TABLE activity_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  admin_id INT NULL,
  action VARCHAR(100) NOT NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------- Seed data ----------
INSERT INTO settings (`key`,`value`) VALUES
 ('site_name','Mitho Yatra'),
 ('currency','NPR'),
 ('hold_minutes','30'),
 ('contact_phone',''),
 ('contact_email','');

INSERT INTO categories (name, slug) VALUES
 ('Trekking','trekking'),('Pilgrimage','pilgrimage'),('Jungle Safari','jungle-safari'),('City & Culture','city-culture');

-- Default admin is created by setup.php (never ship a fixed password).
