USE [LotusDB1]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Add_Application]
    @CompanyName [nvarchar](max),
    @ProjectName [nvarchar](max),
    @CompanyTIN [nvarchar](128),
    @ProjectCode [nvarchar](max),
    @EvaluationStatus [nvarchar](max),
    @MobileNo [nvarchar](max),
    @Station [nvarchar](max),
    @StartDate [datetime2](7),
    @CreatedDate [datetime2](7),
    @Expiredate [datetime2](7),
    @Amount [decimal](18, 2),
    @ControlNo [nvarchar](max),
    @UpdatedDate [datetime2](7),
    @PaymentStatus [nvarchar](max),
    @CompanyEmail [nvarchar](128),
    @ReceiptNo [nvarchar](max),
    @PayedDate [datetime2](7),
    @CertificateNo [nvarchar](max),
    @ServiceName [nvarchar](max),
    @Comments [nvarchar](max),
    @FullName [nvarchar](max),
    @InvoiceID bigint,
    @GePGComment [nvarchar](max),
    @Currency [nvarchar](max)
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT [dbo].[ApplicationManagers]([CompanyName], [ProjectName], [CompanyTIN], [ProjectCode], [EvaluationStatus], [MobileNo], [Station], [StartDate], [CreatedDate], [Expiredate], [Amount], [ControlNo], [UpdatedDate], [PaymentStatus], [CompanyEmail], [ReceiptNo], [PayedDate], [CertificateNo], [ServiceName],[Comments],[FullName],[InvoiceID],[GePGComment],[Currency])
    VALUES (@CompanyName, @ProjectName, @CompanyTIN, @ProjectCode, @EvaluationStatus, @MobileNo, @Station, @StartDate, @CreatedDate, @Expiredate, @Amount, @ControlNo, @UpdatedDate, @PaymentStatus, @CompanyEmail, @ReceiptNo, @PayedDate, @CertificateNo, @ServiceName,@Comments,@FullName,@InvoiceID,@GePGComment,@Currency)   
    
    DECLARE @ApplicationID bigint
    SELECT @ApplicationID = [ApplicationID]
    FROM [dbo].[ApplicationManagers]
    WHERE @@ROWCOUNT > 0 AND [ApplicationID] = scope_identity()
    
    SELECT t0.[ApplicationID]
    FROM [dbo].[ApplicationManagers] AS t0
    WHERE @@ROWCOUNT > 0 AND t0.[ApplicationID] = @ApplicationID

    -- Find full name of informant
    DECLARE @ServiceTypeID bigint
    DECLARE @FrontUserId bigint
    
    SET @FrontUserId = (SELECT InvestorID FROM InvestorProfileExternals WHERE CompanyEmail=@CompanyEmail)
    SET @FullName = (SELECT FirstName + ' ' + COALESCE(MiddleName, '') + ' ' + LastName FROM InvestorProfileExternals WHERE CompanyEmail = @CompanyEmail)
    
    -- SET Service TypeID  
    IF(@ServiceName='New' AND @Currency='USD')
        SET @ServiceTypeID = 1
    ELSE IF(@ServiceName='Amendment' AND @Currency='USD')
        SET @ServiceTypeID = 2
    ELSE IF(@ServiceName='Expansion' AND @Currency='USD')
        SET @ServiceTypeID = 3
    ELSE IF(@ServiceName='Extension' AND @Currency='USD')
        SET @ServiceTypeID = 4
    ELSE IF(@ServiceName='New' AND @Currency='TZS')
        SET @ServiceTypeID = 5
    ELSE IF(@ServiceName='Amendment' AND @Currency='TZS')
        SET @ServiceTypeID = 6
    ELSE IF(@ServiceName='Expansion' AND @Currency='TZS')
        SET @ServiceTypeID = 7
    ELSE IF(@ServiceName='Extension' AND @Currency='TZS')
        SET @ServiceTypeID = 8
    ELSE IF(@ServiceName='New_Foreign' AND @Currency='TZS')
        SET @ServiceTypeID = 9
    ELSE IF(@ServiceName='Expansion_Foreign' AND @Currency='TZS')
        SET @ServiceTypeID = 10
    ELSE
        SET @ServiceTypeID = 11

    -- Generate Billcode
    DECLARE @MaxID AS varchar(max)
    SELECT @MaxID=MAX([InvoiceID]) FROM [dbo].[tblInvoice] WHERE Year([StartDate]) = Year(Getdate()) AND month([StartDate]) = month(Getdate())
    
    DECLARE @BillCode varchar(max)
    DECLARE @invoiceNo varchar(max)
    DECLARE @Year nvarchar(max)
    DECLARE @Month nvarchar(max)
    DECLARE @Yrmth nvarchar(max)
    SELECT @Year = Year(getdate())
    SELECT @Month = Month(getdate())
    SET @Yrmth = @Year + @Month
    SET @invoiceNo = (COALESCE(@MaxID,0)+1)
    SET @BillCode = @Yrmth + @invoiceNo
    
    -- Find GFSCode from tblService - determine correct ServiceName based on ownership
    DECLARE @GFSCode nvarchar(50)
    DECLARE @TypeofOwnership nvarchar(max)
    DECLARE @ServiceNameForLookup nvarchar(max) = @ServiceName
    
    -- Get ownership from ProjectProfilesExternal to determine correct ServiceName for lookup
    SELECT @TypeofOwnership = TypeofOwnership 
    FROM ProjectProfilesExternal 
    WHERE ProjectCode = @ProjectCode
    
    -- Adjust ServiceName for lookup if foreign/mixed ownership and TZS currency
    IF (@TypeofOwnership IS NOT NULL AND @TypeofOwnership <> 'Local' AND @Currency = 'TZS')
    BEGIN
        IF (@ServiceName = 'New')
            SET @ServiceNameForLookup = 'New_Foreign'
        ELSE IF (@ServiceName = 'Expansion')
            SET @ServiceNameForLookup = 'Expansion_Foreign'
    END
    
    -- Get GFSCode from tblService using the correct ServiceName
    SELECT @GFSCode = GFSCode
    FROM [dbo].[tblService] 
    WHERE ServiceName = @ServiceNameForLookup AND [Currency] = @Currency AND Status = '1'
    
    -- Use default GFSCode if not found
    IF (@GFSCode IS NULL OR @GFSCode = '')
        SET @GFSCode = '142201370002'
    
    -- Insert into tblInvoice using @Amount (already calculated correctly in C#) instead of querying tblService for Fee
    -- This ensures ApplicationManagers.Amount matches tblInvoice.Amount
    INSERT INTO [dbo].[tblInvoice] ([FullName],[PhoneNo],[GFSCode],[BillItemRefNo],[SubServiceName],[Amount],FrontUserId,ApplicationID,StartDate,Expiredate,ServiceTypeID,ApplicationCode,[Currency],[TIN_No])
    VALUES (@FullName,@MobileNo,@GFSCode,@BillCode,@ServiceName,@Amount,@FrontUserId,@ApplicationID,getdate(),getdate()+30,@ServiceTypeID,@ProjectCode,@Currency,@CompanyTIN)
    
    -- Update ApplicationManagers with FullName and InvoiceID
    DECLARE @NewInvoiceID bigint
    SELECT @NewInvoiceID = InvoiceID 
    FROM tblInvoice 
    WHERE ApplicationCode = @ProjectCode AND ApplicationID = @ApplicationID
    ORDER BY InvoiceID DESC
    
    UPDATE ApplicationManagers  
    SET FullName = @FullName, InvoiceID = @NewInvoiceID 
    WHERE ProjectCode = @ProjectCode AND ApplicationID = @ApplicationID
END
GO