CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.customers_base_v` AS
SELECT
  SAFE_CAST(k.kunnr AS STRING) AS customer_id,
  LOWER(TRIM(a6.smtp_addr))    AS email,
  TRIM(k.name1)                AS first_name,
  TRIM(k.name2)                AS last_name,
  TRIM(k.telf1)                AS phone,
  TRIM(addr.nation)            AS country,
  TRIM(addr.region)            AS region,
  TRIM(addr.city1)             AS city,
  TRIM(addr.street)            AS address,
  SAFE_CAST(k.erdat AS DATE)   AS created_at,
  IFNULL(k.sperr, '')          AS status_raw
FROM `minuto-verde-cortex-poc.CDC_SAP.kna1` k
LEFT JOIN `minuto-verde-cortex-poc.CDC_SAP.adr6` a6
  ON a6.addrnumber = k.adrnr
LEFT JOIN `minuto-verde-cortex-poc.CDC_SAP.adrc` addr
  ON addr.addrnumber = k.adrnr;

CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.customers_sales_v` AS
SELECT
  SAFE_CAST(kunnr AS STRING) AS customer_id,
  vkorg,
  vtweg,
  spart,
  kdgrp,
  konda
FROM `minuto-verde-cortex-poc.CDC_SAP.knvv`;

CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.customers_finance_v` AS
SELECT
  SAFE_CAST(kunnr AS STRING) AS customer_id,
  bukrs,
  zterm,
  loevm
FROM `minuto-verde-cortex-poc.CDC_SAP.knb1`;

CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.customers_behavior_fin_v` AS
WITH ar AS (
  SELECT
    SAFE_CAST(bseg.kunnr AS STRING) AS customer_id,
    MAX(bkpf.budat) AS last_doc_date,
    COUNT(*) AS ar_postings,
    SUM(CAST(bseg.dmbtr AS NUMERIC)) AS total_amount_local
  FROM `minuto-verde-cortex-poc.CDC_SAP.bseg` bseg
  JOIN `minuto-verde-cortex-poc.CDC_SAP.bkpf` bkpf
    ON bkpf.bukrs = bseg.bukrs
   AND bkpf.gjahr = bseg.gjahr
   AND bkpf.belnr = bseg.belnr
  WHERE bseg.koart = 'D'
  GROUP BY customer_id
)
SELECT * FROM ar;

CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.customers_behavior_deliv_v` AS
WITH deliv AS (
  SELECT
    SAFE_CAST(likp.kunnr AS STRING) AS customer_id,
    MAX(likp.erdat) AS last_delivery_date,
    COUNT(DISTINCT likp.vbeln) AS deliveries_count
  FROM `minuto-verde-cortex-poc.CDC_SAP.likp`
  GROUP BY customer_id
)
SELECT * FROM deliv;

CREATE OR REPLACE TABLE `minuto-verde-cortex-poc.mkt_curated.customer_consent` (
  customer_id   STRING NOT NULL,
  consent_email BOOL NOT NULL,
  consent_date  TIMESTAMP,
  source_system STRING
);

CREATE OR REPLACE VIEW `minuto-verde-cortex-poc.mkt_curated.acoustic_profiles_v` AS
SELECT
  b.email               AS Email,
  b.first_name          AS FirstName,
  b.last_name           AS LastName,
  b.customer_id         AS CustomerID,
  b.phone               AS Phone,
  b.country, b.region, b.city, b.address,
  b.created_at          AS CreatedAt,
  COALESCE(c.consent_email, FALSE) AS Consent,
  c.consent_date        AS ConsentDate,
  c.source_system       AS SourceSystem,
  f.last_doc_date       AS LastDocDate_AR,
  f.ar_postings         AS ARPostings,
  f.total_amount_local  AS TotalAmountLocal,
  d.last_delivery_date  AS LastDeliveryDate,
  d.deliveries_count    AS DeliveriesCount
FROM `...customers_base_v` b
LEFT JOIN `...customer_consent` c ON c.customer_id = b.customer_id
LEFT JOIN `...customers_behavior_fin_v` f ON f.customer_id = b.customer_id
LEFT JOIN `...customers_behavior_deliv_v` d ON d.customer_id = b.customer_id;