Which accounts approaching renewal have overdue invoices and open tickets?

-- Turn a company name into a matching key:
-- "NORTHWIND ANALYTICS B.V." and "Northwind Analytics" both become "northwindanalytics"
CREATE OR REPLACE FUNCTION name_key(raw text) RETURNS text AS $$
  SELECT replace(
           regexp_replace(
             regexp_replace(
               ' ' || trim(regexp_replace(lower(raw), '[^a-z0-9]+', ' ', 'g')) || ' ',
               ' (b v|bv|ltd|inc|gmbh|llc|as) $', ' '),   -- drop a legal suffix
             ' and ', ' ', 'g'),                           -- "and" and "&" match
           ' ', '')
$$ LANGUAGE sql IMMUTABLE;

-- Business email domain only; personal addresses can't identify a company
CREATE OR REPLACE FUNCTION email_domain(email text) RETURNS text AS $$
  SELECT CASE
           WHEN lower(split_part(email, '@', 2)) IN ('gmail.com', 'outlook.com', 'hotmail.com', 'icloud.com')
           THEN NULL
           ELSE lower(split_part(email, '@', 2))
         END
$$ LANGUAGE sql IMMUTABLE;

-- The identity layer: one row per real customer, owned by us, not by any tool
CREATE SEQUENCE customer_seq;

CREATE TABLE customers (
  customer_id      text PRIMARY KEY DEFAULT 'CUST_' || lpad(nextval('customer_seq')::text, 6, '0'),
  canonical_name   text NOT NULL,     -- the name we agree to use
  canonical_domain text,              -- decided once, kept even if a source loses it
  created_at       timestamptz NOT NULL DEFAULT now()
);

-- Which source record belongs to which customer, how we know, and who confirmed it
CREATE TABLE customer_source_ids (
  customer_id  text NOT NULL REFERENCES customers,
  source       text NOT NULL,         -- 'crm', 'billing', 'support'
  source_id    text NOT NULL,         -- the native ID, for linking back to the tool
  match_method text NOT NULL,         -- 'seed', 'domain', 'name'
  status       text NOT NULL CHECK (status IN ('proposed', 'confirmed', 'rejected')),
  confirmed_by text,
  confirmed_at timestamptz,
  PRIMARY KEY (customer_id, source, source_id)
);

-- A source record can be CONFIRMED for one customer only
CREATE UNIQUE INDEX one_confirmed_customer_per_record
  ON customer_source_ids (source, source_id) WHERE status = 'confirmed';

-- Seed: every CRM company with a contract becomes a customer, once.
-- The canonical ID is ours; the CRM ID is kept as a source link.
WITH seeded AS MATERIALIZED (
  SELECT id AS crm_id, name, lower(domain) AS domain,
         'CUST_' || lpad(nextval('customer_seq')::text, 6, '0') AS customer_id
  FROM crm_companies
  WHERE renewal_date IS NOT NULL
),
new_customers AS (
  INSERT INTO customers (customer_id, canonical_name, canonical_domain)
  SELECT customer_id, name, domain FROM seeded
)
INSERT INTO customer_source_ids (customer_id, source, source_id, match_method, status, confirmed_by, confirmed_at)
SELECT customer_id, 'crm', crm_id, 'seed', 'confirmed', 'seed', now()
FROM seeded;

-- Propose links for every source record that isn't linked yet.
-- Domain matches are confirmed automatically; name matches wait for a person.
INSERT INTO customer_source_ids (customer_id, source, source_id, match_method, status, confirmed_by, confirmed_at)
SELECT c.customer_id, 'billing', b.id,
       CASE WHEN c.canonical_domain = email_domain(b.email) THEN 'domain' ELSE 'name' END,
       CASE WHEN c.canonical_domain = email_domain(b.email) THEN 'confirmed' ELSE 'proposed' END,
       CASE WHEN c.canonical_domain = email_domain(b.email) THEN 'rule:domain' END,
       CASE WHEN c.canonical_domain = email_domain(b.email) THEN now() END
