(eval-when (:load-toplevel :compile-toplevel :execute)
  (require :postmodern)
  (require :postmodern-utils)
  (require :cl-fad)
  (require :split-sequence))

(defpackage :netflix
  (:use :common-lisp :postmodern :postmodern-utils)
  (:export #:setup-database
	   #:load-data
	   #:predict-ratings
	   #:movies #:id-of #:year-of #:title-of #:get-movie
	   #:ratings #:user-of #:movie-of #:rating-of #:date-of
	   #:get-ratings-of-user #:get-ratings-of-movie))

(in-package :netflix)

(defparameter *netflix-data-dir* "/Users/lucindo/Documents/Netflix/download")
(defparameter *movies-file* 
  (concatenate 'string *netflix-data-dir* "/movie_titles.txt"))
(defparameter *ratings-dir* 
  (concatenate 'string *netflix-data-dir* "/training_set/"))

(eval-when (:load-toplevel :compile-toplevel :execute)
  (set-connection-spec :username "netflix"
		       :password ""
		       :database "netflix"
		       :hostname "localhost"))

(deftable movies ()
  ((movie-id :type integer
	     :accessor id-of
	     :initarg :movie-id)
   (year :type string ;; may be "NULL"
	 :accessor year-of
	 :initarg :year)
   (title :type string
	  :accessor title-of
	  :initarg :title))
  (:indices movie-id)
  (:class-name movies))

(defun make-movie (movie-id year title)
  (with-pooled-connection
      (save-dao (make-instance 'movies
			       :movie-id movie-id
			       :year year
			       :title title))))

(defun get-movie (movie-id)
  (with-pooled-connection
      (get-dao 'movies movie-id)))

(defun process-file-by-line (file-name fn)
  (with-open-file (stream file-name :external-format :iso-8859-1)
    (do ((line (read-line stream nil)
               (read-line stream nil)))
        ((null line))
      (funcall fn line))))

(defun load-movies (movies-file)
  (process-file-by-line
   movies-file
   #'(lambda (line)
       (let* ((splitted-line (split-sequence:split-sequence #\, line))
	      (movie-id (parse-integer (first splitted-line)))
	      (year (second splitted-line))
	      (title (third splitted-line)))
	 (make-movie movie-id year title)))))

(deftable ratings ()
  ((user-id :type integer
	    :accessor user-of
	    :initarg :user-id)
   (movie-id :type integer
	     :accessor movie-of
	     :initarg :movie-id)
   (rating :type integer
	   :accessor rating-of
	   :initarg :rating)
   (date :type string
	 :accessor date-of
	 :initarg :date))
  (:indices (user-id movie-id) rating)
  (:class-name ratings))

(defun make-rating (user-id movie-id rating date)
  (with-pooled-connection
      (save-dao (make-instance 'ratings
			       :user-id user-id
			       :movie-id movie-id
			       :rating rating
			       :date date))))

(defun get-ratings-of-user (user-id)
  (with-pooled-connection
    (select-daos 'ratings :test `(:= user-id ,user-id))))

(defun get-ratings-of-movie (movie-id)
  (with-pooled-connection
    (select-daos 'ratings :test `(:= movie-id ,movie-id))))

(defun load-ratings-file (file)
  (let ((movie-id 0))
    (process-file-by-line
     file
     #'(lambda (line)
	 (if (search ":" line)
	     (setq movie-id (parse-integer line :junk-allowed t))
	     (let* ((splitted-line (split-sequence:split-sequence #\, line))
		    (user-id (parse-integer (first splitted-line)))
		    (rating (parse-integer (second splitted-line)))
		    (date (third splitted-line)))
	       (make-rating user-id movie-id rating date)))))
    (format t "processed: ~a~%" file)))

(defun load-ratings (ratings-dir)
  (cl-fad:walk-directory
   ratings-dir
   #'(lambda (file)
       (load-ratings-file file))))

(defun load-data ()
  (load-movies *movies-file*)
  (load-ratings *ratings-dir*))

(defun setup-database (&optional (first-drop nil))
  (with-pooled-connection
    (create-tables '(movies ratings) :first-drop first-drop)))

(defun predict-ratings (query-file predict-file predict-fn)
  (with-open-file (stream predict-file :direction :output)
    (let ((movie-id 0))
      (process-file-by-line
       query-file
       #'(lambda (line)
	   (if (search ":" line)
	       (progn
		 (setq movie-id (parse-integer line :junk-allowed t))
		 (format stream "~a:~%" movie-id))
	       (let* ((splitted-line (split-sequence:split-sequence #\, line))
		      (user-id (parse-integer (first splitted-line)))
		      (date (second splitted-line)))
		 (format stream "~1$~%"
			 (funcall predict-fn user-id movie-id date)))))))))