USE `sample`;

--
-- 調整結構
--
/*
ALTER TABLE employee_roles
ADD CONSTRAINT fk_er_employee
FOREIGN KEY (employee_id)
REFERENCES EMPLOYEES(employee_id)
ON DELETE CASCADE;

ALTER TABLE employee_roles
ADD CONSTRAINT fk_er_role
FOREIGN KEY (role_id)
REFERENCES roles(role_id)
ON DELETE CASCADE;

ALTER TABLE employee_roles
ADD UNIQUE KEY uniq_employee_role (employee_id, role_id);

ALTER TABLE employee_roles
ADD INDEX idx_employee (employee_id),
ADD INDEX idx_role (role_id);

ALTER TABLE roles
ADD COLUMN role_name VARCHAR(50) NULL AFTER role_key,
ADD COLUMN is_system_role TINYINT(1) DEFAULT 1 AFTER role_name;

ALTER TABLE authority ENGINE=InnoDB;
*/

--
-- 預設登入帳號密碼
--
TRUNCATE TABLE authority;
INSERT INTO authority (username,password,role,location) VALUES
('admin','{hash_admin}',255,9),
('supervisor','{hash_supervisor}',100,9),
('doctor','{hash_doctor}',90,0),
('nurse','{hash_nurse}',70,0),
('pharmacist','{hash_pharmacist}',60,0),
('finance','{hash_finance}',40,0),
('hr','{hash_hr}',30,0);

--
-- 預設角色名稱
--
TRUNCATE TABLE roles;
INSERT INTO roles (role_key, role_name, is_system_role) VALUES
('system_admin','系統管理員',1),
('medical_supervisor','醫務主管',1),
('doctor','醫師',1),
('nurse','護理師',1),
('pharmacist','藥師',1),
('finance','財務',1),
('hr','人事',1);

--
-- 預設部門
--
TRUNCATE TABLE DEPARTMENTS;
INSERT INTO DEPARTMENTS
(name,en_name,parent_dept,manager_id,extension)
VALUES
('系統部','system',0,NULL,'100'),
('醫療部','medical',0,NULL,'200'),
('護理部','nursing',0,NULL,'300'),
('藥劑部','pharmacy',0,NULL,'400'),
('財務部','finance',0,NULL,'500'),
('人事部','hr',0,NULL,'600');

--
-- 預設人員資料
--
TRUNCATE TABLE EMPLOYEES;
INSERT INTO EMPLOYEES (
login_id,cname,phonetic,pinyin,ename,
gender,birth_date,nationality,marital_status,blood_type,
national_id_type,national_id,
mobile_phone,home_phone,work_phone,
email,
address_country,address_city,address_district,address_postcode,address_detail,
education_level,graduated_from,major,graduation_year,
work_experience,
emergency_name,emergency_relation,emergency_phone,
hire_date,jobtitle_id,department_id
)
VALUES
(1,'系統管理員','','','Admin',
'M','1980-01-01','台灣','未婚','O',
'身分證','A100000001',
'0911000001','','',
'admin@clinic.com',
'台灣','台北市','中正區','100','系統部辦公室',
'學士','台灣大學','資訊工程',2002,
'系統維運管理',
'王小明','朋友','0911999991',
'2025-01-01',4,1),

(2,'醫務主管','','','Supervisor',
'M','1978-05-10','台灣','已婚','A',
'身分證','A100000002',
'0911000002','','',
'supervisor@clinic.com',
'台灣','台北市','中山區','104','醫療行政辦公室',
'碩士','陽明交通大學','醫務管理',2004,
'醫療管理經驗',
'李小華','配偶','0911999992',
'2025-01-01',1,2),

(3,'王醫師','','','Doctor',
'M','1985-03-15','台灣','已婚','B',
'身分證','A100000003',
'0911000003','','',
'doctor@clinic.com',
'台灣','台北市','大安區','106','門診醫療部',
'博士','中國醫藥大學','中醫學',2012,
'專長內科',
'林小美','配偶','0911999993',
'2025-01-01',1,2),

(4,'林護理師','','','Nurse',
'F','1990-07-20','台灣','未婚','O',
'身分證','A100000004',
'0911000004','','',
'nurse@clinic.com',
'台灣','台北市','萬華區','108','護理部',
'學士','長庚科技大學','護理',2013,
'臨床護理',
'張小華','母親','0911999994',
'2025-01-01',2,3),

