/***
 **
 ** FlowBetween File format version 1
 **
 ***/

/*
 *  ===== File declarations
 */

/*
 * The FlowBetween table indicates version information for this file and
 * contains a single record.
 */
CREATE TABLE FlowBetween (
    /* Data format version */
    DataVersion     INTEGER NOT NULL,

    /* Description of the version of FlowBetween that created this animation */
    FloVersion      TEXT NOT NULL,

    /* 1 if this was generating using a stable schema, 0 if generated using an unstable one */
    Stable          INTEGER NOT NULL
);

/*
 *  ===== The edit log
 */

/*
 * The edit log contains a list of edits made by the user
 */
CREATE TABLE Flo_EditLog (
    /* Order of this edit */
    Id      INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    /* Type of edit (see Flo_EnumerationDescriptions for the values) */
    Edit    INTEGER NOT NULL
);

/*
 * The editlog size table provides the size parameter for edits that require one
 */
CREATE TABLE Flo_EL_Size (
    EditId      INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    X           REAL NOT NULL,
    Y           REAL NOT NULL
) WITHOUT ROWID;

/*
 * If an element specifies an element ID, the EL_ElementId table supplies what that ID is
 */
CREATE TABLE Flo_EL_ElementId (
    EditId      INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    ElementId   INTEGER NOT NULL
) WITHOUT ROWID;

/*
 * If an edit applies to a layer, this supplies the layer ID
 */
CREATE TABLE Flo_EL_Layer (
    EditId      INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    Layer       INTEGER NOT NULL
) WITHOUT ROWID;

/*
 * The time for edits that use one. Times here are measured in microseconds
 * from the start of the animation.
 */
CREATE TABLE Flo_EL_When (
    EditId      INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    AtTime      INTEGER NOT NULL
) WITHOUT ROWID;

/*
 * Associates an edit containing brush information with the brush
 */
CREATE TABLE Flo_EL_Brush (
    EditId          INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    DrawingStyle    INTEGER NOT NULL,
    Brush           INTEGER NOT NULL REFERENCES Flo_Brush_Type(Brush)
) WITHOUT ROWID;

/*
 * Associates an edit containing brush properties with definition
 */
CREATE TABLE Flo_EL_BrushProperties (
    EditId          INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_EditLog(Id),

    BrushProperties INTEGER NOT NULL REFERENCES Flo_BrushProperties(BrushProperties)
) WITHOUT ROWID;

/*
 * Associates an edit with a series of raw input points
 */
CREATE TABLE Flo_EL_RawPoints (
    EditId      INTEGER NOT NULL REFERENCES Flo_EditLog(Id),
    Points      BLOB NOT NULL,

    PRIMARY KEY (EditId)
) WITHOUT ROWID;

/*
 *  ===== Colours, brushes and similar resources
 */

/*
 * Associates a brush with a brush type
 */
CREATE TABLE Flo_Brush_Type (
    Brush       INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,
    BrushType   INTEGER NOT NULL
);

/*
 * Provides an ink brush definition (BrushType == Ink)
 */
CREATE TABLE Flo_Brush_Ink (
    Brush           INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_Brush_Type(Brush),

    MinWidth        REAL NOT NULL,
    MaxWidth        REAL NOT NULL,
    ScaleUpDistance REAL NOT NULL
) WITHOUT ROWID;

/*
 * Associates an edit with any brush properties it may have
 */
CREATE TABLE Flo_BrushProperties (
    BrushProperties INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    Size            REAL NOT NULL,
    Opacity         REAL NOT NULL,
    Color           INTEGER NOT NULL REFERENCES Flo_Color_Type(Color)
);

/*
 * Associates a colour with a colour type
 */
CREATE TABLE Flo_Color_Type (
    Color       INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,
    ColorType   INTEGER NOT NULL
);

/*
 * A colour defined as an RGB triplet
 */
CREATE TABLE Flo_Color_Rgb (
    Color       INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_Color_Type(Color),

    R           REAL NOT NULL,
    G           REAL NOT NULL,
    B           REAL NOT NULL
) WITHOUT ROWID;

/*
 * A colour defined as an HSLuv triplet
 */
CREATE TABLE Flo_Color_Hsluv (
    Color       INTEGER NOT NULL PRIMARY KEY ASC REFERENCES Flo_Color_Type(Color),

    H           REAL NOT NULL,
    S           REAL NOT NULL,
    L           REAL NOT NULL
) WITHOUT ROWID;

