TestUseCases.sql

USE TravelAgencyDB;
GO


-- Example Simpler Use Cases with test data and queries


CREATE OR ALTER PROCEDURE dbo.DemonstrateUseCases
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- 1. Example: "Client makes a reservation for a tour"
        DECLARE @StartDate DATE = '2024-06-01';
        DECLARE @EndDate DATE = '2024-06-30';
        DECLARE @MaxPrice DECIMAL(10,2) = 5000.00;
        DECLARE @ClientID INT;
        DECLARE @ScheduleID INT;
        DECLARE @ReservationID INT;

        -- Search for available tours
        EXEC dbo.SearchAvailableTours
            @StartDate = @StartDate,
            @EndDate = @EndDate,
            @MaxPrice = @MaxPrice;

        -- Create new client
        EXEC dbo.CreateClient
            @FirstName = N'AnnaTest',
            @LastName = N'KowalskaTest',
            @Email = N'anna.kowalskaTest@email.com',
            @Phone = N'123456789',
            @ClientID = @ClientID OUTPUT;

        -- Assuming we have at least one tour schedule
        SELECT TOP 1 @ScheduleID = ScheduleID 
        FROM TourSchedules 
        WHERE Status = 'Active';

        IF @ScheduleID IS NOT NULL
        BEGIN
            -- Create reservation
            EXEC dbo.CreateReservation
                @ClientID = @ClientID,
                @ScheduleID = @ScheduleID,
                @NumberOfParticipants = 2,
                @ReservationID = @ReservationID OUTPUT;

            -- 2. Example: "Guide checks their schedule for next month"
            DECLARE @GuideID INT;
            
            -- Get a guide ID
            SELECT TOP 1 @GuideID = GuideID FROM Guides;
            
            IF @GuideID IS NOT NULL
            BEGIN
                EXEC dbo.GetGuideSchedule
                    @GuideID = @GuideID,
                    @StartDate = @StartDate,
                    @EndDate = @EndDate;
            END

            -- 3. Example: "Manager analyzes tour statistics"
            -- Most popular tours(needs created tour schedules and confiremed reservations to show output)
            SELECT TOP 5
                t.TourName,
                COUNT(r.ReservationID) AS TotalReservations,
                SUM(r.NumberOfParticipants) AS TotalParticipants,
                SUM(r.TotalPrice) AS TotalRevenue
            FROM Tours t
            JOIN TourSchedules ts ON t.TourID = ts.TourID
            JOIN Reservations r ON ts.ScheduleID = r.ScheduleID
            WHERE r.Status = 'Confirmed'
            GROUP BY t.TourName
            ORDER BY TotalRevenue DESC;

            -- 4. Example: "Adding insurance to a reservation"
            DECLARE @InsurancePlanID INT;

            -- Create insurance plan
            EXEC dbo.CreateInsurancePlan
                @PlanName = N'Standard Travel InsuranceTest',
                @Coverage = N'Basic travel coverage including medical and cancellationTest',
                @Cost = 200.00,
                @InsurancePlanID = @InsurancePlanID OUTPUT;

            -- Add insurance to reservation
            IF @ReservationID IS NOT NULL AND @InsurancePlanID IS NOT NULL
            BEGIN
                DECLARE @ReservationInsuranceID INT;
                EXEC dbo.AddInsuranceToReservation
                    @ReservationID = @ReservationID,
                    @InsurancePlanID = @InsurancePlanID,
                    @ReservationInsuranceID = @ReservationInsuranceID OUTPUT;
            END

            -- 5. Example: "Processing payment for reservation"
            IF @ReservationID IS NOT NULL
            BEGIN
                DECLARE @PaymentID INT;
                EXEC dbo.CreatePayment
                    @ReservationID = @ReservationID,
                    @Amount = 2000.00,
                    @PaymentMethod = N'Credit CardTest',
                    @PaymentID = @PaymentID OUTPUT;
            END
        END
    END TRY
    BEGIN CATCH
        PRINT 'Error executing example use cases: ' + ERROR_MESSAGE();
    END CATCH
END;
GO


-- More Complex Example Use Cases


CREATE OR ALTER PROCEDURE dbo.DemonstrateSearchTours
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Example: Find all tours under 5000 PLN with available spots in next 3 months(needs data to create output)
    DECLARE @MaxPrice DECIMAL(10,2) = 5000.00;
    DECLARE @StartDate DATE = GETDATE();
    DECLARE @EndDate DATE = DATEADD(MONTH, 3, GETDATE());
    
    EXEC dbo.SearchAvailableTours
        @StartDate = @StartDate,
        @EndDate = @EndDate,
        @MaxPrice = @MaxPrice;
END;
GO

