SET NAMES utf8mb4;

ALTER TABLE md_clients
    ADD COLUMN IF NOT EXISTS cnp VARCHAR(13) NULL AFTER name,
    ADD COLUMN IF NOT EXISTS cui VARCHAR(30) NULL AFTER cnp;

CREATE INDEX IF NOT EXISTS idx_md_clients_cnp
    ON md_clients(cnp);

CREATE INDEX IF NOT EXISTS idx_md_clients_cui
    ON md_clients(cui);

UPDATE md_clients
SET
    cnp = CASE
        WHEN type = 'individual'
             AND CHAR_LENGTH(
                 REPLACE(REPLACE(REPLACE(tax_id, ' ', ''), '-', ''), '.', '')
             ) = 13
        THEN REPLACE(REPLACE(REPLACE(tax_id, ' ', ''), '-', ''), '.', '')
        ELSE cnp
    END,
    cui = CASE
        WHEN type = 'company'
        THEN UPPER(REPLACE(REPLACE(tax_id, ' ', ''), '-', ''))
        ELSE cui
    END
WHERE tax_id IS NOT NULL
  AND tax_id <> '';