StoredProceduers.sql

USE TravelAgencyDB;
GO

-- Error handling procedure


CREATE OR ALTER PROCEDURE dbo.LogError
    @ProcedureName NVARCHAR(100),
    @ErrorMessage NVARCHAR(MAX)
AS
BEGIN
    PRINT 'Error in procedure ' + @ProcedureName + ': ' + @ErrorMessage;
END;
GO

-- Client Management Procedures


CREATE OR ALTER PROCEDURE dbo.CreateClient
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @Phone NVARCHAR(20),
    @CategoryID INT = NULL,
    @DateOfBirth DATE = NULL,
    @Address NVARCHAR(200) = NULL,
    @ClientID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM Clients WHERE Email = @Email)
        BEGIN
            RAISERROR('Client with this email already exists.', 16, 1);
            RETURN;
        END

        INSERT INTO Clients (
            FirstName, LastName, Email, Phone, 
            CategoryID, DateOfBirth, Address
        )
        VALUES (
            @FirstName, @LastName, @Email, @Phone,
            @CategoryID, @DateOfBirth, @Address
        );

        SET @ClientID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateClient', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateClient
    @ClientID INT,
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @Email NVARCHAR(100),
    @Phone NVARCHAR(20),
    @CategoryID INT = NULL,
    @DateOfBirth DATE = NULL,
    @Address NVARCHAR(200) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM Clients WHERE ClientID = @ClientID)
        BEGIN
            RAISERROR('Client does not exist.', 16, 1);
            RETURN;
        END

        IF EXISTS (SELECT 1 FROM Clients WHERE Email = @Email AND ClientID != @ClientID)
        BEGIN
            RAISERROR('Email address is already in use by another client.', 16, 1);
            RETURN;
        END

        UPDATE Clients
        SET FirstName = @FirstName,
            LastName = @LastName,
            Email = @Email,
            Phone = @Phone,
            CategoryID = @CategoryID,
            DateOfBirth = @DateOfBirth,
            Address = @Address,
            ModifiedDate = GETDATE()
        WHERE ClientID = @ClientID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateClient', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Reservation Management Procedures


CREATE OR ALTER PROCEDURE dbo.CreateReservation
    @ClientID INT,
    @ScheduleID INT,
    @NumberOfParticipants INT,
    @ReservationID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Check if tour schedule exists and has capacity
        DECLARE @MaxParticipants INT;
        DECLARE @CurrentParticipants INT;
        DECLARE @TourBasePrice DECIMAL(10,2);
        
        SELECT @MaxParticipants = t.MaxParticipants,
               @CurrentParticipants = ts.CurrentParticipants,
               @TourBasePrice = t.BasePrice
        FROM TourSchedules ts
        JOIN Tours t ON ts.TourID = t.TourID
        WHERE ts.ScheduleID = @ScheduleID;

        IF @CurrentParticipants + @NumberOfParticipants > @MaxParticipants
        BEGIN
            RAISERROR('Not enough capacity for this reservation.', 16, 1);
            RETURN;
        END

        -- Calculate total price
        DECLARE @TotalPrice DECIMAL(10,2) = @TourBasePrice * @NumberOfParticipants;

        -- Create reservation
        INSERT INTO Reservations (
            ClientID, ScheduleID, NumberOfParticipants, 
            TotalPrice, Status
        )
        VALUES (
            @ClientID, @ScheduleID, @NumberOfParticipants,
            @TotalPrice, 'Pending'
        );

        SET @ReservationID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateReservation', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Tour Schedules Management Procedures


