SQL(
    PRAGMA foreign_keys = ON;
    BEGIN TRANSACTION;
)

CREATE_TABLE(AcePolicyResult)
    COLUMN_NOT_NULL(decision,       INTEGER,    check(decision between 0 and 6))
    COLUMN_NOT_NULL(hash,           TEXT,)
    COLUMN_NOT_NULL(rule_id,        INTEGER)
    TABLE_CONSTRAINTS(
        PRIMARY KEY(hash)
    )
CREATE_TABLE_END()

CREATE_TABLE(AcePromptDecision)
    COLUMN_NOT_NULL(app_id,         INTEGER,)
    COLUMN_NOT_NULL(decision,       INTEGER,   check(decision between 0 and 5))
    COLUMN(session,                 TEXT,)
    COLUMN_NOT_NULL(rule_id,        INTEGER,)
    TABLE_CONSTRAINTS(
        PRIMARY KEY(app_id,rule_id)
    )
CREATE_TABLE_END()

CREATE_TABLE(AceAttribute)
    COLUMN_NOT_NULL(attr_id,        INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(name,           TEXT,)
    COLUMN_NOT_NULL(type,           INTEGER,    check(type between 0 and 4))

    TABLE_CONSTRAINTS(unique(name,type))
CREATE_TABLE_END()

CREATE_TABLE(AceSubject)
    COLUMN_NOT_NULL(subject_id,     INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(id_uri,         TEXT,       unique)
CREATE_TABLE_END()

CREATE_TABLE(AceDevCap)
    COLUMN_NOT_NULL(resource_id,    INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(id_uri,         TEXT,       unique)
    COLUMN_NOT_NULL(general_setting,INTEGER,    check(general_setting between -1 and 4))
CREATE_TABLE_END()

CREATE_TABLE(AceWidgetDevCapSetting)
    COLUMN_NOT_NULL(app_id,         INTEGER,    not null)
    COLUMN_NOT_NULL(resource_id,    INTEGER,    references AceDevCap(resource_id))
    COLUMN_NOT_NULL(access_value,   INTEGER,    check(access_value between -1 and 4))

    TABLE_CONSTRAINTS(unique(app_id,resource_id))
CREATE_TABLE_END()

CREATE_TABLE(AceRequestedDevCaps)
    COLUMN_NOT_NULL(app_id,        INTEGER,  not null)
    COLUMN_NOT_NULL(grant_smack,   INTEGER,  not null)
    COLUMN_NOT_NULL(dev_cap,       TEXT,)

    TABLE_CONSTRAINTS(unique(app_id,dev_cap))
CREATE_TABLE_END()

CREATE_TABLE(AceAcceptedFeature)
    COLUMN_NOT_NULL(app_id,        INTEGER,  not null)
    COLUMN_NOT_NULL(feature,       TEXT,     not null)

    TABLE_CONSTRAINTS(unique(app_id,feature))
CREATE_TABLE_END()

CREATE_TABLE(WidgetInfo)
    COLUMN_NOT_NULL(app_id,         INTEGER,      PRIMARY KEY)
    COLUMN(widget_type,             INT,          DEFAULT 1)
    COLUMN(widget_id,               VARCHAR(256), DEFAULT '')
    COLUMN(widget_version,          VARCHAR(256), DEFAULT '')
    COLUMN(author_name,             VARCHAR(256), DEFAULT '')
    COLUMN(share_href,              VARCHAR(256), DEFAULT '')
CREATE_TABLE_END()

CREATE_TABLE(WidgetCertificateFingerprint)
    COLUMN_NOT_NULL(app_id,     INT,)
    COLUMN_NOT_NULL(owner,      INT,)
    COLUMN_NOT_NULL(chainid,    INT,)
    COLUMN_NOT_NULL(type,       INT,)
    COLUMN(md5_fingerprint,     VARCHAR(64),)
    COLUMN(sha1_fingerprint,    VARCHAR(64),)
    COLUMN(common_name,         VARCHAR(64),)
    TABLE_CONSTRAINTS(
        PRIMARY KEY (app_id, chainid, owner, type)
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

SQL(
    COMMIT;
)
