-- v_permission_table
create view v_permission_table as 
select  isn_permissions.id,
        isn_roles.name as 'role_name',
        isn_permissions.role_id,
        
		isn_permissions.table_id,
        isn_table.name as 'table_name',
        
        isn_permissions.allow_create,
        isn_permissions.allow_read,
        isn_permissions.allow_update,
        isn_permissions.allow_delete,
        isn_permissions.allow_serach,
        isn_permissions.allow_upload,
        isn_permissions.allow_send_email,
         isn_permissions.allow_approval,

        isn_permissions.inserted_by,
        isn_permissions.inserted_at,
        isn_permissions.updated_at,
        isn_permissions.updated_by,
        isn_permissions.deleted_at,
        isn_permissions.deleted_by,
        isn_permissions.restored_at,
        isn_permissions.restored_by,
        isn_permissions.is_deleted,
        isn_permissions.notes



        
        
        
        from isn_permissions 
        inner join isn_table 
         inner join isn_roles
on isn_permissions.table_id=isn_table.id AND
isn_permissions.role_id=isn_roles.id;


CREATE VIEW user_details AS

SELECT 
  u.id,
  u.full_name,
  u.username,
  TIMESTAMPDIFF(YEAR, u.join_date, CURDATE()) * 30 AS daysoff ,

  u.password,
  u.email,
  d.name AS department_name,  -- Assuming a departments table with a 'name' field
  u.dept_id,
  r.name AS role_name,        -- Assuming a roles table with a 'name' field
  u.role_id,
  c.name AS city_name,        -- Assuming a cities table with a 'name' field
  u.city_id,

  CASE
        WHEN TIMESTAMPDIFF(YEAR, u.join_date, CURDATE()) <= 5 THEN 
            TIMESTAMPDIFF(YEAR, u.join_date, CURDATE()) * 21
        ELSE 
            (5 * 21) + ((TIMESTAMPDIFF(YEAR, u.join_date, CURDATE()) - 5) * 30)
    END AS end_of_service,

  u.address,
  u.last_login,
  u.active,
  u.verified,
  u.verification_code,
  u.is_deleted,
  u.inserted_at,
  u.updated_at,
  u.deleted_at,
  u.inserted_by,
  u.updated_by,
  u.deleted_by,
  u.token,
  u.restored_at,
  u.restored_by,
  u.notes,
  u.Mobile,
  u.Evaluated_by,
  ue.full_name as Evaluated_full_name,

  u.Accommodation_type,
    acctype.name as Accommodation_type_name,
  acctype.color as Accommodation_type_color,

  u.Camp_location_or_address,
  u.Join_date,
  u.department_code,
  dep_code.code_number,


  u.Position,
  poi.name as Position_name,

  u.Emp_code,
  u.reporting_to,
  ux.full_name as reporting_full_name,
  
  u.Basic_Salary,
  u.bank_name,
  u.IBAN,
  u.Swift,
  u.account_number,
  u.account_name,
  camp.title AS camp_name,     -- Assuming a camps table with a 'name' field
  u.Camp_id,
  u.Allowances_accommodation,

  u.transportation,
  u.telecom,
  u.Allowances_empty_1,
  u.Allowances_empty_2,
  u.Allowances_empty_3,
  u.end_of_contract,


  u.birthdate,
  u.nationality,
  u.gender
FROM isn_users AS u
LEFT JOIN isn_department AS d ON u.dept_id = d.id  -- Assuming foreign key relationship
LEFT JOIN isn_roles AS r ON u.role_id = r.id        -- Assuming foreign key relationship
LEFT JOIN isn_city AS c ON u.city_id = c.id        -- Assuming foreign key relationship
LEFT JOIN isn_camps AS camp ON u.Camp_id = camp.id  -- Assuming foreign key relationship;
LEFT JOIN  isn_accommodation_types as acctype on u.Accommodation_type=acctype.id
LEFT JOIN  isn_positions as poi on u.Position=poi.id
LEFT JOIN  department_code_details as dep_code on u.department_code=dep_code.id
LEFT JOIN  isn_users as ux on u.reporting_to=ux.id
LEFT JOIN  isn_users as ue on u.Evaluated_by =ue.id
;




