-- Database per Gestione Territori
-- Creato il 6 dicembre 2025

CREATE DATABASE IF NOT EXISTS territoryassigner CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE territoryassigner;

-- Tabella utenti
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    is_admin TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabella territori
CREATE TABLE IF NOT EXISTS territories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(20) NOT NULL,
    zona VARCHAR(100) NOT NULL,
    tipologia VARCHAR(50) NOT NULL,
    image_path VARCHAR(255),
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_territory (numero, zona)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabella assegnazioni
CREATE TABLE IF NOT EXISTS assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    territory_id INT NOT NULL,
    assigned_to VARCHAR(100) NOT NULL,
    assigned_date DATE NOT NULL,
    returned_date DATE NULL,
    link_token VARCHAR(64) UNIQUE,
    link_expires_at DATETIME NULL,
    is_priority TINYINT(1) DEFAULT 0,
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (territory_id) REFERENCES territories(id) ON DELETE CASCADE,
    INDEX idx_territory (territory_id),
    INDEX idx_token (link_token),
    INDEX idx_dates (assigned_date, returned_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabella configurazione
CREATE TABLE IF NOT EXISTS config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(50) NOT NULL UNIQUE,
    config_value VARCHAR(255) NOT NULL,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserimento configurazioni di default
INSERT INTO config (config_key, config_value, description) VALUES
('link_expiry_days', '7', 'Giorni di validità dei link temporanei'),
('warning_days_normal', '90', 'Giorni dopo i quali un territorio è da assegnare'),
('warning_days_priority', '180', 'Giorni dopo i quali un territorio è prioritario'),
('warning_days_return', '120', 'Giorni dopo i quali un territorio è da riconsegnare');

-- Utente amministratore di default (password: admin123)
-- IMPORTANTE: Cambiare la password dopo la prima installazione!
INSERT INTO users (username, password, email, is_admin) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com', 1);

-- Vista per territori attualmente assegnati
CREATE OR REPLACE VIEW current_assignments AS
SELECT 
    t.id as territory_id,
    t.numero,
    t.zona,
    t.tipologia,
    a.id as assignment_id,
    a.assigned_to,
    a.assigned_date,
    a.is_priority,
    DATEDIFF(CURDATE(), a.assigned_date) as days_assigned
FROM territories t
LEFT JOIN assignments a ON t.id = a.territory_id
WHERE a.returned_date IS NULL
ORDER BY a.assigned_date ASC;

-- Vista per territori disponibili (in reparto)
CREATE OR REPLACE VIEW available_territories AS
SELECT 
    t.id as territory_id,
    t.numero,
    t.zona,
    t.tipologia,
    t.image_path,
    MAX(a.returned_date) as last_returned_date,
    DATEDIFF(CURDATE(), MAX(a.returned_date)) as days_in_depot
FROM territories t
LEFT JOIN assignments a ON t.id = a.territory_id
WHERE t.id NOT IN (
    SELECT territory_id FROM assignments WHERE returned_date IS NULL
)
GROUP BY t.id, t.numero, t.zona, t.tipologia, t.image_path
ORDER BY last_returned_date ASC NULLS FIRST;

-- Tabella log attività
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    action_description TEXT NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_action_type (action_type),
    INDEX idx_created_at (created_at),
    INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;