(5,'張藥師','','','Pharmacist',
'F','1988-09-10','台灣','未婚','AB',
'身分證','A100000005',
'0911000005','','',
'pharmacist@clinic.com',
'台灣','台北市','士林區','111','藥劑部',
'碩士','台北醫學大學','藥學',2011,
'中藥調劑',
'陳小華','父親','0911999995',
'2025-01-01',3,4),

(6,'陳財務','','','Finance',
'F','1982-02-02','台灣','已婚','A',
'身分證','A100000006',
'0911000006','','',
'finance@clinic.com',
'台灣','台北市','信義區','110','財務部',
'學士','政治大學','會計',2005,
'帳務處理',
'吳小華','配偶','0911999996',
'2025-01-01',4,5),

(7,'黃人事','','','HR',
'F','1983-11-11','台灣','已婚','B',
'身分證','A100000007',
'0911000007','','',
'hr@clinic.com',
'台灣','台北市','內湖區','114','人事部',
'學士','輔仁大學','人資管理',2006,
'人事管理',
'鄭小華','配偶','0911999997',
'2025-01-01',4,6);

--
-- 預設人員角設對應
--
TRUNCATE TABLE employee_roles;
INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='system_admin'
WHERE e.login_id=1;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='medical_supervisor'
WHERE e.login_id=2;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='doctor'
WHERE e.login_id=3;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='nurse'
WHERE e.login_id=4;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='pharmacist'
WHERE e.login_id=5;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='finance'
WHERE e.login_id=6;

INSERT INTO employee_roles (employee_id, role_id)
SELECT e.employee_id, r.role_id
FROM EMPLOYEES e
JOIN roles r ON r.role_key='hr'
WHERE e.login_id=7;

--
-- 預設職稱
--
TRUNCATE TABLE JOBTITLES;
INSERT INTO JOBTITLES
(position_id, title, en_title, phonetic, pinyin, category, salary_range)
VALUES
(1,'系統管理員','System Administrator','ㄒㄊㄍㄌㄩ','twexm','技術','依公司規定'),
(2,'醫務主管','Medical Supervisor','ㄧㄨㄓㄍ','uj5e','醫療','依公司規定'),
(3,'醫師','Doctor','ㄧㄕ','ug','醫療','依公司規定'),
(4,'護士','Nurse','ㄏㄕ','cg','醫療','依公司規定'),
(5,'藥劑師','Pharmacist','ㄧㄐㄕ','urg','醫療','依公司規定'),
(6,'財務專員','Finance Officer','ㄘㄨㄓㄩ','hj5m','行政','依公司規定'),
(7,'人事專員','HR Officer','ㄖㄕㄓㄩ','bg5m','行政','依公司規定'),
(8,'職員','Staff','ㄓㄩ','5m','行政','依公司規定');

--
-- 預設人員部門、職稱及生效日期
--
TRUNCATE TABLE EMPLOYEE_POSITIONS;
INSERT INTO EMPLOYEE_POSITIONS
(employee_id, department_id, position_id, start_date)
SELECT employee_id, department_id, jobtitle_id, '2025-01-01'
FROM EMPLOYEES;

UPDATE EMPLOYEES SET jobtitle_id = 1 WHERE login_id = 1; -- admin
UPDATE EMPLOYEES SET jobtitle_id = 2 WHERE login_id = 2; -- supervisor
UPDATE EMPLOYEES SET jobtitle_id = 3 WHERE login_id = 3; -- doctor
UPDATE EMPLOYEES SET jobtitle_id = 4 WHERE login_id = 4; -- nurse
UPDATE EMPLOYEES SET jobtitle_id = 5 WHERE login_id = 5; -- pharmacist
UPDATE EMPLOYEES SET jobtitle_id = 6 WHERE login_id = 6; -- finance
UPDATE EMPLOYEES SET jobtitle_id = 7 WHERE login_id = 7; -- hr

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 1 LIMIT 1)
WHERE name = '系統部';

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 2 LIMIT 1)
WHERE name = '醫療部';

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 4 LIMIT 1)
WHERE name = '護理部';

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 5 LIMIT 1)
WHERE name = '藥劑部';

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 6 LIMIT 1)
WHERE name = '財務部';

UPDATE DEPARTMENTS
SET manager_id = (SELECT employee_id FROM EMPLOYEES WHERE jobtitle_id = 7 LIMIT 1)
WHERE name = '人事部';


