TblPrtUsers
PrtSubScriberID	nvarchar(50)	Checked
StuSmsSendDate	datetime	Checked
StuSmsStatus	nvarchar(50)	Checked
StuSmsOtpNo	nvarchar(50)	Checked
StuSmsMaxSent	nvarchar(50)	Checked
--------------------
use proacademy
GO

/****** Object:  Table [dbo].[TblBranch]    Script Date: 10/16/2025 11:39:03 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TblBranch](
	[BID] [int] IDENTITY(1,1) NOT NULL,
	[BranchID] [nvarchar](50) NULL,
	[BranchName] [nvarchar](100) NULL,
	[BranchLocation] [nvarchar](500) NULL,
	[Lat] [nvarchar](50) NULL,
	[Lang] [nvarchar](50) NULL,
	[PrtSubScriberID] [nvarchar](50) NULL,
	[ProductName] [nvarchar](100) NULL,
	[ProductID] [int] NULL,
	[IsDataStatus] [int] NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ModifyBy] [nvarchar](100) NULL,
	[ModifyDate] [datetime] NULL,
	[isDataStatusval]  AS ([DBO].[GetIsDataStatusVal]([IsDataStatus])),
	[ContactID] [int] NULL,
	[KitProductID] [int] NULL,
	[KitProductName] [nvarchar](100) NULL,
	[InventoryID] [int] NULL,
 CONSTRAINT [PK_TblBranch] PRIMARY KEY CLUSTERED
(
	[BID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

---------------------
update TblPrtUsers set PrtSubScriberID='a6d8c1e2f9b0475ab2e3c4d5917f8a30'

----------------
TblStudentInfo
PrtSubScriberID
-------------
-- ==============================================
-- Add column [PrtSubScriberID NVARCHAR(50)] to all user tables
-- Skips if column already exists
-- ==============================================
DECLARE @TableName NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);

DECLARE TableCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
WHERE t.is_ms_shipped = 0  -- exclude system tables
ORDER BY t.name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@TableName)
          AND c.name = 'PrtSubScriberID'
    )
    BEGIN
        SET @Sql = 'ALTER TABLE [' + @TableName + '] ADD [PrtSubScriberID] NVARCHAR(50) NULL;';
        PRINT 'Adding column to table: ' + @TableName;
        EXEC sp_executesql @Sql;
    END
    ELSE
    BEGIN
        PRINT 'Column already exists in table: ' + @TableName;
    END

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

PRINT '✅ Done: Checked all tables and added PrtSubScriberID where missing.';
----------------------------
-- ==============================================
-- Update PrtSubScriberID for all tables that have the column
-- ==============================================
DECLARE @TableName NVARCHAR(255);
DECLARE @Sql NVARCHAR(MAX);
DECLARE @Value NVARCHAR(50) = N'a6d8c1e2f9b0475ab2e3c4d5917f8a30';

DECLARE TableCursor CURSOR FOR
SELECT t.name
FROM sys.tables t
WHERE t.is_ms_shipped = 0
ORDER BY t.name;

OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check if PrtSubScriberID column exists
    IF EXISTS (
        SELECT 1
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@TableName)
          AND c.name = 'PrtSubScriberID'
    )
    BEGIN
        SET @Sql = 'UPDATE [' + @TableName + '] SET [PrtSubScriberID] = N''' + @Value + ''';';
        PRINT 'Updating table: ' + @TableName;
        EXEC sp_executesql @Sql;
    END
    ELSE
    BEGIN
        PRINT 'Skipped table (no PrtSubScriberID): ' + @TableName;
    END

    FETCH NEXT FROM TableCursor INTO @TableName;
END

CLOSE TableCursor;
DEALLOCATE TableCursor;

PRINT '✅ Done: All tables with PrtSubScriberID updated successfully.';
--------------
TblStuQuodInvoice
InvoiceNumber
--------------
TblStuOffer
OfferDesc
---------------------
TblStudentPayKit
StuKitID
StuProductID

TblGroupInfo
SubstituteCoach
------------
TblLokDuration
TransportAmount
DisPercentage

TblStudentInfo
StuCityName nvarchar(50)
StuDistrict nvarchar(50)
StuOtherNationality nvarchar(50)


TblStudentPay
StuProductID nvarchar(50)


------
/* ============================================
   Add missing NVARCHAR(50) columns, if needed
   ============================================ */

