all codes.txt

create table user_account(
    userid int not null PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) BINARY NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL, 
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
    money integer default 0,
    delay integer default 0
);


# 
delimiter $$
CREATE TRIGGER usernamegsix BEFORE insert ON user_account
    FOR EACH row 
        BEGIN
            DECLARE numLength INT;
            SET numLength = (SELECT LENGTH(NEW.username));
            IF (numLength < 6) THEN
                SIGNAL SQLSTATE '45000' set message_text='Username must consist of 6 characters at least!!!';
            END IF;
end;


#
insert into user_account(username, password)
values('234234234234', 'asd');

#
# SELECT *  FROM user_account WHERE BINARY username = 'amirmohammad'


#
SELECT * FROM user_account WHERE username REGEXP '^[A-Za-z0-9]+$';


#
delimiter $$
CREATE TRIGGER usernamecomplexity BEFORE INSERT ON user_account
    FOR EACH ROW
        BEGIN
            IF NOT (SELECT NEW.username REGEXP '[A-Za-z]' and NEW.username REGEXP '[0-9]') THEN
                SIGNAL sqlstate '45000' set message_text = 'Username must have both alphabets an numbers!!';
            END IF;
END;


SELECT '234234234234' REGEXP '[A-Za-z]' and '234234234234' REGEXP '[0-9]'



######################
delimiter $$
CREATE TRIGGER passwordg8 BEFORE insert ON user_account
    FOR EACH row 
        BEGIN
            DECLARE numLength INT;
            SET numLength = (SELECT LENGTH(NEW.password));
            IF (numLength < 8) THEN
                SIGNAL SQLSTATE '45000' set message_text='Password must consist of 8 characters at least!!!';
            END IF;
end;






##########################################
#
delimiter $$
CREATE TRIGGER passwordcomplexity BEFORE INSERT ON user_account
    FOR EACH ROW
        BEGIN
            IF NOT (SELECT NEW.password REGEXP '[A-Za-z]' and NEW.password REGEXP '[0-9]') THEN
                SIGNAL sqlstate '45000' set message_text = 'Password must have both alphabets an numbers!!';
            else
                set new.password = SHA2(new.password, 224);
            END IF;
END;



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
CREATE TABLE child (
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES user_account(userid)
        ON DELETE CASCADE
);




(((((((((((((((((((())))))))))))))))))))
INSERT INTO user_account (username, password) 
    VALUES ('tom37482394stills', 'asdkjahsdkj7387928388');
INSERT INTO child (parent_id)
    VALUES (LAST_INSERT_ID());







################################################### create table user_informatio
create table user_information (
    address varchar(512) not null, 
    fname varchar(50) not null, 
    surname varchar(50) not null, 
    role varchar(50) not null,
    userid int not null,
    FOREIGN KEY (`userid`) REFERENCES `user_account` (`userid`) ON DELETE CASCADE
);





##################################### join user account and user informations
SELECT user_account.userid, user_account.username, user_information.fname, user_information.surname
FROM user_account
INNER JOIN user_information ON user_information.userid = user_account.userid
where user_account.id = 1;


################################### see table fields
DESCRIBE user_account;
DESCRIBE user_information;




$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ book table
create table book(
    bookid int not null PRIMARY KEY AUTO_INCREMENT,
    types varchar(40) not null,
    name VARCHAR(50) BINARY NOT NULL UNIQUE,
    writer VARCHAR(50) NOT NULL,
    date date not null, 
    verion int,
    count int,
    price int
);


insert into book(name, writer, date, verion, count, types)
values ('100', 'amir', '2020-12-12', 0, 3, 'amoozeshi');




##################################################check money
delimiter $$
CREATE TRIGGER checkvalue BEFORE insert ON user_account
    FOR EACH row 
        BEGIN
            IF (select sign(new.money)) < 1 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The money that you insert must be over 0 toman.';
            END IF;
end;


delimiter $$
CREATE TRIGGER checkbvalue BEFORE update ON user_account
    FOR EACH row 
        BEGIN
            IF (select sign(new.money)) < 1 THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The money that you insert must be over 0 toman.';
            END IF;
end;



