begin;
-- improve vw_fiche_attr
alter table fiche add column f_enable char(1);
update fiche set f_enable=ad_value from fiche_detail as fd1 where fd1.f_id=fiche.f_id and ad_id=54;
update fiche set f_enable='1' where f_enable is null;
alter table fiche alter f_enable set  not null;
alter table fiche add constraint f_enable_ck  check (f_enable in ('0','1'));
comment on column fiche.f_enable is 'value = 1 if card enable , otherwise 0 ';

-- improve performance on this view
drop index if exists fiche_detail_attr_ix;
create index fiche_detail_attr_ix on fiche_detail (ad_id);

drop view vw_fiche_attr cascade;

-- add "fiche.f_enable" in the view
create view vw_fiche_attr as SELECT a.f_id,
                                    a.fd_id,
                                    a.ad_value AS vw_name,
                                    k.ad_value AS vw_first_name,
                                    b.ad_value AS vw_sell,
                                    c.ad_value AS vw_buy,
                                    d.ad_value AS tva_code,
                                    tva_rate.tva_id,
                                    tva_rate.tva_rate,
                                    tva_rate.tva_label,
                                    e.ad_value AS vw_addr,
                                    f.ad_value AS vw_cp,
                                    j.ad_value AS quick_code,
                                    h.ad_value AS vw_description,
                                    i.ad_value AS tva_num,
                                    fiche_def.frd_id,
                                    l.ad_value AS accounting,
                                    f_enable
                             FROM ( SELECT fiche.f_id,
                                           fiche.fd_id,
                                           fiche.f_enable,
                                           fiche_detail.ad_value
                                    FROM fiche
                                             LEFT JOIN fiche_detail USING (f_id)
                                    WHERE fiche_detail.ad_id = 1) a
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 6) b ON a.f_id = b.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 7) c ON a.f_id = c.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 2) d ON a.f_id = d.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 14) e ON a.f_id = e.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 15) f ON a.f_id = f.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 23) j ON a.f_id = j.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 9) h ON a.f_id = h.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 13) i ON a.f_id = i.f_id
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 32) k ON a.f_id = k.f_id
                                      LEFT JOIN tva_rate ON d.ad_value = tva_rate.tva_id::text
                                      JOIN fiche_def USING (fd_id)
                                      LEFT JOIN ( SELECT fiche_detail.f_id,
                                                         fiche_detail.ad_value
                                                  FROM fiche_detail
                                                  WHERE fiche_detail.ad_id = 5) l ON a.f_id = l.f_id
;

create view v_detail_sale as
WITH m AS (
    SELECT sum(quant_sold_1.qs_price) AS htva,
           sum(quant_sold_1.qs_vat) AS tot_vat,
           sum(quant_sold_1.qs_vat_sided) AS tot_tva_np,
           jrn_1.jr_id
    FROM quant_sold quant_sold_1
             JOIN jrnx jrnx_1 USING (j_id)
             JOIN jrn jrn_1 ON jrnx_1.j_grpt = jrn_1.jr_grpt_id
    GROUP BY jrn_1.jr_id
)
SELECT jrn.jr_id,
       jrn.jr_date,
       jrn.jr_date_paid,
       jrn.jr_ech,
       jrn.jr_tech_per,
       jrn.jr_comment,
       jrn.jr_pj_number,
       jrn.jr_internal,
       jrn.jr_def_id,
       jrnx.j_poste,
       jrnx.j_text,
       jrnx.j_qcode,
       jrn.jr_rapt,
       quant_sold.qs_fiche AS item_card,
       a.name AS item_name,
       quant_sold.qs_client,
       b.vw_name AS tiers_name,
       b.quick_code,
       tva_rate.tva_label,
       tva_rate.tva_comment,
       tva_rate.tva_both_side,
       quant_sold.qs_vat_sided AS vat_sided,
       quant_sold.qs_vat_code AS vat_code,
       quant_sold.qs_vat AS vat,
       quant_sold.qs_price AS price,
       quant_sold.qs_quantite AS quantity,
       quant_sold.qs_price / quant_sold.qs_quantite AS price_per_unit,
       m.htva,
       m.tot_vat,
       m.tot_tva_np,
       oc.oc_amount,
       oc.oc_vat_amount,
       ( SELECT currency.cr_code_iso
         FROM currency
         WHERE jrn.currency_id = currency.id) AS cr_code_iso
FROM jrn
         JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
         JOIN quant_sold USING (j_id)
         JOIN vw_fiche_name a ON quant_sold.qs_fiche = a.f_id
         JOIN vw_fiche_attr b ON quant_sold.qs_client = b.f_id
         JOIN tva_rate ON quant_sold.qs_vat_code = tva_rate.tva_id
         JOIN m ON m.jr_id = jrn.jr_id
         LEFT JOIN operation_currency oc ON oc.j_id = jrnx.j_id;

