DROP DATABASE IF EXISTS cuckoo;
CREATE DATABASE cuckoo;
GRANT ALL PRIVILEGES ON cuckoo.* to cuckoo@'%' IDENTIFIED BY 'cuckoo';
GRANT ALL PRIVILEGES ON cuckoo.* to cuckoo@'localhost' IDENTIFIED BY 'cuckoo';
USE cuckoo;

CREATE TABLE users (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    username VARCHAR(50) UNIQUE default '' NOT NULL,
    password VARCHAR(32) default '' NOT NULL,
    email VARCHAR(128),
    openid_url VARCHAR(518),
    full_name VARCHAR(64),
    location VARCHAR(128),
    description TEXT,
    profile_image_url VARCHAR(256),
    url VARCHAR(256),
    created DATETIME,

    INDEX (password),
    INDEX (username)
);

CREATE TABLE friends (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    user_id INTEGER NOT NULL,
    friend_id INTEGER NOT NULL,
    ignored INTEGER DEFAULT 0,
    created DATETIME,

    INDEX (user_id),
    INDEX (friend_id)
);

CREATE TABLE updates (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    user_id INTEGER,
    text TEXT,
    kind INTEGER,
    tags TEXT,
    source VARCHAR(255),
    source_etag VARCHAR(128),
    created DATETIME
);

CREATE TABLE favorites (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    update_id INTEGER,
    user_id INTEGER,
    created DATETIME,

    INDEX (user_id),
    INDEX (update_id)
);

CREATE TABLE tags (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    user_id INTEGER,
    update_id INTEGER,
    tag VARCHAR(256),
    created DATETIME,

    INDEX (tag)
);

CREATE TABLE feeds (
	id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    user_id INTEGER,
    url VARCHAR(512),
    title VARCHAR(512),
    data TEXT,
    tags TEXT,
    last_modified VARCHAR(128),
    etag VARCHAR(128),
    last_scan DATETIME,
    next_scan DATETIME,
    scan_period INTEGER,
    created DATETIME,

    INDEX(last_scan),
    INDEX(next_scan),
    INDEX(user_id)
);