$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ count of book
delimiter $$
CREATE TRIGGER bookcount BEFORE update ON book
    FOR EACH row 
        BEGIN
            IF (select sign(new.count) = -1) and (select sign(old.count) = 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This book is not currently available.';
            END IF;
end;





#################################### check role to borrow
select * from user_account u
join book b where u.role = 'student' and (b.types = '' or b.types = 'amoozeshi') and u.userid = 1;







########################################################## kam kardane poool
delimiter $$
CREATE TRIGGER reduce_money BEFORE update ON user_account
    FOR EACH row 
        BEGIN
            IF (select sign(new.money) = -1) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'You do not have enough money.';
            END IF;
end;



delimiter $$
CREATE TRIGGER reduce_money BEFORE update ON user_account
    FOR EACH row 
        BEGIN
            IF (select sign(old.money - new.money) = -1) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This book is not currently available.';
            END IF;
end;



$$$$$$$$$$$$$$$$$$$$ test kam shodan az hesab
update user_account u
join book b
set u.money = u.money - ( b.price * 5 ) / 100
where u.userid = 1 and b.bookid = 1;




$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$ operations in get book successfull or not
create table getbook_opt (
    message varchar(512) not null, 
    operation BOOLEAN not null,
    userid int not null,
    FOREIGN KEY (`userid`) REFERENCES `user_account` (`userid`) ON DELETE CASCADE
);




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% successful request to admin inbox
create table inbox (
    inboxid int not null PRIMARY KEY AUTO_INCREMENT,
    message varchar(512) not null, 
    operation BOOLEAN not null default True,
    userid int not null,
    bookid int not null,
    FOREIGN KEY (`userid`) REFERENCES `user_account` (`userid`) ON DELETE CASCADE,
    FOREIGN KEY (`bookid`) REFERENCES `book` (`bookid`) ON DELETE CASCADE,
    date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);






$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$     join bara inbox
select book.name, inbox.inboxid from book 
join inbox where book.bookid = inbox.bookid;


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% list darkhast ha
select * from inbox order by date_created DESC;






%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%  ezafe kardan be zaman
UPDATE `user_account` SET `date_created` = DATE_ADD(`date_created` , INTERVAL 1 DAY) WHERE `userid` = 1;

delimiter $$
CREATE TRIGGER deliver_date BEFORE INSERT ON `inbox` 
FOR EACH ROW SET
    NEW.deliver_date = TIMESTAMPADD(DAY, 14, NEW.deliver_date);




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% add delivered or not in inbox
alter table `inbox`
add column delivered boolean default False;

alter table `inbox`
add column delays boolean default False;



*************************************************************  compare date
select '2000-12-12' < CURDATE();


###########################################################   trigger to check delay in deliver
delimiter $$
CREATE TRIGGER check_delay BEFORE update ON `inbox`
    FOR EACH row 
        BEGIN
            IF old.deliver_date > CURDATE() THEN
                set new.delays = False;
            END IF;
END;





delimiter $$
CREATE TRIGGER check_delay BEFORE update ON `inbox`
    FOR EACH row 
        BEGIN
            IF deliver_date > CURDATE() THEN
                update inbox set delays = False;
            END IF;
END;



*********************************************************************   hash password
delimiter $$
CREATE TRIGGER `hash_password`
    AFTER INSERT ON `user_account` FOR EACH ROW
    BEGIN
        UPDATE user_account 
            SET password = SHA2(new.password, 224);
    END




&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&       deliver book table
create table deliver_book (
    deliver_id int not null PRIMARY KEY AUTO_INCREMENT,
    message varchar(512) not null, 
    userid int not null,
    bookid int not null,
    FOREIGN KEY (`userid`) REFERENCES `user_account` (`userid`) ON DELETE CASCADE,
    FOREIGN KEY (`bookid`) REFERENCES `book` (`bookid`) ON DELETE CASCADE,
    date_delivered TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


((((((((((((((((((((((((((((((((((((((((((()))))))))))))))))))))))))))))))))))))))))))   check count and version of book  in add book
delimiter $$
CREATE TRIGGER count_version_book BEFORE insert ON book
    FOR EACH row 
        BEGIN
            IF (select sign(new.count) = -1) or (select sign(new.count) = 0) or (select sign(new.verion) = -1) or (select sign(new.verion) = 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'نسخه و تعداد کتاب وارد شده باید بزرگتر از صفر باشد';
            END IF;
end;


&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& chech count in update
delimiter $$
CREATE TRIGGER count_version_book1 BEFORE update ON book
    FOR EACH row 
        BEGIN
            IF (select sign(new.count) = -1) or (select sign(new.count) = 0) or (select sign(new.verion) = -1) or (select sign(new.verion) = 0) THEN
                SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'نسخه و تعداد کتاب وارد شده باید بزرگتر از صفر باشد';
            END IF;
end;


%%%%%%%%%%%%%%%%%%%%%%%%%%%%
$ export FLASK_APP=main
$ export FLASK_ENV=development
$ flask run