CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('owner','admin','editor') NOT NULL DEFAULT 'owner',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ai_settings (
  id TINYINT UNSIGNED PRIMARY KEY,
  endpoint_url VARCHAR(500) NULL,
  api_token_encrypted TEXT NULL,
  model VARCHAR(120) NOT NULL DEFAULT 'gpt-4o-mini',
  system_prompt TEXT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO ai_settings (id, system_prompt) VALUES (1, 'Você é um editor técnico claro. Gere textos úteis, objetivos e sem afirmações jurídicas não verificadas.');

CREATE TABLE passports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  type ENUM('product','service') NOT NULL DEFAULT 'product',
  name VARCHAR(190) NOT NULL,
  slug VARCHAR(220) NOT NULL UNIQUE,
  short_description VARCHAR(500) NULL,
  description TEXT NULL,
  brand VARCHAR(190) NULL,
  category VARCHAR(120) NULL,
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  language CHAR(5) NOT NULL DEFAULT 'pt-BR',
  warranty_text TEXT NULL,
  contact_email VARCHAR(190) NULL,
  website_url VARCHAR(500) NULL,
  cover_url VARCHAR(500) NULL,
  metadata_json JSON NULL,
  created_by INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_passports_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
  INDEX idx_passports_status (status),
  INDEX idx_passports_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE passport_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  passport_id INT UNSIGNED NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NULL,
  event_date DATE NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_events_passport FOREIGN KEY (passport_id) REFERENCES passports(id) ON DELETE CASCADE,
  INDEX idx_events_passport (passport_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;