/*
Expand dbo.MedicineCacheSource to include ALL active medicines from dbo.Medicine (medicine master).

Use this to test default MedicineCache startup (LoadFromMedicineTable=false):
  - App loads from MedicineCacheSource at login
  - After this script, that subset equals the full active master catalogue (~154k)

Steps:
  1) Backs up current MedicineCacheSource MedicineIds (for revert)
  2) Builds missing rows only (insert-only; does not refresh rows already in cache)
  3) Inserts in batches ordered by MedicineNameNospace (clustered key friendly)

Performance notes (no new indexes on dbo tables):
  - Replaces per-medicine OUTER APPLY on Batch with two set-based Batch scans
  - Avoids MERGE + mass UPDATE (slow when clustered index is on MedicineNameNospace)
  - Uses temp-table joins for "already cached" detection (not dbo.MedicineCacheSource scans per row)

After running: log out / restart app so in-memory MedicineCache reloads.

Revert: database\MedicineCacheSource_PerfTest_Revert.sql
*/

USE PharmacyProDB;
GO

SET NOCOUNT ON;

DECLARE @BeforeCount INT;
DECLARE @MasterCount INT;
DECLARE @AfterCount INT;
DECLARE @MissingCount INT;
DECLARE @Inserted INT = 0;
DECLARE @BatchSize INT = 10000;
DECLARE @BatchNum INT = 0;
DECLARE @BatchInserted INT;

SELECT @BeforeCount = COUNT(*) FROM dbo.MedicineCacheSource;

SELECT @MasterCount = COUNT(*)
FROM dbo.Medicine m WITH (NOLOCK)
WHERE ISNULL(m.Deleted, 0) = 0
  AND ISNULL(m.IsActive, 1) = 1;

PRINT CONCAT('MedicineCacheSource rows before: ', @BeforeCount);
PRINT CONCAT('Active medicines in master: ', @MasterCount);

IF OBJECT_ID(N'dbo.MedicineCacheSource', N'U') IS NULL
BEGIN
    RAISERROR('dbo.MedicineCacheSource does not exist. Run database\MedicineCacheSource_FromStockEffects.sql first.', 16, 1);
    RETURN;
END;

IF OBJECT_ID(N'dbo.MedicineCacheSource_PerfTestBackup', N'U') IS NOT NULL
    DROP TABLE dbo.MedicineCacheSource_PerfTestBackup;

SELECT MedicineId
INTO dbo.MedicineCacheSource_PerfTestBackup
FROM dbo.MedicineCacheSource;

PRINT CONCAT('Backup table created with ', @@ROWCOUNT, ' MedicineId(s).');

IF OBJECT_ID(N'tempdb..#ExistingIds', N'U') IS NOT NULL
    DROP TABLE #ExistingIds;

-- COLLATE DATABASE_DEFAULT: temp tables inherit tempdb collation; user DBs may differ
-- (Msg 468: cannot resolve collation conflict in equal to operation).
CREATE TABLE #ExistingIds
(
    MedicineId NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY CLUSTERED
);

INSERT INTO #ExistingIds (MedicineId)
SELECT MedicineId
FROM dbo.MedicineCacheSource WITH (NOLOCK);

PRINT CONCAT('Existing cache MedicineId snapshot: ', @@ROWCOUNT);

IF OBJECT_ID(N'tempdb..#ToInsert', N'U') IS NOT NULL
    DROP TABLE #ToInsert;

CREATE TABLE #ToInsert
(
    MedicineId NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL PRIMARY KEY CLUSTERED,
    MedicineNameDetailed NVARCHAR(512) COLLATE DATABASE_DEFAULT NULL,
    MedicineNameNospace NVARCHAR(512) COLLATE DATABASE_DEFAULT NULL,
    MedicineShortCode NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    MedicineBarcode NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    ManufacturerName NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    GenericId NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    CurrQuantity BIGINT NULL,
    CurrLoose BIGINT NULL,
    SellingPrice FLOAT NULL,
    RateItemwise BIT NULL,
    Mrp FLOAT NULL,
    MaxDiscount FLOAT NULL,
    DiscPer FLOAT NULL,
    AllowDiscount BIT NULL,
    IsActive BIT NULL,
    Rack NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    Rack1 NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    Divisor INT NULL,
    IsLoose BIT NULL,
    MedicineGstPer FLOAT NULL,
    HsnCode NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    CategoryName NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    CategoryId NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    GenericType NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    GenericDosage NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NULL,
    IsH1 BIT NULL,
    BestBatch NVARCHAR(255) COLLATE DATABASE_DEFAULT NULL,
    HasValidBatch BIT NOT NULL
);

PRINT 'Building missing rows (set-based Batch MRP / BestBatch)...';

