/*
 * Copyright (c) 2007 National Center for Toxicological Research, U.S. Food and Drug Administration
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 *     * Redistributions of source code must retain the above copyright notice,
 *       this list of conditions and the following disclaimer.
 *
 *     * Redistributions in binary form must reproduce the above copyright
 *       notice, this list of conditions and the following disclaimer in the
 *       documentation and/or other materials provided with the distribution.
 *
 *     * Neither the name of the National Center for Toxicological Research nor the names of its contributors
 *       may be used to endorse or promote products derived from this software
 *       without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE
 * LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
 * CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
 * SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
 * CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
 * POSSIBILITY OF SUCH DAMAGE.
 */
/*
 * MovieRentalsQueries.cal
 * Created: Aug. 2007
 * By: Stephen Harris
 */
/**
 * Provides examples of building and using {@link typeConstructor = MappedQuery@}'s.
 *
 * @author Stephen Harris, reachable on gmail.com as steveOfAR
 */
module NCTR.Cal.Examples.MovieRentals.MovieRentalsQueries;
import Cal.Core.Prelude using
    typeConstructor = Boolean, Maybe, String;
    dataConstructor = False, True, Nothing, Just;
    function = fromJust, isJust;
    ;
import Cal.Data.DataGems;
import Cal.Data.SqlBuilder using
    function = derbySqlBuilder;
    ;
import Cal.Data.Sql;
import Cal.Data.DatabaseMetadata using
    function = makeTableReference;
    ;
import Cal.Collections.List using
    function = head, map;
    ;
import Cal.Core.Dynamic using
    typeConstructor = Dynamic;
    ;
import NCTR.Cal.Data.MappedQueries using
    typeConstructor = MappedQuery, RightNotJoinable;
    function =
        branchInner2, chainInner1, chainInner2, chainInner2Left1,
        emptyQueryContext, innerJoin, innerJoinProjectLeft, leftJoin,
        leftJoinVia, makeInnerJoin, makeInnerJoinProjectLeft, makeRightJoin,
        queryTextForMappedQuery, runMappedQuery, toQuery;
    ;
import NCTR.Cal.Data.RelatingEntities using
    function =
        innerJoinPathProjectingLastTable,
        innerJoinPathProjectingLastTableAsDynamic, pathsFromToInContext,
        shortestPathsFromToInContext, showPath;
    ;
import NCTR.Cal.Examples.MovieRentals.GeneratedForeignKeyLinks using
    function =
        customers, discs, dynamicMappedForeignKeyLinks, employees, movies,
        rentals, stores;
    ;
// Entity types
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Accounts using typeConstructor = Account;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Categories using typeConstructor = Category;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Customers using typeConstructor = Customer;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Discs using typeConstructor = Disc;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Employees using typeConstructor = Employee;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Movies using typeConstructor = Movie;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Rentals using typeConstructor = Rental;;
import NCTR.Cal.Examples.MovieRentals.GeneratedEntities.Stores using typeConstructor = Store;;
// Ex. 1:  A single-table mapped query.
/* Mapped queries have 3 type parameters - the first two are the left and right entity types that it exposes for
 * joining on either side, so that only compatible mapped foreign keys and queries may be joined.
 * The final type parameter is the type of the output values that it generates for each result row.
 */
rentalsMQ :: MappedQuery Rental Rental Rental;
rentalsMQ = Rentals.mappedQuery;
// Running the mapped query to obtain the results.
allRentals :: [Rental];
allRentals = run rentalsMQ Nothing; // Nothing here means no restrictions
/* Output: (module name qualifiers shortened)
 * [(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1),
 *  (Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2),
 *  ...]
 */
/* Ex. 2: A join of two tables.
 * The foreign key link rentals.disc_id is defined in GeneratedForeignKeyLinks which is generated along with the
 * entity types during reverse engineering.
 * Many-to-one links (>--) have the form <child table>.<foreign key> .
 * One-to-many links (--<) would have the form <parent-table>.<child-table>.<foreign key> .
 */
rentalsDiscsMQ :: MappedQuery Rental Disc (Maybe Rental, Disc);
rentalsDiscsMQ = makeRightJoin rentals.disc_id;
// The results.
rentalsDiscs :: [(Maybe Rental, Disc)];
rentalsDiscs = run rentalsDiscsMQ Nothing;
/* Output:
 * [[(Just (Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1)), (Discs.Disc AB-12345 1 (Just 2))],
 *  [(Just (Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2)), (Discs.Disc AB-67472 1 (Just 5))],
 *  ...]
 */
