DROP TABLE joseph_test.X;
CREATE TABLE joseph_test.X (
  a integer
);

INSERT INTO joseph_test.X (a) VALUES (1), (2), (3), (4), (5), (6), (7);

DROP TABLE joseph_test.Y;
CREATE TABLE joseph_test.Y (col1 INT, col2 INT);
INSERT INTO joseph_test.Y (col1,col2) VALUES (1,2), (2,0), (0,4), (4,0), (0,6), (6,7), (7,8);

DROP TABLE joseph_test.Z;
CREATE TABLE joseph_test.Z(col1 BYTEA);
DROP TABLE deposits;
/*CREATE TABLE deposits(
       deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
      ,shard INT8 NOT NULL
      ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
      ,known_coin_id INT8 NOT NULL
      ,amount_with_fee_val INT8 NOT NULL
      ,amount_with_fee_frac INT4 NOT NULL
      ,wallet_timestamp INT8 NOT NULL
      ,exchange_timestamp INT8 NOT NULL
      ,refund_deadline INT8 NOT NULL
      ,wire_deadline INT8 NOT NULL
      ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
      ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
      ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
      ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
      ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
      ,done BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_details_serial_id INT8);
*/
--INSERT INTO deposits VALUES ();



CREATE TABLE deposits(
       deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY
      ,shard INT8 NOT NULL
      ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
      ,known_coin_id INT8 NOT NULL
      ,amount_with_fee_val INT8 NOT NULL
      ,amount_with_fee_frac INT4 NOT NULL
      ,wallet_timestamp INT8 NOT NULL
      ,exchange_timestamp INT8 NOT NULL
      ,refund_deadline INT8 NOT NULL
      ,wire_deadline INT8 NOT NULL
      ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
      ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
      ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
      ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
      ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32)
      ,done BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_blocked BOOLEAN NOT NULL DEFAULT FALSE
      ,policy_details_serial_id INT8);


CREATE OR REPLACE FUNCTION pg_spi_insert_int()
  RETURNS VOID
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_insert_int';
DROP FUNCTION pg_spi_select_from_x();
CREATE OR REPLACE FUNCTION pg_spi_select_from_x()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_from_x';

/*DROP FUNCTION pg_spi_select_pair_from_y();
CREATE OR REPLACE FUNCTION pg_spi_select_pair_from_y()
  RETURNS valuest
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_pair_from_y';
*/
/*CREATE OR REPLACE FUNCTION pg_spi_select_with_cond()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_select_with_cond';
*/
DROP FUNCTION pg_spi_update_y();
CREATE OR REPLACE FUNCTION pg_spi_update_y()
  RETURNS VOID
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_update_y';
DROP FUNCTION pg_spi_prepare_example();

CREATE OR REPLACE FUNCTION pg_spi_prepare_example()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_example';

DROP FUNCTION pg_spi_prepare_example_without_saveplan();
CREATE OR REPLACE FUNCTION pg_spi_prepare_example_without_saveplan()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_example_without_saveplan';

CREATE OR REPLACE FUNCTION pg_spi_prepare_insert()
  RETURNS VOID
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_insert';

CREATE OR REPLACE FUNCTION pg_spi_prepare_insert_without_saveplan()
  RETURNS VOID
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_insert_without_saveplan';

/*DROP FUNCTION pg_spi_prepare_select_with_cond();
CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond';
*/
DROP FUNCTION pg_spi_prepare_select_with_cond_without_saveplan();
CREATE OR REPLACE FUNCTION pg_spi_prepare_select_with_cond_without_saveplan()
  RETURNS INT8
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_select_with_cond_without_saveplan';

DROP FUNCTION pg_spi_prepare_update();
CREATE OR REPLACE FUNCTION pg_spi_prepare_update()
  RETURNS VOID
  LANGUAGE c COST 100
AS '$libdir/own_test', 'pg_spi_prepare_update';

DROP FUNCTION pg_spi_get_dep_ref_fees(
  IN in_timestamp INT8
 ,IN merchant_pub BYTEA
 ,IN wire_target_h_payto BYTEA
 ,IN wtid BYTEA);
CREATE OR REPLACE FUNCTION pg_spi_get_dep_ref_fees(
  IN in_timestamp INT8
 ,IN merchant_pub BYTEA
 ,IN wire_target_h_payto BYTEA
 ,IN wtid BYTEA
)
  RETURNS VOID
  LANGUAGE c VOLATILE COST 100
AS '$libdir/own_test', 'pg_spi_get_dep_ref_fees';

CREATE OR REPLACE FUNCTION update_pg_spi_get_dep_ref_fees(
 IN in_refund_deadline INT8,
 IN in_merchant_pub BYTEA,
 IN in_wire_target_h_payto BYTEA
)
RETURNS SETOF record
LANGUAGE plpgsql VOLATILE
AS $$
DECLARE

BEGIN
RETURN QUERY
  UPDATE deposits
  SET done = TRUE
  WHERE NOT (done OR policy_blocked)
  AND refund_deadline < in_refund_deadline
  AND merchant_pub = in_merchant_pub
  AND wire_target_h_payto = in_wire_target_h_payto
  RETURNING
  deposit_serial_id,
  coin_pub,
  amount_with_fee_val,
  amount_with_fee_frac;
END $$;

CREATE OR REPLACE FUNCTION stored_procedure_update(
IN in_number INT8
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE joseph_test.Y
  SET col1 = 4
  WHERE col2 = in_number;
END $$;

CREATE OR REPLACE FUNCTION stored_procedure_select(OUT out_value INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT 1
    INTO out_value
  FROM joseph_test.X;
  RETURN;
END $$;


CREATE OR REPLACE FUNCTION stored_procedure_insert(
IN in_number INT8,
OUT out_number INT8)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO joseph_test.X (a)
  VALUES (in_number)
  RETURNING a INTO out_number;
END $$;

CREATE OR REPLACE FUNCTION stored_procedure_select_with_cond(
IN in_number INT8,
OUT out_number INT8
)
RETURNS INT8
LANGUAGE plpgsql
AS $$
BEGIN
 SELECT col1 INTO out_number
 FROM joseph_test.Y
 WHERE col2 = in_number;
 RETURN;
END $$;