-- PROPOSTA NAO EXECUTADA - LAHub catalogo externo
-- Fonte oficial: https://catalogo-dental.lahub.com.br
-- O catalogo e separado de ofertas comerciais para nao inventar fornecedor/estoque.

CREATE TABLE catalog_sources (
  id CHAR(36) PRIMARY KEY,
  code VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(160) NOT NULL,
  base_url VARCHAR(1000) NOT NULL,
  seller_origin VARCHAR(160) NULL,
  rights_status VARCHAR(32) NOT NULL DEFAULT 'pending_review',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
);

CREATE TABLE catalog_import_batches (
  id CHAR(36) PRIMARY KEY,
  source_id CHAR(36) NOT NULL,
  source_snapshot_date DATE NOT NULL,
  source_url VARCHAR(1000) NOT NULL,
  source_sha256 CHAR(64) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'staged',
  rows_total INT UNSIGNED NOT NULL DEFAULT 0,
  rows_accepted INT UNSIGNED NOT NULL DEFAULT 0,
  rows_quarantined INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  completed_at TIMESTAMP(3) NULL,
  CONSTRAINT fk_catalog_batch_source FOREIGN KEY (source_id) REFERENCES catalog_sources(id)
);

CREATE TABLE catalog_products (
  id CHAR(36) PRIMARY KEY,
  source_id CHAR(36) NOT NULL,
  import_batch_id CHAR(36) NOT NULL,
  source_code VARCHAR(80) NOT NULL,
  parent_source_code VARCHAR(80) NULL,
  manufacturer_code VARCHAR(120) NULL,
  ean VARCHAR(32) NULL,
  anvisa_registration VARCHAR(80) NULL,
  name VARCHAR(255) NOT NULL,
  description_full LONGTEXT NOT NULL,
  brand VARCHAR(160) NULL,
  manufacturer_brand VARCHAR(160) NULL,
  seller_origin VARCHAR(160) NULL,
  category VARCHAR(180) NOT NULL,
  variant_label VARCHAR(255) NULL,
  validity_text VARCHAR(160) NULL,
  presentation TEXT NULL,
  material TEXT NULL,
  product_type TEXT NULL,
  model TEXT NULL,
  arch TEXT NULL,
  color TEXT NULL,
  size_text TEXT NULL,
  format_text TEXT NULL,
  volume_text TEXT NULL,
  composition LONGTEXT NULL,
  procedure_name TEXT NULL,
  professional_area TEXT NULL,
  specialty TEXT NULL,
  sale_price_cents BIGINT UNSIGNED NULL,
  pix_price_cents BIGINT UNSIGNED NULL,
  source_availability VARCHAR(40) NULL,
  source_product_url VARCHAR(1000) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_catalog_product_source_code (source_id, source_code),
  KEY idx_catalog_product_ean (ean),
  KEY idx_catalog_product_anvisa (anvisa_registration),
  KEY idx_catalog_product_parent (source_id, parent_source_code),
  CONSTRAINT fk_catalog_product_source FOREIGN KEY (source_id) REFERENCES catalog_sources(id),
  CONSTRAINT fk_catalog_product_batch FOREIGN KEY (import_batch_id) REFERENCES catalog_import_batches(id)
);

CREATE TABLE catalog_product_images (
  id CHAR(36) PRIMARY KEY,
  catalog_product_id CHAR(36) NOT NULL,
  url VARCHAR(1000) NOT NULL,
  position SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_catalog_product_image (catalog_product_id, url(255)),
  CONSTRAINT fk_catalog_image_product FOREIGN KEY (catalog_product_id) REFERENCES catalog_products(id)
);

-- Ofertas comerciais ficam separadas e so nascem apos definir loja, fornecedor, estoque e fulfillment.
CREATE TABLE store_product_offers (
  id CHAR(36) PRIMARY KEY,
  store_id CHAR(36) NOT NULL,
  catalog_product_id CHAR(36) NOT NULL,
  seller_sku VARCHAR(80) NULL,
  price_cents BIGINT UNSIGNED NOT NULL,
  stock_quantity INT UNSIGNED NULL,
  fulfillment_mode VARCHAR(40) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  UNIQUE KEY uq_store_catalog_offer (store_id, catalog_product_id),
  CONSTRAINT fk_offer_store FOREIGN KEY (store_id) REFERENCES stores(id),
  CONSTRAINT fk_offer_catalog_product FOREIGN KEY (catalog_product_id) REFERENCES catalog_products(id)
);

-- ROLLBACK DE LOTE (executar em transacao; nao apagar os arquivos oficiais):
-- UPDATE catalog_products SET active = 0 WHERE import_batch_id = :batch_id;
-- UPDATE catalog_import_batches SET status = 'rolled_back' WHERE id = :batch_id;