print "commencing"

databasename = GetInfo (67) .. GetPluginID () .. "_mud.db"
db = "db"


function dbcheck (code)

 if code ~= 0 and       -- no error
    code ~= 100 and     -- completed OK with another row of data
    code ~= 101 then    -- completed OK, no more rows
    local err = DatabaseError(db)  -- the rollback will change the error message
    DatabaseExec (db, "ROLLBACK")  -- rollback any transaction to unlock the database
    error (err, 2)                 -- show error in caller's context
  end -- if

end -- dbcheck 

DatabaseOpen (db, databasename, 6)  -- open database for read/write (+ create if necessary)

dbcheck (DatabaseExec (db, [[


  PRAGMA foreign_keys = ON;

  BEGIN;
  
  -- player classes
  DROP TABLE IF EXISTS classes;
  CREATE TABLE classes (
          class_id    INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL,
          initial_hp  INTEGER NOT NULL
        );


 -- players
  DROP TABLE IF EXISTS players;
  CREATE TABLE players (
          player_id INTEGER PRIMARY KEY AUTOINCREMENT,
          name      TEXT NOT NULL,
          level     INTEGER,
          class_id  INTEGER,  -- foreign key
          FOREIGN KEY(class_id) REFERENCES classes(class_id)

        );
    

  -- inventory item types (eg. weapon, bag)
  DROP TABLE IF EXISTS item_types;
  CREATE TABLE item_types (
          type_id     INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL
        );




  -- one entry for each inventory item per player
  DROP TABLE IF EXISTS inventory;
  CREATE TABLE inventory (
          inv_id      INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id   INTEGER NOT NULL,   -- foreign key
          type_id     INTEGER NOT NULL,   -- foreign key
          name        TEXT NOT NULL,
          FOREIGN KEY(player_id) REFERENCES players(player_id),
          FOREIGN KEY(type_id) REFERENCES item_types(type_id)
        );


  -- improve efficiency of looking up inventory for particular players            
  CREATE INDEX IF NOT EXISTS player_id_index ON inventory (player_id);

  -- improve efficiency of looking up inventory items of a certain type
  CREATE INDEX IF NOT EXISTS type_id_index ON inventory(type_id);

  -- skill names
  DROP TABLE IF EXISTS skills;
  CREATE TABLE skills (
          skill_id    INTEGER PRIMARY KEY AUTOINCREMENT,
          name        TEXT NOT NULL
        );
            
  -- this table has an entry for each player who has each skill
  DROP TABLE IF EXISTS player_skills;
  CREATE TABLE player_skills (
          ps_id     INTEGER PRIMARY KEY AUTOINCREMENT,
          player_id INTEGER NOT NULL,   -- foreign key
          skill_id  INTEGER NOT NULL,   -- foreign key
          FOREIGN KEY(player_id) REFERENCES players (player_id),
          FOREIGN KEY(skill_id) REFERENCES skills (skill_id),
          
          UNIQUE (player_id, skill_id)  -- make sure we don't duplicate them
        );

  -- improve efficiency of looking up skills for particular players            
  CREATE INDEX IF NOT EXISTS player_id_index ON player_skills (player_id);

  -- improve efficiency of looking up who has a particular skill            
  CREATE INDEX IF NOT EXISTS skill_id_index ON player_skills (skill_id);
        
  -- some classes
  INSERT INTO classes VALUES (1, 'mage', 100);
  INSERT INTO classes VALUES (2, 'warrior', 200);
  INSERT INTO classes VALUES (3, 'thief', 150);
  
  -- some players
  INSERT INTO players (name, level, class_id) VALUES ('Nick', 1, 1);
  INSERT INTO players (name, level, class_id) VALUES ('David', 1, 2);
  INSERT INTO players (name, level, class_id) VALUES ('Twisol', 5, 3);
  INSERT INTO players (name, level, class_id) VALUES ('Zeno', 10, 1);

  -- types of inventory
  INSERT INTO item_types (type_id, name) VALUES (1, 'weapon');
  INSERT INTO item_types (type_id, name) VALUES (2, 'armour');
  INSERT INTO item_types (type_id, name) VALUES (3, 'bag');
  INSERT INTO item_types (type_id, name) VALUES (4, 'shield');
  
  -- inventory - Nick
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 1, 'sword of misgiving');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 2, 'cloak of hiding');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 3, 'leather bag');
  INSERT INTO inventory (player_id, type_id, name) VALUES (1, 4, 'wooden shield');
  -- David
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 1, 'sword of attacking');
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 3, 'leather bag');
  INSERT INTO inventory (player_id, type_id, name) VALUES (2, 4, 'iron shield');
    
  -- skills
  INSERT INTO skills (skill_id, name) VALUES (1, 'fireball');
  INSERT INTO skills (skill_id, name) VALUES (2, 'frostshock');
  INSERT INTO skills (skill_id, name) VALUES (3, 'freeze');
  INSERT INTO skills (skill_id, name) VALUES (4, 'cure');
  
  -- player/skill combinations
  
  -- Nick
  INSERT INTO player_skills (player_id, skill_id) VALUES (1, 1);  -- Nick / fireball
  INSERT INTO player_skills (player_id, skill_id) VALUES (1, 2);  -- Nick / frostshock
  
  -- David
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 3);  -- David / freeze
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 4);  -- David / cure
  INSERT INTO player_skills (player_id, skill_id) VALUES (2, 2);  -- David / frostshock
  

  COMMIT;
  
          ]]))
        
DatabaseClose (db)  -- close it        
print "done"

INSERT INTO player_skills (player_id, skill_id) VALUES (2, 3);  -- David / freeze

INSERT INTO player_skills (player_id, skill_id) VALUES (999, 3);  -- David / freeze