-- Estrutura inicial do banco para o sistema de painéis LED

CREATE TABLE clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  document VARCHAR(50) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE stores (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin_master','admin_cliente','cliente') NOT NULL,
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
);

CREATE TABLE user_store_permissions (
  user_id INT NOT NULL,
  store_id INT NOT NULL,
  PRIMARY KEY (user_id, store_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
);

CREATE TABLE panels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  store_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  panel_key VARCHAR(64) NOT NULL UNIQUE,
  width INT NOT NULL DEFAULT 96,
  height INT NOT NULL DEFAULT 96,
  brightness TINYINT DEFAULT 10,
  is_active TINYINT(1) DEFAULT 1,
  last_seen DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE
);

CREATE TABLE panel_settings (
  panel_id INT PRIMARY KEY,
  power TINYINT(1) DEFAULT 1,
  use_schedule TINYINT(1) DEFAULT 0,
  on_time CHAR(5) DEFAULT '08:00',
  off_time CHAR(5) DEFAULT '22:00',
  FOREIGN KEY (panel_id) REFERENCES panels(id) ON DELETE CASCADE
);

CREATE TABLE offers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  name VARCHAR(100) NOT NULL,
  header VARCHAR(50) DEFAULT NULL,
  title VARCHAR(50) NOT NULL,
  subtitle VARCHAR(50) DEFAULT NULL,
  price_cents INT NOT NULL,
  unit VARCHAR(10) DEFAULT NULL,
  aux VARCHAR(50) DEFAULT NULL,
  footer VARCHAR(100) DEFAULT NULL,
  duration_sec INT DEFAULT 5,
  enabled TINYINT(1) DEFAULT 1,
  opt_half_screen TINYINT(1) DEFAULT 0,
  opt_enable_subtitle TINYINT(1) DEFAULT 0,
  opt_disable_cents TINYINT(1) DEFAULT 0,
  opt_3_digits_cents TINYINT(1) DEFAULT 0,
  opt_small_cents TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE panel_offers (
  panel_id INT NOT NULL,
  offer_id INT NOT NULL,
  position INT DEFAULT 0,
  enabled TINYINT(1) DEFAULT 1,
  PRIMARY KEY (panel_id, offer_id),
  FOREIGN KEY (panel_id) REFERENCES panels(id) ON DELETE CASCADE,
  FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE
);


-- Mensagens livres (tela "Mensagens")
CREATE TABLE messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  use_global TINYINT(1) NOT NULL DEFAULT 1,
  transition_effect VARCHAR(32) NOT NULL DEFAULT 'fade',
  duration_sec INT NOT NULL DEFAULT 8,
  border_animated TINYINT(1) NOT NULL DEFAULT 1,
  border_speed INT NOT NULL DEFAULT 5,
  lines_json LONGTEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE panel_messages (
  panel_id INT NOT NULL,
  message_id INT NOT NULL,
  position INT DEFAULT 0,
  enabled TINYINT(1) DEFAULT 1,
  PRIMARY KEY (panel_id, message_id),
  FOREIGN KEY (panel_id) REFERENCES panels(id) ON DELETE CASCADE,
  FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE
);




CREATE TABLE global_settings (
  id INT PRIMARY KEY,
  transition_effect VARCHAR(32) NOT NULL DEFAULT 'fade',
  duration_sec INT NOT NULL DEFAULT 8,
  transition_sec INT NOT NULL DEFAULT 2,
  border_animated TINYINT(1) NOT NULL DEFAULT 1,
  border_speed INT NOT NULL DEFAULT 5,
  price_blink TINYINT(1) NOT NULL DEFAULT 1,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO global_settings (id) VALUES (1) ON DUPLICATE KEY UPDATE id=id;




CREATE TABLE panel_playlist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  panel_id INT NOT NULL,
  item_type ENUM('offer','message') NOT NULL,
  item_id INT NOT NULL,
  position INT NOT NULL DEFAULT 1,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_panel_pos (panel_id, position),
  INDEX idx_panel_type (panel_id, item_type),
  FOREIGN KEY (panel_id) REFERENCES panels(id) ON DELETE CASCADE
);

