CREATE TABLE public.payor(
  pyr_id SERIAL PRIMARY KEY,
  pyr_name VARCHAR(255) NULL,
  pyr_email VARCHAR(100) NULL,
  pyr_phone VARCHAR(50) NULL,
  pyr_company VARCHAR(255) NULL,
  pyr_status INTEGER NOT NULL DEFAULT 1,
  pyr_created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  pyr_updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL
);
COMMENT ON COLUMN public.payor.reg_status IS '1 => Active, 0 => Inactive';

CREATE TABLE public.users(
  usr_id SERIAL PRIMARY KEY,
  usr_pyr_id INTEGER NULL,
  usr_hos_id INTEGER NULL,
  usr_name VARCHAR(255) NULL,
  usr_email VARCHAR(100) NULL,
  usr_password VARCHAR(500) NULL,
  usr_otp INTEGER NULL,
  usr_picture VARCHAR(500) NULL,
  usr_category_type INTEGER NOT NULL DEFAULT 1,
  usr_status INTEGER NOT NULL DEFAULT 1,
  usr_created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  usr_updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL
);
COMMENT ON COLUMN public.users.usr_category_type IS '1 => Hospital, 2 => Payor';
COMMENT ON COLUMN public.users.usr_status IS '1 => Active, 0 => Inactive';

CREATE TABLE public.measure(
  id SERIAL PRIMARY KEY,
  measure_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  grid_section VARCHAR(50) NULL,
  benchmark_target VARCHAR(255) NULL,
  target_note VARCHAR(500) NULL,
  dinominator INTEGER NOT NULL DEFAULT 1,
  hedis_impact VARCHAR(255) NULL,
  benchmark_source VARCHAR(255) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL,
  year INTEGER NOT NULL,
);
COMMENT ON COLUMN public.measure.dinominator IS '1 => Yes, 2 => No, 3=> None';

CREATE TABLE public.hospital_users(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NOT NULL,
  name VARCHAR(255) NULL,
  email VARCHAR(100) NULL,
  password VARCHAR(500) NULL,
  otp INTEGER NULL,
  picture VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL
);
COMMENT ON COLUMN public.hospital_users.status IS '1 => Active, 0 => Inactive';



CREATE TABLE public.contractor(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NULL,
  email VARCHAR(100) NULL,
  picture VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL,
  created_by INTEGER NULL,
  payor_id INTEGER NULL,
);
COMMENT ON COLUMN public.contractor.status IS '1 => Active, 0 => Inactive';

