-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_data_projected" AS (
    -- Projection: Selecting 35 out of 36 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "allow_channelback",
        "assignee_id",
        "brand_id",
        "created_at",
        "description",
        "due_at",
        "external_id",
        "forum_topic_id",
        "group_id",
        "has_incidents",
        "is_public",
        "organization_id",
        "priority",
        "problem_id",
        "recipient",
        "requester_id",
        "status",
        "subject",
        "submitter_id",
        "system_client",
        "ticket_form_id",
        "type",
        "updated_at",
        "url",
        "via_channel",
        "via_source_from_id",
        "via_source_from_title",
        "via_source_rel",
        "via_source_to_address",
        "via_source_to_name",
        "merged_ticket_ids",
        "via_source_from_address",
        "followup_ids",
        "via_followup_source_id"
    FROM "ticket_data"
),

"ticket_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> ticket_id
    -- allow_channelback -> channelback_allowed
    -- created_at -> creation_timestamp
    -- description -> ticket_description
    -- due_at -> due_date
    -- recipient -> recipient_email
    -- status -> ticket_status
    -- subject -> ticket_subject
    -- type -> ticket_type
    -- updated_at -> last_update_timestamp
    -- url -> api_url
    -- via_channel -> submission_channel
    -- via_source_from_id -> ticket_source_id
    -- via_source_from_title -> ticket_source_name
    -- via_source_rel -> ticket_source_relationship
    -- via_source_to_address -> ticket_recipient_address
    -- via_source_to_name -> ticket_recipient_name
    -- via_source_from_address -> source_email_address
    -- via_followup_source_id -> followup_source_id
    SELECT 
        "id" AS "ticket_id",
        "allow_channelback" AS "channelback_allowed",
        "assignee_id",
        "brand_id",
        "created_at" AS "creation_timestamp",
        "description" AS "ticket_description",
        "due_at" AS "due_date",
        "external_id",
        "forum_topic_id",
        "group_id",
        "has_incidents",
        "is_public",
        "organization_id",
        "priority",
        "problem_id",
        "recipient" AS "recipient_email",
        "requester_id",
        "status" AS "ticket_status",
        "subject" AS "ticket_subject",
        "submitter_id",
        "system_client",
        "ticket_form_id",
        "type" AS "ticket_type",
        "updated_at" AS "last_update_timestamp",
        "url" AS "api_url",
        "via_channel" AS "submission_channel",
        "via_source_from_id" AS "ticket_source_id",
        "via_source_from_title" AS "ticket_source_name",
        "via_source_rel" AS "ticket_source_relationship",
        "via_source_to_address" AS "ticket_recipient_address",
        "via_source_to_name" AS "ticket_recipient_name",
        "merged_ticket_ids",
        "via_source_from_address" AS "source_email_address",
        "followup_ids",
        "via_followup_source_id" AS "followup_source_id"
    FROM "ticket_data_projected"
),

