--
--
-- GEOLYNX BASELINE SCHEMA as of 2026-07-06.
-- Includes migrations db/archive/000-005 (do not re-apply those).
-- Fresh install: restore this dump, then run `php db/migrate.php --apply`
-- to bring the database up to date (applies db/006 onward).
-- See docs/database-migrations.md. Supersedes sql/geolynx_ddl.sql.
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: accounts; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA accounts;


--
-- Name: adhoc; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA adhoc;


--
-- Name: basedata; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA basedata;


--
-- Name: competitor_data; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA competitor_data;


--
-- Name: inventory; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA inventory;


--
-- Name: landregistry; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA landregistry;


--
-- Name: openreach; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA openreach;


--
-- Name: ordnancesurvey; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA ordnancesurvey;


--
-- Name: projects; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA projects;


--
-- Name: prospector; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA prospector;


--
-- Name: public; Type: SCHEMA; Schema: -; Owner: -
--

-- *not* creating schema, since initdb creates it


--
-- Name: reports; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA reports;


--
-- Name: stocklists; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA stocklists;


--
-- Name: strategy; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA strategy;


--
-- Name: tasks; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA tasks;


--
-- Name: temp; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA temp;


--
-- Name: users; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA users;


--
-- Name: wayleave; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA wayleave;


--
-- Name: btree_gin; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gin WITH SCHEMA adhoc;


--
-- Name: EXTENSION btree_gin; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION btree_gin IS 'support for indexing common datatypes in GIN';


--
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA adhoc;


--
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST';


--
-- Name: dblink; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS dblink WITH SCHEMA public;


--
-- Name: EXTENSION dblink; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION dblink IS 'connect to other PostgreSQL databases from within a database';


--
-- Name: dict_int; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS dict_int WITH SCHEMA adhoc;


--
-- Name: EXTENSION dict_int; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION dict_int IS 'text search dictionary template for integers';


--
-- Name: dict_xsyn; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS dict_xsyn WITH SCHEMA adhoc;


--
-- Name: EXTENSION dict_xsyn; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION dict_xsyn IS 'text search dictionary template for extended synonym processing';


--
-- Name: fuzzystrmatch; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch WITH SCHEMA public;


--
-- Name: EXTENSION fuzzystrmatch; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION fuzzystrmatch IS 'determine similarities and distance between strings';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: pgcrypto; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA adhoc;


--
-- Name: EXTENSION pgcrypto; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';


--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';


--
-- Name: pgrouting; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pgrouting WITH SCHEMA public;


--
-- Name: EXTENSION pgrouting; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION pgrouting IS 'pgRouting Extension';


--
-- Name: postgres_fdw; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA public;


--
-- Name: EXTENSION postgres_fdw; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION postgres_fdw IS 'foreign-data wrapper for remote PostgreSQL servers';


--
-- Name: unaccent; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA adhoc;


--
-- Name: EXTENSION unaccent; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON EXTENSION unaccent IS 'text search dictionary that removes accents';


--
-- Name: accounts_update_modified_fields(); Type: FUNCTION; Schema: accounts; Owner: -
--

CREATE FUNCTION accounts.accounts_update_modified_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.modified_datetime = now();
    --NEW.modified_user = current_user_id();  -- Replace this with your logic to get the current user ID
    RETURN NEW;
END;
$$;


--
-- Name: projects_update_modified_fields(); Type: FUNCTION; Schema: projects; Owner: -
--

CREATE FUNCTION projects.projects_update_modified_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.modified_datetime = now();
    --NEW.modified_user = current_user_id();  -- Replace this with your logic to get the current user ID
    RETURN NEW;
END;
$$;


--
-- Name: calculate_cable_length(); Type: FUNCTION; Schema: prospector; Owner: -
--

CREATE FUNCTION prospector.calculate_cable_length() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Calculate cable_length from the geometry
    IF NEW.geom_user IS NOT NULL THEN
        NEW.cable_length := st_length(st_transform(NEW.geom_user, 27700));
    END IF;
    
    RETURN NEW;
END;
$$;


--
-- Name: network_cables_history_trigger(); Type: FUNCTION; Schema: prospector; Owner: -
--

CREATE FUNCTION prospector.network_cables_history_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    next_revision int;
BEGIN
    -- Get the next revision number for this cable_id
    SELECT COALESCE(MAX(revision_id) + 1, 1) INTO next_revision
    FROM prospector.network_cables_history
    WHERE cable_id = COALESCE(NEW.cable_id, OLD.cable_id);
    
    -- For insert operation
    IF TG_OP = 'INSERT' THEN
        INSERT INTO prospector.network_cables_history (
            cable_id, project_id, cable_type, cable_length, a_end, b_end,
            geom_user, geom, cable_datetime, cable_user, cable_ref, 
            pianoi_ref, cable_status, revision_operation, revision_id, customer_cable_id)
        VALUES (
            NEW.cable_id, NEW.project_id, NEW.cable_type, NEW.cable_length,
            NEW.a_end, NEW.b_end, NEW.geom_user, NEW.geom, NEW.cable_datetime,
            NEW.cable_user, NEW.cable_ref, NEW.pianoi_ref, NEW.cable_status,
            'I', next_revision, NEW.customer_cable_id);
    
    -- For update operation  
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO prospector.network_cables_history (
            cable_id, project_id, cable_type, cable_length, a_end, b_end,
            geom_user, geom, cable_datetime, cable_user, cable_ref, 
            pianoi_ref, cable_status, revision_operation, revision_id,
			customer_cable_id
)
        VALUES (
            NEW.cable_id, NEW.project_id, NEW.cable_type, NEW.cable_length,
            NEW.a_end, NEW.b_end, NEW.geom_user, NEW.geom, NEW.cable_datetime,
            NEW.cable_user, NEW.cable_ref, NEW.pianoi_ref, NEW.cable_status,
            'U', next_revision, NEW.customer_cable_id);
    
    -- For delete operation
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO prospector.network_cables_history (
            cable_id, project_id, cable_type, cable_length, a_end, b_end,
            geom_user, geom, cable_datetime, cable_user, cable_ref, 
            pianoi_ref, cable_status, revision_operation, revision_id, customer_cable_id)
        VALUES (
            OLD.cable_id, OLD.project_id, OLD.cable_type, OLD.cable_length,
            OLD.a_end, OLD.b_end, OLD.geom_user, OLD.geom, OLD.cable_datetime,
            OLD.cable_user, OLD.cable_ref, OLD.pianoi_ref, OLD.cable_status,
            'D', next_revision, OLD.customer_cable_id);
    END IF;
    
    RETURN NULL;
END;
$$;


