%%%%% Page 8 %%%%%

CREATE KEYSPACE m201_student WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 3};

DESCRIBE KEYSPACES

DESCRIBE f201_student

USE f201_student;

%%%%% Page 9: Exercise 1 %%%%%

DROP TABLE users;

CREATE TABLE users (
    id INT PRIMARY KEY,
    fname TEXT,
    lname TEXT
);

CREATE TABLE users (
    id INT,
    fname TEXT,
    lname TEXT,
    PRIMARY KEY (id)
);

DESCRIBE TABLES

DESCRIBE TABLE users

INSERT INTO users (id, fname, lname) VALUES (1, 'Irena', 'Holubova');

INSERT INTO users (fname, lname, id) VALUES ('Pavel', 'Contos', 2);

SELECT * FROM users;

SELECT * FROM users WHERE (id = 1);

%%%%% Page 10: Exercise 2 %%%%%

SELECT * FROM users WHERE (lname = 'Holubova');

SELECT * FROM users WHERE (lname = 'Holubova') ALLOW FILTERING;

CREATE INDEX ON users (lname);

%%%%% Page 11: Exercise 3 %%%%%

CREATE TYPE person (
    first TEXT,
    last TEXT
);

CREATE TABLE contacts (
    id INT,
    name person,
    address TUPLE<TEXT, TEXT, INT>,
    emails SET<TEXT>,
    apps LIST<TEXT>,
    phones MAP<TEXT, TEXT>,
    PRIMARY KEY (id)
);

%%%%% Page 12: Exercise 4 %%%%%

INSERT INTO contacts (id, name, address, emails, apps, phones)
VALUES (
    1,
    {first: 'Irena', last: 'Holubova'},
    ('Malostranske namesti', 'Praha', 11800),
    {'holubova@ksi.mff.cuni.cz'},
    ['WhatsApp', 'Messenger'],
    {'work' : '+420951554316'}
);

INSERT INTO contacts (id, name, emails, apps, phones)
VALUES (
    2,
    {first: 'Pavel', last: 'Contos'},
    {'contos@ksi.mff.cuni.cz', 'pavel.contos@eli-beams.eu'},
    ['Viber', 'iMessage'],
    {'work' : '+420999999999', 'fax': '+420999333999'}
);

%%%%% Page 13: Exercise 5 %%%%%

UPDATE contacts
SET
    address = ('Malostranske namesti 25', 'Praha', 11800),
    apps = ['Hangouts']
WHERE (id = 1);

UPDATE contacts
SET
    emails = emails + {'irena.holubova@mff.cuni.cz'},
    apps = ['Messenger', 'WhatsApp'] + apps,
    phones = phones + {'home' : '+420123456789'}
WHERE (id = 1);

UPDATE contacts
SET
    emails = emails - {'irena.holubova@mff.cuni.cz'},
    apps = apps - ['Hangouts', 'Messenger'],
    phones = phones - {'home'}
WHERE (id = 1);

%%%%% Page 14: Exercise 6 %%%%%

DELETE
    address,
    apps[0],
    phones['work']
FROM contacts
WHERE (id = 1);

%%%%% Page 15-16: Exercise 7 %%%%%

CREATE TABLE messages (
    sender INT,
    app TEXT,
    date DATE,
    time TIME,
    recipient INT,
    message TEXT,
    PRIMARY KEY ((sender, app), date, time)
);

INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (2, 'WhatsApp', '2020-11-10', '10:00:00', 1, 'Hi Irena');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (2, 'WhatsApp', '2020-11-10', '10:15:00', 1, 'Are you there?');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (2, 'Messenger', '2020-11-10', '11:30:00', 1, 'Are you there?');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (1, 'Messenger', '2020-11-10', '11:32:00', 2, 'Yes, I am');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (1, 'Messenger', '2020-11-10', '11:33:00', 2, 'How are you?');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (2, 'iMessage', '2020-11-11', '11:59:00', 1, 'I am fine');
INSERT INTO messages(sender, app, date, time, recipient, message) VALUES (2, 'iMessage', '2020-11-11', '12:00:00', 1, 'And you?');

SELECT * FROM messages;

%%%%% Page 17: Exercise 8 %%%%%

SELECT *
FROM messages
WHERE sender = 2 AND app = 'iMessage'
ORDER BY date DESC, time DESC
ALLOW FILTERING;

SELECT sender, app, date, COUNT(*)
FROM messages
WHERE sender = 2
GROUP BY sender, app, date
ALLOW FILTERING;

SELECT app, date, COUNT(*)
FROM messages
WHERE sender = 2
GROUP BY app, date
ALLOW FILTERING;