<?php
/**
 * -------------------------------------------------------------------------
 *
 * The viewer module for displaying a channel list or channel page. It is
 * always called using run() method implemented below. The initiator of that
 * call is the main module of your application.
 *
 * We can say that it is a Controller. However, this module also acts as
 * a Model to read certain data from the database. So it contains the
 * corresponding methods: getItem(), getItems(), and getSitemap().
 *
 * -------------------------------------------------------------------------
 *
 * @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 ( 'NodeWithTable.php' );
    class MyMimimiChannels extends MimimiNodeModuleWithTable {
        protected $myNodeFile = __FILE__;

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

        public $table = 'channels';

        /**
         * -----------------------------------------------------------------
         *
         * Define a database table structure.
         *
         * -----------------------------------------------------------------
         *
         * Please note that the default value of "enabled" 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 "channel system identifier"',
                      '`url`      VARCHAR(255)   NOT NULL                                 COMMENT "page URL relative to the channel list URL"',
                      '`name`     VARCHAR(80)    NOT NULL                                 COMMENT "channel name"',
                      '`meta`     VARCHAR(512)   NOT NULL                                 COMMENT "text for meta description tag"',
                      '`avatar`   VARCHAR(255)   NOT NULL                                 COMMENT "avatar URL relative to the site URL"',
                      '`info`     VARCHAR(2048)  NOT NULL                                 COMMENT "channel description"',
                      '`enabled`  BOOLEAN        NOT NULL  DEFAULT FALSE                  COMMENT "1 if the channel is visible"',
                      '`viewed`   TIMESTAMP      NOT NULL  DEFAULT "2000-01-01 00:00:01"  COMMENT "date up to which the channel was viewed"',
                      '`created`  TIMESTAMP      NOT NULL  DEFAULT CURRENT_TIMESTAMP      COMMENT "creation time"'
                  ];

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

        protected $tableKeys = [
                      'PRIMARY KEY ( `id`      )',
                      'UNIQUE KEY  ( `url`     )',
                      'KEY         ( `name`    )',
                      'KEY         ( `enabled` )',
                      'KEY         ( `created` )'
                  ];

        /**
         * -----------------------------------------------------------------
         *
         * Displays the contents of channel list page.
         *
         * -----------------------------------------------------------------
         *
         * Please note that due to the specifics of the app's router, this
         * method of the visual module "Channels" is called for any URLs
         * starting with "channels":
         *
         *     https://your.site/channels
         *     https://your.site/channels/A-CHANNEL-URL-PATH
         *                                │                │
         *                                └───────┬────────┘
         *                                        │
         *                                        └─> it is the incoming $params
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   mixed  $params  (optional) The rest of page's URL if detected by the app's routing method.
         * @return  bool            TRUE  if the page was rendered successfully.
         *                          FALSE if requested channel is not found and an Error404 page needs to be displayed.
         *
         * -----------------------------------------------------------------
         */

        public function run ( $params = '' ) {
            switch ( $params ) {
                case '':
                     $file = 'channels-list.tpl';
                     $data = $this->getItems (         );
                     break;
                default:
                     $file = 'channels-page.tpl';
                     $data = $this->getItem  ( $params );
            }
            return $data
                && $this->app->renderTemplate ( $file, 'Oops, this site does not have a template for Channels page!',
                                                $data );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves a channel entry by its URL.
         *
         * -----------------------------------------------------------------
         *
         * Please note that "t1" below is an alias for the primary database
         * table "channels", and "t2" is an alias for the secondary table
         * "channels_messages".
         *
         * Note also that the filter used below will actually result in the
         * following MySQL query:
         *
         *     SELECT    t1.*,
         *               t2.*
         *     FROM      channels          AS t1
         *     LEFT JOIN channels_messages AS t2
         *                                    ON t2.channel_id = t1.id AND
         *                                       t2.visible    = TRUE
         *     WHERE     t1.url     = $url AND
         *               t1.enabled = TRUE
         *     ORDER BY  t2.created DESC
         *     LIMIT     1000000000
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   string      $url  The relative URL for the channel you are looking for.
         * @return  array|bool        ARRAY on success. It contains a list of rows obtained from a database table.
         *                            FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getItem ( $url ) {
            $filter = [
                'select'   => [ 't1.*' => TRUE ,
                                't2.*' => TRUE ],
                'join'     => [ $this->messages->table => [ 't2.channel_id' => 't1.id' ,
                                                            't2.visible'    => TRUE    ] ],
                /* where */     't1.url'     => $url,
                                't1.enabled' => TRUE,
                'orderby'  => [ 't2.created' => 'desc' ]
            ];
            return $this->select ( $filter, 0, 1000000000 );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves all channels entries for displaying.
         *
         * -----------------------------------------------------------------
         *
         * These entries are sorted by creation date of their messages. We
         * also add an abstract column to the result entries. This column
         * is called "viewer". And it always contains the string "channels/"
         * to print later as the first segment of the channel URL.
         *
         * Please note that "t1" below is an alias for the primary database
         * table "channels", and "t2" is an alias for the secondary table
         * "channels_messages".
         *
         * Note also that the filter used below will actually result in the
         * following MySQL query:
         *
         *     SELECT    t1.*,
         *               "channels/"  AS      viewer,
         *               t2.created   AS      message_date,
         *               COUNT(t2.id) AS      unreaded
         *     FROM      channels          AS t1
         *     LEFT JOIN channels_messages AS t2
         *                                    ON t2.channel_id = t1.id     AND
         *                                       t2.visible    = TRUE      AND
         *                                       t2.created    > t1.viewed
         *     WHERE     t1.enabled = TRUE
         *     GROUP BY  t1.id        ASC
         *     ORDER BY  message_date DESC,
         *               t1.created   DESC
         *     LIMIT     1000000000
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @return  array|bool  ARRAY on success. Each element is an array, like a row obtained from a database table.
         *                      FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getItems ( ) {
            $filter = [
                'select'     => [ 't1.*'             => TRUE           ,
                                  '"channels/"'      => 'viewer'       ,
                                  't2.created'       => 'message_date' ,
                                  'COUNT(`t2`.`id`)' => 'unreaded'     ],

                'join'       => [ $this->messages->table => [ 't2.channel_id' => 't1.id'     ,
                                                              't2.visible'    => TRUE        ,
                                                              '> t2.created'  => 't1.viewed' ] ],
                /* where */       't1.enabled' => TRUE,

                'groupby'    => [ 't1.id' => 'asc' ],

                'orderby'    => [ 'message_date' => 'desc' ,
                                  't1.created'   => 'desc' ]
            ];
            return $this->select ( $filter, 0, 1000000000 );
        }

        /**
         * -----------------------------------------------------------------
         *
         * Retrieves all channels entries for a sitemap.
         *
         * -----------------------------------------------------------------
         *
         * These entries are sorted by their creation date. Please note that
         * "t1" below is an alias for the primary database table "channels".
         *
         * Note also that the filter used below will actually result in the
         * following MySQL query:
         *
         *     SELECT   t1.url,
         *              "channels/"  AS viewer,
         *     FROM     channels AS t1
         *     WHERE    t1.enabled = TRUE
         *     ORDER BY t1.created DESC
         *     LIMIT    1000000000
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @return  array|bool  ARRAY on success. Each element is an array that contains the "url" and "viewer" columns only.
         *                      FALSE on failure. This means no entries were found.
         *
         * -----------------------------------------------------------------
         */

        public function getSitemap ( ) {
            $filter = [
                'select'  => [ 't1.url'      => TRUE     ,
                               '"channels/"' => 'viewer' ],

                /* where */    't1.enabled'  => TRUE,

                'orderby' => [ 't1.created'  => 'desc' ]
            ];
            return $this->select ( $filter, 0, 1000000000 );
        }

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

        protected $demoRows = [
            [
                'id'       => 1,
                'url'      => 'demo-channel-1',
                'name'     => 'How to install this app?',
                'meta'     => 'It is a short instruction that demonstrates how to install this package on a site.',
                'info'     => '<p>Maybe you want to install such an application on your website. Okay, let\'s put a few messages in this channel demonstrating the installation process.</p>',
                'enabled'  => TRUE,
                'created'  => '2024-01-02 17:06:55'
            ], [
                'id'       => 2,
                'url'      => 'demo-channel-2',
                'avatar'   => 'media/demo-posts/five.viewers/avatar-1.png',
                'name'     => 'Julius Caesar',
                'meta'     => 'A few famous quotes from William Shakespeare\'s play Julius Caesar.',
                'info'     => '<p>For demonstration purposes, let us list in this channel a few famous quotes from William Shakespeare\'s play Julius Caesar.</p>',
                'enabled'  => TRUE,
                'created'  => '2024-01-19 11:21:32'
            ], [
                'id'       => 3,
                'url'      => 'demo-channel-3',
                'avatar'   => 'media/demo-posts/five.viewers/avatar-2.png',
                'name'     => 'Hamlet',
                'meta'     => 'The most famous quotes from William Shakespeare\'s play Hamlet.',
                'info'     => '<p>This demo channel contains the most famous quotes from William Shakespeare\'s play Hamlet.</p>',
                'enabled'  => TRUE,
                'created'  => '2024-04-16 09:10:11'
            ], [
                'id'       => 4,
                'url'      => 'demo-channel-4',
                'name'     => 'The Merchant of Venice',
                'meta'     => 'Quotes from William Shakespeare\'s play The Merchant of Venice.',
                'info'     => '<p>These quotes listed at this demo channel are included the words of Keats, Milton, Mark Twain, Ralph Waldo Emerson and Oscar Wilde.</p>',
                'enabled'  => TRUE,
                'created'  => '2024-03-28 08:11:14'
            ]
        ];

        /**
         * -----------------------------------------------------------------
         *
         * Installs the demo table entries.
         *
         * -----------------------------------------------------------------
         *
         * The need to overwrite this method is due to the presence in your
         * web app of a node module with a primary and secondary table.
         *
         * -----------------------------------------------------------------
         *
         * @public
         * @param   mixed  $params  Some parameters if you need.
         * @return  bool            TRUE  if at least one new entry has been added.
         *                          FALSE if the table has not changed.
         *
         * -----------------------------------------------------------------
         */

        public function install ( $params = NULL ) {
            $this->messages->install (         );
            return parent::install   ( $params );
        }
    };