/*
 * Chains of foreign keys
 *    fk1 -- fk2 -- ... -- fkN
 */
// Ex. 3: A chain consisting of two inner joins followed by a left join.
rentalsCategories2MQ :: MappedQuery Rental Category (((Rental, Disc), Movie), Maybe Category);
rentalsCategories2MQ = makeInnerJoin  rentals.disc_id
                          `innerJoin` discs.movie_id
                          `leftJoin`  movies.category_id;
/* We could write the above more explicitly as:
 *   ((makeInnerJoin  rentals.disc_id)        // MappedQuery Rental Disc  (Rental,Disc)
 *        `innerJoin` discs.movie_id)         // MappedQuery Rental Movie ((Rental,Disc),Movie)
 *        `leftJoin`  movies.category_id;     // MappedQuery Rental Category (((Rental, Disc), Movie), Maybe Category)
 * The first line generates an initial mapped query from the rentals.disc_id mapped foreign key, with endpoint
 * types Rental and Disc, and result type (Rental,Disc).
 * The next line then inner joins this query with Movies to generate a MappedQuery Rental Movie ((Rental,Disc),Movie).
 * The third line then left outer joins this query with Categories to produce the final mapped query.
 */
// The results.
rentalsCategories2 :: [(((Rental, Disc), Movie), Maybe Category)];
rentalsCategories2 = run rentalsCategories2MQ Nothing;
/*[[[[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), (Discs.Disc AB-12345 1 (Just 2))], (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24]))], (Just (Categories.Category 2 Crime))],
 * [[[(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), (Discs.Disc AB-67472 1 (Just 5))], (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24]))], (Just (Categories.Category 2 Crime))],
 * ...]
 */
/* Ex. 4:
 * This is the same query again but this time we use a predefined function chainInner2Left1 to build the chain.
 * The predefined chain* functions are obviously less flexible than the incremental chain building functions
 * used above (which can be used to build arbitrary unlimited chains), but they do have a couple of practical
 * advantages:
 *    (i)  they generate flat tuples instead of nested right-pairings, and the flattened tuples can be more
 *         convenient in pattern matching;
 *    (ii) they generate simpler diagrams in the GemCutter.
 *
 * These functions have names of the form chainInner[N]Left[M] where N is the number of initial (foreign key)
 * arguments to join as inner joins, and M is the remaining number to join as left outer joins.  (Left joins
 * always follow inner joins in chains since if we had a left joining preceeding an inner join, it would just
 * eliminate the rows with unmatched sides in the preceeding left join, making it equivalent to inner join in
 * that place). When M or N is 0, the name degenerates to just chainInner[N] or chainLeft[M].
 */
rentalsCategoriesMQ :: MappedQuery Rental Category (Rental, Disc, Movie, Maybe Category);
rentalsCategoriesMQ = chainInner2Left1
                          rentals.disc_id
                          discs.movie_id
                          movies.category_id;
// The results.
rentalsCategories :: [(Rental, Disc, Movie, Maybe Category)];
rentalsCategories = run rentalsCategoriesMQ Nothing;
/* Output:
 * [[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), (Discs.Disc AB-12345 1 (Just 2)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Just (Categories.Category 2 Crime))],
 *  [(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), (Discs.Disc AB-67472 1 (Just 5)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Just (Categories.Category 2 Crime))],
 *  ...]
 */
