/*
FILE: <NN>_<name>.sql
PROJECT: budgetdb finance warehouse
LAYER: raw / analytics
STEP: <step #>

PURPOSE
- What this file builds and why it exists (1–3 bullets)

CREATES / UPDATES (OBJECTS)
- Table(s):
- View(s):
- Index(es):

DEPENDS ON
- Upstream tables/views this file requires

HOW TO RUN
- Run the whole file top-to-bottom (or list blocks if needed)

VALIDATION QUERIES (RUN AFTER)
- Include 2–5 short checks

NOTES / DESIGN CHOICES
- Any important assumptions (keys, joining logic, time grain, etc.)
*/



-- 17_step4_employee_normalization.sql
-- Step 4:
--  1) Put commission_2025 in raw schema (if still in public)
--  2) Create a canonical employee dimension (analytics.dim_employee)
--  3) Create analytics.commission_2025_clean with normalized join keys
--  4) Rebuild the joined view to use dim_employee + mapping table
--
-- Notes:
--  - We keep raw as raw.
--  - We compute deterministic normalized keys (no fuzzy matching).
--  - employee_id (email) remains the authoritative employee key.

BEGIN;

CREATE SCHEMA IF NOT EXISTS raw;
CREATE SCHEMA IF NOT EXISTS analytics;

-- 1) Ensure raw.commission_2025 exists (move from public if needed)
DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public' AND table_name = 'commission_2025'
  ) THEN
    EXECUTE 'ALTER TABLE public.commission_2025 SET SCHEMA raw';
  END IF;
END $$;

-- 2) Canonical employee dimension from payroll clean table
CREATE TABLE IF NOT EXISTS analytics.dim_employee (
  employee_id TEXT PRIMARY KEY,              -- email
  first_name TEXT,
  last_name  TEXT,
  full_name  TEXT,                           -- canonical "First Last"
  team       TEXT,
  role_title TEXT,
  employment_type TEXT,
  base_salary_annual NUMERIC,
  benefits_rate NUMERIC,
  start_date DATE,
  end_date   DATE,
  name_key_full TEXT,                        -- normalized full name key
  name_key_firstlast TEXT,                   -- normalized first+last token key
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Upsert from analytics.budget_master_clean
-- (If payroll changes, rerun this file and dim_employee refreshes)
INSERT INTO analytics.dim_employee (
  employee_id, first_name, last_name, full_name,
  team, role_title, employment_type, base_salary_annual, benefits_rate,
  start_date, end_date,
  name_key_full, name_key_firstlast,
  updated_at
)
SELECT
  b.employee_id,
  b.first_name,
  b.last_name,
  TRIM(COALESCE(b.first_name,'') || ' ' || COALESCE(b.last_name,'')) AS full_name,
  b.team,
  b.role_title,
  b.employment_type,
  b.base_salary_annual,
  b.benefits_rate,
  b.start_date,
  b.end_date,

  -- full name normalized key
  LOWER(
    REGEXP_REPLACE(
      TRIM(COALESCE(b.first_name,'') || ' ' || COALESCE(b.last_name,'')),
      '[^a-z0-9]+',
      '',
      'g'
    )
  ) AS name_key_full,

  -- first + last token normalized key (handles middle names / extra spaces)
  LOWER(
    REGEXP_REPLACE(
      TRIM(
        (REGEXP_SPLIT_TO_ARRAY(TRIM(COALESCE(b.first_name,'') || ' ' || COALESCE(b.last_name,'')), '\s+'))[1]
        || ' ' ||
        (REGEXP_SPLIT_TO_ARRAY(TRIM(COALESCE(b.first_name,'') || ' ' || COALESCE(b.last_name,'')), '\s+'))[
          ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(TRIM(COALESCE(b.first_name,'') || ' ' || COALESCE(b.last_name,'')), '\s+'), 1)
        ]
      ),
      '[^a-z0-9]+',
      '',
      'g'
    )
  ) AS name_key_firstlast,

  NOW() AS updated_at
FROM analytics.budget_master_clean b
WHERE b.employee_id IS NOT NULL
ON CONFLICT (employee_id)
DO UPDATE SET
  first_name = EXCLUDED.first_name,
  last_name  = EXCLUDED.last_name,
  full_name  = EXCLUDED.full_name,
  team       = EXCLUDED.team,
  role_title = EXCLUDED.role_title,
  employment_type = EXCLUDED.employment_type,
  base_salary_annual = EXCLUDED.base_salary_annual,
  benefits_rate = EXCLUDED.benefits_rate,
  start_date = EXCLUDED.start_date,
  end_date = EXCLUDED.end_date,
  name_key_full = EXCLUDED.name_key_full,
  name_key_firstlast = EXCLUDED.name_key_firstlast,
  updated_at = NOW();

CREATE INDEX IF NOT EXISTS idx_dim_employee_name_key_full
  ON analytics.dim_employee (name_key_full);

CREATE INDEX IF NOT EXISTS idx_dim_employee_name_key_firstlast
  ON analytics.dim_employee (name_key_firstlast);

