"""Queries."""
from constants import constants
from ddex_ingester_common.constants.flow_control import \
    PROCESS_NON_CONFIDENTIAL_HOURS_BEFORE_RELEASE

# queries
UPDATE_ROW_STATUS = """
  UPDATE ddex_ingester.ddex_delivery_to_ingest
  SET status = 'ingest_started'
  WHERE ddex_delivery_to_ingest_id = %s;
"""

"""Select count of rows from a given table.
catalog_ingestion_source_id = 5 is SME_ANALYTICS_PROVIDER
"""
NON_CONFIDENTIAL_DDEX_DELIVERIES = f"""
with latest_ddex_delivery_to_ingest  as (
    select
        max(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id ,
        has_artwork,
        grid
    from ddex_ingester.ddex_delivery_to_ingest
        where catalog_ingestion_source_id = 5
        group by grid, has_artwork
), latest_ddex_delivery_to_ingest_id_by_grid as (
    select
        max(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id,
        grid
    from latest_ddex_delivery_to_ingest
        group by grid
), grid_artwork_ingestion_status as (
    select
       i.grid,
       i.status,
       i.has_artwork,
       i.ddex_delivery_to_ingest_id,
       i.s3_key_name
    from ddex_ingester.ddex_delivery_to_ingest i
    inner join latest_ddex_delivery_to_ingest u on i.ddex_delivery_to_ingest_id =u.ddex_delivery_to_ingest_id
        where catalog_ingestion_source_id = 5
        and i.has_artwork
), non_confidential_products as (
    select
        ddti.s3_key_name
    from ddex_ingester.ddex_delivery_to_ingest ddti
        inner join latest_ddex_delivery_to_ingest_id_by_grid lgrid on ddti.ddex_delivery_to_ingest_id = lgrid.ddex_delivery_to_ingest_id
        where NOT ddti.is_confidential
        and ddti.catalog_ingestion_source_id = 5
), non_confidential_products_to_process as (
    select *
    from ddex_ingester.ddex_delivery_to_ingest ddti
        where ddti.s3_key_name in (select cp.s3_key_name from non_confidential_products cp)
        and catalog_ingestion_source_id  = 5
        and status = 'delivered'
        and DATE_SUB(original_release_datetime, INTERVAL {PROCESS_NON_CONFIDENTIAL_HOURS_BEFORE_RELEASE} HOUR) < now()
        and NOT is_confidential 
), non_conf_products_to_process_without_products_in_progress as (
    select
        nc.*
    from non_confidential_products_to_process nc
        left join ddex_ingester.ddex_delivery_to_ingest ddti on ddti.grid = nc.grid and ddti.catalog_ingestion_source_id = 5 and ddti.status = 'ingest_started'
        where ddti.grid is null
),
non_confidential_products_to_process_artwork_only as (
 select
        s.ddex_delivery_to_ingest_id,
        s.grid
    from grid_artwork_ingestion_status s
        join non_conf_products_to_process_without_products_in_progress p on s.grid = p.grid
        left join non_conf_products_to_process_without_products_in_progress p1 on p1.s3_key_name = s.s3_key_name
        where s.status = 'delivered'
        and p1.s3_key_name is null
), run_only_one_full_ingestion_per_s3_key as (
    select 
        min(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id, 
        s3_key_name 
    from non_conf_products_to_process_without_products_in_progress 
        group by s3_key_name 
) 
select '{constants.ARTWORK_INGESTION_ONLY_INGESTION}' as ingestion_type, ddti.*
from ddex_ingester.ddex_delivery_to_ingest ddti
    join non_confidential_products_to_process_artwork_only a on a.ddex_delivery_to_ingest_id = ddti.ddex_delivery_to_ingest_id
    where catalog_ingestion_source_id = 5
union
select '{constants.FULL_INGESTION}' as ingestion_type, cptp.*
from non_conf_products_to_process_without_products_in_progress cptp
    join run_only_one_full_ingestion_per_s3_key roo on roo.ddex_delivery_to_ingest_id = cptp.ddex_delivery_to_ingest_id
    left join non_confidential_products_to_process_artwork_only ar on cptp.grid = ar.grid
    where ar.grid  is null 
"""

