drop table if exists `fields`;
create table `fields`(
    field_id bigint not null auto_increment comment 'Auto-assigned internal MOPS field ID',
    epoch_mjd real not null                 comment 'Time (epoch) of field acquisition, MJD, UTC',
    uncorrected_epoch_mjd real not null     comment 'Uncorrected epoch of field acquisition, MJD, UTC',
    ra_deg real not null                    comment 'RA, deg, of field center',
    dec_deg real not null                   comment 'Dec, deg, of field center',
    survey_mode varchar(120)                comment 'PS-defined survey mode, e.g. ESS, MSS, 3PI, CAL',
    time_start real                         comment 'Time of field start acquisition, MJD, UTC',
    time_stop real                          comment 'Time of field stop acquisition, MJD, UTC',
    filter_id char(2)                       comment 'Filter used for acquisition, g/r/i/z/y/w',
    limiting_mag real                       comment 'Field limiting magnitude',
    ra_sigma real                           comment 'Uncertainty in RA',
    dec_sigma real                          comment 'Uncertainty in Dec',
    obscode char(3)                         comment 'MPC observatory code',
    de1 real                                comment 'IPP-defined detection efficiency 1',
    de2 real                                comment 'IPP-defined detection efficiency 2',
    de3 real                                comment 'IPP-defined detection efficiency 3',
    de4 real                                comment 'IPP-defined detection efficiency 4',
    de5 real                                comment 'IPP-defined detection efficiency 5',
    de6 real                                comment 'IPP-defined detection efficiency 6',
    de7 real                                comment 'IPP-defined detection efficiency 7',
    de8 real                                comment 'IPP-defined detection efficiency 8',
    de9 real                                comment 'IPP-defined detection efficiency 9',
    de10 real                               comment 'IPP-defined detection efficiency 10',

    ocnum int                               comment 'pre-computed observing cycle number',
    nn int                                  comment 'pre-comptued night number',
    fpa_id varchar(20)                      comment 'IPP EXP_NAME (camera ID)',
    exp_id varchar(20)                      comment 'IPP EXP_ID (full exposure ID)',
    diff_id varchar(20)                     comment 'IPP DIFF_ID (image diff ID)',
    pa_deg real                             comment 'Boresight position angle, 0=North',
    fov_deg real                            comment 'Field-of-view (diameter [circular] or side [rect], degrees',
    xyidx_size int                          comment 'num of bins in each direction of X/Y index',
    se_deg real                             comment 'Solar elongation of field center, degrees', 
    eb_deg real                             comment 'Ecliptic latitude (eb) of field center, degrees',
    gb_deg real                             comment 'Galactic latitude (gb) of field center, degrees',

    status char(1) default 'N'              comment 'processing status of field',
    parent_id bigint                        comment 'ID of master (final) parent in tuple; NULL to indicate master',

    PRIMARY KEY (field_id),
    FOREIGN KEY (filter_id) REFERENCES filter_info(filter_id) ON DELETE CASCADE,
    index(epoch_mjd),
    index(nn),
    index(fpa_id)
) type=InnoDB;