CREATE OR ALTER PROCEDURE dbo.CreateTourSchedule
    @TourID INT,
    @GuideID INT,
    @StartDate DATE,
    @EndDate DATE,
    @ScheduleID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Validate dates
        IF @EndDate < @StartDate
        BEGIN
            RAISERROR('End date cannot be earlier than start date.', 16, 1);
            RETURN;
        END

        -- Check if guide is available for these dates
        IF EXISTS (
            SELECT 1 
            FROM TourSchedules
            WHERE GuideID = @GuideID
            AND (
                (@StartDate BETWEEN StartDate AND EndDate)
                OR (@EndDate BETWEEN StartDate AND EndDate)
                OR (StartDate BETWEEN @StartDate AND @EndDate)
            )
        )
        BEGIN
            RAISERROR('Guide is not available for these dates.', 16, 1);
            RETURN;
        END

        INSERT INTO TourSchedules (
            TourID, GuideID, StartDate, EndDate, 
            CurrentParticipants, Status
        )
        VALUES (
            @TourID, @GuideID, @StartDate, @EndDate,
            0, 'Active'
        );

        SET @ScheduleID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateTourSchedule', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Payment Management Procedures


CREATE OR ALTER PROCEDURE dbo.CreatePayment
    @ReservationID INT,
    @Amount DECIMAL(10,2),
    @PaymentMethod NVARCHAR(50),
    @PaymentID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Validate reservation exists and amount matches
        DECLARE @ExpectedAmount DECIMAL(10,2);
        DECLARE @CurrentPaid DECIMAL(10,2);
        
        SELECT @ExpectedAmount = TotalPrice
        FROM Reservations 
        WHERE ReservationID = @ReservationID;

        SELECT @CurrentPaid = ISNULL(SUM(Amount), 0)
        FROM Payments
        WHERE ReservationID = @ReservationID 
        AND PaymentStatus = 'Completed';

        IF @CurrentPaid + @Amount > @ExpectedAmount
        BEGIN
            RAISERROR('Payment amount exceeds remaining balance.', 16, 1);
            RETURN;
        END

        INSERT INTO Payments (
            ReservationID, Amount, PaymentMethod,
            PaymentStatus, PaymentDate
        )
        VALUES (
            @ReservationID, @Amount, @PaymentMethod,
            'Completed', GETDATE()
        );

        SET @PaymentID = SCOPE_IDENTITY();

        -- Update reservation status if fully paid
        IF @CurrentPaid + @Amount >= @ExpectedAmount
        BEGIN
            UPDATE Reservations
            SET Status = 'Confirmed'
            WHERE ReservationID = @ReservationID;
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreatePayment', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Tour Management Procedures


CREATE OR ALTER PROCEDURE dbo.CreateTour
    @TourName NVARCHAR(100),
    @TourTypeID INT,
    @StartCityID INT,
    @EndCityID INT,
    @Duration INT,
    @BasePrice DECIMAL(10,2),
    @MaxParticipants INT,
    @Description NVARCHAR(MAX) = NULL,
    @TourID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Validate inputs
        IF @Duration <= 0 OR @BasePrice <= 0 OR @MaxParticipants <= 0
        BEGIN
            RAISERROR('Invalid duration, price, or participant count.', 16, 1);
            RETURN;
        END

        INSERT INTO Tours (
            TourName, TourTypeID, StartCityID, EndCityID,
            Duration, BasePrice, MaxParticipants, Description
        )
        VALUES (
            @TourName, @TourTypeID, @StartCityID, @EndCityID,
            @Duration, @BasePrice, @MaxParticipants, @Description
        );

        SET @TourID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateTour', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Status Update Procedures


