-- Generated by Oracle SQL Developer Data Modeler 4.0.2.840
--   at:        2015-01-25 15:20:45 CET
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g




CREATE TABLE bids
(
  uuid      VARCHAR(36)              NOT NULL,
  item_uuid VARCHAR(36)              NOT NULL,
  user_uuid VARCHAR(36)              NOT NULL,
  bid_date  TIMESTAMP WITH TIME ZONE NOT NULL,
  offer     NUMERIC(8, 2)            NOT NULL
);
ALTER TABLE bids ADD CONSTRAINT bids_PK PRIMARY KEY (uuid);

CREATE TABLE charges
(
  uuid       VARCHAR(36)              NOT NULL,
  start_date TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date   TIMESTAMP WITH TIME ZONE NOT NULL,
  fee        NUMERIC(8, 2)            NOT NULL,
  rate       NUMERIC(8, 2)            NOT NULL
);
ALTER TABLE charges ADD CONSTRAINT charges_PK PRIMARY KEY (uuid);

CREATE TABLE items
(
  uuid          VARCHAR(36)              NOT NULL,
  user_uuid     VARCHAR(36)              NOT NULL,
  start_date    TIMESTAMP WITH TIME ZONE NOT NULL,
  end_date      TIMESTAMP WITH TIME ZONE NOT NULL,
  item_name     VARCHAR(20)              NOT NULL,
  short_desc    VARCHAR(30)              NOT NULL,
  long_desc     TEXT                     NOT NULL,
  initial_price NUMERIC(8, 2)            NOT NULL
);
ALTER TABLE items ADD CONSTRAINT items_PK PRIMARY KEY (uuid);

CREATE TABLE transactions
(
  uuid             VARCHAR(36)              NOT NULL,
  user_uuid        VARCHAR(36)              NOT NULL,
  transaction_date TIMESTAMP WITH TIME ZONE NOT NULL,
  amount           NUMERIC(8, 2)            NOT NULL,
  label            VARCHAR(255)             NOT NULL
);
ALTER TABLE transactions ADD CONSTRAINT transactions_PK PRIMARY KEY (uuid);

CREATE TABLE users
(
  uuid          VARCHAR(36)              NOT NULL,
  username      VARCHAR(20)              NOT NULL,
  email         VARCHAR(255)             NOT NULL,
  user_password VARCHAR(255)             NOT NULL,
  creation_date TIMESTAMP WITH TIME ZONE NOT NULL,
  first_name    VARCHAR(255)             NOT NULL,
  last_name     VARCHAR(255)             NOT NULL,
  country_code  VARCHAR(2)               NOT NULL,
  postal_code   VARCHAR(20)              NOT NULL,
  address       VARCHAR(255)             NOT NULL,
  phone         VARCHAR(20)              NOT NULL,
  birthdate     TIMESTAMP WITH TIME ZONE NOT NULL
);
ALTER TABLE users ADD CONSTRAINT users_PK PRIMARY KEY (uuid);

ALTER TABLE bids ADD CONSTRAINT bids_items_FK FOREIGN KEY (item_uuid) REFERENCES items (uuid);

ALTER TABLE bids ADD CONSTRAINT bids_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);

ALTER TABLE items ADD CONSTRAINT items_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);

ALTER TABLE transactions ADD CONSTRAINT transactions_users_FK FOREIGN KEY (user_uuid) REFERENCES users (uuid);


-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             5
-- CREATE INDEX                             0
-- ALTER TABLE                              9
-- CREATE VIEW                              0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0

-- constraints

CREATE OR REPLACE FUNCTION current_datetime()
  RETURNS TIMESTAMP WITH TIME ZONE
AS '
BEGIN

  --RETURN to_timestamp(''2014-01-01'', ''YYYY-MM-DD'');
  RETURN now();

END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION gen_uuid()
  RETURNS VARCHAR
AS '
BEGIN

  RETURN upper(cast(uuid_generate_v4() AS VARCHAR));

END;
' LANGUAGE plpgsql;

-- constraints

ALTER TABLE USERS
ADD CONSTRAINT username_unique UNIQUE (USERNAME);


-- defaults

CREATE EXTENSION "uuid-ossp";

ALTER TABLE USERS
ALTER COLUMN UUID SET DEFAULT gen_uuid();

ALTER TABLE BIDS
ALTER COLUMN UUID SET DEFAULT gen_uuid();

ALTER TABLE CHARGES
ALTER COLUMN UUID SET DEFAULT gen_uuid();

ALTER TABLE ITEMS
ALTER COLUMN UUID SET DEFAULT gen_uuid();

ALTER TABLE TRANSACTIONS
ALTER COLUMN UUID SET DEFAULT gen_uuid();

COMMIT;
CREATE OR REPLACE VIEW sales AS

  SELECT
    items.uuid                                                             AS item_uuid,
    items.user_uuid                                                        AS seller_uuid,
    items.start_date                                                       AS start_date,
    items.end_date                                                         AS end_date,
    best_bids.uuid                                                         AS best_bid_uuid,
    best_bids.user_uuid                                                    AS best_bidder_uuid,
    best_bids.offer                                                        AS best_offer,
    COALESCE(charges.fee, 0) + COALESCE(charges.rate, 0) * best_bids.offer AS charges

  FROM items

    LEFT OUTER JOIN (
                      SELECT DISTINCT ON (item_uuid) *
                      FROM bids
                      ORDER BY item_uuid, offer DESC
                    ) best_bids
      ON best_bids.item_uuid = items.uuid

    LEFT OUTER JOIN (
                      SELECT
                        start_date,
                        end_date,
                        SUM(fee)  AS fee,
                        SUM(rate) AS rate

                      FROM charges
                      GROUP BY charges.start_date, charges.end_date
                    ) charges
      ON items.end_date BETWEEN charges.start_date AND charges.end_date;
