-- =============================================
-- MyTab CRM - Schema Database MySQL
-- =============================================

-- Creazione database (se non esiste)
CREATE DATABASE IF NOT EXISTS mytab_crm 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE mytab_crm;

-- =============================================
-- Tabella Licenze
-- =============================================
CREATE TABLE IF NOT EXISTS licenze (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codice VARCHAR(50) NOT NULL UNIQUE,
    mac_address VARCHAR(17) DEFAULT NULL,
    ragione_sociale VARCHAR(255) DEFAULT NULL,
    data_creazione DATETIME DEFAULT CURRENT_TIMESTAMP,
    data_attivazione DATETIME DEFAULT NULL,
    data_scadenza DATETIME DEFAULT NULL,
    numero_client INT DEFAULT 1,
    attiva BOOLEAN DEFAULT TRUE,
    blocked BOOLEAN DEFAULT FALSE,
    software_version VARCHAR(20) DEFAULT NULL,
    note TEXT DEFAULT NULL,
    
    -- Funzionalità abilitate
    funz_betpoint BOOLEAN DEFAULT FALSE,
    funz_distributore BOOLEAN DEFAULT FALSE,
    funz_kiosk BOOLEAN DEFAULT FALSE,
    funz_robot BOOLEAN DEFAULT FALSE,
    funz_ecommerce BOOLEAN DEFAULT FALSE,
    
    -- Audit
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_codice (codice),
    INDEX idx_mac_address (mac_address),
    INDEX idx_scadenza (data_scadenza),
    INDEX idx_attiva (attiva)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Aziende
-- =============================================
CREATE TABLE IF NOT EXISTS aziende (
    id INT AUTO_INCREMENT PRIMARY KEY,
    licenza_id INT NOT NULL,
    ragione_sociale VARCHAR(255) NOT NULL,
    partita_iva VARCHAR(20) DEFAULT NULL,
    codice_fiscale VARCHAR(20) DEFAULT NULL,
    indirizzo TEXT DEFAULT NULL,
    citta VARCHAR(100) DEFAULT NULL,
    provincia VARCHAR(5) DEFAULT NULL,
    cap VARCHAR(10) DEFAULT NULL,
    telefono VARCHAR(20) DEFAULT NULL,
    email VARCHAR(100) DEFAULT NULL,
    pec VARCHAR(100) DEFAULT NULL,
    
    -- Configurazioni specifiche
    giorno_levata INT DEFAULT 1,
    stampante_80 VARCHAR(100) DEFAULT NULL,
    stampante_a4 VARCHAR(100) DEFAULT NULL,
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (licenza_id) REFERENCES licenze(id) ON DELETE CASCADE,
    INDEX idx_licenza (licenza_id),
    INDEX idx_ragione_sociale (ragione_sociale)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Catalogo Prodotti
-- =============================================
CREATE TABLE IF NOT EXISTS catalogo_prodotti (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codice_prodotto VARCHAR(50) NOT NULL,
    descrizione VARCHAR(255) NOT NULL,
    categoria VARCHAR(100) DEFAULT NULL,
    prezzo_acquisto DECIMAL(10,2) DEFAULT NULL,
    prezzo_vendita DECIMAL(10,2) DEFAULT NULL,
    iva_percentuale DECIMAL(5,2) DEFAULT 22.00,
    barcode VARCHAR(50) DEFAULT NULL,
    fornitore VARCHAR(100) DEFAULT NULL,
    attivo BOOLEAN DEFAULT TRUE,
    versione INT DEFAULT 1,
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_codice_versione (codice_prodotto, versione),
    INDEX idx_categoria (categoria),
    INDEX idx_barcode (barcode),
    INDEX idx_fornitore (fornitore),
    INDEX idx_attivo (attivo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Categorie E-commerce
-- =============================================
CREATE TABLE IF NOT EXISTS categorie_ecommerce (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    descrizione TEXT DEFAULT NULL,
    parent_id INT DEFAULT NULL,
    ordinamento INT DEFAULT 0,
    attiva BOOLEAN DEFAULT TRUE,
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (parent_id) REFERENCES categorie_ecommerce(id) ON DELETE SET NULL,
    INDEX idx_parent (parent_id),
    INDEX idx_ordinamento (ordinamento),
    INDEX idx_attiva (attiva)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Messaggi Broadcast
-- =============================================
CREATE TABLE IF NOT EXISTS messaggi_broadcast (
    id INT AUTO_INCREMENT PRIMARY KEY,
    titolo VARCHAR(255) NOT NULL,
    messaggio TEXT NOT NULL,
    tipo ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    data_inizio DATETIME DEFAULT CURRENT_TIMESTAMP,
    data_fine DATETIME DEFAULT NULL,
    attivo BOOLEAN DEFAULT TRUE,
    priorita INT DEFAULT 0,
    
    -- Filtri di targeting
    target_licenze TEXT DEFAULT NULL,  -- JSON array di codici licenza
    target_versioni TEXT DEFAULT NULL, -- JSON array di versioni software
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_attivo (attivo),
    INDEX idx_data_inizio (data_inizio),
    INDEX idx_data_fine (data_fine),
    INDEX idx_priorita (priorita)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Log Accessi
-- =============================================
CREATE TABLE IF NOT EXISTS log_accessi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    licenza_codice VARCHAR(50) DEFAULT NULL,
    mac_address VARCHAR(17) DEFAULT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT DEFAULT NULL,
    operazione VARCHAR(100) NOT NULL,
    dettagli TEXT DEFAULT NULL,
    esito ENUM('success', 'error', 'warning') DEFAULT 'success',
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_licenza (licenza_codice),
    INDEX idx_mac (mac_address),
    INDEX idx_ip (ip_address),
    INDEX idx_operazione (operazione),
    INDEX idx_data (created_at),
    INDEX idx_esito (esito)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Tabella Opzioni Sistema
-- =============================================
CREATE TABLE IF NOT EXISTS opzioni_sistema (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chiave VARCHAR(100) NOT NULL UNIQUE,
    valore TEXT DEFAULT NULL,
    descrizione TEXT DEFAULT NULL,
    tipo ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_chiave (chiave)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- Dati di esempio/iniziali
-- =============================================

-- Inserimento licenza di test
INSERT IGNORE INTO licenze (codice, ragione_sociale, data_scadenza, numero_client, funz_betpoint, funz_kiosk) VALUES
('TEST-1234-5678-9ABC', 'Tabaccheria di Test', DATE_ADD(NOW(), INTERVAL 1 YEAR), 1, TRUE, TRUE);

-- Inserimento azienda di test
INSERT IGNORE INTO aziende (licenza_id, ragione_sociale, indirizzo, citta) VALUES
(1, 'Tabaccheria di Test', 'Via Roma 123', 'Milano');

-- Categorie e-commerce di base
INSERT IGNORE INTO categorie_ecommerce (nome, descrizione, ordinamento) VALUES
('Tabacchi', 'Prodotti del tabacco', 1),
('Alimentari', 'Prodotti alimentari', 2),
('Bevande', 'Bevande varie', 3),
('Cartoleria', 'Articoli di cartoleria', 4),
('Ricariche', 'Ricariche telefoniche', 5);

-- Prodotti di esempio
INSERT IGNORE INTO catalogo_prodotti (codice_prodotto, descrizione, categoria, prezzo_vendita, barcode) VALUES
('CIG001', 'Marlboro Rosse', 'Tabacchi', 5.20, '1234567890123'),
('BEV001', 'Coca Cola 33cl', 'Bevande', 1.50, '2345678901234'),
('ALI001', 'Pane Integrale', 'Alimentari', 2.80, '3456789012345');

-- Messaggio di benvenuto
INSERT IGNORE INTO messaggi_broadcast (titolo, messaggio, tipo) VALUES
('Sistema CRM Attivo', 'Il sistema CRM MyTab è operativo e pronto all\'uso.', 'success');

-- Opzioni sistema di base
INSERT IGNORE INTO opzioni_sistema (chiave, valore, descrizione, tipo) VALUES
('sistema_versione', '1.0.0', 'Versione del sistema CRM', 'string'),
('manutenzione_attiva', 'false', 'Modalità manutenzione', 'boolean'),
('max_licenze_attive', '1000', 'Numero massimo di licenze attive', 'number'),
('backup_automatico', 'true', 'Backup automatico abilitato', 'boolean');

-- =============================================
-- Viste per report
-- =============================================

-- Vista licenze attive
CREATE OR REPLACE VIEW v_licenze_attive AS
SELECT 
    l.id,
    l.codice,
    l.ragione_sociale,
    l.data_attivazione,
    l.data_scadenza,
    l.numero_client,
    a.citta,
    a.provincia,
    CASE 
        WHEN l.data_scadenza < NOW() THEN 'SCADUTA'
        WHEN l.blocked = TRUE THEN 'BLOCCATA'
        WHEN l.attiva = FALSE THEN 'DISATTIVA'
        ELSE 'ATTIVA'
    END as stato
FROM licenze l
LEFT JOIN aziende a ON l.id = a.licenza_id
WHERE l.attiva = TRUE;

-- Vista statistiche accessi
CREATE OR REPLACE VIEW v_statistiche_accessi AS
SELECT 
    DATE(created_at) as data,
    COUNT(*) as totale_accessi,
    COUNT(DISTINCT licenza_codice) as licenze_uniche,
    COUNT(DISTINCT ip_address) as ip_unici,
    SUM(CASE WHEN esito = 'success' THEN 1 ELSE 0 END) as accessi_riusciti,
    SUM(CASE WHEN esito = 'error' THEN 1 ELSE 0 END) as accessi_falliti
FROM log_accessi
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY data DESC;

-- =============================================
-- Procedure stored per manutenzione
-- =============================================

DELIMITER //

-- Pulizia log vecchi
CREATE PROCEDURE IF NOT EXISTS PulisciLogVecchi(IN giorni_retention INT)
BEGIN
    DELETE FROM log_accessi 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL giorni_retention DAY);
    
    SELECT ROW_COUNT() as records_eliminati;
END //

-- Backup tabelle principali
CREATE PROCEDURE IF NOT EXISTS BackupTabelle()
BEGIN
    DECLARE backup_timestamp VARCHAR(20) DEFAULT DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s');
    
    SET @sql = CONCAT('CREATE TABLE backup_licenze_', backup_timestamp, ' AS SELECT * FROM licenze');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SET @sql = CONCAT('CREATE TABLE backup_aziende_', backup_timestamp, ' AS SELECT * FROM aziende');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    SELECT CONCAT('Backup completato: ', backup_timestamp) as risultato;
END //

DELIMITER ;

-- =============================================
-- Trigger per audit automatico
-- =============================================

-- Trigger per log modifiche licenze
DELIMITER //
CREATE TRIGGER IF NOT EXISTS tr_licenze_audit
AFTER UPDATE ON licenze
FOR EACH ROW
BEGIN
    INSERT INTO log_accessi (licenza_codice, operazione, dettagli, ip_address)
    VALUES (NEW.codice, 'LICENZA_MODIFICATA', 
            CONCAT('Campi modificati: ', 
                   CASE WHEN OLD.attiva != NEW.attiva THEN 'attiva ' ELSE '' END,
                   CASE WHEN OLD.blocked != NEW.blocked THEN 'blocked ' ELSE '' END,
                   CASE WHEN OLD.data_scadenza != NEW.data_scadenza THEN 'scadenza ' ELSE '' END
            ), 
            COALESCE(CONNECTION_ID(), '0'));
END //
DELIMITER ;

-- =============================================
-- Indici per performance
-- =============================================

-- Indici composti per query frequenti
CREATE INDEX idx_licenze_stato ON licenze (attiva, blocked, data_scadenza);
CREATE INDEX idx_log_data_operazione ON log_accessi (created_at, operazione);
CREATE INDEX idx_prodotti_categoria_attivo ON catalogo_prodotti (categoria, attivo);

-- =============================================
-- Commenti finali
-- =============================================

-- Schema completato
-- Versione: 1.0.0
-- Data creazione: 2025-01-08
-- Compatibile con: MySQL 5.7+, MariaDB 10.2+
