DROP TABLE CAccount;
DROP TABLE Customer;
DROP TABLE AType;

CREATE TABLE Customer
(
	nr INT NOT NULL,
	name VARCHAR(40) NOT NULL
);

CREATE TABLE AType
(
	nr INT PRIMARY KEY,
	name VARCHAR(20) NOT NULL
);

CREATE TABLE CAccount
(
	nr INT NOT NULL,
	customer_nr INT NOT NULL,
	type_nr INT NOT NULL,
	balance DECIMAL NOT NULL,
	"limit" DECIMAL NOT NULL
);


INSERT INTO Atype (nr,name) VALUES (1,'Standard');
INSERT INTO Atype (nr,name) VALUES (2,'Plus');
INSERT INTO Atype (nr,name) VALUES (3,'Extra-Plus');
INSERT INTO Atype (nr,name) VALUES (4,'Exclusive');

-- 
-- Create procedures
--
DROP FUNCTION giveRandomFloat(int);

CREATE FUNCTION giveRandomFloat(int) RETURNS float AS '
  SELECT random() * $1;
' LANGUAGE SQL STRICT;

DROP FUNCTION giveRandomNumber(int);

CREATE FUNCTION giveRandomNumber(int) RETURNS INTEGER AS '
  SELECT CAST(random()*($1-1) AS int) + 1;
' LANGUAGE SQL STRICT;

DROP FUNCTION giveRandomLine(int);

CREATE FUNCTION giveRandomLine(nrOfChars int, OUT line VARCHAR(100))
AS '
DECLARE
	counter smallint;
	char VARCHAR(1);
	r float;
BEGIN
	line := '''';
	counter := 0;
	WHILE counter < nrOfChars LOOP
		r := CAST(random()*94 AS int) + 32;
		char := chr(CAST(r AS integer));
		line := line || char;
		counter := counter + 1;
	END LOOP;
END;
' LANGUAGE plpgsql;

DROP FUNCTION insertCustomers(int);

CREATE FUNCTION insertCustomers(amount int) RETURNS void AS '
DECLARE
	counter int;
	cusname varchar(40);
BEGIN
	counter := 0;
	WHILE counter < amount LOOP
		cusname := giveRandomLine(40);
		
		INSERT INTO Customer (nr, name) VALUES (counter,cusname);
		
		counter := counter + 1;
	END LOOP;
END;
' LANGUAGE plpgsql STRICT;

DROP FUNCTION insertAccounts(int, int);

CREATE FUNCTION insertAccounts(amount int, maxCustNr int) RETURNS void
AS '
DECLARE
	abalance float;
	counter int;
	"type" int;
	alimit float;
	custNr int;
BEGIN
	counter := 0;
	WHILE counter < amount LOOP
		custNr := giveRandomNumber(maxCustNr);
		"type" := giveRandomNumber(5);
		abalance := giveRandomFloat(50000);
		alimit := giveRandomFloat(60000);
		--SELECT alimit;
		--PRINT type;
		
		INSERT INTO CAccount (nr,customer_nr,type_nr,balance,"limit") VALUES (counter,custNr,"type",abalance,alimit);
		
		counter := counter + 1;
	END LOOP;
END;
' LANGUAGE plpgsql STRICT;

DROP FUNCTION insertTestData(int, int);

CREATE FUNCTION insertTestData(nrOfCustomers int, nrOfAccounts int)
RETURNS void
AS '
  delete from CAccount;
  delete from customer;
  SELECT insertCustomers($1);
  SELECT insertAccounts($2, $1-1);
' LANGUAGE SQL STRICT;

SELECT insertTestData(10000,10000);

