104 lines
No EOL
3.4 KiB
SQL
104 lines
No EOL
3.4 KiB
SQL
-- Migration: 001_baseline
|
|
-- Description: Initial database schema
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
display_name VARCHAR(120) NOT NULL,
|
|
phone_encrypted TEXT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS help_requests (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
requester_id BIGINT NOT NULL,
|
|
title VARCHAR(180) NOT NULL,
|
|
description TEXT NOT NULL,
|
|
value_chf DECIMAL(10,2) NOT NULL,
|
|
status ENUM('open','negotiating','agreed','completed','cancelled') DEFAULT 'open',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (requester_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS offers (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
request_id BIGINT NOT NULL,
|
|
helper_id BIGINT NOT NULL,
|
|
amount_chf DECIMAL(10,2) NOT NULL,
|
|
message TEXT NULL,
|
|
status ENUM('pending','countered','accepted','rejected') DEFAULT 'pending',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (request_id) REFERENCES help_requests(id),
|
|
FOREIGN KEY (helper_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS negotiations (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
offer_id BIGINT NOT NULL,
|
|
sender_id BIGINT NOT NULL,
|
|
amount_chf DECIMAL(10,2) NOT NULL,
|
|
message TEXT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (offer_id) REFERENCES offers(id),
|
|
FOREIGN KEY (sender_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS deals (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
request_id BIGINT NOT NULL,
|
|
offer_id BIGINT NOT NULL,
|
|
agreed_amount_chf DECIMAL(10,2) NOT NULL,
|
|
agreed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
completed_at TIMESTAMP NULL,
|
|
FOREIGN KEY (request_id) REFERENCES help_requests(id),
|
|
FOREIGN KEY (offer_id) REFERENCES offers(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS contact_exchange_requests (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
deal_id BIGINT NOT NULL,
|
|
requester_id BIGINT NOT NULL,
|
|
target_id BIGINT NOT NULL,
|
|
accepted BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (deal_id) REFERENCES deals(id),
|
|
FOREIGN KEY (requester_id) REFERENCES users(id),
|
|
FOREIGN KEY (target_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS addresses (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id BIGINT NOT NULL,
|
|
address_encrypted TEXT NOT NULL,
|
|
postal_verified_at TIMESTAMP NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS address_change_requests (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
user_id BIGINT NOT NULL,
|
|
new_address_encrypted TEXT NOT NULL,
|
|
verification_code_hash VARCHAR(255) NOT NULL,
|
|
status ENUM('pending_letter','verified','expired','rejected') DEFAULT 'pending_letter',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
verified_at TIMESTAMP NULL,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
|
deal_id BIGINT NOT NULL,
|
|
reviewer_id BIGINT NOT NULL,
|
|
reviewee_id BIGINT NOT NULL,
|
|
rating TINYINT NOT NULL,
|
|
comment TEXT NULL,
|
|
earliest_prompt_at TIMESTAMP NOT NULL,
|
|
latest_prompt_at TIMESTAMP NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (deal_id) REFERENCES deals(id),
|
|
FOREIGN KEY (reviewer_id) REFERENCES users(id),
|
|
FOREIGN KEY (reviewee_id) REFERENCES users(id),
|
|
CHECK (rating BETWEEN 1 AND 5)
|
|
); |