-- Add project variant and reset codes from project names

ALTER TABLE qt_projects
    ADD COLUMN variant VARCHAR(100) NOT NULL DEFAULT '' AFTER code;

-- Drop legacy unique index on code to allow duplicate codes
DROP INDEX IF EXISTS idx_qt_projects_code ON qt_projects;
DROP INDEX IF EXISTS idx_qt_projects_code_variant ON qt_projects;

-- Reset code from name and clear variant
UPDATE qt_projects
SET code = LEFT(TRIM(COALESCE(name, '')), 100),
    variant = '';

-- De-duplicate by assigning variant numbers: -2, -3...
UPDATE qt_projects p
JOIN (
    SELECT p1.id,
           p1.code,
           (SELECT COUNT(*)
            FROM qt_projects p2
            WHERE p2.code = p1.code AND p2.id <= p1.id) AS rn
    FROM qt_projects p1
) r ON r.id = p.id
SET p.code = r.code,
    p.variant = CASE WHEN r.rn = 1 THEN '' ELSE CONCAT('-', r.rn) END;

CREATE UNIQUE INDEX idx_qt_projects_code_variant ON qt_projects(code, variant);