-- MySQL 8 schema (multi-tenant) para vitrine + leads
-- Charset recomendado: utf8mb4

CREATE TABLE tenants (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  logo_url VARCHAR(500) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  address VARCHAR(255) NULL,
  city VARCHAR(120) NULL,
  whatsapp VARCHAR(50) NULL,
  primary_color VARCHAR(20) NULL,
  accent_color VARCHAR(20) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE properties (
  id CHAR(36) PRIMARY KEY,
  tenant_id CHAR(36) NOT NULL,
  code VARCHAR(50) NOT NULL,
  title VARCHAR(200) NOT NULL,
  type VARCHAR(50) NOT NULL,
  purpose VARCHAR(20) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'Disponível',
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  area DECIMAL(10,2) NULL,
  bedrooms INT NOT NULL DEFAULT 0,
  bathrooms INT NOT NULL DEFAULT 0,
  parking INT NOT NULL DEFAULT 0,
  description TEXT NULL,
  address VARCHAR(255) NULL,
  neighborhood VARCHAR(120) NULL,
  city VARCHAR(120) NOT NULL DEFAULT 'Paranaguá',
  state VARCHAR(10) NOT NULL DEFAULT 'PR',
  zip_code VARCHAR(20) NULL,
  latitude DECIMAL(10,7) NULL,
  longitude DECIMAL(10,7) NULL,
  featured TINYINT(1) NOT NULL DEFAULT 0,
  high_end TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tenant_code (tenant_id, code),
  KEY idx_tenant_active (tenant_id, active),
  CONSTRAINT fk_properties_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE property_images (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  property_id CHAR(36) NOT NULL,
  url VARCHAR(800) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  KEY idx_prop_img (property_id, sort_order),
  CONSTRAINT fk_propimg_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE leads (
  id CHAR(36) PRIMARY KEY,
  tenant_id CHAR(36) NOT NULL,
  property_id CHAR(36) NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NULL,
  phone VARCHAR(60) NULL,
  interest VARCHAR(80) NULL,
  message TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  KEY idx_leads_tenant (tenant_id, created_at),
  CONSTRAINT fk_leads_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_leads_property FOREIGN KEY (property_id) REFERENCES properties(id) ON DELETE SET NULL
) ENGINE=InnoDB;