// It is easy to extract a Sql.Query from a mapped query, using the toQuery function.  The emptyQueryContext
// argument to toQuery below just means this query is not part of a larger query, with which auto-generated
// table aliases would need to be coordinated.
rentalsCategoriesQ = toQuery rentalsCategoriesMQ emptyQueryContext;
// Here is the SQL text for the query.
rentalCategoriesQueryText = Sql.queryText sqlBuilder True rentalsCategoriesQ;
// or more directly,      = queryTextForMappedQuery rentalsCategoriesMQ sqlBuilder;
/* Output:
SELECT
    "rentals"."RENTAL_ID" AS "rentals/RENTAL_ID",
    "rentals"."DISC_ID" AS "rentals/DISC_ID",
    "rentals"."RENTAL_DATE" AS "rentals/RENTAL_DATE",
    "rentals"."RETURN_DATE" AS "rentals/RETURN_DATE",
    "rentals"."CUSTOMER_ID" AS "rentals/CUSTOMER_ID",
    "rentals"."EMPLOYEE_ID" AS "rentals/EMPLOYEE_ID",
    "rentals"."STORE_ID" AS "rentals/STORE_ID",
    "discs"."DISC_ID" AS "discs/DISC_ID",
    "discs"."MOVIE_ID" AS "discs/MOVIE_ID",
    "discs"."RENTAL_DURATION_DAYS" AS "discs/RENTAL_DURATION_DAYS",
    "movies"."MOVIE_ID" AS "movies/MOVIE_ID",
    "movies"."TITLE" AS "movies/TITLE",
    "movies"."CATEGORY_ID" AS "movies/CATEGORY_ID",
    "movies"."MOVIE_DURATION" AS "movies/MOVIE_DURATION",
    "movies"."RATING" AS "movies/RATING",
    "movies"."RELEASE_DATE" AS "movies/RELEASE_DATE",
    "categories"."CATEGORY_ID" AS "categories/CATEGORY_ID",
    "categories"."NAME" AS "categories/NAME"
FROM
    (("RENTALS" AS "rentals"
        INNER JOIN "DISCS" AS "discs"
            ON "rentals"."DISC_ID" = "discs"."DISC_ID")
        INNER JOIN "MOVIES" AS "movies"
            ON "discs"."MOVIE_ID" = "movies"."MOVIE_ID")
        LEFT OUTER JOIN "CATEGORIES" AS "categories"
            ON "movies"."CATEGORY_ID" = "categories"."CATEGORY_ID"
 */
// The result rows of the generated SQL as text, without mapping to entities (for exploration/debugging).
rentalCategoriesResultsText = resultsText rentalsCategoriesMQ;
/* Output:
 *  rentals/RENTAL_ID  rentals/DISC_ID  rentals/RENTAL_DATE  rentals/RETURN_DATE  rentals/CUSTOMER_ID  rentals/EMPLOYEE_ID  rentals/STORE_ID  discs/DISC_ID  discs/MOVIE_ID  discs/RENTAL_DURATION_DAYS  movies/MOVIE_ID  movies/TITLE   movies/CATEGORY_ID  movies/MOVIE_DURATION  movies/RATING  movies/RELEASE_DATE  categories/CATEGORY_ID  categories/NAME
 *  -----------------  ---------------  -------------------  -------------------  -------------------  -------------------  ----------------  -------------  --------------  --------------------------  ---------------  ------------   ------------------  ---------------------  -------------  -------------------  ----------------------  ---------------
 *  1                  AB-12345         2001-11-25           2001-11-30           1                    1                    1                 AB-12345       1               2                           1                The Godfather  2                   175 min                USA:R          1972-03-24           2                       Crime
 *  2                  AB-67472         2001-11-25           2001-11-30           3                    2                    2                 AB-67472       1               5                           1                The Godfather  2                   175 min                USA:R          1972-03-24           2                       Crime
 *  ...
 */
/* Ex. 5: Similar query but with only Rentals projected, by using the xJoinProjectLeft functions, which only
 * project their left side's results.  This is an easy way of seeing which records in one table (here Rentals) are related to
 * records in some other table (Categories), via a path of foreign keys between them.
 */
rentalsProjectedMQ ::  MappedQuery Rental Category Rental;
rentalsProjectedMQ = makeInnerJoinProjectLeft  rentals.disc_id
                        `innerJoinProjectLeft` discs.movie_id
                        `innerJoinProjectLeft` movies.category_id;
/* We'll add a restriction when running this mapped query.  When building restrictions, the table aliases are
 * just the lowercase table names; except if a table occurs multiple times in a query, then its occurrence number
 * is appended to the alias, starting with 1 for the leftmost occurrence.  Any table involved in the mapped query
 * may be used in the restriction, not just the exposed endpoints.
 */
categoryIsMystery :: Sql.TypedExpr Boolean;
categoryIsMystery = Sql.eqExpr (Sql.stringField (Sql.makeQueryTable "categories") "NAME")
                               (Sql.stringConstant "Mystery");
rentalsOfMysteries :: [Rental];
rentalsOfMysteries = run rentalsProjectedMQ (Just [categoryIsMystery]);
/* Output:
 * [(Rentals.Rental 3 OW-41221 [2001, 11, 25] [2001, 11, 30] 1 3 3),
 *  (Rentals.Rental 6 OW-41221 [2002, 1, 3] [2002, 1, 6] 4 6 2)]
 */
