-- =============================================================================
-- Rechnungsexport – Odoo 17 / PostgreSQL
-- Rechnungsdatum · Rechnungsnummer · Länder · Zahlart · USt-Nr. · Steuersplit
--
-- Modell : account.move (Kundenrechnungen und Gutschriften)
--
-- Parameter:
--     %(month)s   Abrechnungszeitraum als Text, z. B. '2026-SEP'
--
-- Aufruf (Odoo / psycopg2):
--     cr.execute(open('Rechnungsexport.sql').read(), {'month': '2026-SEP'})
--
-- In psql stattdessen:
--     \set month '2026-SEP'
--     ... AND am.invoice_date >= :'month'   (siehe params-CTE)
--
-- Hinweis zum Monatsfilter:
--     Das Rechnungsdatum wird auf einen vollen Kalendermonat gefiltert.
--     Der Monat wird locale-unabhängig in ein Datum umgerechnet; es werden
--     deutsche (MÄR, MAI, OKT, DEZ) und englische (MAR, MAY, OCT, DEC)
--     Dreibuchstaben-Kürzel akzeptiert.
--
-- Änderungen gegenüber der Vorversion (nur die fehlenden Felder ergänzt):
--     * Auftragsdatum  : aus sale_order.date_order (über invoice_origin)
--     * Zahldatum      : aus der letzten Ausgleichsbuchung der Debitorenzeile
--     * Lieferadresse Name / Lieferland : aus account_move.partner_shipping_id
--                         (Fallback: Rechnungspartner)
-- =============================================================================

WITH params AS (
    -- Kalendermonat aus %(month)s (z. B. '2026-SEP') ableiten.
    SELECT
        to_date(
            split_part(%(month)s, '-', 1) || '-' ||
            CASE upper(split_part(%(month)s, '-', 2))
                WHEN 'JAN' THEN '01' WHEN 'FEB' THEN '02'
                WHEN 'MÄR' THEN '03' WHEN 'MAR' THEN '03' WHEN 'APR' THEN '04'
                WHEN 'MAI' THEN '05' WHEN 'MAY' THEN '05' WHEN 'JUN' THEN '06'
                WHEN 'JUL' THEN '07' WHEN 'AUG' THEN '08' WHEN 'SEP' THEN '09'
                WHEN 'OKT' THEN '10' WHEN 'OCT' THEN '10' WHEN 'NOV' THEN '11'
                WHEN 'DEZ' THEN '12' WHEN 'DEC' THEN '12'
            END,
            'YYYY-MM'
        ) AS first_day
),

invoices AS (
    SELECT
        am.id,
        am.name,
        am.ref,
        am.move_type,
        am.invoice_date,
        am.invoice_origin,
        am.partner_id,
        am.partner_shipping_id,
        am.invoice_payment_term_id,
        am.payment_reference
    FROM account_move am
    CROSS JOIN params p
    WHERE am.invoice_date >= p.first_day
      AND am.invoice_date <  p.first_day + INTERVAL '1 month'
      AND am.move_type IN ('out_invoice', 'out_refund')
),

-- ---------------------------------------------------------------------------
-- tax summary: invoice line -> tax relation -> account_tax
-- ---------------------------------------------------------------------------
line_taxes AS (
    SELECT
        aml.move_id             AS invoice_id,
        at.amount               AS tax_rate,
        SUM(aml.price_subtotal) AS netto,
        SUM(aml.price_total)    AS brutto
    FROM invoices inv
    JOIN account_move_line aml
      ON aml.move_id = inv.id
    JOIN account_move_line_account_tax_rel amlatr
      ON amlatr.account_move_line_id = aml.id
    JOIN account_tax at
      ON at.id = amlatr.account_tax_id
    GROUP BY aml.move_id, at.amount
),

