use Modern::Perl;

return {

    bug_number => "31028",

    description => "Add a way to record users concerns about catalog records",

    up => sub {

        my ($args) = @_;

        my ($dbh, $out) = @$args{qw(dbh out)};

        unless ( TableExists('catalog_concerns') ) {

            $dbh->do(q{

                CREATE TABLE IF NOT EXISTS `catalog_concerns` (

                  `id` int(11) NOT NULL auto_increment COMMENT 'primary key',

                  `biblio_id` int(11) NOT NULL COMMENT 'id of biblio record the report concerns',

                  `reporter_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the patron who reported the concern',

                  `reported_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this concern was reported',

                  `message` text NOT NULL COMMENT 'concern message content',

                  `resolver_id` int(11) DEFAULT NULL COMMENT 'id of the user who resolved the concern',

                  `resolved_date` datetime DEFAULT NULL COMMENT 'date and time this concern was resolved',

                  `resolution_message` text DEFAULT NULL COMMENT 'resolution message content',

                  PRIMARY KEY(`id`),

                  CONSTRAINT `catalog_concerns_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,

                  CONSTRAINT `catalog_concerns_ibfk_2` FOREIGN KEY (`reporter_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,

                  CONSTRAINT `catalog_concerns_ibfk_3` FOREIGN KEY (`resolver_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE

                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

            });

            say $out "Added new table 'catalog_concerns'";

        }

    },

}

--

-- Table structure for table `catalog_concerns`

--

DROP TABLE IF EXISTS `catalog_concerns`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE IF NOT EXISTS `catalog_concerns` (

  `id` int(11) NOT NULL auto_increment COMMENT 'primary key',

  `biblio_id` int(11) NOT NULL COMMENT 'id of biblio record the report concerns',

  `reporter_id` int(11) NOT NULL DEFAULT 0 COMMENT 'id of the patron who reported the concern',

  `reported_date` timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'date and time this concern was reported',

  `message` text NOT NULL COMMENT 'concern message content',

  `resolver_id` int(11) DEFAULT NULL COMMENT 'id of the user who resolved the concern',

  `resolved_date` datetime DEFAULT NULL COMMENT 'date and time this concern was resolved',

  `resolution_message` text DEFAULT NULL COMMENT 'resolution message content',

  PRIMARY KEY(`id`),

  CONSTRAINT `catalog_concerns_ibfk_1` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `catalog_concerns_ibfk_2` FOREIGN KEY (`reporter_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `catalog_concerns_ibfk_3` FOREIGN KEY (`resolver_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*!40101 SET character_set_client = @saved_cs_client */;