"ticket_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- ticket_description: The problem is that 'description1' appears to be a placeholder value rather than actual ticket content. It's unusual because real ticket descriptions would typically contain varied and specific information about different issues or requests. The presence of only this single, generic value suggests that actual description data is missing or was not properly imported. In this case, there isn't a "correct" value to map to, as we don't have the real descriptions. 
    -- ticket_subject: The problem is that 'subject1' appears to be a placeholder value rather than actual ticket subject content. In a real dataset, ticket subjects would typically contain varied, descriptive text related to the issue being reported. The correct values should be actual ticket subject content, but since we don't have that information, we can't map it to a specific correct value. 
    -- ticket_recipient_address: The problem is that 'X' is not a valid email address format and likely represents missing or placeholder data. The correct value for missing or placeholder data in an email address field should be an empty string. 
    -- merged_ticket_ids: The problem is that the values in the merged_ticket_ids column are string representations of lists rather than actual list values. The correct values should be actual Python lists. The '[]' represents an empty list, while '[1967]' represents a list containing a single integer value. 
    SELECT
        "ticket_id",
        "channelback_allowed",
        "assignee_id",
        "brand_id",
        "creation_timestamp",
        CASE
            WHEN "ticket_description" = '''description1''' THEN ''''
            ELSE "ticket_description"
        END AS "ticket_description",
        "due_date",
        "external_id",
        "forum_topic_id",
        "group_id",
        "has_incidents",
        "is_public",
        "organization_id",
        "priority",
        "problem_id",
        "recipient_email",
        "requester_id",
        "ticket_status",
        CASE
            WHEN "ticket_subject" = '''subject1''' THEN ''''
            ELSE "ticket_subject"
        END AS "ticket_subject",
        "submitter_id",
        "system_client",
        "ticket_form_id",
        "ticket_type",
        "last_update_timestamp",
        "api_url",
        "submission_channel",
        "ticket_source_id",
        "ticket_source_name",
        "ticket_source_relationship",
        CASE
            WHEN "ticket_recipient_address" = '''X''' THEN ''''
            ELSE "ticket_recipient_address"
        END AS "ticket_recipient_address",
        "ticket_recipient_name",
        CASE
            WHEN "merged_ticket_ids" = '''[]''' THEN '[]'
            WHEN "merged_ticket_ids" = '''[1967]''' THEN '[1967]'
            ELSE "merged_ticket_ids"
        END AS "merged_ticket_ids",
        "source_email_address",
        "followup_ids",
        "followup_source_id"
    FROM "ticket_data_projected_renamed"
),

"ticket_data_projected_renamed_cleaned_null" AS (
    -- NULL Imputation: Impute Null to Disguised Missing Values
    -- ticket_recipient_address: ['X']
    -- merged_ticket_ids: ['[]']
    SELECT 
        CASE
            WHEN "ticket_recipient_address" = 'X' THEN NULL
            ELSE "ticket_recipient_address"
        END AS "ticket_recipient_address",
        CASE
            WHEN "merged_ticket_ids" = '[]' THEN NULL
            ELSE "merged_ticket_ids"
        END AS "merged_ticket_ids",
        "forum_topic_id",
        "last_update_timestamp",
        "system_client",
        "is_public",
        "due_date",
        "ticket_recipient_name",
        "followup_source_id",
        "creation_timestamp",
        "api_url",
        "has_incidents",
        "source_email_address",
        "ticket_description",
        "priority",
        "recipient_email",
        "ticket_subject",
        "submitter_id",
        "ticket_source_name",
        "assignee_id",
        "followup_ids",
        "organization_id",
        "ticket_source_relationship",
        "problem_id",
        "external_id",
        "ticket_status",
        "ticket_source_id",
        "brand_id",
        "submission_channel",
        "channelback_allowed",
        "ticket_id",
        "requester_id",
        "ticket_type",
        "ticket_form_id",
        "group_id"
    FROM "ticket_data_projected_renamed_cleaned"
),

"ticket_data_projected_renamed_cleaned_null_casted" AS (
    -- Column Type Casting: 
    -- assignee_id: from DECIMAL to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- due_date: from DECIMAL to DATE
    -- external_id: from DECIMAL to VARCHAR
    -- followup_ids: from VARCHAR to ARRAY
    -- followup_source_id: from DECIMAL to VARCHAR
    -- forum_topic_id: from DECIMAL to VARCHAR
    -- last_update_timestamp: from VARCHAR to TIMESTAMP
    -- merged_ticket_ids: from VARCHAR to ARRAY
    -- priority: from DECIMAL to VARCHAR
    -- problem_id: from DECIMAL to VARCHAR
    -- requester_id: from INT to VARCHAR
    -- source_email_address: from DECIMAL to VARCHAR
    -- submitter_id: from INT to VARCHAR
    -- system_client: from DECIMAL to VARCHAR
    -- ticket_source_id: from DECIMAL to VARCHAR
    -- ticket_source_name: from DECIMAL to VARCHAR
    -- ticket_source_relationship: from DECIMAL to VARCHAR
    SELECT
        "ticket_recipient_address",
        "is_public",
        "ticket_recipient_name",
        "api_url",
        "has_incidents",
        "ticket_description",
        "recipient_email",
        "ticket_subject",
        "organization_id",
        "ticket_status",
        "brand_id",
        "submission_channel",
        "channelback_allowed",
        "ticket_id",
        "ticket_type",
        "ticket_form_id",
        "group_id",
        CAST("assignee_id" AS VARCHAR) AS "assignee_id",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("due_date" AS DATE) AS "due_date",
        CAST("external_id" AS VARCHAR) AS "external_id",
        CASE 
            WHEN "followup_ids" = '[]' THEN CAST('[]' AS INTEGER[])
            ELSE TRY_CAST("followup_ids" AS INTEGER[])
        END AS "followup_ids",
        CAST("followup_source_id" AS VARCHAR) AS "followup_source_id",
        CAST("forum_topic_id" AS VARCHAR) AS "forum_topic_id",
        CAST("last_update_timestamp" AS TIMESTAMP) AS "last_update_timestamp",
        from_json("merged_ticket_ids", '["INTEGER"]') AS "merged_ticket_ids",
        CAST("priority" AS VARCHAR) AS "priority",
        CAST("problem_id" AS VARCHAR) AS "problem_id",
        CAST("requester_id" AS VARCHAR) AS "requester_id",
        CAST("source_email_address" AS VARCHAR) AS "source_email_address",
        CAST("submitter_id" AS VARCHAR) AS "submitter_id",
        CAST("system_client" AS VARCHAR) AS "system_client",
        CAST("ticket_source_id" AS VARCHAR) AS "ticket_source_id",
        CAST("ticket_source_name" AS VARCHAR) AS "ticket_source_name",
        CAST("ticket_source_relationship" AS VARCHAR) AS "ticket_source_relationship"
    FROM "ticket_data_projected_renamed_cleaned_null"
),

"ticket_data_projected_renamed_cleaned_null_casted_missing_handled" AS (
    -- Handling missing values: There are 11 columns with unacceptable missing values
    -- assignee_id has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- external_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- group_id has 10.0 percent missing. Strategy: 🔄 Unchanged
    -- organization_id has 10.0 percent missing. Strategy: 🔄 Unchanged
    -- source_email_address has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- system_client has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- ticket_recipient_address has 20.0 percent missing. Strategy: 🔄 Unchanged
    -- ticket_recipient_name has 10.0 percent missing. Strategy: 🔄 Unchanged
    -- ticket_source_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- ticket_source_name has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- ticket_source_relationship has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "ticket_recipient_address",
        "is_public",
        "ticket_recipient_name",
        "api_url",
        "has_incidents",
        "ticket_description",
        "recipient_email",
        "ticket_subject",
        "organization_id",
        "ticket_status",
        "brand_id",
        "submission_channel",
        "channelback_allowed",
        "ticket_id",
        "ticket_type",
        "ticket_form_id",
        "group_id",
        "assignee_id",
        "creation_timestamp",
        "due_date",
        "followup_ids",
        "followup_source_id",
        "forum_topic_id",
        "last_update_timestamp",
        "merged_ticket_ids",
        "priority",
        "problem_id",
        "requester_id",
        "submitter_id"
    FROM "ticket_data_projected_renamed_cleaned_null_casted"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_data_projected_renamed_cleaned_null_casted_missing_handled"

version: 2
models:
- name: stg_ticket_data
  description: The table is about support tickets. It contains details like ticket
    ID, creation date, status, requester, assignee, and subject. Each row represents
    a single ticket with its attributes. The table includes information on how the
    ticket was submitted (via web, email), its current status (solved, closed, deleted),
    and associated organizations or groups. It also tracks ticket updates and links
    to related tickets.
  columns:
  - name: ticket_recipient_address
    description: Email address or identifier of the ticket recipient
    tests:
    - not_null
  - name: is_public
    description: Indicates if the ticket is publicly visible
    tests:
    - not_null
  - name: ticket_recipient_name
    description: Name of the ticket recipient or department
    tests:
    - not_null
    - accepted_values:
        values:
        - Support
        - IT
        - Customer Service
        - Technical Support
        - Sales
        - Billing
        - Product Team
        - Quality Assurance
        - Operations
        - Escalations
        - Management
        - Human Resources
        - Legal
        - Security
  - name: api_url
    description: URL to access the ticket in the API
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains a unique URL for each ticket. For this table,
        each row represents a single ticket, and the api_url is unique across rows
        as it includes the ticket_id in the URL.
  - name: has_incidents
    description: Indicates if ticket has associated incidents
    tests:
    - not_null
  - name: ticket_description
    description: Detailed description of the ticket issue
    tests:
    - not_null
  - name: recipient_email
    description: Email address of the ticket recipient
    tests:
    - not_null
  - name: ticket_subject
    description: Brief subject line of the ticket
    tests:
    - not_null
  - name: organization_id
    description: ID of the organization associated with the ticket
    tests:
    - not_null
  - name: ticket_status
    description: Current status of the ticket
    tests:
    - not_null
    - accepted_values:
        values:
        - closed
        - deleted
        - solved
        - open
        - pending
        - in progress
        - on hold
        - escalated
        - new
        - reopened
  - name: brand_id
    description: ID of the brand associated with the ticket
    tests:
    - not_null
  - name: submission_channel
    description: Channel through which the ticket was submitted
    tests:
    - not_null
    - accepted_values:
        values:
        - email
        - web
        - phone
        - in-person
        - mobile app
        - social media
        - chat
  - name: channelback_allowed
    description: Indicates if channelback is allowed
    tests:
    - not_null
  - name: ticket_id
    description: Unique identifier for the ticket
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each ticket. For this table,
        where each row is a ticket, the ticket_id is unique across rows.
  - name: ticket_type
    description: Type of the ticket
    tests:
    - accepted_values:
        values:
        - incident
        - problem
        - change
        - service request
        - task
        - alert
        - event
        - knowledge
        - project
        - maintenance
    cocoon_meta:
      missing_acceptable: Some tickets may not require a specific type.
  - name: ticket_form_id
    description: ID of the form used to create the ticket
    tests:
    - not_null
  - name: group_id
    description: ID of the group handling the ticket
    tests:
    - not_null
  - name: assignee_id
    description: ID of the agent assigned to the ticket
    tests:
    - not_null
  - name: creation_timestamp
    description: Timestamp of ticket creation
    tests:
    - not_null
  - name: due_date
    description: Due date for the ticket
    cocoon_meta:
      missing_acceptable: Not all tickets require a due date.
  - name: followup_ids
    description: IDs of follow-up tickets
    cocoon_meta:
      missing_acceptable: Not all tickets have followups.
  - name: followup_source_id
    description: ID of the follow-up source
    cocoon_meta:
      missing_acceptable: Not all tickets have followups.
  - name: forum_topic_id
    description: ID of related forum topic
    cocoon_meta:
      missing_acceptable: Not all tickets are related to forum topics.
  - name: last_update_timestamp
    description: Timestamp of last ticket update
    tests:
    - not_null
  - name: merged_ticket_ids
    description: IDs of tickets merged with this one
    cocoon_meta:
      missing_acceptable: Not all tickets are merged with others.
  - name: priority
    description: Priority level of the ticket
    cocoon_meta:
      missing_acceptable: Not all tickets are assigned a priority.
  - name: problem_id
    description: ID of the problem associated with the ticket
    cocoon_meta:
      missing_acceptable: Not all tickets are linked to a specific problem.
  - name: requester_id
    description: ID of the person who requested the ticket
    tests:
    - not_null
  - name: submitter_id
    description: ID of the person who submitted the ticket
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"daylight_time_data_projected" AS (
    -- Projection: Selecting 5 out of 6 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "time_zone",
        "year_",
        "daylight_end_utc",
        "daylight_offset",
        "daylight_start_utc"
    FROM "daylight_time_data"
),

"daylight_time_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- daylight_end_utc -> daylight_saving_end_utc
    -- daylight_offset -> daylight_saving_offset_hours
    -- daylight_start_utc -> daylight_saving_start_utc
    SELECT 
        "time_zone",
        "year_",
        "daylight_end_utc" AS "daylight_saving_end_utc",
        "daylight_offset" AS "daylight_saving_offset_hours",
        "daylight_start_utc" AS "daylight_saving_start_utc"
    FROM "daylight_time_data_projected"
),

"daylight_time_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- daylight_saving_end_utc: from VARCHAR to TIMESTAMP
    -- daylight_saving_start_utc: from VARCHAR to TIMESTAMP
    SELECT
        "time_zone",
        "year_",
        "daylight_saving_offset_hours",
        CAST("daylight_saving_end_utc" AS TIMESTAMP) AS "daylight_saving_end_utc",
        CAST("daylight_saving_start_utc" AS TIMESTAMP) AS "daylight_saving_start_utc"
    FROM "daylight_time_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "daylight_time_data_projected_renamed_casted"

version: 2
models:
- name: stg_daylight_time_data
  description: The table is about daylight saving time data. It contains information
    for different time zones and years. The details include the start and end times
    of daylight saving in UTC, and the daylight offset. Each row represents a specific
    time zone and year combination with its corresponding daylight saving information.
  columns:
  - name: time_zone
    description: Name of the time zone
    tests:
    - not_null
  - name: year_
    description: Year of the daylight saving time data
    tests:
    - not_null
  - name: daylight_saving_offset_hours
    description: Number of hours added during daylight saving time
    tests:
    - not_null
  - name: daylight_saving_end_utc
    description: End date and time of daylight saving in UTC
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the end date and time of daylight saving
        in UTC. For this table, each row is for a specific time zone and year combination.
        The daylight_saving_end_utc could potentially be unique across rows, as it's
        unlikely for different time zones to have the exact same end time down to
        the second.
  - name: daylight_saving_start_utc
    description: Start date and time of daylight saving in UTC
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the start date and time of daylight saving
        in UTC. For this table, each row is for a specific time zone and year combination.
        The daylight_saving_start_utc could potentially be unique across rows, as
        it's unlikely for different time zones to have the exact same start time down
        to the second.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_field_history_data_projected" AS (
    -- Projection: Selecting 5 out of 6 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "field_name",
        "ticket_id",
        "updated",
        "user_id",
        "value_"
    FROM "ticket_field_history_data"
),

"ticket_field_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- field_name -> changed_field
    -- updated -> update_timestamp
    -- value_ -> new_value
    SELECT 
        "field_name" AS "changed_field",
        "ticket_id",
        "updated" AS "update_timestamp",
        "user_id",
        "value_" AS "new_value"
    FROM "ticket_field_history_data_projected"
),

"ticket_field_history_data_projected_renamed_dedup" AS (
    -- Deduplication: Removed 1 duplicated rows
    SELECT DISTINCT * FROM "ticket_field_history_data_projected_renamed"
),

"ticket_field_history_data_projected_renamed_dedup_casted" AS (
    -- Column Type Casting: 
    -- update_timestamp: from VARCHAR to TIMESTAMP
    -- user_id: from DECIMAL to VARCHAR
    SELECT
        "changed_field",
        "ticket_id",
        "new_value",
        CAST("update_timestamp" AS TIMESTAMP) AS "update_timestamp",
        CAST("user_id" AS VARCHAR) AS "user_id"
    FROM "ticket_field_history_data_projected_renamed_dedup"
),

"ticket_field_history_data_projected_renamed_dedup_casted_missing_handled" AS (
    -- Handling missing values: There are 1 columns with unacceptable missing values
    -- user_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "changed_field",
        "ticket_id",
        "new_value",
        "update_timestamp"
    FROM "ticket_field_history_data_projected_renamed_dedup_casted"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_field_history_data_projected_renamed_dedup_casted_missing_handled"

version: 2
models:
- name: stg_ticket_field_history_data
  description: The table is about ticket field history data. It records changes to
    ticket fields like status and priority. Each row represents a change, including
    the field name, ticket ID, update timestamp, user ID (if applicable), and new
    value. This allows tracking of how ticket details evolve over time.
  columns:
  - name: changed_field
    description: Name of the ticket field that was changed
    tests:
    - not_null
    - accepted_values:
        values:
        - status
        - assignee_id
        - priority
        - subject
        - description
        - type
        - due_date
        - tags
        - requester_id
        - group_id
        - cc_emails
        - custom_fields
  - name: ticket_id
    description: Unique identifier for the ticket
    tests:
    - not_null
  - name: new_value
    description: New value of the changed field
    tests:
    - not_null
    - accepted_values:
        values:
        - solved
        - unsolved
        - '1111'
  - name: update_timestamp
    description: Timestamp of when the field was updated
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"brand_data_projected" AS (
    -- Projection: Selecting 21 out of 22 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "active",
        "brand_url",
        "default_",
        "has_help_center",
        "help_center_state",
        "logo_content_type",
        "logo_content_url",
        "logo_deleted",
        "logo_file_name",
        "logo_height",
        "logo_id",
        "logo_inline",
        "logo_mapped_content_url",
        "logo_size",
        "logo_url",
        "logo_width",
        "name",
        "subdomain",
        "url"
    FROM "brand_data"
),

"brand_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> brand_id
    -- _fivetran_deleted -> is_deleted
    -- active -> is_active
    -- default_ -> is_default
    -- logo_deleted -> is_logo_deleted
    -- logo_inline -> is_logo_inline
    -- name -> brand_name
    -- subdomain -> brand_subdomain
    -- url -> brand_main_url
    SELECT 
        "id" AS "brand_id",
        "_fivetran_deleted" AS "is_deleted",
        "active" AS "is_active",
        "brand_url",
        "default_" AS "is_default",
        "has_help_center",
        "help_center_state",
        "logo_content_type",
        "logo_content_url",
        "logo_deleted" AS "is_logo_deleted",
        "logo_file_name",
        "logo_height",
        "logo_id",
        "logo_inline" AS "is_logo_inline",
        "logo_mapped_content_url",
        "logo_size",
        "logo_url",
        "logo_width",
        "name" AS "brand_name",
        "subdomain" AS "brand_subdomain",
        "url" AS "brand_main_url"
    FROM "brand_data_projected"
),

"brand_data_projected_renamed_cleaned" AS (
    -- Clean unusual string values: 
    -- brand_name: The problem is that the brand_name column contains a value that appears to be a hash or encoded string ('2abdc594c0ad6eb2438448b3cbf7da56') rather than a readable brand name. This value is not meaningful or interpretable as a brand name. The correct value should be an empty string, as we don't have enough information to determine the actual brand name. 
    SELECT
        "brand_id",
        "is_deleted",
        "is_active",
        "brand_url",
        "is_default",
        "has_help_center",
        "help_center_state",
        "logo_content_type",
        "logo_content_url",
        "is_logo_deleted",
        "logo_file_name",
        "logo_height",
        "logo_id",
        "is_logo_inline",
        "logo_mapped_content_url",
        "logo_size",
        "logo_url",
        "logo_width",
        CASE
            WHEN "brand_name" = '''2abdc594c0ad6eb2438448b3cbf7da56''' THEN ''''
            ELSE "brand_name"
        END AS "brand_name",
        "brand_subdomain",
        "brand_main_url"
    FROM "brand_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "brand_data_projected_renamed_cleaned"

