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);

CREATE INDEX IF NOT EXISTS idx_md_clients_code
    ON md_clients(code);

CREATE INDEX IF NOT EXISTS idx_md_clients_name
    ON md_clients(name);

UPDATE md_clients
SET
    cnp = CASE
        WHEN COALESCE(cnp, '') = ''
             AND CHAR_LENGTH(
                 REGEXP_REPLACE(
                     COALESCE(tax_id, ''),
                     '[^0-9]',
                     ''
                 )
             ) = 13
        THEN REGEXP_REPLACE(
            COALESCE(tax_id, ''),
            '[^0-9]',
            ''
        )
        ELSE cnp
    END,
    cui = CASE
        WHEN COALESCE(cui, '') = ''
             AND COALESCE(tax_id, '') <> ''
             AND CHAR_LENGTH(
                 REGEXP_REPLACE(
                     COALESCE(tax_id, ''),
                     '[^0-9]',
                     ''
                 )
             ) <> 13
        THEN UPPER(
            REGEXP_REPLACE(
                COALESCE(tax_id, ''),
                '[^A-Za-z0-9]',
                ''
            )
        )
        ELSE cui
    END,
    type = CASE
        WHEN CHAR_LENGTH(
            REGEXP_REPLACE(
                COALESCE(tax_id, ''),
                '[^0-9]',
                ''
            )
        ) = 13
        THEN 'individual'
        WHEN COALESCE(tax_id, '') <> ''
        THEN 'company'
        ELSE type
    END
WHERE COALESCE(tax_id, '') <> '';

-- Verificare rapidă după import:
-- SELECT COUNT(*) AS total_clienti FROM md_clients;
-- SELECT COUNT(*) AS cu_cnp FROM md_clients WHERE COALESCE(cnp, '') <> '';
-- SELECT COUNT(*) AS cu_cui FROM md_clients WHERE COALESCE(cui, '') <> '';
-- SELECT COUNT(*) AS fara_identificator
-- FROM md_clients
-- WHERE COALESCE(cnp, '') = ''
--   AND COALESCE(cui, '') = ''
--   AND COALESCE(tax_id, '') = '';