create table user(
    id int auto_increment primary key,
    username varchar(50) not null,
    password varchar(50) not null,
    display_name varchar(40)
);

create table category(
    id int auto_increment primary key,
    name varchar(50) not null
);

create table post(
    id int auto_increment primary key,
    user_id int not null,
    category_id int not null,
    title varchar(100) not null,
    content text not null,
    created date,
    constraint post_user_fk_user_id
        foreign key (user_id) references user(id),
    constraint post_category_fk_category_id
        foreign key (category_id) references category(id)
);

create table menu(
    id int auto_increment primary key,
    name varchar(50) not null,
    url varchar(255) not null
);

create index user_id on post(user_id);
create index category_id on post(category_id);

INSERT INTO user (username, password, display_name) VALUES ('admin', 'value2', 'God');
INSERT INTO user (username, password, display_name) VALUES ('marian222', 'value2', 'Mariusz');
INSERT INTO user (username, password, display_name) VALUES ('stefan93', 'value2', 'Stefan');
INSERT INTO user (username, password, display_name) VALUES ('kowal22', 'value2', 'Kamil');

INSERT INTO category (name) VALUES ('Prono');
INSERT INTO category (name) VALUES ('Gry');
INSERT INTO category (name) VALUES ('News');
INSERT INTO category (name) VALUES ('Story');

INSERT INTO post (content, title, user_id, category_id)
VALUES ('So perhaps, you''ve generated some fancy text, and you''re content that you can now copy and paste your fancy text in the comments section of funny cat videos, but perhaps you''re wondering how it''s even possible to change the font of your text? Is it some sort of hack? Are you copying and pasting an actual font?','Hello world',1,1);

INSERT INTO post (title, content, user_id, category_id)
values ('Unicode text','So perhaps, you''ve generated some fancy text, and you''re content that you can now copy and paste your fancy text in the comments section of funny cat videos, but perhaps you''re wondering how it''s even possible to change the font of your text? Is it some sort of hack? Are you copying and pasting an actual font?',2,2);

create table country(
    id int auto_increment primary key,
    code varchar(3) not null ,
    name varchar(50)
);

alter table user
add country_id int,
add CONSTRAINT user_country_fk_country_id
FOREIGN KEY (country_id) REFERENCES country(id);

create index country_id on user(country_id);


select * from user;

select *
from user u
left join country c on u.country_id = c.id;

CREATE VIEW users_with_country AS
select u.username, c.name
from user u
left join country c on u.country_id = c.id;

select * from users_with_country;

UPDATE country
SET code = 'XX';

UPDATE country SET code = 'zS' WHERE id = 4;


select u.username, c.name
from user u
left join country c on u.country_id = c.id;


create table usercountry(
    id int primary key,
    username varchar(50) not null,
        name varchar(50)
);

INSERT INTO usercountry
select u.id, u.username, c.name
from user u
left join country c on u.country_id = c.id;

insert into users_with_country (username, name) value ('sds','sds')