// 12-21-2022

Proposed Tables:

[wayfair_teams].[tbl_teams]
    - [team_id] INT IDENTITY (or SEQUENCE?)
    (https://www.sqlshack.com/difference-between-identity-sequence-in-sql-server/) -- this could be interesting
    - [team_name] [varchar](max) NOT NULL -- // we can't guarantee uniqueness for the names outside our system
    - [team_type] [int] NOT NULL, (ENUM['PUBLIC', 'PRIVATE', 'ADGROUP', 'WORKGROUP' ])
    
    - [team_source] [varchar] (ENUM['wayfair-teams','adgroups','workgroups']) 
    - [team_identifier] [varchar] -- this would contain the identifier for adgroups; if those are always 1-1 then we don't need either the tblAdGroup or the join the table;
    
    - [team_desciption] [varchar](max) NULL,
    - [team_icon_id] [int] NULL,
    - [team_created] [datetime] NULL,
    - [team_isactive] [bit] NULL, -- I wonder if we can hold off on this property; it's kind of a useless concept for the teams that we don't own (adgroups)
    - [team_org_label] [int] NOT NULL  ('atomic', 'pod', 'superpod'...) -- I kind of hate it but it's probably the easiest for now


[wayfair_teams].[tbl_employee_memberOf_team]
    - source_id
    - target_id
    -- technically, any other info is redundant;
    
[wayfair_teams].[tbl_team_contains_team]  -- current definition of "hierarchy" 
    - source_id
    - target_id
    -- any other info is probably also redundant here;

-- rules of relationship of verb CONTAINS: acyclical, each node can have one and only one parent;


[wayfair_teams].[tbl_team_communication]

    - team_id INT
    - mode ENUM ['slack', 'email']
    - identifier [varchar]
    - labels: ["kronos-detect", "kronos-conduct", "primary"]

team_id communication_mode identifier labels

123 slack SLF4654G ["kronos-detect", "kronos-conduct"]
234 email dashinfra ["primary"]

Other tables:

- picklist for icons
- picklist for types [PRIVATE, PUBLIC, ADGROUP]
- UI labels (those could live on a team table too, but it depends on how it's used, so won't change the structure now)


Other Notes

is_admin: let's figure out how we are going to deal with access management;

1. For PUBLIC teams, add/remove directly
2. FOR PRIVATE teams, admin have the permissions to add/remove members of the team
3. ADGROUPS,  


--------------------------------------------------------------------------------
# Teams data Tables


Existing tables:
1. tblplbiteam

2. tblplteamtype [atomic, pod, superpod, misc] -- "utility labels for now" or [could be a required on tbl_team]

4. tbljoinemployeebiteam
5. tbljointeamlabels [UI labels]
6. tbljointeamhierarchy
7. tblplicons
8. tblTeamSlackWebhooks -- this one is going to die
9. tbljointeamgroup (join teams with AD groups-- not needed in the new wayfair teams model?)
10. tblAdGroup



Database: wayfair_teams


Table: tbl_teams

Description: Meta data for teams

columns: 

team_id

team_type: Enum[PRIVATE, PUBLIC, ADGROUP, SECURITY etc.]  REQUIRED
team_label: Enum["atomic", "pod", "superpod", "misc", "deparment", "permission"] REQUIRED

// team_labels: [{label: atomic, ...}]  --- this is another option;

team_name
team_description
~~team_type_id~~
team_icon_id
team_created
team_isactive

~~--team_membership_id-   team_type: Enum[PRIVATE, PUBLIC, ADGROUP, SECURITY etc.]~~

PRIVATE/PUBLIC -- we are the system of record so we own the rules;
ADGROUPS - we are not the system of record, so we only replicate as read-only;



DDL:

CREATE TABLE [wayfair_teams].[tbl_teams]
(
[team_id] [int] NOT NULL,
[team_name] [varchar](100) NOT NULL,
[team_desciption] [varchar](max) NULL,
[team_type] [int] NOT NULL,
[team_icon_id] [int] NOT NULL,
[team_created] [datetime] NULL,
[team_isactive] [bit] NULL,
[team_label] [int] NOT NULL
)

Table: tbl_team_type

columns:

type_id ENUM[1,2,3]
type_name ENUM[PUBLIC, PRIVATE, ADGROUP]
type_description - 'describe it'

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_memberships]
(
[membership_id] [int] NOT NULL,
[membership_name] [varchar](100) NOT NULL,
[membership_description] [varchar](max) NULL
)