CREATE VIEW vacation_details AS
SELECT 
  v.Id,
  u.full_name AS employee_name,  -- Assuming a foreign key relationship with isn_users.id
  v.emp_id,
  v.start_date,
  v.end_date,
  vt.name AS vacation_type,     -- Assuming a vacation_types table with a 'name' field
  v.vacation_type_id,
  vt.color,
  v.approval_reason,
  v.approval_status,
  v.rejected_reason,
  v.approved_by,
  v.approved_at,
  v.inserted_by,
  u1.full_name AS inserted_by_name,

  v.inserted_at,
  v.updated_at,
  v.updated_by,
  v.deleted_at,
  v.deleted_by,
  v.restored_at,
  v.restored_by,
  v.is_deleted,
  v.notes
FROM isn_vacations AS v
LEFT JOIN isn_users AS u ON v.emp_id = u.id  -- Assuming foreign key relationship
LEFT JOIN isn_vacation_type AS vt ON v.vacation_type_id = vt.id
LEFT JOIN isn_users AS u1 ON v.inserted_by = u1.id
;

CREATE VIEW promotion_details AS
SELECT 
  p.Id,
  p.title,
  u.full_name AS employee_name,  -- Assuming a foreign key relationship with isn_users.id
  p.emp_id,
  p.description,
  p.value,

 p.pro_code,
  p.approval_status,
  u1.full_name AS Reply_name,
  p.approved_at,
  p.approved_by,
  p.rejected_reason,
  p.approval_reason,


  p.inserted_by,
  p.inserted_at,
  p.updated_at,
  p.updated_by,
  p.deleted_at,
  p.deleted_by,
  p.restored_at,
  p.restored_by,
  p.is_deleted,
  p.notes
FROM isn_promotion AS p
LEFT JOIN isn_users AS u ON p.emp_id = u.id  -- Assuming foreign key relationship
LEFT JOIN isn_users AS u1 ON p.approved_by = u1.id 

;

CREATE VIEW location_details AS
SELECT 
  l.id,
  c.name AS client_name,        -- Assuming a clients table with a 'name' field
  l.client_id,
  l.title,
  l.description,
  ct.name AS city_name,          -- Assuming a cities table with a 'name' field
  l.city_id ,
  l.address_line_1,
  l.address_line_2,
  l.maps_url,
  ls.name AS location_status,   -- Assuming a location_statuses table with a 'name' field
  l.location_status_id,
  ls.color,
  l.inserted_by,
  l.inserted_at,
  l.updated_at,
  l.updated_by,
  l.deleted_at,
  l.deleted_by,
  l.restored_at,
  l.restored_by,
  l.is_deleted,
  l.notes
FROM isn_locations AS l
LEFT JOIN isn_clients AS c ON l.client_id = c.id  -- Assuming foreign key relationship
LEFT JOIN isn_city AS ct ON l.city_id = ct.id    -- Assuming foreign key relationship
LEFT JOIN isn_location_status AS ls ON l.location_status_id = ls.id;  -- Assuming foreign key relationship


;

CREATE VIEW department_code_details AS
SELECT 
  dc.Id,
  dc.title,
  d.name AS department_name,   -- Assuming a departments table with a 'name' field
  dc.dept_id,
  dc.code_number,
  dc.job_description,
  dc.color,
  dc.inserted_by,
  dc.inserted_at,
  dc.updated_at,
  dc.updated_by,
  dc.deleted_at,
  dc.deleted_by,
  dc.restored_at,
  dc.restored_by,
  dc.is_deleted,
  dc.notes
FROM isn_departments_codes AS dc
LEFT JOIN isn_department AS d ON dc.dept_id = d.id;  -- Assuming foreign key relationship


;


