-- Rental Unit Inspection & Laundry Management System
-- MySQL / MariaDB (cPanel-friendly)
SET NAMES utf8mb4;
SET time_zone = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;

-- IMPORTANT (cPanel):
-- Create the database in cPanel first, then select it in phpMyAdmin.
-- If you want, you may uncomment the next 2 lines and change the DB name.
-- CREATE DATABASE IF NOT EXISTS rental_inspection CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE rental_inspection;

-- =========================
-- USERS (Admin/Staff)
-- =========================
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default accounts (change passwords after first login)
-- Default password hash below corresponds to: Admin@12345
INSERT INTO users (full_name, email, password_hash, role, is_active)
VALUES
('System Admin', 'admin@example.com',
'$2y$10$k8bGv6Uqg2J8f9S7vQkN0u8hJQw7Yt2m0qk3z5Q4Bf7O6wzB3l9yK',
'admin', 1),
('Default Staff', 'staff@example.com',
'$2y$10$k8bGv6Uqg2J8f9S7vQkN0u8hJQw7Yt2m0qk3z5Q4Bf7O6wzB3l9yK',
'staff', 1)
ON DUPLICATE KEY UPDATE email = email;

-- =========================
-- BUILDINGS
-- =========================
CREATE TABLE IF NOT EXISTS buildings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  address VARCHAR(255) NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- UNITS (each has QR token)
-- =========================
CREATE TABLE IF NOT EXISTS units (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  building_id INT UNSIGNED NOT NULL,
  unit_code VARCHAR(50) NOT NULL,
  floor VARCHAR(20) NULL,
  status ENUM('occupied','vacant','maintenance') NOT NULL DEFAULT 'vacant',
  qr_token VARCHAR(80) NOT NULL UNIQUE,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL DEFAULT NULL,
  CONSTRAINT fk_units_building
    FOREIGN KEY (building_id) REFERENCES buildings(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_units_building (building_id),
  INDEX idx_units_code (unit_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- TENANTS
-- =========================
CREATE TABLE IF NOT EXISTS tenants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(150) NOT NULL,
  phone VARCHAR(40) NULL,
  email VARCHAR(190) NULL,
  move_in_date DATE NULL,
  move_out_date DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  notes TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tenants_unit
    FOREIGN KEY (unit_id) REFERENCES units(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_tenants_unit (unit_id),
  INDEX idx_tenants_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- INVENTORY ITEMS
-- =========================
CREATE TABLE IF NOT EXISTS inventory_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id INT UNSIGNED NOT NULL,
  item_name VARCHAR(150) NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  item_condition ENUM('good','fair','poor','broken') NOT NULL DEFAULT 'good',
  remarks VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inventory_unit
    FOREIGN KEY (unit_id) REFERENCES units(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_inventory_unit (unit_id),
  INDEX idx_inventory_item (item_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- INSPECTIONS (with GEO + ADDRESS)
-- =========================
CREATE TABLE IF NOT EXISTS inspections (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id INT UNSIGNED NOT NULL,
  staff_user_id INT UNSIGNED NOT NULL,
  inspected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes TEXT NULL,

  -- ✅ geotagging (used by your code)
  geo_lat DECIMAL(10,7) NULL,
  geo_lng DECIMAL(10,7) NULL,
  geo_accuracy INT NULL,
  geo_address VARCHAR(255) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_inspections_unit
    FOREIGN KEY (unit_id) REFERENCES units(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_inspections_staff
    FOREIGN KEY (staff_user_id) REFERENCES users(id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  INDEX idx_inspections_unit (unit_id),
  INDEX idx_inspections_staff (staff_user_id),
  INDEX idx_inspections_time (inspected_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- INSPECTION PHOTOS
-- =========================
CREATE TABLE IF NOT EXISTS inspection_photos (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  inspection_id BIGINT UNSIGNED NOT NULL,
  photo_type ENUM('before','after') NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_photos_inspection
    FOREIGN KEY (inspection_id) REFERENCES inspections(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_photos_inspection (inspection_id),
  INDEX idx_photos_type (photo_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- AUDIT LOGS
-- =========================
CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  details TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_audit_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  INDEX idx_audit_user (user_id),
  INDEX idx_audit_time (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- NOTIFICATIONS (Admin alerts)
-- =========================
CREATE TABLE IF NOT EXISTS notifications (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  type VARCHAR(60) NOT NULL,
  title VARCHAR(150) NOT NULL,
  body TEXT NULL,
  link_url VARCHAR(255) NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  actor_user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_notif_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_notif_actor
    FOREIGN KEY (actor_user_id) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  INDEX idx_notif_user (user_id),
  INDEX idx_notif_read (is_read),
  INDEX idx_notif_time (created_at),
  INDEX idx_notif_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- LAUNDRY BAGS
-- =========================
CREATE TABLE IF NOT EXISTS laundry_bags (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  unit_id INT UNSIGNED NOT NULL,

  bag_number VARCHAR(50) NOT NULL UNIQUE,
  status ENUM('picked_up','delivered','clean','dirty') NOT NULL DEFAULT 'dirty',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  CONSTRAINT fk_laundry_bags_unit
    FOREIGN KEY (unit_id) REFERENCES units(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  INDEX idx_laundry_unit (unit_id),
  INDEX idx_laundry_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- LAUNDRY BAG ITEMS (contents inside a bag)
-- =========================
CREATE TABLE IF NOT EXISTS laundry_bag_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bag_id INT UNSIGNED NOT NULL,
  item_type ENUM('bedsheet','pillowcase','towel','doormat','other') NOT NULL DEFAULT 'other',
  item_name VARCHAR(100) NULL,
  qty INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_laundry_item_bag
    FOREIGN KEY (bag_id) REFERENCES laundry_bags(id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_laundry_item_bag (bag_id),
  INDEX idx_laundry_item_type (item_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =========================
-- LAUNDRY BAG EVENTS (transactions/history + GEO)
-- =========================
CREATE TABLE IF NOT EXISTS laundry_bag_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bag_id INT UNSIGNED NOT NULL,
  item_type VARCHAR(50) NOT NULL,
  action VARCHAR(50) NOT NULL,
  status_after VARCHAR(50) NOT NULL,
  note TEXT NULL,
  changed_by_user_id INT UNSIGNED NULL,

  -- ✅ geotagging (used by your code)
  geo_lat DECIMAL(10,7) NULL,
  geo_lng DECIMAL(10,7) NULL,
  geo_accuracy INT NULL,
  geo_address VARCHAR(255) NULL,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

  CONSTRAINT fk_laundry_event_bag
    FOREIGN KEY (bag_id) REFERENCES laundry_bags(id)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT fk_laundry_event_user
    FOREIGN KEY (changed_by_user_id) REFERENCES users(id)
    ON DELETE SET NULL ON UPDATE CASCADE,

  INDEX idx_laundry_event_bag (bag_id),
  INDEX idx_laundry_event_time (created_at),
  INDEX idx_laundry_event_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
