import java.sql.*;
import java.util.List;
import java.util.ArrayList;

public class DerbyTest {
    public static void main(String[] args) throws Exception {
        Class.forName("org.apache.derby.jdbc.ClientDriver");
        Connection con = DriverManager.getConnection("jdbc:derby://172.31.128.158:1528/offlinedb2;create=true", "APP", "derby");

        Statement st = con.createStatement();

        try {
            st.execute("DROP TABLE RECEIPT");
        } catch (Exception ex) {
        }

        st.execute("\n" +
                "create table RECEIPT(receipt_pk int generated by default as identity PRIMARY KEY NOT NULL, \n" +
                "\t\t     hoVenueId varchar(100),\n" +
                "\t\t     openedHoLcUnitId varchar(100) NOT NULL,\n" +
                "\t\t     closedHoLcUnitId varchar(100),\n" +
                "\t\t     receiptReference varchar(100) CONSTRAINT RECEIPT_REC_REF UNIQUE NOT NULL,\n" +
                "\t\t     receiptBarCode varchar(100),\n" +
                "\t\t     isoCurrency varchar(20) NOT NULL,\n" +
                "\t\t     value int NOT NULL,\n" +
                "\t\t     created DATE NOT NULL,\n" +
                "\t\t     closed DATE, \n" +
                "\t\t     runTimeState int NOT NULL,\n" +
                "\t\t     actionState varchar(100) NOT NULL,\n" +
                "\t\t     cashier smallint NOT NULL)");
        st.close();

        dropUsingMetadata(con);
        //dropUsingSysconstraints(con);

        con.close();
    }

    private static void dropUsingMetadata(Connection con) throws Exception {
        DatabaseMetaData dbm = con.getMetaData();
        Statement st = con.createStatement();

        ResultSet rs = dbm.getIndexInfo(null, null, "RECEIPT", false, false);

        while (rs.next()) {

            if (rs.getInt("TYPE") == DatabaseMetaData.tableIndexStatistic) continue;

            System.out.println("Table: " + rs.getString(3) + " Index: " + rs.getString("INDEX_NAME"));

            try {
                st.executeUpdate("alter table " + rs.getString(3) + " drop constraint "
                        + rs.getString("INDEX_NAME"));
            } catch (Exception ex) {
                System.out.println(ex);
            }
        }
    }

    private static void dropUsingSysconstraints(Connection con) throws Exception {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT C.CONSTRAINTNAME FROM SYS.SYSCONSTRAINTS C, SYS.SYSTABLES T WHERE C.TABLEID=T.TABLEID AND T.TABLENAME='RECEIPT'");

        List<String> constraints = new ArrayList<String>();

        while (rs.next()) {
            constraints.add(rs.getString(1));
        }

        rs.close();

        for (String constraint : constraints) {
            System.out.println(constraint);
            st.execute("ALTER TABLE RECEIPT DROP CONSTRAINT " + constraint);
        }
    }
}