my database

create table account
(
    debit double default '0' null,
    credit double default '0' null,
    type enum('PERSONAL', 'DEPOSIT', 'DEPOSIT-PERCENTAGE') default 'passive' not null,
    amount double default '0' null,
    person_id tinyint null,
    deposit_id tinyint null,
    currency_id tinyint default '4' not null,
    operation_type enum('active', 'passive', 'active-passive') default 'passive' not null,
    code char(13) not null,
    id tinyint not null auto_increment
        primary key,
    credit_id tinyint null,
    constraint account_code_uindex
        unique (code),
    constraint account_id_uindex
        unique (id),
    constraint account_fk_credit
        foreign key (credit_id) references bank.credit (id),
    constraint account_fk_currency
        foreign key (currency_id) references bank.currency (id)
)
;

create index account_fk_currency
    on account (currency_id)
;

create index account_fk_person
    on account (person_id)
;

create index account_fk_deposit
    on account (deposit_id)
;

create index account_fk_credit
    on account (credit_id)
;

create table bank_fund
(
    id int not null
        primary key,
    debit double default '0' not null,
    credit double default '0' not null,
    type enum('active', 'passive', 'active-passive') not null,
    amount double default '0' null,
    currency_id tinyint not null
)
;

create table city
(
    id tinyint not null auto_increment
        primary key,
    name varchar(25) not null
)
;

create table contact
(
    id tinyint not null auto_increment
        primary key,
    phone_number char(13) not null,
    email varchar(255) not null,
    address varchar(255) not null,
    city_id tinyint null,
    constraint contact_phone_number_uindex
        unique (phone_number),
    constraint contact_email_uindex
        unique (email),
    constraint contact_fk_city
        foreign key (city_id) references bank.city (id)
)
;

create index contact_fk_city
    on contact (city_id)
;

create table credit
(
    amount double not null,
    start_date date null,
    end_date date null,
    person_id tinyint not null,
    currency_id tinyint not null,
    code char(7) not null,
    id tinyint not null auto_increment
        primary key,
    credit_type_id tinyint null,
    status enum('PENDING', 'ACCEPTED', 'DECLINED', 'EXPIRED') default 'PENDING' not null,
    period tinyint not null,
    constraint credit_id_uindex
        unique (id),
    constraint credit_fk_person
        foreign key (person_id) references bank.person (id),
    constraint credit_fk_currency
        foreign key (currency_id) references bank.currency (id),
    constraint credit_fk_deposit_type
        foreign key (credit_type_id) references bank.credit_type (id)
)
;

create index credit_fk_currency
    on credit (currency_id)
;

create index credit_fk_deposit_type
    on credit (credit_type_id)
;

create index credit_fk_person
    on credit (person_id)
;

create table credit_type
(
    id tinyint not null auto_increment
        primary key,
    name varchar(45) not null,
    percentage double not null,
    min_amount double default '100' not null,
    max_amount double default '50000' not null,
    min_period tinyint default '6' not null,
    max_period tinyint default '12' not null,
    constraint deposit_type_id_uindex
        unique (id),
    constraint deposit_type_name_uindex
        unique (name)
)
;

create table currency
(
    id tinyint not null auto_increment
        primary key,
    name varchar(25) not null
)
;

create table deposit
(
    amount double not null,
    start_date date null,
    end_date date null,
    person_id tinyint not null,
    currency_id tinyint not null,
    code char(7) not null,
    id tinyint not null auto_increment
        primary key,
    deposit_type_id tinyint null,
    status enum('PENDING', 'ACCEPTED', 'DECLINED', 'EXPIRED') default 'PENDING' not null,
    period tinyint not null,
    constraint deposit_id_uindex
        unique (id),
    constraint deposit_fk_person
        foreign key (person_id) references bank.person (id),
    constraint deppsit_fk_currency
        foreign key (currency_id) references bank.currency (id),
    constraint deposit_fk_deposit_type
        foreign key (deposit_type_id) references bank.deposit_type (id)
)
;

create index deppsit_fk_currency
    on deposit (currency_id)
;

create index deposit_fk_person
    on deposit (person_id)
;

create index deposit_fk_deposit_type
    on deposit (deposit_type_id)
;

alter table account
    add constraint account_fk_deposit
        foreign key (deposit_id) references Bank.deposit (id)
            on delete cascade
;

