-- Function: public.export_sas7bdat(text, text)

-- DROP FUNCTION public.export_sas7bdat(text, text);

CREATE OR REPLACE FUNCTION public.export_sas7bdat(
    tbl text DEFAULT 'p1_currency'::text,
    path text DEFAULT '/media/sf_PinkTransfert/'::text)
  RETURNS text AS
$BODY$

require(RPostgreSQL)
require(haven)
require(data.table)


return('makes server explode out of RAM on big tables. NOT SAFE altoough brilliant')


# NOT USED BY PL/R
con = dbConnect(dbDriver("PostgreSQL"), user="selector", password="select01",
                host="192.168.1.50", dbname="postgres")

time_start = Sys.time()

data <- dbGetQuery(con, paste0("TABLE ", tbl))

time_get = Sys.time()
pg.thrownotice(paste0('done fetch data: ', (time_get - time_start)))

write_sas(data, paste0(path, tbl, '.sas7dbat'))
fwrite(data, paste0(path, tbl, '.csv'))

time_write = Sys.time()

pg.thrownotice(paste0('done export data: ', (time_write - time_get) ))

return(paste0(tbl, ' exported to ', path))

$BODY$
  LANGUAGE plr VOLATILE
  COST 100;
ALTER FUNCTION public.export_sas7bdat(text, text)
  OWNER TO datamart;

SELECT
  nspname AS schemaname, relname, reltuples::bigint
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;/*

CREATE UNLOGGED TABLE temp_neolaneid_sample AS
	SELECT p.primary_key FROM p10_client_propre p TABLESAMPLE SYSTEM(1)

DROP TABLE IF EXISTS temp_sales_sample;
CREATE UNLOGGED TABLE temp_sales_sample AS
	SELECT * FROM p10_vente_propre p
	RIGHT JOIN temp_neolaneid_sample t ON t.primary_key = p.idclient;

CREATE INDEX ON temp_sales_sample (idclient);
CREATE INDEX ON temp_sales_sample (datetransaction);
ANALYSE temp_sales_sample;

-- pq y a t il des null ???? On les supprime
DELETE FROM temp_sales_sample WHERE idclient is null;


SELECT t.idclient
	, sum(t.ca)
FROM temp_sales_sample t
GROUP BY 1
ORDER BY 2 DESC;

-- Check example
SELECT * FROM temp_sales_sample WHERE idclient = '51165675';

SELECT date_trunc('month', datetransaction) as month_transac, SUM(ca) FROM temp_sales_sample WHERE idclient = '51165675'
GROUP BY  month_transac
ORDER BY month_transac ;

-- TODO keep local curency name or drop local amount (ca_local)
CREATE UNLOGGED TABLE IF NOT EXISTS temp_sales_sample_month_aggr AS
	SELECT idclient
		, date_trunc('month', datetransaction) as month_transac
		, SUM(ca) as ca
		, SUM(ca_local) as ca_local
		, SUM(qte) as qte
	FROM temp_sales_sample
	GROUP BY 1, 2
	ORDER BY 2;

CREATE INDEX ON temp_sales_sample_month_aggr (idclient);
CREATE INDEX ON temp_sales_sample_month_aggr (month_transac);
ANALYZE temp_sales_sample_month_aggr;


*/

SELECT t1.idclient
	, t1.month_transac
	, t1.ca
	, SUM(t2.ca) as ca_12dm
FROM temp_sales_sample_month_aggr t1
LEFT JOIN temp_sales_sample_month_aggr t2 USING (idclient)
WHERE t1.idclient = '51165675' AND
t2.month_transac >= (t1.month_transac - interval '12 months') AND t2.month_transac < t1.month_transac
GROUP by t1.idclient
	, t1.month_transac
	, t1.ca
ORDER BY 2
LIMIT 200;SELECT *, pg_size_pretty(total_bytes) AS total
    , pg_size_pretty(index_bytes) AS INDEX
    , pg_size_pretty(toast_bytes) AS toast
    , pg_size_pretty(table_bytes) AS TABLE
  FROM (
  SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
      SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
              , c.reltuples::bigint AS row_estimate
              , pg_total_relation_size(c.oid) AS total_bytes
              , pg_indexes_size(c.oid) AS index_bytes
              , pg_total_relation_size(reltoastrelid) AS toast_bytes
          FROM pg_class c
          LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE relkind = 'r'
  ) a
) a
ORDER BY total_bytes DESC
;