-- Rulează acest SQL dacă nu ai rulat deja patch-ul pentru roluri / portal client.
-- Dacă l-ai rulat deja și primești eroare de obiect existent, sari peste instrucțiunea respectivă.

ALTER TABLE users
    MODIFY COLUMN role ENUM('admin','manager','user') NOT NULL DEFAULT 'user';

ALTER TABLE users
    ADD COLUMN linked_client_id INT UNSIGNED NULL AFTER role,
    ADD KEY idx_users_linked_client (linked_client_id);

ALTER TABLE users
    ADD CONSTRAINT fk_users_linked_client
    FOREIGN KEY (linked_client_id) REFERENCES clients(id) ON DELETE SET NULL;

ALTER TABLE vehicle_alerts
    MODIFY COLUMN alert_type VARCHAR(100) NOT NULL;

CREATE TABLE client_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_id INT UNSIGNED NOT NULL,
    vehicle_id INT UNSIGNED NULL,
    owner_id INT UNSIGNED NOT NULL,
    uploaded_by INT UNSIGNED NULL,
    document_type VARCHAR(50) NOT NULL DEFAULT 'other',
    title VARCHAR(190) NULL,
    notes MEDIUMTEXT NULL,
    file_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_client_documents_client (client_id),
    KEY idx_client_documents_vehicle (vehicle_id),
    KEY idx_client_documents_owner (owner_id),
    CONSTRAINT fk_client_documents_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_documents_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    CONSTRAINT fk_client_documents_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_client_documents_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;