;;; -*- Mode: LISP; Syntax: COMMON-LISP; Package: CL-USER; Base: 10 -*-
;;; $Header: src/setup.lisp $

;;; Copyright (c) 2008, Andrea Chiumenti.  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.

;;; THIS SOFTWARE IS PROVIDED BY THE AUTHOR 'AS IS' AND ANY EXPRESSED
;;; 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 AUTHOR 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.

(in-package :claw-demo-backend)

(defvar *claw-demo-db* nil
  "The demo datebase")

(defun db-connect (&optional (connection-string '("127.0.0.1" "claw-demo" "claw-demo" "demo")))
  (connect connection-string :database-type :postgresql :pool t))

(defun db-disconnect (&optional (database *claw-demo-db*) )
  (disconnect :database database))


(defun create-claw-demo-tables ()
  (let ((clsql:*default-database* *claw-demo-db*))
    (create-view-from-class 'user-role)
    (create-view-from-class 'user)
    (create-view-from-class 'role)
    (let ((user-role-table (symbol-name (view-table (find-class 'user-role)))))
      (execute-command (format nil "ALTER TABLE ~a ADD CONSTRAINT ~a_fk1 FOREIGN KEY (~a) REFERENCES ~a (id) ON DELETE CASCADE"
                               user-role-table
                               user-role-table
                               "USER_ID"
                               (symbol-name (view-table (find-class 'user)))))
      (execute-command (format nil "ALTER TABLE ~a ADD CONSTRAINT ~a_fk2 FOREIGN KEY (~a) REFERENCES ~a (id) ON DELETE CASCADE"
                               user-role-table
                               user-role-table
                               "ROLE_ID"
                               (symbol-name (view-table (find-class 'role))))))
    (create-view-from-class 'city)
    (create-view-from-class 'customer)
    (create-view-from-class 'customer-address)
    (let ((customer-address-table (symbol-name (view-table (find-class 'customer-address)))))
      (execute-command (format nil "ALTER TABLE ~a ADD CONSTRAINT ~a_fk1 FOREIGN KEY (~a) REFERENCES ~a (id) ON DELETE CASCADE"
                               customer-address-table
                               customer-address-table
                               "CUSTOMER_ID"
                               (symbol-name (view-table (find-class 'customer))))))))

(defun drop-claw-demo-tables ()
  (let ((clsql:*default-database* *claw-demo-db*))
    (dolist (table (list-tables))
      (execute-command (format nil "DROP TABLE ~a CASCADE" table)))
    (dolist (sequence (list-sequences))
      (execute-command (format nil "DROP SEQUENCE ~a" sequence)))))

(defun demo-setup ()
  (let ((*claw-demo-db* (db-connect)))
  (drop-claw-demo-tables)
  (create-claw-demo-tables)
  (with-transaction ()
    (let ((admin-role (make-instance 'role :name "administrator"))
          (user-role (make-instance 'role :name "user")))
      (update-db-item admin-role)
      (update-db-item user-role)
      (update-db-item (make-instance 'user :firstname "Andrea"
                                     :surname "Chiumenti"
                                     :username "admin"
                                     :password "admin"
                                     :email "admin@new.com"
                                     :roles (list admin-role user-role)))
      (loop for i from 1 to 400 
         do (update-db-item (make-instance 'customer 
                                           :name1 (format nil "Andrea~a" i)
                                           :name2 (format nil "Chiumenti~a" i)
                                           :email (format nil "a~a.chiumenti@new.com" i)
                                           :phone1 "+393900001"
                                           :phone2 "+393900002"
                                           :phone3 "+393900003"
                                           :fax "+393900010"
                                           :vat (format nil "9999999999-~a" i)
                                           :code1 (format nil "code1-~a" i)
                                           :code1 (format nil "code2-~a" i)
                                           :code1 (format nil "code3-~a" i)
                                           :code1 (format nil "code4-~a" i))))))
  (db-disconnect)))