-- Ensure TblStudentPay exists before altering
IF OBJECT_ID('dbo.TblStudentPay', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('dbo.TblStudentPay', 'StuKitID') IS NULL
        ALTER TABLE dbo.TblStudentPay ADD StuKitID NVARCHAR(50) NULL;

    IF COL_LENGTH('dbo.TblStudentPay', 'TransPortAmount') IS NULL
        ALTER TABLE dbo.TblStudentPay ADD TransPortAmount decimal(18,2) NULL;

    IF COL_LENGTH('dbo.TblStudentPay', 'BusID') IS NULL
        ALTER TABLE dbo.TblStudentPay ADD BusID NVARCHAR(50) NULL;

    PRINT '✅ Updated: dbo.TblStudentPay';
END
ELSE
BEGIN
    PRINT '⚠️ Table not found: dbo.TblStudentPay';
END
GO

-- Ensure TblStudentPayInstallment exists before altering
IF OBJECT_ID('dbo.TblStudentPayInstallment', 'U') IS NOT NULL
BEGIN
    IF COL_LENGTH('dbo.TblStudentPayInstallment', 'StuProductID') IS NULL
        ALTER TABLE dbo.TblStudentPayInstallment ADD StuProductID NVARCHAR(50) NULL;

    IF COL_LENGTH('dbo.TblStudentPayInstallment', 'StuKitID') IS NULL
        ALTER TABLE dbo.TblStudentPayInstallment ADD StuKitID NVARCHAR(50) NULL;

    IF COL_LENGTH('dbo.TblStudentPayInstallment', 'TransPayStuTransportAmount') IS NULL
        ALTER TABLE dbo.TblStudentPayInstallment ADD TransPayStuTransportAmount decimal(18,2);

    PRINT '✅ Updated: dbo.TblStudentPayInstallment';
END
ELSE
BEGIN
    PRINT '⚠️ Table not found: dbo.TblStudentPayInstallment';
END
GO
-------------
IF COL_LENGTH('dbo.TblStudentPay', 'BusID') IS NULL
BEGIN
    ALTER TABLE dbo.TblStudentPay ADD BusID NVARCHAR(50) NULL;
    PRINT '✅ Added column BusID NVARCHAR(50) to TblStudentPay';
END
ELSE
BEGIN
    PRINT 'ℹ️ Column BusID already exists in TblStudentPay';
END
------------------------------------------

use proacademy
GO

/****** Object:  Table [dbo].[TblStudentPayTransport]    Script Date: 10/16/2025 12:53:40 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TblStudentPayTransport](
	[TransPayID] [bigint] IDENTITY(1,1) NOT NULL,
	[TransPayPrKeyID] [nvarchar](50) NULL,
	[TransPayUserID] [nvarchar](50) NULL,
	[TransPayStuTypeID] [int] NULL,
	[TransPayStuDurationID] [int] NULL,
	[TransPayStuAmount] [decimal](18, 2) NULL,
	[TransPayStartDate] [datetime] NULL,
	[TransPayEndDate] [datetime] NULL,
	[CreatedBy] [nvarchar](100) NULL,
	[CreatedDate] [datetime] NULL,
	[ModifyBy] [nvarchar](100) NULL,
	[ModifyDate] [datetime] NULL,
	[IsDataStatus] [int] NULL,
	[RefStuPaidSessionID] [nvarchar](100) NULL,
	[RefStuPaidInvoiceID] [nvarchar](50) NULL,
	[RefStuPaidDateTime] [nvarchar](50) NULL,
	[RefStuPayStatus] [nvarchar](50) NULL,
	[TransPayStuTransportAmount] [decimal](18, 2) NULL,
	[PrtSubScriberID] [nvarchar](50) NULL,
	[BusID] [nvarchar](50) NULL,
 CONSTRAINT [PK_TblStudentPayTransport] PRIMARY KEY CLUSTERED
(
	[TransPayID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


------------------------
use proacademy

/****** Object:  Table [dbo].[TblLokBus]    Script Date: 10/16/2025 12:54:50 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TblLokBus](
	[BID] [int] IDENTITY(1,1) NOT NULL,
	[BusID] [nvarchar](50) NOT NULL,
	[BusNo] [nvarchar](20) NULL,
	[EnBusName] [nvarchar](300) NULL,
	[ArBusName] [nvarchar](300) NULL,
	[CreatedBy] [nvarchar](50) NULL,
	[CreatedDate] [datetime] NULL,
	[ModifyBy] [nvarchar](50) NULL,
	[ModifyDate] [datetime] NULL,
	[OrderID] [int] NULL,
	[IsDataStatus] [int] NULL,
	[PrtSubScriberID] [nvarchar](50) NULL,
 CONSTRAINT [PK_TblLokBus] PRIMARY KEY CLUSTERED
(
	[BID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


------------------
TblStudentPay
PayRefNo
-------------
TblStudentPayInstallment
PrdGroupID
BusID

TblStudentInfo
------------
StuIsOTPVerified
-----------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter TABLE [dbo].[TblCoachInfo] add [CoachProfileImage]  AS ([DBO].[GetCoachphotov1]([CoachUserID],[PrtSubScriberID]))

------------
 


/****** Object:  UserDefinedFunction [dbo].[GetCoachphoto]    Script Date: 10/16/2025 1:42:58 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE  FUNCTION [dbo].[GetCoachphotov1]
(

	@CoachUserID  nvarchar(50),
	@PrtSubScriberID  nvarchar(50)



)
RETURNS NVARCHAR(200)
AS
BEGIN
    -- Declare the return variable here

	DECLARE @RESULT NVARCHAR(200)


	SELECT @RESULT =  (Select top 1   CoachPhoto    from TblCoachInfo where CoachUserID=@CoachUserID and PrtSubScriberID=@PrtSubScriberID );

	if (@RESULT !='')

	SELECT @RESULT = (Select  top 1 concat('https://aljoker.sa/zkm/Uploads/Coach/',CoachPhoto) as CoachPhoto   from TblCoachInfo where CoachUserID=@CoachUserID and PrtSubScriberID=@PrtSubScriberID) ;

	 	ELSE

	SELECT @RESULT = (Select top 1 'https://proacademy.sa/assets/images/logo/logo.png' as CoachPhoto   from TblCoachInfo where CoachUserID=@CoachUserID and PrtSubScriberID=@PrtSubScriberID) ;



    RETURN @RESULT

END


GO