-- sql goes here
CREATE TABLE IF NOT EXISTS 'users' (
       id INTEGER PRIMARY KEY,
       tgb_id INTEGER NOT NULL UNIQUE,
       username TEXT,
       first_name TEXT,
       last_name TEXT,
       created INTEGER NOT NULL,
       edited INTEGER NOT NULL,
       deleted INTEGER
);

CREATE INDEX IF NOT EXISTS tgb_id_user_index ON 'users' (tgb_id);

CREATE TABLE IF NOT EXISTS 'visits' (
       id INTEGER PRIMARY KEY,
       tgb_id INTEGER NOT NULL,
       quantity INTEGER,
       edited INTEGER,
       FOREIGN KEY(tgb_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS tgb_id_visit_index ON 'users' (tgb_id);

CREATE TRIGGER IF NOT EXISTS check_daily_visits
   BEFORE INSERT ON 'users'
 BEGIN
  INSERT OR IGNORE INTO 'visits' (tgb_id, quantity, edited)
  VALUES (new.tgb_id, 1, strftime('%s', 'now'));
  END;


CREATE TABLE IF NOT EXISTS 'bots' (
       id INTEGER PRIMARY KEY,
       bot_name TEXT UNIQUE,
       created INTEGER NOT NULL
);


CREATE TABLE IF NOT EXISTS 'bot_users' (
       id INTEGER PRIMARY KEY,
       tgb_id INTEGER NOT NULL,
       bot_id INTEGER NOT NULL,
       created INTEGER NOT NULL,
       UNIQUE(tgb_id, bot_id),
       FOREIGN KEY(tgb_id) REFERENCES users(id) ON DELETE CASCADE,
       FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS 'bot_access_requests' (
       id INTEGER PRIMARY KEY,				
       tgb_id INTEGER NOT NULL,
       bot_id INTEGER NOT NULL,
       created INTEGER NOT NULL,
       UNIQUE(tgb_id, bot_id),
       FOREIGN KEY(tgb_id) REFERENCES users(id) ON DELETE CASCADE,
       FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS 'banned_bot_access' (
       id INTEGER PRIMARY KEY,				
       tgb_id INTEGER NOT NULL,
       bot_id INTEGER NOT NULL,
       created INTEGER NOT NULL,
       until INTEGER NOT NULL,
       UNIQUE(tgb_id, bot_id),
       FOREIGN KEY(tgb_id) REFERENCES users(id) ON DELETE CASCADE,
       FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
);