import sqlite3
from faker import Faker
from random import randrange
from datetime import datetime, timedelta


def create_tables(con):
    con.execute("""CREATE TABLE "cfp_talk" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "title" varchar(128) NOT NULL, "accepted" bool NULL, "category_id" integer NOT NULL REFERENCES "cfp_talkcategory" ("id") DEFERRABLE INITIALLY DEFERRED, "site_id" integer NOT NULL, "start_date" datetime NULL, "room_id" integer NULL REFERENCES "cfp_room" ("id") DEFERRABLE INITIALLY DEFERRED, "confirmed" bool NULL)""")
    con.execute("""CREATE TABLE "cfp_room" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(256) NOT NULL)""")
    con.execute("""CREATE TABLE "cfp_talkcategory" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(64) NOT NULL)""")
    con.execute("""CREATE TABLE "cfp_participant" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar(128) NOT NULL, "twitter" varchar(100) NOT NULL, "mastodon" varchar(100) NOT NULL)""")
    con.execute("""CREATE TABLE "cfp_talk_speakers" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "talk_id" integer NOT NULL REFERENCES "cfp_talk" ("id") DEFERRABLE INITIALLY DEFERRED, "participant_id" integer NOT NULL REFERENCES "cfp_participant" ("id") DEFERRABLE INITIALLY DEFERRED)""")


def populate_fake_data(con):
    faker = Faker()
    categories = ["Conférence de 30 minutes", "Conférence d'une heure"]
    participants = 16
    rooms = 4
    talks = 10
    assignments = (talks + participants) // 2
    for _ in range(participants):
        con.execute("INSERT INTO cfp_participant (name, mastodon, twitter) VALUES (?, ?, ?)",
                    (faker.name(), '@' + faker.email(), '@' + faker.user_name()))
    for _ in range(rooms):
        con.execute("INSERT INTO cfp_room (name) VALUES (?)", (faker.name(),))
    for category in categories:
        con.execute("INSERT INTO cfp_talkcategory (name) VALUES (?)", (category,))
    start_date = datetime.now() + timedelta(days=5)
    for _ in range(talks):
        start_date += timedelta(hours=randrange(1))
        con.execute("INSERT INTO cfp_talk (title, accepted, category_id, site_id, start_date, room_id, confirmed) VALUES (?, true, ?, 4, ?, ?, true)",
                    (faker.sentence(nb_words=5), randrange(len(categories)), start_date, randrange(rooms)))
    for _ in range(assignments):
        con.execute("INSERT INTO cfp_talk_speakers (talk_id, participant_id) VALUES (?, ?)",
                    (randrange(talks), randrange(participants)))


def main():
    con = sqlite3.connect("db.sqlite3", detect_types=sqlite3.PARSE_DECLTYPES)

    with con:
        create_tables(con)
        populate_fake_data(con)

if __name__ == '__main__':
    main()