-- source
drop table if exists users_exported;
drop table if exists projects_exported;
drop table if exists themes_exported;
drop table if exists subscriptions_exported;
drop table if exists addons_exported;
drop table if exists exports_exported;
drop table if exists files_exported;
drop table if exists pano_groups_exported;
drop table if exists panos_exported;
drop table if exists hotspots_exported;

create table users_exported as SELECT uuid() as id, id as user_id, name, email, email_verified_at, password, stripe_id, card_brand, card_last_four, remember_token, created_at, updated_at, country, custom_domain, force_premium
FROM users;

create table projects_exported as SELECT uuid() as id, p.id as project_id, u.id as user_id, p.title, p.slug, p.settings, p.created_at, p.updated_at, p.custom_domain, p.order_in_dashboard
FROM projects p
left join users_exported u on p.user_id = u.user_id;

create table themes_exported as SELECT uuid() as id, t.id as theme_id, p.id as project_id, t.title, t.slug, t.settings, t.`type`, t.created_at, t.updated_at
FROM themes t
left join projects_exported p on t.project_id = p.project_id;

create table subscriptions_exported as SELECT uuid() as id, s.id as subscription_id, u.id as user_id, s.name, s.stripe_id, s.stripe_plan, s.quantity, s.trial_ends_at, s.ends_at, s.created_at, s.updated_at
FROM subscriptions s
left join users_exported u on s.user_id = u.user_id;

create table addons_exported as SELECT uuid() as id, a.id as addon_id, p.id as project_id, a.title, a.slug, a.settings, a.created_at, a.updated_at
FROM addons a
left join projects_exported p on a.project_id = p.project_id;

create table exports_exported as SELECT uuid() as id, e.id as export_id, u.id as user_id, p.id as project_id, e.name, e.url, e.created_at, e.updated_at
FROM exports e
left join users_exported u on e.user_id = u.user_id
left join projects_exported p on p.project_id = e.project_id;

create table files_exported as SELECT uuid() as id, f.id as file_id, p.id as project_id, f.title, f.`type`, f.url, f.created_at, f.updated_at
FROM files f
left join projects_exported p on f.project_id = p.project_id;

create table pano_groups_exported as SELECT uuid() as id, pg.id as pano_group_id, p.id as project_id, pg.title, pg.`order`, pg.created_at, pg.updated_at
FROM pano_groups pg
left join projects_exported p on pg.project_id = p.project_id;

create table panos_exported as SELECT uuid() as id, ps.id as pano_id, p.id as project_id, ps.title, ps.slug, ps.`order`, ps.settings, ps.created_at, ps.updated_at
FROM panos ps
left join projects_exported p on ps.project_id = p.project_id;

create table hotspots_exported as SELECT uuid() as id, h.id as hotspot_id, p.id as project_id, pe.id as pano_id, h.`data`, h.title, h.`type`, h.ath, h.atv, h.created_at, h.updated_at
FROM hotspots h
left join projects_exported p on p.project_id = h.project_id
left join panos_exported pe on pe.pano_id = h.pano_id;