create table deposit_type
(
    id tinyint not null auto_increment
        primary key,
    name varchar(45) not null,
    percentage double not null,
    min_amount double default '100' not null,
    max_amount double default '50000' not null,
    min_period tinyint default '6' not null,
    max_period tinyint default '12' not null,
    constraint deposit_type_id_uindex
        unique (id),
    constraint deposit_type_name_uindex
        unique (name)
)
;

create table login
(
    username varchar(45) not null,
    password varchar(45) not null,
    id tinyint not null auto_increment
        primary key,
    constraint login_username_uindex
        unique (username)
)
;

create table passport
(
    serial_number char(9) not null,
    issue_facility varchar(255) not null,
    issue_date date not null,
    expire_date date not null,
    address varchar(255) not null,
    city_id tinyint not null,
    id tinyint not null auto_increment
        primary key,
    constraint passport_serial_number_uindex
        unique (serial_number),
    constraint passport_fk_city
        foreign key (city_id) references bank.city (id)
)
;

create index passport_fk_city
    on passport (city_id)
;

create table person
(
    id tinyint not null auto_increment
        primary key,
    firstname varchar(25) not null,
    secondname varchar(25) not null,
    lastname varchar(25) not null,
    birth_date date not null,
    pension tinyint(1) not null,
    military tinyint(1) not null,
    contact_id tinyint null,
    sex enum('Мужской', 'Женский') null,
    passport_id tinyint null,
    login_id tinyint null,
    constraint person_passport_id_uindex
        unique (passport_id),
    constraint person_contact_id_uindex
        unique (contact_id),
    constraint person_login_id_uindex
        unique (login_id),
    constraint person_fk_passport
        foreign key (passport_id) references bank.passport (id),
    constraint person_fk_contact
        foreign key (contact_id) references bank.contact (id),
    constraint person_fk_login
        foreign key (login_id) references bank.login (id)
)
;

alter table account
    add constraint account_fk_person
        foreign key (person_id) references Bank.person (id)
            on delete cascade
;

create table transaction_log
(
    id tinyint not null auto_increment
        primary key,
    from_account varchar(255) not null,
    to_account varchar(255) not null,
    amount int not null,
    type enum('USABLE', 'USED', 'REUSEABLE') not null,
    constraint transaction_log_id_uindex
        unique (id)
)
;

create procedure acceptCredit (IN input_credit_id tinyint, OUT output_credit_code char(7))
BEGIN
    update Bank.credit
    set status = 'ACCEPTED'
    where id = input_credit_id;
    select code from Bank.credit
    where id = input_credit_id
    into output_credit_code;
  end;

create procedure acceptDeposit (IN input_deposit_id tinyint, OUT output_deposit_code char(7))
BEGIN
    update Bank.deposit
    set status = 'ACCEPTED'
    where id = input_deposit_id;
    select code from Bank.deposit
    where id = input_deposit_id
    into output_deposit_code;
  end;

create procedure addContactInfo (IN input_phone_number varchar(15), IN input_email varchar(255), IN input_address varchar(255), IN input_city_id tinyint, OUT output_id tinyint)
begin
insert into contact(phone_number, email, address, city_id)
values(input_phone_number, input_email, input_address, input_city_id);
select last_insert_id() into output_id;
end;

create procedure addCredit (IN input_code char(7), IN input_period tinyint, IN input_person_id tinyint, IN input_currency_id tinyint, IN input_credit_type_id tinyint, IN input_status varchar(45), IN input_amount double, OUT output_id tinyint)
BEGIN
    insert into credit(code, period, person_id, currency_id, credit_type_id,
                       status, amount)
    values(input_code, input_period, input_person_id, input_currency_id,
           input_credit_type_id, input_status, input_amount);
    SELECT last_insert_id() into output_id;
  END;

create procedure addDeposit (IN input_code char(7), IN input_period tinyint, IN input_person_id tinyint, IN input_currency_id tinyint, IN input_deposit_type_id tinyint, IN input_status varchar(45), IN input_amount double, OUT output_id tinyint)
BEGIN
    insert into deposit(code, period, person_id, currency_id, deposit_type_id,
                        status, amount)
      values(input_code, input_period, input_person_id, input_currency_id,
            input_deposit_type_id, input_status, input_amount);
    SELECT last_insert_id() into output_id;
  END;

create procedure addDepositAccount (IN input_code char(13), IN input_type varchar(45), IN input_person_id tinyint, IN input_deposit_id tinyint, IN input_currency_id tinyint, IN input_amount double, OUT output_id tinyint)
BEGIN
    insert into Bank.account(code, type, person_id, deposit_id, currency_id, amount)
    values(input_code, input_type, input_person_id, input_deposit_id,
           input_currency_id, input_amount);
    select last_insert_id() into output_id;
  end;