CREATE VIEW contract_details AS
SELECT 
  c.id,
  c.contact_number,
  cl.name AS client_name,        -- Assuming a clients table with a 'name' field
  c.client_id,
  c.start_date,
  c.end_date,
  ct.name AS contract_type,     -- Assuming a contract_types table with a 'name' field
  c.contract_type_id,
  ct.color,
  c.contract_amount,
  c.description,
  c.status,
  c.inserted_by,
  c.inserted_at,
  c.updated_at,
  c.updated_by,
  c.deleted_at,
  c.deleted_by,
  c.restored_at,
  c.restored_by,
  c.is_deleted,
  c.notes
FROM isn_contracts AS c
LEFT JOIN isn_clients AS cl ON c.client_id = cl.id  -- Assuming foreign key relationship
LEFT JOIN isn_contract_types AS ct ON c.contract_type_id = ct.id;  -- Assuming foreign key relationship

;




CREATE VIEW client_details AS
SELECT 
  c.id,
  c.name,
  c.name_Addon AS additional_name,
  c.Client_Code,
  ct.name AS client_type,        -- Assuming a client_types table with a 'name' field
  c.Client_Type_id,
  c.Mobile,
  c.Email,
  cty.name AS city_name,          -- Assuming a cities table with a 'name' field
  c.City_id,
  c.Address_Line_1,
  c.Address_Line_2,
  cs.name AS client_status,       -- Assuming a client_statuses table with a 'name' field
  c.Client_Status_id,
  ct.color as client_type_color,
  cs.color as client_status_color,
  c.inserted_by,
  c.inserted_at,
  c.updated_at,
  c.updated_by,
  c.deleted_at,
  c.deleted_by,
  c.restored_at,
  c.restored_by,
  c.is_deleted,
  c.notes
FROM isn_clients AS c
LEFT JOIN isn_client_type AS ct ON c.Client_Type_id = ct.id  -- Assuming foreign key relationship
LEFT JOIN isn_city AS cty ON c.City_id = cty.id           -- Assuming foreign key relationship
LEFT JOIN isn_client_status AS cs ON c.Client_Status_id = cs.id;  -- Assuming foreign key relationship


;



CREATE VIEW camp_details AS
SELECT 
  c.Id,
  c.title,
  cty.name AS city_name,        -- Assuming a cities table with a 'name' field
  c.city_id,
  c.location,
  c.map,
  u.full_name AS camp_supervisor_name,  -- Assuming a users table with a 'full_name' field (replace if different)
  c.camp_supervisor,
  c.camp_capacity,
  c.camp_current_number_of_residents,
  c.camp_code,
  c.inserted_by,
  c.inserted_at,
  c.updated_at,
  c.updated_by,
  c.deleted_at,
  c.deleted_by,
  c.restored_at,
  c.restored_by,
  c.is_deleted,
  c.notes
FROM isn_camps AS c
LEFT JOIN isn_city AS cty ON c.city_id = cty.id -- Assuming foreign key relationship
LEFT JOIN isn_users AS u ON c.camp_supervisor = u.id;  -- Assuming foreign key relationship

;



CREATE VIEW employee_attendance AS
SELECT 
  a.Id,
  e.full_name AS employee_name,  -- Assuming a users/employees table with a 'full_name' field (replace if different)
  a.emp_id ,
  e.Basic_Salary,
  a.date,
  a.attended,
  a.is_holiday AS is_public_holiday,  -- Renaming for clarity
  a.overtime,
  a.cost,
  a.inserted_by,
  a.inserted_at,
  a.updated_at,
  a.updated_by,
  a.deleted_at,
  a.deleted_by,
  a.restored_at,
  a.restored_by,
  a.is_deleted,
  a.notes
FROM isn_attendance AS a
LEFT JOIN isn_users AS e ON a.emp_id = e.id;  -- Assuming foreign key relationship


;