--
-- Name: address_to_words(text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.address_to_words(text) RETURNS text[]
    LANGUAGE sql IMMUTABLE
    AS $_$
    SELECT array_agg(DISTINCT UPPER(word))
    FROM regexp_split_to_table($1, '[^a-zA-Z]+') AS word
    WHERE length(word) >= 3
$_$;


--
-- Name: escape_regex(text); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.escape_regex(text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
    SELECT regexp_replace($1, '([\\.\*\+\?\|\(\)\[\]\{\}\^\$])', '\\\1', 'g');
$_$;


--
-- Name: fn_projects_history(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION public.fn_projects_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_action varchar(10);
    v_row    projects.projects;
    v_user   integer;
BEGIN
    v_action := TG_OP;

    -- For DELETE, capture the old row; otherwise capture the new row
    IF TG_OP = 'DELETE' THEN
        v_row  := OLD;
        v_user := OLD.modified_user;
    ELSE
        v_row  := NEW;
        v_user := NEW.modified_user;
    END IF;

    INSERT INTO projects.projects_history (
        project_id,
        parent_project_id,
        project_name,
        project_status_id,
        project_manager,
        geom,
        created_datetime,
        created_user,
        modified_datetime,
        modified_user,
        company_id,
        account_id,
        stocklist_id,
        cover_image_url,
        history_action,
        history_datetime,
        history_user
    ) VALUES (
        v_row.project_id,
        v_row.parent_project_id,
        v_row.project_name,
        v_row.project_status_id,
        v_row.project_manager,
        v_row.geom,
        v_row.created_datetime,
        v_row.created_user,
        v_row.modified_datetime,
        v_row.modified_user,
        v_row.company_id,
        v_row.account_id,
        v_row.stocklist_id,
        v_row.cover_image_url,
        v_action,
        now(),
        v_user
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$;


--
-- Name: stocklists_update_modified_fields(); Type: FUNCTION; Schema: stocklists; Owner: -
--

CREATE FUNCTION stocklists.stocklists_update_modified_fields() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    NEW.modified_datetime = now();
    --NEW.modified_user = current_user_id();  -- Replace this with your logic to get the current user ID
    RETURN NEW;
END;
$$;


--
-- Name: geo_db; Type: SERVER; Schema: -; Owner: -
--

CREATE SERVER geo_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'geo',
    host 'localhost'
);


--
-- Name: USER MAPPING postgres SERVER geo_db; Type: USER MAPPING; Schema: -; Owner: -
--

CREATE USER MAPPING FOR postgres SERVER geo_db OPTIONS (
    password 'N0m3551n',
    "user" 'postgres'
);


--
-- Name: geo_server; Type: SERVER; Schema: -; Owner: -
--

CREATE SERVER geo_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    dbname 'geo',
    host 'localhost'
);


--
-- Name: USER MAPPING postgres SERVER geo_server; Type: USER MAPPING; Schema: -; Owner: -
--

CREATE USER MAPPING FOR postgres SERVER geo_server OPTIONS (
    password 'N0m3551n',
    "user" 'postgres'
);




--
-- Name: account_access_log; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_access_log (
    id bigint NOT NULL,
    account_id bigint,
    user_id integer,
    access_type character varying(50),
    access_datetime timestamp without time zone DEFAULT now()
);


--
-- Name: account_access_log_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_access_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_access_log_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_access_log_id_seq OWNED BY accounts.account_access_log.id;


--
-- Name: account_field_category; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_category (
    field_category_id integer NOT NULL,
    field_category_name character varying NOT NULL,
    field_category_ref character varying,
    field_section_id integer,
    field_category_display_order integer
);


--
-- Name: account_field_category_field_category_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_category_field_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_category_field_category_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_category_field_category_id_seq OWNED BY accounts.account_field_category.field_category_id;


--
-- Name: account_field_dropdown_options; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_dropdown_options (
    dropdown_id bigint NOT NULL,
    field_id integer NOT NULL,
    dropdown_value character varying,
    dropdown_name character varying
);


--
-- Name: account_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_dropdown_options_dropdown_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_dropdown_options_dropdown_id_seq OWNED BY accounts.account_field_dropdown_options.dropdown_id;


--
-- Name: account_field_sections; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_sections (
    field_section_id integer NOT NULL,
    field_section_name character varying,
    field_section_display_order integer,
    field_section_ref character varying,
    field_section_icon character varying(50)
);


--
-- Name: account_field_sections_field_section_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_sections_field_section_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_sections_field_section_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_sections_field_section_id_seq OWNED BY accounts.account_field_sections.field_section_id;


--
-- Name: account_field_sub_category; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_sub_category (
    field_sub_category_id integer NOT NULL,
    field_sub_category_name character varying NOT NULL,
    field_sub_category_ref character varying
);


--
-- Name: account_field_sub_category_field_sub_category_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_sub_category_field_sub_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_sub_category_field_sub_category_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_sub_category_field_sub_category_id_seq OWNED BY accounts.account_field_sub_category.field_sub_category_id;


--
-- Name: account_field_values_boolean; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_boolean (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_boolean_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_boolean_history (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_boolean_history_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_boolean_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_boolean_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_boolean_history_value_id_seq OWNED BY accounts.account_field_values_boolean_history.value_id;


--
-- Name: account_field_values_boolean_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_boolean_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_boolean_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_boolean_value_id_seq OWNED BY accounts.account_field_values_boolean.value_id;


--
-- Name: account_field_values_date; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_date (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_date_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_date_history (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_date_history_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_date_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_date_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_date_history_value_id_seq OWNED BY accounts.account_field_values_date_history.value_id;


--
-- Name: account_field_values_date_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_date_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_date_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_date_value_id_seq OWNED BY accounts.account_field_values_date.value_id;


--
-- Name: account_field_values_int; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_int (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_int_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_int_history (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_int_history_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_int_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_int_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_int_history_value_id_seq OWNED BY accounts.account_field_values_int_history.value_id;


--
-- Name: account_field_values_int_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_int_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_int_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_int_value_id_seq OWNED BY accounts.account_field_values_int.value_id;


--
-- Name: account_field_values_numeric; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_numeric (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_numeric_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_numeric_history (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_numeric_history_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_numeric_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_numeric_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_numeric_history_value_id_seq OWNED BY accounts.account_field_values_numeric_history.value_id;


--
-- Name: account_field_values_numeric_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_numeric_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_numeric_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_numeric_value_id_seq OWNED BY accounts.account_field_values_numeric.value_id;


--
-- Name: account_field_values_text; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_text (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_text_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_field_values_text_history (
    value_id bigint NOT NULL,
    field_id integer,
    account_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: account_field_values_text_history_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_text_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_text_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_text_history_value_id_seq OWNED BY accounts.account_field_values_text_history.value_id;


--
-- Name: account_field_values_text_value_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_field_values_text_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_field_values_text_value_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_field_values_text_value_id_seq OWNED BY accounts.account_field_values_text.value_id;


--
-- Name: account_fields; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_fields (
    field_id bigint NOT NULL,
    field_section integer,
    field_category integer,
    field_sub_category integer,
    field_display_order integer,
    field_data_type character varying,
    field_name character varying,
    field_input_type character varying,
    field_form_id character varying,
    field_required boolean,
    field_active boolean DEFAULT true,
    field_spacer_after integer,
    field_type character varying(10),
    pass_field_to_stocklist boolean,
    pass_field_to_project boolean
);


--
-- Name: account_fields_field_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.account_fields_field_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: account_fields_field_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.account_fields_field_id_seq OWNED BY accounts.account_fields.field_id;


--
-- Name: account_journal; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_journal (
    id bigint NOT NULL,
    account_id bigint,
    user_id integer,
    log_datetime timestamp without time zone,
    log_text text
);


--
-- Name: account_premises; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_premises (
    uprn bigint,
    account_id integer
);


--
-- Name: account_status; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.account_status (
    account_status_id integer,
    account_status_desc character varying(100)
);


--
-- Name: accounts; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.accounts (
    account_id integer NOT NULL,
    parent_account_id integer,
    account_name character varying(100),
    account_status_id integer,
    account_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone DEFAULT now(),
    created_user integer,
    modified_datetime timestamp without time zone DEFAULT now(),
    modified_user integer,
    company_id integer
);


--
-- Name: accounts_account_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.accounts_account_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: accounts_account_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.accounts_account_id_seq OWNED BY accounts.accounts.account_id;


--
-- Name: accounts_history; Type: TABLE; Schema: accounts; Owner: -
--

CREATE TABLE accounts.accounts_history (
    account_id integer NOT NULL,
    parent_account_id integer,
    account_name character varying(100),
    account_status_id integer,
    account_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone DEFAULT now(),
    created_user integer,
    modified_datetime timestamp without time zone DEFAULT now(),
    modified_user integer,
    company_id integer
);


--
-- Name: accounts_history_account_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.accounts_history_account_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: accounts_history_account_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.accounts_history_account_id_seq OWNED BY accounts.accounts_history.account_id;


--
-- Name: newtable_id_seq; Type: SEQUENCE; Schema: accounts; Owner: -
--

CREATE SEQUENCE accounts.newtable_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: newtable_id_seq; Type: SEQUENCE OWNED BY; Schema: accounts; Owner: -
--

ALTER SEQUENCE accounts.newtable_id_seq OWNED BY accounts.account_journal.id;


--
-- Name: companies; Type: TABLE; Schema: users; Owner: -
--

CREATE TABLE users.companies (
    company_id integer NOT NULL,
    company_name character varying,
    company_active boolean,
    company_active_datetime timestamp without time zone
);


--
-- Name: users; Type: TABLE; Schema: users; Owner: -
--

CREATE TABLE users.users (
    id bigint NOT NULL,
    username character varying(100) NOT NULL,
    manager character varying(100) NOT NULL,
    email character varying(50) NOT NULL,
    company integer NOT NULL,
    role integer NOT NULL,
    division integer NOT NULL,
    team integer NOT NULL,
    password character varying(255) NOT NULL,
    is_active integer NOT NULL,
    date_user_created timestamp without time zone,
    date_last_login timestamp without time zone,
    date_last_active timestamp without time zone,
    failed_login_attempts integer DEFAULT 0 NOT NULL,
    locked_until timestamp without time zone
);


--
-- Name: vw_accounts; Type: VIEW; Schema: accounts; Owner: -
--

CREATE VIEW accounts.vw_accounts AS
 SELECT p.account_id,
    p.parent_account_id,
    p.account_name,
    p.account_status_id,
    p.account_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS account_manager_name,
    p.company_id,
    c.company_name
   FROM ((accounts.accounts p
     LEFT JOIN users.users u ON ((p.account_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)));


--
-- Name: projects; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.projects (
    project_id integer NOT NULL,
    parent_project_id integer,
    project_name character varying(150),
    project_status_id integer,
    project_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone DEFAULT now(),
    created_user integer,
    modified_datetime timestamp without time zone DEFAULT now(),
    modified_user integer,
    company_id integer,
    account_id integer,
    stocklist_id integer,
    cover_image_url character varying(100)
);


--
-- Name: stocklists; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklists (
    stocklist_id integer NOT NULL,
    parent_stocklist_id integer,
    stocklist_name character varying(100),
    stocklist_status_id integer,
    stocklist_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone DEFAULT now(),
    created_user integer,
    modified_datetime timestamp without time zone DEFAULT now(),
    modified_user integer,
    company_id integer,
    account_id integer
);


--
-- Name: vw_accounts_list; Type: VIEW; Schema: accounts; Owner: -
--

CREATE VIEW accounts.vw_accounts_list AS
 WITH projects AS (
         SELECT p_1.account_id,
            count(*) AS total_projects,
            count(
                CASE
                    WHEN (p_1.project_status_id = 100) THEN 1
                    ELSE NULL::integer
                END) AS complete_projects
           FROM projects.projects p_1
          WHERE (p_1.account_id IS NOT NULL)
          GROUP BY p_1.account_id
        ), stocklists AS (
         SELECT s.account_id,
            count(*) AS total_stocklists
           FROM stocklists.stocklists s
          WHERE (s.account_id IS NOT NULL)
          GROUP BY s.account_id
        )
 SELECT p.account_id,
    COALESCE((p.parent_account_id)::text, ''::text) AS parent_account_id,
    COALESCE((pp.account_name)::text, ''::text) AS parent_account_name,
    p.account_name,
    p.account_status_id,
    COALESCE(ps.account_status_desc, ''::character varying) AS account_status_desc,
    p.account_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    p.created_user,
    COALESCE(uc.username, ''::character varying) AS created_username,
    to_char(p.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    p.modified_user,
    um.username AS modified_username,
    projects.total_projects,
    projects.complete_projects,
    stocklists.total_stocklists
   FROM (((((((accounts.accounts p
     LEFT JOIN users.users um ON ((p.modified_user = um.id)))
     LEFT JOIN users.users uc ON ((p.created_user = uc.id)))
     LEFT JOIN users.users upm ON ((p.account_manager = upm.id)))
     LEFT JOIN accounts.account_status ps ON ((p.account_status_id = ps.account_status_id)))
     LEFT JOIN accounts.accounts pp ON ((p.parent_account_id = pp.account_id)))
     LEFT JOIN projects ON ((p.account_id = projects.account_id)))
     LEFT JOIN stocklists ON ((p.account_id = stocklists.account_id)));


--
-- Name: file_uploads; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.file_uploads (
    id bigint NOT NULL,
    file_id character varying,
    file_location character varying,
    file_upload_datetime timestamp without time zone,
    file_upload_user integer,
    file_name character varying(100),
    file_type character varying,
    entity character varying,
    project_id integer,
    stocklist_id integer,
    account_id integer,
    opportunity_id integer,
    file_category character varying,
    file_description text,
    file_size numeric(10,1),
    file_size_raw bigint,
    file_is_deleted boolean DEFAULT false NOT NULL,
    agreement_id integer
);


--
-- Name: vw_file_uploads; Type: VIEW; Schema: accounts; Owner: -
--

CREATE VIEW accounts.vw_file_uploads AS
 SELECT fu.file_id,
    to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
    u.username,
    fu.file_name,
    fu.file_type,
    fu.account_id,
    fu.file_category,
    fu.file_description,
    fu.file_size
   FROM (public.file_uploads fu
     LEFT JOIN users.users u ON ((fu.file_upload_user = u.id)))
  WHERE ((fu.entity)::text = 'account'::text)
  ORDER BY fu.file_upload_datetime DESC;


--
-- Name: vw_parent_account_links; Type: VIEW; Schema: accounts; Owner: -
--

CREATE VIEW accounts.vw_parent_account_links AS
 SELECT p.account_id,
    p.account_name,
    p.parent_account_id,
    pp.account_name AS parent_account_name,
    NULL::public.geometry AS geom,
    NULL::numeric AS distance
   FROM (accounts.accounts p
     JOIN accounts.accounts pp ON ((p.parent_account_id = pp.account_id)));


--
-- Name: bduk_fmp_work_may_2026_20260601; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_fmp_work_may_2026_20260601 (
    uprn bigint,
    lot_id integer,
    lot_name text,
    single_line_address text,
    postcode text,
    longitude double precision,
    latitude double precision,
    top_up_scheme text,
    rurality text,
    is_white_but_blocked text,
    note text
);


--
-- Name: bduk_fmp_work_may_2026_20260601_geom; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_fmp_work_may_2026_20260601_geom (
    uprn bigint NOT NULL,
    lot_id integer,
    lot_name text,
    single_line_address text,
    postcode text,
    longitude double precision,
    latitude double precision,
    top_up_scheme text,
    rurality text,
    is_white_but_blocked text,
    note text,
    building_type text,
    building_use text,
    building_premises bigint,
    building_premises_residential bigint,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_go_list_address_view_20250807; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_address_view_20250807 (
    lot_id bigint,
    lot_name character varying(64),
    top_up_scheme character varying(101),
    rurality character varying(102),
    uprn bigint,
    sprn bigint,
    class_type character varying(16),
    address character varying(512),
    block character varying(128),
    postcode character varying(10),
    borough character varying(32),
    toid character varying(32),
    resi_unit_count integer,
    distance_to_reserved_inventory real,
    title_no character varying(256),
    wayleave_flag character varying(103)
);


--
-- Name: bduk_go_list_address_view_geom; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_address_view_geom (
    lot_id bigint,
    lot_name character varying(64),
    top_up_scheme character varying(101),
    rurality character varying(102),
    uprn bigint,
    sprn bigint,
    class_type character varying(16),
    address character varying(512),
    block character varying(128),
    postcode character varying(10),
    borough character varying(32),
    toid character varying(32),
    resi_unit_count integer,
    distance_to_reserved_inventory real,
    title_no character varying(256),
    wayleave_flag character varying(103),
    building_premises bigint,
    building_premises_residential bigint,
    building_premises_residential_flat bigint,
    building_premises_commercial bigint,
    building_type text,
    building_use text,
    example_uprn bigint,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_go_list_fmp_work; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_fmp_work (
    uprn integer,
    lot_id integer,
    lot_name character varying(100),
    single_line_address character varying(256),
    postcode character varying(50),
    longitude real,
    latitude real,
    top_up_scheme character varying(100),
    rurality character varying(50),
    deprioritised character varying(256)
);


--
-- Name: bduk_go_list_fmp_work_20251003; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_fmp_work_20251003 (
    uprn bigint,
    lot_id bigint,
    lot_name character varying(64),
    top_up_scheme character varying(32),
    rurality character varying(12)
);


--
-- Name: bduk_go_list_fmp_work_20251003_address_geom; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_fmp_work_20251003_address_geom (
    uprn bigint,
    lot_id bigint,
    lot_name character varying(64),
    top_up_scheme character varying(32),
    rurality character varying(12),
    address text,
    postcode character varying(8),
    borough character varying(50),
    toid character varying(50),
    classification_code character varying,
    class_description character varying,
    building_premises bigint,
    building_type text,
    building_use text,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_go_list_fmp_work_20260217; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_fmp_work_20260217 (
    uprn bigint,
    lot_id integer,
    lot_name character varying(200),
    single_line_address character varying(256),
    postcode character varying(50),
    longitude real,
    latitude real,
    top_up_scheme character varying(200),
    rurality character varying(20),
    deprioritised character varying(256)
);


--
-- Name: bduk_go_list_fmp_work_20260217_geom; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_go_list_fmp_work_20260217_geom (
    uprn bigint,
    lot_id integer,
    lot_name character varying(200),
    single_line_address character varying(256),
    postcode character varying(50),
    longitude real,
    latitude real,
    top_up_scheme character varying(200),
    rurality character varying(20),
    deprioritised character varying(256),
    classification_code character varying,
    class_description character varying,
    toid character varying(50),
    building_premises bigint,
    building_type text,
    building_use text,
    geom public.geometry(Point,27700),
    voucher_status text
);


--
-- Name: bduk_import; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_import (
    uprn bigint NOT NULL,
    struprn text,
    bduk_recognised_premises boolean,
    country text,
    postcode text,
    lot_id bigint,
    lot_name text,
    subsidy_control_status text,
    current_gigabit boolean,
    future_gigabit boolean,
    local_authority_district_ons_code text,
    local_authority_district_ons text,
    region_ons_code text,
    region_ons text,
    bduk_gis boolean,
    bduk_gis_contract_scope text,
    gis_final_coverage_date text,
    bduk_vouchers boolean,
    bduk_superfast boolean,
    bduk_hubs boolean,
    supplier text,
    contract_name text
);


--
-- Name: bduk_project_checks_20260527_stg1; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_project_checks_20260527_stg1 (
    project_name character varying(150),
    project_id integer,
    created_datetime timestamp without time zone,
    uprn bigint
);


--
-- Name: bduk_project_checks_20260527_stg2; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_project_checks_20260527_stg2 (
    project_name character varying(150),
    project_id integer,
    created_datetime timestamp without time zone,
    bduk_2025_04 bigint,
    bduk_2025_05 bigint,
    bduk_2025_08 bigint,
    bduk_2025_10 bigint,
    bduk_2026_02 bigint
);


--
-- Name: bduk_uprn_tracking; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking (
    uprn bigint,
    toid character varying,
    building_type text,
    building_premises bigint,
    voucher_available boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_stg1; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_stg1 (
    uprn bigint,
    toid character varying(50),
    building_type text,
    building_premises bigint,
    voucher_available boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_stg2; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_stg2 (
    uprn bigint,
    toid character varying(32),
    building_type text,
    building_premises bigint,
    bool boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_stg3; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_stg3 (
    uprn bigint,
    toid character varying(32),
    building_type text,
    building_premises bigint,
    voucher_available boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_stg4; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_stg4 (
    uprn bigint,
    toid character varying(50),
    building_type text,
    building_premises bigint,
    voucher_available boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_stg5; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_stg5 (
    uprn bigint,
    toid character varying(50),
    building_type text,
    building_premises bigint,
    voucher_available boolean,
    source_table text,
    source_notes text,
    date_first_seen date,
    date_last_seen date,
    is_record_current boolean,
    geom public.geometry(Point,27700)
);


--
-- Name: bduk_uprn_tracking_v2; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bduk_uprn_tracking_v2 (
    uprn bigint,
    address text,
    postcode character varying(8),
    building_type text,
    building_use text,
    building_premises bigint,
    building_premises_residential bigint,
    toid character varying(50),
    geom public.geometry(Point,27700),
    is_current boolean,
    date_first_seen date,
    date_last_seen date,
    times_seen bigint
);


--
-- Name: bsr_high_rise_register_final_20251003; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bsr_high_rise_register_final_20251003 (
    application_reference character varying(12),
    structure_and_address character varying(256),
    postcode character varying(12),
    height_m integer,
    floors integer,
    residential_units integer,
    pap_name character varying(128),
    construction_year_range character varying(12),
    local_authority_area character varying(64),
    safety_report integer
);


--
-- Name: bsr_high_rise_register_final_20251003_geom; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bsr_high_rise_register_final_20251003_geom (
    id bigint NOT NULL,
    application_reference character varying(12),
    structure_and_address character varying(256),
    postcode character varying(12),
    height_m integer,
    floors integer,
    residential_units integer,
    pap_name character varying(128),
    construction_year_range character varying(12),
    local_authority_area character varying(64),
    safety_report integer,
    match_score numeric,
    toid character varying(50),
    building_premises bigint,
    building_premises_residential bigint,
    building_premises_residential_flat bigint,
    building_premises_commercial bigint,
    building_type text,
    building_use text,
    example_uprn bigint,
    example_address text,
    geom public.geometry(Point,27700)
);


--
-- Name: bsr_high_rise_register_final_20251003_geom_stg1; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bsr_high_rise_register_final_20251003_geom_stg1 (
    address_a character varying(256),
    postcode_a character varying(12),
    address_b text,
    uprn bigint,
    toid character varying(50),
    match_score real
);


--
-- Name: bsr_high_rise_register_final_20251003_geom_stg2; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.bsr_high_rise_register_final_20251003_geom_stg2 (
    address_a character varying(256),
    postcode_a character varying(12),
    address_b text,
    uprn bigint,
    toid character varying(50),
    match_score real,
    toid_count bigint,
    r bigint
);


--
-- Name: exchange_restriction_dates; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.exchange_restriction_dates (
    sl_no integer,
    exchange_name character varying(32),
    exchange_location character varying(64),
    exchange_code character varying(12),
    order_restriction_implimentation_date character varying(12),
    last_amended_date character varying(12),
    tranche character varying(32)
);


--
-- Name: exchanges; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.exchanges (
    css_db_exch character varying(12),
    exchange_name character varying(128),
    css_db character varying(12),
    css_exchange character varying(12),
    "1141_code" character varying(12),
    supplier_region character varying(32),
    supplier_area character varying(32),
    cqdm_area character varying(32),
    cqdm_name character varying(12),
    ni_7_area_complex_gm character varying(32),
    gm_area character varying(32),
    cst_a character varying(32),
    street character varying(64),
    town character varying(32),
    county character varying(32),
    post_code character varying(12),
    latitude real,
    longitude real,
    tree_cont_no integer,
    ctregion_old character varying(16),
    fnd_rmb_name character varying(16),
    fnd_gm_name character varying(32),
    sd_rbm_name character varying(16),
    sd_gm_name character varying(32),
    geom public.geometry
);


--
-- Name: TABLE exchanges; Type: COMMENT; Schema: adhoc; Owner: -
--

COMMENT ON TABLE adhoc.exchanges IS 'Exchange list sent by Tom Chesterman on 24/11/2025';


--
-- Name: scci_bduk_mdu_20250408; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.scci_bduk_mdu_20250408 (
    units integer,
    postcodes text,
    example_address text,
    class_descriptions text,
    block_name text,
    borough text,
    landlord text,
    class_type text,
    rurality text
);


--
-- Name: scci_bduk_mdu_geom_20250408; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.scci_bduk_mdu_geom_20250408 (
    units integer,
    example_address text,
    class_descriptions text,
    block_name text,
    borough text,
    landlord text,
    class_type text,
    rurality text,
    postcode text,
    geom public.geometry
);


--
-- Name: scci_cfl_mdu_pia_distance_with_bduk_20250519; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.scci_cfl_mdu_pia_distance_with_bduk_20250519 (
    toid character varying(32),
    example_uprn bigint,
    example_address character varying(256),
    postcode text,
    latest_wayleave_date character varying(16),
    class character varying(16),
    units integer,
    old_id integer,
    olt_name character varying(32),
    voucher character varying(10),
    freeholder text,
    straight_line_distance_to_cab integer,
    pia_distance_to_cab integer,
    estimated_unit_capacity integer,
    routing_notes character varying(32),
    borough character varying(32),
    capacity character varying(10)
);


--
-- Name: scci_cfl_mdu_pia_distance_with_bduk_geom_20250519; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.scci_cfl_mdu_pia_distance_with_bduk_geom_20250519 (
    toid character varying(32),
    example_uprn bigint,
    example_address character varying(256),
    postcode text,
    latest_wayleave_date character varying(16),
    class character varying(16),
    units integer,
    old_id integer,
    olt_name character varying(32),
    voucher character varying(10),
    freeholder text,
    straight_line_distance_to_cab integer,
    pia_distance_to_cab integer,
    estimated_unit_capacity integer,
    routing_notes character varying(32),
    borough character varying(32),
    capacity character varying(10),
    building_type text,
    building_use text,
    geom public.geometry(Point,27700)
);


--
-- Name: scci_project_import_postcodes; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.scci_project_import_postcodes (
    postcode character varying(12),
    geom public.geometry
);


--
-- Name: tbl_abp_import; Type: TABLE; Schema: adhoc; Owner: -
--

CREATE TABLE adhoc.tbl_abp_import (
    source_id character varying(20),
    uprn bigint NOT NULL,
    usrn bigint,
    address_logical_status smallint,
    address_logical_status_desc character varying(200),
    building_logical_status smallint,
    building_logical_status_desc character varying(200),
    building_status smallint,
    building_status_desc character varying(200),
    building_classification_code character varying(25),
    building_classification_code_desc character varying(200),
    organisation_name character varying(100),
    sao character varying(105),
    sao_text character varying(90),
    sao_start_number smallint,
    sao_start_suffix character varying(2),
    sao_end_number smallint,
    sao_end_suffix character varying(2),
    pao character varying(105),
    pao_text character varying(90),
    pao_start_number smallint,
    pao_start_suffix character varying(2),
    pao_end_number smallint,
    pao_end_suffix character varying(2),
    administrative_area character varying(30),
    street_description character varying(110),
    postcode character varying(8),
    postcode_area character varying(2),
    postcode_district character varying(4),
    postcode_sector character varying(6),
    locality_name character varying(40),
    town_name character varying(30),
    parent_uprn bigint,
    multi_occ_count smallint,
    x_coordinate character varying(15),
    y_coordinate character varying(15),
    wgs84_longitude character varying(20),
    wgs84_latitude character varying(20),
    rpc smallint,
    postal_address character varying(1),
    address_start_date_time timestamp without time zone,
    address_end_date_time timestamp without time zone,
    last_update_date_time timestamp without time zone,
    entry_date_time timestamp without time zone,
    os_topo_toid character varying(20),
    building_premise_count integer,
    valid_address_flag character varying(1),
    dna_address_type character varying(20),
    building_type character varying(20),
    iud_indicator character varying(1),
    row_start_date_time timestamp without time zone,
    row_end_date_time timestamp without time zone,
    row_is_current character varying(1)
);


--
-- Name: abp; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.abp (
    uprn bigint,
    classification_code character varying,
    class_description character varying,
    parent_uprn bigint,
    block text,
    postcode character varying(8),
    address text,
    business_name text,
    borough character varying(50),
    main_proprietor text,
    title_no text,
    property_type text,
    cross_reference character varying(50),
    udprn bigint,
    umprn integer,
    geom public.geometry(Point,27700),
    sprn integer,
    portfolio_id smallint,
    bd_status smallint,
    leasehold_portfolio_id integer,
    wayleave_date date,
    mdu_id bigint,
    class_type text,
    category_code smallint,
    wayleave_portfolio_id smallint,
    main_leaseholder text,
    leaseholder_managed boolean,
    usrn bigint,
    provider integer,
    postcode_sector character varying(7),
    street_name character varying(100),
    street_authority character varying(100)
);


--
-- Name: toid_building_type_geom; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.toid_building_type_geom (
    toid character varying(50),
    building_premises bigint,
    building_premises_residential bigint,
    building_premises_residential_flat bigint,
    building_premises_commercial bigint,
    building_type text,
    building_use text,
    example_uprn bigint,
    example_address text,
    geom public.geometry(Point,27700)
);


--
-- Name: vw_bduk_go_list_fmp_work_20260217_geom; Type: VIEW; Schema: adhoc; Owner: -
--

CREATE VIEW adhoc.vw_bduk_go_list_fmp_work_20260217_geom AS
 SELECT b.uprn,
    b.lot_id,
    b.lot_name,
    b.single_line_address,
    b.postcode,
    b.longitude,
    b.latitude,
    b.top_up_scheme,
    b.rurality,
    b.deprioritised,
    a.classification_code,
    a.class_description,
    bt.toid,
    bt.building_premises,
    bt.building_type,
    bt.building_use,
    a.geom,
        CASE
            WHEN (bd.uprn IS NULL) THEN 'New'::text
            ELSE 'Existing'::text
        END AS voucher_status
   FROM (((adhoc.bduk_go_list_fmp_work_20260217 b
     LEFT JOIN adhoc.bduk_go_list_fmp_work_20251003_address_geom bd ON ((b.uprn = bd.uprn)))
     JOIN basedata.abp a ON ((a.uprn = b.uprn)))
     JOIN basedata.toid_building_type_geom bt ON (((a.cross_reference)::text = (bt.toid)::text)));


--
-- Name: vw_bduk_uprn_latest; Type: VIEW; Schema: adhoc; Owner: -
--

CREATE VIEW adhoc.vw_bduk_uprn_latest AS
 SELECT bduk_fmp_work_may_2026_20260601_geom.uprn,
    bduk_fmp_work_may_2026_20260601_geom.lot_id,
    bduk_fmp_work_may_2026_20260601_geom.lot_name,
    bduk_fmp_work_may_2026_20260601_geom.single_line_address,
    bduk_fmp_work_may_2026_20260601_geom.postcode,
    bduk_fmp_work_may_2026_20260601_geom.longitude,
    bduk_fmp_work_may_2026_20260601_geom.latitude,
    bduk_fmp_work_may_2026_20260601_geom.top_up_scheme,
    bduk_fmp_work_may_2026_20260601_geom.rurality,
    bduk_fmp_work_may_2026_20260601_geom.is_white_but_blocked,
    bduk_fmp_work_may_2026_20260601_geom.note,
    bduk_fmp_work_may_2026_20260601_geom.building_type,
    bduk_fmp_work_may_2026_20260601_geom.building_use,
    bduk_fmp_work_may_2026_20260601_geom.building_premises,
    bduk_fmp_work_may_2026_20260601_geom.building_premises_residential,
    bduk_fmp_work_may_2026_20260601_geom.geom
   FROM adhoc.bduk_fmp_work_may_2026_20260601_geom;


--
-- Name: abp_class_type_summary; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.abp_class_type_summary (
    classification_code character varying,
    class_description character varying,
    c bigint
);


--
-- Name: abp_deletes; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.abp_deletes (
    uprn bigint,
    classification_code character varying,
    class_description character varying,
    parent_uprn bigint,
    block text,
    postcode character varying(8),
    address text,
    business_name text,
    borough character varying(50),
    main_proprietor text,
    title_no text,
    property_type text,
    cross_reference character varying(50),
    udprn bigint,
    umprn integer,
    geom public.geometry(Point,27700),
    sprn integer,
    portfolio_id smallint,
    bd_status smallint,
    leasehold_portfolio_id integer,
    wayleave_date date,
    mdu_id bigint,
    class_type text,
    category_code smallint,
    wayleave_portfolio_id smallint,
    main_leaseholder text,
    leaseholder_managed boolean,
    usrn bigint,
    provider integer,
    postcode_sector character varying(7),
    street_name character varying(42),
    street_authority character varying(42),
    delete_reason text
);


--
-- Name: abp_import; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.abp_import (
    source_id character varying(20),
    uprn bigint NOT NULL,
    usrn bigint,
    address_logical_status smallint,
    address_logical_status_desc character varying(200),
    building_logical_status smallint,
    building_logical_status_desc character varying(200),
    building_status smallint,
    building_status_desc character varying(200),
    building_classification_code character varying(25),
    building_classification_code_desc character varying(200),
    organisation_name character varying(100),
    sao character varying(105),
    sao_text character varying(90),
    sao_start_number smallint,
    sao_start_suffix character varying(2),
    sao_end_number smallint,
    sao_end_suffix character varying(2),
    pao character varying(105),
    pao_text character varying(90),
    pao_start_number smallint,
    pao_start_suffix character varying(2),
    pao_end_number smallint,
    pao_end_suffix character varying(2),
    administrative_area character varying(30),
    street_description character varying(110),
    postcode character varying(8),
    postcode_area character varying(2),
    postcode_district character varying(4),
    postcode_sector character varying(6),
    locality_name character varying(40),
    town_name character varying(30),
    parent_uprn bigint,
    multi_occ_count smallint,
    x_coordinate character varying(15),
    y_coordinate character varying(15),
    wgs84_longitude character varying(20),
    wgs84_latitude character varying(20),
    rpc smallint,
    postal_address character varying(1),
    address_start_date_time timestamp without time zone,
    address_end_date_time timestamp without time zone,
    last_update_date_time timestamp without time zone,
    entry_date_time timestamp without time zone,
    os_topo_toid character varying(20),
    building_premise_count integer,
    valid_address_flag character varying(1),
    dna_address_type character varying(20),
    building_type character varying(20),
    iud_indicator character varying(1),
    row_start_date_time timestamp without time zone,
    row_end_date_time timestamp without time zone,
    geom public.geometry(Point,27700),
    row_is_current character varying(8)
);


--
-- Name: abp_import_removed; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.abp_import_removed (
    uprn bigint,
    classification_code character varying,
    class_description character varying,
    parent_uprn bigint,
    block text,
    postcode character varying(8),
    address text,
    business_name text,
    borough character varying(50),
    main_proprietor text,
    title_no text,
    property_type text,
    cross_reference character varying(50),
    udprn bigint,
    umprn integer,
    geom public.geometry(Point,27700),
    sprn integer,
    portfolio_id smallint,
    bd_status smallint,
    leasehold_portfolio_id integer,
    wayleave_date date,
    mdu_id bigint,
    class_type text,
    category_code smallint,
    wayleave_portfolio_id smallint,
    main_leaseholder text,
    leaseholder_managed boolean,
    usrn bigint,
    provider integer,
    postcode_sector character varying(7),
    street_name character varying(100),
    street_authority character varying(100),
    removed_at timestamp without time zone DEFAULT now() NOT NULL
);


--
-- Name: bduk_uk_geom; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.bduk_uk_geom (
    uprn bigint,
    struprn text,
    bduk_recognised_premises boolean,
    country text,
    postcode text,
    lot_id bigint,
    lot_name text,
    subsidy_control_status text,
    current_gigabit boolean,
    future_gigabit boolean,
    local_authority_district_ons_code text,
    local_authority_district_ons text,
    region_ons_code text,
    region_ons text,
    bduk_gis boolean,
    bduk_gis_contract_scope text,
    gis_final_coverage_date text,
    bduk_vouchers boolean,
    bduk_superfast boolean,
    bduk_hubs boolean,
    supplier text,
    contract_name text,
    geom public.geometry(Point,27700)
);


--
-- Name: os_code_point_open; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.os_code_point_open (
    postcode character varying(12),
    positional_quality_indicator integer,
    eastings integer,
    northings integer,
    country_code character varying(10),
    nhs_regional_ha_code character varying,
    nhs_ha_code character varying(10),
    admin_county_code character varying(10),
    admin_district_code character varying(10),
    admin_ward_code character varying(10),
    geom public.geometry
);


--
-- Name: postcode_summary; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.postcode_summary (
    postcode character varying(8),
    toid_count bigint,
    uprn_count bigint
);


--
-- Name: tbl_uk_postcode_unit; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.tbl_uk_postcode_unit (
    postcode character varying(8) NOT NULL,
    geom public.geometry(Geometry,4326)
);


--
-- Name: toid_building_type; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.toid_building_type (
    toid character varying(50),
    building_premises bigint,
    building_premises_residential bigint,
    building_premises_residential_flat bigint,
    building_premises_commercial bigint,
    building_type text,
    building_use text
);


--
-- Name: uprn_address; Type: TABLE; Schema: basedata; Owner: -
--

CREATE TABLE basedata.uprn_address (
    uprn bigint NOT NULL,
    usrn bigint,
    os_topo_toid character varying(20) DEFAULT NULL::character varying,
    sao character varying(105) DEFAULT NULL::character varying,
    pao character varying(105) DEFAULT NULL::character varying,
    street_description character varying(110) DEFAULT NULL::character varying,
    town_name character varying(30) DEFAULT NULL::character varying,
    postcode character varying(8) DEFAULT NULL::character varying,
    s_state integer,
    triserviceability_code character varying(3) DEFAULT NULL::character varying
);


--
-- Name: vw_abp_min; Type: VIEW; Schema: basedata; Owner: -
--

CREATE VIEW basedata.vw_abp_min AS
 SELECT a.uprn,
    a.address,
    a.postcode,
    a.geom
   FROM basedata.abp a;


--
-- Name: ccod; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod (
    title_number character varying(10),
    tenure character varying(10),
    property_address text,
    district character varying(42),
    county character varying(42),
    region character varying(19),
    postcode character varying(9),
    multiple_address_indicator character varying(1),
    price_paid numeric,
    proprietor_name_1 text,
    company_registration_no_1 character varying,
    proprietorship_category_1 character varying,
    country_incorporated_1 character varying,
    proprietor_1_address_1 text,
    proprietor_1_address_2 character varying,
    proprietor_1_address_3 character varying,
    proprietor_name_2 character varying,
    company_registration_no_2 character varying,
    proprietorship_category_2 character varying,
    country_incorporated_2 text,
    proprietor_2_address_1 text,
    proprietor_2_address_2 character varying,
    proprietor_2_address_3 character varying,
    proprietor_name_3 character varying,
    company_registration_no_3 character varying,
    proprietorship_category_3 character varying(49),
    country_incorporated_3 text,
    proprietor_3_address_1 character varying,
    proprietor_3_address_2 character varying,
    proprietor_3_address_3 character varying,
    proprietor_name_4 character varying,
    company_registration_no_4 character varying,
    proprietorship_category_4 character varying(49),
    country_incorporated_4 text,
    proprietor_4_address_1 character varying,
    proprietor_4_address_2 character varying,
    proprietor_4_address_3 character varying,
    date_proprietor_added character varying(12),
    additional_proprietor_indicator character varying(1),
    data_source text,
    import_datetime timestamp with time zone,
    postcode_sector character varying(7),
    address_words text[] GENERATED ALWAYS AS (public.address_to_words(property_address)) STORED,
    street_name character varying(42),
    company_id bigint
);


--
-- Name: toid_title_map; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.toid_title_map (
    id bigint,
    os_topo_toid character varying(20),
    freehold_title_no character varying(10)
);


--
-- Name: vw_toid_building_type_geom; Type: VIEW; Schema: basedata; Owner: -
--

CREATE VIEW basedata.vw_toid_building_type_geom AS
 SELECT bt.toid,
    bt.building_premises,
    bt.building_premises_residential,
    bt.building_premises_residential_flat,
    bt.building_premises_commercial,
    bt.building_type,
    bt.building_use,
    bt.example_uprn,
    bt.example_address,
    bt.geom,
    c.tenure,
    c.title_number,
    c.proprietor_name_1,
        CASE
            WHEN (c.title_number IS NOT NULL) THEN '100%'::text
            ELSE '0%'::text
        END AS title_match_score
   FROM ((basedata.toid_building_type_geom bt
     JOIN landregistry.toid_title_map tm ON (((bt.toid)::text = (tm.os_topo_toid)::text)))
     LEFT JOIN landregistry.ccod c ON (((c.title_number)::text = (tm.freehold_title_no)::text)));


--
-- Name: community_fibre_pt; Type: TABLE; Schema: competitor_data; Owner: -
--

CREATE TABLE competitor_data.community_fibre_pt (
    id integer,
    hp_date date,
    uprn bigint,
    door_number character varying,
    postcode character varying,
    type character varying,
    block character varying,
    geom public.geometry(Point,27700),
    address text,
    status smallint,
    borough text,
    business_name text,
    main_proprietor text,
    last_status_update text,
    comment text,
    date_added date,
    project_id integer,
    portfolio_id integer,
    business_live integer,
    bd_status smallint,
    sprn integer,
    splitter_id text,
    sales_status smallint,
    declared_live_date date,
    install_type smallint,
    planning_ecd date,
    civils_done boolean,
    cabled boolean,
    spliced boolean,
    tested boolean,
    on_hold boolean,
    on_hold_category smallint,
    on_hold_subcategory smallint,
    on_hold_start date,
    on_hold_revisit date,
    on_hold_stop date,
    last_updater character varying,
    last_update_timestamp timestamp without time zone,
    mdu_id integer,
    forecast_live_date date,
    wayleave_date date,
    main_leaseholder text,
    leaseholder_managed boolean,
    provider integer
);


--
-- Name: competitor_cfl_postcode; Type: TABLE; Schema: competitor_data; Owner: -
--

CREATE TABLE competitor_data.competitor_cfl_postcode (
    postcode character varying(12),
    geom public.geometry,
    fttp_providers bigint
);


--
-- Name: competitor_summary_postcode; Type: TABLE; Schema: competitor_data; Owner: -
--

CREATE TABLE competitor_data.competitor_summary_postcode (
    postcode character varying(12),
    geom public.geometry,
    fttp_providers integer,
    operators character varying(200)
);


--
-- Name: competitors_by_postcode; Type: TABLE; Schema: competitor_data; Owner: -
--

CREATE TABLE competitor_data.competitors_by_postcode (
    postcode character varying(20),
    fttp_available character(1),
    geom public.geometry(Point,27700),
    provider_count integer
);


--
-- Name: openreach; Type: TABLE; Schema: competitor_data; Owner: -
--

CREATE TABLE competitor_data.openreach (
    id integer NOT NULL,
    postcode character varying(20),
    uprn bigint,
    check_date timestamp without time zone DEFAULT now(),
    nadkey character varying(20),
    districtcode character varying(20),
    dp character varying(10),
    wayleave_required character(1),
    prem_lat numeric(10,7),
    prem_lng numeric(10,7),
    exchange_name character varying(50),
    exchange_sauid character varying(20),
    exchange_lat numeric(10,7),
    exchange_lng numeric(10,7),
    exchange_address text,
    copper_ex_switch_off_date date,
    fttp_priority_exchange character(1),
    cabinet_number character varying(10),
    fttc_available character(1),
    fttc_active_at_property character(1),
    fttc_up_top numeric(5,2),
    fttc_up_worst numeric(5,2),
    fttc_down_top numeric(5,2),
    fttc_down_worst numeric(5,2),
    fttp_available character(1),
    fttp_active_at_property character(1),
    fttp_coming_soon character(1),
    fttp_delivery_date_planned boolean,
    fttpondemand_available character(1),
    ethernet_available character(1),
    adsl_available character(1),
    adsl_active_at_property character(1),
    adsl_line_length_calculated numeric(6,2),
    gfast_available character(1),
    gfast_up_top numeric(5,2),
    gfast_down_top numeric(5,2),
    sogea_available character(1),
    sogea_up_top numeric(5,2),
    sogea_up_worst numeric(5,2),
    sogea_down_top numeric(5,2),
    sogea_down_worst numeric(5,2),
    sogfast_available character(1),
    sogfast_up_top numeric(5,2),
    sogfast_down_top numeric(5,2),
    apigee_vdsl_predict_bottom_us_speed numeric(5,2),
    apigee_vdsl_predict_bottom_ds_speed numeric(5,2),
    apigee_gfast_predict_top_ds_speed numeric(5,2),
    apigee_gfast_predict_top_us_speed numeric(5,2),
    apigee_gfast_predict_bottom_ds_speed numeric(5,2),
    apigee_gfast_predict_bottom_us_speed numeric(5,2)
);


--
-- Name: openreach_id_seq; Type: SEQUENCE; Schema: competitor_data; Owner: -
--

CREATE SEQUENCE competitor_data.openreach_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: openreach_id_seq; Type: SEQUENCE OWNED BY; Schema: competitor_data; Owner: -
--

ALTER SEQUENCE competitor_data.openreach_id_seq OWNED BY competitor_data.openreach.id;


--
-- Name: point_topic; Type: FOREIGN TABLE; Schema: competitor_data; Owner: -
--

CREATE FOREIGN TABLE competitor_data.point_topic (
    postcode character varying(8),
    operator character varying(50),
    tech character varying(50),
    id integer NOT NULL
)
SERVER geo_db
OPTIONS (
    schema_name 'public',
    table_name 'point_topic'
);
ALTER FOREIGN TABLE competitor_data.point_topic ALTER COLUMN postcode OPTIONS (
    column_name 'postcode'
);
ALTER FOREIGN TABLE competitor_data.point_topic ALTER COLUMN operator OPTIONS (
    column_name 'operator'
);
ALTER FOREIGN TABLE competitor_data.point_topic ALTER COLUMN tech OPTIONS (
    column_name 'tech'
);
ALTER FOREIGN TABLE competitor_data.point_topic ALTER COLUMN id OPTIONS (
    column_name 'id'
);


--
-- Name: ccod_address_matches; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matches (
    match_id bigint NOT NULL,
    title_number character varying(10),
    tenure character varying(10),
    match_class integer,
    match_score real,
    match_datetime timestamp with time zone,
    uprn bigint,
    toid character varying(50)
);


--
-- Name: ccod_address_matches_match_id_seq; Type: SEQUENCE; Schema: landregistry; Owner: -
--

CREATE SEQUENCE landregistry.ccod_address_matches_match_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: ccod_address_matches_match_id_seq; Type: SEQUENCE OWNED BY; Schema: landregistry; Owner: -
--

ALTER SEQUENCE landregistry.ccod_address_matches_match_id_seq OWNED BY landregistry.ccod_address_matches.match_id;


--
-- Name: ccod_address_matching_stg1; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg1 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real
);


--
-- Name: ccod_address_matching_stg2; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg2 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real,
    rn bigint,
    match_class integer
);


--
-- Name: ccod_address_matching_stg3; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg3 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real
);


--
-- Name: ccod_address_matching_stg4; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg4 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real,
    rn bigint,
    match_class integer
);


--
-- Name: ccod_address_matching_stg5; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg5 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real,
    match_score_word real
);


--
-- Name: ccod_address_matching_stg6; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_address_matching_stg6 (
    title_number character varying(10),
    address_a text,
    postcode_a character varying(9),
    address_b text,
    uprn bigint,
    toid character varying(50),
    toid_count bigint,
    match_score real,
    match_score_word real,
    rn bigint,
    match_class integer
);


--
-- Name: ccod_company_summary; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_company_summary (
    company_id bigint NOT NULL,
    proprietor_name_1 text,
    company_registration_no_1 text,
    proprietorship_category_1 text,
    country_incorporated_1 text,
    proprietor_1_address_1 text,
    proprietor_1_address_2 text,
    proprietor_1_address_3 text,
    total_titles bigint,
    freehold_titles bigint,
    leasehold_titles bigint
);


--
-- Name: ccod_cou_2025_10_import_20251013; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_cou_2025_10_import_20251013 (
    title_number character varying(10),
    tenure character varying(10),
    property_address text,
    district character varying(42),
    county character varying(42),
    region character varying(19),
    postcode character varying(9),
    multiple_address_indicator character varying(1),
    price_paid numeric,
    proprietor_name_1 text,
    company_registration_no_1 character varying(36),
    proprietorship_category_1 character varying(74),
    proprietor_1_address_1 character varying(187),
    proprietor_1_address_2 character varying(177),
    proprietor_1_address_3 character varying(156),
    proprietor_name_2 character varying(75),
    company_registration_no_2 character varying(27),
    proprietorship_category_2 character varying(49),
    proprietor_2_address_1 character varying(128),
    proprietor_2_address_2 character varying(118),
    proprietor_2_address_3 character varying(62),
    proprietor_name_3 character varying(73),
    company_registration_no_3 character varying(9),
    proprietorship_category_3 character varying(49),
    proprietor_3_address_1 character varying(92),
    proprietor_3_address_2 character varying(39),
    proprietor_3_address_3 date,
    proprietor_name_4 character varying(21),
    company_registration_no_4 numeric,
    proprietorship_category_4 character varying(49),
    proprietor_4_address_1 character varying(81),
    proprietor_4_address_2 date,
    proprietor_4_address_3 date,
    date_proprietor_added character varying(12),
    additional_proprietor_indicator character varying(1),
    change_indicator character varying(1),
    change_date character varying(12)
);


--
-- Name: ccod_full_2025_10_import_20251013; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_full_2025_10_import_20251013 (
    title_number character varying(10),
    tenure character varying(10),
    property_address text,
    district character varying(42),
    county character varying(42),
    region character varying(19),
    postcode character varying(9),
    multiple_address_indicator character varying(1),
    price_paid numeric,
    proprietor_name_1 text,
    company_registration_no_1 character varying(36),
    proprietorship_category_1 character varying(74),
    proprietor_1_address_1 text,
    proprietor_1_address_2 character varying(240),
    proprietor_1_address_3 character varying(223),
    proprietor_name_2 character varying(255),
    company_registration_no_2 character varying(34),
    proprietorship_category_2 character varying(58),
    proprietor_2_address_1 text,
    proprietor_2_address_2 character varying(148),
    proprietor_2_address_3 character varying(133),
    proprietor_name_3 character varying(190),
    company_registration_no_3 character varying(10),
    proprietorship_category_3 character varying(49),
    proprietor_3_address_1 character varying(176),
    proprietor_3_address_2 character varying(118),
    proprietor_3_address_3 character varying(86),
    proprietor_name_4 character varying(78),
    company_registration_no_4 character varying(9),
    proprietorship_category_4 character varying(49),
    proprietor_4_address_1 character varying(110),
    proprietor_4_address_2 character varying(82),
    proprietor_4_address_3 character varying(82),
    date_proprietor_added character varying(12),
    additional_proprietor_indicator character varying(1)
);


--
-- Name: ccod_to_streets; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ccod_to_streets (
    title_number character varying(10),
    district character varying(42),
    property_address text,
    designatedname1 text
);


--
-- Name: leases_full_2025_10_import_20251013; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.leases_full_2025_10_import_20251013 (
    unique_identifier character varying(48),
    tenure character varying(10),
    register_property_description text,
    county character varying(42),
    region character varying(19),
    associated_property_description_id numeric,
    associated_property_description character varying(210),
    os_uprn numeric,
    price_paid numeric,
    reg_order numeric,
    date_of_lease character varying(12),
    term text,
    alienation_clause_indicator character varying(1)
);


--
-- Name: ocod_full_2025_10_import_20251013; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.ocod_full_2025_10_import_20251013 (
    title_number character varying(10),
    tenure character varying(10),
    property_address text,
    district character varying(42),
    county character varying(42),
    region character varying(19),
    postcode character varying(9),
    multiple_address_indicator character varying(1),
    price_paid numeric,
    proprietor_name_1 character varying(225),
    company_registration_no_1 character varying(34),
    proprietorship_category_1 character varying(49),
    country_incorporated_1 character varying(130),
    proprietor_1_address_1 character varying(240),
    proprietor_1_address_2 character varying(170),
    proprietor_1_address_3 character varying(194),
    proprietor_name_2 character varying(74),
    company_registration_no_2 character varying(14),
    proprietorship_category_2 character varying(49),
    country_incorporated_2 character varying(26),
    proprietor_2_address_1 character varying(176),
    proprietor_2_address_2 character varying(145),
    proprietor_2_address_3 character varying(129),
    proprietor_name_3 character varying(34),
    company_registration_no_3 character varying(70),
    proprietorship_category_3 character varying(49),
    country_incorporated_3 character varying(26),
    proprietor_3_address_1 character varying(109),
    proprietor_3_address_2 character varying(70),
    proprietor_3_address_3 character varying(70),
    proprietor_name_4 character varying(33),
    company_registration_no_4 character varying(70),
    proprietorship_category_4 character varying(49),
    country_incorporated_4 character varying(26),
    proprietor_4_address_1 character varying(102),
    proprietor_4_address_2 character varying(36),
    proprietor_4_address_3 character varying(70),
    date_proprietor_added character varying(12),
    additional_proprietor_indicator character varying(1)
);


--
-- Name: tbl_vis_premises_master_v2; Type: TABLE; Schema: landregistry; Owner: -
--

CREATE TABLE landregistry.tbl_vis_premises_master_v2 (
    altnets character varying(200),
    premise_count bigint
);


--
-- Name: chamber; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.chamber (
    objectid bigint NOT NULL,
    geom public.geometry(Point,27700),
    longitude character varying(200),
    latitude character varying(200),
    "CATEGORY_NAME" character varying(200),
    "DP_IDENTIFIER" character varying(200),
    "EXCHANGE_1141_CODE" character varying(200),
    "INVENTORY_STATUS_CODE" character varying(200),
    "OBJECT_CLASS" character varying(200),
    "OBJECT_ID" character varying(200),
    "OPIS_SERIAL_NO" character varying(200),
    "PLANT_ITEM" character varying(200),
    "STRUCTURE_NAME" character varying(200),
    "TYPE_NAME" character varying(200),
    geometry_coordinates character varying(2048),
    id character varying(20),
    globalid character varying(2048)
);


--
-- Name: duct; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint NOT NULL,
    shape public.geometry,
    shape_length double precision,
    import_id bigint,
    dir character varying,
    source bigint,
    target bigint,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    component_id integer,
    CONSTRAINT enforce_bounds CHECK ((shape OPERATOR(public.&&) public.st_makeenvelope((2126.06403809413)::double precision, (1529.05000358)::double precision, (655523.587002702)::double precision, (1217076.37801362)::double precision, 27700))),
    CONSTRAINT enforce_geotype_shape CHECK ((public.geometrytype(shape) = 'LINESTRING'::text)),
    CONSTRAINT enforce_srid_shape CHECK ((public.st_srid(shape) = 27700))
);


--
-- Name: duct_road_crossing_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_road_crossing_test (
    objectid bigint,
    is_road_crossing boolean
);


--
-- Name: duct_segment_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_segment_test (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint,
    shape_length double precision,
    import_id bigint,
    dir character varying,
    source bigint,
    target bigint,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    pgr_process_datetime timestamp with time zone,
    datasource character varying,
    import_datetime timestamp with time zone,
    component_id integer,
    geom public.geometry,
    id bigint NOT NULL
);


--
-- Name: duct_segment_test_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.duct_segment_test_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: duct_segment_test_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.duct_segment_test_id_seq OWNED BY openreach.duct_segment_test.id;


--
-- Name: duct_segment_test_vertices_pgr; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_segment_test_vertices_pgr (
    id bigint NOT NULL,
    cnt integer,
    chk integer,
    ein integer,
    eout integer,
    the_geom public.geometry(Point,27700)
);


--
-- Name: duct_segment_test_vertices_pgr_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.duct_segment_test_vertices_pgr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: duct_segment_test_vertices_pgr_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.duct_segment_test_vertices_pgr_id_seq OWNED BY openreach.duct_segment_test_vertices_pgr.id;


--
-- Name: duct_segmment_test_vertices_pgr_main_component; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_segmment_test_vertices_pgr_main_component (
    id bigint
);


--
-- Name: duct_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_test (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint NOT NULL,
    shape public.geometry,
    shape_length double precision,
    import_id bigint,
    dir character varying,
    source bigint,
    target bigint,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    pgr_process_datetime timestamp with time zone,
    datasource character varying,
    import_datetime timestamp with time zone,
    component_id integer
);


--
-- Name: duct_test_vertices_pgr; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_test_vertices_pgr (
    id bigint NOT NULL,
    cnt integer,
    chk integer,
    ein integer,
    eout integer,
    the_geom public.geometry(Point,27700)
);


--
-- Name: duct_test_vertices_pgr_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.duct_test_vertices_pgr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: duct_test_vertices_pgr_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.duct_test_vertices_pgr_id_seq OWNED BY openreach.duct_test_vertices_pgr.id;


--
-- Name: duct_ursn_map_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_ursn_map_test (
    objectid bigint,
    shape public.geometry,
    usrn bigint
);


--
-- Name: duct_vertice_pgr_ursn_map_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_vertice_pgr_ursn_map_test (
    id bigint,
    the_geom public.geometry(Point,27700),
    usrn bigint
);


--
-- Name: duct_vertices_pgr; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.duct_vertices_pgr (
    id bigint NOT NULL,
    cnt integer,
    chk integer,
    ein integer,
    eout integer,
    the_geom public.geometry(Point,27700)
);


--
-- Name: duct_vertices_pgr_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.duct_vertices_pgr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: duct_vertices_pgr_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.duct_vertices_pgr_id_seq OWNED BY openreach.duct_vertices_pgr.id;


--
-- Name: exchanges; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.exchanges (
    css_db_exch character varying(12),
    exchange_name character varying(128),
    css_db character varying(12),
    css_exchange character varying(12),
    "1141_code" character varying(12),
    street character varying(64),
    town character varying(32),
    county character varying(32),
    post_code character varying(12),
    order_restriction_present text,
    order_restriction_implimentation_date character varying(12),
    last_amended_date character varying(12),
    tranche character varying(32),
    geom public.geometry
);


--
-- Name: import_duct; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.import_duct (
    id bigint NOT NULL,
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint,
    shape public.geometry
);


--
-- Name: import_duct_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.import_duct_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: import_duct_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.import_duct_id_seq OWNED BY openreach.import_duct.id;


--
-- Name: import_duct_orion; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.import_duct_orion (
    exchange_1141_code character varying,
    measured_length numeric,
    plant_item character varying,
    inventory_status_code character varying,
    object_class text,
    span_name character varying,
    objectid bigint,
    shape public.geometry,
    shape_length double precision,
    cost double precision,
    reverse_cost double precision,
    datasource text,
    import_datetime timestamp with time zone
);


--
-- Name: import_duct_orion_intersecting; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.import_duct_orion_intersecting (
    objectid bigint
);


--
-- Name: import_structure; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.import_structure (
    id bigint NOT NULL,
    exchange_1141_code character varying(255),
    object_id character varying(255),
    plant_item character varying(255),
    object_class character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    category_name character varying(255),
    structure_name character varying(255),
    ownership_type_code character varying(255),
    objectid bigint,
    dp_identifier character varying(255),
    opis_serial_no character varying(255),
    pole_type character varying(255),
    insp_date date,
    result_description text,
    result character varying(255),
    pole_year character varying(255),
    pole_class character varying(255),
    depot character varying(255),
    species character varying(255),
    sub_pole character varying(255),
    a1024_exists boolean,
    pole_age integer,
    shape public.geometry
);


--
-- Name: import_structure_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.import_structure_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: import_structure_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.import_structure_id_seq OWNED BY openreach.import_structure.id;


--
-- Name: live_mdu_equipment_vert_map; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.live_mdu_equipment_vert_map (
    objectid bigint,
    id bigint,
    distance double precision,
    linegeom public.geometry
);


--
-- Name: network_equipment_cab_map; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.network_equipment_cab_map (
    eqip_id integer,
    id bigint,
    distance double precision,
    linegeom public.geometry
);


--
-- Name: openreach_exchange_polygons; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.openreach_exchange_polygons (
    exchange_1141_code character varying(255) NOT NULL,
    geom public.geometry,
    polygon_datetime timestamp without time zone DEFAULT now(),
    alpha numeric
);


--
-- Name: openreach_scrape_grid; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.openreach_scrape_grid (
    id bigint,
    shape_geom public.geometry,
    center_geom public.geometry,
    long double precision,
    lat double precision,
    uprn_count bigint,
    borough character varying,
    duct_scrape_datetime timestamp without time zone,
    duct_scrape_feature_count integer,
    structure_scrape_datetime timestamp without time zone,
    structure_scrape_feature_count integer,
    duct_scrape_status character varying(20),
    structure_scrape_status character varying(20)
);


--
-- Name: or_dfx_geom; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.or_dfx_geom (
    ofcom_postcode character varying(8),
    mdf_id character varying(50),
    ofcom_exchange_name character varying(50),
    status character varying(50),
    region character varying(50),
    suitable_as_b_end_for_dfx character varying(10),
    geom public.geometry(Point,27700)
);


--
-- Name: or_dfx_postcode; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.or_dfx_postcode (
    ofcom_postcode character varying(8),
    mdf_id character varying(50),
    ofcom_exchange_name character varying(50),
    status character varying(50),
    region character varying(50),
    "suitable_as_b-end_for_dfx" character varying(10)
);


--
-- Name: pole; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.pole (
    objectid bigint NOT NULL,
    geom public.geometry(Point,27700),
    longitude character varying(200),
    latitude character varying(200),
    "A1024_EXISTS" character varying(200),
    "CATEGORY_NAME" character varying(200),
    "DEPOT" character varying(200),
    "DP_IDENTIFIER" character varying(200),
    "EXCHANGE_1141_CODE" character varying(200),
    "INSP_DATE" character varying(200),
    "INVENTORY_STATUS_CODE" character varying(200),
    "OBJECT_CLASS" character varying(200),
    "OPIS_SERIAL_NO" character varying(200),
    "PLANT_ITEM" character varying(200),
    "POLE_AGE" character varying(200),
    "POLE_CLASS" character varying(200),
    "POLE_TYPE" character varying(200),
    "POLE_YEAR" character varying(200),
    "RESULT" character varying(200),
    "RESULT_DESCRIPTION" character varying(200),
    "SPECIES" character varying(200),
    "STRUCTURE_NAME" character varying(200),
    "SUB_POLE" character varying(200),
    "TYPE_NAME" character varying(200),
    geometry_coordinates character varying(2048),
    id character varying(20),
    globalid character varying(2048)
);


--
-- Name: pole_to_vert_map; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.pole_to_vert_map (
    pole_objectid bigint,
    node_id bigint,
    distance double precision
);


--
-- Name: span_coupler; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.span_coupler (
    fid integer NOT NULL,
    objectid bigint,
    longitude character varying(200),
    latitude character varying(200),
    category_name character varying(200),
    exchange_1141_code character varying(200),
    inventory_status_code character varying(200),
    object_class character varying(200),
    opis_serial_no character varying(200),
    plant_item character varying(200),
    result character varying(200),
    result_description character varying(200),
    structure_name character varying(200),
    type_name character varying(200),
    geometry_coordinates character varying(2048),
    id character varying(20),
    globalid character varying(2048),
    geom public.geometry(Point,27700)
);


--
-- Name: span_coupler_fid_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.span_coupler_fid_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: span_coupler_fid_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.span_coupler_fid_seq OWNED BY openreach.span_coupler.fid;


--
-- Name: spans; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.spans (
    id integer NOT NULL,
    objectid bigint,
    category_name character varying(200),
    exchange_1141_code character varying(200),
    inventory_status_code character varying(200),
    measured_length character varying(200),
    number_of_ways character varying(200),
    object_class character varying(200),
    plant_item character varying(200),
    rag_status character varying(200),
    section_number character varying(200),
    span_name character varying(200),
    type_name character varying(200),
    id2 character varying(20),
    globalid character varying(2048),
    shape_length double precision,
    cost_factor integer,
    the_geom public.geometry(MultiLineString,27700),
    dir character varying,
    source bigint,
    target bigint,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision
);


--
-- Name: spans_fid_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.spans_fid_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: spans_fid_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.spans_fid_seq OWNED BY openreach.spans.id;


--
-- Name: spans_vertices_cab_map; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.spans_vertices_cab_map (
    cab_id bigint,
    id bigint,
    distance double precision,
    linegeom public.geometry
);


--
-- Name: spans_vertices_pgr; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.spans_vertices_pgr (
    id bigint NOT NULL,
    cnt integer,
    chk integer,
    ein integer,
    eout integer,
    the_geom public.geometry(Point,27700),
    object_type character varying,
    objectid integer
);


--
-- Name: spans_vertices_pgr_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.spans_vertices_pgr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: spans_vertices_pgr_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.spans_vertices_pgr_id_seq OWNED BY openreach.spans_vertices_pgr.id;


--
-- Name: structure; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.structure (
    exchange_1141_code character varying(255),
    object_id character varying(255),
    plant_item character varying(255),
    object_class character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    category_name character varying(255),
    structure_name character varying(255),
    ownership_type_code character varying(255),
    objectid bigint,
    dp_identifier character varying(255),
    opis_serial_no character varying(255),
    pole_type character varying(255),
    insp_date date,
    result_description text,
    result character varying(255),
    pole_year character varying(255),
    pole_class character varying(255),
    depot character varying(255),
    species character varying(255),
    sub_pole character varying(255),
    a1024_exists boolean,
    pole_age integer,
    shape public.geometry,
    import_id bigint,
    CONSTRAINT enforce_geotype_shape CHECK ((public.geometrytype(shape) = 'POINT'::text)),
    CONSTRAINT enforce_srid_shape CHECK ((public.st_srid(shape) = 27700))
);


--
-- Name: subset; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.subset (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint,
    shape public.geometry,
    shape_length double precision,
    import_id bigint,
    dir character varying,
    source bigint,
    target bigint,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    pgr_process_datetime timestamp with time zone,
    datasource character varying,
    import_datetime timestamp with time zone
);


--
-- Name: subset_vertices_pgr; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.subset_vertices_pgr (
    id bigint NOT NULL,
    cnt integer,
    chk integer,
    ein integer,
    eout integer,
    the_geom public.geometry(Point,27700)
);


--
-- Name: subset_vertices_pgr_id_seq; Type: SEQUENCE; Schema: openreach; Owner: -
--

CREATE SEQUENCE openreach.subset_vertices_pgr_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: subset_vertices_pgr_id_seq; Type: SEQUENCE OWNED BY; Schema: openreach; Owner: -
--

ALTER SEQUENCE openreach.subset_vertices_pgr_id_seq OWNED BY openreach.subset_vertices_pgr.id;


--
-- Name: temp_duct; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.temp_duct (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint,
    shape public.geometry,
    shape_length double precision,
    length_ratio numeric,
    straightness_ratio double precision,
    r bigint,
    c bigint,
    import_id bigint
);


--
-- Name: temp_duct_segment_test; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.temp_duct_segment_test (
    exchange_1141_code character varying(255),
    section_number character varying(255),
    measured_length numeric(10,2),
    number_of_ways integer,
    rag_status character(1),
    plant_item character varying(255),
    inventory_status_code character varying(255),
    type_name character varying(255),
    object_class character varying(255),
    category_name character varying(255),
    ownership_type_code character varying(255),
    span_name character varying(255),
    objectid bigint,
    shape_length double precision,
    import_id bigint,
    dir character varying,
    source text,
    target text,
    cost double precision,
    reverse_cost double precision,
    capacity bigint,
    reverse_capacity bigint,
    category_id integer,
    reverse_category_id integer,
    x1 double precision,
    y1 double precision,
    x2 double precision,
    y2 double precision,
    pgr_process_datetime timestamp with time zone,
    datasource character varying,
    import_datetime timestamp with time zone,
    component_id integer,
    geom public.geometry
);


--
-- Name: uk_map; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.uk_map (
    geom public.geometry
);


--
-- Name: uk_map_grid; Type: TABLE; Schema: openreach; Owner: -
--

CREATE TABLE openreach.uk_map_grid (
    id bigint,
    geom public.geometry
);


--
-- Name: vw_exchanges; Type: MATERIALIZED VIEW; Schema: openreach; Owner: -
--

CREATE MATERIALIZED VIEW openreach.vw_exchanges AS
 SELECT replace((e.css_db_exch)::text, '-'::text, ''::text) AS e,
    e.css_db_exch,
    e.exchange_name,
    e.css_db,
    e.css_exchange,
    e."1141_code",
    e.street,
    e.town,
    e.county,
    e.post_code,
        CASE
            WHEN (erd.order_restriction_implimentation_date IS NOT NULL) THEN 'Yes'::text
            ELSE 'No'::text
        END AS order_restriction_present,
    erd.order_restriction_implimentation_date,
    erd.last_amended_date,
    erd.tranche,
    oep.geom
   FROM ((adhoc.exchanges e
     LEFT JOIN adhoc.exchange_restriction_dates erd ON ((replace((e.css_db_exch)::text, '-'::text, ''::text) = (erd.exchange_code)::text)))
     JOIN openreach.openreach_exchange_polygons oep ON (((e."1141_code")::text = (oep.exchange_1141_code)::text)))
  WITH NO DATA;


--
-- Name: osmm_highways_street; Type: TABLE; Schema: ordnancesurvey; Owner: -
--

CREATE TABLE ordnancesurvey.osmm_highways_street (
    usrn character varying(20) NOT NULL,
    identifier bigint,
    beginlifespanversion timestamp without time zone,
    validfrom timestamp without time zone,
    localroadcode character varying(30),
    nationalroadcode character varying(10),
    descriptor1 character varying(30),
    descriptor1_language character varying(10),
    descriptor2 character varying(30),
    descriptor2_language character varying(10),
    designatedname1 character varying(30),
    designatedname1_language character varying(10),
    designatedname1_authorityid character varying(10),
    designatedname1_authority character varying(30),
    designatedname2 character varying(30),
    designatedname2_language character varying(10),
    designatedname2_authorityid character varying(10),
    designatedname2_authority character varying(30),
    localname1 character varying(30),
    localname1_language character varying(10),
    localname2 character varying(30),
    localname2_language character varying(10),
    reasonforchange character varying(30),
    roadclassification character varying(30),
    streettype character varying(30),
    operationalstate_state character varying(30),
    operationsstate_reason character varying(30),
    operationalstate_starttime timestamp without time zone,
    operationalstate_endtime timestamp without time zone,
    locality1 character varying(30),
    locality1_language character varying(10),
    locality2 character varying(30),
    locality2_language character varying(10),
    town1 character varying(30),
    town1_language character varying(10),
    town2 character varying(30),
    town2_language character varying(10),
    responsibleauthority_authorityid character varying(10),
    responsibleauthority_authority character varying(30),
    administrativearea1 character varying(30),
    administrativearea1_language character varying(10),
    administrativearea2 character varying(30),
    administrativearea2_language character varying(10),
    gsscode1 character varying(10),
    gsscode2 character varying(10),
    geometryprovenance character varying(30),
    geom_varbinary bytea,
    geom public.geometry(Geometry,4326),
    street_words text[] GENERATED ALWAYS AS (public.address_to_words((designatedname1)::text)) STORED
);


--
-- Name: osmm_highways_street_street_names; Type: TABLE; Schema: ordnancesurvey; Owner: -
--

CREATE TABLE ordnancesurvey.osmm_highways_street_street_names (
    name_id bigint,
    designatedname1 text,
    street_words text[],
    designatedname1_authority text[],
    street_count bigint
);


--
-- Name: uprn_usrn_map; Type: TABLE; Schema: ordnancesurvey; Owner: -
--

CREATE TABLE ordnancesurvey.uprn_usrn_map (
    uprn bigint,
    usrn bigint
);


--
-- Name: project_field_sub_category; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_sub_category (
    field_sub_category_id integer NOT NULL,
    field_sub_category_name character varying NOT NULL,
    field_sub_category_ref character varying
);


--
-- Name: newtable_field_sub_category_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.newtable_field_sub_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: newtable_field_sub_category_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.newtable_field_sub_category_id_seq OWNED BY projects.project_field_sub_category.field_sub_category_id;


--
-- Name: project_access_log; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_access_log (
    id bigint NOT NULL,
    project_id bigint,
    user_id integer,
    access_type character varying(50),
    access_datetime timestamp without time zone DEFAULT now()
);


--
-- Name: project_access_log_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_access_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_access_log_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_access_log_id_seq OWNED BY projects.project_access_log.id;


--
-- Name: project_field_category; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_category (
    field_category_id integer NOT NULL,
    field_category_name character varying NOT NULL,
    field_category_ref character varying,
    field_section_id integer,
    field_category_display_order integer
);


--
-- Name: project_field_category_field_category_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_category_field_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_category_field_category_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_category_field_category_id_seq OWNED BY projects.project_field_category.field_category_id;


--
-- Name: project_field_dropdown_options; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_dropdown_options (
    dropdown_id bigint NOT NULL,
    field_id integer NOT NULL,
    dropdown_value character varying,
    dropdown_name character varying
);


--
-- Name: project_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_dropdown_options_dropdown_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_dropdown_options_dropdown_id_seq OWNED BY projects.project_field_dropdown_options.dropdown_id;


--
-- Name: project_field_sections; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_sections (
    field_section_id integer NOT NULL,
    field_section_name character varying,
    field_section_display_order integer,
    field_section_ref character varying,
    field_section_icon character varying(50)
);


--
-- Name: project_field_sections_field_section_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_sections_field_section_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_sections_field_section_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_sections_field_section_id_seq OWNED BY projects.project_field_sections.field_section_id;


--
-- Name: project_field_values_boolean; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_boolean (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_boolean_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_boolean_history (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_boolean_history_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_boolean_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_boolean_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_boolean_history_value_id_seq OWNED BY projects.project_field_values_boolean_history.value_id;


--
-- Name: project_field_values_boolean_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_boolean_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_boolean_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_boolean_value_id_seq OWNED BY projects.project_field_values_boolean.value_id;


--
-- Name: project_field_values_date; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_date (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_date_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_date_history (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_date_history_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_date_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_date_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_date_history_value_id_seq OWNED BY projects.project_field_values_date_history.value_id;


--
-- Name: project_field_values_date_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_date_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_date_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_date_value_id_seq OWNED BY projects.project_field_values_date.value_id;


--
-- Name: project_field_values_int; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_int (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_int_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_int_history (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_int_history_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_int_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_int_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_int_history_value_id_seq OWNED BY projects.project_field_values_int_history.value_id;


--
-- Name: project_field_values_int_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_int_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_int_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_int_value_id_seq OWNED BY projects.project_field_values_int.value_id;


--
-- Name: project_field_values_numeric; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_numeric (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_numeric_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_numeric_history (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_numeric_history_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_numeric_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_numeric_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_numeric_history_value_id_seq OWNED BY projects.project_field_values_numeric_history.value_id;


--
-- Name: project_field_values_numeric_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_numeric_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_numeric_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_numeric_value_id_seq OWNED BY projects.project_field_values_numeric.value_id;


--
-- Name: project_field_values_text; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_text (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_text_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_field_values_text_history (
    value_id bigint NOT NULL,
    field_id integer,
    project_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: project_field_values_text_history_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_text_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_text_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_text_history_value_id_seq OWNED BY projects.project_field_values_text_history.value_id;


--
-- Name: project_field_values_text_value_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_field_values_text_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_field_values_text_value_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_field_values_text_value_id_seq OWNED BY projects.project_field_values_text.value_id;


--
-- Name: project_fields; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_fields (
    field_id bigint NOT NULL,
    field_section integer,
    field_category integer,
    field_sub_category integer,
    field_display_order integer,
    field_data_type character varying,
    field_name character varying,
    field_input_type character varying,
    field_form_id character varying,
    field_required boolean,
    field_active boolean DEFAULT true,
    field_spacer_after integer,
    field_type character varying(10)
);


--
-- Name: project_fields_field_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_fields_field_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_fields_field_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_fields_field_id_seq OWNED BY projects.project_fields.field_id;


--
-- Name: project_file_uploads; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_file_uploads (
    id bigint NOT NULL,
    file_name character varying(100),
    file_type character varying(50),
    file_description integer,
    file_size numeric,
    upload_user integer,
    upload_datetime timestamp without time zone,
    is_deleted boolean DEFAULT false,
    uuid uuid,
    project_id integer
);


--
-- Name: project_file_uploads_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_file_uploads_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_file_uploads_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_file_uploads_id_seq OWNED BY projects.project_file_uploads.id;


--
-- Name: project_journal; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_journal (
    id bigint NOT NULL,
    project_id bigint,
    user_id integer,
    log_datetime timestamp without time zone,
    log_text text
);


--
-- Name: project_journal_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.project_journal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: project_journal_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.project_journal_id_seq OWNED BY projects.project_journal.id;


--
-- Name: project_status; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_status (
    project_status_id integer,
    project_status_desc character varying(100)
);


--
-- Name: project_nearest_neighbours; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.project_nearest_neighbours AS
 SELECT bci.project_id,
    n.neighbour_id,
    n.project_name,
    n.geom,
    n.distance,
    n.project_status,
    n.account_id,
    n.account_name
   FROM (projects.projects bci
     CROSS JOIN LATERAL ( SELECT b.project_id AS neighbour_id,
            b.project_name,
            COALESCE(ps.project_status_desc, 'Status Not Set'::character varying) AS project_status,
            a.account_id,
            a.account_name,
            b.geom,
            round((public.st_distance(b.geom, bci.geom))::numeric, 2) AS distance
           FROM ((projects.projects b
             LEFT JOIN projects.project_status ps ON ((b.project_status_id = ps.project_status_id)))
             LEFT JOIN accounts.accounts a ON ((b.account_id = a.account_id)))
          WHERE ((b.project_id <> bci.project_id) AND (b.geom OPERATOR(public.&&) public.st_expand(bci.geom, (5000)::double precision)) AND (public.st_distance(b.geom, bci.geom) < (5000)::double precision))) n);


--
-- Name: project_premises; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.project_premises (
    uprn bigint,
    project_id integer
);


--
-- Name: projects_history; Type: TABLE; Schema: projects; Owner: -
--

CREATE TABLE projects.projects_history (
    history_id integer NOT NULL,
    project_id integer NOT NULL,
    parent_project_id integer,
    project_name character varying(150),
    project_status_id integer,
    project_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone,
    created_user integer,
    modified_datetime timestamp without time zone,
    modified_user integer,
    company_id integer,
    account_id integer,
    stocklist_id integer,
    cover_image_url character varying(100),
    history_action character varying(10) NOT NULL,
    history_datetime timestamp without time zone DEFAULT now() NOT NULL,
    history_user integer
);


--
-- Name: projects_history_history_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.projects_history_history_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: projects_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.projects_history_history_id_seq OWNED BY projects.projects_history.history_id;


--
-- Name: projects_project_id_seq; Type: SEQUENCE; Schema: projects; Owner: -
--

CREATE SEQUENCE projects.projects_project_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: projects_project_id_seq; Type: SEQUENCE OWNED BY; Schema: projects; Owner: -
--

ALTER SEQUENCE projects.projects_project_id_seq OWNED BY projects.projects.project_id;


--
-- Name: vw_file_uploads; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.vw_file_uploads AS
 SELECT fu.file_id,
    to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
    u.username,
    fu.file_name,
    fu.file_type,
    fu.project_id,
    fu.file_category,
    fu.file_description,
    fu.file_size
   FROM (public.file_uploads fu
     LEFT JOIN users.users u ON ((fu.file_upload_user = u.id)))
  WHERE ((fu.entity)::text = 'project'::text)
  ORDER BY fu.file_upload_datetime DESC;


--
-- Name: vw_parent_project_links; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.vw_parent_project_links AS
 SELECT p.project_id,
    p.project_name,
    p.parent_project_id,
    pp.project_name AS parent_project_name,
    public.st_makeline(public.st_centroid(p.geom), public.st_centroid(pp.geom)) AS geom,
    round((public.st_distance(public.st_centroid(p.geom), public.st_centroid(pp.geom)))::numeric, 0) AS distance
   FROM (projects.projects p
     JOIN projects.projects pp ON ((p.parent_project_id = pp.project_id)));


--
-- Name: vw_projects_full_information; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.vw_projects_full_information AS
 SELECT p.project_id,
    p.parent_project_id,
    p.project_name,
    p.project_status_id,
    p.project_manager,
    p.geom,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    p.company_id,
    p.account_id,
    p.stocklist_id,
    p.cover_image_url,
    ps.project_status_desc,
    s.stocklist_name,
    a.account_name
   FROM (((projects.projects p
     LEFT JOIN projects.project_status ps ON ((p.project_status_id = ps.project_status_id)))
     LEFT JOIN stocklists.stocklists s ON ((p.stocklist_id = s.stocklist_id)))
     LEFT JOIN accounts.accounts a ON ((p.project_id = a.account_id)));


--
-- Name: vw_projects_geom; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.vw_projects_geom AS
 SELECT p.project_id,
    p.parent_project_id,
    p.project_name,
    p.project_status_id,
    p.project_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS project_manager_name,
    p.company_id,
    c.company_name,
    p.account_id,
    a.account_name,
    p.cover_image_url,
    p.geom,
        CASE
            WHEN ((p.project_status_id = 100) AND (NULLIF(vt.value, ''::text) IS NOT NULL)) THEN 'purple'::text
            WHEN (p.project_status_id = 100) THEN 'green'::text
            WHEN (p.project_status_id = 0) THEN 'black'::text
            WHEN (p.project_status_id = 5) THEN 'red'::text
            ELSE 'blue'::text
        END AS polygon_style
   FROM ((((projects.projects p
     LEFT JOIN users.users u ON ((p.project_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((p.account_id = a.account_id)))
     LEFT JOIN projects.project_field_values_text vt ON (((p.project_id = vt.project_id) AND (vt.field_id = 21))));


--
-- Name: vw_projects_list; Type: VIEW; Schema: projects; Owner: -
--

CREATE VIEW projects.vw_projects_list AS
 WITH prems AS (
         SELECT p_1.project_id,
            count(*) AS total_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'MDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SMDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS smdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises,
            count(
                CASE
                    WHEN (bd.uprn IS NOT NULL) THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers,
            count(
                CASE
                    WHEN ((bd.uprn IS NOT NULL) AND (bt.building_type = 'MDU'::text)) THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers_mdu
           FROM (((projects.projects p_1
             LEFT JOIN basedata.abp ON (public.st_intersects(p_1.geom, abp.geom)))
             LEFT JOIN basedata.toid_building_type bt ON (((abp.cross_reference)::text = (bt.toid)::text)))
             LEFT JOIN adhoc.bduk_uprn_tracking_v2 bd ON ((abp.uprn = bd.uprn)))
          GROUP BY p_1.project_id
        )
 SELECT p.project_id,
    COALESCE((p.parent_project_id)::text, ''::text) AS parent_project_id,
    COALESCE((pp.project_name)::text, ''::text) AS parent_project_name,
    p.project_name,
    p.project_status_id,
    COALESCE(ps.project_status_desc, ''::character varying) AS project_status_desc,
    p.project_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    to_char(p.created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS created_date,
    p.created_user,
    COALESCE(uc.username, ''::character varying) AS created_username,
    to_char(p.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    p.modified_user,
    um.username AS modified_username,
    COALESCE(c.company_name, ''::character varying) AS contractor_name,
    p.company_id,
    p.account_id,
    COALESCE(a.account_name, ''::character varying) AS account_name,
    prems.total_premises,
    prems.mdu_premises,
    prems.smdu_premises,
    prems.sdu_premises,
    prems.bduk_vouchers,
    prems.bduk_vouchers_mdu
   FROM ((((((((projects.projects p
     LEFT JOIN users.users um ON ((p.modified_user = um.id)))
     LEFT JOIN users.users uc ON ((p.created_user = uc.id)))
     LEFT JOIN users.users upm ON ((p.project_manager = upm.id)))
     LEFT JOIN projects.project_status ps ON ((p.project_status_id = ps.project_status_id)))
     LEFT JOIN projects.projects pp ON ((p.parent_project_id = pp.project_id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((a.account_id = p.account_id)))
     LEFT JOIN prems ON ((p.project_id = prems.project_id)));


--
-- Name: estimates; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.estimates (
    project_id integer,
    costing_datetime timestamp with time zone,
    total_premises numeric,
    mdu_premises numeric,
    mdu_with_wayleave numeric,
    sdu_premises numeric,
    mdu_buildings bigint,
    sdu_buildings bigint,
    pia_oh_premises numeric,
    pia_ug_l_premises numeric,
    pia_ug_c_premises numeric,
    civils_premises numeric,
    pia_oh_mdu_premises numeric,
    pia_oh_sdu_premises numeric,
    pia_ug_l_mdu_premises numeric,
    pia_ug_l_sdu_premises numeric,
    pia_ug_c_mdu_premises numeric,
    pia_ug_c_sdu_premises numeric,
    civils_mdu_premises numeric,
    civils_sdu_premises numeric,
    cab_name character varying,
    total_distance numeric,
    cluster_capex_budget numeric,
    mdu_low_capex numeric,
    sdu_low_capex numeric,
    capacity_low_capex numeric,
    civils_low_capex numeric,
    pia_ug_low_capex numeric,
    pia_oh_low_capex numeric,
    mdu_high_capex numeric,
    sdu_high_capex numeric,
    capacity_high_capex numeric,
    civils_high_capex numeric,
    pia_ug_high_capex numeric,
    pia_oh_high_capex numeric,
    mdu_wayleave_percent numeric,
    capex_low numeric,
    cppp_low numeric,
    capex_high numeric,
    cppp_high numeric,
    percent_ugl_to_ugc character varying(100),
    fibre_count integer,
    distribution_distance numeric,
    distribution_capex_low numeric,
    distribution_cppp_low numeric,
    distribution_capex_high numeric,
    distribution_cppp_high numeric
);


--
-- Name: network_cables; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_cables (
    cable_id bigint NOT NULL,
    project_id integer,
    cable_type character varying,
    cable_length double precision,
    a_end bigint,
    b_end bigint,
    geom_user public.geometry,
    geom public.geometry,
    cable_datetime timestamp with time zone,
    cable_user bigint,
    cable_ref character varying(50),
    pianoi_ref character varying(50),
    cable_status character varying(25),
    customer_cable_id character varying(100),
    fibre_allocation text
);


--
-- Name: network_cables_cable_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_cables_cable_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_cables_cable_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_cables_cable_id_seq OWNED BY prospector.network_cables.cable_id;


--
-- Name: network_cables_history; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_cables_history (
    history_id bigint NOT NULL,
    cable_id bigint NOT NULL,
    project_id integer,
    cable_type character varying,
    cable_length double precision,
    a_end bigint,
    b_end bigint,
    geom_user public.geometry,
    geom public.geometry,
    cable_datetime timestamp with time zone,
    cable_user bigint,
    cable_ref character varying(50),
    pianoi_ref character varying(50),
    cable_status character varying(25),
    revision_operation character(1) NOT NULL,
    revision_datetime timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    revision_id integer NOT NULL,
    revision_user text,
    customer_cable_id character varying(100),
    fibre_allocation text
);


--
-- Name: network_cables_history_history_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_cables_history_history_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_cables_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_cables_history_history_id_seq OWNED BY prospector.network_cables_history.history_id;


--
-- Name: network_duct; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_duct (
    duct_id bigint NOT NULL,
    project_id integer,
    duct_type character varying,
    duct_length double precision,
    a_end bigint,
    b_end bigint,
    geom_user public.geometry,
    geom public.geometry,
    duct_datetime timestamp with time zone,
    duct_user bigint,
    duct_ref character varying(50),
    duct_status character varying(25),
    customer_duct_id character varying(100)
);


--
-- Name: network_duct_duct_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_duct_duct_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_duct_duct_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_duct_duct_id_seq OWNED BY prospector.network_duct.duct_id;


--
-- Name: network_points; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_points (
    point_id integer NOT NULL,
    point_type character varying(20),
    point_name character varying(100),
    project_id integer,
    parent_point_id integer,
    geom public.geometry,
    pianoi_ref character varying(50),
    point_status character varying(25),
    customer_point_id character varying(100),
    point_address character varying(500),
    point_fibre_allocation_details text,
    sld_x double precision,
    sld_y double precision
);


--
-- Name: network_points_point_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_points_point_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_points_point_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_points_point_id_seq OWNED BY prospector.network_points.point_id;


--
-- Name: network_routes_delete; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_routes_delete (
    project_id integer,
    route_id bigint,
    route_type character varying,
    route_length double precision,
    route_datetime timestamp with time zone,
    geom public.geometry,
    cable_ref character varying(50),
    route_status character varying(25)
);


--
-- Name: network_structures; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_structures (
    structure_id integer NOT NULL,
    structure_type character varying(20),
    structure_name character varying(100),
    project_id integer,
    parent_structure_id integer,
    geom public.geometry,
    structure_status character varying(25),
    customer_structure_id character varying(100)
);


--
-- Name: network_structures_structure_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_structures_structure_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_structures_structure_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_structures_structure_id_seq OWNED BY prospector.network_structures.structure_id;


--
-- Name: network_subduct; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.network_subduct (
    subduct_id bigint NOT NULL,
    project_id integer,
    subduct_type character varying,
    subduct_length double precision,
    a_end bigint,
    b_end bigint,
    geom_user public.geometry,
    geom public.geometry,
    subduct_datetime timestamp with time zone,
    subduct_user bigint,
    subduct_ref character varying(50),
    pianoi_ref character varying(50),
    subduct_status character varying(25),
    customer_subduct_id character varying(100)
);


--
-- Name: network_subduct_subduct_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.network_subduct_subduct_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: network_subduct_subduct_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.network_subduct_subduct_id_seq OWNED BY prospector.network_subduct.subduct_id;


--
-- Name: network_topo_link; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.network_topo_link AS
 SELECT nc.project_id,
    concat(nc.cable_id, ' (', nc.a_end, ' -> ', nc.b_end, ')') AS id,
    nc.a_end AS source,
    nc.b_end AS target,
    'link'::text AS type,
    false AS grabbable,
    false AS pannable,
    concat(COALESCE(nc.customer_cable_id, nc.cable_ref, 'Unnamed'::character varying), ' | ', COALESCE(nc.cable_type, 'Unk'::character varying), ' | ', (round((public.st_length(nc.geom_user))::numeric, 0))::text, 'm') AS fibres,
    concat(nc.pianoi_ref, '') AS pianoi_ref,
    nc.fibre_allocation
   FROM ((prospector.network_cables nc
     JOIN prospector.network_points nps ON ((nc.a_end = nps.point_id)))
     JOIN prospector.network_points npt ON ((nc.b_end = npt.point_id)))
  WHERE (
        CASE
            WHEN ((nc.a_end IS NULL) AND (nc.b_end IS NOT NULL)) THEN 1
            WHEN ((nc.a_end IS NOT NULL) AND (nc.b_end IS NOT NULL)) THEN 1
            ELSE 0
        END = 1);


--
-- Name: network_topo_point; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.network_topo_point AS
 SELECT np.project_id,
    np.point_id AS id,
    np.point_type AS type,
    COALESCE(np.point_name, (('Unnamed '::text || (np.point_type)::text))::character varying) AS point_name,
    'square'::text AS classes,
    true AS grabbable,
    false AS pannable,
    np.point_address,
    np.point_fibre_allocation_details,
    np.sld_x AS x,
    np.sld_y AS y
   FROM prospector.network_points np
  WHERE ((np.point_type)::text = 'L0'::text)
UNION ALL
 SELECT np.project_id,
    np.point_id AS id,
    np.point_type AS type,
    COALESCE(np.point_name, (('Unnamed '::text || (np.point_type)::text))::character varying) AS point_name,
    COALESCE(replace(replace(replace(replace((np.point_type)::text, '-'::text, '_'::text), ':'::text, '_'::text), '('::text, '_'::text), ')'::text, '_'::text), 'UNK'::text) AS classes,
    true AS grabbable,
    false AS pannable,
    np.point_address,
    np.point_fibre_allocation_details,
    np.sld_x AS x,
    np.sld_y AS y
   FROM prospector.network_points np
  WHERE ((np.point_type)::text <> 'L0'::text);


--
-- Name: opportunity; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.opportunity (
    opportunity_id bigint NOT NULL,
    opportunity_name character varying(200),
    opportunity_created_datetime timestamp without time zone DEFAULT now(),
    opportunity_created_user integer,
    opportunity_modified_datetime timestamp without time zone DEFAULT now(),
    opportunity_modified_user integer,
    geom public.geometry
);


--
-- Name: opportunity_access_log; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.opportunity_access_log (
    id bigint NOT NULL,
    opportunity_id bigint,
    user_id integer,
    access_type character varying(50),
    access_datetime timestamp without time zone DEFAULT now()
);


--
-- Name: opportunity_access_log_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.opportunity_access_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: opportunity_access_log_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.opportunity_access_log_id_seq OWNED BY prospector.opportunity_access_log.id;


--
-- Name: opportunity_opportunity_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.opportunity_opportunity_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: opportunity_opportunity_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.opportunity_opportunity_id_seq OWNED BY prospector.opportunity.opportunity_id;


--
-- Name: opportunity_project_link; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.opportunity_project_link (
    id bigint NOT NULL,
    opportunity_id bigint,
    project_id bigint,
    link_date timestamp without time zone,
    link_active boolean
);


--
-- Name: opportunity_project_link_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.opportunity_project_link_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: opportunity_project_link_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.opportunity_project_link_id_seq OWNED BY prospector.opportunity_project_link.id;


--
-- Name: opportunity_routes; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.opportunity_routes (
    opportunity_id bigint,
    project_id bigint,
    route_id integer,
    seq integer,
    path_seq integer,
    node bigint,
    edge bigint,
    cost double precision,
    agg_cost double precision,
    shape_length double precision
);


--
-- Name: pia_blockages; Type: TABLE; Schema: prospector; Owner: -
--

CREATE TABLE prospector.pia_blockages (
    blockage_id integer NOT NULL,
    blockage_type character varying(20),
    blockage_name character varying(100),
    blockage_status character varying(100),
    blockage_status_date date,
    project_id integer,
    geom public.geometry,
    pianoi_ref character varying(50),
    blockage_address text,
    blockage_contact_details character varying(200),
    blockage_requested_access_date date
);


--
-- Name: pia_blockages_blockage_id_seq; Type: SEQUENCE; Schema: prospector; Owner: -
--

CREATE SEQUENCE prospector.pia_blockages_blockage_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: pia_blockages_blockage_id_seq; Type: SEQUENCE OWNED BY; Schema: prospector; Owner: -
--

ALTER SEQUENCE prospector.pia_blockages_blockage_id_seq OWNED BY prospector.pia_blockages.blockage_id;


--
-- Name: vw_cabinet_locations; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_cabinet_locations AS
 SELECT np.point_id,
    np.point_type,
    np.point_name,
    np.project_id,
    np.parent_point_id,
    np.geom,
    np.pianoi_ref,
    np.point_status,
    np.customer_point_id,
    np.point_address,
    np.point_fibre_allocation_details,
    np.sld_x,
    np.sld_y
   FROM prospector.network_points np
  WHERE ((np.point_type)::text = 'L0'::text);


--
-- Name: vw_network_cable_export; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_network_cable_export AS
 SELECT nc.cable_id,
    nc.project_id,
    COALESCE(nc.cable_type, 'Not Set'::character varying) AS cable_type,
    round((nc.cable_length)::numeric, 2) AS cable_length,
    nc.a_end,
    nc.b_end,
    nc.geom_user,
    nc.geom,
    nc.cable_datetime,
    nc.cable_user,
    nc.cable_ref,
    COALESCE(nc.pianoi_ref, ''::character varying) AS pianoi_ref,
    nc.cable_status,
        CASE
            WHEN ((NULLIF((nc.customer_cable_id)::text, ''::text) IS NULL) AND (NULLIF((nc.cable_ref)::text, ''::text) IS NULL)) THEN (('Unnamed Cable ('::text || nc.cable_id) || ')'::text)
            WHEN (NULLIF((nc.customer_cable_id)::text, ''::text) IS NULL) THEN ((nc.cable_ref)::text || ' (Cust. ID Not Set)'::text)
            WHEN (NULLIF((nc.customer_cable_id)::text, ''::text) IS NOT NULL) THEN (nc.customer_cable_id)::text
            ELSE 'Unknown Cable'::text
        END AS customer_cable_id,
    COALESCE(npa.point_name, 'Not Set'::character varying) AS a_end_name,
    COALESCE(npa.customer_point_id, 'Not Set'::character varying) AS customer_a_end_name,
    COALESCE(npb.point_name, 'Not Set'::character varying) AS b_end_name,
    COALESCE(npb.customer_point_id, 'Not Set'::character varying) AS customer_b_end_name,
    COALESCE(nc.fibre_allocation, ''::text) AS fibre_allocation
   FROM ((prospector.network_cables nc
     LEFT JOIN prospector.network_points npa ON ((npa.point_id = nc.a_end)))
     LEFT JOIN prospector.network_points npb ON ((npb.point_id = nc.b_end)));


--
-- Name: vw_opportunity_list; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_opportunity_list AS
 WITH r AS (
         SELECT o_1.opportunity_id,
            o_1.project_id,
            o_1.route_id,
            o_1.seq,
            o_1.path_seq,
            o_1.node,
            o_1.edge,
            o_1.cost,
            o_1.agg_cost,
            o_1.shape_length,
            count(*) OVER (PARTITION BY o_1.edge, o_1.opportunity_id) AS edge_used_count,
            row_number() OVER (PARTITION BY o_1.edge, o_1.opportunity_id ORDER BY o_1.route_id, o_1.seq) AS r
           FROM prospector.opportunity_routes o_1
          ORDER BY o_1.seq
        ), pr AS (
         SELECT o_1.opportunity_id,
            count(DISTINCT opl.project_id) AS project_count,
            count(*) AS total_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'MDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises,
            count(
                CASE
                    WHEN (bt.building_type <> ALL (ARRAY['MDU'::text, 'SDU'::text])) THEN 1
                    ELSE NULL::integer
                END) AS other_premises
           FROM ((((prospector.opportunity o_1
             LEFT JOIN prospector.opportunity_project_link opl ON (((o_1.opportunity_id = opl.opportunity_id) AND (opl.link_active = true))))
             LEFT JOIN projects.projects p ON ((opl.project_id = p.project_id)))
             LEFT JOIN basedata.abp a ON (public.st_intersects(p.geom, a.geom)))
             LEFT JOIN basedata.toid_building_type bt ON (((a.cross_reference)::text = (bt.toid)::text)))
          GROUP BY o_1.opportunity_id
        ), s AS (
         SELECT r.opportunity_id,
            round((sum(r.shape_length))::numeric, 0) AS total_distance,
            round((sum(
                CASE
                    WHEN (r.edge_used_count > 1) THEN r.shape_length
                    ELSE (0)::double precision
                END))::numeric, 0) AS shared_route_distance,
            round((sum(
                CASE
                    WHEN (r.edge_used_count = 1) THEN r.shape_length
                    ELSE (0)::double precision
                END))::numeric, 0) AS unique_route_distance
           FROM r
          WHERE (r.r = 1)
          GROUP BY r.opportunity_id
        )
 SELECT o.opportunity_id,
    o.opportunity_name,
    to_char(o.opportunity_created_datetime, 'YYYY-MM-DD HH24:MI'::text) AS opportunity_created_datetime,
    o.opportunity_created_user,
    to_char(o.opportunity_modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS opportunity_modified_datetime,
    o.opportunity_modified_user,
    uc.username AS opportunity_created_username,
    um.username AS opportunity_modified_username,
    pr.project_count,
    COALESCE(s.total_distance, (0)::numeric) AS total_distance,
    COALESCE(s.shared_route_distance, (0)::numeric) AS shared_route_distance,
    COALESCE(s.unique_route_distance, (0)::numeric) AS unique_route_distance,
    pr.total_premises,
    pr.mdu_premises,
    pr.sdu_premises,
    pr.other_premises,
    COALESCE(round((s.total_distance / (NULLIF(pr.mdu_premises, 0))::numeric), 1), (0)::numeric) AS total_mtr_per_mdu_prem,
    COALESCE(round((s.unique_route_distance / (NULLIF(pr.mdu_premises, 0))::numeric), 1), (0)::numeric) AS unique_mtr_per_mdu_prem,
    COALESCE(round(((s.shared_route_distance / NULLIF(s.total_distance, (0)::numeric)) * (100)::numeric), 1), (0)::numeric) AS shared_meterage_percent
   FROM ((((prospector.opportunity o
     LEFT JOIN users.users uc ON ((o.opportunity_created_user = uc.id)))
     LEFT JOIN users.users um ON ((o.opportunity_created_user = um.id)))
     LEFT JOIN pr ON ((o.opportunity_id = pr.opportunity_id)))
     LEFT JOIN s ON ((o.opportunity_id = s.opportunity_id)));


--
-- Name: vw_opportunity_network_points; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_opportunity_network_points AS
 SELECT o.opportunity_id,
    o.opportunity_name,
    p.project_id,
    p.project_name,
    np.point_id,
    np.point_name,
    np.geom,
    p.company_id,
    np.point_type
   FROM (((prospector.opportunity o
     JOIN prospector.opportunity_project_link opl ON (((o.opportunity_id = opl.opportunity_id) AND (opl.link_active = true))))
     JOIN projects.projects p ON ((opl.project_id = p.project_id)))
     JOIN prospector.network_points np ON ((p.project_id = np.project_id)));


--
-- Name: vw_opportunity_projects; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_opportunity_projects AS
 SELECT o.id,
    o.opportunity_id,
    o.link_date,
    p.project_id,
    p.project_name,
    p.geom
   FROM (prospector.opportunity_project_link o
     JOIN projects.projects p ON ((o.project_id = p.project_id)))
  WHERE (o.link_active = true);


--
-- Name: vw_opportunity_projects_output; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_opportunity_projects_output AS
 WITH r AS (
         SELECT o.opportunity_id,
            o.project_id,
            o.route_id,
            o.seq,
            o.path_seq,
            o.node,
            o.edge,
            o.cost,
            o.agg_cost,
            o.shape_length,
            count(*) OVER (PARTITION BY o.edge, o.opportunity_id) AS edge_used_count
           FROM prospector.opportunity_routes o
          ORDER BY o.seq
        ), pr AS (
         SELECT o.opportunity_id,
            p_1.project_id,
            p_1.project_name,
            count(*) AS total_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'MDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises,
            count(
                CASE
                    WHEN (bt.building_type <> ALL (ARRAY['MDU'::text, 'SDU'::text])) THEN 1
                    ELSE NULL::integer
                END) AS other_premises
           FROM ((((prospector.opportunity o
             LEFT JOIN prospector.opportunity_project_link opl ON (((o.opportunity_id = opl.opportunity_id) AND (opl.link_active = true))))
             LEFT JOIN projects.projects p_1 ON ((opl.project_id = p_1.project_id)))
             LEFT JOIN basedata.abp a ON (public.st_intersects(p_1.geom, a.geom)))
             LEFT JOIN basedata.toid_building_type bt ON (((a.cross_reference)::text = (bt.toid)::text)))
          GROUP BY o.opportunity_id, p_1.project_id, p_1.project_name
        ), s AS (
         SELECT r.route_id,
            r.project_id,
            round((sum(r.shape_length))::numeric, 0) AS total_distance,
            round((sum(
                CASE
                    WHEN (r.edge_used_count > 1) THEN r.shape_length
                    ELSE (0)::double precision
                END))::numeric, 0) AS shared_route_distance,
            round((sum(
                CASE
                    WHEN (r.edge_used_count = 1) THEN r.shape_length
                    ELSE (0)::double precision
                END))::numeric, 0) AS unique_route_distance
           FROM r
          GROUP BY r.route_id, r.project_id
          ORDER BY r.route_id
        )
 SELECT pr.opportunity_id,
    row_number() OVER (PARTITION BY pr.opportunity_id ORDER BY s.total_distance) AS build_order,
        CASE
            WHEN (s.route_id IS NOT NULL) THEN 'Complete'::text
            ELSE 'Failed'::text
        END AS route_status,
    s.route_id,
    s.project_id,
    pr.project_name,
    COALESCE(s.total_distance, (0)::numeric) AS total_distance,
    COALESCE(s.shared_route_distance, (0)::numeric) AS shared_route_distance,
    COALESCE(s.unique_route_distance, (0)::numeric) AS unique_route_distance,
    pr.total_premises,
    pr.mdu_premises,
    pr.sdu_premises,
    pr.other_premises,
    COALESCE(round((s.total_distance / (NULLIF(pr.mdu_premises, 0))::numeric), 1), (0)::numeric) AS total_mtr_per_mdu_prem,
    COALESCE(round((s.unique_route_distance / (NULLIF(pr.mdu_premises, 0))::numeric), 1), (0)::numeric) AS unique_mtr_per_mdu_prem,
    p.geom
   FROM ((pr
     LEFT JOIN s ON ((s.project_id = pr.project_id)))
     LEFT JOIN projects.projects p ON ((pr.project_id = p.project_id)))
  ORDER BY s.total_distance;


--
-- Name: vw_opportunity_routes; Type: VIEW; Schema: prospector; Owner: -
--

CREATE VIEW prospector.vw_opportunity_routes AS
 WITH a AS (
         SELECT r.edge,
            round((r.shape_length)::numeric, 2) AS duct_section_length,
            count(*) OVER (PARTITION BY r.edge, r.opportunity_id) AS edge_used_count,
            string_agg((p.project_name)::text, ', '::text) OVER (PARTITION BY r.edge, r.opportunity_id) AS duct_feeding_projects,
            r.opportunity_id,
            p.company_id,
            d.shape AS geom,
            row_number() OVER (PARTITION BY r.edge, r.opportunity_id ORDER BY r.route_id, r.seq) AS r
           FROM ((prospector.opportunity_routes r
             JOIN openreach.duct d ON ((r.edge = d.objectid)))
             JOIN projects.projects p ON ((r.project_id = p.project_id)))
          ORDER BY r.route_id, r.seq
        )
 SELECT a.opportunity_id,
    a.edge,
    a.duct_section_length,
    a.edge_used_count,
    a.duct_feeding_projects,
        CASE
            WHEN (a.edge_used_count = 1) THEN 'unique'::text
            ELSE 'shared'::text
        END AS route_type,
    a.company_id,
    a.geom
   FROM a
  WHERE (a.r = 1);


--
-- Name: blpu_uprn_street_usrn_11; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.blpu_uprn_street_usrn_11 (
    correlation_id text,
    identifier_1 bigint,
    version_number_1 text,
    version_date_1 timestamp without time zone,
    identifier_2 bigint,
    version_number_2 text,
    version_date_2 timestamp without time zone,
    confidence text
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'blpu_uprn_street_usrn_11'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN correlation_id OPTIONS (
    column_name 'correlation_id'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN identifier_1 OPTIONS (
    column_name 'identifier_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN version_number_1 OPTIONS (
    column_name 'version_number_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN version_date_1 OPTIONS (
    column_name 'version_date_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN identifier_2 OPTIONS (
    column_name 'identifier_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN version_number_2 OPTIONS (
    column_name 'version_number_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN version_date_2 OPTIONS (
    column_name 'version_date_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_street_usrn_11 ALTER COLUMN confidence OPTIONS (
    column_name 'confidence'
);


--
-- Name: blpu_uprn_topographicarea_toid_5; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 (
    correlation_id text,
    identifier_1 bigint,
    version_number_1 double precision,
    version_date_1 timestamp with time zone,
    identifier_2 character varying(20),
    version_number_2 bigint,
    version_date_2 timestamp with time zone,
    confidence text
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'blpu_uprn_topographicarea_toid_5'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN correlation_id OPTIONS (
    column_name 'correlation_id'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN identifier_1 OPTIONS (
    column_name 'identifier_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN version_number_1 OPTIONS (
    column_name 'version_number_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN version_date_1 OPTIONS (
    column_name 'version_date_1'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN identifier_2 OPTIONS (
    column_name 'identifier_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN version_number_2 OPTIONS (
    column_name 'version_number_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN version_date_2 OPTIONS (
    column_name 'version_date_2'
);
ALTER FOREIGN TABLE public.blpu_uprn_topographicarea_toid_5 ALTER COLUMN confidence OPTIONS (
    column_name 'confidence'
);


--
-- Name: code_point_open; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.code_point_open (
    postcode character varying(12),
    positional_quality_indicator integer,
    eastings integer,
    northings integer,
    country_code character varying(10),
    nhs_regional_ha_code character varying,
    nhs_ha_code character varying(10),
    admin_county_code character varying(10),
    admin_district_code character varying(10),
    admin_ward_code character varying(10),
    geom public.geometry
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'code_point_open'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN postcode OPTIONS (
    column_name 'postcode'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN positional_quality_indicator OPTIONS (
    column_name 'positional_quality_indicator'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN eastings OPTIONS (
    column_name 'eastings'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN northings OPTIONS (
    column_name 'northings'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN country_code OPTIONS (
    column_name 'country_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN nhs_regional_ha_code OPTIONS (
    column_name 'nhs_regional_ha_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN nhs_ha_code OPTIONS (
    column_name 'nhs_ha_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN admin_county_code OPTIONS (
    column_name 'admin_county_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN admin_district_code OPTIONS (
    column_name 'admin_district_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN admin_ward_code OPTIONS (
    column_name 'admin_ward_code'
);
ALTER FOREIGN TABLE public.code_point_open ALTER COLUMN geom OPTIONS (
    column_name 'geom'
);


--
-- Name: file_uploads_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.file_uploads_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: file_uploads_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.file_uploads_id_seq OWNED BY public.file_uploads.id;


--
-- Name: full_ultimate; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.full_ultimate (
    uprn bigint,
    classification_code character varying,
    class_description character varying,
    parent_uprn bigint,
    block text,
    postcode character varying(8),
    address text,
    business_name text,
    borough character varying(50),
    main_proprietor text,
    title_no text,
    property_type text,
    cross_reference character varying(50),
    udprn bigint,
    umprn integer,
    geom public.geometry(Point,27700),
    sprn integer NOT NULL,
    portfolio_id smallint,
    bd_status smallint,
    leasehold_portfolio_id integer,
    wayleave_date date,
    mdu_id bigint,
    class_type text,
    category_code smallint,
    wayleave_portfolio_id smallint,
    main_leaseholder text,
    leaseholder_managed boolean DEFAULT false NOT NULL,
    usrn bigint,
    provider integer
);


--
-- Name: geo_temp_estimates; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.geo_temp_estimates (
    project_id integer,
    costing_datetime timestamp with time zone,
    total_premises numeric,
    mdu_premises numeric,
    mdu_with_wayleave numeric,
    sdu_premises numeric,
    mdu_buildings bigint,
    sdu_buildings bigint,
    pia_oh_premises numeric,
    pia_ug_l_premises numeric,
    pia_ug_c_premises numeric,
    civils_premises numeric,
    pia_oh_mdu_premises numeric,
    pia_oh_sdu_premises numeric,
    pia_ug_l_mdu_premises numeric,
    pia_ug_l_sdu_premises numeric,
    pia_ug_c_mdu_premises numeric,
    pia_ug_c_sdu_premises numeric,
    civils_mdu_premises numeric,
    civils_sdu_premises numeric,
    cab_name character varying,
    total_distance numeric,
    cluster_capex_budget numeric,
    mdu_low_capex numeric,
    sdu_low_capex numeric,
    capacity_low_capex numeric,
    civils_low_capex numeric,
    pia_ug_low_capex numeric,
    pia_oh_low_capex numeric,
    mdu_high_capex numeric,
    sdu_high_capex numeric,
    capacity_high_capex numeric,
    civils_high_capex numeric,
    pia_ug_high_capex numeric,
    pia_oh_high_capex numeric,
    mdu_wayleave_percent numeric,
    capex_low numeric,
    cppp_low numeric,
    capex_high numeric,
    cppp_high numeric,
    percent_ugl_to_ugc character varying(100),
    fibre_count integer,
    distribution_distance numeric,
    distribution_capex_low numeric,
    distribution_cppp_low numeric,
    distribution_capex_high numeric,
    distribution_cppp_high numeric
)
SERVER geo_server
OPTIONS (
    schema_name 'temp',
    table_name 'estimates'
);


--
-- Name: geolynx_address_staging; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.geolynx_address_staging (
    id integer NOT NULL,
    file_upload_id bigint NOT NULL,
    stocklist_id integer NOT NULL,
    batch_id character varying(50) NOT NULL,
    source_row integer,
    address_raw text NOT NULL,
    postcode character varying(8),
    address_type character varying(20),
    match_method character varying(30),
    match_confidence character varying(10),
    uprn bigint,
    import_action character varying(50) NOT NULL,
    notes text,
    upload_timestamp timestamp with time zone DEFAULT now()
);


--
-- Name: geolynx_address_staging_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.geolynx_address_staging_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: geolynx_address_staging_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.geolynx_address_staging_id_seq OWNED BY public.geolynx_address_staging.id;


--
-- Name: geolynx_identifiers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.geolynx_identifiers (
    id integer NOT NULL,
    uprn bigint,
    toid character varying(20),
    stocklist_id integer NOT NULL,
    batch_id character varying(50) NOT NULL,
    source_row integer,
    source_file character varying(255),
    upload_timestamp timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    file_upload_id bigint,
    CONSTRAINT check_identifier CHECK ((((uprn IS NOT NULL) AND (toid IS NULL)) OR ((uprn IS NULL) AND (toid IS NOT NULL))))
);


--
-- Name: geolynx_identifiers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.geolynx_identifiers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: geolynx_identifiers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.geolynx_identifiers_id_seq OWNED BY public.geolynx_identifiers.id;


--
-- Name: map_layer_page_config; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.map_layer_page_config (
    layer_id integer NOT NULL,
    page_context text NOT NULL,
    layer_editable boolean DEFAULT false NOT NULL,
    layer_allow_create boolean DEFAULT false NOT NULL,
    visible boolean DEFAULT true NOT NULL,
    snap_to_this boolean DEFAULT false NOT NULL,
    focus_on_load boolean DEFAULT false NOT NULL
);


--
-- Name: TABLE map_layer_page_config; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON TABLE public.map_layer_page_config IS 'Junction table: presence of a row means the layer is shown on that page. Column values are per-page overrides that take precedence over map_layers defaults.';


--
-- Name: COLUMN map_layer_page_config.page_context; Type: COMMENT; Schema: public; Owner: -
--

COMMENT ON COLUMN public.map_layer_page_config.page_context IS 'Matches the page identifier used in JS calls: projectedit, accountedit, stocklistedit, wayleaveedit, opportunityedit, map';


--
-- Name: map_layers; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.map_layers (
    layer_id integer NOT NULL,
    layer_title text NOT NULL,
    layer_name text NOT NULL,
    wms_layer_name text,
    tooltip text,
    layer_type character varying(50) NOT NULL,
    layer_category character varying(50) NOT NULL,
    interact_type character varying(50) NOT NULL,
    interact_group character varying(50) NOT NULL,
    toggle_min_zoom integer,
    source_min_zoom integer,
    source_max_zoom integer,
    url text,
    z_index integer,
    layer_interactive boolean DEFAULT false,
    vector_style text,
    vector_style_field text,
    label_field text,
    render_mode text,
    layer_is_edited boolean DEFAULT false,
    uid text,
    api_key text,
    attribute_form text,
    editable_fields jsonb,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    layer_active boolean,
    database_schema character varying(50),
    database_table character varying(100)
);


--
-- Name: map_layers_backup_2026_06_28; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.map_layers_backup_2026_06_28 (
    layer_id integer,
    layer_title text,
    layer_name text,
    wms_layer_name text,
    tooltip text,
    layer_type character varying(50),
    layer_category character varying(50),
    interact_type character varying(50),
    interact_group character varying(50),
    toggle_min_zoom integer,
    source_min_zoom integer,
    source_max_zoom integer,
    url text,
    z_index integer,
    visible boolean,
    layer_interactive boolean,
    vector_style text,
    vector_style_field text,
    label_field text,
    render_mode text,
    focus_on_load boolean,
    layer_editable boolean,
    layer_is_edited boolean,
    layer_allow_create boolean,
    uid text,
    snap_to_this boolean,
    api_key text,
    attribute_form text,
    editable_fields jsonb,
    created_at timestamp without time zone,
    layer_active boolean,
    database_schema character varying(50),
    database_table character varying(100)
);


--
-- Name: map_layers_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.map_layers_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: map_layers_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.map_layers_id_seq OWNED BY public.map_layers.layer_id;


--
-- Name: motorway_junction; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.motorway_junction (
    fid integer NOT NULL,
    id character varying,
    junction_number character varying,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'motorway_junction'
);
ALTER FOREIGN TABLE public.motorway_junction ALTER COLUMN fid OPTIONS (
    column_name 'fid'
);
ALTER FOREIGN TABLE public.motorway_junction ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.motorway_junction ALTER COLUMN junction_number OPTIONS (
    column_name 'junction_number'
);
ALTER FOREIGN TABLE public.motorway_junction ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_building; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_building (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_building'
);
ALTER FOREIGN TABLE public.openmaplocal_building ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_building ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_building ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_car_charging_point; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_car_charging_point (
    id text,
    charge_method text,
    feature_code integer,
    rated_voltage integer,
    type text,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_car_charging_point'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN charge_method OPTIONS (
    column_name 'charge_method'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN rated_voltage OPTIONS (
    column_name 'rated_voltage'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN type OPTIONS (
    column_name 'type'
);
ALTER FOREIGN TABLE public.openmaplocal_car_charging_point ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_electricity_transmission_line; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_electricity_transmission_line (
    id text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_electricity_transmission_line'
);
ALTER FOREIGN TABLE public.openmaplocal_electricity_transmission_line ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_electricity_transmission_line ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_electricity_transmission_line ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_foreshore; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_foreshore (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_foreshore'
);
ALTER FOREIGN TABLE public.openmaplocal_foreshore ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_foreshore ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_foreshore ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_functional_site; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_functional_site (
    id text,
    classification text,
    distinctive_name text,
    feature_code integer,
    site_theme text,
    geometry public.geometry(MultiPolygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_functional_site'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN distinctive_name OPTIONS (
    column_name 'distinctive_name'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN site_theme OPTIONS (
    column_name 'site_theme'
);
ALTER FOREIGN TABLE public.openmaplocal_functional_site ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_glasshouse; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_glasshouse (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_glasshouse'
);
ALTER FOREIGN TABLE public.openmaplocal_glasshouse ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_glasshouse ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_glasshouse ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_important_building; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_important_building (
    id text,
    building_theme text,
    classification text,
    distinctive_name text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_important_building'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN building_theme OPTIONS (
    column_name 'building_theme'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN distinctive_name OPTIONS (
    column_name 'distinctive_name'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_important_building ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_motorway_junction; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_motorway_junction (
    id text,
    feature_code integer,
    junction_number text,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_motorway_junction'
);
ALTER FOREIGN TABLE public.openmaplocal_motorway_junction ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_motorway_junction ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_motorway_junction ALTER COLUMN junction_number OPTIONS (
    column_name 'junction_number'
);
ALTER FOREIGN TABLE public.openmaplocal_motorway_junction ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_named_place; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_named_place (
    id text,
    classification text,
    distinctive_name text,
    feature_code integer,
    font_height text,
    text_orientation integer,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_named_place'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN distinctive_name OPTIONS (
    column_name 'distinctive_name'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN font_height OPTIONS (
    column_name 'font_height'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN text_orientation OPTIONS (
    column_name 'text_orientation'
);
ALTER FOREIGN TABLE public.openmaplocal_named_place ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_railway_station; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_railway_station (
    id text,
    classification text,
    distinctive_name text,
    feature_code integer,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_railway_station'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_station ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_station ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_station ALTER COLUMN distinctive_name OPTIONS (
    column_name 'distinctive_name'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_station ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_station ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_railway_track; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_railway_track (
    id text,
    classification text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_railway_track'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_track ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_track ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_track ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_track ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_railway_tunnel; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_railway_tunnel (
    id text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_railway_tunnel'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_tunnel ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_tunnel ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_railway_tunnel ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_road; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_road (
    id text,
    classification text,
    distinctive_name text,
    draw_level integer,
    feature_code integer,
    override text,
    road_number text,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_road'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN distinctive_name OPTIONS (
    column_name 'distinctive_name'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN draw_level OPTIONS (
    column_name 'draw_level'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN override OPTIONS (
    column_name 'override'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN road_number OPTIONS (
    column_name 'road_number'
);
ALTER FOREIGN TABLE public.openmaplocal_road ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_road_tunnel; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_road_tunnel (
    id text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_road_tunnel'
);
ALTER FOREIGN TABLE public.openmaplocal_road_tunnel ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_road_tunnel ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_road_tunnel ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_roundabout; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_roundabout (
    id text,
    classification text,
    feature_code integer,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_roundabout'
);
ALTER FOREIGN TABLE public.openmaplocal_roundabout ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_roundabout ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_roundabout ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_roundabout ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_surface_water_area; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_surface_water_area (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_surface_water_area'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_area ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_area ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_area ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_surface_water_line; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_surface_water_line (
    id text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_surface_water_line'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_line ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_line ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_surface_water_line ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_tidal_boundary; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_tidal_boundary (
    id text,
    classification text,
    feature_code integer,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_tidal_boundary'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_boundary ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_boundary ALTER COLUMN classification OPTIONS (
    column_name 'classification'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_boundary ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_boundary ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_tidal_water; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_tidal_water (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_tidal_water'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_water ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_water ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_tidal_water ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openmaplocal_woodland; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openmaplocal_woodland (
    id text,
    feature_code integer,
    geometry public.geometry(Polygon,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openmaplocal_woodland'
);
ALTER FOREIGN TABLE public.openmaplocal_woodland ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openmaplocal_woodland ALTER COLUMN feature_code OPTIONS (
    column_name 'feature_code'
);
ALTER FOREIGN TABLE public.openmaplocal_woodland ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openreach_exchange_polygons; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.openreach_exchange_polygons (
    exchange_1141_code character varying(255),
    geom public.geometry
);


--
-- Name: openstreetmap_ways_vertice_map; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openstreetmap_ways_vertice_map (
    postcode character varying(12),
    gid bigint,
    osm_id bigint,
    distance double precision
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openstreetmap_ways_vertice_map'
);
ALTER FOREIGN TABLE public.openstreetmap_ways_vertice_map ALTER COLUMN postcode OPTIONS (
    column_name 'postcode'
);
ALTER FOREIGN TABLE public.openstreetmap_ways_vertice_map ALTER COLUMN gid OPTIONS (
    column_name 'gid'
);
ALTER FOREIGN TABLE public.openstreetmap_ways_vertice_map ALTER COLUMN osm_id OPTIONS (
    column_name 'osm_id'
);
ALTER FOREIGN TABLE public.openstreetmap_ways_vertice_map ALTER COLUMN distance OPTIONS (
    column_name 'distance'
);


--
-- Name: opentoid_os_mastermap_sites_layer; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.opentoid_os_mastermap_sites_layer (
    toid text NOT NULL,
    version_number bigint,
    version_date timestamp with time zone,
    easting real,
    northing real,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'opentoid_os_mastermap_sites_layer'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN version_number OPTIONS (
    column_name 'version_number'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN version_date OPTIONS (
    column_name 'version_date'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN easting OPTIONS (
    column_name 'easting'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN northing OPTIONS (
    column_name 'northing'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_sites_layer ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: opentoid_os_mastermap_topography_layer; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.opentoid_os_mastermap_topography_layer (
    toid text NOT NULL,
    version_number bigint,
    version_date timestamp with time zone,
    easting real,
    northing real,
    geometry public.geometry
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'opentoid_os_mastermap_topography_layer'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN version_number OPTIONS (
    column_name 'version_number'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN version_date OPTIONS (
    column_name 'version_date'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN easting OPTIONS (
    column_name 'easting'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN northing OPTIONS (
    column_name 'northing'
);
ALTER FOREIGN TABLE public.opentoid_os_mastermap_topography_layer ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openuprn; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openuprn (
    uprn bigint NOT NULL,
    x_coordinate double precision,
    y_coordinate double precision,
    latitude double precision,
    longitude double precision,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openuprn'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN x_coordinate OPTIONS (
    column_name 'x_coordinate'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN y_coordinate OPTIONS (
    column_name 'y_coordinate'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN latitude OPTIONS (
    column_name 'latitude'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN longitude OPTIONS (
    column_name 'longitude'
);
ALTER FOREIGN TABLE public.openuprn ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: openusrn; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.openusrn (
    id integer NOT NULL,
    usrn bigint,
    street_type character varying(35),
    geometry public.geometry(GeometryZM,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'openusrn'
);
ALTER FOREIGN TABLE public.openusrn ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.openusrn ALTER COLUMN usrn OPTIONS (
    column_name 'usrn'
);
ALTER FOREIGN TABLE public.openusrn ALTER COLUMN street_type OPTIONS (
    column_name 'street_type'
);
ALTER FOREIGN TABLE public.openusrn ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: project_nearest_neighbours; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.project_nearest_neighbours AS
 SELECT bci.project_id,
    n.neighbour_id,
    n.project_name,
    n.geom,
    n.distance,
    n.project_status,
    n.account_id,
    n.account_name
   FROM (projects.projects bci
     CROSS JOIN LATERAL ( SELECT b.project_id AS neighbour_id,
            b.project_name,
            COALESCE(ps.project_status_desc, 'Status Not Set'::character varying) AS project_status,
            a.account_id,
            a.account_name,
            b.geom,
            round((public.st_distance(b.geom, bci.geom))::numeric, 2) AS distance
           FROM ((projects.projects b
             LEFT JOIN projects.project_status ps ON ((b.project_status_id = ps.project_status_id)))
             LEFT JOIN accounts.accounts a ON ((b.account_id = a.account_id)))
          WHERE ((b.project_id <> bci.project_id) AND (b.geom OPERATOR(public.&&) public.st_expand(bci.geom, (5000)::double precision)) AND (public.st_distance(b.geom, bci.geom) < (5000)::double precision))) n);


--
-- Name: road_link; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.road_link (
    fid integer NOT NULL,
    id character varying,
    fictitious boolean,
    road_classification character varying,
    road_function character varying,
    form_of_way character varying,
    road_classification_number character varying,
    name_1 character varying,
    name_1_lang character varying,
    name_2 character varying,
    name_2_lang character varying,
    road_structure character varying,
    length double precision,
    length_uom character varying,
    loop boolean,
    primary_route boolean,
    trunk_road boolean,
    start_node character varying,
    end_node character varying,
    road_number_toid character varying,
    road_name_toid character varying,
    geometry public.geometry(LineString,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'road_link'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN fid OPTIONS (
    column_name 'fid'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN fictitious OPTIONS (
    column_name 'fictitious'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_classification OPTIONS (
    column_name 'road_classification'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_function OPTIONS (
    column_name 'road_function'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN form_of_way OPTIONS (
    column_name 'form_of_way'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_classification_number OPTIONS (
    column_name 'road_classification_number'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN name_1 OPTIONS (
    column_name 'name_1'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN name_1_lang OPTIONS (
    column_name 'name_1_lang'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN name_2 OPTIONS (
    column_name 'name_2'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN name_2_lang OPTIONS (
    column_name 'name_2_lang'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_structure OPTIONS (
    column_name 'road_structure'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN length OPTIONS (
    column_name 'length'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN length_uom OPTIONS (
    column_name 'length_uom'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN loop OPTIONS (
    column_name 'loop'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN primary_route OPTIONS (
    column_name 'primary_route'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN trunk_road OPTIONS (
    column_name 'trunk_road'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN start_node OPTIONS (
    column_name 'start_node'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN end_node OPTIONS (
    column_name 'end_node'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_number_toid OPTIONS (
    column_name 'road_number_toid'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN road_name_toid OPTIONS (
    column_name 'road_name_toid'
);
ALTER FOREIGN TABLE public.road_link ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: road_node; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.road_node (
    fid integer NOT NULL,
    id character varying,
    form_of_road_node character varying,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'road_node'
);
ALTER FOREIGN TABLE public.road_node ALTER COLUMN fid OPTIONS (
    column_name 'fid'
);
ALTER FOREIGN TABLE public.road_node ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE public.road_node ALTER COLUMN form_of_road_node OPTIONS (
    column_name 'form_of_road_node'
);
ALTER FOREIGN TABLE public.road_node ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: temp_opentoid_os_mastermap_highways_network; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network (
    toid text,
    version_number double precision,
    version_date timestamp with time zone,
    easting real,
    northing real,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'temp_opentoid_os_mastermap_highways_network'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN version_number OPTIONS (
    column_name 'version_number'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN version_date OPTIONS (
    column_name 'version_date'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN easting OPTIONS (
    column_name 'easting'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN northing OPTIONS (
    column_name 'northing'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_highways_network ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: temp_opentoid_os_mastermap_sites_layer; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer (
    toid text,
    version_number bigint,
    version_date timestamp with time zone,
    easting real,
    northing real,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'temp_opentoid_os_mastermap_sites_layer'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN version_number OPTIONS (
    column_name 'version_number'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN version_date OPTIONS (
    column_name 'version_date'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN easting OPTIONS (
    column_name 'easting'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN northing OPTIONS (
    column_name 'northing'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_sites_layer ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: temp_opentoid_os_mastermap_topography_layer; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer (
    toid text,
    version_number bigint,
    version_date timestamp with time zone,
    easting real,
    northing real,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'temp_opentoid_os_mastermap_topography_layer'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN version_number OPTIONS (
    column_name 'version_number'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN version_date OPTIONS (
    column_name 'version_date'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN easting OPTIONS (
    column_name 'easting'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN northing OPTIONS (
    column_name 'northing'
);
ALTER FOREIGN TABLE public.temp_opentoid_os_mastermap_topography_layer ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: temp_openuprn; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.temp_openuprn (
    uprn bigint,
    x_coordinate double precision,
    y_coordinate double precision,
    latitude double precision,
    longitude double precision,
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'temp_openuprn'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN x_coordinate OPTIONS (
    column_name 'x_coordinate'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN y_coordinate OPTIONS (
    column_name 'y_coordinate'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN latitude OPTIONS (
    column_name 'latitude'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN longitude OPTIONS (
    column_name 'longitude'
);
ALTER FOREIGN TABLE public.temp_openuprn ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: uprn_usrn_toid_map; Type: FOREIGN TABLE; Schema: public; Owner: -
--

CREATE FOREIGN TABLE public.uprn_usrn_toid_map (
    uprn bigint,
    toid character varying(20),
    usrn bigint,
    street_type character varying(35),
    geometry public.geometry(Point,27700)
)
SERVER geo_db
OPTIONS (
    schema_name 'ordnancesurvey',
    table_name 'uprn_usrn_toid_map'
);
ALTER FOREIGN TABLE public.uprn_usrn_toid_map ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE public.uprn_usrn_toid_map ALTER COLUMN toid OPTIONS (
    column_name 'toid'
);
ALTER FOREIGN TABLE public.uprn_usrn_toid_map ALTER COLUMN usrn OPTIONS (
    column_name 'usrn'
);
ALTER FOREIGN TABLE public.uprn_usrn_toid_map ALTER COLUMN street_type OPTIONS (
    column_name 'street_type'
);
ALTER FOREIGN TABLE public.uprn_usrn_toid_map ALTER COLUMN geometry OPTIONS (
    column_name 'geometry'
);


--
-- Name: users; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.users (
    id bigint NOT NULL,
    username character varying(100) NOT NULL,
    manager character varying(100) NOT NULL,
    email character varying(50) NOT NULL,
    company integer NOT NULL,
    access_level integer NOT NULL,
    division integer NOT NULL,
    team integer NOT NULL,
    password character varying(255) NOT NULL,
    is_active integer NOT NULL,
    date_user_created timestamp without time zone,
    date_last_login timestamp without time zone,
    date_last_active timestamp without time zone
);


--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE public.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;


--
-- Name: vw_projects_list; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.vw_projects_list AS
 WITH prems AS (
         SELECT p_1.project_id,
            count(*) AS total_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'MDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SMDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS smdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises,
            count(
                CASE
                    WHEN (bd.uprn IS NOT NULL) THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers,
            count(
                CASE
                    WHEN ((bd.uprn IS NOT NULL) AND (bt.building_type = 'MDU'::text)) THEN 1
                    ELSE NULL::integer
                END) AS bduk_vouchers_mdu
           FROM (((projects.projects p_1
             LEFT JOIN basedata.abp ON (public.st_intersects(p_1.geom, abp.geom)))
             LEFT JOIN basedata.toid_building_type bt ON (((abp.cross_reference)::text = (bt.toid)::text)))
             LEFT JOIN adhoc.bduk_go_list_fmp_work_20260217_geom bd ON ((abp.uprn = bd.uprn)))
          GROUP BY p_1.project_id
        )
 SELECT p.project_id,
    COALESCE((p.parent_project_id)::text, ''::text) AS parent_project_id,
    COALESCE((pp.project_name)::text, ''::text) AS parent_project_name,
    p.project_name,
    p.project_status_id,
    COALESCE(ps.project_status_desc, ''::character varying) AS project_status_desc,
    p.project_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    p.created_user,
    COALESCE(uc.username, ''::character varying) AS created_username,
    to_char(p.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    p.modified_user,
    um.username AS modified_username,
    COALESCE(c.company_name, ''::character varying) AS contractor_name,
    p.company_id,
    p.account_id,
    COALESCE(a.account_name, ''::character varying) AS account_name,
    prems.total_premises,
    prems.mdu_premises,
    prems.smdu_premises,
    prems.sdu_premises,
    prems.bduk_vouchers,
    prems.bduk_vouchers_mdu
   FROM ((((((((projects.projects p
     LEFT JOIN users.users um ON ((p.modified_user = um.id)))
     LEFT JOIN users.users uc ON ((p.created_user = uc.id)))
     LEFT JOIN users.users upm ON ((p.project_manager = upm.id)))
     LEFT JOIN projects.project_status ps ON ((p.project_status_id = ps.project_status_id)))
     LEFT JOIN projects.projects pp ON ((p.parent_project_id = pp.project_id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((a.account_id = p.account_id)))
     LEFT JOIN prems ON ((p.project_id = prems.project_id)));


--
-- Name: reports; Type: TABLE; Schema: reports; Owner: -
--

CREATE TABLE reports.reports (
    report_id bigint NOT NULL,
    report_ref character varying,
    report_name character varying,
    report_description character varying
);


--
-- Name: newtable_report_id_seq; Type: SEQUENCE; Schema: reports; Owner: -
--

CREATE SEQUENCE reports.newtable_report_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: newtable_report_id_seq; Type: SEQUENCE OWNED BY; Schema: reports; Owner: -
--

ALTER SEQUENCE reports.newtable_report_id_seq OWNED BY reports.reports.report_id;


--
-- Name: vw_project_ecd_change; Type: VIEW; Schema: reports; Owner: -
--

CREATE VIEW reports.vw_project_ecd_change AS
 WITH ch AS (
         SELECT vd.project_id,
            lag(vd.value) OVER (PARTITION BY vd.project_id, vd.field_id ORDER BY vd.record_datetime) AS previous_date_value,
            vd.value AS current_date_value,
            to_char(vd.record_datetime, 'YYYY-MM-DD HH24:MI'::text) AS change_datetime,
            u.username AS change_username,
            vd.record_datetime,
            count(*) OVER (PARTITION BY vd.project_id, vd.field_id) AS change_count
           FROM ((projects.project_fields pf
             JOIN projects.project_field_values_date_history vd ON ((pf.field_id = vd.field_id)))
             JOIN public.users u ON ((vd.record_user = u.id)))
          WHERE (pf.field_id = 8)
        )
 SELECT p.project_id,
    p.project_name,
    ps.project_status_desc,
    ch.previous_date_value,
    ch.current_date_value,
    ch.change_datetime,
    ch.change_username,
    ch.change_count,
        CASE
            WHEN ((ch.previous_date_value IS NULL) AND (ch.current_date_value IS NOT NULL)) THEN 'Date Added'::text
            WHEN ((ch.previous_date_value IS NOT NULL) AND (ch.current_date_value IS NULL)) THEN 'Date Removed'::text
            WHEN (ch.previous_date_value > ch.current_date_value) THEN 'Date Moved In'::text
            WHEN (ch.previous_date_value < ch.current_date_value) THEN 'Date Moved Out'::text
            ELSE ''::text
        END AS change_type
   FROM ((projects.projects p
     JOIN ch ON ((p.project_id = ch.project_id)))
     JOIN projects.project_status ps ON ((p.project_status_id = ps.project_status_id)))
  WHERE (ch.record_datetime >= (CURRENT_DATE - '60 days'::interval))
  ORDER BY ch.record_datetime DESC;


--
-- Name: stocklist_access_log; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_access_log (
    id bigint NOT NULL,
    stocklist_id bigint,
    user_id integer,
    access_type character varying(50),
    access_datetime timestamp without time zone DEFAULT now()
);


--
-- Name: stocklist_access_log_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_access_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_access_log_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_access_log_id_seq OWNED BY stocklists.stocklist_access_log.id;


--
-- Name: stocklist_field_category; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_category (
    field_category_id integer NOT NULL,
    field_category_name character varying NOT NULL,
    field_category_ref character varying,
    field_section_id integer,
    field_category_display_order integer
);


--
-- Name: stocklist_field_category_field_category_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_category_field_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_category_field_category_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_category_field_category_id_seq OWNED BY stocklists.stocklist_field_category.field_category_id;


--
-- Name: stocklist_field_dropdown_options; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_dropdown_options (
    dropdown_id bigint NOT NULL,
    field_id integer NOT NULL,
    dropdown_value character varying,
    dropdown_name character varying
);


--
-- Name: stocklist_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_dropdown_options_dropdown_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_dropdown_options_dropdown_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_dropdown_options_dropdown_id_seq OWNED BY stocklists.stocklist_field_dropdown_options.dropdown_id;


--
-- Name: stocklist_field_sections; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_sections (
    field_section_id integer NOT NULL,
    field_section_name character varying,
    field_section_display_order integer,
    field_section_ref character varying,
    field_section_icon character varying(50)
);


--
-- Name: stocklist_field_sections_field_section_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_sections_field_section_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_sections_field_section_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_sections_field_section_id_seq OWNED BY stocklists.stocklist_field_sections.field_section_id;


--
-- Name: stocklist_field_sub_category; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_sub_category (
    field_sub_category_id integer NOT NULL,
    field_sub_category_name character varying NOT NULL,
    field_sub_category_ref character varying
);


--
-- Name: stocklist_field_sub_category_field_sub_category_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_sub_category_field_sub_category_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_sub_category_field_sub_category_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_sub_category_field_sub_category_id_seq OWNED BY stocklists.stocklist_field_sub_category.field_sub_category_id;


--
-- Name: stocklist_field_values_boolean; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_boolean (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_boolean_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_boolean_history (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value boolean,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_boolean_history_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_boolean_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_boolean_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_boolean_history_value_id_seq OWNED BY stocklists.stocklist_field_values_boolean_history.value_id;


--
-- Name: stocklist_field_values_boolean_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_boolean_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_boolean_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_boolean_value_id_seq OWNED BY stocklists.stocklist_field_values_boolean.value_id;


--
-- Name: stocklist_field_values_date; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_date (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_date_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_date_history (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value date,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_date_history_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_date_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_date_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_date_history_value_id_seq OWNED BY stocklists.stocklist_field_values_date_history.value_id;


--
-- Name: stocklist_field_values_date_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_date_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_date_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_date_value_id_seq OWNED BY stocklists.stocklist_field_values_date.value_id;


--
-- Name: stocklist_field_values_int; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_int (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_int_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_int_history (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value bigint,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_int_history_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_int_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_int_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_int_history_value_id_seq OWNED BY stocklists.stocklist_field_values_int_history.value_id;


--
-- Name: stocklist_field_values_int_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_int_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_int_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_int_value_id_seq OWNED BY stocklists.stocklist_field_values_int.value_id;


--
-- Name: stocklist_field_values_numeric; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_numeric (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_numeric_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_numeric_history (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value numeric,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_numeric_history_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_numeric_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_numeric_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_numeric_history_value_id_seq OWNED BY stocklists.stocklist_field_values_numeric_history.value_id;


--
-- Name: stocklist_field_values_numeric_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_numeric_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_numeric_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_numeric_value_id_seq OWNED BY stocklists.stocklist_field_values_numeric.value_id;


--
-- Name: stocklist_field_values_text; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_text (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_text_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_field_values_text_history (
    value_id bigint NOT NULL,
    field_id integer,
    stocklist_id integer,
    value text,
    record_datetime timestamp without time zone,
    record_user integer
);


--
-- Name: stocklist_field_values_text_history_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_text_history_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_text_history_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_text_history_value_id_seq OWNED BY stocklists.stocklist_field_values_text_history.value_id;


--
-- Name: stocklist_field_values_text_value_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_field_values_text_value_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_field_values_text_value_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_field_values_text_value_id_seq OWNED BY stocklists.stocklist_field_values_text.value_id;


--
-- Name: stocklist_fields; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_fields (
    field_id bigint NOT NULL,
    field_section integer,
    field_category integer,
    field_sub_category integer,
    field_display_order integer,
    field_data_type character varying,
    field_name character varying,
    field_input_type character varying,
    field_form_id character varying,
    field_required boolean,
    field_active boolean DEFAULT true,
    field_spacer_after integer,
    field_type character varying(10),
    pass_field_to_project boolean
);


--
-- Name: stocklist_fields_field_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_fields_field_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_fields_field_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_fields_field_id_seq OWNED BY stocklists.stocklist_fields.field_id;


--
-- Name: stocklist_import_jobs; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_import_jobs (
    job_id uuid NOT NULL,
    status character varying(50),
    stocklist_id integer,
    file_id character varying(100),
    file_name character varying(200),
    file_upload_id bigint,
    progress integer DEFAULT 0,
    message text,
    total_rows integer,
    processed_rows integer,
    matched_rows integer,
    unmatched_rows integer,
    rows_inserted integer,
    batch_id character varying(60),
    error_message text,
    created_at timestamp without time zone,
    started_at timestamp without time zone,
    completed_at timestamp without time zone,
    created_user_id integer
);


--
-- Name: stocklist_journal; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_journal (
    id bigint NOT NULL,
    stocklist_id bigint,
    user_id integer,
    log_datetime timestamp without time zone,
    log_text text
);


--
-- Name: stocklist_journal_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_journal_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_journal_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_journal_id_seq OWNED BY stocklists.stocklist_journal.id;


--
-- Name: stocklist_premises; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_premises (
    uprn bigint,
    stocklist_id integer,
    id bigint NOT NULL,
    added_datetime timestamp without time zone,
    upload_batch_id character varying(50)
);


--
-- Name: stocklist_premises_geom; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.stocklist_premises_geom AS
 SELECT sp.stocklist_id,
    p.project_id,
    a.uprn,
    a.classification_code,
    a.class_description,
    a.address,
    a.postcode,
    a.geom,
        CASE
            WHEN ((p.project_id >= 1) AND (bt.building_type = 'MDU'::text)) THEN 'mdu_project'::text
            WHEN ((p.project_id >= 1) AND (bt.building_type = 'SMDU'::text)) THEN 'smdu_project'::text
            WHEN ((p.project_id >= 1) AND (bt.building_type = 'SDU'::text)) THEN 'sdu_project'::text
            WHEN (bt.building_type = 'MDU'::text) THEN 'mdu_prem'::text
            WHEN (bt.building_type = 'SMDU'::text) THEN 'smdu_prem'::text
            WHEN (bt.building_type = 'SDU'::text) THEN 'sdu_prem'::text
            ELSE 'other'::text
        END AS style_type,
    s.stocklist_name,
    s.account_id,
    ac.account_name,
    s.company_id,
    bt.building_type,
    bt.building_use,
    bt.building_premises,
    p.project_name,
    a.cross_reference AS toid
   FROM (((((stocklists.stocklist_premises sp
     JOIN stocklists.stocklists s ON ((sp.stocklist_id = s.stocklist_id)))
     JOIN basedata.abp a ON ((sp.uprn = a.uprn)))
     LEFT JOIN projects.projects p ON (public.st_intersects(p.geom, a.geom)))
     LEFT JOIN accounts.accounts ac ON ((s.account_id = ac.account_id)))
     LEFT JOIN basedata.toid_building_type_geom bt ON (((a.cross_reference)::text = (bt.toid)::text)));


--
-- Name: stocklist_premises_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklist_premises_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklist_premises_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklist_premises_id_seq OWNED BY stocklists.stocklist_premises.id;


--
-- Name: stocklist_projects_geom; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.stocklist_projects_geom AS
 SELECT DISTINCT sp.stocklist_id,
    p.project_id,
    p.project_name,
    p.geom
   FROM ((stocklists.stocklist_premises sp
     JOIN basedata.abp a ON ((sp.uprn = a.uprn)))
     JOIN projects.projects p ON (public.st_intersects(p.geom, a.geom)));


--
-- Name: stocklist_status; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklist_status (
    stocklist_status_id integer,
    stocklist_status_desc character varying(100)
);


--
-- Name: stocklist_toid; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.stocklist_toid AS
 SELECT a.cross_reference AS toid,
    count(*) AS total_premises,
    count(DISTINCT sp.uprn) AS distinct_uprn
   FROM (((((stocklists.stocklist_premises sp
     JOIN stocklists.stocklists s ON ((sp.stocklist_id = s.stocklist_id)))
     JOIN basedata.abp a ON ((sp.uprn = a.uprn)))
     LEFT JOIN projects.projects p ON (public.st_intersects(p.geom, a.geom)))
     LEFT JOIN accounts.accounts ac ON ((s.account_id = ac.account_id)))
     LEFT JOIN basedata.toid_building_type_geom bt ON (((a.cross_reference)::text = (bt.toid)::text)))
  GROUP BY a.cross_reference;


--
-- Name: stocklists_history; Type: TABLE; Schema: stocklists; Owner: -
--

CREATE TABLE stocklists.stocklists_history (
    stocklist_id integer NOT NULL,
    parent_stocklist_id integer,
    stocklist_name character varying(100),
    stocklist_status_id integer,
    stocklist_manager integer,
    geom public.geometry,
    created_datetime timestamp without time zone DEFAULT now(),
    created_user integer,
    modified_datetime timestamp without time zone DEFAULT now(),
    modified_user integer,
    company_id integer,
    account_id integer
);


--
-- Name: stocklists_history_stocklist_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklists_history_stocklist_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklists_history_stocklist_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklists_history_stocklist_id_seq OWNED BY stocklists.stocklists_history.stocklist_id;


--
-- Name: stocklists_stocklist_id_seq; Type: SEQUENCE; Schema: stocklists; Owner: -
--

CREATE SEQUENCE stocklists.stocklists_stocklist_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- Name: stocklists_stocklist_id_seq; Type: SEQUENCE OWNED BY; Schema: stocklists; Owner: -
--

ALTER SEQUENCE stocklists.stocklists_stocklist_id_seq OWNED BY stocklists.stocklists.stocklist_id;


--
-- Name: vw_file_uploads; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.vw_file_uploads AS
 SELECT fu.file_id,
    to_char(fu.file_upload_datetime, 'DD mon YYYY HH24:MI'::text) AS file_upload_datetime,
    u.username,
    fu.file_name,
    fu.file_type,
    fu.stocklist_id,
    fu.file_category,
    fu.file_description,
    fu.file_size
   FROM (public.file_uploads fu
     LEFT JOIN users.users u ON ((fu.file_upload_user = u.id)))
  WHERE ((fu.entity)::text = 'stocklist'::text)
  ORDER BY fu.file_upload_datetime DESC;


--
-- Name: vw_parent_stocklist_links; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.vw_parent_stocklist_links AS
 SELECT p.stocklist_id,
    p.stocklist_name,
    p.parent_stocklist_id,
    pp.stocklist_name AS parent_stocklist_name,
    NULL::public.geometry AS geom,
    NULL::numeric AS distance
   FROM (stocklists.stocklists p
     JOIN stocklists.stocklists pp ON ((p.parent_stocklist_id = pp.stocklist_id)));


--
-- Name: vw_stocklists; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.vw_stocklists AS
 SELECT p.stocklist_id,
    p.parent_stocklist_id,
    p.stocklist_name,
    p.stocklist_status_id,
    p.stocklist_manager,
    p.created_datetime,
    p.created_user,
    p.modified_datetime,
    p.modified_user,
    u.username AS stocklist_manager_name,
    p.company_id,
    c.company_name,
    p.account_id,
    a.account_name
   FROM (((stocklists.stocklists p
     LEFT JOIN users.users u ON ((p.stocklist_manager = u.id)))
     LEFT JOIN users.companies c ON ((p.company_id = c.company_id)))
     LEFT JOIN accounts.accounts a ON ((p.account_id = a.account_id)));


--
-- Name: vw_stocklists_list; Type: VIEW; Schema: stocklists; Owner: -
--

CREATE VIEW stocklists.vw_stocklists_list AS
 WITH prems AS (
         SELECT sp.stocklist_id,
            count(*) AS total_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'MDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS mdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SMDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS smdu_premises,
            count(
                CASE
                    WHEN (bt.building_type = 'SDU'::text) THEN 1
                    ELSE NULL::integer
                END) AS sdu_premises
           FROM ((stocklists.stocklist_premises sp
             JOIN basedata.abp ON ((sp.uprn = abp.uprn)))
             JOIN basedata.toid_building_type bt ON (((abp.cross_reference)::text = (bt.toid)::text)))
          GROUP BY sp.stocklist_id
        )
 SELECT p.stocklist_id,
    COALESCE((p.parent_stocklist_id)::text, ''::text) AS parent_stocklist_id,
    COALESCE((pp.stocklist_name)::text, ''::text) AS parent_stocklist_name,
    p.stocklist_name,
    p.stocklist_status_id,
    COALESCE(ps.stocklist_status_desc, ''::character varying) AS stocklist_status_desc,
    p.stocklist_manager,
    COALESCE(upm.username, ''::character varying) AS pm_username,
    p.created_datetime,
    p.created_user,
    COALESCE(uc.username, ''::character varying) AS created_username,
    to_char(p.modified_datetime, 'YYYY-MM-DD HH24:MI'::text) AS modified_datetime,
    p.modified_user,
    um.username AS modified_username,
    a.account_id,
    a.account_name,
    prems.total_premises,
    prems.mdu_premises,
    prems.smdu_premises,
    prems.sdu_premises
   FROM (((((((stocklists.stocklists p
     LEFT JOIN users.users um ON ((p.modified_user = um.id)))
     LEFT JOIN users.users uc ON ((p.created_user = uc.id)))
     LEFT JOIN users.users upm ON ((p.stocklist_manager = upm.id)))
     LEFT JOIN stocklists.stocklist_status ps ON ((p.stocklist_status_id = ps.stocklist_status_id)))
     LEFT JOIN stocklists.stocklists pp ON ((p.parent_stocklist_id = pp.stocklist_id)))
     LEFT JOIN accounts.accounts a ON ((a.account_id = p.account_id)))
     LEFT JOIN prems ON ((p.stocklist_id = prems.stocklist_id)));


--
-- Name: DJ Boundary; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy."DJ Boundary" (
    id_0 integer NOT NULL,
    geom public.geometry(MultiPolygon),
    name character varying(22),
    id bigint,
    count bigint,
    type character varying(20),
    ring character varying(20),
    "ward name" character varying(30),
    olt bigint,
    "fp status" character varying(100),
    "oh sdu" bigint,
    "ug sdu" bigint,
    sdu bigint,
    mdu bigint,
    address character varying(100),
    "new dj" character varying(100)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'DJ Boundary'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN id_0 OPTIONS (
    column_name 'id_0'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN geom OPTIONS (
    column_name 'geom'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN name OPTIONS (
    column_name 'name'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN count OPTIONS (
    column_name 'count'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN type OPTIONS (
    column_name 'type'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN ring OPTIONS (
    column_name 'ring'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN "ward name" OPTIONS (
    column_name 'ward name'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN olt OPTIONS (
    column_name 'olt'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN "fp status" OPTIONS (
    column_name 'fp status'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN "oh sdu" OPTIONS (
    column_name 'oh sdu'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN "ug sdu" OPTIONS (
    column_name 'ug sdu'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN sdu OPTIONS (
    column_name 'sdu'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN mdu OPTIONS (
    column_name 'mdu'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN address OPTIONS (
    column_name 'address'
);
ALTER FOREIGN TABLE strategy."DJ Boundary" ALTER COLUMN "new dj" OPTIONS (
    column_name 'new dj'
);


--
-- Name: abp_building_type; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.abp_building_type (
    mdu_id integer,
    uprn bigint,
    postcode character varying(8),
    cfl_delivery_type character varying,
    building_type text,
    building_premise_count bigint
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'abp_building_type'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN mdu_id OPTIONS (
    column_name 'mdu_id'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN postcode OPTIONS (
    column_name 'postcode'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN cfl_delivery_type OPTIONS (
    column_name 'cfl_delivery_type'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN building_type OPTIONS (
    column_name 'building_type'
);
ALTER FOREIGN TABLE strategy.abp_building_type ALTER COLUMN building_premise_count OPTIONS (
    column_name 'building_premise_count'
);


--
-- Name: abp_classification_code_map; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.abp_classification_code_map (
    id integer,
    classification_code character varying(50),
    cfl_class_type character varying(50)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'abp_classification_code_map'
);
ALTER FOREIGN TABLE strategy.abp_classification_code_map ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy.abp_classification_code_map ALTER COLUMN classification_code OPTIONS (
    column_name 'classification_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_code_map ALTER COLUMN cfl_class_type OPTIONS (
    column_name 'cfl_class_type'
);


--
-- Name: abp_classification_codes; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.abp_classification_codes (
    id integer NOT NULL,
    classification_code character varying,
    class_description character varying,
    primary_code character varying,
    secondary_code character varying,
    tertiary_code character varying,
    quaternary_code character varying,
    primary_desc character varying,
    secondary_desc character varying,
    tertiary_desc character varying,
    quaternary_desc character varying,
    interest boolean,
    cfl_delivery_type character varying
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'abp_classification_codes'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN classification_code OPTIONS (
    column_name 'classification_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN class_description OPTIONS (
    column_name 'class_description'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN primary_code OPTIONS (
    column_name 'primary_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN secondary_code OPTIONS (
    column_name 'secondary_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN tertiary_code OPTIONS (
    column_name 'tertiary_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN quaternary_code OPTIONS (
    column_name 'quaternary_code'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN primary_desc OPTIONS (
    column_name 'primary_desc'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN secondary_desc OPTIONS (
    column_name 'secondary_desc'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN tertiary_desc OPTIONS (
    column_name 'tertiary_desc'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN quaternary_desc OPTIONS (
    column_name 'quaternary_desc'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN interest OPTIONS (
    column_name 'interest'
);
ALTER FOREIGN TABLE strategy.abp_classification_codes ALTER COLUMN cfl_delivery_type OPTIONS (
    column_name 'cfl_delivery_type'
);


--
-- Name: abp_london_boroughs; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.abp_london_boroughs (
    uprn bigint,
    borough_id integer,
    borough_gss_code character varying(9),
    borough character varying(22)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'abp_london_boroughs'
);
ALTER FOREIGN TABLE strategy.abp_london_boroughs ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE strategy.abp_london_boroughs ALTER COLUMN borough_id OPTIONS (
    column_name 'borough_id'
);
ALTER FOREIGN TABLE strategy.abp_london_boroughs ALTER COLUMN borough_gss_code OPTIONS (
    column_name 'borough_gss_code'
);
ALTER FOREIGN TABLE strategy.abp_london_boroughs ALTER COLUMN borough OPTIONS (
    column_name 'borough'
);


--
-- Name: abp_london_wards; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.abp_london_wards (
    uprn bigint,
    ward_id integer,
    ward_code character varying(9),
    ward character varying(70)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'abp_london_wards'
);
ALTER FOREIGN TABLE strategy.abp_london_wards ALTER COLUMN uprn OPTIONS (
    column_name 'uprn'
);
ALTER FOREIGN TABLE strategy.abp_london_wards ALTER COLUMN ward_id OPTIONS (
    column_name 'ward_id'
);
ALTER FOREIGN TABLE strategy.abp_london_wards ALTER COLUMN ward_code OPTIONS (
    column_name 'ward_code'
);
ALTER FOREIGN TABLE strategy.abp_london_wards ALTER COLUMN ward OPTIONS (
    column_name 'ward'
);


--
-- Name: active_cabs; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.active_cabs (
    cab_name character varying(11),
    borough character varying(15),
    cab_type character varying(5),
    sdu_pm character varying(24),
    router character varying(2),
    hld character varying(8),
    ring character varying(12),
    jumper_detail character varying(19),
    cardsfitted_prebuild smallint,
    addcards_onsite smallint,
    zendesk_ticket character varying(9),
    cab_landing date,
    pia_uplink_ecd date,
    pia_spurs date,
    uplink_cable_blown_ecd date,
    uplink_spliced_ecd date,
    spurs_blown_ecd date,
    spurs_spliced_ecd date,
    handover_to_netops_ecd date,
    net_opps_ecd date,
    cross_connection_ecd date,
    etd date,
    build_required_by date,
    priority smallint,
    risk_level character varying(4),
    complete character varying(24),
    redund_yes_ecd character varying(15),
    comments character varying(160),
    total_fibrecount character varying(4),
    spur_schematic_link character varying(83)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'active_cabs'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN cab_name OPTIONS (
    column_name 'cab_name'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN borough OPTIONS (
    column_name 'borough'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN cab_type OPTIONS (
    column_name 'cab_type'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN sdu_pm OPTIONS (
    column_name 'sdu_pm'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN router OPTIONS (
    column_name 'router'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN hld OPTIONS (
    column_name 'hld'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN ring OPTIONS (
    column_name 'ring'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN jumper_detail OPTIONS (
    column_name 'jumper_detail'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN cardsfitted_prebuild OPTIONS (
    column_name 'cardsfitted_prebuild'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN addcards_onsite OPTIONS (
    column_name 'addcards_onsite'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN zendesk_ticket OPTIONS (
    column_name 'zendesk_ticket'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN cab_landing OPTIONS (
    column_name 'cab_landing'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN pia_uplink_ecd OPTIONS (
    column_name 'pia_uplink_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN pia_spurs OPTIONS (
    column_name 'pia_spurs'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN uplink_cable_blown_ecd OPTIONS (
    column_name 'uplink_cable_blown_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN uplink_spliced_ecd OPTIONS (
    column_name 'uplink_spliced_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN spurs_blown_ecd OPTIONS (
    column_name 'spurs_blown_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN spurs_spliced_ecd OPTIONS (
    column_name 'spurs_spliced_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN handover_to_netops_ecd OPTIONS (
    column_name 'handover_to_netops_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN net_opps_ecd OPTIONS (
    column_name 'net_opps_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN cross_connection_ecd OPTIONS (
    column_name 'cross_connection_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN etd OPTIONS (
    column_name 'etd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN build_required_by OPTIONS (
    column_name 'build_required_by'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN priority OPTIONS (
    column_name 'priority'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN risk_level OPTIONS (
    column_name 'risk_level'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN complete OPTIONS (
    column_name 'complete'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN redund_yes_ecd OPTIONS (
    column_name 'redund_yes_ecd'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN comments OPTIONS (
    column_name 'comments'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN total_fibrecount OPTIONS (
    column_name 'total_fibrecount'
);
ALTER FOREIGN TABLE strategy.active_cabs ALTER COLUMN spur_schematic_link OPTIONS (
    column_name 'spur_schematic_link'
);


--
-- Name: area_of_interest; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.area_of_interest (
    id integer,
    geom public.geometry
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'area_of_interest'
);
ALTER FOREIGN TABLE strategy.area_of_interest ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy.area_of_interest ALTER COLUMN geom OPTIONS (
    column_name 'geom'
);


--
-- Name: borough_test; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.borough_test (
    borough character varying(80)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'borough_test'
);
ALTER FOREIGN TABLE strategy.borough_test ALTER COLUMN borough OPTIONS (
    column_name 'borough'
);


--
-- Name: boundary_costing; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.boundary_costing (
    id integer NOT NULL,
    name character varying(100),
    notes character varying(100),
    type text,
    borough text,
    olt character varying(20),
    status character varying(50),
    date_sent_to_planning date,
    created_by_user character varying,
    created_datetime timestamp without time zone,
    modified_by_user character varying,
    modified_datetime timestamp without time zone,
    total_premises numeric,
    mdu_premises numeric,
    mdu_with_wayleave numeric,
    sdu_premises numeric,
    mdu_buildings bigint,
    sdu_buildings bigint,
    pia_oh_premises numeric,
    pia_ug_l_premises numeric,
    pia_ug_c_premises numeric,
    civils_premises numeric,
    pia_oh_mdu_premises numeric,
    pia_oh_sdu_premises numeric,
    pia_ug_l_mdu_premises numeric,
    pia_ug_l_sdu_premises numeric,
    pia_ug_c_mdu_premises numeric,
    pia_ug_c_sdu_premises numeric,
    civils_mdu_premises numeric,
    civils_sdu_premises numeric,
    cab_name character varying,
    total_distance numeric,
    cluster_capex_budget numeric,
    mdu_low_capex numeric,
    sdu_low_capex numeric,
    capacity_low_capex numeric,
    civils_low_capex numeric,
    pia_ug_low_capex numeric,
    pia_oh_low_capex numeric,
    mdu_high_capex numeric,
    sdu_high_capex numeric,
    capacity_high_capex numeric,
    civils_high_capex numeric,
    pia_ug_high_capex numeric,
    pia_oh_high_capex numeric,
    mdu_wayleave_percent numeric,
    capex_low numeric,
    cppp_low numeric,
    capex_high numeric,
    cppp_high numeric,
    costing_datetime timestamp with time zone,
    geom public.geometry,
    wayleave_status character varying(100),
    network_status character varying(100),
    projects_raised_datetime timestamp without time zone,
    percent_ugl_to_ugc character varying(100),
    lead_project_id integer,
    parent_boundary_id integer,
    fibre_count integer,
    distribution_distance numeric,
    distribution_capex_low numeric,
    distribution_cppp_low numeric,
    distribution_capex_high numeric,
    distribution_cppp_high numeric
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'boundary_costing'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN name OPTIONS (
    column_name 'name'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN notes OPTIONS (
    column_name 'notes'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN type OPTIONS (
    column_name 'type'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN borough OPTIONS (
    column_name 'borough'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN olt OPTIONS (
    column_name 'olt'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN status OPTIONS (
    column_name 'status'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN date_sent_to_planning OPTIONS (
    column_name 'date_sent_to_planning'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN created_by_user OPTIONS (
    column_name 'created_by_user'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN created_datetime OPTIONS (
    column_name 'created_datetime'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN modified_by_user OPTIONS (
    column_name 'modified_by_user'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN modified_datetime OPTIONS (
    column_name 'modified_datetime'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN total_premises OPTIONS (
    column_name 'total_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_premises OPTIONS (
    column_name 'mdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_with_wayleave OPTIONS (
    column_name 'mdu_with_wayleave'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN sdu_premises OPTIONS (
    column_name 'sdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_buildings OPTIONS (
    column_name 'mdu_buildings'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN sdu_buildings OPTIONS (
    column_name 'sdu_buildings'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_oh_premises OPTIONS (
    column_name 'pia_oh_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_l_premises OPTIONS (
    column_name 'pia_ug_l_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_c_premises OPTIONS (
    column_name 'pia_ug_c_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN civils_premises OPTIONS (
    column_name 'civils_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_oh_mdu_premises OPTIONS (
    column_name 'pia_oh_mdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_oh_sdu_premises OPTIONS (
    column_name 'pia_oh_sdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_l_mdu_premises OPTIONS (
    column_name 'pia_ug_l_mdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_l_sdu_premises OPTIONS (
    column_name 'pia_ug_l_sdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_c_mdu_premises OPTIONS (
    column_name 'pia_ug_c_mdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_c_sdu_premises OPTIONS (
    column_name 'pia_ug_c_sdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN civils_mdu_premises OPTIONS (
    column_name 'civils_mdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN civils_sdu_premises OPTIONS (
    column_name 'civils_sdu_premises'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN cab_name OPTIONS (
    column_name 'cab_name'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN total_distance OPTIONS (
    column_name 'total_distance'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN cluster_capex_budget OPTIONS (
    column_name 'cluster_capex_budget'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_low_capex OPTIONS (
    column_name 'mdu_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN sdu_low_capex OPTIONS (
    column_name 'sdu_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN capacity_low_capex OPTIONS (
    column_name 'capacity_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN civils_low_capex OPTIONS (
    column_name 'civils_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_low_capex OPTIONS (
    column_name 'pia_ug_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_oh_low_capex OPTIONS (
    column_name 'pia_oh_low_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_high_capex OPTIONS (
    column_name 'mdu_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN sdu_high_capex OPTIONS (
    column_name 'sdu_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN capacity_high_capex OPTIONS (
    column_name 'capacity_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN civils_high_capex OPTIONS (
    column_name 'civils_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_ug_high_capex OPTIONS (
    column_name 'pia_ug_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN pia_oh_high_capex OPTIONS (
    column_name 'pia_oh_high_capex'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN mdu_wayleave_percent OPTIONS (
    column_name 'mdu_wayleave_percent'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN capex_low OPTIONS (
    column_name 'capex_low'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN cppp_low OPTIONS (
    column_name 'cppp_low'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN capex_high OPTIONS (
    column_name 'capex_high'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN cppp_high OPTIONS (
    column_name 'cppp_high'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN costing_datetime OPTIONS (
    column_name 'costing_datetime'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN geom OPTIONS (
    column_name 'geom'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN wayleave_status OPTIONS (
    column_name 'wayleave_status'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN network_status OPTIONS (
    column_name 'network_status'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN projects_raised_datetime OPTIONS (
    column_name 'projects_raised_datetime'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN percent_ugl_to_ugc OPTIONS (
    column_name 'percent_ugl_to_ugc'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN lead_project_id OPTIONS (
    column_name 'lead_project_id'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN parent_boundary_id OPTIONS (
    column_name 'parent_boundary_id'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN fibre_count OPTIONS (
    column_name 'fibre_count'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN distribution_distance OPTIONS (
    column_name 'distribution_distance'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN distribution_capex_low OPTIONS (
    column_name 'distribution_capex_low'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN distribution_cppp_low OPTIONS (
    column_name 'distribution_cppp_low'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN distribution_capex_high OPTIONS (
    column_name 'distribution_capex_high'
);
ALTER FOREIGN TABLE strategy.boundary_costing ALTER COLUMN distribution_cppp_high OPTIONS (
    column_name 'distribution_cppp_high'
);


--
-- Name: boundary_costing_access_routes; Type: FOREIGN TABLE; Schema: strategy; Owner: -
--

CREATE FOREIGN TABLE strategy.boundary_costing_access_routes (
    id integer,
    name character varying(100),
    route_distance double precision,
    costing_datetime timestamp with time zone,
    geom public.geometry,
    route_type character varying(20)
)
SERVER geo_db
OPTIONS (
    schema_name 'strategy',
    table_name 'boundary_costing_access_routes'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN id OPTIONS (
    column_name 'id'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN name OPTIONS (
    column_name 'name'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN route_distance OPTIONS (
    column_name 'route_distance'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN costing_datetime OPTIONS (
    column_name 'costing_datetime'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN geom OPTIONS (
    column_name 'geom'
);
ALTER FOREIGN TABLE strategy.boundary_costing_access_routes ALTER COLUMN route_type OPTIONS (
    column_name 'route_type'
);


--