create procedure addDepositPercentageAccount (IN input_code char(13), IN input_type varchar(45), IN input_person_id tinyint, IN input_deposit_id tinyint, IN input_currency_id tinyint, OUT output_id tinyint)
BEGIN
    insert into Bank.account(code, type, person_id, deposit_id, currency_id)
    values(input_code, input_type, input_person_id, input_deposit_id,
           input_currency_id);
    select last_insert_id() into output_id;
  end;

create procedure addLoginInfo (IN input_username varchar(255), IN input_password varchar(255), OUT output_id tinyint)
begin
insert into login(username, password)
values(input_username, input_password);
select last_insert_id() into output_id;
end;

create procedure addPassport (IN input_serial_number char(9), IN input_issue_facility varchar(255), IN input_issue_date date, IN input_expire_date date, IN input_city_id tinyint, IN input_address varchar(255), OUT output_id tinyint)
begin
insert into passport(serial_number, issue_facility, issue_date, expire_date, city_id, address)
value(input_serial_number, input_issue_facility, input_issue_date, input_expire_date, input_city_id, input_address);
select last_insert_id() into output_id;
end;

create procedure addPerson (IN input_firstname varchar(25), IN input_secondname varchar(25), IN input_lastname varchar(25), IN input_birth_date date, IN input_sex varchar(15), IN input_pension tinyint(1), IN input_military tinyint(1), OUT output_id tinyint)
begin
    insert into Bank.person(firstname, secondname, lastname, birth_date, sex, pension, military)
    values(input_firstname, input_secondname, input_lastname, input_birth_date, input_sex, input_pension, input_military);
    select last_insert_id() into output_id;
    end;

create procedure addPersonalAccount (IN input_code char(13), IN input_type varchar(45), IN input_person_id tinyint, OUT output_id tinyint)
BEGIN
    insert into Bank.account(code, type, person_id)
    values(input_code, input_type, input_person_id);
    select last_insert_id() into output_id;
  end;

create procedure changePassword (IN input_username varchar(255), IN input_new_password varchar(255))
begin
    update Bank.login
      set password = input_new_password
    where username = input_username;
  end;

create procedure checkLogin (IN input_username varchar(255), IN input_password varchar(255), OUT permission tinyint(1))
BEGIN
    declare temp_password VARCHAR(255);
    set permission = FALSE;
    select password into temp_password
    from Bank.login
    where username = input_username;
    if (input_password = temp_password) THEN
      set permission = TRUE;
    END IF;
  END;

create procedure createCity (IN input_name varchar(45), OUT output_id tinyint)
begin
insert into city(name) value(input_name);
select last_insert_id() into output_id;
end;

create procedure declineCredit (IN input_credit_id tinyint, OUT output_credit_code char(7))
BEGIN
    update Bank.credit
    set status = 'DECLINED'
    where id = input_credit_id;
    select code from Bank.credit
    where id = input_credit_id
    into output_credit_code;
  end;

create procedure declineDeposit (IN input_deposit_id tinyint, OUT output_deposit_code char(7))
BEGIN
    update Bank.deposit
    set status = 'DECLINED'
    where id = input_deposit_id;
    select code from Bank.deposit
    where id = input_deposit_id
    into output_deposit_code;
  end;

create procedure deletePerson (IN input_person_id tinyint)
BEGIN
    declare temp_passport_id, temp_contact_id, temp_login_id TINYINT;
    select passport_id into temp_passport_id
    from person
    where id = input_person_id;
    select contact_id into temp_contact_id
    from person
    where id = input_person_id;
    select login_id into temp_login_id
    from person
    where id = input_person_id;
    delete from person
      where id = input_person_id;
    delete from passport
      where id = temp_passport_id;
    delete from contact
      where id = temp_contact_id;
    delete from login
      where id = temp_login_id;
  END;

create procedure getAllCreditTypeIds ()
BEGIN
    select id from Bank.credit_type;
  END;

create procedure getAllDepositTypeIds ()
BEGIN
        select id from Bank.deposit_type;
    END;

create procedure getAllPersonIds ()
BEGIN
        select id from person
          where login_id IS NOT NULL
                AND contact_id IS NOT NULL
                AND passport_id IS NOT null;
    END;

