class Story(models.Model):
    #story_id =
    story_name = models.CharField(unique=False, max_length=255)
    story_description = models.TextField()
    story_trailer = models.CharField(unique=False, max_length=255)

    class Meta:
        db_table = 'story'

    def __unicode__(self):
        return self.story_name


class Pool(models.Model):
    transition_text = models.TextField()
    pool_sequence_nr = models.IntegerField(unique=False)
    story = models.ForeignKey('Story')

    class Meta:
        db_table = 'pool'

    def __unicode__(self):
        return self.pool_sequence_nr


class Video(models.Model):
    video_name = models.CharField(unique=False, max_length=255)
    video_url = models.CharField(unique=False, max_length=255)
    pool = models.ForeignKey('Pool')

    class Meta:
        db_table = 'video'

    def __unicode__(self):
        return self.video_name


class Place(models.Model):
    #place_id
    place_name = models.CharField(unique=False, max_length=255)
    beacon_id =  models.CharField(unique=False, max_length=255)
    video = models.ForeignKey('Video')

    class Meta:
        db_table = 'place'

    def __unicode__(self):
        return self.place_name


class Comment(models.Model):
    comment_username = models.CharField(unique=False, max_length=255)
    comment_text = models.TextField()
    place = models.ForeignKey('Place')

    class Meta:
        db_table = 'comment'

    def __unicode__(self):
        return self.comment_username










CREATE TABLE story (
  story_id INT PRIMARY KEY AUTO_INCREMENT,
  story_name VARCHAR(40) NOT NULL,
  story_description VARCHAR(255) NOT NULL
);

CREATE TABLE pool (
  pool_id INT PRIMARY KEY AUTO_INCREMENT,
  transition VARCHAR(255) NOT NULL,
  sequence_nr INT NOT NULL,
  story INT NOT NULL,
  FOREIGN KEY (story) REFERENCES story(story_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE video (
  video_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  url VARCHAR(255) NOT NULL,
  pool INT NOT NULL REFERENCES pool(pool_id)
);

CREATE TABLE place (
  place_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  latitude FLOAT(11,8) NOT NULL,
  longitude FLOAT(11,8) NOT NULL,
  radius FLOAT(4,2) NOT NULL,
  video INT NOT NULL REFERENCES video(video_id)
);

CREATE TABLE comment (
  comment_id INT PRIMARY KEY AUTO_INCREMENT,
  user VARCHAR(40) NOT NULL,
  content VARCHAR(255) NOT NULL,
  place INT NOT NULL REFERENCES place(place_id)
);