BEGIN TRANSACTION;

DROP TABLE IF EXISTS GEV_Units;
CREATE TABLE GEV_Units (
  ID integer primary key autoincrement,
  Name varchar(50) NOT NULL default '',
  ShortName varchar(20) default NULL,
  Category varchar(50) default 'Armor',
  Icon varchar(100) default 'blank',
  ATT integer default NULL,
  RNG integer default NULL,
  DEF integer default NULL,
  MP1 integer default NULL,
  MP2 integer default '0',
  Ciscos double(4,1) default '0.0',
  IsOfficial tinyint(4) default '0',
  Active tinyint(4) NOT NULL default '1',
  SortOrder double(16,4) default '0.0000',
  Notes varchar(255) default NULL
);


DROP TABLE IF EXISTS Gear;
CREATE TABLE Gear (
  ID integer primary KEY autoincrement DEFAULT NULL,
  SortOrder float(6,4) default '0.0000',
  Name varchar(50) NOT NULL default '',
  ShortName varchar(50) default NULL,
  Attack tinyint(1) default '0',
  Range tinyint(1) default '0',
  Defense tinyint(1) default '0',
  Ciscos tinyint(2) default '0',
  IsOfficial tinyint(1) default '0',
  Active tinyint(1) default '1',
  Notes text DEFAULT '',
  -- The following are hints for layout of record forms
  CountPerRow tinyint(1) default '15', -- number of items per row
  CountGroupBy tinyint(1) default '3', -- group items in sets of this many. e.g 3 = ooo ooo ooo ...
  CountDefault tinyint(1) default '0', -- Default count for input field
  CountHintMin tinyint(1) default '0', -- Min count for input field
  CountHintMax tinyint(1) default '10', -- Max count for input field
  CountHintDefault tinyint(1) default '0', -- ???
  CountHintStep tinyint(1) default '1', -- ???
  UNIQUE (Name)
);


DROP TABLE IF EXISTS GearToOgre;
CREATE TABLE GearToOgre (
  Ogre_ID int(10) NOT NULL default '0',
  Gear_ID int(10) NOT NULL default '0',
  Count int(10) default '0',
  UNIQUE(Ogre_ID,Gear_ID) ON CONFLICT REPLACE
);

DROP TABLE IF EXISTS Ogre;
CREATE TABLE Ogre(
-- Ogre Ciscos cost:
--   Cost := 2 * Treads
--   Cost += (Cost of each Gear item)
--   FinalCost := 5.357 * Cost / (10 - Ogre.MP)
--
-- Cisco/SJG Conversions:
--   Ciscos / 10 == Cisco Armor Unit Equivalence
--   Ciscos * 0.6 = Approx SJG VP cost
  ID integer primary KEY autoincrement DEFAULT NULL,
  Active INT DEFAULT 1,
  Name varchar(50) NOT NULL default '',
  Nickname varchar(50) default NULL,
  IsOfficial tinyint(1) default '0',
  Icon varchar(100) default 'Default.png',
  SizeClass integer default 7,
  MP integer default '0',
  TUMP integer default '0',
  Ciscos int(11) default '0',
  CiscosFudge int(11) default '0',
  Submitter varchar(100) default NULL,
  SubmitterInfo varchar(100) default NULL,
  Notes text,
  UNIQUE (Name)
);

DROP TRIGGER IF EXISTS tr_Ogre_delete;
CREATE TRIGGER tr_Ogre_delete
BEFORE DELETE ON Ogre
BEGIN
        DELETE FROM GearToOgre WHERE Ogre_ID=OLD.ID;
END;

-- We use a separate table for the list of icons available
-- to the application, rather than fishing them out of
-- the Ogre table, since that table might not contain all
-- entries.
DROP TABLE IF EXISTS OgreIcons;
CREATE TABLE OgreIcons(
        Icon text,
        UNIQUE(Icon) ON CONFLICT IGNORE
);

-- Overview of available Ogres
DROP VIEW IF EXISTS v_overview;
CREATE VIEW v_overview AS SELECT
         o.ID as ID,
        o.Name as Name,
        o.MP as MP,
        (o.TUMP * o.MP) as 'Treads',
        o.Ciscos as Ciscos,
        (o.Ciscos / 10.0) as 'Cisco Armor Value',
        (o.Ciscos * 0.6) as 'Approx SJG VP'
FROM Ogre o
WHERE
        -- IsOfficial!=0 AND
        Active != 0
;

-- List of all gear for a given Ogre ID:
-- select go.Count,g.ShortName,(go.Count*g.Ciscos) as Ciscos from Gear g, GearToOgre go where go.Ogre_ID=3 and g.ID=go.Gear_ID order by g.SortOrder;

------------------------------------------------------------------------
-- Internal table for calculating VP values
-- We want this to be a TEMP table, but SQL imported
-- from other scripts then apparently can't see it.
DROP TABLE IF EXISTS ciscos;
CREATE TABLE ciscos
(
        count integer default 0,
        desc text default '?',
        Ciscos integer default 0
);

------------------------------------------------------------------------
-- To update the Cisco point value of an Ogre record, insert
-- an entry into ttr_Ogre_Ciscos with an Ogre_ID of the given Ogre.
-- To update them all:
-- INSERT INTO ttr_Ogre_Ciscos SELECT ID FROM Ogre;
-- We want this to be a TEMP table, but SQL imported
-- from other scripts then apparently can't see it.
DROP TABLE IF EXISTS ttr_Ogre_Ciscos;
CREATE TABLE ttr_Ogre_Ciscos
(
        Ogre_ID integer default 0,
        UNIQUE(Ogre_ID) ON CONFLICT REPLACE
);
DROP TRIGGER IF EXISTS tr_Ogre_Ciscos;
CREATE TRIGGER tr_Ogre_Ciscos
AFTER INSERT ON ttr_Ogre_Ciscos
BEGIN
        DELETE FROM ciscos;
        INSERT INTO ciscos
        SELECT
                go.Count,
                g.ShortName,
                (go.Count*g.Ciscos)
        FROM
                Gear g,
                GearToOgre go
        WHERE
                go.Ogre_ID=NEW.Ogre_ID
                AND
                g.ID=go.Gear_ID
        ORDER BY
                g.SortOrder;

        UPDATE Ogre SET Ciscos=(
                SELECT round( 5.357 * (sum(v.Ciscos)+(2*(o.TUMP*o.MP))) / (10 - o.MP) + o.CiscosFudge, 2 )
                FROM ciscos AS v,
                        Ogre AS o
                WHERE
                        o.ID=NEW.Ogre_ID)
        WHERE ID=NEW.Ogre_ID;
        DELETE FROM ciscos;
        DELETE FROM ttr_Ogre_Ciscos;
END;

END TRANSACTION;