CREATE VIEW asset_details AS
SELECT 
  a.Id,
  l.title AS location_name,        -- Assuming a locations table with a 'name' field
  a.location_id,
  cl.name AS client_name,          -- Assuming a clients table with a 'name' field
  a.client_id,
  a.Title AS asset_title,
  a.Description,
  a.Serial_Number,
  m.name AS model_name,            -- Assuming a models table with a 'name' field
  a.Model_id,
  m.color as model_color,
  ma.name AS manufacturer_name,   -- Assuming a manufacturers table with a 'name' field
  a.manufacturer_id,
  ma.color as manufacturer_color,
  a.Year,
  at.name AS asset_type,           -- Assuming an asset_types table with a 'name' field
  a.asset_type_id,
  at.color as assets_type_color,
  a.asset_barcode,
  ast.name AS asset_status,        -- Assuming an asset_statuses table with a 'name' field
  a.asset_status_id,
  ast.color as asset_status_color,
  a.inserted_by,
  a.inserted_at,
  a.updated_at,
  a.updated_by,
  a.deleted_at,
  a.deleted_by,
  a.restored_at,
  a.restored_by,
  a.is_deleted,
  a.notes
FROM isn_assets AS a
LEFT JOIN isn_locations AS l ON a.location_id = l.id -- Assuming foreign key relationship
LEFT JOIN isn_clients AS cl ON a.client_id = cl.id   -- Assuming foreign key relationship
LEFT JOIN isn_assets_model AS m ON a.Model_id = m.id         -- Assuming foreign key relationship
LEFT JOIN isn_manufacturer AS ma ON a.manufacturer_id = ma.id -- Assuming foreign key relationship
LEFT JOIN isn_assets_type AS at ON a.asset_type_id = at.id  -- Assuming foreign key relationship
LEFT JOIN isn_assets_status AS ast ON a.asset_status_id = ast.id;  -- Assuming foreign key relationship

;


CREATE VIEW assessment_read_details AS
SELECT
  ar.id,
  AT.name as type_name,
  ar.type_id ,  -- You might want to join a table here to decode the type_id (if applicable)
  AT.color as assessment_color,
  ar.value,
  ar.evaluation,
  ar.recommendation,
  ar.inserted_by,
  ar.inserted_at,
  ar.updated_at,
  ar.updated_by,
  ar.deleted_at,
  ar.deleted_by,
  ar.restored_at,
  ar.restored_by,
  ar.is_deleted,
  ar.notes
FROM isn_assessment_reads AS ar
left join 	isn_assessment_reading_type AS AT
ON ar.type_id=	AT.id


;

/*
CREATE VIEW approval_details AS
SELECT
  a.ID,
  a.rtable,  -- You might want to join a table here to decode the table name (if applicable)
  tb.name,
  a.record_id,  -- You might want to join a table here to get record details (if applicable)
  a.Status,
  a.Reply_by AS replied_by,  -- Renaming for clarity
  a.Reply_at AS replied_at,  -- Renaming for clarity
  a.rejected_reason,
  a.inserted_by,
  a.inserted_at,
  a.updated_at,
  a.updated_by,
  a.deleted_at,
  a.deleted_by,
  a.restored_at,
  a.restored_by,
  a.is_deleted,
  a.notes
FROM isn_approvals AS a
left join isn_table as tb
on tb.id=a.rtable


;
*/

CREATE VIEW enquiry_details AS
SELECT 
  e.id,
  e.enquiry_number,
  cl.name AS client_name,        -- Assuming isn_clients table with a 'name' field
  e.client_id,
  e.requested_by,
  r.full_name AS reported_by_name,  -- Assuming isn_users/isn_employees table with a 'full_name' field (replace if different)
  p.name AS priority_name,       -- Assuming isn_priorities table with a 'name' field
  e.priority_id,
  s.color as enquiry_status_color,
  e.request_date,
  e.expected_start_date,
  e.next_follow_up,
  e.contract_id,  -- Assuming isn_contracts table with a 'contract_number' field (replace if different)

  p.color as  priority_list_color,
  e.work_description,
  e.special_instructions,
  s.name AS status_name,         -- Assuming isn_enquiry_statuses table with a 'name' field
  e.status_id,
  e.inserted_by,
  e.inserted_at,
  e.updated_at,
  e.updated_by,
  e.deleted_at,
  e.deleted_by,
  e.restored_at,
  e.restored_by,
  e.is_deleted,
  e.notes
