-- Migração V2: papéis, agenda, produtos, comissões, banners, configs

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  role ENUM('super_admin','admin','barbeiro','cliente') NOT NULL,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  phone VARCHAR(40) NULL,
  password_hash VARCHAR(255) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE barbers ADD COLUMN user_id INT NULL;
ALTER TABLE barbers ADD COLUMN nickname VARCHAR(80) NULL;
ALTER TABLE barbers ADD COLUMN bio TEXT NULL;
ALTER TABLE barbers ADD COLUMN comissao_padrao_pct DECIMAL(5,2) DEFAULT 40.00;
ALTER TABLE barbers ADD COLUMN slot_minutes_override INT NULL;
ALTER TABLE barbers ADD COLUMN active TINYINT(1) DEFAULT 1;
ALTER TABLE barbers ADD CONSTRAINT fk_barber_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE services ADD COLUMN buffer_min INT DEFAULT 0;
ALTER TABLE services ADD COLUMN comissao_padrao_pct DECIMAL(5,2) NULL;
ALTER TABLE services ADD COLUMN active TINYINT(1) DEFAULT 1;

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  sku VARCHAR(60) UNIQUE,
  price DECIMAL(10,2) NOT NULL,
  commission_pct DECIMAL(5,2) NULL,
  stock INT NULL,
  active TINYINT(1) DEFAULT 1
);

-- Agenda
CREATE TABLE IF NOT EXISTS agenda (
  id INT AUTO_INCREMENT PRIMARY KEY,
  barber_id INT NOT NULL,
  client_id INT NULL,
  service_id INT NOT NULL,
  date DATE NOT NULL,
  start DATETIME NOT NULL,
  end DATETIME NOT NULL,
  applied_price DECIMAL(10,2) NOT NULL,
  status ENUM('pendente','confirmado','concluido','cancelado','no_show') DEFAULT 'pendente',
  origin ENUM('app','admin') DEFAULT 'app',
  notes TEXT NULL,
  CONSTRAINT fk_agenda_barber FOREIGN KEY (barber_id) REFERENCES barbers(id) ON DELETE CASCADE,
  CONSTRAINT fk_agenda_client FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_agenda_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX idx_agenda_unique ON agenda(barber_id, start);
CREATE INDEX idx_agenda_barber_time ON agenda(barber_id, start, end, status);
CREATE INDEX idx_agenda_client_date ON agenda(client_id, date);

CREATE TABLE IF NOT EXISTS agenda_bloqueios (
  id INT AUTO_INCREMENT PRIMARY KEY,
  barber_id INT NOT NULL,
  start DATETIME NOT NULL,
  end DATETIME NOT NULL,
  reason VARCHAR(200) NULL,
  FOREIGN KEY (barber_id) REFERENCES barbers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS holidays (
  id INT AUTO_INCREMENT PRIMARY KEY,
  date DATE NOT NULL,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agenda_id INT NOT NULL,
  method ENUM('dinheiro','pix','cartao','outro') NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  discount DECIMAL(10,2) DEFAULT 0,
  fee DECIMAL(10,2) DEFAULT 0,
  status ENUM('pendente','pago','falhou') DEFAULT 'pendente',
  transaction_ref VARCHAR(120) NULL,
  FOREIGN KEY (agenda_id) REFERENCES agenda(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS commissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  agenda_id INT NOT NULL,
  barber_id INT NOT NULL,
  base DECIMAL(10,2) NOT NULL,
  percent DECIMAL(5,2) NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  closed_at DATE NULL,
  FOREIGN KEY (agenda_id) REFERENCES agenda(id) ON DELETE CASCADE,
  FOREIGN KEY (barber_id) REFERENCES barbers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS vendas_produtos (
  id INT AUTO_INCREMENT PRIMARY KEY,
  barber_id INT NOT NULL,
  client_id INT NULL,
  product_id INT NOT NULL,
  qty INT NOT NULL,
  unit_value DECIMAL(10,2) NOT NULL,
  payment_id INT NULL,
  date DATETIME NOT NULL,
  status ENUM('pendente','pago','falhou') DEFAULT 'pendente',
  FOREIGN KEY (barber_id) REFERENCES barbers(id) ON DELETE CASCADE,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS commissions_products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sale_id INT NOT NULL,
  barber_id INT NOT NULL,
  base DECIMAL(10,2) NOT NULL,
  percent DECIMAL(5,2) NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (sale_id) REFERENCES vendas_produtos(id) ON DELETE CASCADE,
  FOREIGN KEY (barber_id) REFERENCES barbers(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  image_url VARCHAR(255) NOT NULL,
  link VARCHAR(255) NULL,
  active TINYINT(1) DEFAULT 1,
  sort INT DEFAULT 0,
  position ENUM('login_top','home_top') NOT NULL
);

CREATE TABLE IF NOT EXISTS establishment (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  cnpj VARCHAR(30) NULL,
  address VARCHAR(255) NULL,
  latitude DECIMAL(10,6) NULL,
  longitude DECIMAL(10,6) NULL,
  phone VARCHAR(40) NULL,
  whatsapp_display VARCHAR(40) NULL,
  map_url VARCHAR(255) NULL,
  opening_hours_json JSON NULL
);

CREATE TABLE IF NOT EXISTS coupons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(40) NOT NULL UNIQUE,
  type ENUM('pct','valor') NOT NULL,
  value DECIMAL(10,2) NOT NULL,
  start DATE NOT NULL,
  end DATE NOT NULL,
  active TINYINT(1) DEFAULT 1,
  max_usage INT DEFAULT 0,
  max_usage_per_client INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('whatsapp','email','push') NOT NULL,
  user_id INT NULL,
  payload_json JSON NOT NULL,
  status ENUM('fila','enviado','falhou') DEFAULT 'fila',
  attempts INT DEFAULT 0,
  last_error VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS configs (
  key_name VARCHAR(80) PRIMARY KEY,
  value_json JSON NOT NULL
);

-- Configurações iniciais
INSERT IGNORE INTO configs (key_name, value_json) VALUES
 ('slot_minutes', '30'),
 ('booking_window_hours_min', '1'),
 ('booking_window_days_max', '30'),
 ('cancel_limit_hours', '2'),
 ('commission_base_after_discount', 'true');