unit TableManager;
  interface
    uses
      AdsData          (* tAdsStringField *),
      AdsFunc          (* used by AdsTable *),
      AdsTable         (* tAdsQuery *),
      AdvGrid          (* TMS *),
      AdvObj           (* TMS *),
      AdvUtil          (* TMS *),
      BaseGrid         (* TMS *),
      Classes          (**),
      ComCtrls         (* tDateTimePicker *),
      Controls         (* crHourglass, tCursor, tDate *),
      DB               (* tDataSet, tDataSource, tDateField, tDateTimeField,
                            tIntegerField; also used by AdsTable *),
      DBAdvGrid        (* tDBAdvGrid *),
      ExtCtrls         (* tShape *),
      Forms            (* tForm, Screen *),
      Graphics         (* TMS *),
      Grids            (* TMS *),
      LabeledSpinEdits (* tLabeledSpinInteger *),
      ObservedAndWM    (* InitializeObservedAndWM, tMeasurements, tTableUsed,
                            tuCO2, tuLight, tuOnOff, tuPond, tuPower, tuRain,
                            tuSPS30, tuThermostats, tuTRHP, tuWind, tuVC0706,
                            wmsetOnOff, wmsetPower, wmsetSPS30, wmsetThermostat,
                            wmsetTRHP, wmsetWind *),
      RRFSpinBase      (* used by LabeledSpinEdits *),
      StdCtrls         (* tButton, tGroupBox, tLabel; also used by DBCtrls *),
      Windows          (**);

    type
      tTableDescription = class(tObject)
         TheBoringChar: char;
         TheEarliestNew: tDate;
         TheTableName: string;
         UsesObserved: boolean;
         Table_oldExists: boolean;
         constructor Create(const TableName: string;
                            UseObserved: boolean = true;
                            BoringChar: char = 'x';
                            Table_old: boolean = true);
         end (* tTableDescription *);

    type
      tfmTableManager = class(tForm)
          qArchive:      tAdsQuery;
          qCensus:       tAdsQuery;
          qDataTables:   tAdsQuery;
            qDataTablesName: tAdsStringField;
            qDataTablesEarliestNew: tDateField;
            qDataTablesDateLastTrim: tDateTimeField;
            qDataTablesNRows:          tIntegerField;
            qDataTablesNRowsAfterTrim: tIntegerField;
            qDataTablesXRowsPerDay:    tIntegerField;
            qDataTablesUID:            tIntegerField;
            qDataTablesXDaysToTrim:    tIntegerField;
            dataDataTables: tDataSource;
              dbadvgridDataTables: tDBAdvGrid;
          qOldToOldest:     tAdsQuery;
          qUpdateDataTable: tAdsQuery;
          btnRefresh: tButton;
          grpDeleteBadData: tGroupBox;
            qDeleteDateRange: tAdsQuery;
            dtpDeleteEnd:   tDateTimePicker;
            dtpDeleteStart: tDateTimePicker;
            lbFrom:    tLabel;
            lbThrough: tLabel;
            shapeDelete: tShape;
              btnDelete: tButton;
          grpOldToOldest: tGroupBox;
            btnOldToOldest: tButton;
            lbspintOldOldest: tLabeledSpinInteger;
            shapeOldOldest: tShape;
          grpTrimTable: tGroupBox;
            btnTrim: tButton;
            lbspintTrimLimit: tLabeledSpinInteger;

          procedure qDataTablesAfterOpen (DataSet: tDataSet);
          procedure qDataTablesCalcFields(DataSet: tDataSet);

          procedure btnDeleteClick     (Sender: tObject);
          procedure btnOldToOldestClick(Sender: TObject);
          procedure btnRefreshClick    (Sender: tObject);
          procedure btnTrimClick       (Sender: tObject);
          procedure FormDblClick       (Sender: tObject);
        private
          function RowCount(const TU: tTableUsed): integer;
        public
          procedure DoCensus;
          function  EarliestNewData(const TU: tTableUsed): tDate;
          procedure Initialize;
          function  TableIndex(const TableName: string): tTableUsed;
        end (* tfmTableManager *);

    var
      fmTableManager: tfmTableManager;
      TableInfo: array[tTableUsed] of tTableDescription;

  implementation
    uses
      Dashboard      (* fmDashboard *),
      DateUtils      (* DaysBetween, IncDay, MinutesBetween, Today, Tomorrow,
                          YearOf *),
      DBAdvGridUtils (* SetUpDBAdvGridReadOnly *),
      Dialogs        (* ShowMessageFmt *),
      HC3DM          (* dmHC3, Global *),
      HC4Main        (* fmHC4Main *),
      HC3Utilities   (* INILoadInteger *),
      MyConsts       (* BLANK, UsualFormColor *),
      SQLUtils       (* qRequestLiveAfterOpen *),
      SysUtils       (* Format, FormatDateTime, Now, QuotedStr, StringReplace *);
      {$R *.dfm}

    procedure tfmTableManager.btnDeleteClick(Sender: tObject);
      (* delete records from the selected table.  This DOES NOT clean up the
      summary table *)

      var
        RangeEnd, RangeStart, TableName: string;

      begin (* tfmTableManager.btnDeleteClick *)
        TableName := qDataTables.FieldByName('Name').AsString;
        RangeStart := Format('convert(WhenRecorded, SQL_Date) >= ''%s''',
                             [FormatDateTime('yyyy-mm-dd', dtpDeleteStart.Date)]);
        RangeEnd   := Format('convert(WhenRecorded, SQL_Date) <= ''%s''',
                             [FormatDateTime('yyyy-mm-dd', dtpDeleteEnd.Date)]);
        qDeleteDateRange.SQL.Text := Format('delete from %s where %s and %s',
                                            [TableName, RangeStart, RangeEnd]);
        qDeleteDateRange.ExecSQL
      end   (* tfmTableManager.btnDeleteClick *);

    procedure tfmTableManager.btnOldToOldestClick(Sender: tObject);
      (* use year from spinedit *)

      var
        NewOldThrough: integer;

      procedure OldToOldest(const TU: tTableUsed);
        (* process one table *)

        const
          SQLScheme = 'insert into                           ' +
                      '  <name>_oldest                       ' +
                      'select                                ' +
                      '  *                                   ' +
                      'from                                  ' +
                      '  <name>_old                          ' +
                      'where                                 ' +
                      '  year(WhenRecorded) <= :OldThrough   ' +
                      ';                                     ' +
                      'delete from                           ' +
                      '  <name>_old                          ' +
                      'where                                 ' +
                      '  year(WhenRecorded) <= :OldThrough   ';

        var
          OldCursor: tCursor;

        begin (* OldToOldest *)
          with TableInfo[TU] do
            if Table_oldExists then
              if YearOf(TheEarliestNew) > NewOldThrough then
                begin (* nondegenerate *)
                  OldCursor := Screen.Cursor;
                  Screen.Cursor := crHourglass;
                  with qOldToOldest do
                    try     (* restore cursor *)
                      SQL.Text := StringReplace(SQLScheme,
                                                '<name>',
                                                TheTableName,
                                                [rfReplaceAll]);

                      ParamByName('OldThrough').AsInteger := NewOldThrough;
                      ShowMessageFmt('about to run "%s" with OldThrough = %d',
                                     [TrimInternalWhiteSpace(SQL.Text), NewOldThrough]);
                      ExecSQL;
                      fmDashboard.ExtendChat(Format('tm_172 %s: data before %d to _oldest.  Repack it soon',
                                                    [TheTableName, NewOldThrough + 1]));
                      fmDashboard.Show
                    finally (* restore cursor *)
                      Screen.Cursor := OldCursor
                    end     (* restore cursor *)
                end   (* nondegenerate *)
        end   (* OldToOldest *);

      begin (* tfmTableManager.btnOldToOldestClick *)
        NewOldThrough := lbspintOldOldest.Value;
        with qDataTables do
          begin (* qDataTables *)
            First;
            while not EOF do
              begin (* one record *)
                OldToOldest(tTableUsed(FieldByName('UID').AsInteger));
                Next
              end   (* one record *)
          end   (* qDataTables *);

        Global.OldestThrough := lbspintOldOldest.Value;
        Global.SaveOldestThrough()
      end   (* tfmTableManager.btnOldToOldestClick *);

    procedure tfmTableManager.btnRefreshClick(Sender: tObject);
      (* new census.  *)

      begin (* tfmTableManager.btnRefreshClick *)
        DoCensus
      end   (* tfmTableManager.btnRefreshClick *);

    procedure tfmTableManager.btnTrimClick(Sender: tObject);
      (* trim big tables *)

      var
        NRowsAfterTrim, OldNRows: integer;
    
      function TrimTable(const TU: tTableUsed): string;
        (*   Move about 5/6 of the records of the table to the old table.
           The result is line for log, or for Dashboard.

             This code ASSUMES that the file and file_old have identical fields.

             The line
                     execute procedure sp_Packtable('Power')
           is syntactically correct SQL, but it works only when the engine has
           exclusive access to the table; this would be tricky from within
           the application.
         *)

        var
          OldCursor: tCursor;
          NewEarliestNew: tDate;
          MoveToOld, NewEarliestTimeStamp, OldTableName, Where: string;

        begin (* TrimTable *)
          Show;
          OldCursor := Screen.Cursor;
          Screen.Cursor := crHourglass;
          with TableInfo[TU] do
            try     (* restore cursor *)
              NewEarliestNew := IncDay(TheEarliestNew,
                                       5 * (DaysBetween(Now, TheEarliestNew) div 6));
              NewEarliestTimeStamp := Format('%sT00:00:00',
                                            [FormatDateTime('yyyy-mm-dd',
                                                            NewEarliestNew)]);
              Result := Format('archiving data of %s table from before %s',
                               [TheTableName, NewEarliestTimeStamp]);

              OldTableName := TheTableName + '_old';
              Where := 'WhenRecorded < TIMESTAMP' + QuotedStr(NewEarliestTimeStamp);

              if Table_oldExists then
                MoveToOld := 'insert into            ' +
                                OldTableName + BLANK   +
                             'select                 ' +
                             '  *                    ' +
                             'from                   ' +
                                TheTableName + BLANK   +
                             'where                  ' +
                                Where                  +
                             ' ; '
              else
                MoveToOld := nullstring;

              qArchive.SQL.Text := MoveToOld +
                                   'delete from            ' +
                                      TheTableName + BLANK   +
                                   'where                  ' +
                                      Where;
              qArchive.ExecSQL;
              NRowsAfterTrim := RowCount(TU);
              ShowMessageFmt('%s: EarliestNew %s to %s; NRows %d to %d',
                             [TheTableName,
                              FormatDateTime('yyyy-mm-dd', TheEarliestNew),
                              FormatDateTime('yyyy-mm-dd', NewEarliestNew),
                              OldNRows, NRowsAfterTrim]);
            finally (* restore cursor *)
              Screen.Cursor := OldCursor
            end     (* restore cursor *)
        end   (* TrimTable *);

      begin (* tfmTableManager.btnTrimClick *)
        with qDataTables do
          begin (* qDataTables *)
            First;
            while not EOF do
              begin (* one record *)
                OldNRows := FieldByName('NRows').AsInteger;
                if OldNRows > lbspintTrimLimit.Value then
                  begin (* trim this one *)
                    with FieldByName('UID') do
                      fmDashboard.ExtendChat('tm_285 ' + TrimTable(tTableUsed(AsInteger)));
                    Edit;
                    FieldByName('NRowsAfterTrim').AsInteger  := NRowsAfterTrim;
                    FieldByName('DateLastTrim'  ).AsDateTime := Now;
                    Post
                  end   (* trim this one *);

                Next
              end   (* one record *)
          end   (* qDataTables *);

        DoCensus
      end   (* tfmTableManager.btnTrimClick *);

    procedure tfmTableManager.DoCensus;
      (* look at all the files *)

      var
        NRows: integer;
        TU: tTableUsed;

      begin (* tfmTableManager.DoCensus *)
        for TU := low(tTableUsed) to high(tTableUsed) do
          if TU <> tuLightningNOTUSED then
            with TableInfo[TU] do
              begin (* fill the table *)
                NRows := RowCount(TU);

                qCensus.Close;
                qCensus.SQL.Text := 'select top 1         ' +
                            '  WhenRecorded       ' +
                            'from                 ' +
                               TheTableName + BLANK +
                            'order by             ' +
                            '  WhenRecorded       ';
                qCensus.Open;
                TheEarliestNew := qCensus.Fields[0].AsDateTime;
                qCensus.Close;

                with qUpdateDataTable do
                  begin (* qUpdateDataTable *)
                    ParamByName('EarliestNew').AsDate    := TheEarliestNew;
                    ParamByName('Name'       ).AsString  := TheTableName;
                    ParamByName('NRows'      ).AsInteger := NRows;
                    ParamByName('UID'        ).AsInteger := ord(TU);
                    ExecSQL
                  end   (* qUpdateDataTable *)
              end   (* fill the table *);
        qDataTables.Close;
        qDataTables.Open
      end   (* tfmTableManager.DoCensus *);

    function tfmTableManager.EarliestNewData(const TU: tTableUsed): tDate;
      (* service routine for GenericGraph *)

      begin (* tfmTableManager.EarliestNewData *)
        Result := TableInfo[TU].TheEarliestNew
      end   (* tfmTableManager.EarliestNewData *);

    procedure tfmTableManager.FormDblClick(Sender: tObject);
      (* show master form *)

      begin (* tfmTableManager.FormDblClick *)
        fmHC4Main.Show
      end   (* tfmTableManager.FormDblClick *);

    procedure tfmTableManager.Initialize;
      (* usual startup *)

      procedure LoadTableInfo;
        (* debulk *)

        procedure LTI(const WhichTable: tTableUsed;
                      const Name: string;
                      const ObservedQ: boolean;
                      BoringChar: char = 'x';
                      TableOld: boolean = true);
          (* shortcut *)

          begin (* LTI *)
            TableInfo[WhichTable] :=
              tTableDescription.Create(Name, ObservedQ, BoringChar, TableOld)
          end   (* LTI *);

        begin (* LoadTableInfo *)      //       has        boring      has
          //                 TableName     Observed field   char   _old table
          LTI(tuCO2,         'CO2',            true,        'c',      false     );
          LTI(tuLight,       'Light',          true,        'l'                 );
          // LTI(tuLightning,   'Lightning',     false                             );
          LTI(tuOnOff,       'OnOff',          true ,       'o'                 );
          LTI(tuPond,        'Pond',          false,        'p'                 );
          LTI(tuPower,       'Power',          true,        'e',      false     );
          LTI(tuRain,        'Rain',          false,        'r'                 );
          LTI(tuSPS30,       'SPS30',          true,        's'                 );
          LTI(tuThermostats, 'Thermostats',    true,        'r',      false     );
          LTI(tuTRHP,        'TRHP',           true,        't'                 );
          LTI(tuWind,        'Wind',          false,        'w'                 );
          LTI(tuVC0706,      'VC0706',         true,        'v',      false     )
        end   (* LoadTableInfo *);

      begin (* tfmTableManager.Initialize *)
        self.Color := UsualFormColor;
        dtpDeleteStart.Date := Tomorrow;
        dtpDeleteEnd.Date := Tomorrow;
        lbspintTrimLimit.Value := INILoadInteger('Table Manager', 'TableTrimThreshhold', 20000);
        lbspintOldOldest.Value := Global.OldestThrough;
        btnOldToOldest.Enabled := (Global.OldestThrough < YearOf(Today)-2);
        LoadTableInfo;
        InitializeObservedAndWM;
        SetUpDBAdvGridReadOnly(dbadvgridDataTables);
    { TODO : update TableObservedLink }
        DoCensus;
        with qDataTables do
          begin (* time to trim? *)
            First;
            if FieldByName('NRows').AsInteger >= lbspintTrimLimit.Value then
              Show 
          end   (* time to trim? *)
      end   (* tfmTableManager.Initialize *);

    procedure tfmTableManager.qDataTablesAfterOpen(DataSet: tDataSet);
      (* paranoia *)
    
      begin (* tfmTableManager.qDataTablesAfterOpen *)
        SQLUtils.qRequestLiveAfterOpen(tAdsQuery(DataSet))
      end   (* tfmTableManager.qDataTablesAfterOpen *);

    procedure tfmTableManager.qDataTablesCalcFields(DataSet: tDataSet);
      (* for XDaysToTrim *)

      var
        TimeLastTrim: tDateTime;
        MinutesSinceTrim, NRows, NRowsAfterTrim, RowsPerDay, TrimTrigger: integer;

      begin (* tfmTableManager.qDataTablesCalcFields *)
        TrimTrigger := lbspintTrimLimit.Value;

        with DataSet do
          begin (* DataSet *)
            TimeLastTrim      := FieldByName('DateLastTrim'  ).AsDateTime;
            NRows             := FieldByName('NRows'         ).AsInteger;
            NRowsAfterTrim    := FieldByName('NRowsAfterTrim').AsInteger;
            MinutesSinceTrim  := MinutesBetween(Now, TimeLastTrim);
            if MinutesSinceTrim < 1 then
              MinutesSinceTrim := 1;
            RowsPerDay        := (24 * 60 * (NRows - NRowsAfterTrim))
                                     div MinutesSinceTrim;
            // ShowMessageFmt('MinST = %d, NRAT = %d', [MinutesSinceTrim, NRowsAfterTrim]);

            with FieldByName('XDaysToTrim') do
              if RowsPerDay <= 0 then
                Clear
              else
                AsInteger := (TrimTrigger - NRows + (RowsPerDay div 2)) div RowsPerDay;

            FieldByName('XRowsPerDay').AsInteger := RowsPerDay
          end   (* DataSet *)
      end   (* tfmTableManager.qDataTablesCalcFields *);

    function tfmTableManager.RowCount(const TU: tTableUsed): integer;
      (* utility *)

      begin (* tfmTableManager.RowCount *)
        with qCensus do
          begin (* qCensus *)
            Close;
            SQL.Text := 'select count(*) from ' + TableInfo[TU].TheTableName;
            Open;
            Result := Fields[0].AsInteger
          end   (* qCensus *)
      end   (* tfmTableManager.RowCount *);

    function tfmTableManager.TableIndex(const TableName: string): tTableUsed;
      (* just a lookup; ASSUMED to succeed in finding match *)

      var
        TU: tTableUsed;

      begin (* tfmTableManager.TableIndex *)
        Result := low(tTableUsed);
        for TU := low(tTableUsed) to high(tTableUsed) do
          if TU <> tuLightningNOTUSED then
            if TableInfo[TU].TheTableName = TableName then
              begin (* success *)
                Result := TU;
                break
              end   (* success *)
      end   (* tfmTableManager.TableIndex *);

{ tTableDescription }

    constructor tTableDescription.Create(const TableName: string;
                                         UseObserved: boolean;
                                         BoringChar: char;
                                         Table_old: boolean);
      (* OTN is just OLD+NTN if nullstring *)

      begin (* tTableDescription.Create *)
        inherited Create;
        TheEarliestNew  := 0;
        TheTableName    := TableName;
        TheBoringChar   := BoringChar;
        UsesObserved    := UseObserved;
        Table_oldExists := Table_old
      end   (* tTableDescription.Create *);

end.
