-----------------------------------------------------------------------
-- users
-----------------------------------------------------------------------

DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users
(
    id serial NOT NULL,
    username VARCHAR(20) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(60),
    profile_picture_id INTEGER,
    facebook_id INT8,
    remember_token VARCHAR(100),
    role INT2,
    sub_role INT2,
    last_login_at TIMESTAMP NOT NULL,
    active BOOLEAN DEFAULT 't',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT users_u_f86ef3 UNIQUE (username),
    CONSTRAINT users_u_ce4c89 UNIQUE (email)
);

-----------------------------------------------------------------------
-- loan_categories
-----------------------------------------------------------------------

DROP TABLE IF EXISTS loan_categories CASCADE;

CREATE TABLE loan_categories
(
    id serial NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    what_description TEXT,
    why_description TEXT,
    how_description TEXT,
    admin_only BOOLEAN DEFAULT 'f',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    sortable_rank INTEGER,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- countries
-----------------------------------------------------------------------

DROP TABLE IF EXISTS countries CASCADE;

CREATE TABLE countries
(
    id serial NOT NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    capital VARCHAR(100),
    continent_code VARCHAR(2) NOT NULL,
    country_code VARCHAR(2) NOT NULL,
    dialing_code VARCHAR(4) NOT NULL,
    phone_number_length INTEGER NOT NULL,
    currency_code VARCHAR(3) NOT NULL,
    borrower_country BOOLEAN,
    registration_fee DECIMAL(10,2) DEFAULT 0.00,
    installment_period INT2,
    repayment_instructions TEXT,
    language_code VARCHAR(10),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE INDEX name ON countries (name);

CREATE INDEX country_code ON countries (country_code);

-----------------------------------------------------------------------
-- loans
-----------------------------------------------------------------------

DROP TABLE IF EXISTS loans CASCADE;

CREATE TABLE loans
(
    id serial NOT NULL,
    borrower_id INTEGER,
    summary TEXT,
    summary_translation TEXT,
    proposal TEXT,
    proposal_translation TEXT,
    native_amount DECIMAL(10,2),
    installment_day INTEGER,
    interest_rate DECIMAL(5,2),
    final_interest_rate DECIMAL(5,2),
    category_id INTEGER NOT NULL,
    secondary_category_id INTEGER,
    status INT2,
    application_date TIMESTAMP NOT NULL,
    accepted_date TIMESTAMP,
    expired_date TIMESTAMP,
    registration_fee_rate DECIMAL(5,2),
    amount DECIMAL(10,2) NOT NULL,
    amount_raised INTEGER,
    native_disbursed_amount DECIMAL(10,2),
    disbursed_date TIMESTAMP,
    extra_days INTEGER,
    service_fee_rate DECIMAL(5,2),
    currency_code VARCHAR(3) NOT NULL,
    installment_period INT2,
    installment_count INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- loan_bids
-----------------------------------------------------------------------

DROP TABLE IF EXISTS loan_bids CASCADE;

CREATE TABLE loan_bids
(
    id serial NOT NULL,
    loan_id INTEGER NOT NULL,
    lender_id INTEGER NOT NULL,
    borrower_id INTEGER NOT NULL,
    bid_amount DECIMAL(10,2) NOT NULL,
    interest_rate DECIMAL(5,2) NOT NULL,
    active INT2 DEFAULT 0 NOT NULL,
    accepted_amount DECIMAL(10,2) DEFAULT 0.00 NOT NULL,
    bid_date TIMESTAMP NOT NULL,
    lender_invite_credit INT2 DEFAULT 0 NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- password_reminders
-----------------------------------------------------------------------

DROP TABLE IF EXISTS password_reminders CASCADE;

CREATE TABLE password_reminders
(
    email VARCHAR(100) NOT NULL,
    token VARCHAR(100) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE INDEX email ON password_reminders (email);

CREATE INDEX token ON password_reminders (token);

-----------------------------------------------------------------------
-- lenders
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lenders CASCADE;

CREATE TABLE lenders
(
    id INTEGER NOT NULL,
    country_id INTEGER NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    admin_donate BOOLEAN DEFAULT 'f',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- borrowers
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrowers CASCADE;

CREATE TABLE borrowers
(
    id INTEGER NOT NULL,
    country_id INTEGER NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    active_loan_id INTEGER,
    loan_status INT2 DEFAULT 4,
    active BOOLEAN DEFAULT 't',
    volunteer_mentor_id INTEGER,
    referrer_id INTEGER,
    verified BOOLEAN DEFAULT 'f',
    activation_status INT2 DEFAULT 0,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- borrower_profiles
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_profiles CASCADE;

CREATE TABLE borrower_profiles
(
    borrower_id INTEGER NOT NULL,
    about_me TEXT,
    about_me_translation TEXT,
    about_business  TEXT,
    about_business_translation TEXT,
    address TEXT NOT NULL,
    address_instructions TEXT NOT NULL,
    city VARCHAR(100) NOT NULL,
    national_id_number VARCHAR(100) NOT NULL,
    phone_number VARCHAR(45) NOT NULL,
    alternate_phone_number VARCHAR(45) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (borrower_id)
);

-----------------------------------------------------------------------
-- lender_profiles
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lender_profiles CASCADE;

CREATE TABLE lender_profiles
(
    lender_id INTEGER NOT NULL,
    about_me TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (lender_id)
);

-----------------------------------------------------------------------
-- loan_stages
-----------------------------------------------------------------------

DROP TABLE IF EXISTS loan_stages CASCADE;

CREATE TABLE loan_stages
(
    id serial NOT NULL,
    loan_id INTEGER NOT NULL,
    borrower_id INTEGER NOT NULL,
    status INT2 NOT NULL,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- transactions
-----------------------------------------------------------------------

DROP TABLE IF EXISTS transactions CASCADE;

CREATE TABLE transactions
(
    id serial NOT NULL,
    user_id INTEGER NOT NULL,
    amount DECIMAL(12,4) NOT NULL,
    description VARCHAR(100) NOT NULL,
    loan_id INTEGER,
    transaction_date TIMESTAMP NOT NULL,
    exchange_rate DECIMAL(10,4),
    type INT2 NOT NULL,
    sub_type INT2,
    loan_bid_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE INDEX loan_id ON transactions (loan_id);

CREATE INDEX user_id ON transactions (user_id);

CREATE INDEX loan_bid_id ON transactions (loan_bid_id);

CREATE INDEX transaction_date ON transactions (transaction_date);

CREATE INDEX type ON transactions (type);

CREATE INDEX sub_type ON transactions (sub_type);

-----------------------------------------------------------------------
-- comments
-----------------------------------------------------------------------

DROP TABLE IF EXISTS comments CASCADE;

CREATE TABLE comments
(
    id serial NOT NULL,
    user_id INTEGER,
    borrower_id INTEGER NOT NULL,
    message TEXT,
    message_translation TEXT,
    translator_id INTEGER,
    parent_id INTEGER,
    root_id INTEGER,
    level INTEGER NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- uploads
-----------------------------------------------------------------------

DROP TABLE IF EXISTS uploads CASCADE;

CREATE TABLE uploads
(
    id serial NOT NULL,
    extension VARCHAR NOT NULL,
    user_id INTEGER NOT NULL,
    type VARCHAR NOT NULL,
    mime_type VARCHAR NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- comment_upload
-----------------------------------------------------------------------

DROP TABLE IF EXISTS comment_upload CASCADE;

CREATE TABLE comment_upload
(
    comment_id INTEGER NOT NULL,
    upload_id INTEGER NOT NULL,
    PRIMARY KEY (comment_id,upload_id)
);

-----------------------------------------------------------------------
-- borrower_upload
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_upload CASCADE;

CREATE TABLE borrower_upload
(
    borrower_id INTEGER NOT NULL,
    upload_id INTEGER NOT NULL,
    PRIMARY KEY (borrower_id,upload_id)
);

-----------------------------------------------------------------------
-- exchange_rates
-----------------------------------------------------------------------

DROP TABLE IF EXISTS exchange_rates CASCADE;

CREATE TABLE exchange_rates
(
    id serial NOT NULL,
    rate DECIMAL(10,5) NOT NULL,
    start_date TIMESTAMP,
    end_date TIMESTAMP,
    currency_code VARCHAR(3) NOT NULL,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- settings
-----------------------------------------------------------------------

DROP TABLE IF EXISTS settings CASCADE;

CREATE TABLE settings
(
    name VARCHAR(45) NOT NULL,
    value VARCHAR(45) DEFAULT '0' NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (name)
);

-----------------------------------------------------------------------
-- installments
-----------------------------------------------------------------------

DROP TABLE IF EXISTS installments CASCADE;

CREATE TABLE installments
(
    id serial NOT NULL,
    borrower_id INTEGER NOT NULL,
    loan_id INTEGER NOT NULL,
    due_date TIMESTAMP,
    native_amount DECIMAL(10,2),
    paid_date TIMESTAMP,
    native_paid_amount DECIMAL(10,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- installment_payments
-----------------------------------------------------------------------

DROP TABLE IF EXISTS installment_payments CASCADE;

CREATE TABLE installment_payments
(
    id serial NOT NULL,
    installment_id serial NOT NULL,
    borrower_id INTEGER NOT NULL,
    loan_id INTEGER NOT NULL,
    paid_date TIMESTAMP NOT NULL,
    paid_amount DECIMAL(15,4) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id,installment_id)
);

CREATE INDEX loan_borrower ON installment_payments (loan_id,borrower_id);

-----------------------------------------------------------------------
-- borrower_payments
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_payments CASCADE;

CREATE TABLE borrower_payments
(
    id serial NOT NULL,
    country_id INTEGER NOT NULL,
    receipt VARCHAR(32) NOT NULL,
    date TIMESTAMP NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    borrower_id INTEGER NOT NULL,
    status INT2 NOT NULL,
    phone VARCHAR(45) NOT NULL,
    details TEXT NOT NULL,
    error TEXT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- lender_invites
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lender_invites CASCADE;

CREATE TABLE lender_invites
(
    id serial NOT NULL,
    lender_id INTEGER NOT NULL,
    email VARCHAR(100) NOT NULL,
    invited BOOLEAN DEFAULT 't',
    hash VARCHAR(40),
    invitee_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- lender_invite_visits
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lender_invite_visits CASCADE;

CREATE TABLE lender_invite_visits
(
    id serial NOT NULL,
    lender_id INTEGER NOT NULL,
    lender_invite_id INTEGER,
    share_type INT2,
    http_referer TEXT,
    ip_address VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- lender_invite_transactions
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lender_invite_transactions CASCADE;

CREATE TABLE lender_invite_transactions
(
    id serial NOT NULL,
    lender_id INTEGER NOT NULL,
    amount DECIMAL(12,4) NOT NULL,
    description VARCHAR(100) NOT NULL,
    transaction_date TIMESTAMP NOT NULL,
    type INT2 NOT NULL,
    loan_id INTEGER,
    loan_bid_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE INDEX lender_id ON lender_invite_transactions (lender_id);

CREATE INDEX transaction_date_lender ON lender_invite_transactions (transaction_date);

CREATE INDEX type_lender ON lender_invite_transactions (type);

-----------------------------------------------------------------------
-- paypal_ipn_log
-----------------------------------------------------------------------

DROP TABLE IF EXISTS paypal_ipn_log CASCADE;

CREATE TABLE paypal_ipn_log
(
    id serial NOT NULL,
    log VARCHAR(255) NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- paypal_transactions
-----------------------------------------------------------------------

DROP TABLE IF EXISTS paypal_transactions CASCADE;

CREATE TABLE paypal_transactions
(
    id serial NOT NULL,
    transaction_id VARCHAR(255),
    transaction_type VARCHAR(255),
    amount DECIMAL(10,2) NOT NULL,
    donation_amount DECIMAL(10,2) NOT NULL,
    paypal_transaction_fee DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(100) NOT NULL,
    custom VARCHAR(255) NOT NULL,
    token VARCHAR(255),
    payment_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- stripe_transactions
-----------------------------------------------------------------------

DROP TABLE IF EXISTS stripe_transactions CASCADE;

CREATE TABLE stripe_transactions
(
    id serial NOT NULL,
    stripe_id VARCHAR,
    amount DECIMAL(10,2) NOT NULL,
    donation_amount DECIMAL(10,2) NOT NULL,
    transaction_fee DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(100) NOT NULL,
    payment_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- stripe_logs
-----------------------------------------------------------------------

DROP TABLE IF EXISTS stripe_logs CASCADE;

CREATE TABLE stripe_logs
(
    id serial NOT NULL,
    log VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- payments
-----------------------------------------------------------------------

DROP TABLE IF EXISTS payments CASCADE;

CREATE TABLE payments
(
    id serial NOT NULL,
    credit_amount DECIMAL(10,2) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    donation_amount DECIMAL(10,2) NOT NULL,
    donation_credit_amount DECIMAL(10,2) NOT NULL,
    transaction_fee DECIMAL(10,2) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR NOT NULL,
    lender_id INTEGER NOT NULL,
    loan_id INTEGER,
    interest_rate DECIMAL(5,2),
    gift_card_transaction_id INTEGER,
    class_key INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- gift_cards
-----------------------------------------------------------------------

DROP TABLE IF EXISTS gift_cards CASCADE;

CREATE TABLE gift_cards
(
    id serial NOT NULL,
    Lender_id INTEGER,
    template INTEGER,
    order_type VARCHAR(10) NOT NULL,
    card_amount DECIMAL(10,2) NOT NULL,
    recipient_email VARCHAR(50),
    confirmation_email VARCHAR(50),
    recipient_name VARCHAR(50),
    from_name VARCHAR(50),
    message TEXT,
    date TIMESTAMP,
    expire_date TIMESTAMP,
    card_code VARCHAR(20),
    status INT2 DEFAULT 0,
    claimed INT2 DEFAULT 0,
    recipient_id INTEGER,
    donated INT2 DEFAULT 0,
    gift_card_transaction_id INTEGER,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- gift_card_transaction
-----------------------------------------------------------------------

DROP TABLE IF EXISTS gift_card_transaction CASCADE;

CREATE TABLE gift_card_transaction
(
    id serial NOT NULL,
    transaction_id INTEGER,
    transaction_type VARCHAR(50),
    lender_id INTEGER,
    invoice_id INTEGER,
    status INT2 DEFAULT 0,
    total_cards INTEGER NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    donation DECIMAL(10,2),
    date TIMESTAMP NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- forgiven_loans
-----------------------------------------------------------------------

DROP TABLE IF EXISTS forgiven_loans CASCADE;

CREATE TABLE forgiven_loans
(
    id serial NOT NULL,
    loan_id INTEGER,
    lender_id INTEGER,
    borrower_id INTEGER,
    native_amount DECIMAL(10,4),
    amount DECIMAL(10,4),
    date TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

CREATE INDEX borrower ON forgiven_loans (borrower_id);

CREATE INDEX loan_date ON forgiven_loans (loan_id,date);

CREATE INDEX loan_lender ON forgiven_loans (loan_id,lender_id);

-----------------------------------------------------------------------
-- borrower_refunds
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_refunds CASCADE;

CREATE TABLE borrower_refunds
(
    id serial NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    borrower_id INTEGER NOT NULL,
    loan_id INTEGER NOT NULL,
    borrower_payment_id INTEGER NOT NULL,
    refunded BOOLEAN DEFAULT 'f',
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- volunteer_mentors
-----------------------------------------------------------------------

DROP TABLE IF EXISTS volunteer_mentors CASCADE;

CREATE TABLE volunteer_mentors
(
    borrower_id INTEGER NOT NULL,
    country_id INTEGER NOT NULL,
    grant_date TIMESTAMP NOT NULL,
    note TEXT,
    status INT2 DEFAULT 0,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    mentee_count INTEGER,
    PRIMARY KEY (borrower_id)
);

-----------------------------------------------------------------------
-- borrower_join_logs
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_join_logs CASCADE;

CREATE TABLE borrower_join_logs
(
    borrower_id INTEGER NOT NULL,
    ip_address VARCHAR(256) NOT NULL,
    verification_code VARCHAR,
    verified_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (borrower_id),
    CONSTRAINT borrower_join_logs_u_ff2f18 UNIQUE (verification_code)
);

-----------------------------------------------------------------------
-- borrower_contacts
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_contacts CASCADE;

CREATE TABLE borrower_contacts
(
    id serial NOT NULL,
    borrower_id INTEGER NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(32) NOT NULL,
    description VARCHAR(255) NOT NULL,
    type INT2 NOT NULL,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- borrower_guests
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_guests CASCADE;

CREATE TABLE borrower_guests
(
    id serial NOT NULL,
    session TEXT,
    form TEXT,
    email VARCHAR(100) NOT NULL,
    resumeCode VARCHAR NOT NULL,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- borrower_feedback_messages
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_feedback_messages CASCADE;

CREATE TABLE borrower_feedback_messages
(
    id serial NOT NULL,
    borrower_id INTEGER NOT NULL,
    type INT2,
    borrower_email VARCHAR(100) NOT NULL,
    cc VARCHAR,
    reply_to VARCHAR NOT NULL,
    subject VARCHAR NOT NULL,
    message TEXT NOT NULL,
    sent_at TIMESTAMP NOT NULL,
    sender_name VARCHAR NOT NULL,
    loan_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- borrower_reviews
-----------------------------------------------------------------------

DROP TABLE IF EXISTS borrower_reviews CASCADE;

CREATE TABLE borrower_reviews
(
    borrower_id INTEGER NOT NULL,
    is_photo_clear TEXT,
    is_desc_clear TEXT,
    is_address_locatable BOOLEAN NOT NULL,
    is_address_locatable_note TEXT NOT NULL,
    is_number_provided TEXT,
    is_nat_id_uploaded TEXT,
    is_rec_form_uploaded TEXT,
    is_rec_form_offcr_name TEXT,
    is_pending_mediation TEXT,
    created_by INTEGER,
    modified_by INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (borrower_id)
);

-----------------------------------------------------------------------
-- languages
-----------------------------------------------------------------------

DROP TABLE IF EXISTS languages CASCADE;

CREATE TABLE languages
(
    language_code VARCHAR(10) NOT NULL,
    name VARCHAR NOT NULL,
    active BOOLEAN DEFAULT 'f',
    PRIMARY KEY (language_code)
);

-----------------------------------------------------------------------
-- category_translations
-----------------------------------------------------------------------

DROP TABLE IF EXISTS category_translations CASCADE;

CREATE TABLE category_translations
(
    id serial NOT NULL,
    category_id INTEGER NOT NULL,
    language_code VARCHAR(10),
    translation TEXT,
    PRIMARY KEY (id),
    CONSTRAINT category_translations_u_81ec1d UNIQUE (category_id,language_code)
);

-----------------------------------------------------------------------
-- lending_groups
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lending_groups CASCADE;

CREATE TABLE lending_groups
(
    id serial NOT NULL,
    name VARCHAR(50) NOT NULL,
    website VARCHAR,
    group_profile_picture_id INTEGER,
    about TEXT NOT NULL,
    creator_id INTEGER NOT NULL,
    leader_id INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT lending_groups_u_d94269 UNIQUE (name),
    CONSTRAINT lending_groups_u_2b6d22 UNIQUE (website)
);

-----------------------------------------------------------------------
-- lending_group_members
-----------------------------------------------------------------------

DROP TABLE IF EXISTS lending_group_members CASCADE;

CREATE TABLE lending_group_members
(
    id serial NOT NULL,
    group_id INTEGER NOT NULL,
    member_id INTEGER NOT NULL,
    leaved BOOLEAN DEFAULT 'f',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- translation_labels
-----------------------------------------------------------------------

DROP TABLE IF EXISTS translation_labels CASCADE;

CREATE TABLE translation_labels
(
    id serial NOT NULL,
    key VARCHAR NOT NULL,
    value TEXT,
    language_code CHAR(5) NOT NULL,
    filename VARCHAR NOT NULL,
    translated BOOLEAN DEFAULT 'f',
    updated BOOLEAN DEFAULT 'f',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- notifications
-----------------------------------------------------------------------

DROP TABLE IF EXISTS notifications CASCADE;

CREATE TABLE notifications
(
    id serial NOT NULL,
    type INT2 NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (id)
);

-----------------------------------------------------------------------
-- translation_labels_archive
-----------------------------------------------------------------------

DROP TABLE IF EXISTS translation_labels_archive CASCADE;

CREATE TABLE translation_labels_archive
(
    id INTEGER NOT NULL,
    key VARCHAR NOT NULL,
    value TEXT,
    language_code CHAR(5) NOT NULL,
    filename VARCHAR NOT NULL,
    translated BOOLEAN DEFAULT 'f',
    updated BOOLEAN DEFAULT 'f',
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    archived_at TIMESTAMP,
    PRIMARY KEY (id)
);

ALTER TABLE users ADD CONSTRAINT users_fk_3a9e92
    FOREIGN KEY (profile_picture_id)
    REFERENCES uploads (id);

ALTER TABLE countries ADD CONSTRAINT countries_fk_0fb9dd
    FOREIGN KEY (language_code)
    REFERENCES languages (language_code);

ALTER TABLE loans ADD CONSTRAINT loans_fk_a01d57
    FOREIGN KEY (category_id)
    REFERENCES loan_categories (id);

ALTER TABLE loans ADD CONSTRAINT loans_fk_f9706a
    FOREIGN KEY (secondary_category_id)
    REFERENCES loan_categories (id);

ALTER TABLE loans ADD CONSTRAINT loans_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE loan_bids ADD CONSTRAINT loan_bids_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE loan_bids ADD CONSTRAINT loan_bids_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE loan_bids ADD CONSTRAINT loan_bids_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE lenders ADD CONSTRAINT lenders_fk_987e4e
    FOREIGN KEY (id)
    REFERENCES users (id);

ALTER TABLE lenders ADD CONSTRAINT lenders_fk_b1f482
    FOREIGN KEY (country_id)
    REFERENCES countries (id);

ALTER TABLE borrowers ADD CONSTRAINT borrowers_fk_e407c5
    FOREIGN KEY (referrer_id)
    REFERENCES borrowers (id);

ALTER TABLE borrowers ADD CONSTRAINT borrowers_fk_987e4e
    FOREIGN KEY (id)
    REFERENCES users (id);

ALTER TABLE borrowers ADD CONSTRAINT borrowers_fk_b1f482
    FOREIGN KEY (country_id)
    REFERENCES countries (id);

ALTER TABLE borrowers ADD CONSTRAINT borrowers_fk_3f987d
    FOREIGN KEY (active_loan_id)
    REFERENCES loans (id);

ALTER TABLE borrowers ADD CONSTRAINT borrowers_fk_2a1546
    FOREIGN KEY (volunteer_mentor_id)
    REFERENCES volunteer_mentors (borrower_id);

ALTER TABLE borrower_profiles ADD CONSTRAINT borrower_profiles_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE lender_profiles ADD CONSTRAINT lender_profiles_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE loan_stages ADD CONSTRAINT loan_stages_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE loan_stages ADD CONSTRAINT loan_stages_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE transactions ADD CONSTRAINT transactions_fk_69bd79
    FOREIGN KEY (user_id)
    REFERENCES users (id);

ALTER TABLE transactions ADD CONSTRAINT transactions_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE comments ADD CONSTRAINT comments_fk_69bd79
    FOREIGN KEY (user_id)
    REFERENCES users (id);

ALTER TABLE comments ADD CONSTRAINT comments_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE comments ADD CONSTRAINT comments_fk_f1fc33
    FOREIGN KEY (parent_id)
    REFERENCES comments (id);

ALTER TABLE comments ADD CONSTRAINT comments_fk_47dd40
    FOREIGN KEY (translator_id)
    REFERENCES users (id);

ALTER TABLE uploads ADD CONSTRAINT uploads_fk_69bd79
    FOREIGN KEY (user_id)
    REFERENCES users (id);

ALTER TABLE comment_upload ADD CONSTRAINT comment_upload_fk_a3090d
    FOREIGN KEY (comment_id)
    REFERENCES comments (id);

ALTER TABLE comment_upload ADD CONSTRAINT comment_upload_fk_7bd8a2
    FOREIGN KEY (upload_id)
    REFERENCES uploads (id);

ALTER TABLE borrower_upload ADD CONSTRAINT borrower_upload_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_upload ADD CONSTRAINT borrower_upload_fk_7bd8a2
    FOREIGN KEY (upload_id)
    REFERENCES uploads (id);

ALTER TABLE installments ADD CONSTRAINT installments_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE installments ADD CONSTRAINT installments_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE installment_payments ADD CONSTRAINT installment_payments_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE installment_payments ADD CONSTRAINT installment_payments_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE installment_payments ADD CONSTRAINT installment_payments_fk_d8b355
    FOREIGN KEY (installment_id)
    REFERENCES installments (id);

ALTER TABLE borrower_payments ADD CONSTRAINT borrower_payments_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_payments ADD CONSTRAINT borrower_payments_fk_b93ef3
    FOREIGN KEY (country_id)
    REFERENCES countries (id);

ALTER TABLE lender_invites ADD CONSTRAINT lender_invites_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE lender_invites ADD CONSTRAINT lender_invites_fk_6c9cd5
    FOREIGN KEY (invitee_id)
    REFERENCES lenders (id);

ALTER TABLE lender_invite_visits ADD CONSTRAINT lender_invite_visits_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE lender_invite_visits ADD CONSTRAINT lender_invite_visits_fk_af44e0
    FOREIGN KEY (lender_invite_id)
    REFERENCES lender_invites (id);

ALTER TABLE lender_invite_transactions ADD CONSTRAINT lender_invite_transactions_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE paypal_transactions ADD CONSTRAINT paypal_transactions_fk_decb5d
    FOREIGN KEY (payment_id)
    REFERENCES payments (id);

ALTER TABLE stripe_transactions ADD CONSTRAINT stripe_transactions_fk_decb5d
    FOREIGN KEY (payment_id)
    REFERENCES payments (id);

ALTER TABLE payments ADD CONSTRAINT payments_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE payments ADD CONSTRAINT payments_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE payments ADD CONSTRAINT payments_fk_342886
    FOREIGN KEY (gift_card_transaction_id)
    REFERENCES gift_card_transaction (id);

ALTER TABLE gift_cards ADD CONSTRAINT gift_cards_fk_7e0a6f
    FOREIGN KEY (Lender_id)
    REFERENCES lenders (id);

ALTER TABLE gift_cards ADD CONSTRAINT gift_cards_fk_bbcfd6
    FOREIGN KEY (recipient_id)
    REFERENCES lenders (id);

ALTER TABLE gift_cards ADD CONSTRAINT gift_cards_fk_342886
    FOREIGN KEY (gift_card_transaction_id)
    REFERENCES gift_card_transaction (id);

ALTER TABLE gift_card_transaction ADD CONSTRAINT gift_card_transaction_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE forgiven_loans ADD CONSTRAINT forgiven_loans_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE forgiven_loans ADD CONSTRAINT forgiven_loans_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE forgiven_loans ADD CONSTRAINT forgiven_loans_fk_7e0a6f
    FOREIGN KEY (lender_id)
    REFERENCES lenders (id);

ALTER TABLE borrower_refunds ADD CONSTRAINT borrower_refunds_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_refunds ADD CONSTRAINT borrower_refunds_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE borrower_refunds ADD CONSTRAINT borrower_refunds_fk_eae020
    FOREIGN KEY (borrower_payment_id)
    REFERENCES borrower_payments (id);

ALTER TABLE volunteer_mentors ADD CONSTRAINT volunteer_mentors_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE volunteer_mentors ADD CONSTRAINT volunteer_mentors_fk_b1f482
    FOREIGN KEY (country_id)
    REFERENCES countries (id);

ALTER TABLE borrower_join_logs ADD CONSTRAINT borrower_join_logs_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_contacts ADD CONSTRAINT borrower_contacts_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_feedback_messages ADD CONSTRAINT borrower_feedback_messages_fk_1eaec8
    FOREIGN KEY (loan_id)
    REFERENCES loans (id);

ALTER TABLE borrower_feedback_messages ADD CONSTRAINT borrower_feedback_messages_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE borrower_reviews ADD CONSTRAINT borrower_reviews_fk_1d2ce7
    FOREIGN KEY (borrower_id)
    REFERENCES borrowers (id);

ALTER TABLE category_translations ADD CONSTRAINT category_translations_fk_0fb9dd
    FOREIGN KEY (language_code)
    REFERENCES languages (language_code);

ALTER TABLE category_translations ADD CONSTRAINT category_translations_fk_a01d57
    FOREIGN KEY (category_id)
    REFERENCES loan_categories (id);

ALTER TABLE lending_groups ADD CONSTRAINT lending_groups_fk_976d10
    FOREIGN KEY (creator_id)
    REFERENCES lenders (id);

ALTER TABLE lending_groups ADD CONSTRAINT lending_groups_fk_7c908e
    FOREIGN KEY (leader_id)
    REFERENCES lenders (id);

ALTER TABLE lending_groups ADD CONSTRAINT lending_groups_fk_60573b
    FOREIGN KEY (group_profile_picture_id)
    REFERENCES uploads (id);

ALTER TABLE lending_group_members ADD CONSTRAINT lending_group_members_fk_b5a104
    FOREIGN KEY (member_id)
    REFERENCES lenders (id);

ALTER TABLE lending_group_members ADD CONSTRAINT lending_group_members_fk_e15860
    FOREIGN KEY (group_id)
    REFERENCES lending_groups (id);

ALTER TABLE notifications ADD CONSTRAINT notifications_fk_69bd79
    FOREIGN KEY (user_id)
    REFERENCES users (id);