version: 2
models:
- name: stg_brand_data
  description: The table is about brand data. It contains details of brands including
    their ID, name, URL, logo information, and help center status. The table tracks
    whether brands are active, have a default status, and includes various attributes
    of their logos such as file type, dimensions, and URLs. It also records subdomain
    information and whether the brand has an enabled help center.
  columns:
  - name: brand_id
    description: Unique identifier for the brand
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each brand. For this
        table, each row represents a unique brand. The brand_id is designed to be
        a unique identifier across rows.
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: is_active
    description: Indicates if the brand is currently active
    tests:
    - not_null
  - name: brand_url
    description: URL associated with the brand
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column likely represents a unique URL for each brand. For this
        table, each row represents a unique brand. The brand_url is likely to be unique
        across rows as it's typically a distinct identifier for each brand's page
        or profile.
  - name: is_default
    description: Indicates if this is the default brand
    tests:
    - not_null
  - name: has_help_center
    description: Indicates if the brand has a help center
    tests:
    - not_null
  - name: help_center_state
    description: Current state of the help center
    tests:
    - not_null
    - accepted_values:
        values:
        - enabled
        - disabled
  - name: logo_content_type
    description: File type of the brand logo
    tests:
    - not_null
    - accepted_values:
        values:
        - image/png
        - image/jpeg
        - image/gif
        - image/bmp
        - image/tiff
        - image/webp
        - image/svg+xml
  - name: logo_content_url
    description: URL to access the logo content
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains the URL to access the logo content. While it's
        likely to be unique for each brand's logo, there's a small possibility of
        duplicate URLs. However, given the nature of content URLs, it could be considered
        a candidate key.
  - name: is_logo_deleted
    description: Indicates if the logo has been deleted
    tests:
    - not_null
  - name: logo_file_name
    description: File name of the brand logo
    tests:
    - not_null
  - name: logo_height
    description: Height of the logo in pixels
    tests:
    - not_null
  - name: logo_id
    description: Unique identifier for the logo
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for each brand's logo.
        For this table, each row represents a unique brand. The logo_id is likely
        to be unique across rows as each brand would have its own distinct logo.
  - name: is_logo_inline
    description: Indicates if the logo is inline
    tests:
    - not_null
  - name: logo_mapped_content_url
    description: Mapped URL for logo content
    tests:
    - not_null
  - name: logo_size
    description: File size of the logo in bytes
    tests:
    - not_null
  - name: logo_url
    description: URL to access the logo
    tests:
    - not_null
  - name: logo_width
    description: Width of the logo in pixels
    tests:
    - not_null
  - name: brand_name
    description: Name of the brand
    tests:
    - not_null
  - name: brand_subdomain
    description: Subdomain associated with the brand
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column likely represents a subdomain associated with each brand.
        For this table, each row represents a unique brand. The brand_subdomain is
        likely to be unique across rows as it's typically a distinct identifier for
        each brand's subdomain.
  - name: brand_main_url
    description: Main URL for the brand
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the main URL for the brand. For this table,
        each row represents a unique brand. The brand_main_url is likely to be unique
        across rows as it's typically a distinct identifier for each brand's website.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"user_tag_data_projected" AS (
    -- Projection: Selecting 2 out of 3 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "tag",
        "user_id"
    FROM "user_tag_data"
),

