digifi-Core/SQL/salaryloan.sql

CREATE DATABASE salaryloan;
CREATE user salaryloan with encrypted password 'salaryloan';
GRANT all privileges on database salaryloan to salaryloan;

CREATE TABLE demo_bank_accounts (
                id SERIAL,
                uid uuid DEFAULT  uuid_generate_v4(),
                name VARCHAR(125),
                offers INT DEFAULT 0,
                salary_account INT DEFAULT 0,
                current_loans INT DEFAULT 0,
                mobile VARCHAR(25) UNIQUE NOT NULL,
                bvn VARCHAR(12) UNIQUE NOT NULL,
                email VARCHAR(125),
                pin VARCHAR(6),
                added timestamp without time zone DEFAULT now()
);
ALTER TABLE ONLY demo_bank_accounts
    ADD CONSTRAINT demo_bank_accounts_id_key UNIQUE (id);

ALTER TABLE demo_bank_accounts ADD balance INT DEFAULT 0;
ALTER TABLE demo_bank_accounts ADD updated TIMESTAMP;



CREATE TABLE loan_offers (
            id SERIAL,
            uid uuid DEFAULT  uuid_generate_v4(),
            loan VARCHAR(25) UNIQUE NOT NULL,
            amount  INT DEFAULT 0,
            description VARCHAR(125),
            days_duration INT DEFAULT 0,
            active INT DEFAULT 1,
            lorder INT DEFAULT 0,
            score INT DEFAULT 0,
            added timestamp without time zone DEFAULT now()
);
ALTER TABLE ONLY loan_offers
    ADD CONSTRAINT loan_offers_id_key UNIQUE (id);

INSERT INTO loan_offers (
   loan, amount, description, days_duration
) VALUES (
    'LOAN001',
    100000,
    '100,000 Naira for 30 Days',
    30
);

INSERT INTO loan_offers (
   loan, amount, description, days_duration
) VALUES (
    'LOAN002',
    300000,
    '300,000 Naira for 60 Days',
    60
);


INSERT INTO loan_offers (
   loan, amount, description, days_duration
) VALUES (
    'LOAN003',
    900000,
    '900,000 Naira for 90 Days',
    90
);



CREATE TABLE loan_select (
         id SERIAL,
         uid uuid DEFAULT  uuid_generate_v4(),
         loan VARCHAR(25)  NOT NULL,
         bvn VARCHAR(12)  NOT NULL,
         added timestamp without time zone DEFAULT now()
);
ALTER TABLE ONLY loan_select
    ADD CONSTRAINT loan_select_id_key UNIQUE (id);

CREATE TABLE loan_apply (
         id SERIAL,
         uid uuid DEFAULT  uuid_generate_v4(),
         loan VARCHAR(25)  NOT NULL,
         bvn VARCHAR(12)  NOT NULL,
         amount  INT DEFAULT 0,
         added timestamp without time zone DEFAULT now(),
         status  INT DEFAULT 1,
         verified timestamp,
         due_date timestamp
);
ALTER TABLE ONLY loan_apply
    ADD CONSTRAINT loan_apply_id_key UNIQUE (id);



CREATE TABLE loans (
        id SERIAL,
        uid uuid DEFAULT  uuid_generate_v4(),
        application_uid VARCHAR(150) NOT NULL,
        bvn VARCHAR(12)  NOT NULL,
        loan VARCHAR(25) REFERENCES  loan_offers (loan),
        approved_amount  INT DEFAULT 0,
        days_duration  INT DEFAULT 0,
        status  INT DEFAULT 1,
        due_date timestamp,
        payment  INT DEFAULT 0,
        added timestamp without time zone DEFAULT now()
    );
ALTER TABLE ONLY loans
    ADD CONSTRAINT loans_id_key UNIQUE (id);


--- ALTER TABLE loan_apply ADD  verified timestamp;
--- ALTER TABLE loan_apply ADD  status  INT DEFAULT 1;
--  ALTER TABLE loan_apply ADD  due_date timestamp;


{"cid": "425611f2-c692-4404-b93d-76ca7a5ce00", "description": "100,000 Naira for 30 Days" , "active" : 1 },
{"cid": "425611f2-c692-4404-b93d-76ca7a5ce01", "description": "300,000 Naira for 60 Days" , "active" : 1 },
{"cid": "425611f2-c692-4404-b93d-76ca7a5ce02", "description": "900,000 Naira for 90 Days" , "active" : 1 },
]


{"cid": "1", "description": "Product Loan 01" , "active" : 0 },
{"cid": "2", "description": "Product Loan 02" , "active" : 0  },
{"cid": "3", "description": "First Advance" , "active" : 1 },


CREATE TABLE trx_raw (
       id SERIAL,
       AccountID VARCHAR(15) NOT NULL,
       trx_start_date timestamp,
       trx_end_date timestamp,
       amount  float DEFAULT 0.0,
       D1 VARCHAR(3),
       D2 VARCHAR(3),
       D3 VARCHAR(3),
       Description VARCHAR(75),
       D4 VARCHAR(12)
);
ALTER TABLE ONLY trx_raw
    ADD CONSTRAINT trx_raw_id_key UNIQUE (id);

CREATE TABLE trx_raw (
         AccountID VARCHAR(15) NOT NULL,
         trx_start_date VARCHAR(15),
         trx_end_date VARCHAR(15),
         amount  float DEFAULT 0.0,
         D1 VARCHAR(3),
         D2 VARCHAR(3),
         D3 VARCHAR(3),
         Description VARCHAR(75),
         D4 VARCHAR(12)
);

CREATE TABLE accounts (
     id SERIAL,
     AccountID VARCHAR(15) UNIQUE NOT NULL
);

ALTER TABLE ONLY accounts
    ADD CONSTRAINT accounts_id_key UNIQUE (id);



1738039011      |15-03-2018|15-03-2018|      3000|T|CI|D|ATM WD @ 000000000001447 ACCESS NNEWI          NG |1738039
1738039011      |16-03-2018|16-03-2018|      2000|T|CI|D|ATM WD @ 000000000011446 Nnewi  Nnewi          NG |1738039
1738039011      |19-03-2018|19-03-2018|      1065|T|CI|D|ATM WD @ NNEWI 05 NNEWI         NNEWI        ANNG |1738039
1738039011      |20-03-2018|20-03-2018|      1065|T|CI|D|ATM WD @ 000000000001447 ACCESS NNEWI          NG |1738039
1778212012      |22-03-2018|22-03-2018|      8000|T|CI|D|240447189 ATM WD @ 10503184/ENG LA SATELITE       |1778212
1778212012      |19-03-2018|19-03-2018|    260000|T|CI|D|Online : kola                                     |1778212
1778212012      |19-03-2018|19-03-2018|      52.5|T|BI|D|TRANSACTION CHARGE-Online : kola                  |1778212
1778212012      |16-03-2018|16-03-2018|    270000|T|CI|C|TRF B/O CHIBUZO BEATRICE UGOCHUKWU