/*
 * Cabeçalho FAP nova - Techprene
 */
select distinct
       /*
       l.nome_reduzido                                                 as licença,
       case
       	 when coalesce(l.active, false) then 'Ativa'
       	 else 'Inativa'
       end                                                             as ativa,
       */
       p.reference                                                     as referencia,
       case
       	when p.description ~* '^display protein' then 'Não'
       	else 'Sim'
       end                                                             as compartilha_com_franquias,
       --p.description                                                   as descricao,
       --p.description                                                   as nome_reduzido,
       p.description                                                   as nome_produto,
       p.ncm                                                           as ncm,
       to_char(p.ipialiquot, 'FM999999990D00')                         as ipi,
       to_char(fc.comprimento, 'FM999999990D00')                       as comprimento,
       to_char(fc.largura, 'FM999999990D00')                           as largura,
       to_char(fc.altura, 'FM999999990D00')                            as altura,
       to_char(fc.peso, 'FM999999990D000')                             as peso,
       to_char(p.preco_sell_in, 'FM999999990D00')                      as sellin,
       to_char(p.preco_sell_out, 'FM999999990D00')                     as sellout,
       case
	     when un.name ~* 'pc' then 'UN'
	     when un.name ~* 'un' then 'UN'
	     else un.name
	   end                                                             as unidade,
       --p.description                                                   as nome_completo,
       regexp_replace(p.caracteristicas_produto, E'[\r\n]+', ';', 'g') as caracteristicas,
       p.palavras_chave                                                as palavras_chave,
       'Mormaii'                                                       as marca,
              case
         when upper(col.name) = 'ATEMPORAL' then to_char(p.registrationdate, 'YYYY')
         else to_char(p.registrationdate, 'YYYY')
       end                                                             as colecao,
       'Atemporal'                                                     as temporada,
       case
         when upper(gen.name) = 'FEMININO' then 'Feminino'
         when upper(gen.name) = 'MASCULINO' then 'Masculino'
         when upper(gen.name) = 'UNISSEX' then 'Unissex'
         else ''
       end                                                             as genero,
       case
         when fai.name ~* 'adulto' then 'Adulto'
         when fai.name ~* 'infantil' then 'Infantil'
         when fai.name ~* 'todas' then 'Todas as Idades'
         else ''
       end                                                             as faixa_etaria,
       'Código de Defesa do Consumidor'                                as garantia,
       'A'                                                             as classe,
       case
         when col.name ~* 'time brasil' then 'COB - Time Brasil'
         when p.description ~* 'time brasil' then 'COB - Time Brasil'
         else 'N/D'
       end                                                             as colab,
       null                                                            as grupo,
       null                                                            as categoria,
       null                                                            as subcategoria,
       'N/D'                                                           as influencer,
       null                                                            as segmento,
       null                                                            as aplicacao_uso, 
       'N/D'                                                           as familia,
       case
         when fc.origem = '' then 'Brasil'
         when fc.origem is null then 'Brasil'
         else btrim(fn_pri_maiuscula(fc.origem))
       end                                                             as pais
from product p
left join sim_fpa02_produto            fp  on fp.ean = p.ean
left join sim_ficha_analise_produto_02 fc  on fc.id = fp.ficha02_id
left join measure_unit                 un  on un.id = p.measureunit_id
left join collection                   col on col.id = p.collection_id
left join division                     fai on fai.id = p.division_id
left join department                   gen on gen.id = p.department_id
left join licensed                     l   on l.id = p.licensed_id
where p.licensed_id = 19
  and p.reference not like 'EAN-%'
  and p.registrationdate >= '2026-01-01'
order by p.reference

/*
 * SKU FAP nova - Techprene
 */
select /*l.nome_reduzido   as licença,
       case
       	 when coalesce(l.active, false) then 'Ativa'
       	 else 'Inativa'
       end               as ativa,
       */
       p.reference       as referencia,
       ''                as cor,
       ''                as tam,
       p.ean             as ean,
       c.sim_cormormaii  as cor_licenca,
       t.name            as tam_licenca
from product p
left join size t on t.id = p.size_id
left join color c on c.id = p.color_id
left join licensed l on l.id = p.licensed_id
where p.licensed_id = 19
  and p.reference not like 'EAN-%'
  and p.registrationdate >= '2026-01-01'
order by p.reference, c.sim_cormormaii, t.name, p.ean



/*
 * Relação de Licenças - Techprene
 */
select l.id,
       l.cnpj,
       l.nome_reduzido,
       case
	     when coalesce(active, false) then 'Ativa'
	     else 'Inativa'
	   end as ativa
from licensed l
where l.id in = 19
order by "ativa", l.id


/*
 * Validações - Techprene
 */
select distinct
       l.nome_reduzido,
       p.reference,
       p.description,
       --to_char(p.registrationdate, 'YYYY') as registro,
       --col.name as colecao_original,
       case
         when upper(col.name) = 'ATEMPORAL' then to_char(p.registrationdate, 'YYYY')
         else to_char(p.registrationdate, 'YYYY')
       end as colecao,
       /*
       case
         when col.name like '%2021%' then 'Atemporal'
         when col.name like '%2022%' then 'Atemporal'
         when col.name like '%2023%' then 'Atemporal'
         when col.name like '%2024%' then 'Atemporal'
         when col.name like '%VERAO%' then 'Verão'
         when upper(col.name) = 'ATEMPORAL' then 'Atemporal' 
         when upper(col.name) = 'BAIXA' then 'Atemporal'
         else ''
       end as temporada,
       */
       'Atemporal' as temporada,
       --fai.name as faixa_etaria_original,
       case
         when fai.name ~* 'adulto' then 'Adulto'
         when fai.name ~* 'infantil' then 'Infantil'
         when fai.name ~* 'todas' then 'Todas as Idades'
         else ''
       end as faixa_etaria,
       --gen.name as genero_original,
       case
         when upper(gen.name) = 'FEMININO' then 'Feminino'
         when upper(gen.name) = 'MASCULINO' then 'Masculino'
         when upper(gen.name) = 'UNISSEX' then 'Unissex'
       end as genero
from product p
left join collection col on col.id = p.collection_id
left join division   fai on fai.id = p.division_id
left join department gen on gen.id = p.department_id
left join licensed   l   on l.id = p.licensed_id
where p.licensed_id = 19
  and p.reference not like 'EAN-%'
  and p.registrationdate >= '2026-01-01'
order by l.nome_reduzido, p.reference
