"user_tag_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- tag -> tag_id
    SELECT 
        "tag" AS "tag_id",
        "user_id"
    FROM "user_tag_data_projected"
)

-- COCOON BLOCK END
SELECT * FROM "user_tag_data_projected_renamed"

version: 2
models:
- name: stg_user_tag_data
  description: The table represents a many-to-many relationship between users and
    tags. Each row shows a tag associated with a user_id. Users can have multiple
    tags, and tags can be assigned to multiple users. The tag column contains hash-like
    strings, while user_id contains numeric identifiers. This structure allows for
    tracking which tags are associated with which users.
  columns:
  - name: tag_id
    description: Unique identifier for a tag
    tests:
    - not_null
  - name: user_id
    description: Numeric identifier for a user
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"schedule_holiday_data_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "schedule_id",
        "_fivetran_deleted",
        "end_date",
        "name",
        "start_date"
    FROM "schedule_holiday_data"
),

"schedule_holiday_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> holiday_id
    -- _fivetran_deleted -> is_deleted
    -- name -> holiday_name
    SELECT 
        "id" AS "holiday_id",
        "schedule_id",
        "_fivetran_deleted" AS "is_deleted",
        "end_date",
        "name" AS "holiday_name",
        "start_date"
    FROM "schedule_holiday_data_projected"
),

"schedule_holiday_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- end_date: from VARCHAR to DATE
    -- start_date: from VARCHAR to DATE
    SELECT
        "holiday_id",
        "schedule_id",
        "is_deleted",
        "holiday_name",
        CAST("end_date" AS DATE) AS "end_date",
        CAST("start_date" AS DATE) AS "start_date"
    FROM "schedule_holiday_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "schedule_holiday_data_projected_renamed_casted"

