CREATE TABLE IF NOT EXISTS app_settings (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(100) NOT NULL UNIQUE, setting_value TEXT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190) NULL UNIQUE, password_hash VARCHAR(255) NULL, role ENUM('admin','manager','staff') NOT NULL DEFAULT 'staff', active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS students (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(190) NULL, phone VARCHAR(60) NULL, nationality VARCHAR(100) NULL, date_of_birth DATE NULL, address TEXT NULL, emergency_name VARCHAR(160) NULL, emergency_phone VARCHAR(60) NULL, notes TEXT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(email), INDEX(last_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS clients (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(190) NOT NULL, client_type ENUM('hotel','dive_operation','business','other') DEFAULT 'business', contact_name VARCHAR(160) NULL, email VARCHAR(190) NULL, phone VARCHAR(60) NULL, billing_address TEXT NULL, notes TEXT NULL, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS courses (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category ENUM('PADI Freediving Courses','AIDA Freediving Courses','Specialty Courses','Scuba Courses') NOT NULL, agency VARCHAR(80) NULL, code VARCHAR(60) NULL, name VARCHAR(190) NOT NULL, price DECIMAL(10,2) NULL, description TEXT NULL, sort_order INT DEFAULT 0, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(category), INDEX(name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS activities (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, activity_type ENUM('Freediving Course','Private Freediving','Private Scuba','Hotel Instructor','Scuba Course','Equipment Rental / Return','Other') NOT NULL, title VARCHAR(190) NOT NULL, start_at DATETIME NOT NULL, end_at DATETIME NULL, student_id INT UNSIGNED NULL, client_id INT UNSIGNED NULL, course_id INT UNSIGNED NULL, location VARCHAR(190) NULL, status VARCHAR(60) DEFAULT 'Scheduled', billable TINYINT(1) DEFAULT 0, billing_basis VARCHAR(60) NULL, rate DECIMAL(10,2) NULL, quantity DECIMAL(10,2) DEFAULT 1, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE SET NULL, INDEX(start_at), INDEX(activity_type)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS activity_students (activity_id INT UNSIGNED NOT NULL, student_id INT UNSIGNED NOT NULL, enrollment_status VARCHAR(60) DEFAULT 'Confirmed', PRIMARY KEY(activity_id,student_id), FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE CASCADE, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS form_templates (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, form_type VARCHAR(100) NOT NULL, agency VARCHAR(80) NULL, version VARCHAR(50) NULL, definition_json LONGTEXT NULL, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS course_form_requirements (course_id INT UNSIGNED NOT NULL, form_template_id INT UNSIGNED NOT NULL, required TINYINT(1) DEFAULT 1, PRIMARY KEY(course_id,form_template_id), FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE, FOREIGN KEY(form_template_id) REFERENCES form_templates(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS student_forms (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NOT NULL, activity_id INT UNSIGNED NULL, form_template_id INT UNSIGNED NOT NULL, public_token CHAR(64) NOT NULL UNIQUE, status ENUM('Pending','Sent','Opened','Completed','Needs Review') DEFAULT 'Pending', sent_at DATETIME NULL, opened_at DATETIME NULL, completed_at DATETIME NULL, answers_json LONGTEXT NULL, signature_path VARCHAR(255) NULL, signed_pdf_path VARCHAR(255) NULL, signer_ip VARCHAR(45) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE, FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE SET NULL, FOREIGN KEY(form_template_id) REFERENCES form_templates(id) ON DELETE RESTRICT, INDEX(status), INDEX(public_token)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS inventory_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku VARCHAR(80) NULL UNIQUE, name VARCHAR(190) NOT NULL, item_type ENUM('rental','retail','both') DEFAULT 'rental', category VARCHAR(100) NULL, size VARCHAR(60) NULL, qty_on_hand DECIMAL(10,2) DEFAULT 0, qty_available DECIMAL(10,2) DEFAULT 0, cost DECIMAL(10,2) NULL, sale_price DECIMAL(10,2) NULL, rental_rate DECIMAL(10,2) NULL, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS rentals (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NULL, activity_id INT UNSIGNED NULL, rented_at DATETIME NOT NULL, due_at DATETIME NULL, returned_at DATETIME NULL, status VARCHAR(60) DEFAULT 'Open', total DECIMAL(10,2) DEFAULT 0, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL, FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS rental_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, rental_id INT UNSIGNED NOT NULL, inventory_item_id INT UNSIGNED NULL, description VARCHAR(190) NOT NULL, qty DECIMAL(10,2) DEFAULT 1, rate DECIMAL(10,2) DEFAULT 0, FOREIGN KEY(rental_id) REFERENCES rentals(id) ON DELETE CASCADE, FOREIGN KEY(inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS invoices (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_number VARCHAR(60) NOT NULL UNIQUE, student_id INT UNSIGNED NULL, client_id INT UNSIGNED NULL, activity_id INT UNSIGNED NULL, issue_date DATE NOT NULL, due_date DATE NULL, status ENUM('Draft','Open','Paid','Void','Overdue') DEFAULT 'Open', subtotal DECIMAL(10,2) DEFAULT 0, tax DECIMAL(10,2) DEFAULT 0, total DECIMAL(10,2) DEFAULT 0, balance DECIMAL(10,2) DEFAULT 0, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE SET NULL, FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE SET NULL, INDEX(status), INDEX(issue_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS invoice_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id INT UNSIGNED NOT NULL, description VARCHAR(255) NOT NULL, qty DECIMAL(10,2) DEFAULT 1, unit_price DECIMAL(10,2) DEFAULT 0, amount DECIMAL(10,2) DEFAULT 0, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS payments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id INT UNSIGNED NOT NULL, paid_at DATETIME NOT NULL, amount DECIMAL(10,2) NOT NULL, method VARCHAR(60) NULL, reference VARCHAR(120) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE, INDEX(paid_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sales (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NULL, sold_at DATETIME NOT NULL, total DECIMAL(10,2) DEFAULT 0, payment_method VARCHAR(60) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sale_items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sale_id INT UNSIGNED NOT NULL, inventory_item_id INT UNSIGNED NULL, description VARCHAR(190) NOT NULL, qty DECIMAL(10,2) DEFAULT 1, unit_price DECIMAL(10,2) DEFAULT 0, amount DECIMAL(10,2) DEFAULT 0, FOREIGN KEY(sale_id) REFERENCES sales(id) ON DELETE CASCADE, FOREIGN KEY(inventory_item_id) REFERENCES inventory_items(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS documents (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id INT UNSIGNED NULL, activity_id INT UNSIGNED NULL, document_type VARCHAR(100) NOT NULL, original_name VARCHAR(255) NOT NULL, stored_path VARCHAR(255) NOT NULL, uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE SET NULL, FOREIGN KEY(activity_id) REFERENCES activities(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS audit_log (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(100) NULL, entity_id INT UNSIGNED NULL, details TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(created_at), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;