-- ============================================
-- K.W.A TRAVELS & TOURS PRIVATE LIMITED
-- MySQL Database + Letter Generator
-- Created: 2026-04-14
-- ============================================

CREATE DATABASE IF NOT EXISTS kwa_travels_db;
USE kwa_travels_db;

-- ============================================
-- 1. CUSTOMERS TABLE
-- ============================================
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(100),
    cnic VARCHAR(15) UNIQUE,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 2. DESTINATIONS TABLE
-- ============================================
CREATE TABLE destinations (
    destination_id INT AUTO_INCREMENT PRIMARY KEY,
    destination_name VARCHAR(100) NOT NULL,
    country VARCHAR(100),
    description TEXT
);

-- ============================================
-- 3. PACKAGES TABLE
-- ============================================
CREATE TABLE packages (
    package_id INT AUTO_INCREMENT PRIMARY KEY,
    package_name VARCHAR(150) NOT NULL,
    destination_id INT,
    price DECIMAL(10,2) NOT NULL,
    duration VARCHAR(50),
    inclusions TEXT,
    status ENUM('Active','Inactive') DEFAULT 'Active',
    FOREIGN KEY (destination_id) REFERENCES destinations(destination_id)
);

-- ============================================
-- 4. BOOKINGS TABLE
-- ============================================
CREATE TABLE bookings (
    booking_id INT AUTO_INCREMENT PRIMARY KEY,
    booking_ref VARCHAR(20) UNIQUE NOT NULL,
    customer_id INT NOT NULL,
    destination_id INT,
    package_id INT,
    travel_date DATE NOT NULL,
    return_date DATE,
    seats INT DEFAULT 1,
    total_amount DECIMAL(10,2),
    booking_status ENUM('Pending','Confirmed','Cancelled','Completed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (destination_id) REFERENCES destinations(destination_id),
    FOREIGN KEY (package_id) REFERENCES packages(package_id)
);

-- ============================================
-- 5. PAYMENTS TABLE
-- ============================================
CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    booking_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) NOT NULL,
    remaining_amount DECIMAL(10,2) GENERATED ALWAYS AS (total_amount - amount_paid) STORED,
    payment_date DATE NOT NULL,
    payment_method ENUM('Cash','Bank Transfer','Cheque','Online') DEFAULT 'Cash',
    payment_status ENUM('Partial','Full','Refunded') DEFAULT 'Partial',
    FOREIGN KEY (booking_id) REFERENCES bookings(booking_id)
);

