text: -- Deploy nz-buildings:buildings_common/schema_and_tables to pg
  
  BEGIN;
  
  ------------------------------------------------------------------------------
  -- Create buildings common schema and tables
  
  -- Tables
  -- capture_method
  -- capture_source_group
  -- capture_source
  ------------------------------------------------------------------------------
  
  -- SCHEMA
  
  -- buildings_common
  
  CREATE SCHEMA IF NOT EXISTS buildings_common;
  
  COMMENT ON SCHEMA buildings_common IS
  'Schema that holds tables referenced in more than one other schema.';
  
  -- TABLES
  
  -- Capture Method
  -- Lookup table that holds all of the methods by which the geometry was captured.
  
  CREATE TABLE IF NOT EXISTS buildings_common.capture_method (
        capture_method_id serial PRIMARY KEY
      , value character varying(40) NOT NULL
  );
  
  COMMENT ON TABLE buildings_common.capture_method IS
  'Lookup table that holds all of the methods by which the geometry was captured.';
  
  COMMENT ON COLUMN buildings_common.capture_method.capture_method_id IS
  'Unique identifier for the capture method.';
  COMMENT ON COLUMN buildings_common.capture_method.value IS
  'The method by which the geometry was captured.';
  
  -- Capture Source Group
  -- Lookup table that holds all of the capture source groups. Capture source
  -- groups are categories of data sources, e.g. NZ Aerial Imagery
  
  CREATE TABLE IF NOT EXISTS buildings_common.capture_source_group (
        capture_source_group_id serial PRIMARY KEY
      , value character varying(80) NOT NULL
      , description character varying(400) NOT NULL
  );
  
  COMMENT ON TABLE buildings_common.capture_source_group IS
  'Lookup table that holds all of the capture source groups. Capture source '
  'groups are categories of data sources, e.g. NZ Aerial Imagery';
  
  COMMENT ON COLUMN buildings_common.capture_source_group.capture_source_group_id IS
  'Unique identifier for the capture source group.';
  COMMENT ON COLUMN buildings_common.capture_source_group.value IS
  'The name of the capture source group e.g. NZ Aerial Imagery.';
  COMMENT ON COLUMN buildings_common.capture_source_group.description IS
  'The description of the capture source group e.g. external_source_id for this '
  'group links to the primary key of the NZ Imagery Surveys layer, available '
  'on LINZ Data Service at: https://data.linz.govt.nz/layer/95677.';
  
  -- Capture Source
  -- Lookup table that holds all of the methods by which the geometry was captured.
  
  CREATE TABLE IF NOT EXISTS buildings_common.capture_source (
        capture_source_id serial PRIMARY KEY
      , capture_source_group_id integer NOT NULL REFERENCES buildings_common.capture_source_group (capture_source_group_id)
      , external_source_id character varying(250)
  );
  
  COMMENT ON TABLE buildings_common.capture_source IS
  'Lookup table that holds all of the methods by which the geometry was captured.';
  
  COMMENT ON COLUMN buildings_common.capture_source.capture_source_id IS
  'Unique identifier for the capture source.';
  COMMENT ON COLUMN buildings_common.capture_source.capture_source_group_id IS
  'Foreign key to the buildings_common.capture_source_group table.';
  COMMENT ON COLUMN buildings_common.capture_source.external_source_id IS
  'Stores a reference to an externally managed identifier that can be linked '
  'in order to find out more information about the source.';
  
  CREATE INDEX idx_capture_source_capture_source_group_id
      ON buildings_common.capture_source USING btree (capture_source_group_id);
  
  COMMIT;
  