FROM isn_enquiry AS e
LEFT JOIN isn_clients AS cl ON e.client_id = cl.id  -- Assuming foreign key relationship
LEFT JOIN isn_users AS r ON e.reported_by = r.id    -- Assuming foreign key relationship
LEFT JOIN isn_priority_list AS p ON e.priority_id = p.id  -- Assuming foreign key relationship
LEFT JOIN isn_enquiry_status AS s ON e.status_id = s.id;  -- Assuming foreign key relationship


;

CREATE VIEW enquiry_location_details AS
SELECT 
  el.id,
  e.enquiry_number,
  el.enquery_id  ,
  l.title AS location_name,
  el.locations_id ,
  el.inserted_by,
  el.inserted_at,
  el.updated_at,
  el.updated_by,
  el.deleted_at,
  el.deleted_by,
  el.restored_at,
  el.restored_by,
  el.is_deleted,
  el.notes
FROM isn_enquery_locations AS el
LEFT JOIN isn_enquiry AS e ON el.enquery_id = e.id  -- Assuming foreign key relationship
LEFT JOIN isn_locations AS l ON el.locations_id = l.id;  -- Assuming foreign key relationship


;


CREATE VIEW file_details AS
SELECT 
  f.id,
  t.name AS table_name,            -- Assuming an isn_tables table with a 'name' field
  f.table_id,
  f.file_name,
  f.record_id,
  f.description,
  f.title,
  dt.name AS doc_type_name,        -- Assuming an isn_doc_types table with a 'name' field
  f.doc_type_id,
  f.inserted_by,
  f.inserted_at,
  f.updated_at,
  f.updated_by,
  f.deleted_at,
  f.deleted_by,
  f.restored_at,
  f.restored_by,
  f.is_deleted,
  f.notes
FROM isn_files AS f
LEFT JOIN isn_table AS t ON f.table_id = t.id  -- Assuming foreign key relationship
LEFT JOIN isn_doc_types AS dt ON f.doc_type_id = dt.id;  -- Assuming foreign key relationship
;

create view user_report_depart
as
SELECT p.role_name,p.role_id, p.table_name,p.table_id,
 p.allow_approval,u.full_name, u.id, u.department_name,
 u.dept_id, u.reporting_full_name, u.reporting_to 
 FROM v_permission_table as p
  left join user_details as u on p.role_id=u.role_id
   where p.allow_approval=1 
    and u.is_deleted=0;




CREATE VIEW v_isn_estimation_details AS
SELECT 
    ed.id,
    ed.number,
    ed.type_id,
    et.type_name AS type_name, -- assuming type_name is the column in isn_estimation_type
    ed.title,
    ed.description,
    ed.est_date,
    ed.valid_till,
    ed.remarks_notes,
    ed.status,
    es.status_name AS status_name, -- assuming status_name is the column in isn_estimation_status
    ed.additions,
    ed.inserted_by,
    ed.inserted_at,
    ed.updated_at,
    ed.updated_by,
    ed.deleted_at,
    ed.deleted_by,
    ed.restored_at,
    ed.restored_by,
    ed.is_deleted,
    ed.notes,
    ed.approval_status,
    ed.rejected_reason,
    ed.approved_by,
    eex.exclusion_details, -- assuming exclusion_details is the column in isn_estimation_exclusions
    eterm.term_details, -- assuming term_details is the column in isn_estimation_terms
    eet.extra_term_details, -- assuming extra_term_details is the column in isn_extra_estimation_terms
    eexcl.extra_exclusion_details -- assuming extra_exclusion_details is the column in isn_extra_estimation_exclusions
FROM 
    isn_estimation_details ed
LEFT JOIN 
    isn_estimation_type et ON ed.type_id = et.id
LEFT JOIN 
    isn_estimation_status es ON ed.status = es.id
LEFT JOIN 
    isn_estimation_exclusions eex ON ed.id = eex.estimation_id
LEFT JOIN 
    isn_estimation_terms eterm ON ed.id = eterm.estimation_id
LEFT JOIN 
    isn_extra_estimation_terms eet ON ed.id = eet.estimation_id
LEFT JOIN 
    isn_extra_estimation_exclusions eexcl ON ed.id = eexcl.estimation_id;