create procedure getCities ()
(
select name from city);

create procedure getCityIdByName (IN input_name varchar(45), OUT output_id tinyint)
begin
select id into output_id
from city where name=input_name;
end;

create procedure getCityNameById (IN input_id tinyint, OUT output_name varchar(45))
begin
select name into output_name
from city where id=input_id;
end;

create procedure getContactInfo (INOUT inoutput_id tinyint, OUT output_phone_number char(13), OUT output_email varchar(255), OUT output_address varchar(255), OUT output_city_id tinyint)
BEGIN
        select phone_number, email, address, city_id
           into output_phone_number, output_email, output_address, output_city_id
        from contact
            where id = inoutput_id;
    END;

create procedure getContactInfoId (IN input_person_id tinyint, OUT output_contact_id tinyint)
begin
select contact_id into output_contact_id
from person where id=input_person_id;
end;

create procedure getCredit (INOUT inoutput_id tinyint, OUT output_code char(7), OUT output_period tinyint, OUT output_person_id tinyint, OUT output_currency_id tinyint, OUT output_credit_type_id tinyint, OUT output_status varchar(45), OUT output_amount double)
begin
  select code, period, person_id, currency_id, credit_type_id,
         status, amount
  into output_code, output_period, output_person_id, output_currency_id,
      output_credit_type_id, output_status, output_amount
    from Bank.credit
    where id = inoutput_id;
    end;

create procedure getCreditType (INOUT inoutput_id tinyint, OUT output_name varchar(45), OUT output_percentage double, OUT output_min_amount double, OUT output_max_amount double, OUT output_min_period tinyint, OUT output_max_period tinyint)
BEGIN
    select  name, percentage, min_amount, max_amount, min_period, max_period
    into output_name, output_percentage, output_min_amount, output_max_amount,
      output_min_period, output_max_period
    from Bank.credit_type
    where id =  inoutput_id;
  END;

create procedure getCurrencies ()
(
select name from currency
);

create procedure getCurrencyIdByName (IN input_name varchar(45), OUT output_id tinyint)
begin
select id into output_id
from currency where name=input_name;
end;

create procedure getDeposit (INOUT inoutput_id tinyint, OUT output_code char(7), OUT output_period tinyint, OUT output_person_id tinyint, OUT output_currency_id tinyint, OUT output_deposit_type_id tinyint, OUT output_status varchar(45), OUT output_amount double)
begin
  select code, period, person_id, currency_id, deposit_type_id,
         status, amount
  into output_code, output_period, output_person_id, output_currency_id,
      output_deposit_type_id, output_status, output_amount
    from Bank.deposit
    where id = inoutput_id;
    end;

create procedure getDepositType (INOUT inoutput_id tinyint, OUT output_name varchar(45), OUT output_percentage double, OUT output_min_amount double, OUT output_max_amount double, OUT output_min_period tinyint, OUT output_max_period tinyint)
BEGIN
    select  name, percentage, min_amount, max_amount, min_period, max_period
    into output_name, output_percentage, output_min_amount, output_max_amount,
      output_min_period, output_max_period
    from Bank.deposit_type
    where id =  inoutput_id;
  END;

create procedure getLoginInfo (INOUT inoutput_id tinyint, OUT output_username varchar(45), OUT output_password varchar(45))
BEGIN
        select username, password
           into output_username, output_password
        from login
            where id = inoutput_id;
    END;

create procedure getLoginInfoId (IN input_person_id tinyint, OUT output_login_id tinyint)
begin
select login_id into output_login_id
from person where id=input_person_id;
end;

create procedure getPassport (INOUT inoutput_id tinyint, OUT output_issue_facility varchar(255), OUT output_issue_date date, OUT output_expire_date date, OUT output_city_id tinyint, OUT output_address varchar(255), OUT output_serial_number char(9))
BEGIN
        select issue_facility, issue_date, expire_date, city_id, address, serial_number
           into output_issue_facility, output_issue_date, output_expire_date, output_city_id,
               output_address, output_serial_number
        from passport
            where id = inoutput_id;
    END;

create procedure getPassportId (IN input_person_id tinyint, OUT output_passport_id tinyint)
begin
select passport_id into output_passport_id
from person where id=input_person_id;
end;