/* Ex. 6:  Branching
 *
 *              |-- fk1 -- mq1
 *              |-- fk2 -- mq2
 * (leftTable)--|  ...
 *              |
 *              |-- fkN -- mqN  (for N <= 5 currently)
 *
 * When joining a single table to mapped queries on several branches, each branch is preceeded by the foreign key
 * link joining the left table to the branch's left exposed table.  The common left table is implied by the foreign
 * keys which must all share the same left table (and which is guaraunteed by the type system so long as the
 * generated entity types are used).  The resulting mapped query is joinable on the left to the common table,
 * and doesn't expose a joinable table on the right.
 */
branchFromRentalsMQ :: MappedQuery Rental RightNotJoinable (Rental,(Disc,Movie,Category),(Customer,Account));
branchFromRentalsMQ =
    branchInner2
        rentals.disc_id      (chainInner2 discs.movie_id movies.category_id) // Rentals--Discs-Movies-Categories
        rentals.customer_id  (chainInner1 customers.account_id);             // Rentals--Customers-Accounts
// The results.
branchFromRentals :: [(Rental,(Disc,Movie,Category),(Customer,Account))];
branchFromRentals = run branchFromRentalsMQ (Just [Sql.notExpr categoryIsMystery]);
/* Output:
 * [[(Rentals.Rental 1 AB-12345 [2001, 11, 25] [2001, 11, 30] 1 1 1), [(Discs.Disc AB-12345 1 (Just 2)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Categories.Category 2 Crime)], [(Customers.Customer 1 Jones Henry (Just 555-1212) (Just [1970, 10, 10]) 3 (Just 0.00)), (Accounts.Account 3 1730 Abbey Pl # 1 Little Rock AR 72116 (Just -14.00))]],
 *  [(Rentals.Rental 2 AB-67472 [2001, 11, 25] [2001, 11, 30] 3 2 2), [(Discs.Disc AB-67472 1 (Just 5)), (Movies.Movie 1 The Godfather 2 (Just 175 min) (Just USA:R) (Just [1972, 3, 24])), (Categories.Category 2 Crime)], [(Customers.Customer 3 Panky Henry (Just 555-1221) (Just [1968, 1, 21]) 4 (Just 0.00)), (Accounts.Account 4 130 Providence Rd Little Rock AR 72211 (Just -3.45))]],
 *  ...]
 */
/* Ex. 7: General joins
 * The following demonstrates a general join of arbitrary mapped queries via a foreign key link between them, using
 * function leftJoinVia.
 * Here employees.rentals.employee_id is the foreign key link being used to join the two mapped queries.
 * It is a one-to-many link (ie. opposite in direction of the foreign key reference), which are written in the form
 *   <parent table>.<child table>.<foreign key to parent>.
 */
storeEmployeeRentalsBranchMQ :: MappedQuery Store RightNotJoinable ((Store,Employee), Maybe (Rental,(Disc,Movie,Category),(Customer,Account)));
storeEmployeeRentalsBranchMQ =
    chainInner1 stores.employees.store_id         // Left hand mapped query, ending with employees.
    `leftJoinVia` employees.rentals.employee_id $ // Linking foreign key, from employees to rentals.
    branchFromRentalsMQ;                          // Right hand mapped query, starting with rentals.
// Example row processing for the above mapped query.
// Flag suspicious employee rentals.
isSuspicious :: ((Store,Employee), Maybe (Rental,(Disc,Movie,Category),(Customer,Account))) ->  Boolean;
isSuspicious !resRow =
    if isJust resRow.#2 then
        let
            (store,employee) = resRow.#1;
            (rental, discMovieCat, custAcc) = fromJust resRow.#2;
            (disc,movie,category) = discMovieCat;
            (customer,account) = custAcc;
        in
            if Customers.phone customer == Employees.phone employee ||
               Categories.name category == "Romance" ||
               Movies.title movie == "Steel Magnolias" then
                True
            else
                False
    else // employees without rentals are also suspicious
        True;
