-- Social Open v3.23 fixed - install.sql (tables clés)
CREATE TABLE IF NOT EXISTS settings(
  id TINYINT PRIMARY KEY,
  site_name VARCHAR(120) NOT NULL DEFAULT 'Social Open',
  site_description VARCHAR(255) NOT NULL DEFAULT '',
  https_enabled TINYINT(1) NOT NULL DEFAULT 0,
  flood_limit INT NOT NULL DEFAULT 3,
  flood_window INT NOT NULL DEFAULT 10,
  role_colors_enabled TINYINT(1) NOT NULL DEFAULT 1,
  notify_enabled TINYINT(1) NOT NULL DEFAULT 1,
  allow_guests TINYINT(1) NOT NULL DEFAULT 1,
  block_urls TINYINT(1) NOT NULL DEFAULT 1,
  default_theme ENUM('dark','light') NOT NULL DEFAULT 'dark',
  maintenance_enabled TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
INSERT IGNORE INTO settings(id) VALUES(1);

CREATE TABLE IF NOT EXISTS rooms(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
INSERT IGNORE INTO rooms(name) VALUES('global');

CREATE TABLE IF NOT EXISTS messages(
  id INT AUTO_INCREMENT PRIMARY KEY,
  room_id INT NOT NULL,
  author VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(room_id,created_at),
  FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS private_messages(
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender VARCHAR(100) NOT NULL,
  receiver VARCHAR(100) NOT NULL,
  content TEXT NOT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(receiver), INDEX(sender)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS guest_sessions(
  session_id VARCHAR(128) PRIMARY KEY,
  pseudo VARCHAR(50) NOT NULL,
  announcement VARCHAR(255) DEFAULT '',
  location VARCHAR(10) DEFAULT '',
  last_seen DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS contact_inbox(
  id INT AUTO_INCREMENT PRIMARY KEY,
  sender VARCHAR(120) NOT NULL,
  email VARCHAR(190) DEFAULT NULL,
  category VARCHAR(60) NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admin_logs(
  id INT AUTO_INCREMENT PRIMARY KEY,
  actor VARCHAR(100) NOT NULL,
  action VARCHAR(100) NOT NULL,
  details TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- users colonnes utiles (exécuter manuellement si besoin)
-- ALTER TABLE users ADD COLUMN role ENUM('fondateur','admin','supermod','moderateur','membre','premium') DEFAULT 'membre';
-- ALTER TABLE users ADD COLUMN is_invisible TINYINT(1) DEFAULT 0;
-- ALTER TABLE users ADD COLUMN theme ENUM('dark','light') NULL;
-- ALTER TABLE users ADD COLUMN color_role TINYINT(1) DEFAULT 1;
-- ALTER TABLE users ADD COLUMN ip_address VARCHAR(45) NULL;
-- ALTER TABLE users ADD COLUMN last_seen DATETIME NULL;