create procedure getPendingCredits ()
begin
    select d.id as credit_id, CONCAT(p.secondname, ' ', p.firstname, ' ', p.lastname) as name,
      d.code, CONCAT(dt.percentage, ' %') as percentage,
      CONCAT(d.amount, ' ', c.name) as amount, p.id as person_id
    from Bank.credit as d
      join Bank.person as p
        on d.person_id = p.id
      join Bank.credit_type as dt
        on d.credit_type_id = dt.id
      join Bank.currency as c
        on d.currency_id = c.id
    where d.status = 'PENDING';
  end;

create procedure getPendingDeposits ()
begin
    select d.id as deposit_id, CONCAT(p.secondname, ' ', p.firstname, ' ', p.lastname) as name,
      d.code, CONCAT(dt.percentage, ' %') as percentage,
      CONCAT(d.amount, ' ', c.name) as amount, p.id as person_id
    from Bank.deposit as d
      join Bank.person as p
        on d.person_id = p.id
      join Bank.deposit_type as dt
        on d.deposit_type_id = dt.id
      join Bank.currency as c
        on d.currency_id = c.id
    where d.status = 'PENDING';
  end;

create procedure getPerson (INOUT inoutput_id tinyint, OUT output_firstname varchar(255), OUT output_secondname varchar(255), OUT output_lastname varchar(255), OUT output_birth_date date, OUT output_sex varchar(15), OUT output_pension tinyint(1), OUT output_military tinyint(1))
BEGIN
        select firstname, secondname, lastname, birth_date, sex, pension, military
           into output_firstname, output_secondname, output_lastname, output_birth_date,
             output_sex, output_pension, output_military
        from person
            where id = inoutput_id;
    END;

create procedure getPersonByUsername (IN input_username varchar(255), OUT output_person_id tinyint)
BEGIN
    DECLARE temp_login_id TINYINT;
    select id into temp_login_id
      from login
      where username = input_username;
    select id into output_person_id
      from person
      where login_id = temp_login_id;
  END;

create procedure setContactInfo (IN input_person_id tinyint, IN input_contact_id tinyint)
begin
    update Bank.person
      set contact_id = input_contact_id
    where id = input_person_id;
  end;

create procedure setDepositPeriod (IN input_deposit_id tinyint, IN input_start_date date, IN input_end_date date)
BEGIN
    update Bank.deposit
      set start_date = input_start_date,
        end_date = input_end_date
    WHERE id = input_deposit_id;
  END;

create procedure setLoginInfo (IN input_person_id tinyint, IN input_login_id tinyint)
begin
    update Bank.person
      set login_id = input_login_id
    where id = input_person_id;
  end;

create procedure setPassport (IN input_person_id tinyint, IN input_passport_id tinyint)
begin
    update Bank.person
      set passport_id = input_passport_id
    where id = input_person_id;
  end;

create procedure updateContactInfo (IN input_contact_id tinyint, IN input_new_phone_number char(13), IN input_new_email varchar(255), IN input_new_address varchar(255), IN input_new_city_id tinyint)
begin
    update Bank.contact
      set phone_number = input_new_phone_number,
          email = input_new_email,
          address = input_new_address,
          city_id = input_new_city_id
    where id = input_contact_id;
  end;

create procedure updateLoginInfo (IN input_login_id tinyint, IN input_new_username varchar(255), IN input_new_password varchar(255))
begin
    update Bank.login
      set username = input_new_username, password = input_new_password
    where id = input_login_id;
  end;

create procedure updatePassport (IN input_passport_id tinyint, IN input_new_serial_number char(9), IN input_new_issue_facility varchar(255), IN input_new_issue_date date, IN input_new_expire_date date, IN input_new_city_id tinyint, IN input_new_address varchar(255))
begin
    update Bank.passport
      set serial_number = input_new_serial_number,
          issue_facility = input_new_issue_facility,
          issue_date = input_new_issue_date,
          expire_date = input_new_expire_date,
          city_id = input_new_city_id,
          address = input_new_address
    where id = input_passport_id;
  end;

create procedure updatePerson (IN input_person_id tinyint, IN input_new_firstname varchar(255), IN input_new_secondname varchar(255), IN input_new_lastname varchar(255), IN input_new_birth_date date, IN input_new_sex varchar(15), IN input_new_pension tinyint(1), IN input_new_military tinyint(1))
begin
    update Bank.person
      set firstname = input_new_firstname,
          secondname = input_new_secondname,
          lastname = input_new_lastname,
          birth_date = input_new_birth_date,
          sex = input_new_sex,
          pension = input_new_pension,
          military = input_new_military
    where id = input_person_id;
  end;