CREATE OR ALTER PROCEDURE dbo.DemonstrateCompleteBooking
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ClientID INT, @ScheduleID INT, @ReservationID INT;
    DECLARE @InsurancePlanID INT, @ReservationInsuranceID INT;
    
    BEGIN TRY
        -- Create a test tour schedule if none exists
        IF NOT EXISTS (
            SELECT 1 
            FROM TourSchedules ts
            JOIN Tours t ON ts.TourID = t.TourID
            WHERE ts.Status = 'Active' 
            AND ts.StartDate > GETDATE()
            AND ts.CurrentParticipants + 2 <= t.MaxParticipants
        )
        BEGIN
            -- Get first available tour
            DECLARE @TourID INT;
            SELECT TOP 1 @TourID = TourID FROM Tours;
            
            -- Get first available guide
            DECLARE @GuideID INT;
            SELECT TOP 1 @GuideID = GuideID FROM Guides;
            
            -- Create new schedule
            IF @TourID IS NOT NULL AND @GuideID IS NOT NULL
            BEGIN
                INSERT INTO TourSchedules (
                    TourID, 
                    GuideID, 
                    StartDate, 
                    EndDate, 
                    CurrentParticipants, 
                    Status
                )
                VALUES (
                    @TourID,
                    @GuideID,
                    DATEADD(DAY, 1, GETDATE()),
                    DATEADD(DAY, 8, GETDATE()),
                    0,
                    'Active'
                );
            END
            ELSE
            BEGIN
                RAISERROR('No tours or guides available to create test schedule', 16, 1);
                RETURN;
            END
        END

        -- Create new client
        EXEC dbo.CreateClient
            @FirstName = N'TomaszTest',
            @LastName = N'NowickiTest',
            @Email = N'tomasz.nowickiTest@email.com',
            @Phone = N'555666777',
            @ClientID = @ClientID OUTPUT;

        -- Find available tour schedule
        SELECT TOP 1 @ScheduleID = ts.ScheduleID 
        FROM TourSchedules ts
        JOIN Tours t ON ts.TourID = t.TourID
        WHERE ts.Status = 'Active' 
        AND ts.StartDate > GETDATE()
        AND ts.CurrentParticipants + 2 <= t.MaxParticipants;

        -- Create reservation
        EXEC dbo.CreateReservation
            @ClientID = @ClientID,
            @ScheduleID = @ScheduleID,
            @NumberOfParticipants = 2,
            @ReservationID = @ReservationID OUTPUT;

        -- Add insurance
        SELECT TOP 1 @InsurancePlanID = InsurancePlanID 
        FROM InsurancePlans 
        WHERE PlanName = 'Standard';

        IF @InsurancePlanID IS NOT NULL
        BEGIN
            EXEC dbo.AddInsuranceToReservation
                @ReservationID = @ReservationID,
                @InsurancePlanID = @InsurancePlanID,
                @ReservationInsuranceID = @ReservationInsuranceID OUTPUT;
        END

        -- Process payment
        DECLARE @PaymentID INT;
        EXEC dbo.CreatePayment
            @ReservationID = @ReservationID,
            @Amount = 2000.00,
            @PaymentMethod = N'Credit Card',
            @PaymentID = @PaymentID OUTPUT;

        -- Show booking details
        SELECT 
            c.FirstName + ' ' + c.LastName AS ClientName,
            t.TourName,
            ts.StartDate,
            ts.EndDate,
            r.NumberOfParticipants,
            r.TotalPrice,
            ip.PlanName AS InsurancePlan,
            p.Amount AS PaidAmount,
            p.PaymentMethod
        FROM Reservations r
        JOIN Clients c ON r.ClientID = c.ClientID
        JOIN TourSchedules ts ON r.ScheduleID = ts.ScheduleID
        JOIN Tours t ON ts.TourID = t.TourID
        LEFT JOIN ReservationInsurance ri ON r.ReservationID = ri.ReservationID
        LEFT JOIN InsurancePlans ip ON ri.InsurancePlanID = ip.InsurancePlanID
        LEFT JOIN Payments p ON r.ReservationID = p.ReservationID
        WHERE r.ReservationID = @ReservationID;
    END TRY
    BEGIN CATCH
        PRINT 'Error in booking process: ' + ERROR_MESSAGE();
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.GenerateGuideWorkloadReport
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    SET @StartDate = ISNULL(@StartDate, GETDATE());
    SET @EndDate = ISNULL(@EndDate, DATEADD(MONTH, 6, GETDATE()));

    SELECT 
        g.FirstName + ' ' + g.LastName AS GuideName,
        COUNT(ts.ScheduleID) AS TotalTours,
        SUM(ts.CurrentParticipants) AS TotalParticipants,
        COUNT(DISTINCT t.TourTypeID) AS DifferentTourTypes,
        MIN(ts.StartDate) AS FirstTour,
        MAX(ts.EndDate) AS LastTour
    FROM Guides g
    LEFT JOIN TourSchedules ts ON g.GuideID = ts.GuideID
    LEFT JOIN Tours t ON ts.TourID = t.TourID
    WHERE ts.StartDate BETWEEN @StartDate AND @EndDate
    GROUP BY g.GuideID, g.FirstName, g.LastName
    ORDER BY TotalTours DESC;
END;
GO

CREATE OR ALTER PROCEDURE dbo.AnalyzePopularDestinations
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        c.CityName AS Destination,
        co.CountryName AS Country,
        COUNT(ts.ScheduleID) AS TotalTours,
        SUM(ts.CurrentParticipants) AS TotalParticipants,
        AVG(t.BasePrice) AS AveragePrice,
        SUM(r.TotalPrice) AS TotalRevenue
    FROM Cities c
    JOIN Countries co ON c.CountryID = co.CountryID
    JOIN Tours t ON c.CityID = t.EndCityID
    JOIN TourSchedules ts ON t.TourID = ts.TourID
    LEFT JOIN Reservations r ON ts.ScheduleID = r.ScheduleID
    GROUP BY c.CityName, co.CountryName
    ORDER BY TotalParticipants DESC;
END;
GO