<?php

  // Include applicaiton specific model base
  require_once APPLICATION_PATH . '/resources/ActiveCollabModuleModel.class.php';

  /**
   * System module model definition
   *
   * @package activeCollab.modules.system
   * @subpackage models
   */
  class SystemModuleModel extends ActiveCollabModuleModel {

    /**
     * Construct system module model definition
     *
     * @param SystemModule $parent
     */
    function __construct(SystemModule $parent) {
      parent::__construct($parent);
      
      $this->addModel(DB::createTable('assignment_filters')->addColumns(array(
        DBIdColumn::create(), 
        DBNameColumn::create(50), 
        DBAdditionalPropertiesColumn::create(), 
        DBDateTimeColumn::create('created_on'),
        DBUserColumn::create('created_by'), 
        DBBoolColumn::create('is_private', false), 
      ))->addIndices(array(
        DBIndex::create('name'), 
      )));

      $this->addModel(DB::createTable('milestone_filters')->addColumns(array(
        DBIdColumn::create(),
        DBNameColumn::create(50),
        DBAdditionalPropertiesColumn::create(),
        DBDateTimeColumn::create('created_on'),
        DBUserColumn::create('created_by'),
        DBBoolColumn::create('is_private', false),
      ))->addIndices(array(
        DBIndex::create('name'),
      )));
      
      $this->addModel(DB::createTable('companies')->addColumns(array(
        DBIdColumn::create(), 
        DBStateColumn::create(), 
        DBNameColumn::create(100),
        DBStringColumn::create('note'),
        DBActionOnByColumn::create('created'), 
        DBActionOnByColumn::create('updated'), 
        DBBoolColumn::create('is_owner', false), 
      ))->addIndices(array(
        DBIndex::create('name'),
      )));
      
      $this->addModel(DB::createTable('projects')->addColumns(array(
        DBIdColumn::create(),
        DBStringColumn::create('slug', 50), 
        DBIntegerColumn::create('template_id', 10)->setUnsigned(true),
        DBStringColumn::create('based_on_type', 50), 
        DBIntegerColumn::create('based_on_id', 10)->setUnsigned(true), 
        DBIntegerColumn::create('company_id', 5, '0')->setUnsigned(true), 
        DBIntegerColumn::create('category_id', 10)->setUnsigned(true), 
        DBIntegerColumn::create('label_id', 5)->setUnsigned(true),
        DBIntegerColumn::create('currency_id', 5)->setUnsigned(true),
        DBMoneyColumn::create('budget')->setUnsigned(true),  
        DBStateColumn::create(), 
        DBNameColumn::create(150), 
        DBUserColumn::create('leader'), 
        DBTextColumn::create('overview'), 
        DBBoolColumn::create('default_visibility', false),  
        DBActionOnByColumn::create('completed', true), 
        DBActionOnByColumn::create('created', true), 
        DBActionOnByColumn::create('updated'), 
      ))->addIndices(array(
        DBIndex::create('slug', DBIndex::UNIQUE, 'slug'), 
        DBIndex::create('company_id'),
        DBIndex::create('category_id'),
        DBIndex::create('label_id'), 
      )));
      
      $this->addModel(DB::createTable('project_roles')->addColumns(array(
        DBIdColumn::create(DBColumn::SMALL), 
        DBNameColumn::create(50), 
        DBTextColumn::create('permissions'), 
        DBBoolColumn::create('is_default'), 
      ))->addIndices(array(
        DBIndex::create('name', DBIndex::UNIQUE, 'name'), 
      )));
      
      $this->addTable(DB::createTable('project_users')->addColumns(array(
        DBIntegerColumn::create('user_id', 5, '0')->setUnsigned(true), 
        DBIntegerColumn::create('project_id', 10, '0')->setUnsigned(true), 
        DBTextColumn::create('permissions'), 
        DBIntegerColumn::create('role_id', 3, '0')->setUnsigned(true), 
      ))->addIndices(array(
        DBIndexPrimary::create(array('user_id', 'project_id')), 
      )));
      
      $this->addModel(DB::createTable('project_requests')->addColumns(array(
			  DBIdColumn::create(), 
			  DBStringColumn::create('public_id', 32, ''), 
			  DBNameColumn::create(150), 
			  DBTextColumn::create('body'), 
			  DBIntegerColumn::create('status', 4, '0'), 
			  DBActionOnByColumn::create('created'),
        DBIntegerColumn::create('created_by_company_id', 10),
			  DBStringColumn::create('created_by_company_name', 100, ''),
			  DBStringColumn::create('created_by_company_address', 150, ''),
			  DBTextColumn::create('custom_field_1'), 
			  DBTextColumn::create('custom_field_2'), 
			  DBTextColumn::create('custom_field_3'), 
			  DBTextColumn::create('custom_field_4'), 
			  DBTextColumn::create('custom_field_5'),  
			  DBBoolColumn::create('is_locked', false), 
			  DBUserColumn::create('taken_by'), 
			  DBActionOnByColumn::create('closed'),
			  DBDateTimeColumn::create('last_comment_on'), 
			)));
      
      $this->addModel(DB::createTable('project_objects')->addColumns(array(
        DBIdColumn::create(), 
        DBTypeColumn::create('ProjectObject'), 
        DBStringColumn::create('source', 50),
        DBStringColumn::create('module', 30, 'system'), 
        DBIntegerColumn::create('project_id', 10, '0')->setUnsigned(true), 
        DBIntegerColumn::create('milestone_id', 10)->setUnsigned(true), 
        DBIntegerColumn::create('category_id', 10)->setUnsigned(true), 
        DBIntegerColumn::create('label_id', 5)->setUnsigned(true),  
        DBIntegerColumn::create('assignee_id', 10)->setUnsigned(true), 
        DBIntegerColumn::create('delegated_by_id', 10)->setUnsigned(true), 
        DBNameColumn::create(150), 
        DBTextColumn::create('body')->setSize(DBColumn::BIG), 
        DBTextColumn::create('tags'), 
        DBStateColumn::create(), 
        DBVisibilityColumn::create(), 
        DBIntegerColumn::create('priority', 4),  
        DBActionOnByColumn::create('created'), 
        DBActionOnByColumn::create('updated'), 
        DBDateColumn::create('due_on'), 
        DBActionOnByColumn::create('completed'), 
        DBBoolColumn::create('is_locked'), 
        DBStringColumn::create('varchar_field_1', 255), 
        DBStringColumn::create('varchar_field_2', 255), 
        DBStringColumn::create('varchar_field_3', 255), 
        DBIntegerColumn::create('integer_field_1', 11), 
        DBIntegerColumn::create('integer_field_2', 11), 
        DBIntegerColumn::create('integer_field_3', 11), 
        DBDecimalColumn::create('float_field_1', 12, 2), 
        DBDecimalColumn::create('float_field_2', 12, 2), 
        DBDecimalColumn::create('float_field_3', 12, 2), 
        DBTextColumn::create('text_field_1'), 
        DBTextColumn::create('text_field_2'), 
        DBTextColumn::create('text_field_3'), 
        DBDateColumn::create('date_field_1'), 
        DBDateColumn::create('date_field_2'), 
        DBDateColumn::create('date_field_3'), 
        DBDateTimeColumn::create('datetime_field_1'), 
        DBDateTimeColumn::create('datetime_field_2'), 
        DBDateTimeColumn::create('datetime_field_3'), 
        DBBoolColumn::create('boolean_field_1'), 
        DBBoolColumn::create('boolean_field_2'), 
        DBBoolColumn::create('boolean_field_3'), 
        DBIntegerColumn::create('position', 10)->setUnsigned(true), 
        DBIntegerColumn::create('version', 11, '0')->setUnsigned(true), 
      ))->addIndices(array(
        DBIndex::create('module'), 
        DBIndex::create('project_id'), 
        DBIndex::create('milestone_id'), 
        DBIndex::create('category_id'), 
        DBIndex::create('assignee_id'), 
        DBIndex::create('delegated_by_id'), 
        DBIndex::create('due_on'), 
      )))->setTypeFromField('type');
      
      $this->addModel(DB::createTable('shared_object_profiles')->addColumns(array(
        DBIdColumn::create(),
        DBParentColumn::create(false),  
        DBStringColumn::create('parent_type', 50), 
        DBIntegerColumn::create('parent_id', 10, '0')->setUnsigned(true), 
        DBStringColumn::create('sharing_context', 50, ''), 
        DBStringColumn::create('sharing_code', 100, ''), 
        DBDateColumn::create('expires_on'), 
        DBAdditionalPropertiesColumn::create(), 
        DBActionOnByColumn::create('created', false, false), 
        DBBoolColumn::create('is_discoverable', false),   
      ))->addIndices(array(
        DBIndex::create('sharing', DBIndex::UNIQUE, array('sharing_context', 'sharing_code')), 
        DBIndex::create('parent', DBIndex::UNIQUE, array('parent_type', 'parent_id')),
      )));
			
      $this->addModel(DB::createTable('tax_rates')->addColumns(array(
        DBIdColumn::create(), 
        DBNameColumn::create(50, true), 
        DBDecimalColumn::create('percentage', 6, 3), 
      )));
			
			// Modify users table
			$users_table = AngieApplicationModel::getTable('users');
			
			$users_table->addColumn(DBIntegerColumn::create('company_id', 5, '0')->setUnsigned(true), 'id');
			$users_table->addIndex(DBIndex::create('company_id', DBIndex::KEY, 'company_id'));
			
			$users_table->addColumn(DBBoolColumn::create('auto_assign', false), 'last_activity_on');
			$users_table->addColumn(DBIntegerColumn::create('auto_assign_role_id', 3)->setUnsigned(true), 'auto_assign');
			$users_table->addColumn(DBTextColumn::create('auto_assign_permissions'), 'auto_assign_role_id');
    } // __construct
    
    /**
     * Load initial module data
     *
     * @param string $environment
     */
    function loadInitialData($environment = null) {
      $this->setConfigOptionValue('theme', 'evolution');
      
      if(LICENSE_PACKAGE == 'corporate') {
        $this->addConfigOption('project_tabs', array('outline', 'milestones', 'tasks', 'discussions', 'files', 'notebooks', 'time', 'source', 'calendar'));
      } else {
        $this->addConfigOption('project_tabs', array('outline', 'milestones', 'todo_lists', 'discussions', 'files'));
      } // if

      $this->addConfigOption('clients_can_delegate_to_employees', true);
      $this->addConfigOption('project_templates_category');

      // System info
      $this->addConfigOption('license_details_updated_on');
      $this->addConfigOption('latest_version');
      $this->addConfigOption('latest_available_version');
      $this->addConfigOption('license_copyright_removed', LICENSE_COPYRIGHT_REMOVED);
      $this->addConfigOption('license_expires', strtotime(LICENSE_EXPIRES));
      $this->addConfigOption('license_package', LICENSE_PACKAGE);
      $this->addConfigOption('remove_branding_url');
      $this->addConfigOption('renew_support_url');
      $this->addConfigOption('upgrade_to_corporate_url');
      $this->addConfigOption('update_instructions_url');

      // Company properties
      $this->addConfigOption('office_address');
      $this->addConfigOption('office_fax');
      $this->addConfigOption('office_homepage');
      $this->addConfigOption('office_phone');
      
      // Identity
      $this->setConfigOptionValue('identity_name', 'Projects');
      
      // User properties
      $this->addConfigOption('title');
      $this->addConfigOption('phone_mobile');
      $this->addConfigOption('phone_work');
      
      $this->addConfigOption('im_type');
      $this->addConfigOption('im_value');
      
      $this->addConfigOption('welcome_message');
      
      $this->addConfigOption('default_project_object_visibility', 1);
      $this->addConfigOption('first_milestone_starts_on');
      
      // Project requests
      $this->addConfigOption('project_requests_enabled', false);
      $this->addConfigOption('project_requests_page_title', 'Request a Project');
      $this->addConfigOption('project_requests_page_description', 'Please tell us more about your project');
      $this->addConfigOption('project_requests_custom_fields', array(
        'custom_field_1' => array('enabled' => true, 'name' => 'Budget'), 
        'custom_field_2' => array('enabled' => true, 'name' => 'Time Frame'), 
        'custom_field_3' => array('enabled' => false, 'name' => ''), 
        'custom_field_4' => array('enabled' => false, 'name' => ''), 
        'custom_field_5' => array('enabled' => false, 'name' => '')
      ));
      $this->addConfigOption('project_requests_captcha_enabled', false);
      $this->addConfigOption('project_requests_notify_user_ids');

      // ---------------------------------------------------
      //  Defaults
      // ---------------------------------------------------

      $this->createHomescreen(array(

        // Welcome tab
        array(
          'type' => 'SplitHomescreenTab',
          'name' => 'Welcome',
          'widgets' => array(
            array('type' => 'RecentActivitiesHomescreenWidget', 'column' => 1),
            array('type' => 'WelcomeHomescreenWidget', 'column' => 2, 'additional' => array(
              'welcome_message' => "Welcome to our project collaboration environment! You will find all your projects when you click on 'Projects' icon in the main navigation. To get back to this page, you can always click on 'Home Screen' navigation item.",
            )),
            array('type' => 'SystemNotificationsHomescreenWidget', 'column' => 2),
            array('type' => 'RemindersHomescreenWidget', 'column' => 2),
            array('type' => 'MyProjectsHomescreenWidget', 'column' => 2),
            array('type' => 'WhosOnlineHomescreenWidget', 'column' => 2),
          )
        )

      ));

      // ---------------------------------------------------
      //  Administrator and Employee
      // ---------------------------------------------------

      $this->createHomescreen(array(

        // Welcome tab
        array(
          'type' => 'SplitHomescreenTab',
          'name' => 'Welcome',
          'widgets' => array(
            array('type' => 'RecentActivitiesHomescreenWidget', 'column' => 1),
            array('type' => 'SystemNotificationsHomescreenWidget', 'column' => 2),
            array('type' => 'RemindersHomescreenWidget', 'column' => 2),
            array('type' => 'MyTasksHomescreenWidget', 'column' => 2),
            array('type' => 'MyProjectsHomescreenWidget', 'column' => 2),
            array('type' => 'WhosOnlineHomescreenWidget', 'column' => 2),
          )
        ),

        // Assignments tab
        array(
          'type' => 'AssignmentFiltersHomescreenTab',
          'name' => 'Filter Assignments',
        )

      ), 'Role', DB::executeFirstCell('SELECT id FROM ' . TABLE_PREFIX . 'roles WHERE name = ?', 'Administrator'));

      $this->createHomescreen(array(

        // Welcome tab
        array(
          'type' => 'SplitHomescreenTab',
          'name' => 'Welcome',
          'widgets' => array(
            array('type' => 'RecentActivitiesHomescreenWidget', 'column' => 1),
            array('type' => 'SystemNotificationsHomescreenWidget', 'column' => 2),
            array('type' => 'RemindersHomescreenWidget', 'column' => 2),
            array('type' => 'MyTasksHomescreenWidget', 'column' => 2),
            array('type' => 'MyProjectsHomescreenWidget', 'column' => 2),
            array('type' => 'WhosOnlineHomescreenWidget', 'column' => 2),
          )
        ),

        // Assignments tab
        array(
          'type' => 'AssignmentFiltersHomescreenTab',
          'name' => 'Filter Assignments',
        )

      ), 'Role', DB::executeFirstCell('SELECT id FROM ' . TABLE_PREFIX . 'roles WHERE name = ?', 'Employee'));

      // ---------------------------------------------------
      //  Project manager role
      // ---------------------------------------------------

      $project_manager_role_id = $this->addRole('Project Manager', array(
        'has_system_access' => true, 
        'can_use_api' => true,
      	'can_use_feeds' => true,
        'can_manage_projects' => true, 
        'can_add_project' => true, 
        'can_see_project_budgets' => true, 
        'can_manage_trash' => true, 
        'can_see_private_objects' => true,
        'can_see_company_notes' => true,
      ));

      $this->createHomescreen(array(

        // Welcome tab
        array(
          'type' => 'SplitHomescreenTab',
          'name' => 'Welcome',
          'widgets' => array(
            array('type' => 'RecentActivitiesHomescreenWidget', 'column' => 1),
            array('type' => 'SystemNotificationsHomescreenWidget', 'column' => 2),
            array('type' => 'RemindersHomescreenWidget', 'column' => 2),
            array('type' => 'MyTasksHomescreenWidget', 'column' => 2),
            array('type' => 'MyProjectsHomescreenWidget', 'column' => 2),
            array('type' => 'WhosOnlineHomescreenWidget', 'column' => 2),
          )
        ),

        // Assignments tab
        array(
          'type' => 'AssignmentFiltersHomescreenTab',
          'name' => 'Filter Assignments',
        )

      ), 'Role', $project_manager_role_id);

      // ---------------------------------------------------
      //  People Manager Role
      // ---------------------------------------------------
      
      // People manager role
      $people_manager_role_id = $this->addRole('People Manager', array(
        'has_system_access' => true, 
        'can_use_api' => true,
      	'can_use_feeds' => true,
        'can_manage_people' => true,
        'can_see_company_notes' => true,
        'can_manage_trash' => true, 
        'can_see_private_objects' => true, 
      ));

      $this->createHomescreen(array(

        // Welcome tab
        array(
          'type' => 'SplitHomescreenTab',
          'name' => 'Welcome',
          'widgets' => array(
            array('type' => 'RecentActivitiesHomescreenWidget', 'column' => 1),
            array('type' => 'SystemNotificationsHomescreenWidget', 'column' => 2),
            array('type' => 'RemindersHomescreenWidget', 'column' => 2),
            array('type' => 'MyTasksHomescreenWidget', 'column' => 2),
            array('type' => 'MyProjectsHomescreenWidget', 'column' => 2),
            array('type' => 'WhosOnlineHomescreenWidget', 'column' => 2),
          )
        ),

        // Assignments tab
        array(
          'type' => 'AssignmentFiltersHomescreenTab',
          'name' => 'Filter Assignments',
        )

      ), 'Role', $people_manager_role_id);

      // ---------------------------------------------------
      //  Client Company Manager Role
      // ---------------------------------------------------

      $this->addRole('Client Company Manager', array(
        'has_system_access' => true, 
        'can_manage_company_details' => true, 
      ));

      // ---------------------------------------------------
      //  Client Company Employee Role
      // ---------------------------------------------------
      
      $this->addRole('Client Company Employee', array(
        'has_system_access' => true, 
      ), true);
      
      // Companies
      $owner_company_id = $this->addCompany('Owner Company', array(
        'is_owner' => true
      ));
      
      // Users
      $this->addUser('user@activecollab.com', $owner_company_id, 1);
      
      // Default set of project labels
      $white = '#FFFFFF';
      $black = '#000000';
      $red = '#FF0000';
      $green = '#00A651';
      $blue = '#0000FF';
      $yellow = '#FFFF00';
      
      $labels = array(
        array('NEW', $black, $yellow),  
        array('INPROGRESS', $white, $green), 
        array('CANCELED', $white, $red), 
        array('PAUSED', $white, $blue), 
      );
      
      $labels_table = TABLE_PREFIX . 'labels';
      
      foreach($labels as $label) {
        list($label_name, $fg_color, $bg_color) = $label;
        
        DB::execute("INSERT INTO $labels_table (type, name, raw_additional_properties) VALUES (?, ?, ?)", 'ProjectLabel', $label_name, serialize(array('fg_color' => $fg_color, 'bg_color' => $bg_color)));
      } // foreach
      
      parent::loadInitialData($environment);
    } // loadInitialData
    
    /**
     * Create a new home screen
     * 
     * If $parent_type and $parent_id are NULL, default home screen will be 
     * created. Other options are home screen for a role and home screen for a 
     * user
     * 
     * @param array $homescreen_tabs
     * @param string $parent_type
     * @param string $parent_id
     * @return integer
     */
    private function createHomescreen($homescreen_tabs = null, $parent_type = null, $parent_id = null) {
      DB::execute('INSERT INTO ' . TABLE_PREFIX . 'homescreens (type, parent_type, parent_id) VALUES (?, ?, ?)', 'Homescreen', $parent_type, $parent_id);
      
      $homescreen_id = DB::lastInsertId();
      
      if($homescreen_tabs) {
        foreach($homescreen_tabs as $homescreen_tab) {
          $this->createHomescreenTab($homescreen_id, array_var($homescreen_tab, 'type'), array_var($homescreen_tab, 'name'), array_var($homescreen_tab, 'widgets'), array_var($homescreen_tab, 'additional'));
        } // foreach
      } // if
      
      return $homescreen_id;
    } // createHomescreen
    
    /**
     * Create a new homescreen tab
     * 
     * @param integer $homescreen_id
     * @param string $type
     * @param string $name
     * @param array $widgets
     * @param array $additional_variables
     * @return integer
     */
    private function createHomescreenTab($homescreen_id, $type, $name, $widgets = null, $additional_variables = null) {
      if($homescreen_id && $type && $name) {
        $homescreen_tabs_table = TABLE_PREFIX . 'homescreen_tabs';
        $raw_additional_variables = is_array($additional_variables) ? $additional_variables : null;
      
        $position = DB::executeFirstCell("SELECT MAX(position) FROM $homescreen_tabs_table WHERE homescreen_id = ?", $homescreen_id) + 1;
        DB::execute("INSERT INTO $homescreen_tabs_table (type, homescreen_id, name, position, raw_additional_properties) VALUES (?, ?, ?, ?, ?)", $type, $homescreen_id, $name, $position, $raw_additional_variables);
        
        $homescreen_tab_id = DB::lastInsertId();
        
        if($widgets) {
          foreach($widgets as $widget) {
            $this->createHomescreenWidget($homescreen_tab_id, array_var($widget, 'type'), array_var($widget, 'column'), array_var($widget, 'additional'));
          } // foreach
        } // if
        
        return $homescreen_tab_id;
      } else {
        return 0;
      } // if
    } // createHomescreenTab
    
    /**
     * Create homescreen widget
     * 
     * @param integer $homescreen_tab_id
     * @param string $type
     * @param integer $column
     * @param array $additional_variables
     */
    private function createHomescreenWidget($homescreen_tab_id, $type, $column, $additional_variables = null) {
      if($homescreen_tab_id && $type) {
        $homescreen_widgets_table = TABLE_PREFIX . 'homescreen_widgets';
        $raw_additional_variables = is_array($additional_variables) ? serialize($additional_variables) : null;
        
        $column = (integer) $column;
        if($column < 1) {
          $column = 1;
        } // if
        
        $position = DB::executeFirstCell("SELECT MAX(position) FROM $homescreen_widgets_table WHERE homescreen_tab_id = ?", $homescreen_tab_id) + 1;
        DB::execute("INSERT INTO $homescreen_widgets_table (type, homescreen_tab_id, column_id, position, raw_additional_properties) VALUES (?, ?, ?, ?, ?)", $type, $homescreen_tab_id, $column, $position, $raw_additional_variables);
        
        return DB::lastInsertId();
      } else {
        return 0;
      } // if
    } // createHomescreenWidget
    
  }