SQL(
    PRAGMA foreign_keys = ON;
    BEGIN TRANSACTION;
)

CREATE_TABLE(WidgetInfo)
    COLUMN_NOT_NULL(app_id,         INTEGER, PRIMARY KEY AUTOINCREMENT)
    COLUMN(widget_type,             INT,     DEFAULT 1)
    COLUMN(widget_id,               TEXT,    DEFAULT '')
    COLUMN(widget_version,          TEXT,    DEFAULT '')
    COLUMN(widget_width,            INT,     DEFAULT 0)
    COLUMN(widget_height,           INT,     DEFAULT 0)
    COLUMN(author_name,             TEXT,    DEFAULT '')
    COLUMN(author_email,            TEXT,    DEFAULT '')
    COLUMN(author_href,             TEXT,    DEFAULT '')
    COLUMN(csp_policy,              TEXT,    DEFAULT '')
    COLUMN(csp_policy_report_only,  TEXT,    DEFAULT '')
    COLUMN(min_version,             TEXT,    DEFAULT '1.0')
    COLUMN_NOT_NULL(back_supported, TINYINT, DEFAULT 0)
    COLUMN(defaultlocale,           TEXT,    DEFAULT 0)
    COLUMN_NOT_NULL(tizen_pkgid,    TEXT,    DEFAULT '')
    COLUMN_NOT_NULL(tizen_appid,    TEXT,    DEFAULT 0 UNIQUE)
    COLUMN(pkg_type,                INT,     DEFAULT 0)
    COLUMN(security_model_version,  INT,     DEFAULT 0)
CREATE_TABLE_END()

SQL(
    CREATE INDEX IF NOT EXISTS WidgetInfo_AppidIndex ON WidgetInfo(tizen_appid);
)

CREATE_TABLE(WidgetCertificate)
    COLUMN_NOT_NULL(app_id,                 INT,)
    COLUMN_NOT_NULL(cert_source,            INT,    CHECK(cert_source between 0 and 2))
    COLUMN_NOT_NULL(encoded_chain,          VARCHAR(16000),)
    TABLE_CONSTRAINTS(
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetWindowModes)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(window_mode,    VARCHAR(256),)
    TABLE_CONSTRAINTS(
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(LocalizedWidgetInfo)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(widget_locale,  TEXT,)
    COLUMN(widget_name,             TEXT,)
    COLUMN(widget_shortname,        TEXT,)
    COLUMN(widget_description,      TEXT,)
    COLUMN(widget_license,          TEXT,)
    COLUMN(widget_license_file,     TEXT,)
    COLUMN(widget_license_href,     TEXT,)

    TABLE_CONSTRAINTS(
        PRIMARY KEY (app_id, widget_locale),
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetExtendedInfo)
    COLUMN_NOT_NULL(app_id,     INTEGER,        PRIMARY KEY)
    COLUMN(install_time,        BIGINT,         DEFAULT 0)
    COLUMN(updated,             INT,            DEFAULT 0)
    COLUMN(splash_img_src,      TEXT,           DEFAULT '')
    COLUMN(background_page,     TEXT,           DEFAULT '')
    COLUMN(installed_path,      TEXT,           DEFAULT '')
    TABLE_CONSTRAINTS(
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetPreference)
    COLUMN_NOT_NULL(app_id,     INTEGER,)
    COLUMN_NOT_NULL(tizen_appid,    TEXT,           DEFAULT 0)
    COLUMN_NOT_NULL(key_name,       TEXT,)
    COLUMN(key_value,               TEXT,           DEFAULT '')
    COLUMN(readonly,                INT,            DEFAULT 0)

    TABLE_CONSTRAINTS(
        PRIMARY KEY(app_id, key_name),
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetFeature)
    COLUMN_NOT_NULL(widget_feature_id,  INTEGER,        primary key autoincrement)
    COLUMN_NOT_NULL(app_id,             INT,)
    COLUMN_NOT_NULL(name,               TEXT,)
    COLUMN_NOT_NULL(rejected,           INT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetPrivilege)
    COLUMN_NOT_NULL(widget_privilege_id, INTEGER,        primary key autoincrement)
    COLUMN_NOT_NULL(app_id,              INT,)
    COLUMN_NOT_NULL(name,                TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetIcon)
    COLUMN_NOT_NULL(icon_id,        INTEGER,   primary key autoincrement)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(icon_src,       TEXT,)
    COLUMN(icon_width,              INT,            DEFAULT 0)
    COLUMN(icon_height,             INT,            DEFAULT 0)
    TABLE_CONSTRAINTS(
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetLocalizedIcon)
    COLUMN_NOT_NULL(app_id,         INT,)   /* TODO key duplicated for efficiency - ORM doesn't support JOIN */
    COLUMN_NOT_NULL(icon_id,        INTEGER,)
    COLUMN_NOT_NULL(widget_locale,  TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY(icon_id) REFERENCES WidgetIcon (icon_id) ON DELETE CASCADE,
        PRIMARY KEY(icon_id, widget_locale)
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetStartFile)
    COLUMN_NOT_NULL(start_file_id,  INTEGER,   primary key autoincrement)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(src,            TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetLocalizedStartFile)
    COLUMN_NOT_NULL(app_id,         INT,)   /* TODO key duplicated for efficiency - ORM doesn't support JOIN */
    COLUMN_NOT_NULL(start_file_id,  INTEGER,)
    COLUMN_NOT_NULL(widget_locale,  TEXT,)
    COLUMN_NOT_NULL(type,           TEXT,)
    COLUMN_NOT_NULL(encoding,       TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY(start_file_id) REFERENCES WidgetStartFile (start_file_id) ON DELETE CASCADE,
        PRIMARY KEY(start_file_id, widget_locale)
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetExternalLocations)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(path,  TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE,
        PRIMARY KEY(app_id, path)
    )
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,     TEXT,)
    COLUMN(sha1_fingerprint,    TEXT,)
    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()

