// AS IMPLEMENTED in https://github.csnzoo.com/shared/wayfair-teams/pull/23/files

CREATE TABLE tbl_teams (
    id INT GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR (250) NOT NULL, 
    type_id  INT NOT NULL, -- wf_private, wf_public, adgroup; absorbs: tbljoinTeamGroup, tblADGroups
    description TEXT,
    org_level_id INT NOT NULL, -- pod, superpod, atomic etc.
    source_identifier JSONB NOT NULL, 
    source_properties JSONB NULL,
    icon_id INT,
    keywords JSONB,  -- this absorbs data_team_service..tbljoinTeamLabels
    
    _created TIMESTAMP NOT NULL,
    _updated TIMESTAMP,
    _archived TIMESTAMP,

    UNIQUE (name, type_id)
);

create table tbl_pl_team_types (
	type_id INT generated by default as identity,
	type_name varchar(50),
	primary key (type_id)
);

create table tbl_pl_icon (
	icon_id INT generated by default as identity,
	icon_name varchar(50),
	primary key (icon_id)
);


create table tbl_pl_team_org_level (
	org_level_id INT generated by default as identity,
	org_level_name varchar(50),
	org_level_description text,
	ordinal_position INT,
	primary key (org_level_id)
);

create table tbl_employee_memberOf_team (
	team_id INT,
	em_id INT,
	primary key (team_id, em_id)
);

create table tbl_team_memberOf_team (
	source_id INT not NULL,
	target_id INT not NULL,
	primary key (source_id, target_id)
);

create table tbl_team_uses_contact (
	team_id INT not null,
	contact_type INT not null,
	contact_record JSONB,
	contact_labels JSONB
);

create table tbl_pl_contact_type (
	contact_id INT generated always as identity,
	contact_name varchar(50)
);

# Wayfair Team tables

[wayfair_teams].[tbl_teams]

CREATE TABLE [wayfair_teams].[tbl_teams]
(
[team_id] [int] NOT NULL,
[team_name] [varchar](100) NOT NULL,
[team_type] [int] NOT NULL, -- ENUM[WF-PRIVATE, WF-PUBLIC, ADGROUP]

[team_source_properties] [varchar](max) NULL,
    
[team_identifier] [int] NULL,
    
[team_org_label] [int] NOT NULL, 
[team_icon_id] [int] NULL,
[team_created] [datetime] NULL,
[team_desciption] [varchar](max) NULL,
[team_isactive] [bit] NULL,
[team_label] [int] NOT NULL  -- ENUM['pod','superpod',...]
)


[wayfair_teams].[tbl_employee_memberof_team]

CREATE TABLE [wayfair_teams].[tbl_employee_memberof_team]
(
    [emp_id] [int] NOT NULL,
    [team_id] [int] NOT NULL,
   ~~ [is_admin] [bit] NULL~~  -- going to hold off on this one
)

[wayfair_teams].[tbl_team_contains_team]

CREATE TABLE [wayfair_teams].[tbl_team_contains_team]
(
    [source_id] [int] NOT NULL,
    [target_id] [int] NOT NULL,
)

[wayfair_teams].[tbl_team_communication]

CREATE TABLE [wayfair_teams].[tbl_team_communication]
(
    [team_id] [int] NOT NULL,
    [mode] [int] NOT NULL,
    [identifier] [varchar] NOT NULL,
    [label] [varchar] NULL
)

123  SLACK {id: JHFIOASF, name: my-channel}
453  EMAIL {email: [e-mail]}


[wayfair_teams].[tbl_pl_team_source_types] -- we probably don't need this yet

CREATE TABLE [wayfair_teams].[tbl_pl_team_source_types]
(
    [team_source_id] [int] NOT NULL,
    [team_source_name] [varchar] NOT NULL,
    [team_source_description] [varchar] NOT NULL
)



[wayfair_teams].[tbl_pl_team_labels]

CREATE TABLE [wayfair_teams].[tbl_pl_team_labels]
(
    [label_id] [int] NOT NULL,
    [label_name] [varchar] NULL,
    [label_description] [varchar] NULL,
    [ordinal_position] [int] NULL
)

[wayfair_teams].[tbl_pl_icons]

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