278 lines
4.9 KiB
SQL
278 lines
4.9 KiB
SQL
INSERT INTO
|
|
dev_users (
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
phone_number,
|
|
created_at,
|
|
updated_at
|
|
)
|
|
VALUES
|
|
(
|
|
'John',
|
|
'Doe',
|
|
'john.doe@example.com',
|
|
'(555) 123-4567',
|
|
NOW(),
|
|
NOW()
|
|
),
|
|
(
|
|
'Jane',
|
|
'Smith',
|
|
'jane.smith@example.com',
|
|
'(555) 123-4568',
|
|
NOW(),
|
|
NOW()
|
|
),
|
|
(
|
|
'Isabella',
|
|
'King',
|
|
'isabella.king@example.com',
|
|
'(555) 123-4576',
|
|
NOW(),
|
|
NOW()
|
|
);
|
|
|
|
INSERT INTO
|
|
clients(name, abbreviation, website)
|
|
VALUES
|
|
('Tech Corp', 'TC', 'techcorp.com'),
|
|
('Health Care Inc', 'HCI', 'healthcareinc.com');
|
|
|
|
INSERT INTO
|
|
client_users(
|
|
first_name,
|
|
last_name,
|
|
email,
|
|
phone_number,
|
|
role_in_company,
|
|
client_id
|
|
)
|
|
VALUES
|
|
(
|
|
'John',
|
|
'Doe',
|
|
'john.doe@techcorp.com',
|
|
'+1 555-0101',
|
|
'Developer',
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Tech Corp'
|
|
)
|
|
),
|
|
(
|
|
'Jane',
|
|
'Smith',
|
|
'jane.smith@healthcareinc.com',
|
|
'+1 555-0202',
|
|
'Project Manager',
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Health Care Inc'
|
|
)
|
|
);
|
|
|
|
INSERT INTO
|
|
projects(name, abbreviation, description, client_id)
|
|
VALUES
|
|
(
|
|
'Web Platform',
|
|
'WP',
|
|
'Core web application platform',
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Tech Corp'
|
|
)
|
|
),
|
|
(
|
|
'Mobile App',
|
|
'MA',
|
|
'Mobile frontend application',
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Tech Corp'
|
|
)
|
|
),
|
|
(
|
|
'EHR System',
|
|
'EHR',
|
|
'Electronic Health Records system',
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Health Care Inc'
|
|
)
|
|
);
|
|
|
|
INSERT INTO
|
|
statuses(name)
|
|
VALUES
|
|
('In Progress'),
|
|
('Completed');
|
|
|
|
INSERT INTO
|
|
tasks(project_id, status_id, description)
|
|
VALUES
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Web Platform'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
statuses
|
|
WHERE
|
|
name = 'In Progress'
|
|
),
|
|
'Backend API Development'
|
|
),
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Mobile App'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
statuses
|
|
WHERE
|
|
name = 'Completed'
|
|
),
|
|
'iOS Implementation'
|
|
);
|
|
|
|
INSERT INTO
|
|
client_user_project_joins(client_id, project_id, is_stakeholder)
|
|
VALUES
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Tech Corp'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Web Platform'
|
|
),
|
|
FALSE
|
|
),
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Tech Corp'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Mobile App'
|
|
),
|
|
TRUE
|
|
),
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
clients
|
|
WHERE
|
|
name = 'Health Care Inc'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'EHR System'
|
|
),
|
|
TRUE
|
|
);
|
|
|
|
INSERT INTO
|
|
dev_user_project_joins(dev_user_id, project_id, is_lead, notes)
|
|
VALUES
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
dev_users
|
|
WHERE
|
|
email = 'john.doe@example.com'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Web Platform'
|
|
),
|
|
TRUE,
|
|
'Lead Developer'
|
|
),
|
|
(
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
dev_users
|
|
WHERE
|
|
email = 'jane.smith@example.com'
|
|
),
|
|
(
|
|
SELECT
|
|
id
|
|
FROM
|
|
projects
|
|
WHERE
|
|
name = 'Mobile App'
|
|
),
|
|
TRUE,
|
|
'Lead Developer'
|
|
);
|