----------------ACCOUNT MST MAIN
INSERT INTO accountsmst (id, mainac, typecode, chs_id, accountgroup, accountname, bsgroupcode, oldmainac, tdspercent)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,CONCAT('J', FORMAT(ROW_NUMBER()OVER( ORDER BY mainac),'000')) AS newmainac, 
IIF(description IN ('Not Applicable','Assests','Liabilities','Expense','Income'),'NA',IIF(description = 'Customer','Member',IIF(description = 'Supplier','Vendor',
IIF(description = 'Bank / Cash',IIF(achead = 'PETTY CASH','Cash','Bank'),description))))
AS typecode,'J0001' AS chs_id, IIF(B.flag ='BL','Liability', IIF(B.flag ='BA','Asset', IIF(B.flag ='PE','Expenses', IIF(B.flag ='PI','Income','')))) AS accountgroup,
achead AS accountname, AC.bsgroup, mainac AS oldmainac, 0 AS tdspercent
FROM blazorchs..accounts AC
LEFT JOIN blazorchs..actypemst AT ON AC.actype = AT.actype
LEFT JOIN blazorchs..Bsplgroups B ON AC.Bsgroup = B.groupcode
WHERE B.Type = 'B';

UPDATE accountsmst SET  accountgroup = 'Liability' WHERE oldmainac IN ('030100', '035000', '040000', '041000');

CREATE TABLE #temp
(
    NewMainAc VARCHAR(20),
    OldMainAc VARCHAR(20)
);


INSERT INTO #temp (NewMainAc, OldMainAc)
VALUES
('0001','010000'),
('0100','019900'),
('0200','020000'),
('0300','020100'),
('0400','020300'),
('0500','020400'),
('1000','030100'),
('1100','035000'),
('1200','040000'),
('1300','041000'),
('2000','050000'),
('2100','051000'),
('2200','051100'),
('2300','051200'),
('2400','051300'),
('2500','052000'),
('2600','052100'),
('2700','052101'),
('3000','053204'),
('3100','053205'),
('3200','053206'),
('3800','054200'),
('5000','061100'),
('5001','061200'),
('5050','061700'),
('5051','061800'),
('5100','063800'),
('5101','063900'),
('5150','064000'),
('5200','064100'),
('5201','064200'),
('5250','064300'),
('5251','064400'),
('5300','064500'),
('5301','064600'),
('5350','065100'),
('5351','065200'),
('5400','065300'),
('5401','065400'),
('5450','065600'),
('5451','065700'),
('5500','066000'),
('5501','066100'),
('5550','066200'),
('5551','066300'),
('5600','066900'),
('5650','067000'),
('5700','067001'),
('5601','067400'),
('5651','067500'),
('6000','071000'),
('6400','071400'),
('6500','080000'),
('7000','082000'),
('7050','082100'),
('7100','082200'),
('7150','082300'),
('7500','082400'),
('7200','082500'),
('7250','082600'),
('7300','082700'),
('7400','083000'),
('6100','083900'),
('6200','084000'),
('6300','084400'),
('6550','084500'),
('6600','085000'),
('6650','085200'),
('6700','085300'),
('6750','086000'),
('6800','086001'),
('6850','087000'),
('6900','087100'),
('6950','087101'),
('6960','087200'),
('8000','090000'),
('8050','092200'),
('8100','092400'),
('8150','092500'),
('8200','092600'),
('9000','120000'),
('9010','120001'),
('9020','120050'),
('9030','120055'),
('9040','120056'),
('9050','120057'),
('9060','120060'),
('9070','120065'),
('9080','120070'),
('9090','120075'),
('9100','120080'),
('9110','120100'),
('9120','120200'),
('9130','120300'),
('9140','120400'),
('9150','120800'),
('9160','120801'),
('9170','120900'),
('9180','121100'),
('9190','121300'),
('9200','121400'),
('9210','121500'),
('9220','121600'),
('9230','121700'),
('9240','121900'),
('9250','122000'),
('9260','122300'),
('9270','123000'),
('9280','123100'),
('9290','123200'),
('9300','123300'),
('9310','124000'),
('9320','124200'),
('9330','124300'),
('9340','125100'),
('9350','125700'),
('9360','125900'),
('9370','126000'),
('9380','126900'),
('9390','126901'),
('9400','126950'),
('9410','127000'),
('9420','127400'),
('9430','127401'),
('9440','127500'),
('9450','127700'),
('9460','127800'),
('9470','127900'),
('9480','127901'),
('9490','128000'),
('9500','128100'),
('9510','129000'),
('9520','129100'),
('9530','130000'),
('9540','140000'),
('9550','170000'),
('9560','180000'),
('9570','899999'),
('9580','900000'),
('9590','900001')