-- ---------------------------------------------------------------------------
-- tax split logic from Python:
--   threshold = 8 for CH, else 16
-- ---------------------------------------------------------------------------
tax_split AS (
    SELECT
        lt.invoice_id,

        MAX(CASE
            WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
              OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            THEN ROUND(lt.brutto::numeric, 2)
        END) AS brutto_normal,

        MAX(CASE
            WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
              OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            THEN ROUND(lt.tax_rate::numeric, 2)
        END) AS ust_satz_normal,

        MAX(CASE
            WHEN (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
              OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            THEN ROUND(lt.netto::numeric, 2)
        END) AS netto_normal,

        MAX(CASE
            WHEN NOT (
                (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
                OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            )
            THEN ROUND(lt.brutto::numeric, 2)
        END) AS brutto_erm,

        MAX(CASE
            WHEN NOT (
                (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
                OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            )
            THEN ROUND(lt.tax_rate::numeric, 2)
        END) AS ust_satz_erm,

        MAX(CASE
            WHEN NOT (
                (rc.code = 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 8)
                OR (COALESCE(rc.code, '') <> 'CH' AND ROUND(lt.tax_rate::numeric, 2) >= 16)
            )
            THEN ROUND(lt.netto::numeric, 2)
        END) AS netto_erm

    FROM line_taxes lt
    JOIN invoices inv
      ON inv.id = lt.invoice_id
    JOIN res_partner rp
      ON rp.id = inv.partner_id
    LEFT JOIN res_country rc
      ON rc.id = rp.country_id
    GROUP BY lt.invoice_id
),

-- ---------------------------------------------------------------------------
-- shipping costs (product category id = 9, as in Python logic)
-- ---------------------------------------------------------------------------
shipping AS (
    SELECT
        aml.move_id AS invoice_id,
        ROUND(SUM(aml.price_total)::numeric, 2)    AS shipping_brutto,
        ROUND(SUM(aml.price_subtotal)::numeric, 2) AS shipping_netto
    FROM invoices inv
    JOIN account_move_line aml
      ON aml.move_id = inv.id
    JOIN product_product pp
      ON pp.id = aml.product_id
    JOIN product_template pt
      ON pt.id = pp.product_tmpl_id
    WHERE pt.categ_id = 9
    GROUP BY aml.move_id
),

invoice_tags AS (
    SELECT
        inv.id AS invoice_id,
        STRING_AGG(
            DISTINCT COALESCE(ct.name->>'en_US', ct.name->>'de_DE', ''),
            ', ' ORDER BY COALESCE(ct.name->>'en_US', ct.name->>'de_DE', '')
        ) AS tags
    FROM invoices inv
    JOIN sale_order so
      ON so.name = inv.invoice_origin
    JOIN sale_order_tag_rel sotr
      ON sotr.order_id = so.id
    JOIN crm_tag ct
      ON ct.id = sotr.tag_id
    GROUP BY inv.id
),

invoice_transaction_reference AS (
    SELECT
        rel.invoice_id,
        pt.provider_reference,
        ROW_NUMBER() OVER (
            PARTITION BY rel.invoice_id
            ORDER BY pt.id
        ) AS rn
    FROM account_invoice_transaction_rel rel
    JOIN payment_transaction pt
      ON pt.id = rel.transaction_id
),

-- ---------------------------------------------------------------------------
-- Auftragsdatum: Bestellung über invoice_origin (Verkaufsauftragsname) finden
-- ---------------------------------------------------------------------------
sale_info AS (
    SELECT
        inv.id             AS invoice_id,
        MIN(so.date_order) AS order_date
    FROM invoices inv
    JOIN sale_order so
      ON so.name = inv.invoice_origin
    GROUP BY inv.id
),

-- ---------------------------------------------------------------------------
-- Zahldatum: Datum der letzten Ausgleichsbuchung der Debitorenzeile.
-- account_partial_reconcile.max_date = jüngeres Datum der beiden Buchungen.
-- ---------------------------------------------------------------------------
invoice_payments AS (
    SELECT
        aml.move_id       AS invoice_id,
        MAX(apr.max_date) AS payment_date
    FROM invoices inv
    JOIN account_move_line aml
      ON aml.move_id = inv.id
    JOIN account_account aa
      ON aa.id = aml.account_id
     AND aa.account_type = 'asset_receivable'
    JOIN account_partial_reconcile apr
      ON apr.debit_move_id  = aml.id
      OR apr.credit_move_id = aml.id
    GROUP BY aml.move_id
)

SELECT
    TO_CHAR(inv.invoice_date, 'YYYY-MM-DD') AS "Rechnungsdatum",

    CASE
        WHEN inv.move_type = 'out_refund'
            THEN COALESCE(inv.name, inv.ref, '') || '-gs'
        ELSE COALESCE(inv.name, inv.ref, '')
    END AS "Rechnungsnummer",

    COALESCE(rc_inv.name->>'en_US', rc_inv.name->>'de_DE', '') AS "Rechnungsland",
    COALESCE(rc_inv.code, '')                                   AS "Rechnungsland Ländercode",
    COALESCE(apt.name->>'en_US', apt.name->>'de_DE', '')       AS "Zahlart",

    COALESCE(itr.provider_reference, '') AS "Zahlungsreference der Bestellung",
    COALESCE(partner.vat, '')           AS "USt. Id / VAT Id des Käufers (in Bestellung)",

    ROUND(COALESCE(ts.brutto_normal, 0) + COALESCE(ts.brutto_erm, 0), 2) AS "Gesamtpreis brutto",
    ROUND(COALESCE(ts.netto_normal, 0) + COALESCE(ts.netto_erm, 0), 2)   AS "Gesamtpreis netto",

    COALESCE(ts.brutto_normal, 0)   AS "Brutto",
    COALESCE(ts.brutto_erm, 0)      AS "Brutto erm",
    COALESCE(ts.ust_satz_normal, 0) AS "USt. Satz",
    COALESCE(ts.ust_satz_erm, 0)    AS "Ust. Satz erm",
    COALESCE(ts.netto_normal, 0)    AS "Netto Betrag",
    COALESCE(ts.netto_erm, 0)       AS "Netto Betrag erm",

    COALESCE(sh.shipping_brutto, 0) AS "Versandkosten brutto",
    COALESCE(sh.shipping_netto, 0)  AS "Versandkosten netto",

    COALESCE(TO_CHAR(si.order_date, 'YYYY-MM-DD'), '') AS "Auftragsdatum",
    COALESCE(inv.invoice_origin, '')                   AS "Bestellnummer",
    COALESCE(TO_CHAR(ip.payment_date, 'YYYY-MM-DD'), '') AS "Zahldatum",

    COALESCE(ship.name, partner.name, '') AS "Lieferadresse Name",
    COALESCE(it.tags, '')                 AS "Tag",
    COALESCE(
        rc_ship.name->>'en_US', rc_ship.name->>'de_DE',
        rc_inv.name->>'en_US',  rc_inv.name->>'de_DE',
        ''
    ) AS "Lieferland"

FROM invoices inv
JOIN res_partner partner
  ON partner.id = inv.partner_id
LEFT JOIN res_country rc_inv
  ON rc_inv.id = partner.country_id
LEFT JOIN res_partner ship
  ON ship.id = inv.partner_shipping_id
LEFT JOIN res_country rc_ship
  ON rc_ship.id = ship.country_id
LEFT JOIN account_payment_term apt
  ON apt.id = inv.invoice_payment_term_id
LEFT JOIN tax_split ts
  ON ts.invoice_id = inv.id
LEFT JOIN shipping sh
  ON sh.invoice_id = inv.id
LEFT JOIN invoice_tags it
  ON it.invoice_id = inv.id
LEFT JOIN invoice_transaction_reference itr
  ON itr.invoice_id = inv.id
 AND itr.rn = 1
LEFT JOIN sale_info si
  ON si.invoice_id = inv.id
LEFT JOIN invoice_payments ip
  ON ip.invoice_id = inv.id
ORDER BY inv.invoice_date, inv.name;