CREATE TABLE atmospheres (
    object_id               TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    surface_pressure_bar    REAL,
    pressure_note           TEXT,
    temperature_k           REAL,
    temperature_note        TEXT,
    density_kg_m3           REAL,
    scale_height_km         REAL,
    mean_molecular_weight   REAL,
    wind_note               TEXT,
    composition_json        TEXT,                  -- [{"species":"N2","fraction":0.78,"unit":"%"},…]
    source                  TEXT NOT NULL DEFAULT 'NASA Planetary Fact Sheet'
);

CREATE TABLE build_meta (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at      TEXT NOT NULL,
    finished_at     TEXT,
    mode            TEXT NOT NULL,              -- 'full','nightly'
    row_count       INTEGER,
    notes           TEXT
);

CREATE TABLE catalogue_identity (
    singleton INTEGER PRIMARY KEY CHECK(singleton=1),
    payload TEXT NOT NULL
);

CREATE TABLE classifications (
    object_id   TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    label       TEXT NOT NULL,           -- 'NEO','PHA','Trojan','Hilda','MBA','Centaur','KBO','SDO','Inner','Outer'
    PRIMARY KEY (object_id, label)
);

CREATE TABLE close_approaches (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    object_id       TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    body            TEXT NOT NULL,                 -- 'Earth', 'Moon', 'Venus', …
    cd_jd           REAL NOT NULL,
    cd_iso          TEXT NOT NULL,
    dist_au         REAL,
    dist_min_au     REAL,
    dist_max_au     REAL,
    v_rel_km_s      REAL,
    v_inf_km_s      REAL,
    t_sigma         TEXT,                          -- formatted uncertainty, e.g. '04:08'
    orbit_ref       TEXT,
    source          TEXT NOT NULL DEFAULT 'JPL CAD',
    UNIQUE (object_id, body, cd_iso)                -- minute resolution: CAD and SBDB-lookup rows dedupe
);

CREATE TABLE designations (
    object_id       TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    designation     TEXT NOT NULL,                 -- '1', 'A801 AA', '2004 MN4', '1P'
    kind            TEXT NOT NULL CHECK (kind IN ('number','provisional','alternate','name')),
    source          TEXT,
    PRIMARY KEY (object_id, designation)
);

