-- Creates a new database when a new game is created
-- @author Eric Pietrocupo
-- @date February 19th, 2026
-- @license GNU GPL 3.0


-- Table dropping (avoids some errors)
DROP TABLE IF EXISTS player;
DROP TABLE IF EXISTS stock;
DROP TABLE IF EXISTS holding;
DROP TABLE IF EXISTS variant;
DROP TABLE IF EXISTS roll;
DROP TABLE IF EXISTS game;
DROP TABLE IF EXISTS event;
DROP TABLE IF EXISTS player_event;
DROP TABLE IF EXISTS log;
DROP TABLE IF EXISTS aipreference;

-- Table creation

CREATE TABLE player (
   pk INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   money INTEGER DEFAULT 5000 NOT NULL CHECK (money >= 0),
   ishuman TEXT DEFAULT 'N' CHECK (ishuman = 'Y' OR ishuman = 'N'),
   aiperso_avg REAL DEFAULT 100.0 NOT NULL,
   aiperso_sd REAL DEFAULT 3.0 NOT NULL,
   aiperso_sell_bias REAL DEFAULT 1.0 NOT NULL,
   aiperso_buy_bias REAL DEFAULT 1.0 NOT NULL,
   aiperso_hold REAL DEFAULT 110.0 NOT NULL
);

-- Contains the ai preference bias for each stock. Used by AI players.
CREATE TABLE aipreference (
   fk_player INTEGER REFERENCES player(pk),
   fk_stock INTEGER NOT NULL REFERENCES stock(pk),
   value REAL DEFAULT 1.0 NOT NULL,
   PRIMARY KEY (fk_player, fk_stock)
);


CREATE TABLE event (
   pk INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   fk_stock INTEGER NOT NULL REFERENCES stock(pk),
   move INTEGER NOT NULL CHECK (move >=-1 AND move <=1),
   number INTEGER NOT NULL CHECK (number >=5 AND number <= 20 AND number % 5 = 0),
   UNIQUE(fk_stock, move, number)
);

-- Keep track of event cards in the hand of players. Simplifies the queries when
-- multiple events can be triggered.
CREATE TABLE player_event (
   fk_player INTEGER REFERENCES player(pk),
   fk_event INTEGER REFERENCES event(pk),
   istriggered TEXT DEFAULT 'N' NOT NULL,
   PRIMARY KEY (fk_player, fk_event)
);

CREATE TABLE stock (
   pk INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   code TEXT NOT NULL,
   isdivpaid TEXT DEFAULT 'N' CHECK (isdivpaid = 'Y' OR isdivpaid = 'N'),
   split TEXT DEFAULT 'N' CHECK (isdivpaid = 'Y' OR isdivpaid = 'N'),
   crash TEXT DEFAULT 'N' CHECK (isdivpaid = 'Y' OR isdivpaid = 'N'),
   current_price INTEGER DEFAULT 100 NOT NULL, -- Allow overflow below 0$ and above 2$ to handle crash and splits
   previous_price INTEGER DEFAULT 100 NOT NULL
);
-- NOTE: Floating number for prices has ben stored as cents to avoid calculation errors
-- due to floating point arithmetics

-- NOTE: If the stock market history is to be saved, keep the market value into a separate table
-- market (fk_stock, turn_number, value) primary key (fk_stock, turn_number);


CREATE TABLE holding (
   fk_player INTEGER REFERENCES player(pk),
   fk_stock  INTEGER REFERENCES stock(pk),
   quantity  INTEGER DEFAULT 0 CHECK (quantity >= 0) NOT NULL,
   cost INTEGER DEFAULT 0 CHECK (cost >= 0) NOT NULL,
   PRIMARY KEY ( fk_player, fk_stock )
);
-- NOTE: The cost is used to determine the average payed and determine % of profit or losses

CREATE TABLE variant (
   pk INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   isenabled TEXT DEFAULT 'N' CHECK (isenabled = 'Y' OR isenabled = 'N')
);

CREATE TABLE roll (
   fk_stock INTEGER NOT NULL REFERENCES stock(pk),
   move INTEGER NOT NULL CHECK (move >=-1 AND move <=1),
   number INTEGER NOT NULL CHECK (number >=5 AND number <= 50 AND number % 5 = 0)
);
-- NOTE: Floating number for prices has ben stored as cents to avoid calculation errors
-- due to floating point arithmetics

CREATE TABLE game (
   turn_number INTEGER DEFAULT 0 NOT NULL,
   game_length INTEGER DEFAULT 100 NOT NULL,
   fk_active_player INTEGER REFERENCES player(pk) NOT NULL,
   nb_roll INTEGER DEFAULT 1 NOT NULL,
   insider_bonus INTEGER DEFAULT 10
);


CREATE TABLE log (
   pk INTEGER PRIMARY KEY,
   turn INTEGER DEFAULT 0 NOT NULL,
   fk_player INTEGER REFERENCES player(pk) NOT NULL,
   message TEXT
);
-- NOTE: I did not set the text not null, so that I could debug null error output as message.
-- It makes debugging faulty triggers easier.
-- Also primary key is essential for accurate log ordering. Else the grouping shuffle the order.