version: 2
models:
- name: stg_schedule_holiday_data
  description: The table is about holiday schedules. It contains details of holidays
    including an ID, schedule ID, deletion status, end date, name, and start date.
    Each row represents a specific holiday period within a schedule. The schedule_id
    likely links to a separate schedule table, allowing multiple holidays to be associated
    with one schedule.
  columns:
  - name: holiday_id
    description: Unique identifier for the holiday entry
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column is a unique identifier for each holiday entry. For this
        table, each row represents a specific holiday period within a schedule. holiday_id
        is likely to be unique across rows as it's designed to be an identifier.
  - name: schedule_id
    description: Identifier linking to a separate schedule table
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the record has been deleted
    tests:
    - not_null
  - name: holiday_name
    description: Name or description of the holiday
    tests:
    - not_null
  - name: end_date
    description: The last day of the holiday period
    tests:
    - not_null
  - name: start_date
    description: The first day of the holiday period
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"user_data_projected" AS (
    -- Projection: Selecting 31 out of 32 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "active",
        "alias",
        "authenticity_token",
        "chat_only",
        "created_at",
        "details",
        "email",
        "external_id",
        "last_login_at",
        "locale",
        "locale_id",
        "moderator",
        "name",
        "notes",
        "only_private_comments",
        "organization_id",
        "phone",
        "remote_photo_url",
        "restricted_agent",
        "role",
        "shared",
        "shared_agent",
        "signature",
        "suspended",
        "ticket_restriction",
        "time_zone",
        "two_factor_auth_enabled",
        "updated_at",
        "url",
        "verified"
    FROM "user_data"
),

"user_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> user_id
    -- active -> is_active
    -- authenticity_token -> auth_token
    -- chat_only -> is_chat_only
    -- created_at -> creation_date
    -- details -> additional_details
    -- last_login_at -> last_login_date
    -- moderator -> is_moderator
    -- name -> full_name
    -- only_private_comments -> private_comments_only
    -- phone -> phone_number
    -- remote_photo_url -> photo_url
    -- restricted_agent -> is_restricted_agent
    -- role -> user_role
    -- shared -> is_shared
    -- shared_agent -> is_shared_agent
    -- suspended -> is_suspended
    -- two_factor_auth_enabled -> is_2fa_enabled
    -- updated_at -> last_update_date
    -- url -> api_url
    -- verified -> account_verified
    SELECT 
        "id" AS "user_id",
        "active" AS "is_active",
        "alias",
        "authenticity_token" AS "auth_token",
        "chat_only" AS "is_chat_only",
        "created_at" AS "creation_date",
        "details" AS "additional_details",
        "email",
        "external_id",
        "last_login_at" AS "last_login_date",
        "locale",
        "locale_id",
        "moderator" AS "is_moderator",
        "name" AS "full_name",
        "notes",
        "only_private_comments" AS "private_comments_only",
        "organization_id",
        "phone" AS "phone_number",
        "remote_photo_url" AS "photo_url",
        "restricted_agent" AS "is_restricted_agent",
        "role" AS "user_role",
        "shared" AS "is_shared",
        "shared_agent" AS "is_shared_agent",
        "signature",
        "suspended" AS "is_suspended",
        "ticket_restriction",
        "time_zone",
        "two_factor_auth_enabled" AS "is_2fa_enabled",
        "updated_at" AS "last_update_date",
        "url" AS "api_url",
        "verified" AS "account_verified"
    FROM "user_data_projected"
),

"user_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- additional_details: from DECIMAL to VARCHAR
    -- alias: from DECIMAL to VARCHAR
    -- auth_token: from DECIMAL to VARCHAR
    -- creation_date: from VARCHAR to TIMESTAMP
    -- external_id: from DECIMAL to VARCHAR
    -- last_login_date: from VARCHAR to TIMESTAMP
    -- last_update_date: from VARCHAR to TIMESTAMP
    -- notes: from DECIMAL to VARCHAR
    -- phone_number: from DECIMAL to VARCHAR
    -- photo_url: from DECIMAL to VARCHAR
    -- signature: from DECIMAL to VARCHAR
    -- user_id: from INT to VARCHAR
    SELECT
        "is_active",
        "is_chat_only",
        "email",
        "locale",
        "locale_id",
        "is_moderator",
        "full_name",
        "private_comments_only",
        "organization_id",
        "is_restricted_agent",
        "user_role",
        "is_shared",
        "is_shared_agent",
        "is_suspended",
        "ticket_restriction",
        "time_zone",
        "is_2fa_enabled",
        "api_url",
        "account_verified",
        CAST("additional_details" AS VARCHAR) AS "additional_details",
        CAST("alias" AS VARCHAR) AS "alias",
        CAST("auth_token" AS VARCHAR) AS "auth_token",
        CAST("creation_date" AS TIMESTAMP) AS "creation_date",
        CAST("external_id" AS VARCHAR) AS "external_id",
        CAST("last_login_date" AS TIMESTAMP) AS "last_login_date",
        CAST("last_update_date" AS TIMESTAMP) AS "last_update_date",
        CAST("notes" AS VARCHAR) AS "notes",
        CAST("phone_number" AS VARCHAR) AS "phone_number",
        CAST("photo_url" AS VARCHAR) AS "photo_url",
        CAST("signature" AS VARCHAR) AS "signature",
        CAST("user_id" AS VARCHAR) AS "user_id"
    FROM "user_data_projected_renamed"
),

"user_data_projected_renamed_casted_missing_handled" AS (
    -- Handling missing values: There are 8 columns with unacceptable missing values
    -- additional_details has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- auth_token has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- external_id has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- notes has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- organization_id has 10.0 percent missing. Strategy: 🔄 Unchanged
    -- phone_number has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- photo_url has 100.0 percent missing. Strategy: 🗑️ Drop Column
    -- signature has 100.0 percent missing. Strategy: 🗑️ Drop Column
    SELECT
        "is_active",
        "is_chat_only",
        "email",
        "locale",
        "locale_id",
        "is_moderator",
        "full_name",
        "private_comments_only",
        "organization_id",
        "is_restricted_agent",
        "user_role",
        "is_shared",
        "is_shared_agent",
        "is_suspended",
        "ticket_restriction",
        "time_zone",
        "is_2fa_enabled",
        "api_url",
        "account_verified",
        "alias",
        "creation_date",
        "last_login_date",
        "last_update_date",
        "user_id"
    FROM "user_data_projected_renamed_casted"
)

-- COCOON BLOCK END
SELECT * FROM "user_data_projected_renamed_casted_missing_handled"

