---------------------------------------------------------------------------
-- FILE      : star-tables.sql
-- SUBJECT   : Variable star database
-- PROGRAMMER: (C) Copyright 2023 by Peter C. Chapin
--
-- Please send comments or bug reports to
--
--      Peter Chapin
--      Department of Computer Information Systems
--      Vermont Technical College
--      Williston, VT 05495
--      pchapin@vtc.edu
---------------------------------------------------------------------------

-- I want to be sure only legal constellation abbreviations are used. Since there are quite a
-- few of them, I will store the legal abbreviations in a table and use referential integrity
-- to check validity.
--
CREATE TABLE IF NOT EXISTS constellation(
        abbrev          CHAR(3) PRIMARY KEY,
        name            VARCHAR(20), -- 'Triangulum Australe' has 19 characters.
        genitive        VARCHAR(20)  -- 'Trianguli Australis' has 19 characters.
);

CREATE TABLE IF NOT EXISTS star(
        name            CHAR(5),
        const           CHAR(3) REFERENCES constellation(abbrev),
        ra              NUMERIC(8,6) CHECK(  0 <= ra  AND ra  <  24), -- Right ascension
        dec             NUMERIC(8,6) CHECK(-90 <= dec AND dec <= 90), -- Declination
        max_bright      NUMERIC(6,3),  -- The smaller the value, the brighter the star.
        min_bright      NUMERIC(6,3),  -- ... a larger number than max_bright.
        kind            CHAR(10),      -- The "kind" (or type) of variable.
        var_period      NUMERIC(7,3),  -- Period of variability in days.
        notes           VARCHAR(80),   -- Free form text with additional information.
        PRIMARY KEY(name, const)       -- For example: ('RR','Lyr')
);

CREATE TABLE observer(
        id              INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
        fname           CHAR(15) NOT NULL,
        minit           CHAR(1),
        lname           CHAR(15) NOT NULL,
        honorific       CHAR(4)  CHECK(honorific IN ('Mr', 'Ms', 'Mrs', 'Miss', 'Dr')),
        mailing_addr    VARCHAR(80),  -- Not structured for now.
        email_addr      VARCHAR(40),  -- Intended format: 'name@host.dom'
        phone_num       CHAR(12),     -- Intended format: 'AAA-123-4567'
        contact         CHAR(1)  CHECK(contact IN ('m', 'e', 'p')),
        bday            DATE,
        deceased        CHAR(1)  CHECK(deceased IN ('y', 'n'))
);

CREATE TABLE location(
        id              INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
        owner_id        INTEGER NOT NULL REFERENCES observer(id),
        longitude       NUMERIC(9,6) CHECK(-180 <= longitude AND longitude <= 180) NOT NULL,
        latitude        NUMERIC(8,6) CHECK( -90 <= latitude  AND latitude  <=  90) NOT NULL,
        elevation       INTEGER,      -- Meters above mean sea level.
        name            VARCHAR(40)
);

-- The main event!
CREATE TABLE observation(
        id              INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
        name            CHAR(5) NOT NULL,
        const           CHAR(3) NOT NULL,
        observer_id     INTEGER NOT NULL REFERENCES observer(id),
        location_id     INTEGER REFERENCES location(id),
        datetime        TIMESTAMP(0) WITH TIME ZONE,
        brightness      NUMERIC(6,3),
        notes           VARCHAR(80),
        FOREIGN KEY(name, const) REFERENCES star(name, const)
);
