- 

$DBversion = 'XXX';

if( CheckVersion( $DBversion ) ) {

    if( !TableExists( 'holds' ) ) {

        $dbh->do( q|

            CREATE TABLE `holds` (

                -- information related to holds in Koha

                `id` INT(11) NOT NULL AUTO_INCREMENT,

                -- primary key

                `patron_id` INT(11) DEFAULT NULL,

                -- foreign key from the borrowers table defining which patron this hold is for

                `pickup_library_id` VARCHAR(10) DEFAULT NULL,

                -- foreign key from the branches table defining which branch the patron wishes to pick this hold up at

                `placing_date` DATE DEFAULT NULL,

                -- the date the hold was placed

                `biblio_id` INT(11) DEFAULT NULL,

                -- foreign key from the biblio table defining which bib record this hold is on

                `item_id` INT(11) DEFAULT NULL,

                -- foreign key from the items table defining the specific item the patron has placed on hold or the item this hold was filled with

                `item_type` VARCHAR(10) DEFAULT NULL,

                -- If record level hold, the optional itemtype of the item the patron is requesting

                `item_level` TINYINT(1) NOT NULL DEFAULT 0,

                -- Is the hold was placed at item level

                `completion_date` DATE DEFAULT NULL,

                -- the date this hold was completed (fulfilled or cancelled)

                `notes` LONGTEXT,

                -- notes related to this hold

                `priority` SMALLINT(6) NOT NULL DEFAULT 1,

                -- where in the queue the patron sits

                `status` ENUM('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled') DEFAULT 'placed' NOT NULL,

                -- the status the hold is ('placed', 'fulfilled', 'waiting', 'in_transit', 'cancelled')

                `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                -- the date and time this hold was last updated

                `waiting_date` DATE DEFAULT NULL,

                -- the date the item was marked as waiting for the patron at the library

                `expiration_date` DATE DEFAULT NULL,

                -- the date the hold expires (usually the date entered by the patron to say they don't need the hold after a certain date)

                `lowest_priority` TINYINT(1) NOT NULL DEFAULT 0,

                -- If it was assigned the lowest priority

                `suspended` TINYINT(1) NOT NULL DEFAULT 0,

                -- If it is suspended

                `suspended_until_date` DATETIME NULL DEFAULT NULL,

                -- Date until it is suspended

                `completed` TINYINT(1) NOT NULL DEFAULT 0,

                -- If it has been completed (i.e. either 'fulfilled' or 'cancelled')

                PRIMARY KEY (`id`),

                KEY `priority_status_idx` (`priority`, `status`),

                KEY `patron_id` (`patron_id`),

                KEY `biblio_id` (`biblio_id`),

                KEY `item_id` (`item_id`),

                KEY `pickup_library_id` (`pickup_library_id`),

                KEY `item_type` (`item_type`),

                CONSTRAINT `holds_ibfk_1` FOREIGN KEY (`patron_id`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE SET NULL ON UPDATE CASCADE,

                CONSTRAINT `holds_ibfk_2` FOREIGN KEY (`biblio_id`) REFERENCES `biblio` (`biblionumber`) ON DELETE SET NULL ON UPDATE CASCADE,

                CONSTRAINT `holds_ibfk_3` FOREIGN KEY (`item_id`)   REFERENCES `items` (`itemnumber`) ON DELETE SET NULL ON UPDATE CASCADE,

                CONSTRAINT `holds_ibfk_4` FOREIGN KEY (`pickup_library_id`) REFERENCES `branches` (`branchcode`) ON DELETE SET NULL ON UPDATE CASCADE,

                CONSTRAINT `holds_ibfk_5` FOREIGN KEY (`item_type`) REFERENCES `itemtypes` (`itemtype`) ON DELETE SET NULL ON UPDATE CASCADE

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

        |);

        $dbh->do(q|

            INSERT INTO holds (

                id,

                patron_id,

                placing_date,

                biblio_id,

                pickup_library_id,

                completion_date,

                notes,

                priority,

                status,

                timestamp,

                item_id,

                waiting_date,

                expiration_date,

                lowest_priority,

                suspended,

                suspended_until_date,

                item_type,

                item_level,

                completed

            )

            SELECT  *

            FROM (

                SELECT reserve_id,

                    borrowernumber,

                    reservedate,

                    biblionumber,

                    branchcode,

                    NULL,

                    reservenotes,

                    priority,

                    CASE

                        WHEN found = 'W' THEN

                            'waiting'

                        WHEN found = 'T' THEN

                            'in_transit'

                        ELSE

                            'placed'

                    END,

                    timestamp,

                    itemnumber,

                    waitingdate,

                    expirationdate,

                    lowestPriority,

                    suspend,

                    suspend_until,

                    itemtype,

                    item_level_hold,

                    0

                FROM reserves

                UNION

                SELECT o.reserve_id,

                    o.borrowernumber,

                    o.reservedate,

                    o.biblionumber,

                    o.branchcode,

                    o.timestamp,

                    o.reservenotes,

                    o.priority,

                    CASE

                        WHEN o.found = 'F' THEN

                            'fulfilled'

                        ELSE

                            'cancelled'

                    END,

                    o.timestamp,

                    o.itemnumber,

                    o.waitingdate,

                    o.expirationdate,

                    o.lowestPriority,

                    o.suspend,

                    o.suspend_until,

                    o.itemtype,

                    o.item_level_hold,

                    1 AS completed

                FROM old_reserves o

                LEFT JOIN

                    reserves r

                ON  o.reserve_id = r.reserve_id

                WHERE r.reserve_id is null

            ) reserves

            ORDER BY reservedate

        |);

        $dbh->do(q|

            ALTER TABLE club_holds_to_patron_holds

            DROP FOREIGN KEY clubs_holds_paton_holds_ibfk_3

        |);

        $dbh->do(q|

            ALTER TABLE club_holds_to_patron_holds

            ADD CONSTRAINT clubs_holds_paton_holds_ibfk_3 FOREIGN KEY (hold_id) REFERENCES holds (id) ON DELETE CASCADE ON UPDATE CASCADE

        |);

    }

    if(TableExists( 'holds' ) && TableExists('reserves')) {

        $dbh->do(q|

            DELETE FROM res USING `reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id

        |);

        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM reserves");

        $sth->execute();

        my $row = $sth->fetchrow_hashref;

        if ($row->{count}) {

            print "Warning to database administrator:\n"

                . "There were $row->{count} reserves that could not be moved, please check '_reserves'.\n";

            $dbh->do("RENAME TABLE reserves TO _reserves");

        } else {

            $dbh->do("DROP TABLE reserves");

        }

        $dbh->do(q|

            CREATE VIEW `reserves`

            AS

                SELECT

                    `id` AS `reserve_id`,

                    `borrowernumber`,

                    `placing_date` AS `reservedate`,

                    `biblionumber`,

                    `branchcode`,

                    `completion_date` AS `cancellationdate`,

                    `notes` AS `reservenotes`,

                    `priority`,

                    CASE

                        when `status` = 'waiting' then

                            'W'

                        when `status` = 'in_transit' then

                            'T'

                        else

                            null

                    END AS `found`,

                    `timestamp`,

                    `itemnumber`,

                    `waiting_date` AS `waitingdate`,

                    `expiration_date` AS `expirationdate`,

                    `lowest_priority` AS `lowestPriority`,

                    `suspended` AS `suspend`,

                    `suspended_until_date` AS `suspend_until`,

                    `itemtype`,

                    `item_level` AS `item_level_hold`

                FROM `holds`

                WHERE completed=0

        |);

    }

    if(TableExists( 'holds' ) && TableExists('old_reserves')) {

        $dbh->do(q|

            DELETE FROM res USING `old_reserves` as res INNER JOIN `holds` as h ON h.id = res.reserve_id

        |);

        $sth = $dbh->prepare("SELECT COUNT(*) AS count FROM old_reserves");

        $sth->execute();

        my $row = $sth->fetchrow_hashref;

        if ($row->{count}) {

            print "Warning to database administrator:\n"

                . "There were $row->{count} old reserves that could not be moved, please check '_old_reserves'.\n";

            $dbh->do("RENAME TABLE old_reserves TO _old_reserves");

        } else {

            $dbh->do("DROP TABLE old_reserves");

        }

        $dbh->do(q|

            CREATE VIEW `old_reserves`

            AS

                SELECT

                    `id` AS `reserve_id`,

                    `borrowernumber`,

                    `placing_date` AS `reservedate`,

                    `biblionumber`,

                    `branchcode`,

                    CASE

                        WHEN `status` = 'cancelled' THEN

                            `completion_date`

                        ELSE

                            NULL

                    END AS `cancellationdate`,

                    `notes` AS `reservenotes`,

                    `priority`,

                    CASE

                        WHEN `status` = 'fulfilled' THEN

                            'F'

                        ELSE

                            NULL

                    END AS `found`,

                    `timestamp`,

                    `itemnumber`,

                    `waiting_date` AS `waitingdate`,

                    `expiration_date` AS `expirationdate`,

                    `lowest_priority` AS `lowestPriority`,

                    `suspended` AS `suspend`,

                    `suspended_until_date` AS `suspend_until`,

                    `itemtype`,

                    `item_level` AS `item_level_hold`

                FROM `holds`

                WHERE completed=1

        |);

    }

    # Always end with this (adjust the bug info)

    NewVersion( $DBversion, 77777, "Add holds table and remove reserves and old_reserves tables");

}