Definició de Claus Primàries i Foranes

-- Taula principal d'usuaris del sistema
CREATE TABLE users (
    -- CLAU PRIMÀRIA: Identificador únic i immutable
    id INT PRIMARY KEY AUTO_INCREMENT,

    -- CAMP ÚNIC: Per a autenticació sense duplicats
    username VARCHAR(50) NOT NULL UNIQUE,

    -- CAMP ÚNIC: Comunicació i recuperació de contrasenya
    email VARCHAR(100) NOT NULL UNIQUE,

    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),

    -- RESTRICCIÓ DOMINI: Valors vàlids predefinits
    role ENUM('assistant', 'technician', 'admin') NOT NULL DEFAULT 'assistant',

    -- CAMP ÚNIC OPCIONAL: Per a assistents registrats
    assistant_number VARCHAR(20) UNIQUE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- ÍNDEX PER A CONSULTES FRQÜENTS
CREATE INDEX idx_users_email_username ON users(email, username);
CREATE INDEX idx_users_role_active ON users(role, is_active);

-- Taula central de tickets d'assistència
CREATE TABLE tickets (
    -- CLAU PRIMÀRIA PRINCIPAL
    id INT PRIMARY KEY AUTO_INCREMENT,

    -- CLAU ALTERNATIVA ÚNICA: Per a referència pública
    ticket_number VARCHAR(20) NOT NULL UNIQUE,

    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    category ENUM('hardware', 'software', 'network', 'peripheral', 'other') NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',

    -- CLAU FORANA OBLIGATÒRIA: Relació amb usuari creador
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    -- CLAU FORANA OPCIONAL: Tècnic assignat (pot ser NULL inicialment)
    technician_id INT,
    FOREIGN KEY (technician_id) REFERENCES technicians(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE,

    -- CLAU FORANA OBLIGATÒRIA: Zona física de l'incidència
    zone_id INT NOT NULL,
    FOREIGN KEY (zone_id) REFERENCES zones(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    closed_at TIMESTAMP,
    estimated_time INT,
    resolution_notes TEXT
);

-- ÍNDEXS PER A CONSULTES FRQÜENTS
CREATE INDEX idx_tickets_status_priority ON tickets(status, priority);
CREATE INDEX idx_tickets_user_created ON tickets(user_id, created_at);
CREATE INDEX idx_tickets_technician_status ON tickets(technician_id, status);

-- Taula d'informació específica de tècnics
CREATE TABLE technicians (
    -- CLAU PRIMÀRIA INTERNA
    id INT PRIMARY KEY AUTO_INCREMENT,

    -- CLAU FORANA ÚNICA: Relació 1:1 amb users
    user_id INT UNIQUE NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    specialization ENUM('hardware', 'software', 'network', 'all') DEFAULT 'all',
    availability BOOLEAN DEFAULT TRUE,
    max_tickets INT DEFAULT 5,
    current_tickets INT DEFAULT 0,
    rating DECIMAL(3,2) DEFAULT 0.00,
    experience_level ENUM('junior', 'mid', 'senior', 'expert') DEFAULT 'junior'
);

-- ÍNDEX PER A CONSULTES D'ASSIGNACIÓ
CREATE INDEX idx_technicians_availability_specialization ON technicians(availability, specialization);
CREATE INDEX idx_technicians_rating_experience ON technicians(rating, experience_level);

-- Taula de comentaris de seguiment de tickets
CREATE TABLE ticket_comments (
    -- CLAU PRIMÀRIA DE COMENTARI
    id INT PRIMARY KEY AUTO_INCREMENT,

    -- CLAU FORANA OBLIGATÒRIA: Ticket al qual pertany el comentari
    ticket_id INT NOT NULL,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,

    -- CLAU FORANA OBLIGATÒRIA: Usuari que va crear el comentari
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    content TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ÍNDEX PER A CONSULTES CRONOLÒGIQUES
CREATE INDEX idx_comments_ticket_created ON ticket_comments(ticket_id, created_at);
CREATE INDEX idx_comments_user_created ON ticket_comments(user_id, created_at);