-- ============================================================
-- AppInMarittime — Schema MySQL
-- Esegui: mysql -u appuser -p appinmarittime < schema.sql
-- ============================================================


-- USERS
CREATE TABLE IF NOT EXISTS users (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    email            VARCHAR(255) UNIQUE NOT NULL,
    password_hash    VARCHAR(255) NOT NULL,
    full_name        VARCHAR(255),
    push_subscription TEXT NULL,         -- JSON Web Push subscription
    is_active        TINYINT(1) DEFAULT 1,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ADMINS
CREATE TABLE IF NOT EXISTS admins (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    email         VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name     VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Password: apppassword (cambiala subito!)
INSERT IGNORE INTO admins (email, password_hash, full_name)
VALUES (
    'admin@entracquebnb.it',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2uheWG/igi.',
    'Amministratore'
);

-- EVENTS
CREATE TABLE IF NOT EXISTS events (
    id                INT AUTO_INCREMENT PRIMARY KEY,
    title             VARCHAR(500) NOT NULL,
    slug              VARCHAR(500) UNIQUE NOT NULL,
    image_url         TEXT,
    short_description TEXT NOT NULL,
    long_description  TEXT NOT NULL,
    event_date        DATE NOT NULL,
    event_time        TIME NOT NULL,
    location_name     VARCHAR(500) NOT NULL,
    location_address  TEXT,
    latitude          DECIMAL(10,8),
    longitude         DECIMAL(11,8),
    category          VARCHAR(100) DEFAULT 'generale',
    is_published      TINYINT(1) DEFAULT 1,
    interested_count  INT DEFAULT 0,
    created_at        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_date (event_date),
    INDEX idx_published (is_published),
    FULLTEXT idx_search (title, short_description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- INTERESTED EVENTS
CREATE TABLE IF NOT EXISTS interested_events (
    id                   INT AUTO_INCREMENT PRIMARY KEY,
    user_id              INT NOT NULL,
    event_id             INT NOT NULL,
    notified_day_before  TINYINT(1) DEFAULT 0,
    notified_day_of      TINYINT(1) DEFAULT 0,
    notified_hour_before TINYINT(1) DEFAULT 0,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_interest (user_id, event_id),
    FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NOTIFICATIONS LOG
CREATE TABLE IF NOT EXISTS notifications (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    body        TEXT NOT NULL,
    event_id    INT,
    target_type VARCHAR(50) DEFAULT 'all',
    sent_count  INT DEFAULT 0,
    status      VARCHAR(50) DEFAULT 'sent',
    sent_at     TIMESTAMP NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- TRIGGER: aggiorna interested_count automaticamente
DELIMITER $$

CREATE TRIGGER IF NOT EXISTS after_interested_insert
AFTER INSERT ON interested_events
FOR EACH ROW
BEGIN
    UPDATE events SET interested_count = interested_count + 1 WHERE id = NEW.event_id;
END$$

CREATE TRIGGER IF NOT EXISTS after_interested_delete
AFTER DELETE ON interested_events
FOR EACH ROW
BEGIN
    UPDATE events SET interested_count = GREATEST(interested_count - 1, 0) WHERE id = OLD.event_id;
END$$

DELIMITER ;

-- Dati di esempio
INSERT IGNORE INTO events (title, slug, short_description, long_description, event_date, event_time, location_name, location_address, latitude, longitude, category) VALUES
(
    'Festival della Lavanda 2025',
    'festival-lavanda-2025',
    'Una giornata immersa nei colori e profumi della lavanda delle Alpi Marittime.',
    'Il Festival della Lavanda torna con una straordinaria edizione 2025. Passeggia tra i campi fioriti, scopri i prodotti locali e goditi musica dal vivo nel cuore delle Alpi Marittime. Un evento per tutta la famiglia.',
    '2025-07-12', '10:00:00', 'Piazza Galimberti, Cuneo', 'Piazza Galimberti, 12100 Cuneo CN', 44.3845, 7.5423, 'festival'
),
(
    'Trekking Alba delle Marittime',
    'trekking-alba-marittime-2025',
    'Escursione guidata all\'alba tra i picchi delle Alpi Marittime con colazione in quota.',
    'Parti all\'alba con guide esperte alla scoperta dei panorami mozzafiato delle Alpi Marittime. L\'escursione include colazione con prodotti locali a 2000m di quota.',
    '2025-07-20', '05:30:00', 'Rifugio Remondino, Valdieri', 'Rifugio Remondino, Valdieri CN', 44.2167, 7.3833, 'natura'
),
(
    'Mercato Medievale Limone Piemonte',
    'mercato-medievale-limone-2025',
    'Rievocazione storica medievale nel borgo alpino di Limone Piemonte.',
    'Limone Piemonte si trasforma in un borgo medievale per un weekend di magia e storia. Artigiani in costume d\'epoca, tornei e gastronomia medievale.',
    '2025-08-02', '15:00:00', 'Centro Storico, Limone Piemonte', 'Via Roma, Limone Piemonte CN', 44.1004, 7.5667, 'cultura'
);