/*
 *  ===== Animation
 */

/*
 * Definition of an animation
 */
CREATE TABLE Flo_Animation (
    /* The identifier of this animation */
    AnimationId     INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    /* Size of the main frame for this animation */
    SizeX           REAL NOT NULL,
    SizeY           REAL NOT NULL,

    /* Length of this animation, in microseconds */
    Duration        INTEGER NOT NULL,

    /* Length of a frame, in nanoseconds */
    Frame_Length_ns INTEGER NOT NULL
);

/*
 * Database is created with a single 2 minute long 30fps animation
 */
INSERT INTO Flo_Animation (
    SizeX, SizeY,
    Duration,
    Frame_Length_ns
) VALUES (
    1980.0, 1080.0,
    1000000 * 60 * 2,
    1000000000 / 30
);

/*
 * Represents a vector layer
 */
CREATE TABLE Flo_LayerType (
    /* The ID of this layer */
    LayerId         INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    /* The type of this layer */
    LayerType       INTEGER NOT NULL
);

/* Deleting a layer type deletes the corresponding animation layers and key frames */
CREATE TRIGGER Flo_Delete_Layer BEFORE DELETE ON Flo_LayerType
    BEGIN
        DELETE FROM Flo_AnimationLayers WHERE LayerId = Old.LayerId;
        DELETE FROM Flo_LayerKeyFrame WHERE LayerId = Old.LayerId;
    END;

/*
 * Associates an animation with the layers it contains
 */
CREATE TABLE Flo_AnimationLayers (
    AnimationId     INTEGER NOT NULL REFERENCES Flo_Animation(AnimationId),
    LayerId         INTEGER NOT NULL REFERENCES Flo_LayerType(LayerId),

    AssignedLayerId INTEGER NOT NULL,

    PRIMARY KEY (AnimationId, LayerId)
) WITHOUT ROWID;

CREATE UNIQUE INDEX FloIdx_AssignedLayers ON Flo_AnimationLayers (AnimationId, AssignedLayerId);

/*
 * Represents a keyframe for a layer
 */
CREATE TABLE Flo_LayerKeyFrame (
    /* ID of this keyframe */
    KeyFrameId  INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    /* The ID of the layer with keyframes */
    LayerId     INTEGER NOT NULL REFERENCES Flo_LayerType(LayerId),

    /* Time in microseconds where this keyframe starts */
    AtTime      INTEGER NOT NULL
);

CREATE INDEX FloIdx_FrameTime ON Flo_LayerKeyFrame (LayerId, AtTime);

/*
 * Specifies the assigned element ID for an element in a layer
 */
CREATE TABLE Flo_AssignedElementId (
    /* The ID of the element that has been given an assigned ID */
    ElementId   INTEGER NOT NULL PRIMARY KEY,

    /* The ID assigned to this element */
    AssignedId  INTEGER NOT NULL
) WITHOUT ROWID;

/*
 * Represents an element in a vector layer
 */
CREATE TABLE Flo_VectorElement (
    /* Identifier for this element */
    ElementId           INTEGER NOT NULL PRIMARY KEY ASC AUTOINCREMENT,

    /* The ID of the keyframe that this element belongs to */
    KeyFrameId          INTEGER NOT NULL REFERENCES Flo_LayerKeyFrame(KeyFrameId),

    /* Type of this element */
    VectorElementType   INTEGER NOT NULL,

    /* When this element is added to the animation relative to when the keyframe started */
    AtTime              INTEGER NOT NULL
);

CREATE INDEX FloIdx_ElementTime ON Flo_VectorElement (KeyFrameId, AtTime, ElementId);

/* Deleting a key frame also deletes any vector elements it contains */
CREATE TRIGGER Flo_Delete_KeyFrame_Elements BEFORE DELETE ON Flo_LayerKeyFrame
    BEGIN
        DELETE FROM Flo_VectorElement WHERE KeyFrameId = Old.KeyFrameId;
    END;

/*
 * Vector brush element
 */
CREATE TABLE Flo_BrushElement (
    ElementId       INTEGER NOT NULL PRIMARY KEY REFERENCES Flo_VectorElement(ElementId),
    Brush           INTEGER NOT NULL REFERENCES Flo_Brush_Type(Brush),
    DrawingStyle    INTEGER NOT NULL
) WITHOUT ROWID;

/* Deleting a vector element deletes the attached brush elements */
CREATE TRIGGER Flo_Delete_BrushElements BEFORE DELETE ON Flo_VectorElement
    BEGIN
        DELETE FROM Flo_BrushElement WHERE ElementId = Old.ElementId;
    END;