version: 2
models:
- name: stg_user_data
  description: The table is about user data in a system. It contains user details
    like ID, name, email, and account settings. The table includes information on
    user activity, roles, and preferences. It also stores authentication details,
    organization affiliations, and system-specific data like API URLs and timestamps
    for creation and updates.
  columns:
  - name: is_active
    description: Indicates if the user account is active
    tests:
    - not_null
  - name: is_chat_only
    description: User is restricted to chat interactions only
    tests:
    - not_null
  - name: email
    description: User's email address
    tests:
    - not_null
  - name: locale
    description: User's preferred language and region
    tests:
    - not_null
  - name: locale_id
    description: Identifier for user's locale
    tests:
    - not_null
  - name: is_moderator
    description: Indicates if user has moderator privileges
    tests:
    - not_null
  - name: full_name
    description: User's full name
    tests:
    - not_null
  - name: private_comments_only
    description: User can only make private comments
    tests:
    - not_null
  - name: organization_id
    description: Identifier of user's associated organization
    tests:
    - not_null
  - name: is_restricted_agent
    description: Indicates if user is a restricted agent
    tests:
    - not_null
  - name: user_role
    description: User's role in the system
    tests:
    - not_null
    - accepted_values:
        values:
        - end-user
        - admin
        - moderator
        - guest
        - editor
        - manager
        - developer
        - support
        - analyst
        - tester
  - name: is_shared
    description: Indicates if user account is shared
    tests:
    - not_null
  - name: is_shared_agent
    description: Indicates if user is a shared agent
    tests:
    - not_null
  - name: is_suspended
    description: Indicates if user account is suspended
    tests:
    - not_null
  - name: ticket_restriction
    description: User's ticket access restriction level
    tests:
    - not_null
    - accepted_values:
        values:
        - requested
        - pending
        - approved
        - denied
        - basic
        - standard
        - premium
        - vip
        - admin
        - superadmin
        - restricted
        - blocked
  - name: time_zone
    description: User's preferred time zone
    tests:
    - not_null
  - name: is_2fa_enabled
    description: Two-factor authentication status
    tests:
    - not_null
  - name: api_url
    description: API URL for user data
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains a unique API URL for each user. For this table,
        each row represents a unique user in the system. The api_url appears to be
        unique across rows, as it includes the user_id in its structure.
  - name: account_verified
    description: User's account verification status
    tests:
    - not_null
  - name: alias
    description: Alternative name for the user
    cocoon_meta:
      missing_acceptable: Optional alternate name for users who don't use one.
  - name: creation_date
    description: Timestamp of user account creation
    tests:
    - not_null
  - name: last_login_date
    description: Timestamp of user's last login
    tests:
    - not_null
  - name: last_update_date
    description: Timestamp of last user account update
    tests:
    - not_null
  - name: user_id
    description: Unique identifier for the user
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains a unique identifier for each user. For this
        table, each row represents a unique user in the system. The user_id is designed
        to be unique across all users and appears to be unique across rows.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_comment_data_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "body",
        "created",
        "facebook_comment",
        "public_",
        "ticket_id",
        "tweet",
        "user_id",
        "voice_comment"
    FROM "ticket_comment_data"
),

"ticket_comment_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> comment_id
    -- body -> comment_content
    -- created -> creation_timestamp
    -- facebook_comment -> is_facebook_comment
    -- public_ -> is_public
    -- tweet -> is_tweet
    -- voice_comment -> is_voice_comment
    SELECT 
        "id" AS "comment_id",
        "body" AS "comment_content",
        "created" AS "creation_timestamp",
        "facebook_comment" AS "is_facebook_comment",
        "public_" AS "is_public",
        "ticket_id",
        "tweet" AS "is_tweet",
        "user_id",
        "voice_comment" AS "is_voice_comment"
    FROM "ticket_comment_data_projected"
),

"ticket_comment_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- comment_id: from INT to VARCHAR
    -- creation_timestamp: from VARCHAR to TIMESTAMP
    -- user_id: from INT to VARCHAR
    SELECT
        "comment_content",
        "is_facebook_comment",
        "is_public",
        "ticket_id",
        "is_tweet",
        "is_voice_comment",
        CAST("comment_id" AS VARCHAR) AS "comment_id",
        CAST("creation_timestamp" AS TIMESTAMP) AS "creation_timestamp",
        CAST("user_id" AS VARCHAR) AS "user_id"
    FROM "ticket_comment_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_comment_data_projected_renamed_casted"

version: 2
models:
- name: stg_ticket_comment_data
  description: The table is about ticket comments. It contains details such as the
    comment ID, body, creation time, ticket ID, and user ID. It also includes boolean
    flags for Facebook comments, public visibility, tweets, and voice comments. Each
    row represents a single comment associated with a specific ticket and user.
  columns:
  - name: comment_content
    description: The content of the comment
    tests:
    - not_null
  - name: is_facebook_comment
    description: Indicates if the comment is from Facebook
    tests:
    - not_null
  - name: is_public
    description: Indicates if the comment is publicly visible
    tests:
    - not_null
  - name: ticket_id
    description: Identifier of the ticket associated with the comment
    tests:
    - not_null
  - name: is_tweet
    description: Indicates if the comment is a tweet
    tests:
    - not_null
  - name: is_voice_comment
    description: Indicates if the comment is a voice comment
    tests:
    - not_null
  - name: comment_id
    description: Unique identifier for the comment
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains a unique identifier for each comment. For this
        table, where each row represents a single comment, the comment_id should be
        unique across all rows, as it's designed to distinctly identify each comment.
  - name: creation_timestamp
    description: Timestamp when the comment was created
    tests:
    - not_null
  - name: user_id
    description: Identifier of the user who made the comment
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"group_data_projected" AS (
    -- Projection: Selecting 6 out of 7 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "_fivetran_deleted",
        "created_at",
        "name",
        "updated_at",
        "url"
    FROM "group_data"
),

"group_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> group_id
    -- _fivetran_deleted -> is_deleted
    -- name -> group_name
    -- url -> api_url
    SELECT 
        "id" AS "group_id",
        "_fivetran_deleted" AS "is_deleted",
        "created_at",
        "name" AS "group_name",
        "updated_at",
        "url" AS "api_url"
    FROM "group_data_projected"
),

"group_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    -- group_id: from INT to VARCHAR
    -- updated_at: from VARCHAR to TIMESTAMP
    SELECT
        "is_deleted",
        "group_name",
        "api_url",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("group_id" AS VARCHAR) AS "group_id",
        CAST("updated_at" AS TIMESTAMP) AS "updated_at"
    FROM "group_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "group_data_projected_renamed_casted"

version: 2
models:
- name: stg_group_data
  description: The table is about support groups. It contains group details such as
    ID, name, creation date, update date, and API URL. Each row represents a distinct
    group like "Non-Support - (Billing Tickets)" or "Information Technology". The
    table likely comes from a customer support platform like Zendesk, as evidenced
    by the URL structure. It tracks when groups were created and last updated, which
    is useful for auditing and management purposes.
  columns:
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: group_name
    description: Name of the support group
    tests:
    - not_null
  - name: api_url
    description: API endpoint URL for the group
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains the API endpoint URL for each group. For this
        table, each row represents a unique group, and the API URL is specific to
        each group. The URL structure includes the group_id, making it unique for
        each group.
  - name: created_at
    description: Timestamp when the group was created
    tests:
    - not_null
  - name: group_id
    description: Unique identifier for the group
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column contains a unique identifier for each group. For this
        table, each row represents a distinct group, and the group_id is designed
        to be a unique identifier across all groups in the system.
  - name: updated_at
    description: Timestamp when the group was last updated
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"time_zone_data_projected" AS (
    -- Projection: Selecting 2 out of 3 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "time_zone",
        "standard_offset"
    FROM "time_zone_data"
),

