# settings = { "prefix": "ss", "PK": "IDENTITY", "semantics": "CHAR" }

-- SkySutra: Quick SQL first draft
-- Kathuko APEX 26.2, second course, Part 1, Lesson 1.8
--
-- Paste into SQL Workshop > Utilities > Quick SQL and look at the SQL it makes.
-- Don't run it: skysutra_schema.sql is the finished version. This draft is
-- deliberately "good enough, not finished". Lesson 1.8 lists what a generator
-- can't decide for us:
--   * ranges on angles and magnitudes (right ascension 0 to 360, declination -90 to 90)
--   * "exactly one target" for an observation (a rule across four columns)
--   * the catalog's natural key (HR number) next to the surrogate id
--   * time-of-day keys in UTC (Oracle won't key on TIMESTAMP WITH TIME ZONE)
--   * the point geometry kept in step with latitude and longitude (a trigger)
--   * which deletes cascade

constellations
  abbr vc3 /nn /unique
  name vc40 /nn
  vedic_name vc40
  area_sq_deg num
  description vc4000
  stars
    hr_number int /nn /unique
    proper_name vc40
    bayer vc20
    ra_deg num /nn
    dec_deg num /nn
    pm_ra_mas num
    pm_dec_mas num
    magnitude num /nn
    b_v num
    spectral_type vc20
    vedic_name vc40

nakshatras
  seq int /nn /unique
  name vc40 /nn
  english_name vc80 /nn
  start_deg num /nn
  end_deg num /nn
  junction_star_id /fk stars
  symbol vc80
  description vc4000

bodies
  code vc10 /nn /unique
  name vc40 /nn
  vedic_name vc20
  kind vc10 /nn /check STAR, MOON, PLANET, NODE
  usno_name vc20

users
  email vc255 /nn /unique
  display_name vc100 /nn
  theme vc15 /nn /check NIGHT, DAY, NIGHT_VISION /default NIGHT
  created_at tstz /nn
  places
    name vc100 /nn
    latitude num /nn
    longitude num /nn
    elevation_m int /nn /default 0
    time_zone vc64 /nn
    bortle int
    is_home vc1 /nn /check Y, N /default N

observations
  user_id /fk users /nn
  place_id /fk places /nn
  star_id /fk stars
  body_id /fk bodies
  constellation_id /fk constellations
  other_target vc200
  observed_at tstz /nn
  seeing int
  transparency int
  instrument vc200
  notes vc4000
  visibility vc10 /nn /check PRIVATE, SHARED, PUBLIC /default PRIVATE
  status vc10 /nn /check DRAFT, IN_REVIEW, PUBLISHED, RETURNED /default DRAFT
  published_at tstz
  observation_photos
    image blob /nn
    mime_type vc100 /nn
    file_name vc400
    caption vc400