CREATE_TABLE(WidgetWARPInfo)
    COLUMN_NOT_NULL(app_id,     INT,)
    COLUMN_NOT_NULL(iri,        TEXT,)
    COLUMN(subdomain_access,    INT,        CHECK(subdomain_access between 0 and 1))

    TABLE_CONSTRAINTS(
        PRIMARY KEY(app_id, iri)
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(WidgetAllowNavigation)
    COLUMN_NOT_NULL(app_id,     INT,)
    COLUMN_NOT_NULL(scheme,     TEXT,)
    COLUMN_NOT_NULL(host,       TEXT,)

    TABLE_CONSTRAINTS(
        PRIMARY KEY(app_id, scheme, host)
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(FeaturesList)
    COLUMN_NOT_NULL(FeatureUUID,            INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(FeatureName,            TEXT,       unique)
    COLUMN_NOT_NULL(PluginPropertiesId,     INT,)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (PluginPropertiesId) REFERENCES PluginProperties (PluginPropertiesId) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(PluginProperties)
    COLUMN_NOT_NULL(PluginPropertiesId,     INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(InstallationState,      INTEGER,    DEFAULT 0)
    COLUMN_NOT_NULL(PluginLibraryName,      TEXT,       unique)
    COLUMN_NOT_NULL(LibraryTimestamp,       INT,)
    COLUMN(PluginLibraryPath,               TEXT,)
CREATE_TABLE_END()

CREATE_TABLE(PluginDependencies)
    COLUMN_NOT_NULL(PluginPropertiesId,              INTEGER,    not null)
    COLUMN_NOT_NULL(RequiredPluginPropertiesId,      INTEGER,    not null)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (PluginPropertiesId) REFERENCES PluginProperties (PluginPropertiesId) ON DELETE CASCADE
        FOREIGN KEY (RequiredPluginPropertiesId) REFERENCES PluginProperties (PluginPropertiesId) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(PluginImplementedObjects)
    COLUMN_NOT_NULL(PluginObject,           TEXT,       unique)
    COLUMN_NOT_NULL(PluginPropertiesId,     INTEGER,    not null)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (PluginPropertiesId) REFERENCES PluginProperties (PluginPropertiesId) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(PluginRequiredObjects)
    COLUMN_NOT_NULL(PluginPropertiesId,     INTEGER,    not null)
    COLUMN_NOT_NULL(PluginObject,           TEXT,       not null)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (PluginPropertiesId) REFERENCES PluginProperties (PluginPropertiesId) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(DeviceCapabilities)
    COLUMN_NOT_NULL(DeviceCapID,            INTEGER,    primary key autoincrement)
    COLUMN_NOT_NULL(DeviceCapName,          TEXT,       unique)
    COLUMN(DeviceCapDefaultValue,           INT,)
CREATE_TABLE_END()

CREATE_TABLE(FeatureDeviceCapProxy)
    COLUMN_NOT_NULL(FeatureUUID,            INT,        not null)
    COLUMN_NOT_NULL(DeviceCapID,            INT,        not null)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (FeatureUUID) REFERENCES FeaturesList (FeatureUUID) ON DELETE CASCADE
        FOREIGN KEY (DeviceCapID) REFERENCES DeviceCapabilities (DeviceCapID) ON DELETE CASCADE
        PRIMARY KEY(FeatureUUID,DeviceCapID)
    )
CREATE_TABLE_END()

CREATE_TABLE(SettingsList)
    COLUMN_NOT_NULL(appId,          INT,)
    COLUMN_NOT_NULL(settingName,    TEXT,)
    COLUMN_NOT_NULL(settingValue,   TEXT,)
    TABLE_CONSTRAINTS(
        FOREIGN KEY (appId) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(AppControlInfo)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(execute_index,  INT,)
    COLUMN_NOT_NULL(src,            TEXT,)
    COLUMN_NOT_NULL(operation,      TEXT,)
    COLUMN_NOT_NULL(uri,            TEXT,)
    COLUMN_NOT_NULL(mime,           TEXT,)
    COLUMN_NOT_NULL(disposition,    TINYINT, DEFAULT 0)

    TABLE_CONSTRAINTS(
        PRIMARY KEY(app_id, operation, uri, mime)
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(EncryptedResourceList)
    COLUMN_NOT_NULL(app_id,         INT,)
    COLUMN_NOT_NULL(resource,       TEXT,)
    COLUMN_NOT_NULL(size,           INT,)

    TABLE_CONSTRAINTS(
        FOREIGN KEY (app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

CREATE_TABLE(LiveBoxInfo)
    COLUMN_NOT_NULL(app_id,     INT,)
    COLUMN(livebox_id,          TEXT,           DEFAULT '')
    TABLE_CONSTRAINTS(
        FOREIGN KEY(app_id) REFERENCES WidgetInfo (app_id) ON DELETE CASCADE
    )
CREATE_TABLE_END()

SQL(
    COMMIT;
)