FROM billing_customers b
JOIN customers c
  ON c.canonical_domain = email_domain(b.email) OR name_key(c.canonical_name) = name_key(b.name)
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l WHERE l.source = 'billing' AND l.source_id = b.id)
UNION ALL
SELECT c.customer_id, 'support', s.id,
       CASE WHEN c.canonical_domain = lower(s.domain) THEN 'domain' ELSE 'name' END,
       CASE WHEN c.canonical_domain = lower(s.domain) THEN 'confirmed' ELSE 'proposed' END,
       CASE WHEN c.canonical_domain = lower(s.domain) THEN 'rule:domain' END,
       CASE WHEN c.canonical_domain = lower(s.domain) THEN now() END
FROM support_orgs s
JOIN customers c
  ON c.canonical_domain = lower(s.domain) OR name_key(c.canonical_name) = name_key(s.name)
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l WHERE l.source = 'support' AND l.source_id = s.id)
UNION ALL
SELECT c.customer_id, 'crm', d.id, 'name', 'proposed', NULL, NULL     -- possible CRM duplicates
FROM crm_companies d
JOIN customers c ON name_key(c.canonical_name) = name_key(d.name)
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l WHERE l.source = 'crm' AND l.source_id = d.id);

-- The review queue: matches a person needs to confirm or reject
SELECT l.customer_id, c.canonical_name, c.canonical_domain,
       l.source, l.source_id, l.match_method,
       coalesce(b.name, s.name, d.name)    AS name_in_source,
       coalesce(b.email, s.domain, d.domain) AS contact_in_source
FROM customer_source_ids l
JOIN customers c USING (customer_id)
LEFT JOIN billing_customers b ON l.source = 'billing' AND b.id = l.source_id
LEFT JOIN support_orgs s      ON l.source = 'support' AND s.id = l.source_id
LEFT JOIN crm_companies d     ON l.source = 'crm'     AND d.id = l.source_id
WHERE l.status = 'proposed'
ORDER BY l.customer_id, l.source;

-- A person reviews the queue and decides
UPDATE customer_source_ids
SET status = 'confirmed', confirmed_by = 'Tim', confirmed_at = now()
WHERE (source, source_id) IN (('support', 'zd_10'), ('billing', 'cus_A5'), ('crm', 'hs_103'));

-- Source records with no confirmed customer yet: nothing should hide here for long
SELECT 'billing' AS source, b.id, b.name FROM billing_customers b
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l
                  WHERE l.source = 'billing' AND l.source_id = b.id AND l.status = 'confirmed')
UNION ALL
SELECT 'support', s.id, s.name FROM support_orgs s
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l
                  WHERE l.source = 'support' AND l.source_id = s.id AND l.status = 'confirmed')
UNION ALL
SELECT 'crm', d.id, d.name FROM crm_companies d
WHERE NOT EXISTS (SELECT 1 FROM customer_source_ids l
                  WHERE l.source = 'crm' AND l.source_id = d.id AND l.status = 'confirmed');

WITH params AS (
  SELECT DATE '2026-10-15' AS today         -- use CURRENT_DATE in production
),
links AS (                                  -- only confirmed identity counts
  SELECT customer_id, source, source_id
  FROM customer_source_ids
  WHERE status = 'confirmed'
),
renewal AS (                                -- definition: renewal date from the CRM contract record
  SELECT l.customer_id, min(c.renewal_date) AS renewal_date, min(c.owner) AS owner
  FROM links l
  JOIN crm_companies c ON l.source = 'crm' AND c.id = l.source_id
  WHERE c.renewal_date IS NOT NULL
  GROUP BY l.customer_id
),
overdue AS (                                -- definition: unpaid and past its due date
  SELECT l.customer_id,
         sum(i.amount_eur)          AS overdue_eur,
         max(p.today - i.due_date)  AS days_overdue,
         string_agg(i.id, ', ')     AS invoices
  FROM links l
  JOIN billing_invoices i ON l.source = 'billing' AND i.customer_id = l.source_id
  CROSS JOIN params p
  WHERE i.status = 'open' AND i.due_date < p.today
  GROUP BY l.customer_id
),
open_tickets AS (                           -- definition: waiting on us or on the customer
  SELECT l.customer_id,
         count(*)                    AS open_tickets,
         max(p.today - t.created_at) AS oldest_days,
         string_agg(t.id, ', ')      AS tickets
  FROM links l
  JOIN support_tickets t ON l.source = 'support' AND t.org_id = l.source_id
  CROSS JOIN params p
  WHERE t.status IN ('open', 'pending')
  GROUP BY l.customer_id
)
SELECT cu.customer_id,
       cu.canonical_name             AS customer,
       r.owner,
       r.renewal_date - p.today      AS renews_in_days,
       o.overdue_eur,
       o.days_overdue,
       t.open_tickets,
       t.oldest_days                 AS oldest_ticket_days,
       o.invoices                    AS evidence_invoices,
       t.tickets                     AS evidence_tickets