UPDATE A
SET A.MainAc = M.NewMainAc
FROM AccountsMst A
JOIN #temp M
    ON A.OldMainAc = M.OldMainAc;

UPDATE accountsmst SET tdspercent = 2 WHERE oldmainac = '053205';
UPDATE accountsmst SET tdspercent = 1 WHERE oldmainac = '053204';
UPDATE accountsmst SET tdspercent = 10 WHERE oldmainac = '053206';


INSERT INTO accountsmst1 (id, mainac,oldmainac, subac, acname, isactive, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac, AC1.mainac AS oldmainac,  RIGHT(subac,4) AS subac, acname, isactive,'J0001'
FROM accountsmst A
LEFT JOIN blazorchs.. accounts1 AC1 ON A.oldmainac = AC1.mainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.chs_id = 'J0001';

WITH A(mainac, subac)AS
(
    SELECT mainac, subac  FROM blazorchs..BillChs1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..journalvoucher1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..voucher1 
    UNION ALL
    SELECT mainac, subac  FROM blazorchs..opgbalances 
    UNION ALL
    SELECT controlac AS mainac, '000001' AS subac  FROM blazorchs..VendorMst 
    UNION ALL 
    SELECT controlac AS mainac, '000001' AS subac  FROM blazorchs..MemberMst 
    UNION ALL
    SELECT tdscode AS mainac, '000001' AS subac FROM blazorchs..BillChs WHERE tdscode != ''
)


DELETE FROM accountsmst1 WHERE  oldmainac+CONCAT('00',subac) NOT IN (SelECT mainac+subac COLLATE SQL_Latin1_General_CP1_CI_AS FROM A );

DELETE FROM accountsmst WHERE oldmainac NOT IN (SELECT oldmainac FROM accountsmst1 );

-- INSERT INTO societies(id, chs_id, chs_name, created_at) VALUES
-- (LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)),'J0001','Jasmine CHS',GETDATE())

--------------Members
INSERT INTO members (id, salutation, dob, gender, bldg_flat, area, email, member_type, doa, notes, member_id, first_name, last_name, mobile, servicegroupcode, updated_at, invoiceupto, controlac, telegram_id, is_active, telegramlinked, transferfrom, chs_id, role) 
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, IIF(title = '1','Mr.',IIF(title = '2','Ms.', IIF(title = '3','Dr.','Other')))  AS salutation, dob, IIF(gender = '1','Male',IIF(gender = '2','Female','Other')) AS gender, 

CONCAT(
        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(wing, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(wing, '-', ''), ',', ''), '/', ''))) 
            ELSE '' 
        END,

        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(bldg, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN 
                CASE 
                    WHEN LTRIM(RTRIM(wing)) <> '' THEN '-' ELSE '' 
                END +
                LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(bldg, '-', ''), ',', ''), '/', '')))
            ELSE '' 
        END,

        CASE 
            WHEN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(flatno, '-', ''), ',', ''), '/', ''))) <> '' 
            THEN 
                CASE 
                    WHEN LTRIM(RTRIM(wing)) <> '' OR LTRIM(RTRIM(bldg)) <> '' THEN '-' ELSE '' 
                END +
                LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(flatno, '-', ''), ',', ''), '/', '')))
            ELSE '' 
        END
    ) AS bldg_flat

, area, email, 'Resident' AS membertype, admitted AS doa, notes, RIGHT(memberid,4) AS member_id, LEFT(Name, CHARINDEX(' ', Name + ' ') - 1) AS first_name, LTRIM(SUBSTRING(Name, CHARINDEX(' ', Name + ' '), LEN(Name))) AS last_name, Cell1 AS mobile, servicegroupcode, updatedon AS updated_at, invoiceupto, A.mainac AS  controlac, '' AS telegram_id, IIF(ceased = '31-Mar-49' AND typeofmember != '12',1,0), IIF('' = '',0,1) AS telegramlinked, RIGHT(transferinfo,6) AS transferfrom,'J0001', 'User' AS role FROM Blazorchs..Membermst M
LEFT JOIN accountsmst A ON M.controlac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