CREATE TABLE discoveries (
    object_id       TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    discovered_on   TEXT,                          -- ISO date
    discoverer      TEXT,
    site            TEXT,
    location        TEXT,
    citation        TEXT,
    reference       TEXT,
    source          TEXT NOT NULL,                 -- 'MPC', 'JPL SBDB', 'seed'
    updated_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE enrichment_state (
    object_id       TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    tier            INTEGER NOT NULL DEFAULT 2,    -- 1 = shown on the site, 2 = everything else
    lookup_at       TEXT,
    lookup_status   TEXT                           -- 'ok', 'not_found', 'error'
);

CREATE TABLE exoplanet_hosts (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    ra_deg REAL, dec_deg REAL,
    distance_pc REAL CHECK (distance_pc > 0),
    distance_error_plus_pc REAL, distance_error_minus_pc REAL,
    x_pc REAL, y_pc REAL, z_pc REAL,
    galactocentric_x_pc REAL, galactocentric_y_pc REAL, galactocentric_z_pc REAL,
    coordinate_source_planet TEXT NOT NULL,
    coordinate_metadata TEXT NOT NULL,
    source TEXT NOT NULL,
    retrieved_at TEXT NOT NULL
);

CREATE TABLE exoplanets (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    host_id TEXT NOT NULL REFERENCES exoplanet_hosts(id),
    discovery_method TEXT, discovery_year INTEGER,
    radius_earth REAL, mass_earth REAL, mass_provenance TEXT,
    period_days REAL, semi_major_axis_au REAL, equilibrium_temperature_k REAL,
    controversial INTEGER NOT NULL DEFAULT 0 CHECK (controversial IN (0, 1)),
    source TEXT NOT NULL, source_url TEXT NOT NULL, retrieved_at TEXT NOT NULL,
    -- Original selected archive fields include asymmetric errors, limits and references.
    source_data TEXT NOT NULL
);

CREATE TRIGGER "identity_invalidate_068d009b0a383048a345" AFTER UPDATE ON "orbital_elements" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_0d73d40b591f4518e839" AFTER UPDATE ON "atmospheres" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_11c2d6c2740f1d37d804" AFTER UPDATE ON "classifications" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_184ff049281910539cb1" AFTER UPDATE ON "close_approaches" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_197b98c1adba87fa02e1" AFTER DELETE ON "objects" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_1c5afc09b96601dc02db" AFTER DELETE ON "sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_1e4d18769186c58bbdbd" AFTER DELETE ON "physical_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_1f5e70fbbe1616246d83" AFTER DELETE ON "meteor_showers" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_24d51a22727980570777" AFTER INSERT ON "classifications" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_26bb7d114ad3852d61f7" AFTER INSERT ON "close_approaches" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_29e7fa0d907ce7296be2" AFTER DELETE ON "exoplanets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_2a4b3e0bd6ab2b2dc3cc" AFTER INSERT ON "objects" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_358d1b3d04dc373684cd" AFTER DELETE ON "starter_targets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_3b15082945cc26faf463" AFTER INSERT ON "sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_4232f3305a52bc8f5b61" AFTER INSERT ON "rings" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_442a14cd4d593b5011ed" AFTER DELETE ON "build_meta" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_49a574a9085f05a33e91" AFTER DELETE ON "orbital_elements" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_51be3c4dcee7f72419b2" AFTER DELETE ON "impact_monitoring" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_57e13f5436bb2096f988" AFTER UPDATE ON "objects" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_66fc6b000d9bb7d2c423" AFTER DELETE ON "visual_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_70bc5434177a5ae8c2b0" AFTER DELETE ON "discoveries" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_760390c3f71bb2f2fafb" AFTER DELETE ON "starter_sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_7bf10458365b43afe859" AFTER INSERT ON "discoveries" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_7f70d1f099a6980e0f75" AFTER INSERT ON "visual_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_91efa8e50270ce8a0b05" AFTER INSERT ON "starter_targets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_951fdf63387776558520" AFTER DELETE ON "close_approaches" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_962b33c90cf91ef9406b" AFTER INSERT ON "exoplanets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_96f961d547efe56c733e" AFTER INSERT ON "atmospheres" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_98a3259c5059ea3e8191" AFTER UPDATE ON "exoplanets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_9c6e32cad5d5025a4a13" AFTER INSERT ON "orbital_elements" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_9f28396fa17c668efecd" AFTER UPDATE ON "starter_sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_a296ad8bdfc5392580e4" AFTER INSERT ON "designations" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_a4d14bd30dad4fe55813" AFTER INSERT ON "physical_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_a898e5754ad2434e68a3" AFTER DELETE ON "classifications" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_a9500f7e6b768c524fe9" AFTER INSERT ON "impact_monitoring" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_aae82677d5e5b5bd4757" AFTER UPDATE ON "rings" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_ac0b5836ebf1d5989220" AFTER UPDATE ON "sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_aff373a101183070c792" AFTER UPDATE ON "discoveries" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_b13258fd3beea68f88ac" AFTER DELETE ON "rings" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_b7f804e6e3dfc89e1184" AFTER INSERT ON "exoplanet_hosts" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_bb027bcf083db52d9808" AFTER UPDATE ON "starter_targets" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_bc1284786638d3e950e0" AFTER UPDATE ON "radar_observations" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_cec889b86199a2b192e6" AFTER UPDATE ON "build_meta" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_d11dde522ab0c9de9e91" AFTER INSERT ON "meteor_showers" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_d144c1815555d08a2104" AFTER UPDATE ON "impact_monitoring" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_d36ae2f9d53c5c6ffdf6" AFTER INSERT ON "starter_sources" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_d76a9f7961c01a7ce435" AFTER UPDATE ON "visual_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_d87b01a77203f306bf70" AFTER DELETE ON "designations" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_dbc7dc1b2fe8229618af" AFTER DELETE ON "atmospheres" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_ddf050166a13ace874fb" AFTER UPDATE ON "physical_properties" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_e154a0de9cb5c500e1af" AFTER INSERT ON "build_meta" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_edb3f2bb6f0208490626" AFTER INSERT ON "radar_observations" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_f5410e53c6454fc32f7a" AFTER DELETE ON "exoplanet_hosts" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_f70c4bd65adbb1a34e97" AFTER UPDATE ON "exoplanet_hosts" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_f9f73f873f95f5c4e766" AFTER UPDATE ON "meteor_showers" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_ff197f2db54d3ea19abc" AFTER UPDATE ON "designations" BEGIN DELETE FROM catalogue_identity; END;

CREATE TRIGGER "identity_invalidate_ff6d7217464e84bd66e0" AFTER DELETE ON "radar_observations" BEGIN DELETE FROM catalogue_identity; END;

CREATE INDEX idx_ca_body   ON close_approaches(body, cd_jd);

CREATE INDEX idx_ca_date   ON close_approaches(cd_jd);

CREATE INDEX idx_ca_object ON close_approaches(object_id, cd_jd);

CREATE INDEX idx_class_label ON classifications(label);

CREATE INDEX idx_designations_des ON designations(designation);

CREATE INDEX idx_enrich_tier ON enrichment_state(tier, lookup_at);

CREATE INDEX idx_exohost_distance ON exoplanet_hosts(distance_pc);

CREATE INDEX idx_exoplanets_host ON exoplanets(host_id);

CREATE INDEX idx_exoplanets_method ON exoplanets(discovery_method);

CREATE INDEX idx_objects_design    ON objects(designation);

CREATE INDEX idx_objects_name      ON objects(name COLLATE NOCASE);

CREATE INDEX idx_objects_parent    ON objects(parent_id);

CREATE INDEX idx_objects_type      ON objects(object_type);

CREATE INDEX idx_oe_a      ON orbital_elements(semi_major_axis_au);

CREATE INDEX idx_oe_class  ON orbital_elements(orbit_class_code);

CREATE INDEX idx_oe_moid   ON orbital_elements(moid_au);

CREATE INDEX idx_pp_radius ON physical_properties(radius_km);

CREATE INDEX idx_radar_object ON radar_observations(object_id);

CREATE INDEX idx_rings_parent ON rings(parent_id);

CREATE INDEX idx_showers_code   ON meteor_showers(code);

CREATE INDEX idx_showers_parent ON meteor_showers(parent_object_id);

CREATE INDEX idx_showers_status ON meteor_showers(status_code);

CREATE INDEX idx_sources_name   ON sources(source_name);

CREATE INDEX idx_sources_object ON sources(object_id);

CREATE INDEX idx_vp_h ON visual_properties(absolute_magnitude_h);

CREATE TABLE impact_monitoring (
    object_id       TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    flagged         INTEGER NOT NULL DEFAULT 0,    -- listed by Sentry / NEODyS
    source          TEXT NOT NULL DEFAULT 'JPL SBDB',
    retrieved_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE meteor_showers (
    iau_no                  INTEGER NOT NULL,
    ad_no                   INTEGER NOT NULL,
    code                    TEXT NOT NULL,
    name                    TEXT NOT NULL,
    status_code             INTEGER,
    status_label            TEXT,
    activity                TEXT,
    solar_longitude_deg     REAL,
    ra_deg                  REAL,
    dec_deg                 REAL,
    dra_deg_per_day         REAL,
    ddec_deg_per_day        REAL,
    vg_km_s                 REAL,
    a_au                    REAL,
    q_au                    REAL,
    e                       REAL,
    peri_deg                REAL,
    node_deg                REAL,
    incl_deg                REAL,
    n_members               INTEGER,
    shower_group            TEXT,
    parent_body             TEXT,
    parent_object_id        TEXT REFERENCES objects(id),
    technique               TEXT,
    reference               TEXT,
    submitted_on            TEXT,
    source                  TEXT NOT NULL DEFAULT 'IAU MDC',
    PRIMARY KEY (iau_no, ad_no)
);

CREATE TABLE objects (
    id              TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    designation     TEXT,                -- e.g. "(2) Pallas", "1P/Halley"
    object_type     TEXT NOT NULL CHECK (object_type IN (
                        'star','planet','moon','dwarf_planet','dwarf_planet_candidate',
                        'asteroid','comet','tno','centaur','trojan','hilda','neo','pha'
                    )),
    parent_id       TEXT REFERENCES objects(id) ON DELETE SET NULL,
    discoverer      TEXT,
    discovery_date  TEXT,                -- ISO 8601 (date or year)
    wikipedia_url   TEXT,
    notes           TEXT,
    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now')),
    updated_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE VIRTUAL TABLE objects_fts USING fts5(
    id UNINDEXED, name, designation, alt, tokenize = 'unicode61 remove_diacritics 2'
);

CREATE TABLE 'objects_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;

CREATE TABLE 'objects_fts_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3);

CREATE TABLE 'objects_fts_data'(id INTEGER PRIMARY KEY, block BLOB);

CREATE TABLE 'objects_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);

CREATE TABLE 'objects_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;

CREATE TABLE orbital_elements (
    object_id                       TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    epoch                           TEXT,        -- JD or ISO 8601
    epoch_jd                        REAL,        -- Julian Date for the elements
    frame                           TEXT,        -- 'J2000' usually
    centre                          TEXT,        -- 'Sun','Earth','Jupiter',...
    semi_major_axis_au              REAL,
    eccentricity                    REAL,
    inclination_deg                 REAL,
    longitude_ascending_node_deg    REAL,
    argument_periapsis_deg          REAL,
    mean_anomaly_deg                REAL,
    orbital_period_days             REAL,
    perihelion_au                   REAL,
    aphelion_au                     REAL,
    mean_motion_deg_per_day         REAL,
    -- v2: everything else the SBDB query API publishes about the orbit
    perihelion_time_jd              REAL,        -- tp
    moid_au                         REAL,        -- Earth MOID
    moid_jupiter_au                 REAL,
    tisserand_jupiter               REAL,        -- t_jup
    condition_code                  INTEGER,     -- MPC "U" 0 (best) … 9
    data_arc_days                   REAL,
    first_obs                       TEXT,
    last_obs                        TEXT,
    n_obs_used                      INTEGER,
    n_delay_obs_used                INTEGER,     -- radar delay
    n_doppler_obs_used              INTEGER,     -- radar Doppler
    rms_arcsec                      REAL,
    solution_date                   TEXT,
    orbit_id                        TEXT,
    producer                        TEXT,
    orbit_source                    TEXT,        -- ORB / JPL / MPC …
    equinox                         TEXT,
    two_body                        TEXT,
    pe_used                         TEXT,        -- planetary ephemeris
    sb_used                         TEXT,        -- small-body perturber set
    orbit_class_code                TEXT,        -- MBA, APO, TNO, JFc …
    orbit_class_name                TEXT,
    sigma_e REAL, sigma_a REAL, sigma_q REAL, sigma_i REAL, sigma_om REAL,
    sigma_w REAL, sigma_ma REAL, sigma_tp REAL, sigma_per REAL, sigma_n REAL, sigma_ad REAL,
    updated_at                      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE physical_properties (
    object_id                   TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    radius_km                   REAL,         -- mean / volumetric radius
    equatorial_radius_km        REAL,
    polar_radius_km             REAL,
    mass_kg                     REAL,
    density_g_cm3               REAL,
    rotation_period_hours       REAL,         -- sidereal
    axial_tilt_deg              REAL,
    surface_gravity_m_s2        REAL,
    escape_velocity_km_s        REAL,
    -- v2: SBDB physical parameters + the rest of the NASA fact-sheet bulk block
    gm_km3_s2                   REAL,
    slope_g                     REAL,         -- H-G phase slope
    extent_km                   TEXT,         -- tri-axial "a x b x c"
    pole_ra_dec                 TEXT,         -- "RA/Dec" of the spin pole, deg
    diameter_sigma_km           REAL,
    volume_km3                  REAL,
    ellipticity                 REAL,
    moment_of_inertia           REAL,         -- I/MR²
    j2                          REAL,         -- ×10⁻⁶
    magnetic_field              TEXT,         -- Yes / No / Unknown
    length_of_day_hours         REAL,
    synodic_period_days         REAL,
    mean_orbital_velocity_km_s  REAL,
    min_orbital_velocity_km_s   REAL,
    max_orbital_velocity_km_s   REAL,
    tropical_period_days        REAL,
    mean_temperature_k          REAL,
    black_body_temperature_k    REAL,
    solar_irradiance_w_m2       REAL,
    updated_at                  TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE radar_observations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    object_id       TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    epoch           TEXT,
    obs_type        TEXT,
    reference       TEXT,
    source          TEXT NOT NULL DEFAULT 'JPL SBDB'
);

CREATE TABLE rings (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id       TEXT NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    inner_radius_km REAL,
    outer_radius_km REAL,
    width_km        REAL,
    thickness_km    REAL,
    notes           TEXT
);

CREATE TABLE sources (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    object_id       TEXT REFERENCES objects(id) ON DELETE CASCADE,
    table_name      TEXT NOT NULL,                 -- 'orbital_elements','physical_properties',...
    field_name      TEXT,                          -- nullable: whole-row provenance
    source_name     TEXT NOT NULL,                 -- 'JPL Horizons','JPL SBDB','MPC','NASA Fact Sheet'
    source_url      TEXT,
    retrieved_at    TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE TABLE starter_sources (source TEXT PRIMARY KEY, payload TEXT NOT NULL);

CREATE TABLE starter_targets (id TEXT PRIMARY KEY, payload TEXT NOT NULL);

CREATE TABLE visual_properties (
    object_id              TEXT PRIMARY KEY REFERENCES objects(id) ON DELETE CASCADE,
    geometric_albedo       REAL,
    bond_albedo            REAL,
    absolute_magnitude_h   REAL,
    colour_b_v             REAL,
    spectral_type          TEXT,            -- SMASSII (Bus) taxonomy — NOT the orbit class (v1 bug)
    dominant_colour_hex    TEXT,            -- best-effort, for rendering models
    -- v2
    colour_u_b             REAL,
    colour_i_r             REAL,
    spectral_type_tholen   TEXT,
    magnitude_v10          REAL,            -- V(1,0), planets
    comet_m1               REAL,            -- total magnitude parameters
    comet_k1               REAL,
    comet_m2               REAL,            -- nuclear
    comet_k2               REAL,
    nongrav_a1             REAL,            -- non-gravitational accelerations
    nongrav_a2             REAL,
    nongrav_a3             REAL,
    nongrav_dt             REAL,
    updated_at             TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ','now'))
);

CREATE VIEW v_comets AS
SELECT o.id, o.name, o.designation, o.discoverer, o.discovery_date,
       oe.semi_major_axis_au, oe.eccentricity, oe.inclination_deg,
       oe.orbital_period_days, oe.perihelion_au, oe.aphelion_au
FROM objects o
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
WHERE o.object_type = 'comet'
ORDER BY oe.orbital_period_days;

CREATE VIEW v_dwarf_planets AS
SELECT o.id, o.name, o.designation, o.object_type,
       p.radius_km, p.mass_kg,
       oe.semi_major_axis_au, oe.eccentricity, oe.inclination_deg,
       oe.orbital_period_days
FROM objects o
LEFT JOIN physical_properties p ON p.object_id = o.id
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
WHERE o.object_type IN ('dwarf_planet','dwarf_planet_candidate')
ORDER BY oe.semi_major_axis_au;

CREATE VIEW v_moons_by_planet AS
SELECT parent.name AS planet,
       o.id, o.name, o.designation, o.discoverer, o.discovery_date,
       p.radius_km, p.mass_kg,
       oe.semi_major_axis_au, oe.orbital_period_days, oe.eccentricity, oe.inclination_deg
FROM objects o
JOIN objects parent ON parent.id = o.parent_id
LEFT JOIN physical_properties p ON p.object_id = o.id
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
WHERE o.object_type = 'moon'
ORDER BY planet, oe.semi_major_axis_au;

CREATE VIEW v_neos AS
SELECT o.id, o.name, o.designation,
       oe.semi_major_axis_au, oe.perihelion_au, oe.eccentricity, oe.inclination_deg,
       vp.absolute_magnitude_h
FROM objects o
JOIN classifications c ON c.object_id = o.id AND c.label = 'NEO'
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
LEFT JOIN visual_properties vp ON vp.object_id = o.id
ORDER BY vp.absolute_magnitude_h;

CREATE VIEW v_object_counts AS
SELECT object_type, COUNT(*) AS n
FROM objects
GROUP BY object_type
ORDER BY n DESC;

CREATE VIEW v_phas AS
SELECT o.id, o.name, o.designation,
       oe.semi_major_axis_au, oe.perihelion_au, oe.eccentricity,
       vp.absolute_magnitude_h
FROM objects o
JOIN classifications c ON c.object_id = o.id AND c.label = 'PHA'
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
LEFT JOIN visual_properties vp ON vp.object_id = o.id
ORDER BY vp.absolute_magnitude_h;

CREATE VIEW v_planets AS
SELECT o.id, o.name, o.designation,
       p.radius_km, p.mass_kg, p.density_g_cm3, p.rotation_period_hours,
       p.axial_tilt_deg, p.surface_gravity_m_s2, p.escape_velocity_km_s,
       oe.semi_major_axis_au, oe.eccentricity, oe.inclination_deg,
       oe.orbital_period_days, oe.perihelion_au, oe.aphelion_au,
       vp.geometric_albedo, vp.dominant_colour_hex
FROM objects o
LEFT JOIN physical_properties p  ON p.object_id  = o.id
LEFT JOIN orbital_elements    oe ON oe.object_id = o.id
LEFT JOIN visual_properties   vp ON vp.object_id = o.id
WHERE o.object_type = 'planet'
ORDER BY oe.semi_major_axis_au;

CREATE VIEW v_tnos AS
SELECT o.id, o.name, o.designation,
       oe.semi_major_axis_au, oe.eccentricity, oe.inclination_deg,
       oe.orbital_period_days,
       vp.absolute_magnitude_h
FROM objects o
LEFT JOIN orbital_elements oe ON oe.object_id = o.id
LEFT JOIN visual_properties vp ON vp.object_id = o.id
WHERE o.object_type IN ('tno','centaur')
ORDER BY oe.semi_major_axis_au;