--
-- Billig::Economy::Settlement SQL (2.0)
--
-- Changes from 1.0 (UKA-09):
--   - Settlement does not contain information about the physical cash register and terminal.
--   - No 'change' transaction type - handled as one refund and one sale pr ticket affected.
--   - No 'reservation' payment method. Reservation payments are not handled in billig unless
--     they are paid through NetAxcept, which makes it a normal netsale (not handled by this module).
--   - Transactions are now made per ticket and not per purchase, which removes the need for the old
--     ticket_transaction-table and decreases the reporting complexity quite a bit.
--   - Transactions do not have the transaction_sum-field anymore as its redundant and a violation of 3NF,
--     join with the price_group-table instead to find the sum.
--   - Added the settlement_report-view for moving a lot of client-side code (TODO: Analyze performance)
--
-- TODO:
--   - Include muliple seller support from eide

CREATE TABLE settlement (
	settlement SERIAL PRIMARY KEY NOT NULL,

	owner VARCHAR NOT NULL,
	organisation INTEGER REFERENCES (organisation) NOT NULL,

	starttime TIMESTAMP NOT NULL DEFAULT NOW(),
	endtime TIMESTAMP,

	cash_in INTEGER, -- Cash contained in the register at endtime
	credit_in INTEGER, -- Credit in as reported by the terminal

	cash_king_id INTEGER, -- Export ID from CashKing (used under UKA) (Renamed from uka_transaction_id in old version)
);

CREATE TABLE transaction_type (
	transaction_type VARCHAR PRIMARY KEY NOT NULL
);

INSERT INTO transaction_type VALUES ('sale');
INSERT INTO transaction_type VALUES ('refund');

CREATE TABLE payment_method (
	payment_method VARCHAR PRIMARY KEY NOT NULL
);

INSERT INTO payment_method VALUES ('cash');
INSERT INTO payment_method VALUES ('credit');

CREATE TABLE transaction (
	transaction SERIAL PRIMARY KEY NOT NULL,
	settlement INTEGER REFERENCES (settlement) NOT NULL,
	ticket INTEGER REFERENCES (ticket) NOT NULL,
        seller VARCHAR NOT NULL,
	transaction_type VARCHAR REFERENCES (transaction_type)
	transaction_time TIMESTAMP NOT NULL DEFAULT NOW(),
	payment_method VARCHAR REFERENCES (payment_method),
	-- Don't allow multiple transactions of the same type on one ticket
	UNIQUE (ticket, transaction_type),
	-- We only support refunds in cash
	CHECK (transaction_type = 'sale' OR (transaction_type = 'refund' AND payment_type = 'cash'))
);

CREATE VIEW settlement_report (
	SELECT settlement, seller, starttime, endtime, cash_in, credit_in,
	       (cash_sale.cash_sale - cash_sale.cash_refund) AS cash,
	       credit_sale.credit_sale AS credit
	FROM settlement
	NATURAL JOIN transaction
	NATURAL JOIN (
		SELECT settlement, SUM(price) AS cash_sale
		  FROM transaction
		    NATURAL JOIN ticket
		    NATURAL JOIN price_group
		WHERE payment_type = 'cash' AND transaction_type = 'sale'
		GROUP BY settlement
		) AS cash_sale,
	NATURAL JOIN (
		SELECT settlement, SUM(price) AS cash_refund
		  FROM transaction
		    NATURAL JOIN ticket
		    NATURAL JOIN price_group
		WHERE payment_type = 'cash' AND transaction_type = 'refund'
		GROUP BY settlement
		) AS cash_refund,
	NATURAL JOIN (
		SELECT settlement, SUM(price) AS credit_sale
		  FROM transaction
		    NATURAL JOIN ticket
		    NATURAL JOIN price_group
		WHERE payment_type = 'credit' AND transaction_type = 'sale'
		GROUP BY settlement
		) AS credit_sale
);

--
-- Returns the sold tickets that were not refunded within the same settlement. Returns the settlement value and the ticket.
-- Ex: SELECT SUM(price) FROM ticket NATURAL JOIN price_group WHERE ticket in (SELECT ticket FROM settlement_tickets WHERE settlement = 100);
--
CREATE VIEW settlement_tickets (
	SELECT settlement, ticket FROM (
	SELECT settlement, ticket, refunds.ticket
	  FROM transaction
	  NATURAL LEFT JOIN (
	  	SELECT settlement, ticket
		  FROM transaction
		  WHERE transaction_type = 'refund') AS refunds
	 WHERE transaction_type = 'sale'
	   AND refunds.ticket IS NULL) AS collector
	 ORDER BY settlement, ticket
);