------------------------------------------------------------------------------
-- 0. User Creation
--    パスワードは環境に合わせて変更してください。
------------------------------------------------------------------------------

DROP USER demo CASCADE;

CREATE USER demo IDENTIFIED BY change_password
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

GRANT CREATE SESSION,
      CREATE TABLE,
      CREATE VIEW,
      CREATE PROCEDURE,
      CREATE SEQUENCE,
      CREATE TRIGGER,
      CREATE TYPE
TO demo;

DROP END USER nsato;
DROP END USER mtanaka;
DROP END USER myamamoto;

CREATE END USER nsato IDENTIFIED BY change_password;
CREATE END USER mtanaka IDENTIFIED BY change_password;
CREATE END USER myamamoto IDENTIFIED BY change_password;


------------------------------------------------------------------------------
-- 1. TABLES
------------------------------------------------------------------------------

DROP TABLE demo.exam_results;
DROP TABLE demo.patients;
DROP TABLE demo.healthcare_workers;

CREATE TABLE demo.healthcare_workers (
  worker_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  username     VARCHAR2(50) NOT NULL,
  firstname    VARCHAR2(100) NOT NULL,
  lastname     VARCHAR2(100) NOT NULL,
  worker_name  VARCHAR2(100) NOT NULL,
  worker_role  VARCHAR2(20) NOT NULL,
  department   VARCHAR2(100) NOT NULL,
  email        VARCHAR2(200),
  created_at   TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TABLE demo.patients (
  patient_id           NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  healthcare_worker_id NUMBER NOT NULL,
  patient_name         VARCHAR2(100) NOT NULL,
  sex                  CHAR(1),
  birth_date           DATE NOT NULL,
  phone                VARCHAR2(30),
  prefecture           VARCHAR2(50),
  created_at           TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

CREATE TABLE demo.exam_results (
  exam_id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  patient_id      NUMBER NOT NULL,
  exam_date       DATE NOT NULL,
  height_cm       NUMBER(5,1) NOT NULL,
  weight_kg       NUMBER(5,1) NOT NULL,
  systolic_bp     NUMBER(4) NOT NULL,
  diastolic_bp    NUMBER(4) NOT NULL,
  hba1c           NUMBER(4,2) NOT NULL,
  ldl             NUMBER(5,1) NOT NULL,
  triglyceride    NUMBER(6,1) NOT NULL,
  fasting_glucose NUMBER(5,1) NOT NULL,
  smoking_status  VARCHAR2(20),
  notes           VARCHAR2(4000),
  bmi             NUMBER(4,1)
                    GENERATED ALWAYS AS
                    (ROUND(weight_kg / POWER(height_cm / 100, 2), 1))
                    VIRTUAL
);


------------------------------------------------------------------------------
-- 2. SAMPLE DATA
------------------------------------------------------------------------------

INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (1, 'nsato',      'naoto',    'sato',      '佐藤 直人', 'DOCTOR',  '内科',       'sato.naoto@hospital.example');
INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (2, 'rsuzuki',    'rina',     'suzuki',    '鈴木 里奈', 'DOCTOR',  '糖尿病内科', 'suzuki.rina@hospital.example');
INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (3, 'mtanaka',    'megumi',   'tanaka',    '田中 恵',   'STAFF',   '医事課',     'tanaka.megumi@hospital.example');
INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (4, 'ykondo',     'yuki',     'kondo',     '近藤 由紀', 'STAFF',   '受付',       'kondo.yuki@hospital.example');
INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (5, 'myamamoto',  'makoto',   'yamamoto',  '山本 誠',   'STUDENT', '医学部実習', 'yamamoto.makoto@hospital.example');
INSERT INTO demo.healthcare_workers (worker_id, username, firstname, lastname, worker_name, worker_role, department, email) VALUES (6, 'ytakahashi', 'yu',       'takahashi', '高橋 悠',   'STUDENT', '医学部実習', 'takahashi.yu@hospital.example');

INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (1, 1, '山田 太郎',     'M', DATE '1982-04-12', '090-1111-0001', '東京都');
INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (2, 1, '田中 花子',     'F', DATE '1978-09-03', '090-1111-0002', '神奈川県');
INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (3, 2, '佐々木 一郎',   'M', DATE '1990-01-21', '090-1111-0003', '埼玉県');
INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (4, 2, '小林 美咲',     'F', DATE '1969-11-30', '090-1111-0004', '千葉県');
INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (5, 5, '中村 健',       'M', DATE '1988-06-18', '090-1111-0005', '東京都');
INSERT INTO demo.patients (patient_id, healthcare_worker_id, patient_name, sex, birth_date, phone, prefecture) VALUES (6, 6, '渡辺 愛',       'F', DATE '1995-02-14', '090-1111-0006', '大阪府');

-- 山田 太郎（3件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (1, DATE '2025-11-10', 170.0, 84.0, 142, 90, 6.1, 158.0, 220.0, 112.0, 'Former', '前回よりやや悪化。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (1, DATE '2026-02-10', 170.0, 85.5, 145, 92, 6.3, 160.0, 230.0, 115.0, 'Former', '生活習慣改善指導。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (1, DATE '2026-05-10', 170.0, 86.5, 148, 94, 6.5, 162.0, 245.0, 118.0, 'Former', '腹囲増加。再検査推奨。');

-- 田中 花子（2件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (2, DATE '2025-11-10', 158.0, 59.0, 132, 84, 5.7, 142.0, 176.0, 99.0, 'Never', '軽度高値あり。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (2, DATE '2026-05-10', 158.0, 60.0, 136, 86, 5.8, 148.0, 182.0, 102.0, 'Never', '経過観察。生活習慣改善指導。');

-- 佐々木 一郎（2件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (3, DATE '2025-11-12', 176.0, 69.0, 124, 80, 5.5, 108.0, 101.0, 94.0, 'Never', '前回も概ね正常。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (3, DATE '2026-05-11', 176.0, 68.0, 122, 78, 5.4, 104.0, 98.0, 92.0, 'Never', '異常所見なし。');

-- 小林 美咲（3件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (4, DATE '2025-11-12', 155.0, 63.0, 130, 82, 6.8, 168.0, 198.0, 138.0, 'Current', '高血糖傾向。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (4, DATE '2026-02-12', 155.0, 63.5, 131, 83, 7.0, 169.0, 205.0, 142.0, 'Current', '糖尿病精査推奨。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (4, DATE '2026-05-11', 155.0, 64.0, 132, 84, 7.2, 170.0, 210.0, 146.0, 'Current', 'HbA1c高値。糖尿病精査推奨。');

-- 中村 健（2件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (5, DATE '2025-11-13', 172.0, 79.0, 126, 80, 5.8, 134.0, 205.0, 101.0, 'Former', '中性脂肪高め。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (5, DATE '2026-05-12', 172.0, 81.0, 128, 82, 5.9, 138.0, 230.0, 105.0, 'Former', '運動・食事指導対象。');

-- 渡辺 愛（2件）
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (6, DATE '2025-11-14', 160.0, 54.0, 118, 76, 5.3, 96.0, 88.0, 90.0, 'Never', '概ね正常。');
INSERT INTO demo.exam_results (patient_id, exam_date, height_cm, weight_kg, systolic_bp, diastolic_bp, hba1c, ldl, triglyceride, fasting_glucose, smoking_status, notes) VALUES (6, DATE '2026-05-13', 160.0, 55.0, 120, 78, 5.4, 100.0, 92.0, 92.0, 'Never', '経過観察。');

COMMIT;

------------------------------------------------------------------------------
-- 3. PACKAGE / END USER CONTEXT
------------------------------------------------------------------------------

DROP DATA GRANT demo.hospital_ctx_read_grant;
DROP END USER CONTEXT demo.hospital_ctx;
DROP PACKAGE demo.ctx_pkg;

CREATE OR REPLACE PACKAGE demo.ctx_pkg AS
  PROCEDURE init_user_context;
END;
/

CREATE OR REPLACE PACKAGE BODY demo.ctx_pkg AS
  PROCEDURE init_user_context IS
    sql_stmt VARCHAR2(4000);
  BEGIN
    sql_stmt := '
      UPDATE END_USER_CONTEXT t
      SET t.CONTEXT.healthcare_worker_id = (
        SELECT hw.worker_id
        FROM demo.healthcare_workers hw
        WHERE UPPER(hw.username) = UPPER(ORA_END_USER_CONTEXT.username)
      )
      WHERE owner = ''DEMO''
      AND name  = ''HOSPITAL_CTX''
    ';
    EXECUTE IMMEDIATE sql_stmt;
  END;
END;
/

CREATE OR REPLACE END USER CONTEXT demo.hospital_ctx USING JSON SCHEMA '{
  "type":"object",
  "properties":{
    "healthcare_worker_id":{
      "type":"integer",
      "o:onFirstRead":"DEMO.ctx_pkg.init_user_context"
    }
  }
}';


------------------------------------------------------------------------------
-- 4. DATA ROLES / ROLE GRANTS
------------------------------------------------------------------------------

DROP ROLE hospital_context_admin;
DROP ROLE db_role;

CREATE OR REPLACE DATA ROLE doctor_role;
CREATE OR REPLACE DATA ROLE staff_role;
CREATE OR REPLACE DATA ROLE student_role;

GRANT DATA ROLE doctor_role TO nsato;
GRANT DATA ROLE staff_role TO mtanaka;
GRANT DATA ROLE student_role TO myamamoto;

CREATE ROLE db_role;
GRANT CREATE SESSION TO db_role;
GRANT db_role TO doctor_role, staff_role, student_role;

CREATE ROLE hospital_context_admin;
GRANT EXECUTE ON demo.ctx_pkg TO hospital_context_admin;
GRANT hospital_context_admin TO doctor_role, staff_role, student_role;

GRANT UPDATE ANY END USER CONTEXT TO demo;

------------------------------------------------------------------------------
-- 5. DATA GRANTS
------------------------------------------------------------------------------

CREATE OR REPLACE DATA GRANT demo.hospital_ctx_read_grant
  AS SELECT
  ON SYS.END_USER_CONTEXT
  WHERE owner = 'DEMO'
    AND name = 'HOSPITAL_CTX'
  TO doctor_role, staff_role, student_role;

------------------------------------------------------------------------------
-- Doctor : 全テーブル参照可能
------------------------------------------------------------------------------

CREATE OR REPLACE DATA GRANT lc_doctor_workers_grant
AS SELECT
ON demo.healthcare_workers
TO doctor_role;

CREATE OR REPLACE DATA GRANT lc_doctor_patients_grant
AS SELECT
ON demo.patients
TO doctor_role;

CREATE OR REPLACE DATA GRANT lc_doctor_exam_results_grant
AS SELECT
ON demo.exam_results
TO doctor_role;

------------------------------------------------------------------------------
-- Staff : 自分の情報 + 患者一覧
------------------------------------------------------------------------------

CREATE OR REPLACE DATA GRANT lc_staff_workers_self_grant
AS SELECT
ON demo.healthcare_workers
WHERE LOWER(username) = LOWER(ORA_END_USER_CONTEXT.username)
TO staff_role;

CREATE OR REPLACE DATA GRANT lc_staff_patients_grant
AS SELECT
ON demo.patients
TO staff_role;


------------------------------------------------------------------------------
-- Student : 自分の情報 + 自分の担当患者 + 検査結果
------------------------------------------------------------------------------

CREATE OR REPLACE DATA GRANT lc_student_workers_self_grant
AS SELECT
ON demo.healthcare_workers
WHERE LOWER(username) = LOWER(ORA_END_USER_CONTEXT.username)
TO student_role;

CREATE OR REPLACE DATA GRANT lc_student_patients_grant
AS SELECT
ON demo.patients
WHERE healthcare_worker_id =
      ORA_END_USER_CONTEXT.demo.hospital_ctx.healthcare_worker_id
TO student_role;

CREATE OR REPLACE DATA GRANT lc_student_exam_results_grant
AS SELECT
ON demo.exam_results
WHERE patient_id IN (
    SELECT p.patient_id
      FROM demo.patients p
     WHERE p.healthcare_worker_id =
           ORA_END_USER_CONTEXT.demo.hospital_ctx.healthcare_worker_id
)
TO student_role;