survey/migrations/20230701082846_survey_mcaptcha_upload.sql

-- SPDX-FileCopyrightText: 2023 Aravinth Manivannan <realaravinth@batsense.net>
--
-- SPDX-License-Identifier: AGPL-3.0-or-later

CREATE TABLE IF NOT EXISTS survey_mcaptcha_hostname (
	url VARCHAR(3000) UNIQUE NOT NULL,
	secret VARCHAR(100) UNIQUE NOT NULL,
	ID SERIAL PRIMARY KEY NOT NULL
);

CREATE TABLE IF NOT EXISTS survey_mcaptcha_campaign (
	campaign_id VARCHAR(100) NOT NULL,
	public_id VARCHAR(100) NOT NULL,
	url_id INTEGER NOT NULL references survey_mcaptcha_hostname(ID) ON DELETE CASCADE,
	synced_till INTEGER NOT NULL DEFAULT 0,
	ID SERIAL PRIMARY KEY NOT NULL
);


CREATE TABLE IF NOT EXISTS survey_mcaptcha_analytics (
	campaign_id INTEGER references survey_mcaptcha_campaign(ID)  ON DELETE CASCADE,
	time INTEGER NOT NULL,
	difficulty_factor INTEGER NOT NULL,
	worker_type VARCHAR(100) NOT NULL,
	ID SERIAL PRIMARY KEY NOT NULL
);


CREATE TABLE IF NOT EXISTS survey_mcaptcha_upload_job_states (
    name VARCHAR(20) NOT NULL UNIQUE,
    ID SERIAL PRIMARY KEY NOT NULL
);

CREATE TABLE IF NOT EXISTS survey_mcaptcha_upload_jobs (
	campaign_id INTEGER references survey_mcaptcha_campaign(ID)  ON DELETE CASCADE,
    public_id varchar(100) NOT NULL UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    scheduled_at timestamptz DEFAULT NULL,
    finished_at timestamptz DEFAULT NULL,
    job_state  INTEGER references survey_mcaptcha_upload_job_states(ID) ON DELETE CASCADE,
	ID SERIAL PRIMARY KEY NOT NULL
);