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,
    ADD COLUMN IF NOT EXISTS account_code VARCHAR(50) NULL AFTER tax_id,
    ADD COLUMN IF NOT EXISTS bank_account VARCHAR(100) NULL AFTER country,
    ADD COLUMN IF NOT EXISTS bank_name VARCHAR(190) NULL AFTER bank_account,
    ADD COLUMN IF NOT EXISTS client_group VARCHAR(100) NULL AFTER bank_name,
    ADD COLUMN IF NOT EXISTS delegate_name VARCHAR(190) NULL AFTER trade_registry,
    ADD COLUMN IF NOT EXISTS id_series VARCHAR(20) NULL AFTER delegate_name,
    ADD COLUMN IF NOT EXISTS id_number VARCHAR(30) NULL AFTER id_series,
    ADD COLUMN IF NOT EXISTS id_issued_by VARCHAR(190) NULL AFTER id_number,
    ADD COLUMN IF NOT EXISTS vehicle_info VARCHAR(255) NULL AFTER id_issued_by,
    ADD COLUMN IF NOT EXISTS agent_code VARCHAR(100) NULL AFTER vehicle_info,
    ADD COLUMN IF NOT EXISTS agent_name VARCHAR(190) NULL AFTER agent_code,
    ADD COLUMN IF NOT EXISTS discount_percent DECIMAL(7,2) NOT NULL DEFAULT 0 AFTER agent_name,
    ADD COLUMN IF NOT EXISTS branch_code VARCHAR(100) NULL AFTER payment_term_days,
    ADD COLUMN IF NOT EXISTS additional_info TEXT NULL AFTER branch_code,
    ADD COLUMN IF NOT EXISTS third_party_type VARCHAR(100) NULL AFTER additional_info,
    ADD COLUMN IF NOT EXISTS is_vat_payer TINYINT(1) NOT NULL DEFAULT 0 AFTER third_party_type,
    ADD COLUMN IF NOT EXISTS is_blocked TINYINT(1) NOT NULL DEFAULT 0 AFTER is_vat_payer,
    ADD COLUMN IF NOT EXISTS vat_validation_date DATE NULL AFTER is_blocked,
    ADD COLUMN IF NOT EXISTS card_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER vat_validation_date,
    ADD COLUMN IF NOT EXISTS vat_suspension_date DATE NULL AFTER card_enabled,
    ADD COLUMN IF NOT EXISTS postal_code VARCHAR(20) NULL AFTER credit_limit,
    ADD COLUMN IF NOT EXISTS is_efactura TINYINT(1) NOT NULL DEFAULT 0 AFTER postal_code,
    ADD COLUMN IF NOT EXISTS efactura_id VARCHAR(100) NULL AFTER is_efactura,
    ADD COLUMN IF NOT EXISTS saft_code VARCHAR(50) NULL AFTER efactura_id;

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_account_code ON md_clients(account_code);
CREATE INDEX IF NOT EXISTS idx_md_clients_saft_code ON md_clients(saft_code);

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 după reimport:
-- SELECT COUNT(*) total FROM md_clients;
-- SELECT COUNT(*) cu_cnp FROM md_clients WHERE COALESCE(cnp,'') <> '';
-- SELECT COUNT(*) cu_cui FROM md_clients WHERE COALESCE(cui,'') <> '';