SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS registrations (
  id VARCHAR(64) PRIMARY KEY, first_name VARCHAR(191) NOT NULL, last_name VARCHAR(191) NOT NULL,
  email VARCHAR(255) NOT NULL, phone VARCHAR(80), country VARCHAR(191) NOT NULL, city VARCHAR(191),
  profession VARCHAR(255), organization VARCHAR(255), sector VARCHAR(255), diaspora VARCHAR(255),
  invest VARCHAR(255), interests TEXT, message TEXT, status VARCHAR(64) NOT NULL DEFAULT 'Nouveau',
  ticket_code VARCHAR(191) UNIQUE, ticket_sent_at VARCHAR(40), ticket_email_provider_id VARCHAR(255),
  checked_in_at VARCHAR(40), checked_in_by VARCHAR(255), created_at VARCHAR(40) NOT NULL,
  INDEX idx_registrations_created_at (created_at), INDEX idx_registrations_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_messages (
  id VARCHAR(64) PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone VARCHAR(80),
  subject VARCHAR(255) NOT NULL, message TEXT NOT NULL, status VARCHAR(64) NOT NULL DEFAULT 'Nouveau',
  created_at VARCHAR(40) NOT NULL, INDEX idx_contact_messages_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_replies (
  id VARCHAR(64) PRIMARY KEY, message_id VARCHAR(64) NOT NULL, subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL, provider_id VARCHAR(255), status VARCHAR(64) NOT NULL, created_at VARCHAR(40) NOT NULL,
  INDEX idx_message_replies_message_id (message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS registration_replies (
  id VARCHAR(64) PRIMARY KEY, registration_id VARCHAR(64) NOT NULL, subject VARCHAR(255) NOT NULL,
  body LONGTEXT NOT NULL, provider_id VARCHAR(255), status VARCHAR(64) NOT NULL, created_at VARCHAR(40) NOT NULL,
  INDEX idx_registration_replies_registration_id (registration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_users (
  id VARCHAR(64) PRIMARY KEY, provider_user_id VARCHAR(191) UNIQUE, email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(191) NOT NULL, password_hash VARCHAR(255) NOT NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  role VARCHAR(40) NOT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_by VARCHAR(64),
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40), last_seen_at VARCHAR(40),
  INDEX idx_admin_users_role_active (role, active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_sessions (
  session_hash VARCHAR(64) PRIMARY KEY, admin_id VARCHAR(64) NOT NULL, created_at VARCHAR(40) NOT NULL,
  expires_at VARCHAR(40) NOT NULL, ip_address VARCHAR(64), user_agent TEXT,
  INDEX idx_admin_sessions_admin (admin_id), INDEX idx_admin_sessions_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_login_attempts (
  ip VARCHAR(64) PRIMARY KEY, attempts INT NOT NULL DEFAULT 0, first_at VARCHAR(40) NOT NULL, blocked_until VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_settings (
  id VARCHAR(64) PRIMARY KEY, `key` VARCHAR(191) NOT NULL UNIQUE, value LONGTEXT,
  secret TINYINT(1) NOT NULL DEFAULT 0, created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS site_secrets (
  `key` VARCHAR(191) PRIMARY KEY, value LONGTEXT NOT NULL, updated_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
  id VARCHAR(64) PRIMARY KEY, slug VARCHAR(191) NOT NULL UNIQUE, title VARCHAR(255) NOT NULL,
  published TINYINT(1) NOT NULL DEFAULT 0, created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sections (
  id VARCHAR(64) PRIMARY KEY, page_id VARCHAR(64) NOT NULL, kind VARCHAR(80) NOT NULL, content LONGTEXT NOT NULL,
  display_order INT NOT NULL DEFAULT 0, published TINYINT(1) NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40), INDEX idx_sections_page (page_id, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS speakers (
  id VARCHAR(64) PRIMARY KEY, name VARCHAR(255) NOT NULL, role VARCHAR(255), organization VARCHAR(255), bio TEXT,
  photo VARCHAR(512), linkedin VARCHAR(512), instagram VARCHAR(512), website VARCHAR(512),
  display_order INT NOT NULL DEFAULT 0, published TINYINT(1) NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS program_items (
  id VARCHAR(64) PRIMARY KEY, start_time VARCHAR(40) NOT NULL, end_time VARCHAR(40) NOT NULL,
  title VARCHAR(500) NOT NULL, description LONGTEXT, category VARCHAR(191), speakers TEXT,
  display_order INT NOT NULL DEFAULT 0, published TINYINT(1) NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS statistics (
  id VARCHAR(64) PRIMARY KEY, value VARCHAR(191) NOT NULL, suffix VARCHAR(80), label VARCHAR(255) NOT NULL,
  source VARCHAR(512), display_order INT NOT NULL DEFAULT 0, published TINYINT(1) NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS partners (
  id VARCHAR(64) PRIMARY KEY, name VARCHAR(255) NOT NULL, logo VARCHAR(512), url VARCHAR(512), category VARCHAR(191),
  display_order INT NOT NULL DEFAULT 0, published TINYINT(1) NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media (
  id VARCHAR(64) PRIMARY KEY, filename VARCHAR(255) NOT NULL, `key` VARCHAR(512) NOT NULL UNIQUE,
  mime VARCHAR(191) NOT NULL, size BIGINT NOT NULL, width INT, height INT, alt VARCHAR(500), created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS page_views (
  id VARCHAR(64) PRIMARY KEY, path VARCHAR(512) NOT NULL, day VARCHAR(10) NOT NULL,
  created_at VARCHAR(40) NOT NULL, source VARCHAR(255), INDEX idx_page_views_day (day),
  INDEX idx_page_views_path_day (path(191), day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seo_settings (
  id VARCHAR(64) PRIMARY KEY, meta_title VARCHAR(255), meta_description TEXT, og_image VARCHAR(512),
  twitter_image VARCHAR(512), indexable TINYINT(1) NOT NULL DEFAULT 1, updated_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS legal_pages (
  id VARCHAR(64) PRIMARY KEY, slug VARCHAR(191) NOT NULL UNIQUE, title VARCHAR(255) NOT NULL,
  intro TEXT NOT NULL, content LONGTEXT NOT NULL, published TINYINT(1) NOT NULL DEFAULT 0,
  system TINYINT(1) NOT NULL DEFAULT 0, created_at VARCHAR(40) NOT NULL, updated_at VARCHAR(40),
  INDEX idx_legal_pages_published (published)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_subscriptions (
  email VARCHAR(255) PRIMARY KEY, unsubscribe_token VARCHAR(191) NOT NULL UNIQUE,
  subscribed TINYINT(1) NOT NULL DEFAULT 1, created_at VARCHAR(40) NOT NULL, unsubscribed_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automated_emails (
  id VARCHAR(64) PRIMARY KEY, record_type VARCHAR(40) NOT NULL, record_id VARCHAR(64) NOT NULL,
  recipient VARCHAR(255) NOT NULL, template VARCHAR(191) NOT NULL, provider_id VARCHAR(255),
  status VARCHAR(64) NOT NULL, error_message TEXT, created_at VARCHAR(40) NOT NULL, sent_at VARCHAR(40),
  INDEX idx_automated_emails_record (record_type, record_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ticket_scans (
  id VARCHAR(64) PRIMARY KEY, registration_id VARCHAR(64), ticket_code VARCHAR(191) NOT NULL,
  scanned_by VARCHAR(255) NOT NULL, method VARCHAR(80) NOT NULL, status VARCHAR(64) NOT NULL,
  scanned_at VARCHAR(40) NOT NULL, INDEX idx_ticket_scans_scanned_at (scanned_at),
  INDEX idx_ticket_scans_registration_id (registration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_notifications (
  id VARCHAR(64) PRIMARY KEY, type VARCHAR(40) NOT NULL, title VARCHAR(255) NOT NULL,
  message VARCHAR(500) NOT NULL, target VARCHAR(191) NOT NULL, read_at VARCHAR(40), created_at VARCHAR(40) NOT NULL,
  INDEX idx_admin_notifications_created_at (created_at), INDEX idx_admin_notifications_read_at (read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS software_updates (
  id VARCHAR(64) PRIMARY KEY, version VARCHAR(40) NOT NULL UNIQUE, filename VARCHAR(255) NOT NULL,
  storage_key VARCHAR(512) NOT NULL UNIQUE, size BIGINT NOT NULL, checksum VARCHAR(64) NOT NULL UNIQUE,
  notes TEXT NOT NULL, status VARCHAR(64) NOT NULL DEFAULT 'Contrôlé', created_by VARCHAR(255) NOT NULL,
  created_at VARCHAR(40) NOT NULL, backup_key VARCHAR(512), INDEX idx_software_updates_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO site_settings (id,`key`,value,secret,created_at,updated_at) VALUES
('setting-smtpHost','smtpHost','mail.fip-forum.com',0,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('setting-smtpPort','smtpPort','465',0,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('setting-smtpUser','smtpUser','contact@fip-forum.com',0,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('setting-smtpFrom','smtpFrom','contact@fip-forum.com',0,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('setting-smtpName','smtpName','Forum des Investissements et des Partenariats',0,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('setting-smtpEncryption','smtpEncryption','SSL',0,UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE value=VALUES(value),updated_at=VALUES(updated_at);

INSERT IGNORE INTO partners (id,name,logo,display_order,published,created_at,updated_at) VALUES
('partner-7','Guiwara','/partners/guiwara.png',6,1,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('partner-8','IA Capital','/partners/ia-capital.png',7,1,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('partner-9','Louma','/partners/louma.png',8,1,UTC_TIMESTAMP(),UTC_TIMESTAMP());

INSERT INTO admin_users (id,email,name,password_hash,must_change_password,role,active,created_at,updated_at)
VALUES ('super-admin-o2switch','admin@fip-forum.com','Super administrateur FIP','$2b$12$x8qrd8ZVzOP6.56a66brE.OV.8UGLDGo9GTkv7L2X1421hDihbULi',1,'super_admin',1,UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE name=VALUES(name),password_hash=VALUES(password_hash),must_change_password=1,role='super_admin',active=1,updated_at=VALUES(updated_at);