CREATE TABLE public.country(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NULL,
  phone_code VARCHAR(100) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

CREATE TABLE public.zip(
  id SERIAL PRIMARY KEY,
  city_id INTEGER NULL,
  zipcode INTEGER NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

CREATE TABLE public.grid(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  facility_name VARCHAR(255) NULL,
  facility_contact VARCHAR(255) NULL,
  facility_email VARCHAR(100) NULL,
  facility_phone VARCHAR(100) NULL,
  healthplan_name VARCHAR(255) NULL,
  healthplan_contact VARCHAR(255) NULL,
  healthplan_email VARCHAR(100) NULL,
  healthplan_phone VARCHAR(100) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL,
);

CREATE TABLE public.grid_details(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  measure_type INTEGER NULL,
  measure_id INTEGER NOT NULL,
  measure_name VARCHAR(255) NULL,
  outcome_details VARCHAR(500) NULL,
  is_measure INTEGER NOT NULL DEFAULT 1,
  baseline_date DATE NULL,
  baseline_numerator FLOAT NULL,
  baseline_denominator FLOAT NULL,
  baseline_rate FLOAT NULL,
  final_target_rate FLOAT NULL,
  is_percentage INTEGER NOT NULL DEFAULT 1,
  final_target_dos DATE NULL,
  final_numerator FLOAT NULL,
  final_denominator FLOAT NULL,
  final_rate FLOAT NULL,
  point_at_risk FLOAT NULL,
  points_earned FLOAT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL,
);
COMMENT ON COLUMN public.grid_details.measure_type IS '1 => Core, 2 => Menu, 3=> Collaborative';
COMMENT ON COLUMN public.grid_details.is_measure IS '1 => No, 2 => Yes;
COMMENT ON COLUMN public.grid_details.is_percentage IS '1 => No, 2 => Yes;

CREATE TABLE public.program(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  category_id INTEGER NULL,
  aim_statement TEXT NULL,
  current_practice TEXT NULL,
  core_team TEXT NULL,
  planning TEXT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

CREATE TABLE public.program_result(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  category_id INTEGER NULL,
  year INTEGER NULL,
  submission_period VARCHAR(255) NULL,
  interventions TEXT NULL,
  result TEXT NULL,
  conclusions TEXT NULL,
  measurement_period DATE NULL,
  numerator FLOAT NULL,
  denominator FLOAT NULL,
  period_rate FLOAT NULL,
  target_rate FLOAT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

COMMENT ON COLUMN public.grid.status IS '1 => Not Approve, 2 => Approve';


CREATE TABLE public.support(
  id SERIAL PRIMARY KEY,
  description TEXT NULL,
  payor_comment TEXT NULL,
  admin_comment TEXT NULL,
  category_id INTEGER NULL,
  complition_date DATE NULL,
  status INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);
COMMENT ON COLUMN public.support.status IS '0 => Open, 1 => Closed';

CREATE TABLE public.hospital_stage(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  year INTEGER NULL,
  current_stage INTEGER NULL,
  stage_name VARCHAR(255) NULL,
  payor_signature VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE public.grid_docs(
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NULL,
  date DATE NULL,
  signature VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

CREATE TABLE public.measure_category(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

CREATE TABLE public.hospital_stage_log(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  usr_id INTEGER NULL,
  year INTEGER NULL,
  description VARCHAR(255) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

CREATE TABLE public.designations(
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NULL,
  shant_name VARCHAR(255) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL
);

INSERT INTO designations (title,shant_name) VALUES 
('Payor','payor'),
('Admin','hospital_admin'),
('CEO','hospital_ceo'),
('CMO','hospital_cmo'),
('CFO','hospital_cfo'),
('HPIP Quality','hospital_hpip_quality'),
('Health Plan CMO','health_plan_cmo'),
('Health Plan Oreceident','health_plan_precident'),
('Health Plan Contractor','health_plan_contractor'),
('Contractor','contractor');

ALTER TABLE users ADD COLUMN designation_id INTEGER NULL;

CREATE TABLE public.user_profile(
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  name VARCHAR(255) NULL,
  email VARCHAR(100) NULL,
  phone_number VARCHAR(100) NULL,
  address VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  deleted_at TIMESTAMP WITH TIME ZONE NULL
);

CREATE TABLE public.program_comment(
  id SERIAL PRIMARY KEY,
  program_type_id INTEGER NULL,
  usr_id INTEGER NULL,
  hospital_id INTEGER NULL,
  year INTEGER NULL,
  comment VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

CREATE TABLE public.program_type(
  id SERIAL PRIMARY KEY,
  type VARCHAR(100) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
);

INSERT INTO public.program_type (type) VALUES 
('Grid'),
('Program'),
('program Result');

CREATE TABLE public.submission_period(
  id SERIAL PRIMARY KEY,
  submission_date DATE NULL,
  quarter VARCHAR(100) NULL,
  year INTEGER NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

INSERT INTO public.submission_period (submission_date, quarter, year) VALUES 
('2024-05-31', 'Quarter 1', '2024'),
('2024-08-31', 'Quarter 2', '2024'),
('2024-11-30', 'Quarter 3', '2024'),
('2025-02-28', 'Quarter 4', '2024'),
('2025-02-28', 'Final', '2024');


ALTER TABLE program_result
CHANGE submission_period INTEGER NULL;

CREATE TABLE public.agreement(
  id SERIAL PRIMARY KEY,
  year INTEGER NOT NULL,
  hospital_id INTEGER NULL,
  hospital_admin_usr_id INTEGER NULL,
  hospital_admin_title VARCHAR(255) NUll,
  hospital_admin_signature_date TIMESTAMP NUll,
  payor_reviewer_usr_id INTEGER NULL,
  payor_reviewer_signature_date TIMESTAMP NUll,
  consultant_usr_id INTEGER NULL,
  consultant_signature_date TIMESTAMP NUll,
  payor_md_usr_id INTEGER NULL,
  payor_md_signature_date TIMESTAMP NUll,
  payor_contractor_usr_id INTEGER NULL,
  payor_contractor_signature_date TIMESTAMP NUll,
  payor_contractor_title VARCHAR(255) NUll,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

INSERT INTO public.agreement (year, hospital_id, hospital_usr_id, hospital_signature_date, created_by) VALUES 
('2024', '812', '30', '2024-02-22 12:18:38.403 +0530', 30);

CREATE TABLE public.blog(
  id SERIAL PRIMARY KEY,
  article VARCHAR(500) NULL,
  summary TEXT NULL,
  document VARCHAR(500) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

ALTER TABLE public.blog ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE NULL;
ALTER TABLE public.blog ADD COLUMN  deleted_at TIMESTAMP WITH TIME ZONE NULL;

CREATE TABLE public.blog_content(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NOT NULL,
  blog_id INTEGER NOT NULL,
  content TEXT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL
);

ALTER TABLE public.measure ADD COLUMN parent_id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE public.measure ADD COLUMN is_care_transitions INTEGER NOT NULL DEFAULT 0;
COMMENT ON COLUMN public.measure.is_care_transitions IS '0 => No, 1 => yes';

CREATE TABLE public.hospitalwise_consultant_list(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NOT NULL,
  consultant_id INTEGER NOT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  created_by INTEGER NULL
);

CREATE TABLE public.forget_password_log(
  id SERIAL PRIMARY KEY,
  usr_id INTEGER NOT NULL,
  token  VARCHAR(255) NULL,
  expire_at TIMESTAMP WITH TIME ZONE NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE TABLE public.login_log(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  ip_address VARCHAR ( 100 ) NULL,
  browser_name VARCHAR ( 100 ) NULL,
  device_name VARCHAR ( 100 ) NULL,
  operating_system VARCHAR ( 100 ) NULL,
  action INT NOT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
ALTER TABLE login_log ADD COLUMN location VARCHAR ( 500 ) NULL;
COMMENT ON COLUMN public.login_log.action IS '1 => Login, 2 => Logout';

CREATE TABLE public.activity_log(
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  api_name VARCHAR ( 100 ) NULL,
  request TEXT NULL,
  response VARCHAR ( 100 ) NULL,
  action INT NOT NULL,
  description VARCHAR ( 500 ) NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

COMMENT ON COLUMN public.activity_log.action IS '1 => Add, 2 => Update, 3 => View, 4 => Delete';

COMMENT ON COLUMN public.hospitals.hospital_type IS '1 => Excellus, 2 => Univera';


CREATE TABLE public.dollar_point(
  id SERIAL PRIMARY KEY,
  hospital_id INT NOT NULL,
  year INT NOT NULL,
  total_dollar_at_risk INT NULL,
  total_dollar_earned INT NULL,
  measure_dollar_at_risk INT NULL,
  measure_dollar_earned INT NULL,
  dollar_earned_per_patient INT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

ALTER TABLE users ADD COLUMN device_token TEXT NULL;

CREATE TABLE public.chat_system(
  id SERIAL PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  massage TEXT NULL,
  picture VARCHAR(255) NULL,
  read INTEGER NOT NULL DEFAULT 0,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
ALTER TABLE public.chat_system ADD COLUMN is_hospital INTEGER NOT NULL DEFAULT 0;
COMMENT ON COLUMN public.chat_system.is_hospital IS '0 => No, 1 => Yes';
ALTER TABLE public.chat_system ADD COLUMN hospital_usr_id INTEGER NOT NULL DEFAULT 0;
ALTER TABLE public.measure ADD COLUMN benchmark_target_type INTEGER NOT NULL DEFAULT 1;
COMMENT ON COLUMN public.measure.benchmark_target_type IS '1 => Percentage, 2 => Numeric';

CREATE TABLE public.token_log(
  id SERIAL PRIMARY KEY,
  usr_id INT NOT NULL,
  email  VARCHAR(255) NULL,
  password VARCHAR(255) NULL,
  token TEXT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE TABLE public.agreement_token_log(
  id SERIAL PRIMARY KEY,
  usr_id INT NOT NULL,
  agreement_id INT NOT NULL,
  designation_id INT NOT NULL,
  email  VARCHAR(255) NULL,
  token TEXT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE public.broadcast_massage(
  id SERIAL PRIMARY KEY,
  sender_id INT NOT NULL,
  receiver_id INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  massage TEXT NULL,
  is_hospital INTEGER NOT NULL DEFAULT 0,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE public.yearwise_hospital(
  id SERIAL PRIMARY KEY,
  hospital_id INT NOT NULL,
  year INT NOT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE TABLE public.exception_form(
  id SERIAL PRIMARY KEY,
  hospital_id INTEGER NULL,
  measure_id INTEGER NOT NULL,
  year INTEGER NULL,
  request_date DATE NULL,
  facility_name VARCHAR(255) NULL,
  contact VARCHAR(255) NULL,
  phone VARCHAR(100) NULL,
  measure_requested VARCHAR(100) NULL,
  aim_statement TEXT NULL,
  baseline_rate FLOAT NULL,
  target_rate FLOAT NULL,
  actual_rate FLOAT NULL,
  numerator FLOAT NULL,
  denominator FLOAT NULL,
  measure_period_form DATE NULL,
  measure_period_to DATE NULL,
  current_rate FLOAT NULL,
  current_numerator FLOAT NULL,
  current_denominator FLOAT NULL,
  timeframe VARCHAR(100) NULL,
  describe_facts TEXT NULL,
  interventions TEXT NULL,
  status INTEGER NOT NULL DEFAULT 1,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  created_by INTEGER NULL,
);