/*
 * Vector brush properties element
 */
CREATE TABLE Flo_BrushPropertiesElement (
    ElementId           INTEGER NOT NULL PRIMARY KEY REFERENCES Flo_VectorElement(ElementId),
    BrushProperties     INTEGER NOT NULL REFERENCES Flo_BrushProperties(BrushProperties)
) WITHOUT ROWID;

/* Deleting a vector element deletes the attached brush properties elements */
CREATE TRIGGER Flo_Delete_BrushPropertiesElements BEFORE DELETE ON Flo_VectorElement
    BEGIN
        DELETE FROM Flo_BrushPropertiesElement WHERE ElementId = Old.ElementId;
    END;

/*
 * Brush point associated with an element
 */
CREATE TABLE Flo_BrushPoint (
    ElementId           INTEGER NOT NULL REFERENCES Flo_VectorElement(ElementId),
    PointId             INTEGER NOT NULL,

    X1                  REAL NOT NULL,
    Y1                  REAL NOT NULL,
    X2                  REAL NOT NULL,
    Y2                  REAL NOT NULL,
    X3                  REAL NOT NULL,
    Y3                  REAL NOT NULL,
    Width               REAL NOT NULL,

    PRIMARY KEY (ElementId, PointId)
) WITHOUT ROWID;

/* Deleting a vector element deletes the points elements */
CREATE TRIGGER Flo_Delete_BrushPoints BEFORE DELETE ON Flo_VectorElement
    BEGIN
        DELETE FROM Flo_BrushPoint WHERE ElementId = Old.ElementId;
    END;

/*
 *  ===== Enumeration descriptions
 */

/*
 * Provides information about what enumerable values mean
 */
CREATE TABLE Flo_EnumerationDescriptions (
    /* Name of the field containing an enumeration */
    FieldName   TEXT NOT NULL,

    /* Value of the field */
    Value       INTEGER NOT NULL,

    /* Name of this enumerable value as used in the API */
    ApiName     TEXT NOT NULL,

    /* Comment */
    Comment     TEXT,

    PRIMARY KEY (FieldName, ApiName)
);

/* The stable/unstable enumeration */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("Stable", 0,   "Unstable", "File was created with a development version of FlowBetween and may not conform to the final schema for this version"),
            ("Stable", 1,   "Stable",   "File was created using a stable version of the schema");

/* The possible types of edit */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("Edit", 0, "SetSize",                          "Sets the size of the animation"),
            ("Edit", 1, "AddNewLayer",                      "Adds a new layer with a particular ID to the animation"),
            ("Edit", 2, "RemoveLayer",                      "Removes a layer from the animation"),
            ("Edit", 3, "Layer::AddKeyFrame",               "Adds a new keyframe to a layer"),
            ("Edit", 4, "Layer::RemoveKeyFrame",            "Removes a keyframe from a layer"),
            ("Edit", 5, "Layer::Paint::SelectBrush",        "Selects a particular brush definition"),
            ("Edit", 6, "Layer::Paint::BrushProperties",    "Sets the brush properties"),
            ("Edit", 7, "Layer::Paint::BrushStroke",        "Represents a brush stroke with the specified raw properties");

/* Brush drawing style */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("DrawingStyle", 0, "Draw",     "Brush should draw onto the canvas"),
            ("DrawingStyle", 1, "Erase",    "Brush should erase from the current layer");

/* Types of brush definition */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("BrushType", 0,    "Simple",   "Simple brush (mainly used for testing)"),
            ("BrushType", 1,    "Ink",      "Ink brush");

/* Types of colour definition */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("ColorType", 0,    "RGB",      "RGB colour"),
            ("ColorType", 1,    "HSLUV",    "HSLuv colour");

/* Types of layer */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("LayerType", 0,    "Vector",   "Vector layer");

/* Types of vector element */
INSERT INTO Flo_EnumerationDescriptions(FieldName, Value, ApiName, Comment)
     VALUES ("VectorElementType",   0,  "BrushDefinition",  "Selects a brush for future vector elements"),
            ("VectorElementType",   1,  "BrushProperties",  "Sets brush properties for future vector elements"),
            ("VectorElementType",   2,  "BrushStroke",      "Brush stroke element");            

/*
 *  ===== DEFAULT DATA
 */

INSERT INTO FlowBetween(DataVersion, FloVersion, Stable)
     VALUES (1, "Unknown", 0);