CONFIDENTIAL_DDEX_DELIVERIES = f"""
with latest_ddex_delivery_to_ingest  as (
    select
        max(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id ,
        has_artwork,
        grid
    from ddex_ingester.ddex_delivery_to_ingest
        where catalog_ingestion_source_id = 5
        group by grid, has_artwork
), latest_ddex_delivery_to_ingest_id_by_grid as (
    select
        max(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id,
        grid
    from latest_ddex_delivery_to_ingest
        group by grid
), grid_artwork_ingestion_status as (
    select
       i.grid,
       i.status,
       i.has_artwork,
       i.ddex_delivery_to_ingest_id,
       i.s3_key_name
    from ddex_ingester.ddex_delivery_to_ingest i
    inner join latest_ddex_delivery_to_ingest u on i.ddex_delivery_to_ingest_id =u.ddex_delivery_to_ingest_id
        where catalog_ingestion_source_id = 5
        and i.has_artwork
), confidential_products as (
    select
        ddti.s3_key_name
    from ddex_ingester.ddex_delivery_to_ingest ddti
        inner join latest_ddex_delivery_to_ingest_id_by_grid lgrid on ddti.ddex_delivery_to_ingest_id = lgrid.ddex_delivery_to_ingest_id
        where ddti.is_confidential
        and ddti.catalog_ingestion_source_id = 5
), confidential_products_to_process as (
    select *
    from ddex_ingester.ddex_delivery_to_ingest ddti
        where ddti.s3_key_name in (select cp.s3_key_name from confidential_products cp)
        and catalog_ingestion_source_id  = 5
        and status = 'delivered'
        and ddti.original_release_datetime <= now()
        and is_confidential 
), confidential_products_to_process_without_products_in_progress as (
    select
        nc.*
    from confidential_products_to_process nc
        left join ddex_ingester.ddex_delivery_to_ingest ddti on ddti.grid = nc.grid and ddti.catalog_ingestion_source_id = 5 and ddti.status = 'ingest_started'
        where ddti.grid is null
),
confidential_products_to_process_artwork_only as (
 select
        s.ddex_delivery_to_ingest_id,
        s.grid
    from grid_artwork_ingestion_status s
        join confidential_products_to_process_without_products_in_progress p on s.grid = p.grid
        left join confidential_products_to_process_without_products_in_progress p1 on p1.s3_key_name = s.s3_key_name
        where s.status = 'delivered'
        and p1.s3_key_name is null
), run_only_one_full_ingestion_per_s3_key as (
    select 
        min(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id, 
        s3_key_name 
    from confidential_products_to_process_without_products_in_progress 
        group by s3_key_name 
) 
select '{constants.ARTWORK_INGESTION_ONLY_INGESTION}' as ingestion_type, ddti.*
from ddex_ingester.ddex_delivery_to_ingest ddti
    join confidential_products_to_process_artwork_only a on a.ddex_delivery_to_ingest_id = ddti.ddex_delivery_to_ingest_id
    where catalog_ingestion_source_id = 5
union
select '{constants.FULL_INGESTION}' as ingestion_type, cptp.*
from confidential_products_to_process_without_products_in_progress cptp
    join run_only_one_full_ingestion_per_s3_key roo on roo.ddex_delivery_to_ingest_id = cptp.ddex_delivery_to_ingest_id
    left join confidential_products_to_process_artwork_only ar on cptp.grid = ar.grid
    where ar.grid  is null 
"""

PURGED_DDEX_DELIVERIES = """
with latest_ddex_delivery_to_ingest  as (
    select
        max(ddex_delivery_to_ingest_id) as ddex_delivery_to_ingest_id ,
        grid
    from ddex_ingester.ddex_delivery_to_ingest
        where catalog_ingestion_source_id = 5
        group by grid
)
select * from ddex_ingester.ddex_delivery_to_ingest ddti 
    inner join latest_ddex_delivery_to_ingest g on ddti.ddex_delivery_to_ingest_id = g.ddex_delivery_to_ingest_id
where catalog_ingestion_source_id = 5 
    and status = 'purged_release'
"""