-- ============================================================
-- EasyOp - Multi Tourism Agency Management System
-- Database Schema
-- ============================================================

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

-- ============================================================
-- USERS, ROLES & PERMISSIONS
-- ============================================================

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    module VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id INT NOT NULL,
    permission_id INT NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- ============================================================
-- AGENCIES
-- ============================================================

CREATE TABLE agencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
    address TEXT,
    city VARCHAR(100),
    logo VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- HOTELS
-- ============================================================

CREATE TABLE hotels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    address TEXT,
    city VARCHAR(100),
    stars INT DEFAULT 3,
    phone VARCHAR(20),
    email VARCHAR(255),
    website VARCHAR(500),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    check_in_time TIME DEFAULT '14:00:00',
    check_out_time TIME DEFAULT '12:00:00',
    amenities JSON,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE hotel_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE hotel_rooms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hotel_id INT NOT NULL,
    room_type VARCHAR(100) NOT NULL,
    description TEXT,
    capacity INT DEFAULT 2,
    price_per_night DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'EGP',
    is_available TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (hotel_id) REFERENCES hotels(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- TICKETS / PLACES
-- ============================================================

CREATE TABLE places (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    category VARCHAR(100) NOT NULL COMMENT 'museum, monument, park, etc.',
    address TEXT,
    city VARCHAR(100),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    opening_hours VARCHAR(200),
    ticket_price DECIMAL(10,2),
    currency VARCHAR(10) DEFAULT 'EGP',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE place_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    place_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (place_id) REFERENCES places(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- CAR RENTAL
-- ============================================================

CREATE TABLE cars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    year INT,
    color VARCHAR(50),
    plate_number VARCHAR(20),
    seats INT DEFAULT 5,
    transmission ENUM('automatic', 'manual') DEFAULT 'automatic',
    fuel_type ENUM('petrol', 'diesel', 'electric', 'hybrid') DEFAULT 'petrol',
    price_per_day DECIMAL(10,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'EGP',
    features JSON,
    is_available TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE car_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    car_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- BUSES
-- ============================================================

CREATE TABLE buses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    bus_type VARCHAR(100) COMMENT 'luxury, standard, mini, etc.',
    capacity INT NOT NULL,
    features JSON,
    price_per_trip DECIMAL(10,2),
    price_per_day DECIMAL(10,2),
    currency VARCHAR(10) DEFAULT 'EGP',
    is_available TINYINT(1) DEFAULT 1,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE bus_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bus_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bus_id) REFERENCES buses(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- BAZAARS
-- ============================================================

CREATE TABLE bazaars (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    address TEXT,
    city VARCHAR(100),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    opening_hours VARCHAR(200),
    specialties TEXT COMMENT 'What the bazaar is known for',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE bazaar_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bazaar_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (bazaar_id) REFERENCES bazaars(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- RESTAURANTS
-- ============================================================

CREATE TABLE restaurants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    cuisine_type VARCHAR(100),
    address TEXT,
    city VARCHAR(100),
    phone VARCHAR(20),
    latitude DECIMAL(10,8),
    longitude DECIMAL(11,8),
    opening_hours VARCHAR(200),
    price_range ENUM('budget', 'moderate', 'expensive', 'luxury') DEFAULT 'moderate',
    rating DECIMAL(3,2) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE restaurant_images (
    id INT AUTO_INCREMENT PRIMARY KEY,
    restaurant_id INT NOT NULL,
    image_path VARCHAR(500) NOT NULL,
    is_primary TINYINT(1) DEFAULT 0,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- SERVICE REQUESTS (Agency <-> Admin communication)
-- ============================================================

CREATE TABLE service_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT,
    user_id INT COMMENT 'App user who submitted',
    request_type ENUM('hotel', 'ticket', 'car', 'bus', 'bazaar', 'restaurant', 'package') NOT NULL,
    title VARCHAR(300) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    num_guests INT DEFAULT 1,
    special_requirements TEXT,
    status ENUM('pending', 'quoted', 'accepted', 'rejected', 'completed', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE service_request_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    item_type VARCHAR(50) NOT NULL,
    item_id INT NOT NULL,
    quantity INT DEFAULT 1,
    notes TEXT,
    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE request_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_id INT NOT NULL,
    user_id INT COMMENT 'Admin user who replied',
    message TEXT,
    price DECIMAL(12,2),
    currency VARCHAR(10) DEFAULT 'EGP',
    price_breakdown JSON,
    status ENUM('pending', 'accepted', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- FINANCE
-- ============================================================

CREATE TABLE finance_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('income', 'expense') NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    request_id INT NULL,
    type ENUM('income', 'expense') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(10) DEFAULT 'EGP',
    description TEXT,
    reference_number VARCHAR(100),
    payment_method ENUM('cash', 'bank_transfer', 'credit_card', 'online') DEFAULT 'cash',
    transaction_date DATE NOT NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES finance_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (request_id) REFERENCES service_requests(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- APP USERS (Mobile app users / Agency users)
-- ============================================================

CREATE TABLE app_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(500),
    is_active TINYINT(1) DEFAULT 1,
    fcm_token VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agency_id) REFERENCES agencies(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ============================================================
-- DEFAULT DATA
-- ============================================================

-- Default roles
INSERT INTO roles (name, description) VALUES
('super_admin', 'Full system access'),
('admin', 'Administrative access'),
('manager', 'Manager level access'),
('operator', 'Day to day operations'),
('viewer', 'Read-only access');

-- Default permissions
INSERT INTO permissions (name, module, description) VALUES
-- Users
('users.view', 'users', 'View users'),
('users.create', 'users', 'Create users'),
('users.edit', 'users', 'Edit users'),
('users.delete', 'users', 'Delete users'),
-- Roles
('roles.view', 'roles', 'View roles'),
('roles.manage', 'roles', 'Manage roles and permissions'),
-- Hotels
('hotels.view', 'hotels', 'View hotels'),
('hotels.create', 'hotels', 'Create hotels'),
('hotels.edit', 'hotels', 'Edit hotels'),
('hotels.delete', 'hotels', 'Delete hotels'),
-- Places/Tickets
('places.view', 'places', 'View places'),
('places.create', 'places', 'Create places'),
('places.edit', 'places', 'Edit places'),
('places.delete', 'places', 'Delete places'),
-- Cars
('cars.view', 'cars', 'View cars'),
('cars.create', 'cars', 'Create cars'),
('cars.edit', 'cars', 'Edit cars'),
('cars.delete', 'cars', 'Delete cars'),
-- Buses
('buses.view', 'buses', 'View buses'),
('buses.create', 'buses', 'Create buses'),
('buses.edit', 'buses', 'Edit buses'),
('buses.delete', 'buses', 'Delete buses'),
-- Bazaars
('bazaars.view', 'bazaars', 'View bazaars'),
('bazaars.create', 'bazaars', 'Create bazaars'),
('bazaars.edit', 'bazaars', 'Edit bazaars'),
('bazaars.delete', 'bazaars', 'Delete bazaars'),
-- Restaurants
('restaurants.view', 'restaurants', 'View restaurants'),
('restaurants.create', 'restaurants', 'Create restaurants'),
('restaurants.edit', 'restaurants', 'Edit restaurants'),
('restaurants.delete', 'restaurants', 'Delete restaurants'),
-- Requests
('requests.view', 'requests', 'View service requests'),
('requests.reply', 'requests', 'Reply to service requests'),
('requests.manage', 'requests', 'Manage service requests'),
-- Finance
('finance.view', 'finance', 'View financial data'),
('finance.create', 'finance', 'Create transactions'),
('finance.edit', 'finance', 'Edit transactions'),
('finance.delete', 'finance', 'Delete transactions'),
('finance.reports', 'finance', 'Access financial reports');

-- Assign all permissions to super_admin
INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Default super admin user (password: admin123)
-- IMPORTANT: After importing, run setup.php once to fix the password hash,
-- or manually run: UPDATE users SET password = '$2y$10$...' WHERE email = 'admin@easyop.com';
-- using the output of PHP: password_hash('admin123', PASSWORD_DEFAULT)
INSERT INTO users (role_id, name, email, password, phone) VALUES
(1, 'Super Admin', 'admin@easyop.com', '$2y$10$YourHashHere', '+201000000000');

-- Default finance categories
INSERT INTO finance_categories (name, type, description) VALUES
('Hotel Booking', 'income', 'Income from hotel bookings'),
('Ticket Sales', 'income', 'Income from ticket sales'),
('Car Rental', 'income', 'Income from car rentals'),
('Bus Rental', 'income', 'Income from bus rentals'),
('Tour Packages', 'income', 'Income from tour packages'),
('Restaurant Bookings', 'income', 'Income from restaurant bookings'),
('Staff Salaries', 'expense', 'Employee salary payments'),
('Office Rent', 'expense', 'Office rental costs'),
('Marketing', 'expense', 'Marketing and advertising'),
('Vehicle Maintenance', 'expense', 'Vehicle maintenance costs'),
('Utilities', 'expense', 'Utility bills'),
('Miscellaneous', 'expense', 'Other expenses');