"time_zone_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- time_zone -> location
    -- standard_offset -> utc_offset
    SELECT 
        "time_zone" AS "location",
        "standard_offset" AS "utc_offset"
    FROM "time_zone_data_projected"
)

-- COCOON BLOCK END
SELECT * FROM "time_zone_data_projected_renamed"

version: 2
models:
- name: stg_time_zone_data
  description: The table is about time zones. It contains information on different
    cities or regions and their standard time offsets from UTC. The "time_zone" column
    lists locations like London and Dublin. The "standard_offset" column shows the
    time difference from UTC, with both examples having "+00:00" indicating they are
    in the same time zone.
  columns:
  - name: location
    description: Name of the city or region in the time zone
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the name of the city or region in the time
        zone. For this table, each row represents a unique location and its corresponding
        UTC offset. Since each city or region typically has its own distinct time
        zone setting, the location is likely to be unique across rows.
  - name: utc_offset
    description: Time difference from UTC in hours and minutes
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"domain_name_data_projected" AS (
    -- Projection: Selecting 3 out of 4 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "index_",
        "organization_id",
        "domain_name"
    FROM "domain_name_data"
),

"domain_name_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- index_ -> row_index
    -- domain_name -> encrypted_domain
    SELECT 
        "index_" AS "row_index",
        "organization_id",
        "domain_name" AS "encrypted_domain"
    FROM "domain_name_data_projected"
)

-- COCOON BLOCK END
SELECT * FROM "domain_name_data_projected_renamed"

version: 2
models:
- name: stg_domain_name_data
  description: The table represents a relation between organizations and domain names.
    Each row links an organization ID to a domain name. Some organizations may have
    multiple domain names associated with them, as seen with organization 370298 which
    has two entries. The domain names appear to be hashed or encrypted values rather
    than readable URLs.
  columns:
  - name: row_index
    description: Constant value, possibly for indexing or sorting
    tests:
    - not_null
  - name: organization_id
    description: Unique identifier for the organization
    tests:
    - not_null
  - name: encrypted_domain
    description: Hashed or encrypted domain name associated with organization
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a hashed or encrypted domain name associated
        with an organization. For this table, each row represents a link between an
        organization and a domain name. The encrypted_domain appears to be unique
        across rows in the given sample, and it's reasonable to assume that each domain
        name would have a unique encrypted value.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"organization_tag_data_projected" AS (
    -- Projection: Selecting 2 out of 3 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "organization_id",
        "tag"
    FROM "organization_tag_data"
),

"organization_tag_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- tag -> tag_hash
    SELECT 
        "organization_id",
        "tag" AS "tag_hash"
    FROM "organization_tag_data_projected"
),

"organization_tag_data_projected_renamed_dedup" AS (
    -- Deduplication: Removed 4 duplicated rows
    SELECT DISTINCT * FROM "organization_tag_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "organization_tag_data_projected_renamed_dedup"

version: 2
models:
- name: stg_organization_tag_data
  description: The table represents a many-to-many relationship between organizations
    and tags. Each row links an organization (identified by organization_id) to a
    tag (represented by a hash-like string). Organizations can have multiple tags,
    and tags can be associated with multiple organizations. The table allows for tracking
    which tags are applied to which organizations.
  columns:
  - name: organization_id
    description: Unique identifier for an organization
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents a unique identifier for an organization.
        For this table, each row links an organization to a tag. The organization_id
        appears to be unique across rows in the sample data, and it's described as
        a "Unique identifier for an organization" in the provided information.
  - name: tag_hash
    description: Hash-like string representing a tag
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_tag_data_projected" AS (
    -- Projection: Selecting 2 out of 3 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "tag",
        "ticket_id"
    FROM "ticket_tag_data"
),

"ticket_tag_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- tag -> tag_name
    SELECT 
        "tag" AS "tag_name",
        "ticket_id"
    FROM "ticket_tag_data_projected"
),

"ticket_tag_data_projected_renamed_dedup" AS (
    -- Deduplication: Removed 2 duplicated rows
    SELECT DISTINCT * FROM "ticket_tag_data_projected_renamed"
),

"ticket_tag_data_projected_renamed_dedup_casted" AS (
    -- Column Type Casting: 
    -- ticket_id: from INT to VARCHAR
    SELECT
        "tag_name",
        CAST("ticket_id" AS VARCHAR) AS "ticket_id"
    FROM "ticket_tag_data_projected_renamed_dedup"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_tag_data_projected_renamed_dedup_casted"

version: 2
models:
- name: stg_ticket_tag_data
  description: The table represents the relationship between tags and tickets. Each
    ticket can have multiple tags. Tags can be applied to multiple tickets. The table
    stores tag names and ticket IDs. It allows for categorizing or labeling tickets
    with various attributes like "customer" or "prospect".
  columns:
  - name: tag_name
    description: Represents the label or category assigned to a ticket
    tests:
    - not_null
    - accepted_values:
        values:
        - customer
        - prospect
        - connector
        - lead
        - partner
        - vendor
        - employee
        - investor
        - affiliate
        - reseller
        - distributor
        - supplier
        - competitor
        - media
        - government
        - non-profit
        - other
  - name: ticket_id
    description: Unique identifier for each support ticket
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_schedule_data_projected" AS (
    -- Projection: Selecting 3 out of 4 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "created_at",
        "ticket_id",
        "schedule_id"
    FROM "ticket_schedule_data"
),

"ticket_schedule_data_projected_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    SELECT
        "ticket_id",
        "schedule_id",
        CAST("created_at" AS TIMESTAMP) AS "created_at"
    FROM "ticket_schedule_data_projected"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_schedule_data_projected_casted"

version: 2
models:
- name: stg_ticket_schedule_data
  description: 'The table represents a relation between tickets and schedules. It
    contains three main pieces of information: the creation timestamp, the ticket
    ID, and the schedule ID. Each row links a specific ticket to a particular schedule,
    indicating when the ticket was created and associated with that schedule. The
    consistent schedule ID (3600) suggests all tickets in the sample are linked to
    the same schedule.'
  columns:
  - name: ticket_id
    description: Unique identifier for each ticket
    tests:
    - not_null
  - name: schedule_id
    description: Identifier for the associated schedule
    tests:
    - not_null
  - name: created_at
    description: Timestamp when the ticket was created
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the timestamp when a ticket was created.
        For this table, each row represents a unique ticket associated with a schedule.
        The created_at timestamp is likely to be unique across rows, as it's unlikely
        that two tickets would be created at the exact same second. However, in theory,
        it's possible for two tickets to have the same creation timestamp if they
        were created simultaneously or if the system doesn't have millisecond precision.

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"schedule_data_projected" AS (
    -- Projection: Selecting 9 out of 10 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "end_time",
        "id",
        "start_time",
        "_fivetran_deleted",
        "end_time_utc",
        "name",
        "start_time_utc",
        "time_zone",
        "created_at"
    FROM "schedule_data"
),

