# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import fields, models, tools, api


class ProjectTimelineReport(models.Model):

    _name = "project.timeline.report"
    _description = "Project Timeline Report"
    _order = 'project_id desc, timeline_type asc'
    _auto = False

    #analytic_account_id = fields.Many2one('account.analytic.account', string='Analytic Account', readonly=True)
    project_id = fields.Many2one('project.project', string='Project', readonly=True)
    parent_project = fields.Many2one('project.project', string='Parent Project', readonly=True)
    #is_sub_project = fields.Boolean("Is Sub Project", readonly=True)
    #sub_project = fields.Many2one('project.project', string='Sub Project', readonly=True)
    start_date = fields.Date(string='Start Date', readonly=True)
    end_date = fields.Date(string='End Date', readonly=True)
    partner_id = fields.Many2one('res.partner', string='Client', readonly=True)
    #employee_id = fields.Many2one('hr.employee', string='Consultant', readonly=True)
    timeline_type = fields.Char(string="Timeline Type", readonly=True)
    timeline = fields.Float("Timeline", digits=(16, 2), readonly=True, group_operator="sum")
    hours = fields.Float("Number of Hours", digits=(16, 2), readonly=True, group_operator="sum")
    pricing_type = fields.Selection([
        ('fixed_rate', 'Fixed rate'),
        ('employee_rate', 'Consultant rate')
    ], string="Pricing", readonly=True)
    project_type = fields.Selection([
        ('hours_in_consultant', 'Hours are budgeted according to a consultant'),
        ('hours_no_limit', 'Total hours are budgeted without division to consultant'),
    ], string="Project Type", readonly=True)

    def init(self):
        '''Create the view'''
        tools.drop_view_if_exists(self._cr, self._table)
        self._cr.execute("""
        CREATE OR REPLACE VIEW %s AS (
        SELECT
         ROW_NUMBER() OVER() as id,
         project_id,
         parentproject as parent_project,
         startdate as start_date,
         enddate as end_date,
         pricing_type as pricing_type,
         project_type as project_type,
         partner_id,
         timeline_type,
         hours,
         timeline from (
             SELECT
              pro.id AS project_id,
              (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,
              date_start AS startdate,
              date AS enddate,
              'Budgeted' as timeline_type,
              pro.budgeted_hours2 as hours,
              (date - date_start) as timeline,
              --DATE_PART('day', AGE(date, date_start)) AS timeline,
              pro.*
            FROM
              project_project PRO
            Where
              pro.active = 't'
        Union all
            SELECT
               pro.id AS project_id,
               (select project_id from project_subproject_rel as par where pro.id=par.id limit 1) as parentproject,
               (select min(al.start_datetime::date) from account_analytic_line as al where pro.id=al.project_id limit 1) as startdate,
               (select max(al.end_datetime::date) from account_analytic_line as al where pro.id=al.project_id) as enddate,
               'Actual' as timeline_type,
               sum(unit_amount) as hours,
               ((select max(al.end_datetime::date) from account_analytic_line as al where pro.id=al.project_id) - (select min(al.start_datetime::date) from account_analytic_line as al where pro.id=al.project_id)) AS timeline,
               --DATE_PART('day', AGE((select max(al.end_datetime::date) from account_analytic_line as al where pro.id=al.project_id), (select min(al.start_datetime::date) from account_analytic_line as al where pro.id=al.project_id))) AS timeline,
               pro.*
               FROM project_project PRO
               LEFT JOIN account_analytic_account AA ON PRO.analytic_account_id = AA.id
               LEFT JOIN account_analytic_line AAL ON AAL.account_id = AA.id and AAL.project_id = PRO.id
            Where
              PRO.active = 't'
              group by pro.id
              order by  project_id desc, timeline_type asc
              ) as res
               order by
               project_id desc,
              --start_date,
              --end_date desc,
              --pricing_type,
              --project_type,
              timeline_type asc
              --employee_id
        )""" % (self._table,))


