-- Drop existing tables with CASCADE to handle any dependencies
DROP TABLE IF EXISTS "gow_corp_ref" CASCADE;
DROP TABLE IF EXISTS "gow_data" CASCADE;
DROP TABLE IF EXISTS "gow_fac_id" CASCADE;

-- Recreate gow_fac_id table
CREATE TABLE "gow_fac_id" (
    id                 SERIAL PRIMARY KEY,
    "importId"         INTEGER NOT NULL REFERENCES imports ON UPDATE CASCADE ON DELETE CASCADE,
    "corp_id" VARCHAR(4000),
    "odeq_fac_id" VARCHAR(4000),
    "odeq_facility_name" VARCHAR(4000),
    "corp_id_2" VARCHAR(4000),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL
);

-- Recreate gow_data table
CREATE TABLE "gow_data" (
    id                 SERIAL PRIMARY KEY,
    "importId"         INTEGER NOT NULL REFERENCES imports ON UPDATE CASCADE ON DELETE CASCADE,
    "well_name" VARCHAR(4000),
    "month" VARCHAR(4000),
    "corpId" VARCHAR(4000),
    "completion_type" VARCHAR(4000),
    "state" VARCHAR(4000),
    "days_on" VARCHAR(4000),
    "gas_production" VARCHAR(4000),
    "gas_sales" VARCHAR(4000),
    "oil_production" VARCHAR(4000),
    "oil_sales" VARCHAR(4000),
    "water_production" VARCHAR(4000),
    "operator" VARCHAR(4000),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL
);

-- Recreate gow_corp_ref table
CREATE TABLE "gow_corp_ref" (
    id                 SERIAL PRIMARY KEY,
    "importId"         INTEGER NOT NULL REFERENCES imports ON UPDATE CASCADE ON DELETE CASCADE,
    "well_name" VARCHAR(4000),
    "api_no" VARCHAR(4000),
    "corporate_id" VARCHAR(4000),
    "current_well_status" VARCHAR(4000),
    "county_parish" VARCHAR(4000),
    "state" VARCHAR(4000),
    "area" VARCHAR(4000),
    "completion_date" VARCHAR(4000),
    "pop_first_production" VARCHAR(4000),
    "operator" VARCHAR(4000),
    "abandon_date" VARCHAR(4000),
    "battery" VARCHAR(4000),
    "common_pad_name" VARCHAR(4000),
    "id_well" VARCHAR(4000),
    "lease_id" VARCHAR(4000),
    "latitude" VARCHAR(4000),
    "longitude" VARCHAR(4000),
    "pad_code" VARCHAR(4000),
    "pad_name" VARCHAR(4000),
    "permit_number" VARCHAR(4000),
    "well_sub_status" VARCHAR(4000),
    "shut_in_date" VARCHAR(4000),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL
);