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;