CREATE TABLE members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_no VARCHAR(32) UNIQUE NULL,
 first_name VARCHAR(100) NOT NULL,
 last_name VARCHAR(100) NOT NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(60) NULL,
 birth_date DATE NULL,
 address VARCHAR(255) NULL,
 guardian_name VARCHAR(190) NULL,
 guardian_email VARCHAR(190) NULL,
 member_kind ENUM('tag','igazolt_sportolo','partolo','tiszteletbeli') NOT NULL DEFAULT 'tag',
 status ENUM('jelentkezo','aktiv','szunetelo','megszunt') NOT NULL DEFAULT 'aktiv',
 joined_at DATE NULL,
 ended_at DATE NULL,
 notes TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_name(last_name,first_name), INDEX idx_email(email), INDEX idx_status(status)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED UNIQUE NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','member') NOT NULL DEFAULT 'member',
 active TINYINT(1) NOT NULL DEFAULT 1,
 last_login_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE departments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 sport VARCHAR(120) NULL,
 leader VARCHAR(190) NULL,
 description TEXT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE member_departments (
 member_id BIGINT UNSIGNED NOT NULL,
 department_id BIGINT UNSIGNED NOT NULL,
 since_date DATE NULL,
 PRIMARY KEY(member_id,department_id),
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE charges (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 due_date DATE NOT NULL,
 period_label VARCHAR(80) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 INDEX idx_due(due_date)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE payments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 charge_id BIGINT UNSIGNED NULL,
 amount DECIMAL(12,2) NOT NULL,
 paid_at DATE NOT NULL,
 method ENUM('atutalas','keszpenz','kartya','egyeb') NOT NULL DEFAULT 'atutalas',
 reference VARCHAR(190) NULL,
 note VARCHAR(255) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(charge_id) REFERENCES charges(id) ON DELETE SET NULL,
 UNIQUE KEY unique_reference(reference)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE documents (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL,
 kind ENUM('szervezeti','elfogadando','feltoltendo','tagi') NOT NULL DEFAULT 'szervezeti',
 member_id BIGINT UNSIGNED NULL,
 file_name VARCHAR(255) NULL,
 disk_name VARCHAR(100) NULL,
 mime_type VARCHAR(100) NULL,
 external_url VARCHAR(500) NULL,
 due_date DATE NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE document_actions (
 document_id BIGINT UNSIGNED NOT NULL,
 member_id BIGINT UNSIGNED NOT NULL,
 action_type ENUM('elfogadta','feltoltotte') NOT NULL,
 action_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(document_id,member_id,action_type),
 FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL,
 starts_at DATETIME NOT NULL,
 ends_at DATETIME NULL,
 place VARCHAR(190) NULL,
 department_id BIGINT UNSIGNED NULL,
 capacity INT NULL,
 description TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL,
 INDEX idx_start(starts_at)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE event_registrations (
 event_id BIGINT UNSIGNED NOT NULL,
 member_id BIGINT UNSIGNED NOT NULL,
 registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 attended TINYINT(1) NOT NULL DEFAULT 0,
 PRIMARY KEY(event_id,member_id),
 FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE surveys (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL,
 question TEXT NOT NULL,
 options_json JSON NOT NULL,
 anonymous TINYINT(1) NOT NULL DEFAULT 0,
 closes_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE survey_responses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 survey_id BIGINT UNSIGNED NOT NULL,
 member_id BIGINT UNSIGNED NULL,
 answer_index INT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY unique_member_answer(survey_id,member_id),
 FOREIGN KEY(survey_id) REFERENCES surveys(id) ON DELETE CASCADE,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE survey_participation (
 survey_id BIGINT UNSIGNED NOT NULL,
 member_id BIGINT UNSIGNED NOT NULL,
 answered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(survey_id,member_id),
 FOREIGN KEY(survey_id) REFERENCES surveys(id) ON DELETE CASCADE,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE meetings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL,
 meeting_at DATETIME NOT NULL,
 place VARCHAR(190) NULL,
 agenda TEXT NULL,
 minutes TEXT NULL,
 status ENUM('tervezet','meghirdetve','lezart') NOT NULL DEFAULT 'tervezet'
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE resolutions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 meeting_id BIGINT UNSIGNED NOT NULL,
 number VARCHAR(60) NOT NULL,
 title VARCHAR(190) NOT NULL,
 text_body TEXT NOT NULL,
 yes_count INT NOT NULL DEFAULT 0,
 no_count INT NOT NULL DEFAULT 0,
 abstain_count INT NOT NULL DEFAULT 0,
 decided_at DATETIME NULL,
 FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE equipment (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 asset_tag VARCHAR(60) UNIQUE NULL,
 name VARCHAR(190) NOT NULL,
 category VARCHAR(100) NULL,
 quantity INT NOT NULL DEFAULT 1,
 condition_note VARCHAR(190) NULL,
 location VARCHAR(190) NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE equipment_loans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 equipment_id BIGINT UNSIGNED NOT NULL,
 member_id BIGINT UNSIGNED NOT NULL,
 issued_at DATE NOT NULL,
 due_at DATE NULL,
 returned_at DATE NULL,
 note VARCHAR(255) NULL,
 FOREIGN KEY(equipment_id) REFERENCES equipment(id) ON DELETE CASCADE,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE crm_notes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NOT NULL,
 author_id BIGINT UNSIGNED NULL,
 kind ENUM('jegyzet','hivas','feladat') NOT NULL DEFAULT 'jegyzet',
 body TEXT NOT NULL,
 due_at DATE NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE CASCADE,
 FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE results (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 member_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NULL,
 competition VARCHAR(190) NOT NULL,
 result_text VARCHAR(190) NOT NULL,
 achieved_at DATE NOT NULL,
 category VARCHAR(100) NULL,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL,
 FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE organizations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(190) NOT NULL,
 contact_name VARCHAR(190) NULL,
 email VARCHAR(190) NULL,
 phone VARCHAR(60) NULL,
 notes TEXT NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL,
 body TEXT NOT NULL,
 recipient_scope ENUM('mindenki','aktiv','egy_tag') NOT NULL DEFAULT 'mindenki',
 member_id BIGINT UNSIGNED NULL,
 sent_at DATETIME NULL,
 sent_count INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(member_id) REFERENCES members(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE pages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 slug VARCHAR(120) NOT NULL UNIQUE,
 title VARCHAR(190) NOT NULL,
 body TEXT NOT NULL,
 published TINYINT(1) NOT NULL DEFAULT 0,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE audit_log (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL,
 action VARCHAR(60) NOT NULL,
 entity VARCHAR(70) NOT NULL,
 entity_id BIGINT UNSIGNED NULL,
 details_json JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_time(created_at),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email VARCHAR(190) NOT NULL,
 ip_hash CHAR(64) NOT NULL,
 succeeded TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_attempt(email,created_at)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;