CREATE OR ALTER PROCEDURE dbo.UpdateReservationStatus
    @ReservationID INT,
    @NewStatus NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF @NewStatus NOT IN ('Pending', 'Confirmed', 'Cancelled', 'Completed')
        BEGIN
            RAISERROR('Invalid reservation status.', 16, 1);
            RETURN;
        END

        UPDATE Reservations
        SET Status = @NewStatus,
            ModifiedDate = GETDATE()
        WHERE ReservationID = @ReservationID;

        -- If cancelled, update tour schedule capacity
        IF @NewStatus = 'Cancelled'
        BEGIN
            UPDATE ts
            SET CurrentParticipants = CurrentParticipants - r.NumberOfParticipants
            FROM TourSchedules ts
            JOIN Reservations r ON ts.ScheduleID = r.ScheduleID
            WHERE r.ReservationID = @ReservationID;
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateReservationStatus', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateTourScheduleStatus
    @ScheduleID INT,
    @NewStatus NVARCHAR(20)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF @NewStatus NOT IN ('Active', 'Cancelled', 'Completed')
        BEGIN
            RAISERROR('Invalid schedule status.', 16, 1);
            RETURN;
        END

        -- If cancelling, check if there are any confirmed reservations
        IF @NewStatus = 'Cancelled'
        BEGIN
            IF EXISTS (
                SELECT 1 
                FROM Reservations 
                WHERE ScheduleID = @ScheduleID 
                AND Status = 'Confirmed'
            )
            BEGIN
                RAISERROR('Cannot cancel tour schedule with confirmed reservations.', 16, 1);
                RETURN;
            END
        END

        UPDATE TourSchedules
        SET Status = @NewStatus,
            ModifiedDate = GETDATE()
        WHERE ScheduleID = @ScheduleID;

        -- If cancelled, update all pending reservations
        IF @NewStatus = 'Cancelled'
        BEGIN
            UPDATE Reservations
            SET Status = 'Cancelled',
                ModifiedDate = GETDATE()
            WHERE ScheduleID = @ScheduleID
            AND Status = 'Pending';
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateTourScheduleStatus', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Search and Filter Procedures


CREATE OR ALTER PROCEDURE dbo.SearchAvailableTours
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @StartCityID INT = NULL,
    @MaxPrice DECIMAL(10,2) = NULL,
    @TourTypeID INT = NULL,
    @MinParticipants INT = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        SELECT 
            t.TourID,
            t.TourName,
            tt.TypeName AS TourType,
            sc.CityName AS StartCity,
            ec.CityName AS EndCity,
            t.Duration,
            t.BasePrice,
            t.MaxParticipants,
            ts.StartDate,
            ts.EndDate,
            ts.CurrentParticipants,
            (t.MaxParticipants - ts.CurrentParticipants) AS AvailableSpots
        FROM Tours t
        INNER JOIN TourTypes tt ON t.TourTypeID = tt.TourTypeID
        INNER JOIN Cities sc ON t.StartCityID = sc.CityID
        INNER JOIN Cities ec ON t.EndCityID = ec.CityID
        INNER JOIN TourSchedules ts ON t.TourID = ts.TourID
        WHERE ts.Status = 'Active'
        AND (@StartDate IS NULL OR ts.StartDate >= @StartDate)
        AND (@EndDate IS NULL OR ts.EndDate <= @EndDate)
        AND (@StartCityID IS NULL OR t.StartCityID = @StartCityID)
        AND (@MaxPrice IS NULL OR t.BasePrice <= @MaxPrice)
        AND (@TourTypeID IS NULL OR t.TourTypeID = @TourTypeID)
        AND (@MinParticipants IS NULL 
             OR (t.MaxParticipants - ts.CurrentParticipants) >= @MinParticipants)
        ORDER BY ts.StartDate;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'SearchAvailableTours', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Client History and Analytics


CREATE OR ALTER PROCEDURE dbo.GetClientTourHistory
    @ClientID INT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        SELECT 
            r.ReservationID,
            t.TourName,
            ts.StartDate,
            ts.EndDate,
            r.NumberOfParticipants,
            r.TotalPrice,
            r.Status AS ReservationStatus,
            ISNULL(SUM(p.Amount), 0) AS PaidAmount,
            r.TotalPrice - ISNULL(SUM(p.Amount), 0) AS RemainingBalance
        FROM Reservations r
        INNER JOIN TourSchedules ts ON r.ScheduleID = ts.ScheduleID
        INNER JOIN Tours t ON ts.TourID = t.TourID
        LEFT JOIN Payments p ON r.ReservationID = p.ReservationID 
            AND p.PaymentStatus = 'Completed'
        WHERE r.ClientID = @ClientID
        GROUP BY 
            r.ReservationID, t.TourName, ts.StartDate, ts.EndDate,
            r.NumberOfParticipants, r.TotalPrice, r.Status
        ORDER BY ts.StartDate DESC;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'GetClientTourHistory', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Guide Management


