CREATE TABLE IF NOT EXISTS tenants (
id CHAR(36) PRIMARY KEY,
name VARCHAR(200) NOT NULL,
status VARCHAR(30) NOT NULL,
created_at DATETIME NOT NULL
);
CREATE TABLE IF NOT EXISTS users (
id CHAR(36) PRIMARY KEY,
email VARCHAR(320) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
status VARCHAR(30) NOT NULL,
created_at DATETIME NOT NULL,
UNIQUE KEY users_email_unique (email)
);
CREATE TABLE IF NOT EXISTS roles (
id CHAR(36) PRIMARY KEY,
code VARCHAR(60) NOT NULL,
name VARCHAR(100) NOT NULL,
UNIQUE KEY roles_code_unique (code)
);
CREATE TABLE IF NOT EXISTS permissions (
id CHAR(36) PRIMARY KEY,
code VARCHAR(100) NOT NULL,
name VARCHAR(150) NOT NULL,
UNIQUE KEY permissions_code_unique (code)
);
CREATE TABLE IF NOT EXISTS tenant_memberships (
id CHAR(36) PRIMARY KEY,
tenant_id CHAR(36) NOT NULL,
user_id CHAR(36) NOT NULL,
role_id CHAR(36) NOT NULL,
status VARCHAR(30) NOT NULL,
created_at DATETIME NOT NULL,
UNIQUE KEY memberships_tenant_user_unique (tenant_id, user_id),
CONSTRAINT memberships_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id),
CONSTRAINT memberships_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT memberships_role_fk FOREIGN KEY (role_id) REFERENCES roles(id)
);
CREATE TABLE IF NOT EXISTS refresh_tokens (
id CHAR(36) PRIMARY KEY,
user_id CHAR(36) NOT NULL,
tenant_id CHAR(36) NOT NULL,
token_hash CHAR(64) NOT NULL,
expires_at DATETIME NOT NULL,
created_at DATETIME NOT NULL,
revoked_at DATETIME NULL,
replaced_by_token_id CHAR(36) NULL,
UNIQUE KEY refresh_tokens_hash_unique (token_hash),
CONSTRAINT refresh_tokens_user_fk FOREIGN KEY (user_id) REFERENCES users(id),
CONSTRAINT refresh_tokens_tenant_fk FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
INSERT IGNORE INTO roles (id, code, name) VALUES
('00000000-0000-4000-8000-000000000001', 'owner', 'Inhaber'),
('00000000-0000-4000-8000-000000000002', 'office', 'Büro'),
('00000000-0000-4000-8000-000000000003', 'supervisor', 'Bauleitung'),
('00000000-0000-4000-8000-000000000004', 'technician', 'Monteur');
INSERT IGNORE INTO permissions (id, code, name) VALUES
('00000000-0000-4000-8000-000000000101', 'tenant.manage', 'Betrieb verwalten'),
('00000000-0000-4000-8000-000000000102', 'users.manage', 'Benutzer verwalten'),
('00000000-0000-4000-8000-000000000103', 'reports.create', 'Nachweise erstellen'),
('00000000-0000-4000-8000-000000000104', 'reports.read_all', 'Alle Nachweise lesen');
