schema

var Schema = apptypes.LoadSchema{
	TableSystemTracking: apptypes.TableSpec{
		Table:     "_mobsql",
		UniqueSet: []string{"source", "source_pair", "tbl"},
		Columns: []apptypes.LoadColumn{
			{Column: "source", DType: apptypes.DTypeInt, Required: true},
			{Column: "source_pair", DType: apptypes.DTypeInt, Required: false},
			{Column: "tbl", DType: apptypes.DTypeText, Required: true},
			{Column: "checksum", DType: apptypes.DTypeText, Required: true},
			{Column: "n_rows", DType: apptypes.DTypeInt, Required: true},
			{Column: "timestamp", DType: apptypes.DTypeInt, Required: true},
		},
	},
	TableSystemMDB: apptypes.TableSpec{
		Table:     "_mdb",
		UniqueSet: []string{"mdb_source_id"},
		Columns: []apptypes.LoadColumn{
			{Column: "source", DType: apptypes.DTypeInt, Required: true},
			{Column: "data_type", DType: apptypes.DTypeText, Required: true},
			{Column: "location.bounding_box.minimum_latitude", DType: apptypes.DTypeReal},
			{Column: "location.bounding_box.minimum_longitude", DType: apptypes.DTypeReal},
			{Column: "location.bounding_box.maximum_latitude", DType: apptypes.DTypeReal},
			{Column: "location.bounding_box.maximum_longitude", DType: apptypes.DTypeReal},
			{Column: "location.country_code", DType: apptypes.DTypeText},
			{Column: "location.subdivision_name", DType: apptypes.DTypeText},
			{Column: "location.municipality", DType: apptypes.DTypeText},
			{Column: "urls.latest", DType: apptypes.DTypeText, Required: true},
			{Column: "mdb_source_id", DType: apptypes.DTypeInt, Required: true},
			{Column: "name", DType: apptypes.DTypeText},
			{Column: "status", DType: apptypes.DTypeText},
			{Column: "provider", DType: apptypes.DTypeText, Required: true},
		},
	},
	TablesGTFS: tablesGTFS,
	ViewsSystem: []apptypes.View{
		{
			Name: "_vsources",
			Doc:  "Internal view overlaying the mobility database & mobsql tracking table",
			SQLCreateStatement: `
        select
          md.mdb_source_id as mdbid,
          tl.tables_loaded,
          tc.tables_computed,
          datetime(tl.timestamp, 'unixepoch', 'localtime') as timestamp_loaded,
          datetime(tc.timestamp, 'unixepoch', 'localtime') as timestamp_computed,
          tl.timestamp is not null as loaded,
          tc.timestamp is not null as computed,
          coalesce(md.status, 'active') as status,
          md.'urls.latest' as url,
          md.'location.bounding_box.minimum_latitude' as min_lat,
          md.'location.bounding_box.minimum_longitude' as min_lon,
          md.'location.bounding_box.maximum_latitude' as max_lat,
          md.'location.bounding_box.maximum_longitude' as max_lon,
          json_array(
            md.'location.bounding_box.minimum_latitude',
            md.'location.bounding_box.minimum_longitude',
            md.'location.bounding_box.maximum_latitude',
            md.'location.bounding_box.maximum_longitude'
          ) as bbox,
          6371.0 * acos(
            sin(90.0 - md.'location.bounding_box.minimum_latitude' * pi() / 180.0) *
            sin(90.0 - md.'location.bounding_box.maximum_latitude' * pi() / 180.0) *
            cos(
              (md.'location.bounding_box.minimum_longitude' * pi() / 180.0) - 
              (md.'location.bounding_box.maximum_longitude' * pi() / 180.0)
            ) +
            cos(90.0 - md.'location.bounding_box.minimum_latitude' * pi() / 180.0) *
            cos(90.0 - md.'location.bounding_box.maximum_latitude' * pi() / 180.0)
          ) as km,
          md.'location.subdivision_name' as subdivision,
          md.'location.country_code' as country,
          md.'location.municipality' as municipality,
          md.provider,
          md.name
        from
          (select * from _mdb where data_type = 'gtfs' and 'urls.latest' is not null) md
          left join (select source, group_concat(tbl) as tables_loaded, * from _mobsql where tbl not like '_cv%' group by source) tl
            on md.mdb_source_id = tl.source and tl.source != -1
          left join (
            select source, group_concat(tbl || iif(source_dual is not null, ':' || source_dual, '')) as tables_computed, timestamp,
            *
            from (
              select
                source, tbl, timestamp,
                iif(source is not null and source_pair is not null, source || ':' || source_pair, null) as source_dual
              from _mobsql
              where tbl like '_cv%'
              union
              select
                source_pair, tbl, timestamp,
                iif(source is not null and source_pair is not null, source || ':' || source_pair, null) as source_dual
              from _mobsql
              where tbl like '_cv%'
            ) g
            group by source
          ) tc
            on md.mdb_source_id = tc.source and tc.source != -1
      `,
		},
	},
}