CREATE OR ALTER PROCEDURE dbo.GetGuideSchedule
    @GuideID INT,
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        SELECT 
            ts.ScheduleID,
            t.TourName,
            ts.StartDate,
            ts.EndDate,
            t.MaxParticipants,
            ts.CurrentParticipants,
            ts.Status,
            sc.CityName AS StartCity,
            ec.CityName AS EndCity
        FROM TourSchedules ts
        INNER JOIN Tours t ON ts.TourID = t.TourID
        INNER JOIN Cities sc ON t.StartCityID = sc.CityID
        INNER JOIN Cities ec ON t.EndCityID = ec.CityID
        WHERE ts.GuideID = @GuideID
        AND ts.StartDate <= @EndDate
        AND ts.EndDate >= @StartDate
        ORDER BY ts.StartDate;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'GetGuideSchedule', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Tour Analytics


CREATE OR ALTER PROCEDURE dbo.GetTourStatistics
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        SELECT 
            t.TourID,
            t.TourName,
            COUNT(DISTINCT ts.ScheduleID) AS TotalSchedules,
            COUNT(DISTINCT r.ReservationID) AS TotalReservations,
            SUM(r.NumberOfParticipants) AS TotalParticipants,
            SUM(r.TotalPrice) AS TotalRevenue,
            AVG(CAST(r.NumberOfParticipants AS FLOAT)) AS AvgParticipantsPerReservation,
            SUM(CASE WHEN r.Status = 'Cancelled' THEN 1 ELSE 0 END) AS CancelledReservations,
            CAST(SUM(CASE WHEN r.Status = 'Cancelled' THEN 1 ELSE 0 END) AS FLOAT) / 
                NULLIF(COUNT(r.ReservationID), 0) * 100 AS CancellationRate
        FROM Tours t
        LEFT JOIN TourSchedules ts ON t.TourID = ts.TourID
        LEFT JOIN Reservations r ON ts.ScheduleID = r.ScheduleID
        WHERE (@StartDate IS NULL OR ts.StartDate >= @StartDate)
        AND (@EndDate IS NULL OR ts.EndDate <= @EndDate)
        GROUP BY t.TourID, t.TourName
        ORDER BY TotalRevenue DESC;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'GetTourStatistics', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Continents


CREATE OR ALTER PROCEDURE dbo.CreateContinent
    @ContinentName NVARCHAR(50),
    @ContinentID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM Continents WHERE ContinentName = @ContinentName)
        BEGIN
            RAISERROR('Continent with this name already exists.', 16, 1);
            RETURN;
        END

        INSERT INTO Continents (ContinentName)
        VALUES (@ContinentName);

        SET @ContinentID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateContinent', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateContinent
    @ContinentID INT,
    @ContinentName NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM Continents WHERE ContinentID = @ContinentID)
        BEGIN
            RAISERROR('Continent does not exist.', 16, 1);
            RETURN;
        END

        UPDATE Continents
        SET ContinentName = @ContinentName
        WHERE ContinentID = @ContinentID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateContinent', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

