from typing import TypedDict, cast
from uuid import UUID

from fastapi.responses import FileResponse
from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.worksheet.worksheet import Worksheet
from pydantic import UUID1, UUID4

from product_staging.allowed_value_sets.api.routers import allowed_value_sets
from product_staging.connectors import ows_account
from product_staging.constants import features as features
from product_staging.logic.utils import features as features_logic
from product_staging.logic.utils import s3
from product_staging.logic.utils.spreadsheet.openpyxl import add_section
from product_staging.logic.utils.spreadsheet.validation import add_value_set_validation

ID_RESTRICTION_TOOLTIP = (
    "Please note that {} IDs are only applicable to Primary and Featuring Artists. IDs for other "
    + "Contributor Roles will not be accepted"
)


async def bulk_template(
    vendor_uuid: UUID1 | UUID4 | None,
    subaccount_id: int | None,
    is_classical: bool = False,
) -> dict[str, str] | FileResponse:
    """
    Generate template and return s3 download link
    """
    if subaccount_id and not vendor_uuid:
        vendor_result = await ows_account.get_vendor_uuid_from_subaccount_id(
            subaccount_id
        )
        if vendor_result:
            vendor_uuid = UUID(vendor_result)

    is_localizations_enabled = (
        await features_logic.check_feature_control_for_vendor_uuid(
            vendor_uuid, features.LOCALIZATIONS
        )
    )
    template = await create_template(is_classical, is_localizations_enabled)

    filename = "bulk_upload_template"
    if is_classical:
        filename += "_classical"
    if is_localizations_enabled:
        filename += "_localizations"
    filename += ".xlsx"

    return {
        "download_link": (
            await s3.upload_workbook_and_generate_download_link(
                template, filename, "template"
            )
        )
    }


