/*
 * Zed Attack Proxy (ZAP) and its related class files.
 *
 * ZAP is an HTTP/HTTPS proxy for assessing web application security.
 *
 * Copyright 2014 The ZAP Development Team
 *
 * Licensed 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.
 */
package org.zaproxy.zap.extension.plugnhack.db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import org.parosproxy.paros.db.DatabaseException;
import org.parosproxy.paros.db.DbUtils;
import org.parosproxy.paros.db.paros.ParosAbstractTable;
import org.zaproxy.zap.extension.plugnhack.MonitoredPage;

public class ClientTable extends ParosAbstractTable {

    private static final String TABLE = "PLUGNHACK_CLIENT";

    private PreparedStatement psInsert;
    private PreparedStatement psUpdate;
    private PreparedStatement psDelete;

    private PreparedStatement psGetAllData = null;

    private CallableStatement psGetIdLastInsert = null;

    @Override
    protected void reconnect(Connection conn) throws DatabaseException {
        try {
            if (!DbUtils.hasTable(conn, TABLE)) {
                // need to create the tables
                DbUtils.execute(
                        conn,
                        "CREATE CACHED TABLE "
                                + TABLE
                                + " ("
                                + "id BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1), "
                                + "client_id VARCHAR(255) NOT NULL,"
                                + "history_id INTEGER NULL,"
                                + "FOREIGN KEY (history_id) REFERENCES HISTORY(HISTORYID) ON DELETE SET NULL ON UPDATE SET NULL"
                                + ")");
            }

            psInsert =
                    conn.prepareStatement(
                            "INSERT INTO " + TABLE + " (client_id, history_id) VALUES (?,?)");

            psUpdate =
                    conn.prepareStatement("UPDATE " + TABLE + " SET history_id = ? WHERE id = ?");

            psDelete = conn.prepareStatement("DELETE FROM " + TABLE + " WHERE id = ?");

            psGetAllData = conn.prepareStatement("SELECT * FROM " + TABLE);

            psGetIdLastInsert = conn.prepareCall("CALL IDENTITY();");
        } catch (SQLException e) {
            throw new DatabaseException(e);
        }
    }

    public synchronized void insert(MonitoredPage client) throws SQLException {
        psInsert.setString(1, client.getId());
        if (client.getHistoryReference() != null) {
            psInsert.setInt(2, client.getHistoryReference().getHistoryId());
        } else {
            psInsert.setNull(2, Types.INTEGER);
        }
        psInsert.executeUpdate();

        ResultSet rs = psGetIdLastInsert.executeQuery();
        rs.next();
        client.setIndex(rs.getLong(1));
        rs.close();
    }

    public synchronized void update(MonitoredPage client) throws SQLException {
        if (client.getHistoryReference() != null) {
            psUpdate.setInt(1, client.getHistoryReference().getHistoryId());
            client.setHrefPersisted(true);
        } else {
            psUpdate.setNull(1, Types.INTEGER);
        }
        psUpdate.setLong(2, client.getIndex());
        psUpdate.executeUpdate();
    }

    public synchronized void delete(MonitoredPage client) throws SQLException {
        psDelete.setLong(1, client.getIndex());
        psDelete.executeUpdate();
    }

    public synchronized List<MonitoredPage> list() throws SQLException {
        ArrayList<MonitoredPage> list = new ArrayList<>();

        ResultSet rs = psGetAllData.executeQuery();
        while (rs.next()) {
            list.add(
                    new MonitoredPage(
                            rs.getLong("id"), rs.getString("client_id"), rs.getInt("history_id")));
        }
        rs.close();

        return list;
    }
}
