Tests.sql

USE TravelAgencyDB;
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'test')
BEGIN
    EXEC('CREATE SCHEMA test');
END
GO

-- Test result logging procedure
CREATE OR ALTER PROCEDURE test.LogTestResult
    @TestName NVARCHAR(100),
    @Result BIT,
    @ErrorMessage NVARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @ResultText NVARCHAR(10) = CASE WHEN @Result = 1 THEN 'PASSED' ELSE 'FAILED' END;
    PRINT @TestName + ': ' + @ResultText;
    IF @ErrorMessage IS NOT NULL
        PRINT 'Error: ' + @ErrorMessage;
END;
GO


-- Main test procedure for data integrity and business rules


CREATE OR ALTER PROCEDURE test.TestBusinessRules
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- Run DataPopulation first to ensure test data
        EXEC dbo.PopulateInitialData;
        
        DECLARE @ErrorMsg NVARCHAR(MAX);
        DECLARE @TestResult BIT;

        -- Test 1: Polish Characters Support
        BEGIN TRY
            -- Add test client with Polish characters
            DECLARE @ClientID INT;
            EXEC dbo.CreateClient 
                @FirstName = N'Żaneta',
                @LastName = N'Źródłowska',
                @Email = N'zaneta.test@test.pl',
                @Phone = N'123456789',
                @ClientID = @ClientID OUTPUT;

            -- Verify data was stored correctly
            IF EXISTS (SELECT 1 FROM Clients WHERE FirstName = N'Żaneta' AND LastName = N'Źródłowska')
                SET @TestResult = 1;
            ELSE
                SET @TestResult = 0;
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Polish Characters Support', @TestResult, @ErrorMsg;

        -- Test 2: Tour Capacity Validation
        BEGIN TRY
            -- Get first available tour
            DECLARE @TourID INT, @ScheduleID INT;
            SELECT TOP 1 @TourID = TourID FROM Tours;
            
            -- Create schedule with max 5 participants
            UPDATE Tours SET MaxParticipants = 5 WHERE TourID = @TourID;
            
            INSERT INTO TourSchedules (TourID, GuideID, StartDate, EndDate, CurrentParticipants, Status)
            SELECT TOP 1 
                @TourID,
                GuideID,
                DATEADD(day, 1, GETDATE()),
                DATEADD(day, 8, GETDATE()),
                0,
                'Active'
            FROM Guides;
            
            SET @ScheduleID = SCOPE_IDENTITY();

            -- Try to create reservation exceeding capacity
            BEGIN TRY
                DECLARE @ReservationID INT;
                EXEC dbo.CreateReservation
                    @ClientID = @ClientID,
                    @ScheduleID = @ScheduleID,
                    @NumberOfParticipants = 6,
                    @ReservationID = @ReservationID OUTPUT;
                
                SET @TestResult = 0;
                SET @ErrorMsg = 'Capacity validation failed - allowed over-capacity reservation';
            END TRY
            BEGIN CATCH
                IF ERROR_MESSAGE() LIKE '%capacity%'
                    SET @TestResult = 1;
                ELSE
                BEGIN
                    SET @TestResult = 0;
                    SET @ErrorMsg = ERROR_MESSAGE();
                END
            END CATCH
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Tour Capacity Validation', @TestResult, @ErrorMsg;

        -- Test 3: Payment Validation
        BEGIN TRY
            -- Create valid reservation
            DECLARE @ValidReservationID INT;
            EXEC dbo.CreateReservation
                @ClientID = @ClientID,
                @ScheduleID = @ScheduleID,
                @NumberOfParticipants = 2,
                @ReservationID = @ValidReservationID OUTPUT;

            -- Try to make payment exceeding reservation total
            DECLARE @PaymentID INT;
            BEGIN TRY
                EXEC dbo.CreatePayment
                    @ReservationID = @ValidReservationID,
                    @Amount = 99999.99,
                    @PaymentMethod = N'Credit Card',
                    @PaymentID = @PaymentID OUTPUT;
                
                SET @TestResult = 0;
                SET @ErrorMsg = 'Payment validation failed - allowed payment exceeding reservation total';
            END TRY
            BEGIN CATCH
                IF ERROR_MESSAGE() LIKE '%exceeds%'
                    SET @TestResult = 1;
                ELSE
                BEGIN
                    SET @TestResult = 0;
                    SET @ErrorMsg = ERROR_MESSAGE();
                END
            END CATCH
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Payment Validation', @TestResult, @ErrorMsg;


		-- Test 4: Reservation Status Flow
		BEGIN TRY
			DECLARE @StatusScheduleID INT;
			DECLARE @ReservationStatus NVARCHAR(20);

			-- Get a tour with sufficient capacity
			DECLARE @StatusTourID INT;
			SELECT TOP 1 @StatusTourID = t.TourID
			FROM Tours t
			LEFT JOIN TourSchedules ts ON t.TourID = ts.TourID
			WHERE t.MaxParticipants >= 5;

			-- Create new schedule for this tour
			INSERT INTO TourSchedules (
				TourID, 
				GuideID,
				StartDate,
				EndDate,
				CurrentParticipants,
				Status
			)
			SELECT 
				@StatusTourID,
				(SELECT TOP 1 GuideID FROM Guides),
				DATEADD(day, 1, GETDATE()),
				DATEADD(day, 8, GETDATE()),
				0,
				'Active';
			
			SET @StatusScheduleID = SCOPE_IDENTITY();
			
			-- Create reservation (should be Pending)
			DECLARE @StatusTestReservationID INT;
			EXEC dbo.CreateReservation
			    @ClientID = @ClientID,
				@ScheduleID = @StatusScheduleID,
				@NumberOfParticipants = 2,
				@ReservationID = @StatusTestReservationID OUTPUT;

			SELECT @ReservationStatus = Status 
			FROM Reservations 
			WHERE ReservationID = @StatusTestReservationID;

            IF @ReservationStatus = 'Pending'
            BEGIN
                -- Make full payment (should change to Confirmed)
                DECLARE @TotalPrice DECIMAL(10,2);
                SELECT @TotalPrice = TotalPrice 
                FROM Reservations 
                WHERE ReservationID = @StatusTestReservationID;

                EXEC dbo.CreatePayment
                    @ReservationID = @StatusTestReservationID,
                    @Amount = @TotalPrice,
                    @PaymentMethod = N'Credit Card',
                    @PaymentID = @PaymentID OUTPUT;

                SELECT @ReservationStatus = Status 
                FROM Reservations 
                WHERE ReservationID = @StatusTestReservationID;

                IF @ReservationStatus = 'Confirmed'
                    SET @TestResult = 1;
                ELSE
                BEGIN
                    SET @TestResult = 0;
                    SET @ErrorMsg = 'Reservation status not updated after payment';
                END
            END
            ELSE
            BEGIN
                SET @TestResult = 0;
                SET @ErrorMsg = 'Initial reservation status not set to Pending';
            END
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Reservation Status Flow', @TestResult, @ErrorMsg;

        -- Test 5: Tour Schedule Date Validation
        BEGIN TRY
            -- Try to create schedule with end date before start date
            BEGIN TRY
                DECLARE @InvalidScheduleID INT;
                EXEC dbo.CreateTourSchedule
                    @TourID = @TourID,
                    @GuideID = 1,
                    @StartDate = '2024-12-31',
                    @EndDate = '2024-12-30',
                    @ScheduleID = @InvalidScheduleID OUTPUT;
                
                SET @TestResult = 0;
                SET @ErrorMsg = 'Date validation failed - allowed end date before start date';
            END TRY
            BEGIN CATCH
                IF ERROR_MESSAGE() LIKE '%date%'
                    SET @TestResult = 1;
                ELSE
                BEGIN
                    SET @TestResult = 0;
                    SET @ErrorMsg = ERROR_MESSAGE();
                END
            END CATCH
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Tour Schedule Date Validation', @TestResult, @ErrorMsg;

        -- Test 6: Unique Email Constraint
        BEGIN TRY
            -- Try to create client with duplicate email
            DECLARE @DuplicateClientID INT;
            BEGIN TRY
                EXEC dbo.CreateClient 
                    @FirstName = N'Test',
                    @LastName = N'User',
                    @Email = N'zaneta.test@test.pl',
                    @Phone = N'987654321',
                    @ClientID = @DuplicateClientID OUTPUT;
                
                SET @TestResult = 0;
                SET @ErrorMsg = 'Unique email constraint failed';
            END TRY
            BEGIN CATCH
                IF ERROR_MESSAGE() LIKE '%email%exists%'
                    SET @TestResult = 1;
                ELSE
                BEGIN
                    SET @TestResult = 0;
                    SET @ErrorMsg = ERROR_MESSAGE();
                END
            END CATCH
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Unique Email Constraint', @TestResult, @ErrorMsg;

        -- Test 7: Insurance Plan Addition
        BEGIN TRY
            -- Create insurance plan
            DECLARE @InsurancePlanID INT;
            EXEC dbo.CreateInsurancePlan
                @PlanName = N'Test Insurance',
                @Coverage = N'Basic Coverage',
                @Cost = 100.00,
                @InsurancePlanID = @InsurancePlanID OUTPUT;

            -- Add insurance to reservation
            DECLARE @ReservationInsuranceID INT;
            EXEC dbo.AddInsuranceToReservation
                @ReservationID = @ValidReservationID,
                @InsurancePlanID = @InsurancePlanID,
                @ReservationInsuranceID = @ReservationInsuranceID OUTPUT;

            IF @ReservationInsuranceID IS NOT NULL
                SET @TestResult = 1;
            ELSE
            BEGIN
                SET @TestResult = 0;
                SET @ErrorMsg = 'Failed to add insurance to reservation';
            END
        END TRY
        BEGIN CATCH
            SET @TestResult = 0;
            SET @ErrorMsg = ERROR_MESSAGE();
        END CATCH
        EXEC test.LogTestResult 'Insurance Plan Addition', @TestResult, @ErrorMsg;

        -- Cleanup test data
        DELETE FROM ReservationInsurance WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
        DELETE FROM Payments WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
        DELETE FROM Reservations WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
        DELETE FROM TourSchedules WHERE ScheduleID = @ScheduleID;
        DELETE FROM Clients WHERE ClientID = @ClientID;
        DELETE FROM InsurancePlans WHERE InsurancePlanID = @InsurancePlanID;
		DELETE FROM ReservationInsurance WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
		DELETE FROM Payments WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
		DELETE FROM Reservations WHERE ReservationID IN (@ValidReservationID, @StatusTestReservationID);
		DELETE FROM TourSchedules WHERE ScheduleID IN (@ScheduleID, @StatusScheduleID);

    END TRY
    BEGIN CATCH
        PRINT 'Error in test execution: ' + ERROR_MESSAGE();
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.TestUserPermissions
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #PermissionTests (
        TestName NVARCHAR(100),
        TestResult NVARCHAR(50)
    );

    -- Test Manager permissions
    BEGIN TRY
        EXECUTE AS USER = 'TravelManagerUser';
        
        -- Test view access
        SELECT TOP 1 1 FROM vwRevenueAnalysis;
        INSERT INTO #PermissionTests VALUES ('Manager Revenue View Access', 'Success');
        
        -- Test procedure access
        EXEC dbo.GetTourStatistics;
        INSERT INTO #PermissionTests VALUES ('Manager Procedure Access', 'Success');

        REVERT;
    END TRY
    BEGIN CATCH
        INSERT INTO #PermissionTests VALUES ('Manager Tests', 'Error: ' + ERROR_MESSAGE());
        IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'TravelManager')
            REVERT;
    END CATCH

    -- Test Agent permissions
    BEGIN TRY
        EXECUTE AS USER = 'TravelAgentUser';
        
        -- Test view access
        SELECT TOP 1 1 FROM vwUpcomingTours;
        INSERT INTO #PermissionTests VALUES ('Agent Tour View Access', 'Success');
        
        -- Test procedure access
        EXEC dbo.SearchAvailableTours;
        INSERT INTO #PermissionTests VALUES ('Agent Procedure Access', 'Success');

        REVERT;
    END TRY
    BEGIN CATCH
        INSERT INTO #PermissionTests VALUES ('Agent Tests', 'Error: ' + ERROR_MESSAGE());
        IF EXISTS (SELECT 1 FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'TravelAgent')
            REVERT;
    END CATCH

    SELECT * FROM #PermissionTests;
    DROP TABLE #PermissionTests;