async def create_template(
    is_classical: bool = False, is_localizations_enabled: bool = False
):
    """
    Generate and return bulk template
    """
    template = Workbook()

    value_sets = await allowed_value_sets.get(is_classical)

    projects = cast(Worksheet, template.active)
    projects.title = "Projects"
    products = template.create_sheet("Products")
    product_contributors = template.create_sheet("Product Contributors")
    product_localizations = template.create_sheet("Product Localizations")
    tracks = template.create_sheet("Tracks")
    other_track_contributors = template.create_sheet("Other Track Contributors")
    track_localizations = template.create_sheet("Track Localizations")
    accepted_territories_sheet = template.create_sheet("Accepted Territories")
    enums = template.create_sheet("enums")
    enums.sheet_state = "hidden"

    if not is_localizations_enabled:
        product_localizations.sheet_state = "hidden"
        track_localizations.sheet_state = "hidden"

    add_section(
        worksheet=projects,
        section_name="",
        col_names=["Subaccount ID**"],
        tooltips=[
            "The numeric code of the Subaccount that you want this release to be created under"
        ],
        enums_sheet=enums,
        is_first_section=True,
    )

    add_section(
        worksheet=projects,
        section_name="Project Basics",
        col_names=[
            "Project Name*",
            "Project Code*",
            "Project Artist*",
            "Apple ID",
            "Spotify ID",
            "Project Description",
        ],
        tooltips=[
            "An internal field, typically the name of your album",
            "This is a unique code used to identify this product. Example APPL123CD",
            "Add an Artist to connect this project to. This is for internal use and will not be displayed at stores.",
            "Add the Apple ID if this is an existing artist",
            "Add the Spotify ID if this is an existing artist",
            "This will be used in the description section of the Sales Sheets and other marketing materials. It "
            + "should be specific to the project and not the different product formats.",
        ],
        enums_sheet=enums,
    )

    add_section(
        worksheet=products,
        section_name="Product Basics",
        col_names=[
            "Project Code*",
            "UPC",
            "Manufacturers UPC",
            "Product Code*",
            ["Product Metadata Language*"] + value_sets["meta_languages"],
            "Product Name*",
            "Product Version",
            "Product Version Notes",
        ],
        tooltips=[
            "Add the code of the project that this product appears on. This Project code must match one added on the "
            + '"Projects" sheet',
            "Please provide the label assigned UPC for each release. There should be one UPC per release. If left "
            + "blank, we will assign a UPC to your product.",
            "Please provide the label-assigned UPC for each product. Only one manufacturer's UPC may be entered for "
            + "each product.",
            "This is a unique code used to identify a product, also know as Catalog Number. Different product codes "
            + "will create different products. Product code must only contain letters, numbers, and hyphens.",
            "This is the language of the digital product's metadata. Please use only accepted values from the "
            + "dropdown.",
            "The name of the product that will be sent to stores.",
            "A specific version of the Product that will be delivered to stores and appear next to the title. "
            + 'Example: "Live", "Remix", "Radio Edit"',
            "If the project has multiple versions of this product, please describe the version here. Example : "
            + '"Deluxe Edition" or "Limited Blue Splatter Vinyl". This will appear on various marketing materials.',
        ],
        enums_sheet=enums,
        is_first_section=True,
    )

    # Prevent scientific notation in Excel for UPC column
    upc_col = products.column_dimensions["B"]
    upc_col.number_format = "############"

    # Allow for leading zeros in Manufacturer's UPC column
    manufacturers_upc_col = products.column_dimensions["C"]
    manufacturers_upc_col.number_format = numbers.FORMAT_TEXT

    add_section(
        worksheet=products,
        section_name="Artwork",
        col_names=["File Name"],
        tooltips=[
            "Enter the filename of this product's artwork. Artwork file names must be unique."
        ],
        enums_sheet=enums,
    )

    product_contributor_cols: list[str | list[str]] = [
        "Primary Artist*",
        "Primary Apple ID",
        "Primary Spotify ID",
    ]
    product_contributor_tooltips = [
        "Please add 1 primary artist in this field with the accompanying Apple/Spotify IDs in the "
        + "following field. Any other Primary Artists/contributors can be added on the "
        + '"Product Contributors"',
        None,
        None,
    ]

    if is_classical:
        product_contributor_cols.append("Composer*")
        product_contributor_tooltips.append(
            "Name of the primary composer who composed the material on the product. Do no include arrangers, "
            + "librettists, or lyricists unless they are also the composer. Additonal Composers can be added "
            + 'in the "Product Contributors" sheet.'
        )

    add_section(
        worksheet=products,
        section_name="Product Contributors",
        col_names=product_contributor_cols,
        tooltips=product_contributor_tooltips,
        enums_sheet=enums,
    )

    add_section(
        worksheet=products,
        section_name="Product Details",
        col_names=[
            "Genre*",
            "Subgenre*",
            ["Format*"] + value_sets["format"],
            "Imprint*",
            "(C) Line*",
            "Special Instructions",
        ],
        tooltips=[
            "Please use only accepted values from the dropdown. Only one genre may be selected per product i.e. "
            + "different tracks from the same product cannot have different genres.",
            "Please use only accepted values from the dropdown. Only one genre may be selected per product i.e. "
            + "different tracks from the same product cannot have different genres.",
            "This indicates the format of the product that will be delivered to stores.",
            "Name of the imprint credited to the release.",
            "This is the copyright information for track ordering, artwork and text of this product. "
            + 'The format should be YYYY Company. Example: "2025 Bandy Records"',
            "Please include any special requests that this release requires, like Apple Digital Master email address, "
            + "digital booklet or other operational requests.",
        ],
        enums_sheet=enums,
    )

    genres_sheet = template.create_sheet("genres")
    genres_sheet.sheet_state = "hidden"

    add_value_set_validation(
        workbook=template,
        worksheet=products,
        start_row=3,
        columns=("N", "O") if is_classical else ("M", "N"),  # this should be dynamic
        enum_name="Genre*",
        enums_sheet=genres_sheet,
        value_set=value_sets["genres"],
    )

    add_section(
        worksheet=products,
        section_name="Product Scheduling & Pricing",
        col_names=[
            "Release Date*",
            "Sale Start Date*",
            ["iTunes Pre-Order*"] + value_sets["itunes_preorder_options"],
            "iTunes Pre-Order Date",
            ["Preorder Preview**"] + value_sets["yes_no"],
            ["Album Pricing*"] + value_sets["pricing_categories"],
            ["Track Pricing*"] + value_sets["track_pricing"],
        ],
        tooltips=[
            "Enter the date on which the product was/wil be first released. This needs to be in YYYY-MM-DD format.",
            "The date this product will be released to stores; commonly known as street date. This needs to be in "
            + "YYYY-MM-DD format.",
            'Choose "Specific Date" for the pre-order field. If the pre-order field is left blank, it will be '
            + 'defaulted to "no pre-order".',
            "Enter pre-order date. This needs to be in YYYY-MM-DD format.",
            "If 'Specific Date' has been selected, add whether your product will be previewable for pre-orders.",
            "Please select the price point for your release. They're ordered from most to least expensive and "
            + "initially set to the most common selection. We will map to the closest price at each store based on "
            + "your selection, but please note pricing across providers is not always the same. As always, final "
            + "retail price is set by the provider and cannot be guaranteed.",
            "Please select the price point for all tracks on your product. As always, final retail price is set by "
            + "the provider and cannot be guaranteed.",
        ],
        enums_sheet=enums,
    )

    add_section(
        worksheet=products,
        section_name="Territory Restrictions (Carveouts)",
        col_names=[
            "Cleared for Sale",
            "NOT Cleared for Sale",
        ],
        tooltips=[
            "Restrictions are defaulted to territories as stipulated in your contractual terms. If you need to make "
            + "any additional revisions, you may do so here. If left blank, we will default the values to worldwide.",
            "Restrictions are defaulted to territories as stipulated in your contractual terms. If you need to make "
            + "any additional revisions, you may do so here. If left blank, we will default the values to worldwide.",
        ],
        enums_sheet=enums,
        section_tooltip="Leaving these values blank will default to distributing Worldwide, minus contract level restrictions.",
    )

    row = 1
    for territory in value_sets["territories"]:
        col = accepted_territories_sheet[f"A{row}"]
        col.value = territory
        row += 1

    product_contributor_cols = [
        "Product Code*",
        ["Contributor Role*"] + value_sets["product_contributor_roles"],
        "Contributor Name*",
        "Apple ID",
        "Spotify ID",
    ]
    product_contributor_tooltips = [
        "Add the code of the product that this contributor appears on. This Product code must match one added on "
        + 'the "Products" sheet',
        "Only enter contributors that appear on every track of this product. Please note that certain services "
        + "will display 'Various Artists' when four or more product artists are entered.",
        "Only enter contributors that appear on every track of this product",
        ID_RESTRICTION_TOOLTIP.format("Apple"),
        ID_RESTRICTION_TOOLTIP.format("Spotify"),
    ]

    if not is_classical:
        product_contributor_cols.append(["Featured to Primary"] + value_sets["yes_no"])
        product_contributor_tooltips.append(
            "Deliver some Featuring Artists to Spotify, Tidal and Deezer as Primary? Please note this value will only "
            + 'apply if "Featuring Artist" has been selected as Contributor Role'
        )

    add_section(
        worksheet=product_contributors,
        section_name="Product Contributors",
        col_names=product_contributor_cols,
        tooltips=product_contributor_tooltips,
        enums_sheet=enums,
        is_first_section=True,
    )

    add_section(
        worksheet=product_localizations,
        section_name="Product Localizations",
        col_names=[
            "Product Code*",
            ["Localized Language*"] + value_sets["localization_languages"],
            "Localized Product Name*",
            ["Localized Field"] + value_sets["product_localization_fields"],
            "Localized Value",
        ],
        tooltips=[None, None, None, None, None],
        enums_sheet=enums,
        is_first_section=True,
    )

    add_section(
        worksheet=tracks,
        section_name="",
        col_names=["Product Code*", "ISRC"],
        tooltips=[
            "Add the code of the product that this track appears on. This Product code must match one added on the "
            + '"Products" sheet',
            "International Standard Recording code needs to be a 12 character alphanumeric code that identifies a "
            + "unique recording. If you have already assigned an ISRC to a given track enter it in this field. If left "
            + "blank, we will assign a ISRC to your track.",
        ],
        enums_sheet=enums,
        is_first_section=True,
    )

    track_basics_cols: list[str | list[str]] = [
        "Volume*",
        "Track Number*",
    ]
    track_basics_tooltips: list[str | None] = [
        'If you product is split into different volumes, add 1, 2, 3 etc. Add "1" to all tracks on this product '
        + "if there are no distinguishable volumes.",
        "Tracks numbers must start at 1 and be sequential.",
    ]

    if not is_classical:
        track_basics_cols.append("Track Name*")
        track_basics_tooltips.append(
            "The name of this song / track / composition, as you want it to appear in stores."
        )
    else:
        track_basics_cols.extend(
            [
                "Work*",
                "Movement",
            ]
        )
        track_basics_tooltips.extend(
            [
                "The name of this work, as you want it to appear in stores.",
                "The name of this movement, as you want it to appear in stores.",
            ]
        )

    track_basics_cols.extend(
        [
            "Track Version",
            ["Lyrics Language*"] + value_sets["lyric_languages"],
            ["Explicit Content*"] + value_sets["explicit"],
            "Track Lyrics",
            "TikTok & Preview Start Point",
        ]
    )
    track_basics_tooltips.extend(
        [
            "The version of the track that will be delivered to stores. This can include anything you want to appear "
            + 'after the track name, except for artist names. Example: "Radio Edit" or "Unreleased Live Track".',
            "This is the language used in the performance of the track. For instrumentals, please select "
            + '"Instrumental" as your Lyrics Language. Please use only accepted values from the dropdown.',
            'Select "Yes" if this track contains explicit language or subject matter not appropriate for all '
            + 'audiences. Only select "Clean" if this is the clean version of another track. Do not select Clean if '
            + 'this is the only version of this track. Do not select "Clean" if this is the only version of this '
            + "track.",
            "Please refer to Apple Lyrics Guidelines for all formatting requirements",
            "This is where the track will begin when listeners are previewing it at stores. The formatting should be "
            "HH:MM:SS (hours.minutes:seconds). If the start time goes beyond the length of the audio file, "
            + "this fields data will not be ingested.",
        ]
    )

    add_section(
        worksheet=tracks,
        section_name="Track Basics",
        col_names=track_basics_cols,
        tooltips=track_basics_tooltips,
        enums_sheet=enums,
    )

    col = tracks.column_dimensions["K" if is_classical else "J"]
    col.number_format = "@"

    add_section(
        worksheet=tracks,
        section_name="Audio File",
        col_names=["File Name"],
        tooltips=[
            "Enter the filename of this tracks audio file. Audio file names must be unique."
        ],
        enums_sheet=enums,
    )

    required_track_contributor_cols: list[str | list[str]] = [
        "Primary Artist*",
        "Primary Artist Apple ID",
        "Primary Artist Spotify ID",
        "Songwriter*",
    ]
    required_track_contributor_tooltips: list[str | None] = [
        'Add 1 main artist for this track. Other primary artists can be added in the "Other Track Contributors" '
        + "sheet.",
        "Add the Apple ID for the Primary Artist",
        "Add the Spotify ID for the Primary Artist",
        'Add 1 Songwriter for this track. Other Songwriters can be added in the "Other Track Contributors" sheet.',
    ]

    if not is_classical:
        required_track_contributor_cols.append("Producer*")
        required_track_contributor_tooltips.append(
            'Add 1 Producer for this track. Other Producers can be added in the "Other Track Contributors" sheet.'
        )
    else:
        required_track_contributor_cols.append("Composer*")
        required_track_contributor_tooltips.append(
            "Name of the primary composer who composed the material on this track. Do no include arrangers, "
            + "librettists, or lyricists unless they are also the composer. Other Composers can be added in the "
            + '"Other Track Contributors" sheet.'
        )

    required_track_contributor_cols.extend(
        [
            "Primary Performer Legal Name*",
            ["Primary Performer Role*"]
            + value_sets["track_contributor_roles"]["Primary Performer"]["values"],
        ]
    )
    required_track_contributor_tooltips.extend(
        [
            'Add 1 Primary Performer for this track. Other Primary Performers can be added in the "Other Track '
            + 'Contributors" sheet.',
            "Please use only accepted roles from the dropdown",
        ]
    )

    add_section(
        worksheet=tracks,
        section_name="Required Track Contributors",
        col_names=required_track_contributor_cols,
        tooltips=required_track_contributor_tooltips,
        enums_sheet=enums,
        section_tooltip="Please only add one contributor in each of these cells. Additional contributors can be "
        + 'added in the "Other Track Contributors" tab',
    )

    add_section(
        worksheet=tracks,
        section_name="Sound Recording Rights",
        col_names=[
            "(P) Line*",
            ["Ownership of Sound Recording*"]
            + value_sets["ownership_of_sound_recording"],
            ["Country of Recording**"] + value_sets["countries"],
            ["Nationality of Original Copyright Owner**"] + value_sets["countries"],
        ],
        tooltips=[
            None,
            "The owner of the master copyright is entitled to claim royalties generated by various uses of the "
            + '"master" sound recording. Depending on the country, royalties can be generated from "public '
            + 'performances" of a sound recording, such as when played at a bar or nightclub, or when broadcast on '
            + "television or radio. The uses of a sound recording in user-generated content (UGC), such as Youtube "
            + "videos, can also generate royalties. The master rights usually belong to the entity that financed a "
            + "song's recording. which is typically a record label. Please use only accepted values from the dropdown.",
            "Choose the country where the majority of the recording sessions for this track took place. Please use "
            + "only accepted values from the dropdown. This is not required if Ownership of Sound Recording = I have no master rights.",
            "Choose the country where the original owner of the master rights was based or incorporated at the time "
            + "of recording. The original owner can be an individual person or a business entity. Please use only "
            + "accepted values from the dropdown. This is not required if Ownership of Sound Recording = I have no master rights.",
        ],
        enums_sheet=enums,
    )

    add_section(
        worksheet=tracks,
        section_name="Publishing",
        col_names=[
            ["US Publishing Obligation*"] + value_sets["us_publishing_obligation"],
            "Publishers",
            ["Withhold publishing royalty from US physical Revenues**"]
            + value_sets["yes_no"],
        ],
        tooltips=[
            'Indicate the publishing obligation for this track in the US. Note that if "100% controlled/administered" '
            + "applies, publishing amount will be paid directly through to you, and you are responsible for any and "
            + 'all publishing payments. If "Public Domain in the US, or non-music" applies, you are representing that '
            + "no publishing licenses are required, and no deductions will apply.",
            "Publishers featured on this particular track. If there are multiple publishers, "
            + "please enter them as <Publisher1><Publisher2>. "
            + "Do not use commas or pipes to separate.",
            'This column only needs to be selected if Mechanical Deductions have been defined on the contract and "Less '
            + 'than 100% controlled" under the US Publishing Obligation column has been selected. '
            + "If you select Yes, you authorize us to seek a mechanical license on your behalf. "
            + "We will withhold the publishing obligation from any download or ringtone revenues "
            + "the track has generated in the US.",
        ],
        enums_sheet=enums,
    )

    add_section(
        worksheet=other_track_contributors,
        section_name="",
        col_names=[
            "Product Code*",
            "Volume*",
            "Track Number*",
        ],
        tooltips=[None, None, None],
        enums_sheet=enums,
        is_first_section=True,
    )

    other_track_contributors_cols: list[str | list[str]] = [
        "Contributor Type*",
        "Contributor Role*",
        "Contributor Name*",
        "Apple ID",
        "Spotify ID",
    ]
    other_track_contributors_tooltips: list[str | None] = [
        None,
        None,
        None,
        ID_RESTRICTION_TOOLTIP.format("Apple"),
        ID_RESTRICTION_TOOLTIP.format("Spotify"),
    ]

    if not is_classical:
        other_track_contributors_cols.append(
            ["Featured to Primary"] + value_sets["yes_no"]
        )
        other_track_contributors_tooltips.append(None)

    add_section(
        worksheet=other_track_contributors,
        section_name="Other Track Contributors",
        col_names=other_track_contributors_cols,
        tooltips=other_track_contributors_tooltips,
        enums_sheet=enums,
    )

    track_contributors_sheet = template.create_sheet("track_contributors")
    track_contributors_sheet.sheet_state = "hidden"

    add_value_set_validation(
        workbook=template,
        worksheet=other_track_contributors,
        start_row=3,
        columns=("D", "E"),  # this should be dynamic
        enum_name="Contributor Type*",
        enums_sheet=track_contributors_sheet,
        value_set=value_sets["track_contributor_roles"],
    )

    add_section(
        worksheet=track_localizations,
        section_name="Track Localizations",
        col_names=[
            "Product Code*",
            "Volume*",
            "Track Number*",
            ["Localized Language*"] + value_sets["localization_languages"],
            "Localized Work*" if is_classical else "Localized Track Name*",
            ["Localized Field"] + value_sets["track_localization_fields"],
            "Localized Value",
        ],
        tooltips=[None, None, None, None, None, None, None],
        enums_sheet=enums,
        is_first_section=True,
    )

    return template


class TemplateLink(TypedDict):
    download_link: str | None
    id: UUID4


def id_to_key(id: UUID4) -> str:
    return f"exports/{id}.xlsx"