-- sql/wayleave_06_v2_refactor.sql
-- V2 refactor support: wayleave teams lookup.
-- Run: psql -U postgres -d netplanner -f sql/wayleave_06_v2_refactor.sql

BEGIN;

CREATE TABLE IF NOT EXISTS wayleave.teams (
    id            serial PRIMARY KEY,
    team_name     varchar(100) NOT NULL UNIQUE,
    display_order integer NOT NULL DEFAULT 0,
    is_active     boolean NOT NULL DEFAULT true,
    created_datetime timestamp NOT NULL DEFAULT now()
);

-- Seed teams from any distinct values already sitting in agreements.wayleave_team,
-- then add a starter set so the dropdown isn't empty on a fresh install.
INSERT INTO wayleave.teams (team_name, display_order)
SELECT DISTINCT wayleave_team, 100
FROM wayleave.agreements
WHERE wayleave_team IS NOT NULL AND wayleave_team <> ''
ON CONFLICT (team_name) DO NOTHING;

INSERT INTO wayleave.teams (team_name, display_order) VALUES
    ('Internal',       10),
    ('External',       20),
    ('Legal',          30),
    ('Property',       40)
ON CONFLICT (team_name) DO NOTHING;

COMMIT;