-- 3) Commission clean table (typed + normalized name fields)
CREATE TABLE IF NOT EXISTS analytics.commission_2025_clean (
  employee_order NUMERIC,
  employee_name_raw TEXT,
  employee_name_clean TEXT,
  role_code TEXT,
  role_group TEXT,
  commission_year INT,
  commission_month INT,
  commission_month_date DATE,
  commission_amount NUMERIC,
  commission_name_key_full TEXT,
  commission_name_key_firstlast TEXT,
  loaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Refresh commission_2025_clean from raw each time (simple & safe for small volumes)
TRUNCATE TABLE analytics.commission_2025_clean;

INSERT INTO analytics.commission_2025_clean (
  employee_order,
  employee_name_raw,
  employee_name_clean,
  role_code,
  role_group,
  commission_year,
  commission_month,
  commission_month_date,
  commission_amount,
  commission_name_key_full,
  commission_name_key_firstlast
)
SELECT
  c.employee_order,
  c.employee_name AS employee_name_raw,

  -- strip parentheses tags: "Name (EE-CSM)" -> "Name"
  TRIM(SPLIT_PART(COALESCE(c.employee_name,''), '(', 1)) AS employee_name_clean,

  c.role_code,
  c.role_group,
  c.commission_year,
  c.commission_month,
  c.commission_month_date,
  c.commission_amount,

  -- normalized full cleaned name key
  LOWER(
    REGEXP_REPLACE(
      TRIM(SPLIT_PART(COALESCE(c.employee_name,''), '(', 1)),
      '[^a-z0-9]+',
      '',
      'g'
    )
  ) AS commission_name_key_full,

  -- normalized first+last tokens key
  LOWER(
    REGEXP_REPLACE(
      TRIM(
        (REGEXP_SPLIT_TO_ARRAY(TRIM(SPLIT_PART(COALESCE(c.employee_name,''), '(', 1)), '\s+'))[1]
        || ' ' ||
        (REGEXP_SPLIT_TO_ARRAY(TRIM(SPLIT_PART(COALESCE(c.employee_name,''), '(', 1)), '\s+'))[
          ARRAY_LENGTH(REGEXP_SPLIT_TO_ARRAY(TRIM(SPLIT_PART(COALESCE(c.employee_name,''), '(', 1)), '\s+'), 1)
        ]
      ),
      '[^a-z0-9]+',
      '',
      'g'
    )
  ) AS commission_name_key_firstlast

FROM raw.commission_2025 c;

CREATE INDEX IF NOT EXISTS idx_commission_2025_clean_name_key_full
  ON analytics.commission_2025_clean (commission_name_key_full);

CREATE INDEX IF NOT EXISTS idx_commission_2025_clean_name_key_firstlast
  ON analytics.commission_2025_clean (commission_name_key_firstlast);

-- 4) Ensure mapping bridge exists (from Step 1)
CREATE TABLE IF NOT EXISTS analytics.employee_commission_name_map (
  commission_employee_name_raw TEXT PRIMARY KEY,
  employee_id TEXT NOT NULL,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_employee_commission_name_map_employee_id
  ON analytics.employee_commission_name_map (employee_id);

-- 5) Rebuild joined view using clean commission + dim_employee
CREATE OR REPLACE VIEW analytics.commission_2025_joined_payroll AS
WITH mapped AS (
  SELECT
    cc.*,
    m.employee_id AS mapped_employee_id
  FROM analytics.commission_2025_clean cc
  LEFT JOIN analytics.employee_commission_name_map m
    ON m.commission_employee_name_raw = cc.employee_name_raw
)
SELECT
  m.employee_order,
  m.employee_name_raw AS employee_name,
  m.employee_name_clean,
  m.role_code,
  m.role_group,
  m.commission_year,
  m.commission_month,
  m.commission_month_date,
  m.commission_amount,

  -- join diagnostics
  CASE
    WHEN m.mapped_employee_id IS NOT NULL THEN 'MAP_OVERRIDE'
    WHEN e_full.employee_id IS NOT NULL THEN 'NAME_KEY_EXACT'
    WHEN e_fl.employee_id IS NOT NULL THEN 'NAME_KEY_FIRSTLAST'
    ELSE 'UNMATCHED'
  END AS match_method,

  CASE
    WHEN COALESCE(m.mapped_employee_id, e_full.employee_id, e_fl.employee_id) IS NULL THEN 'UNMATCHED'
    ELSE 'MATCHED'
  END AS join_status,

  COALESCE(m.mapped_employee_id, e_full.employee_id, e_fl.employee_id) AS employee_id,

  -- employee attributes (from dim)
  COALESCE(e_full.first_name, e_fl.first_name) AS first_name,
  COALESCE(e_full.last_name,  e_fl.last_name)  AS last_name,
  COALESCE(e_full.team,       e_fl.team)       AS team,
  COALESCE(e_full.role_title, e_fl.role_title) AS role_title,
  COALESCE(e_full.employment_type, e_fl.employment_type) AS employment_type,
  COALESCE(e_full.base_salary_annual, e_fl.base_salary_annual) AS base_salary_annual,
  COALESCE(e_full.benefits_rate, e_fl.benefits_rate) AS benefits_rate,
  COALESCE(e_full.start_date, e_fl.start_date) AS start_date,
  COALESCE(e_full.end_date,   e_fl.end_date)   AS end_date

FROM mapped m
LEFT JOIN analytics.dim_employee e_full
  ON m.mapped_employee_id IS NULL
 AND m.commission_name_key_full = e_full.name_key_full
LEFT JOIN analytics.dim_employee e_fl
  ON m.mapped_employee_id IS NULL
 AND e_full.employee_id IS NULL
 AND m.commission_name_key_firstlast = e_fl.name_key_firstlast;

COMMIT;

-- QA quick checks (run manually)
-- SELECT match_method, COUNT(*) FROM analytics.commission_2025_joined_payroll GROUP BY 1 ORDER BY 2 DESC;
-- SELECT SUM(commission_amount) FROM raw.commission_2025;
-- SELECT SUM(commission_amount) FROM analytics.commission_2025_joined_payroll;


SELECT
  (SELECT SUM(commission_amount) FROM raw.commission_2025) AS raw_total,
  (SELECT SUM(commission_amount) FROM analytics.commission_2025_joined_payroll) AS joined_total;


SELECT match_method, COUNT(*)
FROM analytics.commission_2025_joined_payroll
GROUP BY 1
ORDER BY 2 DESC;