Tomorrow's Problem

~~Table: tbl_team_membership_rules~~

columns:

membership_id
membership_rule

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_membership_rules]
(
[membership_id] [int] NOT NULL,
[membership_rule] [varchar](100) NOT NULL
)

Table: tbl_team_labels --- will keep this on tbl_team (either as labels or its own column)

columns:

type_id
type_label(atomic, pod, superpod, department )

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_types]
(
[type_id] [int] NOT NULL,
[type_name] [varchar](100) NOT NULL,
)

Table: tbl_team_channels

columns:

team_id
slack_id
email_id
communication_type

---- 

team_id
communication_mode ENUM[slack, email, etc....]
identifier: VARCHAR   (but could be any verifiable structure)
labels: ["kronos-detect", "kronos-conduct", "primary"]


// Slack channel IDs from Slack-api

team_id communication_mode identifier labels

123 slack SLF4654G ["kronos-detect", "kronos-conduct"]
234 email dashinfra ["primary"]
252

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_channels]
(
[team_id] [int] NOT NULL,
[slack_id] [varchar](20) NULL,
[email_id] [varchar](50) NULL,
[communication_type] [varchar](100) NULL,
)

tblAdGroups 
adgroup_id: 123
adgroup_name: "ad_group_name"

tbl_Member_Of

source_id
source_type [wayfair_team_private, wayfair_team_public, employee, adgroup] (not sure)
target_id
target_type [wayfair_team_private, wayfair_team_public, employee, adgroup]

(--extra recursion step--)  [# let's come to this one]

// preference: stay as close to the native data structrues as possible; 

Questions: 

1. Do we want to put ad group members directly into their correponding wayfair-teams? or do we want to put them into ad-groups and join ad-groups to wayfair teams?

2. Wayfair-Team-to-AdGroup is 1:many right now (I think) -- this means that if we put people directly into wayfair team, we don't know which adgroup they came from; but this is also likely a dying concept; we need to investigate if there are still any reasons for us to maintain 1-many mappigns between adgroups and wayfair teams. 




Table:   -- will only contain the directly the data for the teams we are the system of record for (PRIVATE, PUBLIC) -- or this is a deep hierarchy table (?)

columns:

team_id
employee_id
is_team_admin
employee_team_join_data

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_employees]
(
[team_id] [int] NOT NULL,
[employee_id] [int] NOT NULL,
[is_team_admin] [bit] NULL,
[employee_team_join_date] [datetime] NULL,
)

Table: tbl_team_hierarchy  (we need a name for this hierarchy) -- this is some user-defined hierarchy; 

columns: 

team_source_id
team_target_id
hierarchy_created


DDL:

CREATE TABLE [wayfair_teams].[tbl_team_hierarchy]
(
[hierarchy_created] [datetime] NULL,
[source_team_id] [int] NOT NULL,
[target_team_id] [int] NOT NULL,
)

Table: tbl_team_icons

columns:

icon_id
icon_name

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_icons]
(
[icon_id] [int] NOT NULL,
[icon_name] [varchar](20) NULL,
)

Table: tbl_team_labels  --- should these labels include "atomic","pod", "superpod"? (it's ok if they don't) --- could this be a JSON colum on tbl_team?

columns:

team_id
label_id
label_name

DDL:

CREATE TABLE [wayfair_teams].[tbl_team_labels]
(
[team_id] [int] NOT NULL,
[label_id] [int] NOT NULL,
[label_name] [varchar](20) NULL,
)