261 lines
11 KiB
MySQL
261 lines
11 KiB
MySQL
|
|
||
|
|
CREATE TABLE lesson_package_types (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
name VARCHAR NOT NULL,
|
||
|
|
description VARCHAR NOT NULL,
|
||
|
|
package_type VARCHAR(64) NOT NULL,
|
||
|
|
price NUMERIC(8,2),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE lesson_bookings (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
user_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
active BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
accepter_id VARCHAR(64) REFERENCES users(id),
|
||
|
|
canceler_id VARCHAR(64) REFERENCES users(id),
|
||
|
|
lesson_type VARCHAR(64) NOT NULL,
|
||
|
|
recurring BOOLEAN NOT NULL,
|
||
|
|
lesson_length INTEGER NOT NULL,
|
||
|
|
payment_style VARCHAR(64) NOT NULL,
|
||
|
|
description VARCHAR,
|
||
|
|
booked_price NUMERIC(8,2) NOT NULL,
|
||
|
|
teacher_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
card_presumed_ok BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sent_notices BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
status VARCHAR,
|
||
|
|
cancel_message VARCHAR,
|
||
|
|
user_decremented BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE charges (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
amount_in_cents INTEGER NOT NULL,
|
||
|
|
fee_in_cents INTEGER NOT NULL DEFAULT 0,
|
||
|
|
type VARCHAR(64) NOT NULL,
|
||
|
|
sent_billing_notices BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sent_billing_notices_at TIMESTAMP,
|
||
|
|
last_billing_attempt_at TIMESTAMP,
|
||
|
|
billed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
billed_at TIMESTAMP,
|
||
|
|
post_processed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
post_processed_at TIMESTAMP,
|
||
|
|
billing_error_reason VARCHAR,
|
||
|
|
billing_error_detail VARCHAR,
|
||
|
|
billing_should_retry BOOLEAN NOT NULL DEFAULT TRUE ,
|
||
|
|
billing_attempts INTEGER NOT NULL DEFAULT 0,
|
||
|
|
stripe_charge_id VARCHAR(200),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE lesson_package_purchases (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
lesson_package_type_id VARCHAR(64) REFERENCES lesson_package_types(id) NOT NULL,
|
||
|
|
user_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
teacher_id VARCHAR(64) REFERENCES users(id),
|
||
|
|
price NUMERIC(8,2),
|
||
|
|
recurring BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
year INTEGER,
|
||
|
|
month INTEGER,
|
||
|
|
charge_id VARCHAR(64) REFERENCES charges(id),
|
||
|
|
lesson_booking_id VARCHAR(64) REFERENCES lesson_bookings(id),
|
||
|
|
sent_notices BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sent_notices_at TIMESTAMP,
|
||
|
|
post_processed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
post_processed_at TIMESTAMP,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE lesson_sessions (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
lesson_type VARCHAR(64) NOT NULL,
|
||
|
|
teacher_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
lesson_package_purchase_id VARCHAR(64) REFERENCES lesson_package_purchases(id),
|
||
|
|
lesson_booking_id VARCHAR(64) REFERENCES lesson_bookings(id),
|
||
|
|
duration INTEGER NOT NULL,
|
||
|
|
booked_price NUMERIC(8,2) NOT NULL,
|
||
|
|
teacher_complete BOOLEAN DEFAULT FALSE NOT NULL,
|
||
|
|
student_complete BOOLEAN DEFAULT FALSE NOT NULL,
|
||
|
|
student_canceled BOOLEAN DEFAULT FALSE NOT NULL,
|
||
|
|
teacher_canceled BOOLEAN DEFAULT FALSE NOT NULL,
|
||
|
|
student_canceled_at TIMESTAMP,
|
||
|
|
teacher_canceled_at TIMESTAMP,
|
||
|
|
student_canceled_reason VARCHAR,
|
||
|
|
teacher_canceled_reason VARCHAR,
|
||
|
|
status VARCHAR,
|
||
|
|
analysed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
analysis JSON,
|
||
|
|
analysed_at TIMESTAMP,
|
||
|
|
cancel_message VARCHAR,
|
||
|
|
canceler_id VARCHAR(64) REFERENCES users(id),
|
||
|
|
charge_id VARCHAR(64) REFERENCES charges(id),
|
||
|
|
success BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sent_notices BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
sent_notices_at TIMESTAMP,
|
||
|
|
post_processed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
post_processed_at TIMESTAMP,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
|
||
|
|
ALTER TABLE music_sessions ADD COLUMN lesson_session_id VARCHAR(64) REFERENCES lesson_sessions(id);
|
||
|
|
ALTER TABLE notifications ADD COLUMN lesson_session_id VARCHAR(64) REFERENCES lesson_sessions(id);
|
||
|
|
ALTER TABLE notifications ADD COLUMN purpose VARCHAR(200);
|
||
|
|
ALTER TABLE notifications ADD COLUMN student_directed BOOLEAN;
|
||
|
|
|
||
|
|
INSERT INTO lesson_package_types (id, name, description, package_type, price) VALUES ('single', 'Single Lesson', 'A single lesson purchased at the teacher''s price.', 'single', 0.00);
|
||
|
|
INSERT INTO lesson_package_types (id, name, description, package_type, price) VALUES ('single-free', 'Free Lesson', 'A free, single lesson.', 'single-free', 0.00);
|
||
|
|
INSERT INTO lesson_package_types (id, name, description, package_type, price) VALUES ('test-drive', 'Test Drive', 'Four reduced-price lessons which you can use to find that ideal teacher.', 'test-drive', 49.99);
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE lesson_booking_slots (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
lesson_booking_id VARCHAR(64) REFERENCES lesson_bookings(id),
|
||
|
|
lesson_session_id VARCHAR(64) REFERENCES lesson_sessions(id),
|
||
|
|
slot_type VARCHAR(64) NOT NULL,
|
||
|
|
preferred_day DATE,
|
||
|
|
day_of_week INTEGER,
|
||
|
|
hour INTEGER,
|
||
|
|
minute INTEGER,
|
||
|
|
timezone VARCHAR NOT NULL,
|
||
|
|
message VARCHAR,
|
||
|
|
accept_message VARCHAR,
|
||
|
|
update_all BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
proposer_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE lesson_bookings ADD COLUMN default_slot_id VARCHAR(64) REFERENCES lesson_booking_slots(id);
|
||
|
|
ALTER TABLE lesson_bookings ADD COLUMN counter_slot_id VARCHAR(64) REFERENCES lesson_booking_slots(id);
|
||
|
|
ALTER TABLE lesson_sessions ADD COLUMN counter_slot_id VARCHAR(64) REFERENCES lesson_booking_slots(id);
|
||
|
|
ALTER TABLE lesson_sessions ADD COLUMN slot_id VARCHAR(64) REFERENCES lesson_booking_slots(id);
|
||
|
|
|
||
|
|
ALTER TABLE chat_messages ADD COLUMN target_user_id VARCHAR(64) REFERENCES users(id);
|
||
|
|
ALTER TABLE chat_messages ADD COLUMN lesson_booking_id VARCHAR(64) REFERENCES lesson_bookings(id);
|
||
|
|
ALTER TABLE users ADD COLUMN remaining_free_lessons INTEGER NOT NULL DEFAULT 1;
|
||
|
|
ALTER TABLE users ADD COLUMN stored_credit_card BOOLEAN NOT NULL DEFAULT FALSE;
|
||
|
|
ALTER TABLE users ADD COLUMN remaining_test_drives INTEGER NOT NULL DEFAULT 0;
|
||
|
|
ALTER TABLE users ADD COLUMN stripe_token VARCHAR(200);
|
||
|
|
ALTER TABLE users ADD COLUMN stripe_customer_id VARCHAR(200);
|
||
|
|
ALTER TABLE users ADD COLUMN stripe_zip_code VARCHAR(200);
|
||
|
|
ALTER TABLE sales ADD COLUMN stripe_charge_id VARCHAR(200);
|
||
|
|
ALTER TABLE teachers ADD COLUMN stripe_account_id VARCHAR(200);
|
||
|
|
ALTER TABLE sale_line_items ADD COLUMN lesson_package_purchase_id VARCHAR(64) REFERENCES lesson_package_purchases(id);
|
||
|
|
|
||
|
|
|
||
|
|
-- one is created every time the teacher is paid. N teacher_distributions point to this
|
||
|
|
CREATE TABLE teacher_payments (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
teacher_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
charge_id VARCHAR(64) REFERENCES charges(id) NOT NULL,
|
||
|
|
amount_in_cents INTEGER NOT NULL,
|
||
|
|
fee_in_cents INTEGER NOT NULL,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
-- one is created for every bit of money the teacher is due
|
||
|
|
CREATE TABLE teacher_distributions (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
teacher_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
teacher_payment_id VARCHAR(64) REFERENCES teacher_payments(id),
|
||
|
|
lesson_session_id VARCHAR(64) REFERENCES lesson_sessions(id),
|
||
|
|
lesson_package_purchase_id VARCHAR(64) REFERENCES lesson_package_purchases(id),
|
||
|
|
amount_in_cents INTEGER NOT NULL,
|
||
|
|
ready BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
distributed BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE affiliate_distributions (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
affiliate_referral_id INTEGER REFERENCES affiliate_partners(id) NOT NULL,
|
||
|
|
affiliate_referral_fee_in_cents INTEGER NOT NULL,
|
||
|
|
sale_line_item_id VARCHAR(64) REFERENCES sale_line_items(id) NOT NULL,
|
||
|
|
affiliate_refunded BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
affiliate_refunded_at TIMESTAMP WITHOUT TIME ZONE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE affiliate_partners ADD COLUMN lesson_rate NUMERIC (8,2) NOT NULL DEFAULT 0.20;
|
||
|
|
|
||
|
|
-- move over all sale_line_item affiliate info
|
||
|
|
INSERT INTO affiliate_distributions (
|
||
|
|
SELECT
|
||
|
|
sale_line_items.id,
|
||
|
|
sale_line_items.affiliate_referral_id,
|
||
|
|
sale_line_items.affiliate_referral_fee_in_cents,
|
||
|
|
sale_line_items.id,
|
||
|
|
sale_line_items.affiliate_refunded,
|
||
|
|
sale_line_items.affiliate_refunded_at,
|
||
|
|
sale_line_items.created_at,
|
||
|
|
sale_line_items.updated_at
|
||
|
|
FROM sale_line_items
|
||
|
|
WHERE sale_line_items.affiliate_referral_id IS NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE teacher_intents (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
user_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
teacher_id VARCHAR(64) REFERENCES teachers(id) NOT NULL,
|
||
|
|
intent VARCHAR(64),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
CREATE INDEX teacher_intents_intent_idx ON teacher_intents(teacher_id, intent);
|
||
|
|
|
||
|
|
CREATE TABLE schools (
|
||
|
|
id INTEGER PRIMARY KEY,
|
||
|
|
user_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
||
|
|
name VARCHAR,
|
||
|
|
enabled BOOLEAN DEFAULT TRUE,
|
||
|
|
scheduling_communication VARCHAR NOT NULL DEFAULT 'teacher',
|
||
|
|
correspondence_email VARCHAR,
|
||
|
|
photo_url VARCHAR(2048),
|
||
|
|
original_fpfile VARCHAR(8000),
|
||
|
|
cropped_fpfile VARCHAR(8000),
|
||
|
|
cropped_s3_path VARCHAR(8000),
|
||
|
|
crop_selection VARCHAR(256),
|
||
|
|
large_photo_url VARCHAR(512),
|
||
|
|
cropped_large_s3_path VARCHAR(512),
|
||
|
|
cropped_large_fpfile VARCHAR(8000),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE SEQUENCE school_key_sequence;
|
||
|
|
ALTER SEQUENCE school_key_sequence RESTART WITH 10000;
|
||
|
|
ALTER TABLE schools ALTER COLUMN id SET DEFAULT nextval('school_key_sequence');
|
||
|
|
|
||
|
|
ALTER TABLE users ADD COLUMN school_id INTEGER REFERENCES schools(id);
|
||
|
|
ALTER TABLE users ADD COLUMN joined_school_at TIMESTAMP;
|
||
|
|
ALTER TABLE teachers ADD COLUMN school_id INTEGER REFERENCES schools(id);
|
||
|
|
ALTER TABLE teachers ADD COLUMN joined_school_at TIMESTAMP;
|
||
|
|
|
||
|
|
CREATE TABLE school_invitations (
|
||
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
|
|
user_id VARCHAR(64) REFERENCES users(id),
|
||
|
|
school_id INTEGER REFERENCES schools(id) NOT NULL,
|
||
|
|
invitation_code VARCHAR(256) NOT NULL UNIQUE,
|
||
|
|
note VARCHAR,
|
||
|
|
as_teacher BOOLEAN NOT NULL,
|
||
|
|
email VARCHAR NOT NULL,
|
||
|
|
first_name VARCHAR,
|
||
|
|
last_name VARCHAR,
|
||
|
|
accepted BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
ALTER TABLE teachers ADD jamkazam_rate NUMERIC (8, 2) DEFAULT 0.25;
|
||
|
|
ALTER TABLE schools ADD jamkazam_rate NUMERIC (8, 2) DEFAULT 0.25;
|