create view v_detail_purchase as
WITH m AS (
    SELECT sum(quant_purchase_1.qp_price) AS htva,
           sum(quant_purchase_1.qp_vat) AS tot_vat,
           sum(quant_purchase_1.qp_vat_sided) AS tot_tva_np,
           jrn_1.jr_id
    FROM quant_purchase quant_purchase_1
             JOIN jrnx jrnx_1 USING (j_id)
             JOIN jrn jrn_1 ON jrnx_1.j_grpt = jrn_1.jr_grpt_id
    GROUP BY jrn_1.jr_id
)
SELECT jrn.jr_id,
       jrn.jr_date,
       jrn.jr_date_paid,
       jrn.jr_ech,
       jrn.jr_tech_per,
       jrn.jr_comment,
       jrn.jr_pj_number,
       jrn.jr_internal,
       jrn.jr_def_id,
       jrnx.j_poste,
       jrnx.j_text,
       jrnx.j_qcode,
       jrn.jr_rapt,
       quant_purchase.qp_fiche AS item_card,
       a.name AS item_name,
       quant_purchase.qp_supplier,
       b.vw_name AS tiers_name,
       b.quick_code,
       tva_rate.tva_label,
       tva_rate.tva_comment,
       tva_rate.tva_both_side,
       quant_purchase.qp_vat_sided AS vat_sided,
       quant_purchase.qp_vat_code AS vat_code,
       quant_purchase.qp_vat AS vat,
       quant_purchase.qp_price AS price,
       quant_purchase.qp_quantite AS quantity,
       quant_purchase.qp_price / quant_purchase.qp_quantite AS price_per_unit,
       quant_purchase.qp_nd_amount AS non_ded_amount,
       quant_purchase.qp_nd_tva AS non_ded_tva,
       quant_purchase.qp_nd_tva_recup AS non_ded_tva_recup,
       m.htva,
       m.tot_vat,
       m.tot_tva_np,
       oc.oc_amount,
       oc.oc_vat_amount,
       ( SELECT currency.cr_code_iso
         FROM currency
         WHERE jrn.currency_id = currency.id) AS cr_code_iso
FROM jrn
         JOIN jrnx ON jrn.jr_grpt_id = jrnx.j_grpt
         JOIN quant_purchase USING (j_id)
         JOIN vw_fiche_name a ON quant_purchase.qp_fiche = a.f_id
         JOIN vw_fiche_attr b ON quant_purchase.qp_supplier = b.f_id
         JOIN tva_rate ON quant_purchase.qp_vat_code = tva_rate.tva_id
         JOIN m ON m.jr_id = jrn.jr_id
         LEFT JOIN operation_currency oc ON oc.j_id = jrnx.j_id;


COMMENT ON VIEW vw_fiche_attr IS 'Some attribute for all cards';
comment on view v_detail_sale is 'Summary one row by sale ';
comment on view v_detail_purchase is 'Summary one row by purchase';

-- remove
delete from fiche_detail where ad_id=54;
delete from jnt_fic_attr where ad_id =54;
delete from attr_min  where ad_id=54;
delete from attr_def where ad_id=54;


-- open COMPANY when PARAM is choosen for profile 1
update profile_menu set pm_default=1,p_order=1 where pm_id=54;

update menu_ref set me_menu ='Nouvel achat' where me_code in ('ACH');
update menu_ref set me_menu ='Nouvelle vente' where me_code in ('VEN');
update menu_ref set me_menu ='Nouvelle opération' where me_code in ('ODS');
COMMENT ON TABLE public.action_gestion_comment IS 'comment on action management';
COMMENT ON TABLE public.action_gestion_operation IS 'Operation linked on action';
COMMENT ON TABLE public.action_gestion_related IS 'link between action';
COMMENT ON TABLE public.action_tags IS 'Tags link to  action';

COMMENT ON TABLE public.currency IS 'currency';
COMMENT ON TABLE public.currency_history IS 'currency values history';
COMMENT ON TABLE public.del_action IS 'deleted actions';
COMMENT ON TABLE public.del_jrn IS 'deleted operation';
COMMENT ON TABLE public.del_jrnx IS 'delete operation details';
COMMENT ON TABLE public.forecast_cat IS 'Category of forecast';
COMMENT ON TABLE public.forecast_item IS 'items of forecast';
COMMENT ON TABLE public.formdef IS 'Simple Report name';
COMMENT ON TABLE public.form IS 'Simple report definition';
COMMENT ON TABLE public.groupe_analytique IS 'Group of analytic accountancy';
COMMENT ON TABLE public.jnt_document_option_contact IS 'Many to many table between document and contact option';
COMMENT ON TABLE public.jnt_letter IS 'm2m tables for lettering';
COMMENT ON TABLE public.jrn_info IS 'extra info for operation';
COMMENT ON TABLE public.jrn_periode IS 'Period by ledger';
COMMENT ON TABLE public.letter_cred IS 'Letter cred';
COMMENT ON TABLE public.letter_deb IS 'letter deb';
COMMENT ON TABLE public.menu_default IS 'default menu for certains actions';
COMMENT ON TABLE public.menu_ref IS 'Definition of all the menu';
COMMENT ON TABLE public.operation_currency IS 'Information about currency';
COMMENT ON TABLE public.operation_tag IS 'Tag for operation';
COMMENT ON TABLE public.parm_code IS 'Parameter code and accountancy';
COMMENT ON TABLE public.profile_menu_type IS 'Type of menu';
COMMENT ON TABLE public.quant_purchase IS 'Supplemental info for purchase';
COMMENT ON TABLE public.stock_change IS 'Change of stock';
COMMENT ON TABLE public.tags IS 'Tags name';
COMMENT ON TABLE public.user_filter IS 'Filter for the search';
COMMENT ON TABLE public.user_active_security IS 'Security for user';
COMMENT ON TABLE public.user_sec_act IS 'Security on actions for user';
COMMENT ON TABLE public.user_sec_jrn IS 'Security on ledger for users';
COMMENT ON TABLE public."version" IS 'DB version';
insert into version (val,v_description) values (152,'Comment on tables,card structure change');
commit;