"schedule_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- end_time -> shift_end_local
    -- start_time -> shift_start_local
    -- _fivetran_deleted -> is_deleted
    -- end_time_utc -> shift_end_utc
    -- name -> team_name
    -- start_time_utc -> shift_start_utc
    SELECT 
        "end_time" AS "shift_end_local",
        "id",
        "start_time" AS "shift_start_local",
        "_fivetran_deleted" AS "is_deleted",
        "end_time_utc" AS "shift_end_utc",
        "name" AS "team_name",
        "start_time_utc" AS "shift_start_utc",
        "time_zone",
        "created_at"
    FROM "schedule_data_projected"
),

"schedule_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    -- id: from INT to VARCHAR
    SELECT
        "shift_end_local",
        "shift_start_local",
        "is_deleted",
        "shift_end_utc",
        "team_name",
        "shift_start_utc",
        "time_zone",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("id" AS VARCHAR) AS "id"
    FROM "schedule_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "schedule_data_projected_renamed_casted"

version: 2
models:
- name: stg_schedule_data
  description: The table is about support team schedules. It contains shift details
    like start and end times, both in local and UTC formats. Each record has a unique
    ID, team name (SupportCA), time zone (Pacific Time), and creation timestamp. The
    table tracks multiple shifts for the same team, likely representing different
    work periods throughout the day or week.
  columns:
  - name: shift_end_local
    description: Local end time of the shift in minutes
    tests:
    - not_null
  - name: shift_start_local
    description: Local start time of the shift in minutes
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the record is deleted
    tests:
    - not_null
  - name: shift_end_utc
    description: UTC end time of the shift in minutes
    tests:
    - not_null
  - name: team_name
    description: Name of the support team
    tests:
    - not_null
  - name: shift_start_utc
    description: UTC start time of the shift in minutes
    tests:
    - not_null
  - name: time_zone
    description: Time zone of the support team
    tests:
    - not_null
  - name: created_at
    description: Timestamp when the record was created
    tests:
    - not_null
  - name: id
    description: Unique identifier for the record
    tests:
    - not_null

-- COCOON BLOCK START: PLEASE DO NOT MODIFY THIS BLOCK FOR SELF-MAINTENANCE
WITH 
"ticket_form_history_data_projected" AS (
    -- Projection: Selecting 8 out of 9 columns
    -- Columns projected out: ['_fivetran_synced']
    SELECT 
        "id",
        "updated_at",
        "_fivetran_deleted",
        "active",
        "created_at",
        "display_name",
        "end_user_visible",
        "name"
    FROM "ticket_form_history_data"
),

"ticket_form_history_data_projected_renamed" AS (
    -- Rename: Renaming columns
    -- id -> form_id
    -- _fivetran_deleted -> is_deleted
    -- active -> is_active
    -- display_name -> form_identifier
    -- end_user_visible -> is_visible_to_end_user
    -- name -> form_name
    SELECT 
        "id" AS "form_id",
        "updated_at",
        "_fivetran_deleted" AS "is_deleted",
        "active" AS "is_active",
        "created_at",
        "display_name" AS "form_identifier",
        "end_user_visible" AS "is_visible_to_end_user",
        "name" AS "form_name"
    FROM "ticket_form_history_data_projected"
),

"ticket_form_history_data_projected_renamed_casted" AS (
    -- Column Type Casting: 
    -- created_at: from VARCHAR to TIMESTAMP
    -- updated_at: from VARCHAR to TIMESTAMP
    SELECT
        "form_id",
        "is_deleted",
        "is_active",
        "form_identifier",
        "is_visible_to_end_user",
        "form_name",
        CAST("created_at" AS TIMESTAMP) AS "created_at",
        CAST("updated_at" AS TIMESTAMP) AS "updated_at"
    FROM "ticket_form_history_data_projected_renamed"
)

-- COCOON BLOCK END
SELECT * FROM "ticket_form_history_data_projected_renamed_casted"

version: 2
models:
- name: stg_ticket_form_history_data
  description: The table is about ticket form history data. It contains details of
    ticket forms including their ID, creation date, update date, active status, display
    name, and visibility to end users. Each row represents a version or update of
    a ticket form, with multiple entries for the same ID showing its change history
    over time.
  columns:
  - name: form_id
    description: Unique numeric identifier for the ticket form
    tests:
    - not_null
  - name: is_deleted
    description: Indicates if the record was deleted in Fivetran
    tests:
    - not_null
  - name: is_active
    description: Indicates if the ticket form is currently active
    tests:
    - not_null
  - name: form_identifier
    description: Unique identifier or name for the ticket form
    tests:
    - not_null
  - name: is_visible_to_end_user
    description: Indicates if the form is visible to end users
    tests:
    - not_null
  - name: form_name
    description: Name or identifier of the ticket form
    tests:
    - not_null
  - name: created_at
    description: Timestamp of when the ticket form was created
    tests:
    - not_null
  - name: updated_at
    description: Timestamp of when the ticket form was last updated
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: This column represents the timestamp when the ticket form was last
        updated. For this table, each row represents a version or update of a ticket
        form. The updated_at column is unique across rows, as it captures the exact
        moment of each update, which is unlikely to be exactly the same for different
        updates.

-- Slowly Changing Dimension: Dimension keys are "form_id"
-- Effective date columns are "updated_at"
-- We will create Type 1 SCD (latest snapshot)
SELECT 
    "form_id",
    "is_deleted",
    "is_active",
    "form_identifier",
    "is_visible_to_end_user",
    "form_name",
    "created_at"
FROM (
     SELECT 
            "form_id",
            "is_deleted",
            "is_active",
            "form_identifier",
            "is_visible_to_end_user",
            "form_name",
            "created_at",
            ROW_NUMBER() OVER (
                PARTITION BY "form_id" 
                ORDER BY "updated_at" 
            DESC) AS "cocoon_rn"
    FROM "stg_ticket_form_history_data"
) ranked
WHERE "cocoon_rn" = 1

version: 2
models:
- name: snapshot_ticket_form_history_data
  description: The table is about current ticket form data. It tracks the most recent
    version of each ticket form, including its ID, active status, identifier, visibility
    to end users, and name. Each row represents a unique ticket form with its latest
    information. The table excludes historical versions and update timestamps, focusing
    on the current state of each form.
  columns:
  - name: form_id
    description: Unique numeric identifier for the ticket form
    tests:
    - not_null
    - unique
    cocoon_meta:
      uniqueness: Unique dimension key, derived from the slowly changing dimension
  - name: is_deleted
    description: Indicates if the record was deleted in Fivetran
    tests:
    - not_null
  - name: is_active
    description: Indicates if the ticket form is currently active
    tests:
    - not_null
  - name: form_identifier
    description: Unique identifier or name for the ticket form
    tests:
    - not_null
  - name: is_visible_to_end_user
    description: Indicates if the form is visible to end users
    tests:
    - not_null
  - name: form_name
    description: Name or identifier of the ticket form
    tests:
    - not_null
  - name: created_at
    description: Timestamp of when the ticket form was created
    tests:
    - not_null
cocoon_meta:
  scd_base_table: stg_ticket_form_history_data