// Find suspicious employees by their suspicious rentals.
suspiciousEmployees :: [Employee];
suspiciousEmployees = List.map (\res -> res.#1.#2) $ // get the Employee
                                List.filter isSuspicious $
                                            run storeEmployeeRentalsBranchMQ Nothing;
/* Output:
 * [(Employees.Employee 5 Smykowski Tom (Just 431331313) (Just 555-1221) (Just [1953, 8, 28]) 1 1),
 *  (Employees.Employee 2 Bolton Michael (Just 431351111) (Just 555-1221) (Just [1967, 12, 21]) 2 2),
 *  ...]
 */
/* Now we use the RelatingEntities modules to relate entities across the schema, without having to know their exact
 * relationship.  This use of the RelatingEntities module (as well as its source code) illustrates a more
 * dynamically-typed approach, using many of the same types as before but with a uniform Dynamic type parameter.
 */
/* First we find all paths(*) of foreign key links connecting the Categories table with the Stores table.
 * (*)-The default paths filter ignores paths that pass through the same table twice;  For more control,
 *     the filteredPathsFromToInContext function can be used.
 */
// Show all paths of foreign key links between tables Categories and Stores.
allPaths_Str = map showPath $
                   pathsFromToInContext
                       (makeTableReference "CATEGORIES")
                       (makeTableReference "STORES")
                       dynamicMappedForeignKeyLinks;
/* Output:
[CATEGORIES --["CATEGORY_ID"]--< MOVIES --["MOVIE_ID"]--< DISCS --["DISC_ID"]--< RENTALS >--["EMPLOYEE_ID"]-- EMPLOYEES >--["STORE_ID"]-- STORES,
 CATEGORIES --["CATEGORY_ID"]--< MOVIES --["MOVIE_ID"]--< DISCS --["DISC_ID"]--< RENTALS >--["STORE_ID"]-- STORES]
 We could interpret these paths as:
 1) Categories have member Movies, which are instantiated in Discs, which have Rentals, which are
    handled by Employees, which are assigned to Stores.
 2) Categories have member Movies, which are instantiated in Discs, which have Rentals, which occur in Stores.
 */
// The shortestPathsFromToInContext will select only the shortest paths for us, in this case the latter path.
shortestCategoriesStoresPath =
    head $ shortestPathsFromToInContext
               (makeTableReference "CATEGORIES")
               (makeTableReference "STORES")
               dynamicMappedForeignKeyLinks;
// Now we "push" a restriction on Categories through the path to see what comes out on the Stores side.
// This answers the question, "which Stores are related to the Mystery category?", ie.
// "Which stores have rented mysteries?".  We can get results either as Dynamics or inferenced to some specific type.
// The version that yields a specific entity result type requires a type declaration or some context to determine
// the result type via type inference.
storesWithMysteries :: [Store]; // This type decl. is necessary here to determine the result type.
storesWithMysteries = run (innerJoinPathProjectingLastTable shortestCategoriesStoresPath)
                          (Just [categoryIsMystery]);
/* Output:
 * [(Stores.Store 3 7403 Cantrell Road Little Rock AR 72207 (Just 11:00 AM) (Just 11:00 PM)),
 *  (Stores.Store 2 8521 Geyer Springs Road Little Rock AR 72209 (Just 11:00 AM) (Just 09:00 PM))]
 */
// Dynamic results.
storesWithMysteriesDynamicResults :: [Dynamic]; // Dynamic representation of the Stores.
storesWithMysteriesDynamicResults = run (innerJoinPathProjectingLastTableAsDynamic shortestCategoriesStoresPath)
                                        (Just [categoryIsMystery]);
// [No output for this one since Dynamics are not outputable]
// Connection information and convenience functions
connection :: DataGems.JDBCConnection;
public connection =
    DataGems.jdbcConnectionWithDriverCheck
        "org.apache.derby.jdbc.EmbeddedDriver"
        ("jdbc:derby:src/CAL/NCTR/Cal/Examples/MovieRentals/database/moviesdb")
        ""  //username
        ""; //password
sqlBuilder = derbySqlBuilder;
// Utility functions
// Convenience function for testing the produced queries (without mapping to entities)
resultsText :: MappedQuery a b t -> String;
resultsText !mq =
    let
        resultSet !mq = DataGems.jdbcQueryToResultSet
                            connection
                            (queryTextForMappedQuery mq sqlBuilder);
    in
        DataGems.resultSetText 0 $ resultSet mq;
run :: MappedQuery a b t -> Maybe [Sql.TypedExpr Boolean] -> [t];
run !mq !restrictions =
    runMappedQuery mq restrictions sqlBuilder connection;