CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clientes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  empresa_nome VARCHAR(160) NULL,
  nif VARCHAR(32) NULL,
  nif_validado TINYINT(1) NOT NULL DEFAULT 0,
  telefone VARCHAR(32) NULL,
  morada VARCHAR(255) NULL,
  tipo ENUM('produtor','whitelabel') NOT NULL DEFAULT 'produtor',
  password_hash VARCHAR(255) NOT NULL,
  created_by_whitelabel_id INT UNSIGNED NULL,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE INDEX idx_clientes_created_by_whitelabel_id ON clientes (created_by_whitelabel_id);
CREATE INDEX idx_clientes_ativo ON clientes (ativo);

CREATE TABLE IF NOT EXISTS marcas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  nome VARCHAR(120) NOT NULL,
  logo_url VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_marcas_cliente (cliente_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categorias (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NULL,
  nome VARCHAR(120) NOT NULL,
  scope ENUM('global','privado') NOT NULL DEFAULT 'privado',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_categorias_cliente (cliente_id),
  INDEX idx_categorias_scope (scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vinhos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  marca_id INT UNSIGNED NOT NULL,
  categoria_id INT UNSIGNED NOT NULL,
  nome VARCHAR(160) NOT NULL,
  public_token VARCHAR(64) NULL,
  public_slug VARCHAR(200) NULL,
  qr_png_url VARCHAR(255) NULL,
  qr_png_hi_url VARCHAR(255) NULL,
  qr_svg_url VARCHAR(255) NULL,
  imagem_url VARCHAR(255) NULL,
  colheita INT UNSIGNED NULL,
  regiao VARCHAR(120) NULL,
  castas TEXT NULL,
  notas_vindima TEXT NULL,
  notas_prova_color TEXT NULL,
  aroma TEXT NULL,
  palato TEXT NULL,
  enologo VARCHAR(120) NULL,
  nivel_consumo TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_vinhos_cliente (cliente_id),
  INDEX idx_vinhos_marca (marca_id),
  INDEX idx_vinhos_categoria (categoria_id),
  UNIQUE KEY uniq_vinhos_public_token (public_token),
  UNIQUE KEY uniq_vinhos_public_slug (public_slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vinho_ficha_tecnica (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vinho_id INT UNSIGNED NOT NULL UNIQUE,
  titulo_alcoolometrico DECIMAL(5,2) NULL,
  ph DECIMAL(4,2) NULL,
  acidez_total_g_l DECIMAL(6,2) NULL,
  acucar_residual_g_l DECIMAL(6,2) NULL,
  analises_analiticas TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_ficha_tecnica_vinho (vinho_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vinho_ficha_nutricional (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vinho_id INT UNSIGNED NOT NULL UNIQUE,
  energia_kj DECIMAL(8,2) NULL,
  energia_kcal DECIMAL(8,2) NULL,
  energia_dr DECIMAL(5,2) NULL,
  gordura_g DECIMAL(8,2) NULL,
  gordura_dr DECIMAL(5,2) NULL,
  gordura_saturada_g DECIMAL(8,2) NULL,
  gordura_saturada_dr DECIMAL(5,2) NULL,
  hidratos_g DECIMAL(8,2) NULL,
  hidratos_dr DECIMAL(5,2) NULL,
  acucares_g DECIMAL(8,2) NULL,
  acucares_dr DECIMAL(5,2) NULL,
  proteinas_g DECIMAL(8,2) NULL,
  proteinas_dr DECIMAL(5,2) NULL,
  sal_g DECIMAL(8,3) NULL,
  sal_dr DECIMAL(5,2) NULL,
  por_100ml TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_ficha_nutricional_vinho (vinho_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vinho_cubas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vinho_id INT UNSIGNED NOT NULL,
  cliente_id INT UNSIGNED NOT NULL,
  tipo ENUM('cuba','pipa') NOT NULL DEFAULT 'cuba',
  identificador VARCHAR(80) NOT NULL,
  litros DECIMAL(10,2) NULL,
  garrafas_total INT UNSIGNED NULL,
  num_inicio INT UNSIGNED NULL,
  num_fim INT UNSIGNED NULL,
  data_cuba DATE NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_cubas_vinho (vinho_id),
  INDEX idx_cubas_cliente (cliente_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vinho_rolhas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vinho_id INT UNSIGNED NOT NULL,
  cliente_id INT UNSIGNED NOT NULL,
  identificador VARCHAR(120) NOT NULL,
  fornecedor VARCHAR(160) NULL,
  num_inicio INT UNSIGNED NULL,
  num_fim INT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_rolhas_vinho (vinho_id),
  INDEX idx_rolhas_cliente (cliente_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entity VARCHAR(60) NOT NULL,
  entity_id INT UNSIGNED NULL,
  vinho_id INT UNSIGNED NULL,
  action ENUM('create','update','delete') NOT NULL,
  user_id INT UNSIGNED NULL,
  user_role VARCHAR(20) NULL,
  changes JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_entity (entity, entity_id),
  INDEX idx_audit_vinho (vinho_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS castas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NULL,
  nome VARCHAR(120) NOT NULL,
  scope ENUM('global','privado') NOT NULL DEFAULT 'privado',
  nome_norm VARCHAR(120) GENERATED ALWAYS AS (LOWER(TRIM(nome))) STORED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_castas_cliente (cliente_id),
  INDEX idx_castas_scope (scope),
  UNIQUE KEY uniq_castas_nome_norm (nome_norm, scope)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS regioes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NULL,
  nome VARCHAR(120) NOT NULL,
  logo_url VARCHAR(255) NULL,
  scope ENUM('global','privado') NOT NULL DEFAULT 'privado',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_regioes_cliente (cliente_id),
  UNIQUE KEY uniq_regioes_scope_nome (scope, nome)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS planos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  codigo VARCHAR(50) NOT NULL UNIQUE,
  nome VARCHAR(120) NOT NULL,
  intervalo ENUM('mensal','semestral','anual') NOT NULL,
  preco DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  max_vinhos INT UNSIGNED NOT NULL DEFAULT 0,
  ativo TINYINT(1) NOT NULL DEFAULT 1,
  stripe_product_id VARCHAR(80) NULL,
  stripe_price_id VARCHAR(80) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscricoes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  plano_id INT UNSIGNED NOT NULL,
  status ENUM('ativa','cancelada','expirada','pendente') NOT NULL DEFAULT 'ativa',
  auto_renovar TINYINT(1) NOT NULL DEFAULT 1,
  inicio_em DATETIME NULL,
  fim_em DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  INDEX idx_sub_cliente (cliente_id),
  INDEX idx_sub_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pagamentos (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT UNSIGNED NOT NULL,
  plano_id INT UNSIGNED NOT NULL,
  subscricao_id INT UNSIGNED NULL,
  metodo ENUM('multibanco','mbway','stripe') NOT NULL,
  estado ENUM('pendente','pago','falhado','cancelado') NOT NULL DEFAULT 'pendente',
  acao ENUM('nova','renovacao') NOT NULL DEFAULT 'nova',
  valor DECIMAL(10,2) NOT NULL,
  entidade VARCHAR(20) NULL,
  referencia VARCHAR(50) NULL,
  identificador VARCHAR(64) NOT NULL,
  transacao VARCHAR(64) NULL,
  canal VARCHAR(20) NULL,
  mp VARCHAR(50) NULL,
  stripe_session_id VARCHAR(255) NULL,
  stripe_subscription_id VARCHAR(255) NULL,
  stripe_customer_id VARCHAR(255) NULL,
  pedido JSON NULL,
  resposta JSON NULL,
  callback JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uniq_pag_identificador (identificador),
  INDEX idx_pag_cliente (cliente_id),
  INDEX idx_pag_sub (subscricao_id),
  INDEX idx_pag_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