-- Countries
CREATE OR ALTER PROCEDURE dbo.CreateCountry
    @CountryName NVARCHAR(100),
    @ContinentID INT,
    @CountryCode NCHAR(2),
    @CountryID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM Countries WHERE CountryCode = @CountryCode)
        BEGIN
            RAISERROR('Country with this code already exists.', 16, 1);
            RETURN;
        END

        INSERT INTO Countries (CountryName, ContinentID, CountryCode)
        VALUES (@CountryName, @ContinentID, @CountryCode);

        SET @CountryID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateCountry', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateCountry
    @CountryID INT,
    @CountryName NVARCHAR(100),
    @ContinentID INT,
    @CountryCode NCHAR(2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM Countries WHERE CountryID = @CountryID)
        BEGIN
            RAISERROR('Country does not exist.', 16, 1);
            RETURN;
        END

        IF EXISTS (SELECT 1 FROM Countries WHERE CountryCode = @CountryCode AND CountryID != @CountryID)
        BEGIN
            RAISERROR('Country code is already in use.', 16, 1);
            RETURN;
        END

        UPDATE Countries
        SET CountryName = @CountryName,
            ContinentID = @ContinentID,
            CountryCode = @CountryCode
        WHERE CountryID = @CountryID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateCountry', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Insurance Plans


CREATE OR ALTER PROCEDURE dbo.CreateInsurancePlan
    @PlanName NVARCHAR(100),
    @Coverage NVARCHAR(MAX),
    @Cost DECIMAL(10,2),
    @InsurancePlanID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM InsurancePlans WHERE PlanName = @PlanName)
        BEGIN
            RAISERROR('Insurance plan with this name already exists.', 16, 1);
            RETURN;
        END

        INSERT INTO InsurancePlans (PlanName, Coverage, Cost)
        VALUES (@PlanName, @Coverage, @Cost);

        SET @InsurancePlanID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateInsurancePlan', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateInsurancePlan
    @InsurancePlanID INT,
    @PlanName NVARCHAR(100),
    @Coverage NVARCHAR(MAX),
    @Cost DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM InsurancePlans WHERE InsurancePlanID = @InsurancePlanID)
        BEGIN
            RAISERROR('Insurance plan does not exist.', 16, 1);
            RETURN;
        END

        UPDATE InsurancePlans
        SET PlanName = @PlanName,
            Coverage = @Coverage,
            Cost = @Cost
        WHERE InsurancePlanID = @InsurancePlanID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateInsurancePlan', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Procedure for adding insurance to reservation


CREATE OR ALTER PROCEDURE dbo.AddInsuranceToReservation
    @ReservationID INT,
    @InsurancePlanID INT,
    @ReservationInsuranceID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Verify reservation exists and is not cancelled
        IF NOT EXISTS (
            SELECT 1 
            FROM Reservations 
            WHERE ReservationID = @ReservationID 
            AND Status != 'Cancelled'
        )
        BEGIN
            RAISERROR('Invalid or cancelled reservation.', 16, 1);
            RETURN;
        END

        -- Get insurance cost
        DECLARE @InsuranceCost DECIMAL(10,2);
        SELECT @InsuranceCost = Cost 
        FROM InsurancePlans 
        WHERE InsurancePlanID = @InsurancePlanID;

        IF @InsuranceCost IS NULL
        BEGIN
            RAISERROR('Invalid insurance plan.', 16, 1);
            RETURN;
        END

        INSERT INTO ReservationInsurance (
            ReservationID,
            InsurancePlanID,
            Cost
        )
        VALUES (
            @ReservationID,
            @InsurancePlanID,
            @InsuranceCost
        );

        SET @ReservationInsuranceID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'AddInsuranceToReservation', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Tour Amenities Management