;WITH LatestMrp AS
(
    SELECT
        b.MedicineId,
        b.Mrp,
        ROW_NUMBER() OVER (PARTITION BY b.MedicineId ORDER BY b.CreatedTime DESC) AS rn
    FROM dbo.Batch b WITH (NOLOCK)
    WHERE b.Deleted = 0
),
LatestMrpPick AS
(
    SELECT MedicineId, Mrp
    FROM LatestMrp
    WHERE rn = 1
),
BestBatch AS
(
    SELECT
        b.MedicineId,
        b.BatchId AS BestBatch,
        ROW_NUMBER() OVER (PARTITION BY b.MedicineId ORDER BY b.BatchExpiry DESC) AS rn
    FROM dbo.Batch b WITH (NOLOCK)
    WHERE b.Deleted = 0
      AND b.BatchExpiry > GETDATE()
),
BestBatchPick AS
(
    SELECT MedicineId, BestBatch
    FROM BestBatch
    WHERE rn = 1
)
INSERT INTO #ToInsert
(
    MedicineId,
    MedicineNameDetailed,
    MedicineNameNospace,
    MedicineShortCode,
    MedicineBarcode,
    ManufacturerName,
    GenericId,
    CurrQuantity,
    CurrLoose,
    SellingPrice,
    RateItemwise,
    Mrp,
    MaxDiscount,
    DiscPer,
    AllowDiscount,
    IsActive,
    Rack,
    Rack1,
    Divisor,
    IsLoose,
    MedicineGstPer,
    HsnCode,
    CategoryName,
    CategoryId,
    GenericType,
    GenericDosage,
    IsH1,
    BestBatch,
    HasValidBatch
)
SELECT
    m.MedicineId,
    m.MedicineNameDetailed,
    m.MedicineNameNospace,
    m.MedicineShortCode,
    m.Barcode,
    m.ManufacturerName,
    m.GenericId,
    m.CurrQuantity,
    m.CurrLoose,
    m.SellingPrice,
    m.RateItemwise,
    mrp.Mrp,
    m.MaxDiscount,
    m.DiscPer,
    m.AllowDiscount,
    m.IsActive,
    m.Rack,
    m.Rack1,
    m.Divisor,
    m.IsLoose,
    m.MedicineGstPer,
    m.HsnCode,
    c.CategoryName,
    c.CategoryId,
    g.GenericType,
    g.GenericDosage,
    g.IsH1,
    bb.BestBatch,
    CAST(CASE WHEN bb.BestBatch IS NOT NULL THEN 1 ELSE 0 END AS BIT)
FROM dbo.Medicine m WITH (NOLOCK)
LEFT JOIN LatestMrpPick mrp ON mrp.MedicineId = m.MedicineId
LEFT JOIN BestBatchPick bb ON bb.MedicineId = m.MedicineId
LEFT JOIN dbo.Category c WITH (NOLOCK) ON c.CategoryId = m.CategoryId
LEFT JOIN dbo.Generic g WITH (NOLOCK) ON g.GenericId = m.GenericId
WHERE ISNULL(m.Deleted, 0) = 0
  AND ISNULL(m.IsActive, 1) = 1
  AND NOT EXISTS (
      SELECT 1
      FROM #ExistingIds e
      WHERE e.MedicineId = m.MedicineId
  );

SET @MissingCount = @@ROWCOUNT;
PRINT CONCAT('Rows to insert: ', @MissingCount);

IF @MissingCount = 0
BEGIN
    PRINT 'Nothing to insert — cache already contains all active master medicines.';
END
ELSE
BEGIN
    PRINT CONCAT('Inserting in batches of ', @BatchSize, ' (ordered by MedicineNameNospace)...');

    WHILE EXISTS (SELECT 1 FROM #ToInsert)
    BEGIN
        SET @BatchNum = @BatchNum + 1;

        BEGIN TRY
            BEGIN TRANSACTION;

            INSERT INTO dbo.MedicineCacheSource
            (
                MedicineId,
                MedicineNameDetailed,
                MedicineNameNospace,
                MedicineShortCode,
                MedicineBarcode,
                ManufacturerName,
                GenericId,
                CurrQuantity,
                CurrLoose,
                SellingPrice,
                RateItemwise,
                Mrp,
                MaxDiscount,
                DiscPer,
                AllowDiscount,
                IsActive,
                Rack,
                Rack1,
                Divisor,
                IsLoose,
                MedicineGstPer,
                HsnCode,
                CategoryName,
                CategoryId,
                GenericType,
                GenericDosage,
                IsH1,
                BestBatch,
                HasValidBatch,
                SeededAtUtc,
                SeededFromPurchaseDbFallback
            )
            SELECT TOP (@BatchSize)
                src.MedicineId,
                src.MedicineNameDetailed,
                src.MedicineNameNospace,
                src.MedicineShortCode,
                src.MedicineBarcode,
                src.ManufacturerName,
                src.GenericId,
                src.CurrQuantity,
                src.CurrLoose,
                src.SellingPrice,
                src.RateItemwise,
                src.Mrp,
                src.MaxDiscount,
                src.DiscPer,
                src.AllowDiscount,
                src.IsActive,
                src.Rack,
                src.Rack1,
                src.Divisor,
                src.IsLoose,
                src.MedicineGstPer,
                src.HsnCode,
                src.CategoryName,
                src.CategoryId,
                src.GenericType,
                src.GenericDosage,
                src.IsH1,
                src.BestBatch,
                src.HasValidBatch,
                SYSUTCDATETIME(),
                0
            FROM #ToInsert src
            ORDER BY src.MedicineNameNospace, src.MedicineId;

            SET @BatchInserted = @@ROWCOUNT;
            SET @Inserted = @Inserted + @BatchInserted;

            DELETE t
            FROM #ToInsert t
            INNER JOIN (
                SELECT TOP (@BatchSize) MedicineId
                FROM #ToInsert
                ORDER BY MedicineNameNospace, MedicineId
            ) picked ON picked.MedicineId = t.MedicineId;

            COMMIT TRANSACTION;

            PRINT CONCAT(
                'Batch ', @BatchNum,
                ': inserted ', @BatchInserted,
                ' (total ', @Inserted, ' / ', @MissingCount, ')');
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0
                ROLLBACK TRANSACTION;

            DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE();
            RAISERROR('Insert batch %d failed: %s', 16, 1, @BatchNum, @Err);
            RETURN;
        END CATCH;
    END;
END;

SELECT @AfterCount = COUNT(*) FROM dbo.MedicineCacheSource;

PRINT CONCAT('Expand complete. Rows inserted: ', @Inserted);
PRINT CONCAT('MedicineCacheSource rows after: ', @AfterCount);
PRINT CONCAT('Added since backup: ', @AfterCount - @BeforeCount);
GO