END;
GO

PRINT 'Starting database tests...';
PRINT '------------------------';
EXEC test.TestBusinessRules;
GO


PRINT 'Starting Trigger tests...';
PRINT '------------------------';
INSERT INTO Continents (ContinentName) VALUES (N'Europa Test');
DECLARE @InitialModifiedDate DATETIME2 = (SELECT ModifiedDate FROM Continents WHERE ContinentName = N'Europa Test');
WAITFOR DELAY '00:00:01';
UPDATE Continents SET ContinentName = N'Europa Updated' WHERE ContinentName = N'Europa Test';
DECLARE @NewModifiedDate DATETIME2 = (SELECT ModifiedDate FROM Continents WHERE ContinentName = N'Europa Updated');
IF @NewModifiedDate > @InitialModifiedDate
    PRINT 'ModifiedDate trigger works correctly';
ELSE
    PRINT 'ModifiedDate trigger failed';
GO


PRINT 'Starting View tests...';
PRINT '------------------------';
EXEC dbo.PopulateInitialData;
EXEC dbo.DemonstrateCompleteBooking;
GO

SELECT * FROM dbo.vwOrderedToursAnalytics
SELECT * FROM dbo.vwTourDetails;
SELECT * FROM dbo.vwUpcomingTours;
SELECT * FROM dbo.vwReservationSummary;
SELECT * FROM dbo.vwRevenueAnalysis;
SELECT * FROM dbo.vwPaymentAnalytics WHERE PaymentStatus = 'Completed';
SELECT * FROM dbo.vwGuidePerformance ORDER BY TotalParticipants DESC;
SELECT * FROM dbo.vwDestinationPopularity ORDER BY TotalVisitors DESC;