------------StdTariff
INSERT INTO service_charges (id, service_code, service_name, amount, is_active, mainac, chs_id,  subac)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, tariffcode AS service_code, tariffname AS service_name, amount, IIF(S.isactive = 'Y','1','0') AS is_active, A.mainac AS account_code,
'J0001' AS chs_id,  A.subac
FROM blazorchs.. stdtariffmst S
LEFT JOIN accountsmst1 A ON S.mainac + S.subac = A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE tariffcode !='999997' AND chs_id = 'J0001';;

-----------Service Group
INSERT INTO service_groups (id, service_group_code, service_group_name, isactive, chs_id, service_charges_codes)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, RIGHT(s.GroupCode, 4) AS service_group_code, s.GroupName, s.isactive, 'J0001' AS chs_id,
       STRING_AGG(s1.TariffCode, ',') AS service_charges_codes
FROM blazorchs.. ServiceGroupMst s
LEFT JOIN blazorchs.. ServiceGroupMst1 s1 ON s.GroupCode = s1.GroupCode
GROUP BY s.id, s.GroupCode, s.GroupName, s.isactive;

-------------Invoices
INSERT INTO invoices (id, chs_id, invoice_no, fylabel, dated, memberid, notes, total_amount, gsttype, status, period)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,'J0001' AS chs_id, invoiceno, 
LEFT(YearInvoiceNo, 4)+'-'+Substring(yearinvoiceno,5,2), dated, RIGHT(memberid,4) AS member_id, notes, amount, COALESCE(Description,'NA') AS gsttype,
IIF(createtype ='S','System','User') Status, period
FROM blazorchs.. invoicechs I
LEFT JOIN blazorchs..GstType G ON I.gsttype = G.Type;


-----------Invoice Line
INSERT INTO invoice_details (id, chs_id, invoiceno, fylabel, service_charges_code, amount)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,'J0001' AS chs_id, RIGHT(yearinvoiceno,6),  LEFT(YearInvoiceNo, 4)+'-'+Substring(yearinvoiceno,5,2), TariffCode, amount
FROM blazorchs.. Invoicechs1;


