-- 1) Cria o dataset RAW 
CREATE SCHEMA IF NOT EXISTS `meli_case_raw`;

-- 2) Tabelas EXTERNAS (CSV no GCS) - leitura direta do GCS

-- 2.1) INBOUND OPERATIONS (BT_FBM_INBOUND_OPERATIONS_AGG)
CREATE OR REPLACE EXTERNAL TABLE `meli_case_raw.raw_inbound_operations_ext`
OPTIONS (
  format = 'CSV',
  uris = ['gs://meli-inbound-case/raw/inbound_operations/2025-08-11/*.csv.gz'],
  compression = 'GZIP',
  skip_leading_rows = 1,
  field_delimiter = ',',
  encoding = 'UTF-8',
  quote = '"',
  allow_quoted_newlines = TRUE
);

-- 2.2) FACILITIES (LK_SHP_FACILITIES)
CREATE OR REPLACE EXTERNAL TABLE `meli_case_raw.raw_shp_facilities_ext`
OPTIONS (
  format = 'CSV',
  uris = ['gs://meli-inbound-case/raw/facilities/2025-08-11/*.csv.gz'],
  compression = 'GZIP',
  skip_leading_rows = 1,
  field_delimiter = ',',
  encoding = 'UTF-8',
  quote = '"',
  allow_quoted_newlines = TRUE
);

-- 2.3) SELLERS (LK_SF_COMMERCIAL_SELLERS_DATA)
CREATE OR REPLACE EXTERNAL TABLE `meli_case_raw.raw_sellers_data_ext`
OPTIONS (
  format = 'CSV',
  uris = ['gs://meli-inbound-case/raw/sellers/2025-08-11/*.csv.gz'],
  compression = 'GZIP',
  skip_leading_rows = 1,
  field_delimiter = ',',
  encoding = 'UTF-8',
  quote = '"',
  allow_quoted_newlines = TRUE
);
