96 lines
3.9 KiB
SQL
96 lines
3.9 KiB
SQL
CREATE TABLE retailers (
|
|
id INTEGER PRIMARY KEY,
|
|
user_id VARCHAR(64) REFERENCES users(id) NOT NULL,
|
|
name VARCHAR,
|
|
enabled BOOLEAN DEFAULT TRUE,
|
|
city VARCHAR,
|
|
state VARCHAR,
|
|
slug VARCHAR NOT NULL,
|
|
encrypted_password VARCHAR NOT NULL DEFAULT uuid_generate_v4(),
|
|
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 retailer_key_sequence;
|
|
ALTER SEQUENCE retailer_key_sequence RESTART WITH 10000;
|
|
ALTER TABLE retailers ALTER COLUMN id SET DEFAULT nextval('retailer_key_sequence');
|
|
|
|
|
|
CREATE TABLE retailer_invitations (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id VARCHAR(64) REFERENCES users(id),
|
|
retailer_id INTEGER REFERENCES retailers(id) NOT NULL,
|
|
invitation_code VARCHAR(256) NOT NULL UNIQUE,
|
|
note VARCHAR,
|
|
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
|
|
);
|
|
|
|
|
|
CREATE TABLE posa_cards (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
code VARCHAR(64) UNIQUE NOT NULL,
|
|
user_id VARCHAR (64) REFERENCES users(id) ON DELETE SET NULL,
|
|
card_type VARCHAR(64) NOT NULL,
|
|
origin VARCHAR(200),
|
|
activated_at TIMESTAMP,
|
|
claimed_at TIMESTAMP,
|
|
retailer_id INTEGER REFERENCES retailers(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX posa_card_user_id_idx ON posa_cards(user_id);
|
|
|
|
ALTER TABLE users ADD COLUMN jamclass_credits INTEGER DEFAULT 0;
|
|
|
|
|
|
CREATE TABLE posa_card_types (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
card_type VARCHAR(64) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
INSERT INTO posa_card_types (id, card_type) VALUES ('jam_tracks_5', 'jam_tracks_5');
|
|
INSERT INTO posa_card_types (id, card_type) VALUES ('jam_tracks_10', 'jam_tracks_10');
|
|
INSERT INTO posa_card_types (id, card_type) VALUES ('jam_class_10', 'jam_class_10');
|
|
|
|
CREATE TABLE posa_card_purchases (
|
|
id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
user_id VARCHAR(64) NOT NULL REFERENCES users(id) ON DELETE SET NULL,
|
|
posa_card_type_id VARCHAR(64) REFERENCES posa_card_types(id) ON DELETE SET NULL,
|
|
posa_card_id VARCHAR(64) REFERENCES posa_cards(id) ON DELETE SET NULL,
|
|
recurly_adjustment_uuid VARCHAR(500),
|
|
recurly_adjustment_credit_uuid VARCHAR(500),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
ALTER TABLE sale_line_items ADD COLUMN posa_card_purchase_id VARCHAR(64) REFERENCES posa_card_purchases(id);
|
|
|
|
|
|
ALTER TABLE teachers ADD COLUMN retailer_id INTEGER REFERENCES retailers(id);
|
|
ALTER TABLE teachers ADD COLUMN joined_retailer_at TIMESTAMP;
|
|
ALTER TABLE retailers ADD jamkazam_rate NUMERIC (8, 2) DEFAULT 0.25;
|
|
ALTER TABLE retailers ADD COLUMN affiliate_partner_id INTEGER REFERENCES affiliate_partners(id);
|
|
ALTER TABLE lesson_bookings ADD COLUMN retailer_id INTEGER REFERENCES retailers(id);
|
|
ALTER TABLE teacher_payments ADD COLUMN retailer_id INTEGER REFERENCES retailers(id);
|
|
ALTER TABLE teacher_distributions ADD COLUMN retailer_id INTEGER REFERENCES retailers(id);
|
|
|
|
|
|
ALTER TABLE sales ALTER COLUMN user_id DROP NOT NULL;
|
|
ALTER TABLE sales ADD COLUMN retailer_id INTEGER REFERENCES retailers(id);
|
|
ALTER TABLE sale_line_items ADD COLUMN retailer_id INTEGER REFERENCES retailers(id); |