CREATE OR ALTER PROCEDURE dbo.CreateAmenityType
    @TypeName NVARCHAR(50),
    @Description NVARCHAR(500) = NULL,
    @AmenityTypeID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM AmenityTypes WHERE TypeName = @TypeName)
        BEGIN
            RAISERROR('Amenity type with this name already exists.', 16, 1);
            RETURN;
        END

        INSERT INTO AmenityTypes (TypeName, Description)
        VALUES (@TypeName, @Description);

        SET @AmenityTypeID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateAmenityType', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.AddTourAmenity
    @TourID INT,
    @AmenityTypeID INT,
    @Description NVARCHAR(500) = NULL,
    @AdditionalCost DECIMAL(10,2) = 0,
    @TourAmenityID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        -- Verify Tour exists
        IF NOT EXISTS (SELECT 1 FROM Tours WHERE TourID = @TourID)
        BEGIN
            RAISERROR('Invalid Tour ID.', 16, 1);
            RETURN;
        END

        -- Verify AmenityType exists
        IF NOT EXISTS (SELECT 1 FROM AmenityTypes WHERE AmenityTypeID = @AmenityTypeID)
        BEGIN
            RAISERROR('Invalid Amenity Type ID.', 16, 1);
            RETURN;
        END

        -- Check if this amenity is already added to this tour
        IF EXISTS (
            SELECT 1 
            FROM TourAmenities 
            WHERE TourID = @TourID AND AmenityTypeID = @AmenityTypeID
        )
        BEGIN
            RAISERROR('This amenity is already added to this tour.', 16, 1);
            RETURN;
        END

        INSERT INTO TourAmenities (
            TourID,
            AmenityTypeID,
            Description,
            AdditionalCost
        )
        VALUES (
            @TourID,
            @AmenityTypeID,
            @Description,
            @AdditionalCost
        );

        SET @TourAmenityID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'AddTourAmenity', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateTourAmenity
    @TourAmenityID INT,
    @Description NVARCHAR(500) = NULL,
    @AdditionalCost DECIMAL(10,2) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM TourAmenities WHERE TourAmenityID = @TourAmenityID)
        BEGIN
            RAISERROR('Tour amenity does not exist.', 16, 1);
            RETURN;
        END

        UPDATE TourAmenities
        SET Description = ISNULL(@Description, Description),
            AdditionalCost = ISNULL(@AdditionalCost, AdditionalCost)
        WHERE TourAmenityID = @TourAmenityID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateTourAmenity', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO


-- Client Categories Management


CREATE OR ALTER PROCEDURE dbo.CreateClientCategory
    @CategoryName NVARCHAR(50),
    @DiscountPercentage DECIMAL(5,2),
    @Description NVARCHAR(500) = NULL,
    @CategoryID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF EXISTS (SELECT 1 FROM ClientCategories WHERE CategoryName = @CategoryName)
        BEGIN
            RAISERROR('Category with this name already exists.', 16, 1);
            RETURN;
        END

        IF @DiscountPercentage < 0 OR @DiscountPercentage > 100
        BEGIN
            RAISERROR('Discount percentage must be between 0 and 100.', 16, 1);
            RETURN;
        END

        INSERT INTO ClientCategories (
            CategoryName,
            DiscountPercentage,
            Description
        )
        VALUES (
            @CategoryName,
            @DiscountPercentage,
            @Description
        );

        SET @CategoryID = SCOPE_IDENTITY();
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'CreateClientCategory', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO

CREATE OR ALTER PROCEDURE dbo.UpdateClientCategory
    @CategoryID INT,
    @CategoryName NVARCHAR(50),
    @DiscountPercentage DECIMAL(5,2),
    @Description NVARCHAR(500) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    BEGIN TRY
        IF NOT EXISTS (SELECT 1 FROM ClientCategories WHERE CategoryID = @CategoryID)
        BEGIN
            RAISERROR('Category does not exist.', 16, 1);
            RETURN;
        END

        IF EXISTS (
            SELECT 1 
            FROM ClientCategories 
            WHERE CategoryName = @CategoryName 
            AND CategoryID != @CategoryID
        )
        BEGIN
            RAISERROR('Another category with this name already exists.', 16, 1);
            RETURN;
        END

        IF @DiscountPercentage < 0 OR @DiscountPercentage > 100
        BEGIN
            RAISERROR('Discount percentage must be between 0 and 100.', 16, 1);
            RETURN;
        END

        UPDATE ClientCategories
        SET CategoryName = @CategoryName,
            DiscountPercentage = @DiscountPercentage,
            Description = @Description
        WHERE CategoryID = @CategoryID;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE();
        EXEC dbo.LogError @ProcedureName = 'UpdateClientCategory', @ErrorMessage = @ErrorMsg;
        THROW;
    END CATCH
END;
GO