FROM customers cu
CROSS JOIN params p
JOIN renewal r      ON r.customer_id = cu.customer_id
JOIN overdue o      ON o.customer_id = cu.customer_id
JOIN open_tickets t ON t.customer_id = cu.customer_id
WHERE r.renewal_date BETWEEN p.today AND p.today + 90   -- definition: renewal window
ORDER BY renews_in_days;

-- CRM (HubSpot-style)
CREATE TABLE crm_companies (
  id text PRIMARY KEY, name text, domain text, renewal_date date, owner text);
INSERT INTO crm_companies VALUES
 ('hs_101','Northwind Analytics','northwind.io','2026-12-18','Sanne'),
 ('hs_102','Atlas & Rowe','atlasrowe.com','2026-11-25','Daan'),
 ('hs_103','Atlas and Rowe',NULL,NULL,'Daan'),
 ('hs_104','Kestrel Logistics','kestrel-logistics.eu','2027-03-14','Sanne'),
 ('hs_105','Fjord Studio','fjordstudio.no','2027-01-11','Daan'),
 ('hs_106','Mosaic Retail','mosaicretail.eu','2027-04-13','Sanne');

-- Billing (Stripe/Chargebee-style)
CREATE TABLE billing_customers (id text PRIMARY KEY, name text, email text);
INSERT INTO billing_customers VALUES
 ('cus_A1','NORTHWIND ANALYTICS B.V.','finance@northwind.io'),
 ('cus_A2','ATLAS & ROWE BV','ap@atlasrowe.com'),
 ('cus_A3','Kestrel Logistics Ltd','billing@kestrel-logistics.eu'),
 ('cus_A4','Fjord Studio AS','post@fjordstudio.no'),
 ('cus_A5','Mosaic Retail','jan@gmail.com');

CREATE TABLE billing_invoices (id text PRIMARY KEY, customer_id text, amount_eur numeric, due_date date, status text);
INSERT INTO billing_invoices VALUES
 ('INV-2041','cus_A1',1950,'2026-09-28','open'),
 ('INV-2042','cus_A2',3000,'2026-09-22','open'),
 ('INV-2043','cus_A3',1200,'2026-10-06','open'),
 ('INV-2044','cus_A4',800,'2026-10-01','paid'),
 ('INV-2045','cus_A5',750,'2026-10-20','open');

-- Support (Zendesk/Freshdesk-style)
CREATE TABLE support_orgs (id text PRIMARY KEY, name text, domain text);
INSERT INTO support_orgs VALUES
 ('zd_9','northwind.io','northwind.io'),
 ('zd_10','Atlas & Rowe',NULL),
 ('zd_11','Fjord Studio','fjordstudio.no'),
 ('zd_12','Mosaic Retail','mosaicretail.eu');

CREATE TABLE support_tickets (id text PRIMARY KEY, org_id text, status text, created_at date, subject text);
INSERT INTO support_tickets VALUES
 ('T-4170','zd_9','open','2026-09-30','Export keeps timing out'),
 ('T-4171','zd_9','pending','2026-10-03','Export keeps timing out (again)'),
 ('T-4172','zd_9','solved','2026-08-12','Invite new user'),
 ('T-4180','zd_10','open','2026-10-08','SSO login fails'),
 ('T-4181','zd_11','open','2026-10-02','Downgrade question'),
 ('T-4182','zd_12','open','2026-10-09','CSV import mapping');