-- ============================================
-- 6. EMPLOYEES TABLE
-- ============================================
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    designation VARCHAR(50),
    phone VARCHAR(20),
    salary DECIMAL(10,2),
    status ENUM('Active','Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 7. LETTER TEMPLATES TABLE
-- ============================================
CREATE TABLE letter_templates (
    template_id INT AUTO_INCREMENT PRIMARY KEY,
    template_name VARCHAR(100) NOT NULL,
    letter_type ENUM(
        'Booking Confirmation',
        'Payment Receipt',
        'Visa Application',
        'Tour Itinerary',
        'Cancellation Letter',
        'NOC Letter',
        'Welcome Letter',
        'Custom Letter'
    ) NOT NULL,
    subject VARCHAR(200),
    body_template TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 8. GENERATED LETTERS TABLE
-- ============================================
CREATE TABLE generated_letters (
    letter_id INT AUTO_INCREMENT PRIMARY KEY,
    template_id INT,
    booking_id INT,
    customer_id INT,
    employee_id INT,
    letter_type VARCHAR(100),
    letter_subject VARCHAR(200),
    letter_body TEXT NOT NULL,
    generated_date DATE DEFAULT (CURRENT_DATE),
    generated_by INT,
    status ENUM('Draft','Final','Sent') DEFAULT 'Draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES letter_templates(template_id),
    FOREIGN KEY (booking_id) REFERENCES bookings(booking_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id),
    FOREIGN KEY (generated_by) REFERENCES employees(employee_id)
);

-- ============================================
-- SAMPLE DATA - DESTINATIONS
-- ============================================
INSERT INTO destinations (destination_name, country) VALUES
('Makkah', 'Saudi Arabia'),
('Madinah', 'Saudi Arabia'),
('Istanbul', 'Turkey'),
('Dubai', 'UAE'),
('Bangkok', 'Thailand'),
('Naran', 'Pakistan'),
('Swat', 'Pakistan'),
('London', 'United Kingdom');

-- ============================================
-- SAMPLE DATA - PACKAGES
-- ============================================
INSERT INTO packages (package_name, destination_id, price, duration, inclusions) VALUES
('Umrah Package Basic', 1, 120000.00, '15 Days', 'Flight, Hotel, Transport, Visa'),
('Umrah Package Premium', 1, 180000.00, '21 Days', 'Flight, 5-Star Hotel, Transport, Visa, Meals'),
('Turkey Tour', 3, 85000.00, '7 Days', 'Flight, Hotel, City Tour, Visa'),
('Dubai Shopping Tour', 4, 65000.00, '5 Days', 'Flight, Hotel, Transport, Visa'),
('Naran Kaghan Tour', 6, 25000.00, '5 Days', 'Transport, Hotel, Sightseeing'),
('Swat Valley Tour', 7, 20000.00, '4 Days', 'Transport, Hotel, Sightseeing');

-- ============================================
-- SAMPLE DATA - LETTER TEMPLATES
-- ============================================
INSERT INTO letter_templates (template_name, letter_type, subject, body_template) VALUES

('Booking Confirmation Letter', 'Booking Confirmation',
'Booking Confirmation - [BOOKING_REF]',
'K.W.A TRAVELS & TOURS PRIVATE LIMITED
Address: [COMPANY_ADDRESS]
Phone: [COMPANY_PHONE]
Date: [DATE]

Dear [CUSTOMER_NAME],

Assalam-o-Alaikum!

We are pleased to confirm your booking with K.W.A Travels & Tours Private Limited.

BOOKING DETAILS:
================
Booking Reference : [BOOKING_REF]
Destination       : [DESTINATION]
Package           : [PACKAGE_NAME]
Travel Date       : [TRAVEL_DATE]
Return Date       : [RETURN_DATE]
Number of Seats   : [SEATS]
Total Amount      : PKR [TOTAL_AMOUNT]

We look forward to serving you. For any queries, please contact us.

Warm Regards,
K.W.A TRAVELS & TOURS PRIVATE LIMITED'),

('Payment Receipt Letter', 'Payment Receipt',
'Payment Receipt - [BOOKING_REF]',
'K.W.A TRAVELS & TOURS PRIVATE LIMITED
Date: [DATE]

PAYMENT RECEIPT
===============
Receipt No        : [RECEIPT_NO]
Customer Name     : [CUSTOMER_NAME]
CNIC              : [CNIC]
Booking Ref       : [BOOKING_REF]
Destination       : [DESTINATION]

PAYMENT DETAILS:
Total Amount      : PKR [TOTAL_AMOUNT]
Amount Paid       : PKR [AMOUNT_PAID]
Remaining Amount  : PKR [REMAINING_AMOUNT]
Payment Date      : [PAYMENT_DATE]
Payment Method    : [PAYMENT_METHOD]

Thank you for your payment!

Authorized Signature: _______________
K.W.A TRAVELS & TOURS PRIVATE LIMITED'),

('Visa Application Letter', 'Visa Application',
'Visa Application Support Letter - [CUSTOMER_NAME]',
'K.W.A TRAVELS & TOURS PRIVATE LIMITED
Date: [DATE]

To,
The Visa Officer
Embassy of [COUNTRY]
Islamabad, Pakistan

Subject: Visa Application Support Letter

Dear Sir/Madam,

This is to certify that [CUSTOMER_NAME], holder of CNIC No. [CNIC] and Passport No. [PASSPORT_NO], 
is our valued client and has booked a tour package with our company.

TRAVEL DETAILS:
Destination       : [DESTINATION]
Travel Date       : [TRAVEL_DATE]
Return Date       : [RETURN_DATE]
Package           : [PACKAGE_NAME]

We guarantee that the applicant will return to Pakistan after completing the tour.
All tour arrangements have been made by K.W.A Travels & Tours Private Limited.

Yours Sincerely,

_______________________
Authorized Signatory
K.W.A TRAVELS & TOURS PRIVATE LIMITED'),

('Tour Itinerary Letter', 'Tour Itinerary',
'Tour Itinerary - [DESTINATION] - [CUSTOMER_NAME]',
'K.W.A TRAVELS & TOURS PRIVATE LIMITED

TOUR ITINERARY
==============
Customer Name     : [CUSTOMER_NAME]
Booking Ref       : [BOOKING_REF]
Destination       : [DESTINATION]
Travel Date       : [TRAVEL_DATE]
Return Date       : [RETURN_DATE]
Package           : [PACKAGE_NAME]
Duration          : [DURATION]

ITINERARY DETAILS:
[ITINERARY_DETAILS]

INCLUSIONS:
[INCLUSIONS]

Important Notes:
- Please carry your original passport and CNIC
- Report at airport 3 hours before departure
- For assistance call: [COMPANY_PHONE]

Safe Travels!
K.W.A TRAVELS & TOURS PRIVATE LIMITED'),

('NOC Letter', 'NOC Letter',
'No Objection Certificate - [CUSTOMER_NAME]',
'K.W.A TRAVELS & TOURS PRIVATE LIMITED
Date: [DATE]

NO OBJECTION CERTIFICATE
=========================

To Whom It May Concern,

This is to certify that [CUSTOMER_NAME], holder of CNIC No. [CNIC], 
is traveling to [DESTINATION] from [TRAVEL_DATE] to [RETURN_DATE] 
under our tour package.

We have NO OBJECTION to the issuance of visa for the above-mentioned traveler.

Regards,

_______________________
Authorized Signatory
K.W.A TRAVELS & TOURS PRIVATE LIMITED');

-- ============================================
-- USEFUL VIEW - BOOKING SUMMARY
-- ============================================
CREATE VIEW booking_summary AS
SELECT 
    b.booking_id,
    b.booking_ref,
    c.name AS customer_name,
    c.phone,
    d.destination_name AS destination,
    p.package_name,
    b.travel_date,
    b.return_date,
    b.seats,
    b.total_amount,
    COALESCE(SUM(pay.amount_paid), 0) AS paid,
    (b.total_amount - COALESCE(SUM(pay.amount_paid), 0)) AS remaining,
    b.booking_status
FROM bookings b
JOIN customers c ON b.customer_id = c.customer_id
LEFT JOIN destinations d ON b.destination_id = d.destination_id
LEFT JOIN packages p ON b.package_id = p.package_id
LEFT JOIN payments pay ON b.booking_id = pay.booking_id
GROUP BY b.booking_id;

-- ============================================
-- END OF SCRIPT
-- ============================================