-- DB schema
#CREATE DATABASE IF NOT EXISTS leadpad CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE leadpad;
CREATE TABLE IF NOT EXISTS leads(
 id INT AUTO_INCREMENT PRIMARY KEY,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 event_name VARCHAR(128) NULL,
 activity_source VARCHAR(128) NULL,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 company VARCHAR(150) NULL,
 role_title VARCHAR(150) NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(50) NULL,
 country VARCHAR(100) NULL,
 notes TEXT NULL,
 consent_core TINYINT(1) NOT NULL DEFAULT 0,
 consent_marketing TINYINT(1) NOT NULL DEFAULT 0,
 consent_profiling TINYINT(1) NOT NULL DEFAULT 0,
 signature_path VARCHAR(255) NULL,
 ip_address VARCHAR(45) NULL,
 user_agent TEXT NULL
);
CREATE INDEX idx_leads_email ON leads(email);
CREATE INDEX idx_leads_event ON leads(event_name);


-- Dedicated table for selectable activity sources
CREATE TABLE IF NOT EXISTS activity_sources (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL UNIQUE,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed a few examples
INSERT IGNORE INTO activity_sources (name) VALUES ('Evento A'), ('Evento B'), ('Workshop'), ('Desk Accoglienza');


-- Dedicated table for events
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL UNIQUE,
  event_date DATE NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Seed examples
INSERT IGNORE INTO events (name, event_date) VALUES
('SMAU Milano 2025', '2025-10-20'),
('TTG Rimini 2025', '2025-10-13'),
('Hospitality Day', NULL);

-- Add operator_id column to leads if not exists
ALTER TABLE leads ADD COLUMN IF NOT EXISTS operator_id INT NULL;
ALTER TABLE leads ADD INDEX idx_leads_operator (operator_id);


-- Timestamp della firma lato client
ALTER TABLE leads ADD COLUMN IF NOT EXISTS signature_timestamp DATETIME NULL AFTER signature_path;
