CREATE DATABASE IF NOT EXISTS medical_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE medical_system;

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    phone VARCHAR(20),
    location VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE doctors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    specialty VARCHAR(100),
    department_id INT,
    license_number VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE patients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    birth_date DATE,
    phone VARCHAR(20),
    id_card VARCHAR(18) UNIQUE,
    address TEXT,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    specification VARCHAR(100),
    unit VARCHAR(20),
    manufacturer VARCHAR(200),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE diagnoses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE medical_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    department_id INT NOT NULL,
    visit_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    chief_complaint TEXT,
    present_illness TEXT,
    physical_examination TEXT,
    diagnosis TEXT,
    treatment_plan TEXT,
    prescription TEXT,
    notes TEXT,
    status ENUM('待诊', '就诊中', '已完成') DEFAULT '待诊',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE prescriptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    record_id INT NOT NULL,
    medication_id INT NOT NULL,
    dosage VARCHAR(100),
    frequency VARCHAR(100),
    duration VARCHAR(100),
    instructions TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (record_id) REFERENCES medical_records(id),
    FOREIGN KEY (medication_id) REFERENCES medications(id)
);

INSERT INTO departments (name, description, phone, location) VALUES
('内科', '内科疾病诊治', '010-12345678', '1楼101室'),
('外科', '外科手术治疗', '010-12345679', '2楼201室'),
('儿科', '儿童疾病专科', '010-12345680', '3楼301室'),
('妇科', '妇科疾病诊治', '010-12345681', '4楼401室'),
('急诊科', '急诊抢救治疗', '010-12345682', '1楼急诊大厅');

INSERT INTO doctors (name, gender, phone, email, specialty, department_id, license_number) VALUES
('张医生', '男', '13800138001', 'zhang@hospital.com', '心血管内科', 1, 'DOC001'),
('李医生', '女', '13800138002', 'li@hospital.com', '普通外科', 2, 'DOC002'),
('王医生', '女', '13800138003', 'wang@hospital.com', '儿科急症', 3, 'DOC003'),
('赵医生', '男', '13800138004', 'zhao@hospital.com', '妇科肿瘤', 4, 'DOC004'),
('刘医生', '男', '13800138005', 'liu@hospital.com', '急诊医学', 5, 'DOC005');

INSERT INTO medications (name, specification, unit, manufacturer, price) VALUES
('阿莫西林胶囊', '0.25g*24粒', '盒', '华北制药', 15.80),
('布洛芬缓释胶囊', '0.3g*20粒', '盒', '中美天津史克', 28.50),
('甲硝唑片', '0.2g*100片', '瓶', '石药集团', 12.30),
('头孢克肟胶囊', '0.1g*12粒', '盒', '齐鲁制药', 45.60),
('复方甘草片', '50片', '瓶', '太极集团', 8.90);

INSERT INTO diagnoses (code, name, category, description) VALUES
('J00.9', '急性鼻咽炎(感冒)', '内科', '上呼吸道病毒感染，常见症状包括鼻塞、流涕、咽痛等'),
('K59.1', '功能性便秘', '内科', '排便困难或排便次数减少，无器质性病变'),
('I10', '原发性高血压', '内科', '血压持续升高，收缩压≥140mmHg或舒张压≥90mmHg'),
('K35.9', '急性阑尾炎', '外科', '阑尾急性炎症，常需要手术治疗'),
('S72.0', '股骨颈骨折', '外科', '髋部外伤常见类型，多发生于老年人'),
('N92.0', '月经过多', '妇科', '月经量明显增多或经期延长'),
('A09', '感染性腹泻', '儿科', '多种病原体引起的腹泻，常见于婴幼儿'),
('J06.9', '急性上呼吸道感染', '儿科', '儿童常见疾病，包括咽炎、喉炎等'),
('H10.9', '结膜炎', '眼科', '结膜充血、分泌物增多'),
('L30.9', '皮炎', '皮肤科', '皮肤炎性反应，表现为红斑、瘙痒等');