func Subscribe(g pg.Getter, subscriber int64, link string) (*Subscription, error) {
	now := time.Now()

	var sub Subscription
	err := g.Get(&sub.Source, `
		INSERT INTO sources (link, created)
			VALUES ($1, $2)
		ON CONFLICT DO NOTHING
		RETURNING *
	`, link, now)
	if err != nil {
		return nil, pg.CastErr(err)
	}

	err = g.Get(&sub, `
		INSERT INTO subscriptions (subscriber_id, source_id, created)
			VALUES ($1, $2, $3)
		ON CONFLICT DO NOTHING
		RETURNING *
	`, subscriber, sub.Source.SourceID, now)
	if err != nil {
		return nil, pg.CastErr(err)
	}

	return &sub, err
}

func AccountByLogin(g pg.Getter, login, provider string) (*Account, error) {
	var a Account
	err := g.Get(&a, `
		SELECT * FROM accounts WHERE login = $1 AND provider = $2
		LIMIT 1
	`, login, provider)
	return &a, pg.CastErr(err)
}

func AccountByID(g pg.Getter, accountID int) (*Account, error) {
	var a Account
	err := g.Get(&a, `
		SELECT * FROM accounts WHERE account_id = $1
		LIMIT 1
	`, accountID)
	return &a, pg.CastErr(err)
}

func NoteByID(g pg.Getter, noteID int) (*Note, error) {
	var n Note
	err := g.Get(&n, `
		SELECT * FROM notes
		WHERE note_id = $1 AND (expire_at < $2 OR expire_at IS NULL)
		LIMIT 1
	`, noteID, time.Now())
	return &n, pg.CastErr(err)
}

func UpdateNote(g pg.Getter, note Note) (*Note, error) {
	err := g.Get(&note, `
		UPDATE notes
		SET content = $1, is_public = $2, expire_at = $3
		WHERE note_id = $4
		RETURNING *
	`, note.Content, note.IsPublic, note.ExpireAt, note.NoteID)
	return &note, pg.CastErr(err)
}

func CreateNote(g pg.Getter, n Note) (*Note, error) {
	n.CreatedAt = time.Now()
	err := g.Get(&n.NoteID, `
		INSERT INTO notes (owner_id, content, expire_at, created_at, is_public)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING note_id
	`, n.OwnerID, n.Content, n.ExpireAt, time.Now(), n.IsPublic)
	return &n, pg.CastErr(err)
}

func PasteByID(g pg.Getter, pid int64) (*Paste, error) {
	var p Paste
	err := g.Get(&p, `
		SELECT * FROM pastes
		WHERE id = $1
		LIMIT 1
	`, pid)
	return &p, pg.CastErr(err)
}

func CreatePaste(g pg.Getter, p Paste) (*Paste, error) {
	now := time.Now()
	err := g.Get(&p, `
		INSERT INTO pastes (content, created, updated)
		VALUES ($1, $2, $3)
		RETURNING *
	`, p.Content, now, now)
	return &p, pg.CastErr(err)
}

func UpdatePaste(g pg.Getter, p Paste) (*Paste, error) {
	now := time.Now()
	err := g.Get(&p, `
		UPDATE pastes
		SET content = $1, updated = $2
		WHERE id = $3
		RETURNING *
	`, p.Content, now, p.ID)
	return &p, pg.CastErr(err)
}

func TopicByID(g pg.Getter, topicID int64) (*Topic, error) {
	var t Topic
	err := g.Get(&t, `
		SELECT * FROM topics WHERE topic_id = $1 LIMIT 1
	`, topicID)
	if err != nil {
		return &t, pg.CastErr(err)
	}
	return &t, nil
}

func AccessToken(g pg.Getter, accountID int) (string, error) {
	var token string
	err := g.Get(&token, `
		SELECT access_token FROM sessions
		WHERE account = $1
		ORDER BY created DESC
		LIMIT 1
	`, accountID)
	return token, pg.CastErr(err)
}

func SessionAccount(g pg.Getter, key string) (*AccountWithScopes, error) {
	var a AccountWithScopes
	err := g.Get(&a, `
		SELECT a.account_id, a.login, a.created, s.scopes
		FROM accounts a INNER JOIN sessions s ON s.account = a.account_id
		WHERE s.key = $1
		LIMIT 1
	`, key)
	return &a, pg.CastErr(err)
}

func IsNoteOwner(g pg.Getter, noteID, ownerID int) (bool, error) {
	var isOwner bool
	err := g.Get(&isOwner, `
		SELECT COALESCE(sub.is_owner, false) FROM (
			SELECT true AS is_owner FROM notes
			WHERE note_id = $1 AND owner_id = $2
			LIMIT 1
		) sub
	`, noteID, ownerID)
	return isOwner, pg.CastErr(err)
}

func CreateComment(g pg.Getter, c Comment) (*Comment, error) {
	now := time.Now()

	if c.Created.IsZero() {
		c.Created = now
	}
	c.Updated = now

	var cid int64
	err := g.Get(&cid, `
		INSERT INTO comments (topic_id, author_id, content, created, updated)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING comment_id
	`, c.TopicID, c.AuthorID, c.Content, c.Created, c.Updated)
	if err != nil {
		return nil, pg.CastErr(err)
	}
	c.CommentID = cid
	return &c, nil
}

func CreateTopic(g pg.Getter, t Topic) (*Topic, error) {
	now := time.Now()

	if t.Created.IsZero() {
		t.Created = now
	}
	t.Updated = now

	var tid int64
	err := g.Get(&tid, `
		INSERT INTO topics (author_id, title, tags, created, updated)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING topic_id
	`, t.AuthorID, t.Title, t.Tags, t.Created, t.Updated)
	if err != nil {
		return nil, pg.CastErr(err)
	}
	t.TopicID = tid
	return &t, nil
}

func CreateSession(
	g pg.Getter,
	account int,
	key string,
	accessToken string,
	scopes []string,
) (string, error) {
	var ok bool
	err := g.Get(&ok, `
		INSERT INTO sessions (key, account, created, access_token, provider, scopes)
			SELECT $1, $2, $3, $4, a.provider, $5
			FROM accounts a WHERE a.account_id = $2 LIMIT 1
		RETURNING true
	`, key, account, time.Now(), accessToken, strings.Join(scopes, " "))
	if err != nil {
		return "", pg.CastErr(err)
	}
	if !ok {
		return "", pg.ErrNotFound
	}
	return key, nil
}