-----------opgos
INSERT INTO opgbalances (id, mainac, subac, drcr, amount, fylabel, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac AS accountcode, RIGHT(subac,4) AS subac, drcr, amount, yearlabel AS fylabel,'J0001' AS chs_id
FROM blazorchs..opgos O
LEFT JOIN accountsmst A ON O.mainac= A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

----------opgbal
INSERT INTO opgbalances (id, mainac, subac, drcr, amount, fylabel, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, A.mainac AS accountcode, A.subac, drcr, amount, yearlabel,'J0001'
FROM blazorchs..opgbalances O
LEFT JOIN accountsmst1 A ON O.mainac + O.subac = A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';



INSERT INTO BillChs (id, billno, dated, vendor_id, fylabel, billamt, vendorbillamt, vendorbillno, vendorbilldate, tdsamount, tdscomputedon, tdscode, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, billno, dated, RIGHT(vendorid,4) AS vendor_id, LEFT(yearbillno, 4)+'-'+Substring(yearbillno,5,2), billamt, vendorbillamt, vendorbillno, vendorbilldate, 
tdsamount, computetds, COALESCE(A.mainac,'J000'),'J0001'
FROM blazorchs..BillChs O
LEFT JOIN accountsmst A ON O.tdscode= A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';

INSERT INTO BillChs1 (id, fylabel, billno, amount, mainac, subac, notes, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, LEFT(yearbillno, 4)+'-'+Substring(yearbillno,5,2), RIGHT(yearbillno,6), amount, A.mainac, A.subac, notes, 'J0001'
FROM blazorchs.. BillChs1 O
LEFT JOIN accountsmst1 A ON O.mainac + O.subac= A.oldmainac + CONCAT('00',A.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE chs_id = 'J0001';


------------Excluded one records because it causing any run error duplicate voucherno
INSERT INTO voucher (id, voucherno, dated, vouchertype, cheqno, cheqdate, drawnon, amount, bankcode, oldbankcode, recodate, fylabel, refid, chs_id) 
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, dated, IIF(vouchertype = 'P','Payment','Receipt') AS vouchertype,
CONCAT(IIF(TRIM(COALESCE(UtrNo,'')) IN ('0'),'', TRIM(COALESCE(UtrNo,''))), IIF(TRIM(COALESCE(cheqno,'')) IN ('0'),'', TRIM(COALESCE(cheqno,'')))) AS cheqno, 
cheqdate, drawnon, amount, A.mainac AS bankcode, bankcode, recodate, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 0, 'J0001' FROM blazorchs..voucher V
LEFT JOIN accountsmst A ON V.bankcode = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE A.chs_id = 'J0001' and V.id != 3335  ;


------------------------------
INSERT INTO voucher1 (id, voucherno, mainac, subac, oldmainac, drcr, amount, notes, fylabel, tdscode, tdsamount, interestamt, chs_id) 
(
    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, A1.mainac, A1.subac, V1.mainac AS oldmainac, drcr, amount, notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
    '00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
    LEFT JOIN accountsmst1 A1 ON V1.mainac + V1.subac = A1.oldmainac + CONCAT('00',A1.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND refdoc = 'GL'

    UNION ALL

    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, A1.controlac AS mainac, A1.member_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
    V1.notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
    '00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
    LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
    INNER JOIN members A1 ON A.mainac + V1.subac = A1.controlac + CONCAT('00',A1.member_id) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('SI','OA','AD')

    UNION ALL

    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, voucherno, A1.controlac AS mainac, A1.vendor_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
    V1.notes, LEFT(yearvoucherno, 4)+'-'+Substring(yearvoucherno,5,2) AS fylabel, 
    '00' AS tdscode, 0 AS tdsamount, 0 AS interestamt, 'J0001' FROM blazorchs..voucher1 V1
    LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
    INNER JOIN vendors A1 ON A.mainac + V1.subac =  A1.controlac + CONCAT('00',A1.vendor_id) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('PB','OA','AD')
)

INSERT INTO journalvoucher(id, jvno, dated, fylabel, JvType, chs_id)
SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, jvno, dated, 
LEFT(yearjvno, 4)+'-'+Substring(yearjvno,5,2) AS fylabel, IIF(JvType = 'A','Accounts','') AS JvType, 'J0001' AS chs_id FROM blazorchs..journalvoucher;


INSERT INTO journalvoucher1 (id, jvno, mainac, subac, oldmainac, drcr, amount, notes, fylabel, chs_id) 
(
    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, jvno, A1.mainac, A1.subac, V1.mainac AS oldmainac, drcr, 
    amount, notes, LEFT(yearjvno, 4)+'-'+Substring(yearjvno,5,2) AS fylabel,  'J0001' FROM blazorchs..journalvoucher1 V1
    LEFT JOIN accountsmst1 A1 ON V1.mainac + V1.subac = A1.oldmainac + CONCAT('00',A1.subac) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND refdoc = 'GL'

    UNION ALL

    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, jvno, A1.controlac AS mainac, A1.member_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
    V1.notes, LEFT(yearjvno, 4)+'-'+Substring(yearjvno,5,2) AS fylabel, 'J0001' FROM blazorchs..journalvoucher1 V1
    LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
    INNER JOIN members A1 ON A.mainac + V1.subac = A1.controlac + CONCAT('00',A1.member_id) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('SI','OA','AD')

    UNION ALL

    SELECT LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, jvno, A1.controlac AS mainac, A1.vendor_id AS subac, V1.mainac AS oldmainac, drcr, amount, 
    V1.notes, LEFT(yearjvno, 4)+'-'+Substring(yearjvno,5,2) AS fylabel, 'J0001' FROM blazorchs..journalvoucher1 V1
    LEFT JOIN accountsmst A ON V1.mainac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
    INNER JOIN vendors A1 ON A.mainac + V1.subac =  A1.controlac + CONCAT('00',A1.vendor_id) COLLATE SQL_Latin1_General_CP1_CI_AS
    WHERE A1.chs_id = 'J0001' AND V1.refdoc IN('PB','OA','AD')
)


----sysparameters 
INSERT INTO sysparameters (sysname, name, isactive, dated, chs_id)
SELECT S.sysname, S.name, S.isactive, S.dated, 'J0001'
FROM blazorchs..sysparameters S
WHERE sysname IN ('AIInsights', 'SendEmailCopy', 'InterestInvoicesGeneratedUpto')

-- 1. Find the current maximum member_id as an integer for the tenant
INSERT INTO gst_state_mst (id, statecode, statename, isunionterritory) 
SELECT  LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id, statecode, statename, IIF(isut = 'Y', 'Yes', 'No') AS isunionterritory
FROM blazorchs..gststatemst
WHERE isactive = 'Y';

INSERT INTO societies (
    id, chs_id, chs_name, regn_no, sign_type_name, signatory_name,
    pan_no, gst_in, home_state_code, add_line1, add_line2, 
    pin_code, email, contact_no, gstin_type, shares_per_member, 
    invoice_cycle, created_at
)
SELECT 
    LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,
    'J0001' AS chs_id,
    RTRIM(LTRIM(E.entityname)) AS chs_name,
    RTRIM(LTRIM(E.regnno)) AS regn_no,
    RTRIM(LTRIM(ST.typename)) AS sign_type_name,
    RTRIM(LTRIM(E.signatoryname)) AS signatory_name,
    RTRIM(LTRIM(E.panno)) AS pan_no,
    RTRIM(LTRIM(E.gstin)) AS gst_in,
    RTRIM(LTRIM(E.homestatecode)) AS home_state_code,
    RTRIM(LTRIM(E.addline1)) AS add_line1,
    RTRIM(LTRIM(E.addline2)) AS add_line2,
    RTRIM(LTRIM(E.pincode)) AS pin_code,
    RTRIM(LTRIM(E.email)) AS email,
    RTRIM(LTRIM(E.contactno)) AS contact_no,
    RTRIM(LTRIM(E.gstintype)) AS gstin_type,
    E.SharesPerMember AS shares_per_member,
    RTRIM(LTRIM(E.InvoiceCycle)) AS invoice_cycle,
    GETDATE() AS created_at
FROM blazorchs..entitymst E
LEFT JOIN blazorchs..SignatoryType ST ON E.signtypecode = ST.typecode

------------------------------------------------------------------------------------------
INSERT INTO CHSLite..vendors (
    id, vendor_id, first_name, last_name, org_name, address, city, 
    pincode, mobile, pan_no, tdscode, controlac, created_at, 
    updated_at, chs_id, email, notes
)
SELECT 
    LOWER(SUBSTRING(REPLACE(NEWID(), '-', ''), 1, 13)) AS id,
    RIGHT('0000' + RTRIM(LTRIM(V.vendorid)), 4) AS vendor_id,
    
    -- Split name: first_name is everything before first space
    CASE 
        WHEN CHARINDEX(' ', RTRIM(LTRIM(V.vendorname))) > 0 
        THEN SUBSTRING(RTRIM(LTRIM(V.vendorname)), 1, CHARINDEX(' ', RTRIM(LTRIM(V.vendorname))) - 1)
        ELSE RTRIM(LTRIM(V.vendorname))
    END AS first_name,
    
    -- Split name: last_name is everything after first space
    CASE 
        WHEN CHARINDEX(' ', RTRIM(LTRIM(V.vendorname))) > 0 
        THEN SUBSTRING(RTRIM(LTRIM(V.vendorname)), CHARINDEX(' ', RTRIM(LTRIM(V.vendorname))) + 1, LEN(V.vendorname))
        ELSE NULL
    END AS last_name,
    
    RTRIM(LTRIM(V.vendorname)) AS org_name,
    
    -- Address concatenated from addline1 and addline2
    RTRIM(LTRIM(COALESCE(V1.addline1, ''))) + 
    CASE 
        WHEN V1.addline1 IS NOT NULL AND V1.addline2 IS NOT NULL AND RTRIM(LTRIM(V1.addline2)) <> '' 
        THEN ', ' 
        ELSE '' 
    END + 
    RTRIM(LTRIM(COALESCE(V1.addline2, ''))) AS address,
    
    NULLIF(RTRIM(LTRIM(V1.city)), '') AS city,
    NULLIF(RTRIM(LTRIM(V1.pincode)), '') AS pincode,
    NULLIF(RTRIM(LTRIM(V1.cell)), '') AS mobile,
    NULLIF(RTRIM(LTRIM(V.panno)), '') AS pan_no,
    NULLIF(SUBSTRING(RTRIM(LTRIM(V.tdscode)), 1, 4), '') AS tdscode,
    A.mainac AS controlac,
    GETDATE() AS created_at,
    GETDATE() AS updated_at,
    'J0001' AS chs_id,
    NULLIF(RTRIM(LTRIM(V1.email)), '') AS email,
    NULLIF(RTRIM(LTRIM(V.notes)), '') AS notes
FROM blazorchs..VendorMst V
-- Join the first branch (branchid '001' or lowest branchid)
LEFT JOIN blazorchs..VendorMst1 V1 ON V.vendorid = V1.vendorid AND V1.branchid = (
    SELECT MIN(branchid) 
    FROM blazorchs..VendorMst1 
    WHERE vendorid = V.vendorid
)
LEFT JOIN CHSLite..accountsmst A ON V.controlac = A.oldmainac COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE NOT EXISTS (
    SELECT 1 
    FROM CHSLite..vendors TargetVendors
    WHERE TargetVendors.chs_id = 'J0001'
      AND TargetVendors.vendor_id = RIGHT('0000' + RTRIM(LTRIM(V.vendorid)), 4) COLLATE DATABASE_DEFAULT
);


-- =================================================================================
-- CHS Lite ERP - Joint Member Data Migration Script (Collation Safe)
-- Migrates records from Legacy 'jointmemberinfo' to CHS Lite 'joint_members'
-- =================================================================================

-- Step 1: Set Target CHS ID (Replace 'CHS01' with your active Society CHS ID)
DECLARE @TargetChsId NVARCHAR(5) = 'CHS01'; 

BEGIN TRANSACTION;

BEGIN TRY
    PRINT 'Starting Joint Member data migration for CHS ID: ' + @TargetChsId;

    -- Step 2: Insert Joint Members from legacy jointmemberinfo
    INSERT INTO dbo.joint_members (
        id,
        chs_id,
        member_id,
        jm_name,
        jm_address,
        created_at,
        updated_at
    )
    SELECT 
        -- Generate unique string ID for CHS Lite Prisma model
        CAST(NEWID() AS NVARCHAR(100)) AS id,
        @TargetChsId AS chs_id,
        RIGHT('0000' + LTRIM(RTRIM(src.memberid)), 4) AS member_id, -- Ensures 4-digit formatted member ID
        UPPER(LTRIM(RTRIM(src.jmname))) AS jm_name,
        LTRIM(RTRIM(src.jmaddress)) AS jm_address,
        GETDATE() AS created_at,
        GETDATE() AS updated_at
    FROM blazorchs..jointmemberinfo src
    WHERE NOT EXISTS (
        -- Idempotency check with explicit COLLATE DATABASE_DEFAULT
        SELECT 1 
        FROM dbo.joint_members tgt
        WHERE (tgt.chs_id COLLATE DATABASE_DEFAULT) = (@TargetChsId COLLATE DATABASE_DEFAULT)
          AND (tgt.member_id COLLATE DATABASE_DEFAULT) = (RIGHT('0000' + LTRIM(RTRIM(src.memberid)), 4) COLLATE DATABASE_DEFAULT)
          AND (UPPER(tgt.jm_name) COLLATE DATABASE_DEFAULT) = (UPPER(LTRIM(RTRIM(src.jmname))) COLLATE DATABASE_DEFAULT)
    );

    DECLARE @MigratedCount INT = @@ROWCOUNT;

    COMMIT TRANSACTION;

    PRINT '=======================================================';
    PRINT 'SUCCESS: Migrated ' + CAST(@MigratedCount AS VARCHAR(10)) + ' Joint Member record(s).';
    PRINT '=======================================================';

END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    PRINT 'ERROR: Migration failed!';
    PRINT ERROR_MESSAGE();
END CATCH;