ERP Free version - First Phase

Core Logic & Architecture

1. Database Provisioning:

   • Free users register and get a dedicated tenant database cloned from a template database (mfg_template).
   • Database migrations are automatically run against the cloned tenant database using EvolveDb.
   • Tenant metadata is tracked in ClientDb with the flag IsFreeVersion = 1.

2. Passwordless Authentication & Cookie Coexistence:

   • Free version authentication is passwordless, validating users via Telegram OTP or Email OTP.
   • A cookie authentication scheme (FreeVersionCookie) is introduced for Free Version users, coexisting with the default Azure AD B2C (OpenIdConnect) scheme.

3. Global Record Limits:

   • A global Action Filter (FreeVersionLimitActionFilter) intercepts mutating HTTP requests (POST, PUT, PATCH) for Free Version tenants (IsFreeVersion = 1).
   • Limits are read from FreeVersionLimits table or resolved via the [FreeVersionTable] controller attribute (e.g., 20 invoices, 20 vouchers, 100 items, and a default limit of 11 records for others).

4. Database Prerequisites:

   • Restore the database backup template from SharePoint Backup Link as mfgfreetemplate.

   • Execute the following scripts in the ErpCrystalMfg system database before proceeding:

     Script 1: Setup FreeUserSession Table

     CREATE TABLE FreeUserSession (
         SessionId NVARCHAR(50) PRIMARY KEY,
         FirstName NVARCHAR(100),
         LastName NVARCHAR(100),
         Email NVARCHAR(255),
         MobileNumber NVARCHAR(20),
         NormalizedMobile NVARCHAR(20),
         OrganisationName NVARCHAR(200),
         OrganisationType NVARCHAR(50),
         NoOfUsers INT DEFAULT 1,
         IndustryType NVARCHAR(100),
         Purpose NVARCHAR(MAX),
         VerificationChannel NVARCHAR(20),
         ConsentStatus BIT,
         TelegramToken NVARCHAR(50),
         TelegramUserId BIGINT,
         TelegramChatId NVARCHAR(50),
         HandshakeStatus NVARCHAR(20),
         OtpHash NVARCHAR(255),
         OtpExpiry DATETIME,
         OtpAttemptCount INT DEFAULT 0,
         CreatedAt DATETIME DEFAULT GETDATE(),
         UpdatedAt DATETIME DEFAULT GETDATE()
     );

     Script 2: Setup Tenant Registration & Limits Tables

     BEGIN
         -- 1. Tag Free Version tenants
         DECLARE @clientDbCount INT = 0;
         SELECT @clientDbCount = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ClientDb' AND COLUMN_NAME = 'IsFreeVersion';
     
         IF @clientDbCount = 0
         BEGIN
             ALTER TABLE ClientDb ADD IsFreeVersion BIT NOT NULL DEFAULT 0;
         END
     
         -- 2. Store Free Version user credentials (no password required as we use Telegram OTP verification)
         DECLARE @freeVersionUsersCount INT = 0;
         SELECT @freeVersionUsersCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FreeVersionUsers';
     
         IF @freeVersionUsersCount = 0
         BEGIN
             CREATE TABLE FreeVersionUsers (
                 Id          INT IDENTITY(1,1) PRIMARY KEY,
                 Email       NVARCHAR(256) NOT NULL,
                 MobileNumber NVARCHAR(50) NOT NULL,
                 FullName    NVARCHAR(100) NOT NULL,
                 CompanyName NVARCHAR(200) NOT NULL,
                 DbName      NVARCHAR(128) NOT NULL,
                 CreatedAt   DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
                 LastLoginAt DATETIME2 NULL,
                 IsActive    BIT NOT NULL DEFAULT 1
             );
         END
     
         -- 3. Store per-table record limits
         DECLARE @freeVersionLimitsCount INT = 0;
         SELECT @freeVersionLimitsCount = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FreeVersionLimits';
     
         IF @freeVersionLimitsCount = 0
         BEGIN
             CREATE TABLE FreeVersionLimits (
                 TableName  NVARCHAR(128) PRIMARY KEY,
                 MaxRecords INT NOT NULL,
                 Description NVARCHAR(256) NULL
             );
     
             -- 4. Seed defaults
             INSERT INTO FreeVersionLimits (TableName, MaxRecords, Description) VALUES
                 ('invoice',      21, 'Invoices'),
                 ('voucher',      21, 'Vouchers / Payments / Receipts'),
                 ('inventorymst', 101, 'Items / Inventory Masters'),
                 ('brandmst', 2, 'Brand Masters');
         END
     END

5. Required Web Application Configuration:

   • For routing and login rendering to work based on the subdomain, the following configuration must be added to the Web app’s appsettings.json file:

     "Subdomains": {
       "Enterprise": [ "mfg", "enterprise" ],
       "FreeVersion": [ "free", "demo", "localhost" ]
     }

---

File-by-File Summary of Changes

1. InvalidUser.razor

• Checks if the user is authenticated via the free version (auth_type == "freeversion").
• Redirects invalid users to the Free Version sign-out route (/freeversionsignin/logout) instead of the Azure AD B2C sign-out route.

2. LoginDisplay.razor

• Configured subdomain check based on settings (Subdomains:Enterprise and Subdomains:FreeVersion) to conditionally render Enterprise or Free Version sign-in links.
• Redirects authenticated Free Version users to /freeversion/dashboard instead of the root / page.
• Directs SignOut requests for free users to /freeversionsignin/logout.

3. SideBar.razor

• Adds a null/whitespace safety check on _PostLogin.dbname before calling the repository database methods. This prevents runtime errors during the anonymous onboarding phases.

4. CustomValidation.cs

• Injects MudBlazor’s ISnackbar service.
• If validation errors contain keys such as SnackbarError, SnackbarWarning, SnackbarInfo, or SnackbarSuccess, it displays them directly via toast notifications instead of injecting them into EditContext fields.

5. Program.cs (API)

• Registers the FreeVersionLimitActionFilter globally within the MVC pipeline.
• Registers IFreeVersionRepository and FreeVersionRepository for dependency injection.

6. Program.cs (Web)

• Configures FreeVersionCookie authentication scheme.
• Adds ForwardDefaultSelector to OpenIdConnectOptions to dynamically select the FreeVersionCookie scheme for free.* subdomains, /freeversion and /freeversionsignin paths, or when the FreeVersionCookie is present.
• Registers the IFreeVersionService and FreeVersionService for dependency injection.

7. Index.razor

• Gated the enterprise <WelcomeCard /> so it is only rendered for non-free version users (auth_type != "freeversion").

8. TwoFactorAuthOption.razor

• Redirects Free Version users to the cookie logout endpoint (/freeversionsignin/logout) during SignOut().

9. WelcomeCard.razor

• Cleaned up obsolete, commented-out 2FA session verification and sign-out logic blocks.