--
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements.  See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License.  You may obtain a copy of the License at
--
--      http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

DROP TABLE CAMEL
CREATE TABLE camel (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, species VARCHAR(50) NOT NULL,PRIMARY KEY (id))

-- for consumer
DROP TABLE PROJECTSVIACLASSPATH
CREATE TABLE projectsViaClasspath (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, project VARCHAR(25), license VARCHAR(25), processed SMALLINT,PRIMARY KEY (id))
DROP TABLE PROJECTSVIASQL
CREATE TABLE projectsViaSql (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, project VARCHAR(25), license VARCHAR(25), processed SMALLINT,PRIMARY KEY (id))
DROP TABLE PROJECTSVIAFILE
CREATE TABLE projectsViaFile (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, project VARCHAR(25), license VARCHAR(25), processed SMALLINT,PRIMARY KEY (id))

-- idempotent repo
DROP TABLE CAMEL_MESSAGEPROCESSED
CREATE TABLE CAMEL_MESSAGEPROCESSED (processorName VARCHAR(255), messageId VARCHAR(100), createdAt TIMESTAMP)

-- aggregation repo
DROP TABLE AGGREGATION
CREATE TABLE aggregation (id VARCHAR(255) NOT NULL, exchange BLOB, version BIGINT)
DROP TABLE AGGREGATION_COMPLETED
CREATE TABLE aggregation_completed (id VARCHAR(255) NOT NULL, exchange BLOB, version BIGINT)

-- stored procedure

DROP TABLE ADD_NUMS_RESULTS
CREATE TABLE ADD_NUMS_RESULTS (id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL, value INT NOT NULL)

DROP PROCEDURE ADD_NUMS
CREATE PROCEDURE ADD_NUMS (IN A INT, IN B INT) LANGUAGE SQL P1: BEGIN insert into ADD_NUMS_RESULTS (id, value) VALUES (1, A + B); END P1