CREATE OR REPLACE VIEW accounts AS

  SELECT
    users.uuid                                                       AS user_uuid,
    users.username                                                   AS username,
    COALESCE(transactions.balance, 0)                                AS balance,
    COALESCE(sales.open_bids, 0)                                     AS open_bids,
    COALESCE(transactions.balance, 0) - COALESCE(sales.open_bids, 0) AS equity

  FROM users

    LEFT OUTER JOIN (
                      SELECT
                        user_uuid,
                        SUM(transactions.amount) AS balance

                      FROM transactions
                      WHERE transaction_date < current_datetime()
                      GROUP BY user_uuid
                    ) transactions
      ON transactions.user_uuid = users.uuid

    LEFT OUTER JOIN (
                      SELECT
                        best_bidder_uuid,
                        SUM(sales.best_offer + sales.charges) AS open_bids

                      FROM sales
                      WHERE end_date > current_datetime()
                      GROUP BY best_bidder_uuid
                    ) sales
      ON sales.best_bidder_uuid = users.uuid;

INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES
  ('0C99BEF9-375F-3E4A-E050-D7936EBE2782', 'duf', 'duf@gmail.com', '123456', '2000-01-01 00:00:00.000000', 'Francis',
   'Dumas', 'FR', '86544', '123 rue Bidon', '0123456789', '2001-01-01 00:00:00.000000');
INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES
  ('0C99BEF9-3760-3E4A-E050-D7936EBE2782', 'johnny', 'johnny.martin@wanadoo.fr', 'azerty', '2000-01-01 00:00:00.000000',
   'Johnny', 'Martin', 'FR', '75001', '78 Rue du Faubourg Saint-Honoré', '0000000000', '2015-01-06 00:00:00.000000');
INSERT INTO USERS (UUID, USERNAME, EMAIL, USER_PASSWORD, CREATION_DATE, FIRST_NAME, LAST_NAME, COUNTRY_CODE, POSTAL_CODE, ADDRESS, PHONE, BIRTHDATE)
VALUES
  ('0C99BEF9-3762-3E4A-E050-D7936EBE2782', 'Lefe', 'lefe@outlook.com', 'mbay', '2000-01-01 00:00:00.000000', 'Estelle',
   'Lefebvre', 'FR', '77600', '82, rue de la Place', '0102030405', '2015-01-16 00:00:00.000000');
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
  ('0C98CCCC-219C-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-01-01 08:39:34.000000',
   18000.00, 'VIR');
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
  ('0C98CCCC-219D-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-05-02 08:47:26.000000',
   9500.00, 'VIR');
INSERT INTO TRANSACTIONS (UUID, USER_UUID, TRANSACTION_DATE, AMOUNT, LABEL) VALUES
  ('0C98CCCC-219F-C9B1-E050-D7936EBE02BF', '0C99BEF9-3762-3E4A-E050-D7936EBE2782', '2013-03-01 08:50:15.000000',
   30000.00, 'VIR');
INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
  ('0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '0C99BEF9-375F-3E4A-E050-D7936EBE2782', '2013-03-15 00:00:00.000000',
   '2013-07-01 23:59:59.999999', 'Audi A3', 'Voiture', 'Bonne voiture bon état pas cher', 12900.00);
INSERT INTO ITEMS (UUID, USER_UUID, START_DATE, END_DATE, ITEM_NAME, SHORT_DESC, LONG_DESC, INITIAL_PRICE) VALUES
  ('0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '0C99BEF9-3760-3E4A-E050-D7936EBE2782', '2013-04-25 00:00:00.000000',
   '2013-05-25 23:59:59.999999', 'Harley-Davidson', 'Moto', 'Alors une moto ça roule sur deux roues.', 25000.00);
INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE)
VALUES ('0CA2E8F1-D1EF-49B7-E050-D7936EBE6A05', '2013-01-01 00:00:00.000000', '2013-12-31 23:59:59.999999', 4.00, 0.25);
INSERT INTO CHARGES (UUID, START_DATE, END_DATE, FEE, RATE)
VALUES ('0CA2E8F1-D1F0-49B7-E050-D7936EBE6A05', '2014-01-01 00:00:00.000000', '2014-12-31 23:59:59.999999', 4.20, 0.27);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
  ('0CA2E8F1-D1F1-49B7-E050-D7936EBE6A05', '0C99BEF9-375F-3E4A-E050-D7936EBE2782',
   '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '2013-05-05 23:48:16.000000', 26000.00);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
  ('0CA2E8F1-D1F2-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
   '0C98CCCC-219B-C9B1-E050-D7936EBE02BF', '2012-05-03 23:50:05.000000', 25000.00);
INSERT INTO BIDS (UUID, USER_UUID, ITEM_UUID, BID_DATE, OFFER) VALUES
  ('0CA2E8F1-D1F3-49B7-E050-D7936EBE6A05', '0C99BEF9-3762-3E4A-E050-D7936EBE2782',
   '0C98CCCC-21A5-C9B1-E050-D7936EBE02BF', '2012-06-01 23:50:53.000000', 12900.00);
