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

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(30) NOT NULL,
  password VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  UNIQUE KEY uq_users_phone (phone),
  KEY idx_users_role (role),
  KEY idx_users_status (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS students (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  roll_no VARCHAR(50) NOT NULL,
  hostel_name VARCHAR(120) NOT NULL,
  room_no VARCHAR(50) NOT NULL,
  course VARCHAR(120) NOT NULL,
  can_edit_profile TINYINT(1) NOT NULL DEFAULT 1,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  deleted_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_students_user (user_id),
  UNIQUE KEY uq_students_roll (roll_no),
  KEY idx_students_status (status),
  KEY idx_students_deleted (deleted_at),
  CONSTRAINT fk_students_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meal_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  cutoff_time TIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_meal_types_name (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS food_types (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  type VARCHAR(20) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_food_types_type (type)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meal_rates (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  meal_type_id INT UNSIGNED NOT NULL,
  food_type_id INT UNSIGNED NOT NULL,
  rate DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_meal_rates_combo (meal_type_id, food_type_id),
  CONSTRAINT fk_meal_rates_meal FOREIGN KEY (meal_type_id) REFERENCES meal_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_meal_rates_food FOREIGN KEY (food_type_id) REFERENCES food_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS menus (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  meal_type_id INT UNSIGNED NOT NULL,
  food_type_id INT UNSIGNED NOT NULL,
  menu_name VARCHAR(190) NOT NULL,
  date DATE NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_menus_day (date, meal_type_id, food_type_id),
  KEY idx_menus_date (date),
  CONSTRAINT fk_menus_meal FOREIGN KEY (meal_type_id) REFERENCES meal_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_menus_food FOREIGN KEY (food_type_id) REFERENCES food_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  meal_type_id INT UNSIGNED NOT NULL,
  food_type_id INT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL,
  rate DECIMAL(10,2) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  order_date DATE NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending_payment',
  is_guest TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_orders_student (student_id),
  KEY idx_orders_date (order_date),
  KEY idx_orders_status (status),
  KEY idx_orders_is_guest (is_guest),
  CONSTRAINT fk_orders_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_orders_meal FOREIGN KEY (meal_type_id) REFERENCES meal_types(id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_orders_food FOREIGN KEY (food_type_id) REFERENCES food_types(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS packages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  type VARCHAR(20) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  duration INT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY idx_packages_type (type)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_packages (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  package_id INT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  PRIMARY KEY (id),
  KEY idx_student_packages_student (student_id),
  KEY idx_student_packages_status (status),
  CONSTRAINT fk_student_packages_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_student_packages_package FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS student_subscription_adjustments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  student_package_id INT UNSIGNED NULL,
  applied_in_student_package_id INT UNSIGNED NULL,
  type VARCHAR(40) NOT NULL,
  days INT NOT NULL,
  amount DECIMAL(10,2) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  for_date DATE NULL,
  note VARCHAR(255) NULL,
  created_by_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  applied_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_adj_student_type_date_pkg (student_id, type, for_date, student_package_id),
  KEY idx_adj_student (student_id),
  KEY idx_adj_student_package (student_package_id),
  KEY idx_adj_status (status),
  KEY idx_adj_type (type),
  CONSTRAINT fk_adj_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_adj_student_package FOREIGN KEY (student_package_id) REFERENCES student_packages(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_adj_applied_in FOREIGN KEY (applied_in_student_package_id) REFERENCES student_packages(id) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_adj_created_by FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leaves (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  reason TEXT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  PRIMARY KEY (id),
  KEY idx_leaves_student (student_id),
  KEY idx_leaves_status (status),
  KEY idx_leaves_range (from_date, to_date),
  CONSTRAINT fk_leaves_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id INT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  payment_mode VARCHAR(20) NOT NULL DEFAULT 'UPI',
  transaction_id VARCHAR(120) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'success',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_payments_student (student_id),
  KEY idx_payments_created (created_at),
  UNIQUE KEY uq_payments_txn (transaction_id),
  CONSTRAINT fk_payments_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS permissions (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  module_name VARCHAR(80) NOT NULL,
  can_view TINYINT(1) NOT NULL DEFAULT 0,
  can_add TINYINT(1) NOT NULL DEFAULT 0,
  can_edit TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY uq_permissions_user_module (user_id, module_name),
  CONSTRAINT fk_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;