PRINT 'Starting Use Cases tests...';
PRINT '------------------------';
EXEC dbo.PopulateInitialData;
PRINT 'Testing complete booking process...';
EXEC dbo.DemonstrateCompleteBooking;
GO
PRINT 'Testing tour search...';
EXEC dbo.DemonstrateSearchTours;
GO
PRINT 'Testing simple use cases...(two empty results should pop up, it is expected)';
EXEC dbo.DemonstrateUseCases;
GO
PRINT 'Testing guide workload report...';
EXEC dbo.GenerateGuideWorkloadReport;
GO
PRINT 'Testing destination analysis...';
EXEC dbo.AnalyzePopularDestinations;
GO


PRINT 'Starting Procedure tests...';
PRINT '------------------------';
EXEC dbo.PopulateInitialData;
BEGIN TRY
    -- Test Amenity Type creation
    DECLARE @AmenityTypeID INT;
    EXEC dbo.CreateAmenityType 
        @TypeName = N'WiFiTest', 
        @Description = N'Wireless Internet AccessTest',
        @AmenityTypeID = @AmenityTypeID OUTPUT;

    -- Test Client Category creation
    DECLARE @CategoryID INT;
    EXEC dbo.CreateClientCategory
        @CategoryName = N'PremiumTest',
        @DiscountPercentage = 15.00,
        @Description = N'Premium level clientsTest',
        @CategoryID = @CategoryID OUTPUT;

    -- Add test tour if not exists
    IF NOT EXISTS (SELECT 1 FROM Tours)
    BEGIN
        -- Add necessary data for creating a tour
        DECLARE @TourID INT;
        EXEC dbo.CreateTour
            @TourName = N'Test Tour',
            @TourTypeID = 1, -- Assuming this exists
            @StartCityID = 1, -- Assuming this exists
            @EndCityID = 1,   -- Assuming this exists
            @Duration = 7,
            @BasePrice = 1000.00,
            @MaxParticipants = 20,
            @Description = N'Test Tour Description',
            @TourID = @TourID OUTPUT;

        -- Test Tour Amenity creation
        DECLARE @TourAmenityID INT;
        EXEC dbo.AddTourAmenity
            @TourID = @TourID,
            @AmenityTypeID = @AmenityTypeID,
            @Description = N'Free WiFi during tour',
            @AdditionalCost = 0,
            @TourAmenityID = @TourAmenityID OUTPUT;
    END

    -- Test Continent procedures
    DECLARE @ContinentID INT;
    EXEC dbo.CreateContinent @ContinentName = N'Test Continent', @ContinentID = @ContinentID OUTPUT;
    EXEC dbo.UpdateContinent @ContinentID = @ContinentID, @ContinentName = N'Updated Test Continent';

    -- Test Country procedures
    DECLARE @CountryID INT;
    EXEC dbo.CreateCountry 
        @CountryName = N'Test Country', 
        @ContinentID = @ContinentID, 
        @CountryCode = 'TC', 
        @CountryID = @CountryID OUTPUT;

    -- Test Insurance Plan procedures
    DECLARE @InsurancePlanID INT;
    EXEC dbo.CreateInsurancePlan 
        @PlanName = N'Test Plan', 
        @Coverage = N'Test Coverage', 
        @Cost = 100.00, 
        @InsurancePlanID = @InsurancePlanID OUTPUT;

    PRINT 'All test executions completed successfully.';
END TRY
BEGIN CATCH
    PRINT 'Error during testing: ' + ERROR_MESSAGE();
END CATCH;
GO


PRINT 'Starting Permission tests...';
PRINT '------------------------';
EXEC dbo.TestUserPermissions;
GO