-- Run on MCH_API PostgreSQL database before deploying sequenceno changes.

ALTER TABLE tblsales ADD COLUMN IF NOT EXISTS sequenceno INTEGER DEFAULT 0;

ALTER TABLE tblsalesitemdetails ADD COLUMN IF NOT EXISTS sequenceno INTEGER DEFAULT 0;

UPDATE tblsales SET sequenceno = voucherno ;
UPDATE tblsalesitemdetails SET sequenceno = voucherno;

-- voucherno should store formatted bill numbers like 220626_001
ALTER TABLE tblsales ALTER COLUMN voucherno TYPE TEXT USING voucherno::text;
ALTER TABLE tblsalesitemdetails ALTER COLUMN voucherno TYPE TEXT USING voucherno::text;

ALTER TABLE IF EXISTS public.tblbranchmaster
    ADD COLUMN IF NOT EXISTS is_bill_format text;
