<?php
/**
 * -------------------------------------------------------------------------
 *
 * Submodule for working with a secondary table of the "Channels" module.
 *
 * -------------------------------------------------------------------------
 *
 * @package    MimimiFramework
 * @subpackage Examples / Five Viewers site
 * @license    GPL-2.0
 *             https://opensource.org/license/gpl-2-0/
 * @copyright  2022 MiMiMi Community
 *             https://mimimi.software/
 *
 * -------------------------------------------------------------------------
 */

    mimimiInclude ( 'ModuleWithTable.php' );
    class MyMimimiChannelsMessages extends MimimiModuleWithTable {

        /**
         * -----------------------------------------------------------------
         *
         * Specify a name of the database table to store channel messages.
         *
         * -----------------------------------------------------------------
         *
         * @access public
         * @var    string
         *
         * -----------------------------------------------------------------
         */

        public $table = 'channels_messages';

        /**
         * -----------------------------------------------------------------
         *
         * Define a database table structure.
         *
         * -----------------------------------------------------------------
         *
         * Please note that the default value of "visible" column is FALSE
         * to avoid displaying entries added with an incomplete number of
         * columns. What is meant here is that if such an incomplete entry
         * was somehow added to the table, it was either a software error
         * or a deliberate act of an attacker.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $tableFields = [
                      '`id`             BIGINT(20)     NOT NULL  AUTO_INCREMENT             COMMENT "message system identifier"',
                      '`channel_id`     BIGINT(20)     NOT NULL                             COMMENT "channel identifier"',
                      '`title`          VARCHAR(80)    NOT NULL                             COMMENT "message title"',
                      '`image`          VARCHAR(255)   NOT NULL                             COMMENT "image URL relative to the site URL"',
                      '`text`           VARCHAR(2048)  NOT NULL                             COMMENT "message HTML body"',
                      '`credits`        VARCHAR(255)   NOT NULL                             COMMENT "credits URL if you need"',
                      '`credits_label`  VARCHAR(40)    NOT NULL                             COMMENT "label for the credits URL"',
                      '`visible`        BOOLEAN        NOT NULL  DEFAULT FALSE              COMMENT "1 if the message is visible"',
                      '`created`        TIMESTAMP      NOT NULL  DEFAULT CURRENT_TIMESTAMP  COMMENT "creation time"'
                  ];

        /**
         * -----------------------------------------------------------------
         *
         * Define a list of table keys to speed up the database operations
         * related to channel messages.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $tableKeys = [
                      'PRIMARY KEY ( `id`         )',
                      'KEY         ( `channel_id` )',
                      'KEY         ( `title`      )',
                      'KEY         ( `visible`    )',
                      'KEY         ( `created`    )'
                  ];

        /**
         * -----------------------------------------------------------------
         *
         * Counts the number of entries with visible status.
         *
         * -----------------------------------------------------------------
         *
         * Please note that "t1" below is an alias for the secondary database
         * table "channels_messages". And "t2" is an alias for the primary
         * table "channels", which is referenced through "$this->owner".
         *
         * Note also that the filter used below will actually result in the
         * following MySQL query:
         *
         *     SELECT    COUNT(*)          AS total
         *     FROM      channels_messages AS t1
         *     LEFT JOIN channels          AS t2
         *                                    ON t1.channel_id = t2.id
         *     WHERE     t1.visible    = TRUE AND
         *               t2.enabled    = TRUE AND
         *               t1.channel_id = $id
         *     LIMIT     1
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   int  $id  (optional) The channel identifier for counting.
         * @return  int
         *
         * -----------------------------------------------------------------
         */

        public function getCount ( $id = 0 ) {
            $filter = [
                'select' => [ 'COUNT( * )' => 'total' ],
                'join'   => [ $this->owner->table => [ 't1.channel_id' => 't2.id' ] ],
                /* where */   't1.visible' => TRUE,
                              't2.enabled' => TRUE
            ];
            if ( $id ) {
                $filter[ 't1.channel_id' ] = $id;
            }
            $row = $this->select ( $filter );
            return empty ( $row[ 'total' ] )
                         ? 0
                         : $row[ 'total' ];
        }

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves a random entry with visible status.
         *
         * -----------------------------------------------------------------
         *
         * Please note that "t1" below is an alias for the secondary database
         * table "channels_messages". And "t2" is an alias for the primary
         * table "channels", which is referenced through "$this->owner".
         *
         * Note also that the filter used below will actually result in the
         * following MySQL query:
         *
         *     SELECT    t2.name,
         *               t1.title,
         *               t1.image,
         *               t1.text,
         *               t1.created
         *     FROM      channels_messages AS t1
         *     LEFT JOIN channels          AS t2
         *                                    ON t1.channel_id = t2.id
         *     WHERE     t1.visible    = TRUE AND
         *               t2.enabled    = TRUE AND
         *               t1.channel_id = $id
         *     LIMIT     1
         *     OFFSET    $offset
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   int         $id  (optional) The channel identifier to search in.
         * @return  array|bool       ARRAY on success. It contains an entry obtained from a database table.
         *                           FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getRandom ( $id = 0 ) {
            $count = $this->getCount ( $id );
            if ( $count ) {
                $filter = [
                    'select' => [ 't2.name'    => TRUE ,
                                  't1.title'   => TRUE ,
                                  't1.image'   => TRUE ,
                                  't1.text'    => TRUE ,
                                  't1.created' => TRUE ],
                    'join'   => [ $this->owner->table => [ 't1.channel_id' => 't2.id' ] ],
                    /* where */   't1.visible' => TRUE,
                                  't2.enabled' => TRUE
                ];
                if ( $id ) {
                    $filter[ 't1.channel_id' ] = $id;
                }
                $offset = $count > 1
                                 ? mt_rand ( 0, $count - 1 )
                                 : 0;
                return $this->select ( $filter, $offset );
            }
            return FALSE;
        }

        /**
         * -----------------------------------------------------------------
         *
         * Specify demo rows that will be used as default message entries
         * if the database does not have a table named "channels_messages".
         * In this case, all demo rows will be automatically added to the
         * newly created secondary table.
         *
         * -----------------------------------------------------------------
         *
         * @access protected
         * @var    array
         *
         * -----------------------------------------------------------------
         */

        protected $demoRows = [
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:07:56', 'title' => 'First step',   'image' => '',                                            'text' => '<p>You need to download the MiMiMi installation package from the official website.</p>', 'credits' => 'https://mimimi.software/', 'credits_label' => 'Go to Website' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:08:57', 'title' => 'Second step',                                                            'text' => '<p>Then you need to extract this package to your computer.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:09:58', 'title' => 'Third step',                                                             'text' => '<p>Next, copy the extracted package files to your site\'s root folder.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:10:59', 'title' => 'Fourth step',  'image' => '',                                            'text' => '<p>Open your site\'s home page in a browser. You will see the initial page for installing the package.</p>', 'credits' => 'http://localhost/', 'credits_label' => 'Open Localhost' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:11:00', 'title' => '',             'image' => 'media/demo-posts/five.viewers/install-1.png', 'text' => '<p>That page looks like this.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:12:01', 'title' => 'Fifth step',   'image' => '',                                            'text' => '<p>Go to the next page of the installer. There is a drop box with application variants. You need to select <i>five.viewers</i> there as shown in the following screenshot.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:13:02', 'title' => '',             'image' => 'media/demo-posts/five.viewers/install-2.png', 'text' => '<p>You may also enter a different directory name in the left input box if you want to change the default directory <i>mimimi.app/</i> for your application.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:14:03', 'title' => 'Sixth step',   'image' => '',                                            'text' => '<p>Now go to the next page of the installer where you should select a default theme if the app you are installing has more than one theme.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:15:04', 'title' => '',             'image' => 'media/demo-posts/five.viewers/install-3.png', 'text' => '<p>You don\'t need to do anything here because this application has only theme named <i>default</i>. Just click <strong>Next &raquo;</strong> button.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:15:04', 'title' => 'Seventh step', 'image' => 'media/demo-posts/five.viewers/install-4.png', 'text' => '<p>This application requires three modules only. You must select them as shown in the screenshot. These modules are:</p><ul class="cellular"><li><b>Helper</b> to import several standard routines for site\'s template files. These routines are:<ul><li><em>sendHeaderHTML()</em></li><li><em>sendHeaderXML()</em></li><li><em>sendHeaderTEXT()</em></li><li><em>sendHeaderJSON()</em></li><li><em>sendStatus200()</em></li><li><em>sendStatus404()</em></li><li><em>sendHeaderExpires()</em></li><li><em>stopIfHead()</em></li><li><em>printDomainUrl()</em></li><li><em>printSiteUrl()</em></li><li><em>printThemeUrl()</em></li><li><em>printPageUrl()</em></li><li><em>printValue()</em></li></ul></li><li><b>Db</b> to work with your database.</li><li><b>Url</b> to parse the page address details.</li></ul>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:16:05', 'title' => 'Eighth step',  'image' => '',                                            'text' => '<p>Now you need to configure your database.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:17:06', 'title' => '',             'image' => 'media/demo-posts/five.viewers/install-5.png', 'text' => '<p>Please enter your actual parameters as shown in the screenshot.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:18:07', 'title' => 'Ninth step',   'image' => 'media/demo-posts/five.viewers/install-6.png', 'text' => '<p>The following page is optional. Leave all input boxes blank and just click <strong>Next &raquo;</strong> button to complete the installation.</p>' ],
            [ 'channel_id' => 1, 'visible' => TRUE, 'created' => '2024-01-02 17:19:08', 'title' => '',             'image' => '',                                            'text' => '<p>Good luck!</p>' ],

            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-02 11:23:43', 'title' => 'Quote, Act III, Scene II',              'text' => '<p>Friends, Romans, countrymen, lend me your ears; I come to bury Caesar, not to praise him</p>', 'credits' => 'https://www.william-shakespeare.info/quotes-quotations-play-julius-caesar.htm', 'credits_label' => 'Thanks to Linda Alchin' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-03 11:24:54', 'title' => 'Julius Caesar quote, Act I, Scene II',  'text' => '<p>But, for my own part, it was Greek to me</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-04 11:25:00', 'title' => 'Quote, Act II, Scene I',                'text' => '<p>A dish fit for the gods</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-05 11:26:11', 'title' => 'Julius Caesar quote, Act III, Scene I', 'text' => '<p>Cry "Havoc", and let slip the dogs of war</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-06 11:27:22', 'title' => 'Quote, Act III, Scene I',               'text' => '<p>Et tu, Brute!</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-07 11:28:33', 'title' => 'Quote, Act I, Scene II',                'text' => '<p>Men at some time are masters of their fates: The fault, dear Brutus, is not in our stars, but in ourselves, that we are underlings</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-02-08 11:29:44', 'title' => 'Quote, Act III, Scene II',              'text' => '<p>Not that I loved Caesar less, but that I loved Rome more</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-09 11:30:55', 'title' => 'Quote, Act I, Scene II',                'text' => '<p>Beware the ides of March</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-10 11:31:01', 'title' => 'Quote, Act V, Scene V',                 'text' => '<p>This was the noblest Roman of them all</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-11 11:32:12', 'title' => 'Quote, Act III, Scene II',              'text' => '<p>When that the poor have cried, Caesar hath wept: Ambition should be made of sterner stuff</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-12 11:33:23', 'title' => 'Julius Caesar quote, Act I, Scene II',  'text' => '<p>Yond Cassius has a lean and hungry look; He thinks too much: such men are dangerous</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-13 11:34:34', 'title' => 'Quote, Act III, Scene II',              'text' => '<p>For Brutus is an honourable man; So are they all, all honourable men</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-03-14 11:35:45', 'title' => 'Quote, Act III, Scene II',              'text' => '<p>As he was valiant, I honour him; but, as he was ambitious, I slew him</p>' ],
            [ 'channel_id' => 2, 'visible' => TRUE, 'created' => '2024-04-15 11:36:56', 'title' => 'Julius Caesar quote, Act II, Scene II', 'text' => '<p>Cowards die many times before their deaths; The valiant never taste of death but once. Of all the wonders that I yet have heard, it seems to me most strange that men should fear; Seeing that death, a necessary end, will come when it will come</p>' ],

            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-04-16 09:11:21', 'title' => 'Hamlet quote, Act III, Scene I',  'text' => '<p>To be, or not to be: that is the question</p>', 'credits' => 'https://www.william-shakespeare.info/quotes-quotations-play-hamlet.htm', 'credits_label' => 'Thanks to Linda Alchin' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-04-17 09:12:32', 'title' => 'Hamlet quote, Act I, Scene III',  'text' => '<p>Neither a borrower nor a lender be; For loan oft loses both itself and friend, and borrowing dulls the edge of husbandry</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-04-18 10:13:43', 'title' => 'Hamlet quote, Act I, Scene III',  'text' => '<p>This above all: to thine own self be true</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-04-19 10:14:54', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>Though this be madness, yet there is method in \'t</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-20 11:15:05', 'title' => 'Hamlet quote, Act I, Scene II',   'text' => '<p>That it should come to this!</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-21 11:16:16', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>There is nothing either good or bad, but thinking makes it so</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-22 12:17:27', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>What a piece of work is man! how noble in reason! how infinite in faculty! in form and moving how express and admirable! in action how like an angel! in apprehension how like a god! the beauty of the world, the paragon of animals!</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-23 12:18:38', 'title' => 'Hamlet quote, Act III, Scene II', 'text' => '<p>The lady doth protest too much, methinks</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-24 13:19:49', 'title' => 'Hamlet quote, Act I, Scene II',   'text' => '<p>In my mind\'s eye</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-25 13:20:50', 'title' => 'Hamlet quote, Act I, Scene II',   'text' => '<p>A little more than kin, and less than kind</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-26 14:21:01', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>The play \'s the thing wherein I\'ll catch the conscience of the king</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-27 14:22:12', 'title' => 'Hamlet quote, Act I, Scene III',  'text' => '<p>And it must follow, as the night the day, thou canst not then be false to any man</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-28 15:23:23', 'title' => 'Hamlet quote, Act II, Scene I',   'text' => '<p>This is the very ecstasy of love</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-29 15:24:34', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>Brevity is the soul of wit</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-30 16:25:45', 'title' => 'Hamlet quote, Act II, Scene II',  'text' => '<p>Doubt that the sun doth move, doubt truth to be a liar, but never doubt I love</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-05-31 16:26:56', 'title' => 'Hamlet quote, Act III, Scene I',  'text' => '<p>Rich gifts wax poor when givers prove unkind</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-06-01 17:27:07', 'title' => 'Hamlet quote, Act III, Scene II', 'text' => '<p>Do you think I am easier to be played on than a pipe?</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-06-02 17:28:18', 'title' => 'Hamlet quote, Act III, Scene II', 'text' => '<p>I will speak daggers to her, but use none</p>' ],
            [ 'channel_id' => 3, 'visible' => TRUE, 'created' => '2024-06-03 18:29:29', 'title' => 'Hamlet quote, Act IV, Scene V',   'text' => '<p>When sorrows come, they come not single spies, but in battalions</p>' ],

            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-03-31 09:23:02', 'title' => 'Quote, Act III, Scene I', 'text' => '<p>If you prick us, do we not bleed? if you tickle us, do we not laugh? if you poison us, do we not die? and if you wrong us, shall we not revenge?</p>', 'credits' => 'https://www.william-shakespeare.info/quotes-quotations-play-merchant-of-venice.htm', 'credits_label' => 'Thanks to Linda Alchin' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-03 10:35:50', 'title' => 'Quote, Act I, Scene III', 'text' => '<p>The devil can cite scripture for his purpose</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-06 11:47:48', 'title' => 'Quote, Act I, Scene III', 'text' => '<p>I like not fair terms and a villain\'s mind</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-09 12:59:36', 'title' => 'Quote, Act I, Scene I',   'text' => '<p>I hold the world but as the world, Gratiano, A stage, where every man must play a part; And mine a sad one</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-12 13:01:24', 'title' => 'Quote, Act I, Scene II',  'text' => '<p>Superfluity comes sooner by white hairs, but competency lives longer</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-15 14:13:12', 'title' => 'Quote, Act I, Scene II',  'text' => '<p>I dote on his very absence</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-18 15:25:00', 'title' => 'Quote, Act I, Scene III', 'text' => '<p>The devil can cite scripture for his purpose</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-21 16:37:58', 'title' => 'Quote, Act II, Scene I',  'text' => '<p>Mislike me not for my complexion, The shadow\'d livery of the burnish\'d sun</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-24 17:49:46', 'title' => 'Quote, Act II, Scene II', 'text' => '<p>It is a wise father that knows his own child</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-27 18:51:34', 'title' => 'Quote, Act II, Scene II', 'text' => '<p>In the twinkling of an eye</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-04-30 19:03:22', 'title' => 'Quote, Act II, Scene VI', 'text' => '<p>But love is blind, and lovers cannot see The pretty follies that themselves commit</p>' ],
            [ 'channel_id' => 4, 'visible' => TRUE, 'created' => '2024-05-03 20:15:10', 'title' => 'Quote, Act II',           'text' => '<p>All that glisters is not gold</p>' ]
        ];
    };
