-- SQL for Twootr

DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `twoots`;
DROP TABLE IF EXISTS `friendships`;

-- USERS

CREATE TABLE `users` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`nick` TEXT UNIQUE NOT NULL,
	`jid` TEXT UNIQUE NOT NULL,
	`subscribed_at` REAL NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- TWOOTS
-- blah blah
CREATE TABLE `twoots` (
	`id` INTEGER PRIMARY KEY AUTOINCREMENT,
	`user_id` INTEGER NOT NULL REFERENCES `users`(`id`),
	`created_at` REAL NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`reply_to` INTEGER NULL DEFAULT NULL REFERENCES `id`,
	`text` TEXT
);

-- FRIENDSHIPS
-- links the users together
CREATE TABLE `friendships` (
	`from_id` INTEGER NOT NULL REFERENCES `users`(`id`),
	`to_id` INTEGER NOT NULL REFERENCES `users`(`id`),
	UNIQUE(`from_id`, `to_id`)
);

INSERT INTO `users` (`nick`, `jid`) VALUES ('greut','greut@swissjabber.ch');

INSERT INTO `twoots` (`user_id`, `text`) VALUES (1, "Welcome to Twootr!");