sql

drop database IF exists coop_model;
create database coop_model;
use coop_model;

create table companies (
	company_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	name VARCHAR(255),
	type VARCHAR(255),
	phone VARCHAR(255),
	country VARCHAR(255),
	street_first VARCHAR(255),
	street_second VARCHAR(255),
	city VARCHAR(255),
	state VARCHAR(255),
	code VARCHAR(255)
);

create table company_contacts (
	contact_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	company_id INT NOT NULL,
    foreign key(company_id) references companies(company_id)
);
create table company_addresses (
	address_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	company_id INT NOT NULL NOT NULL,
    foreign key(company_id) references companies(company_id)
);

create table organizations (
	organization_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    company_id INT NOT NULL,
    foreign key(company_id) references companies(company_id)
);
create table locations (
	location_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);

 create table accounts (
    account_id INT PRIMARY KEY NOT NULL,
    foreign key(account_id) references organizations(organization_id)
);

 create table merchants (
    merchant_id INT PRIMARY KEY NOT NULL,
    foreign key(merchant_id) references organizations(organization_id)
);

create table organization_contacts (
	contact_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	organization_id INT NOT NULL,
    foreign key(organization_id) references organizations(organization_id)
);

create table organization_addresses (
	address_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	organization_id INT NOT NULL,
    foreign key(organization_id) references organizations(organization_id)
);
create table organizations_locations(
	organization_id INT NOT NULL,
    location_id INT NOT NULL,
    foreign key(organization_id) references organizations(organization_id),
    foreign key(location_id) references locations(location_id)
 );
create table users (
	user_id INT AUTO_INCREMENT PRIMARY KEY,
	password VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(255),
	email VARCHAR(255) NOT NULL UNIQUE,
	company_id INT,
    foreign key(company_id) references companies(company_id)
);
 create table rights (
	right_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    right_value VARCHAR(255)
);
create table users_rights(
	user_id INT NOT NULL,
    right_id INT NOT NULL,
    foreign key(user_id) references users(user_id),
    foreign key(right_id) references rights(right_id)
 );

create table roles (
	role_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    type VARCHAR(255),
    foreign key(role_id) references users(user_id)
);

create table types_table (
	type_id INT AUTO_INCREMENT PRIMARY KEY UNIQUE NOT NULL,
    type_value VARCHAR(255),
    foreign key(type_id) references users(user_id)
);

create table users_locations(
	user_id INT NOT NULL,
    location_id INT NOT NULL,
    foreign key(user_id) references users(user_id),
    foreign key(location_id) references locations(location_id)
 );

 create table users_organizations(
	user_id INT NOT NULL,
    organization_id INT NOT NULL,
    foreign key(user_id) references users(user_id),
    foreign key(organization_id) references organizations(organization_id)
 );

  create table teams (
	team_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);

  create table teams_locations(
	team_id INT NOT NULL,
    location_id INT NOT NULL,
    foreign key(team_id) references teams(team_id),
    foreign key(location_id) references locations(location_id)
 );


   create table teams_users(
	team_id INT NOT NULL,
    user_id INT NOT NULL,
    foreign key(team_id) references teams(team_id),
    foreign key(user_id) references users(user_id)
 );

 create table teams_organizations(
	team_id INT NOT NULL,
    organization_id INT NOT NULL,
    foreign key(team_id) references teams(team_id),
    foreign key(organization_id) references organizations(organization_id)
 );

create table notes (
	location_id INT NOT NULL,
    foreign key(location_id) references locations(location_id),
	note_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);

create table images (
	 location_id INT NOT NULL,
    foreign key(location_id) references locations(location_id),
	image_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);

create table attributes (
	 location_id INT NOT NULL,
    foreign key(location_id) references locations(location_id),
	attribute_id INT AUTO_INCREMENT PRIMARY KEY
);

create table bookings (
    location_id INT,
    foreign key(location_id) references locations(location_id),
	book_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);
  create table documents (
	document_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
);

 create table documents_type (
    doctype_id INT PRIMARY KEY UNIQUE,
    foreign key(doctype_id) references documents(document_id)
);


 create table tasks (
	task_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
	user_id INT,
    location_id INT,
    isCompleted boolean,
    title VARCHAR(255),
    createdBy varchar(255),
    type VARCHAR(255),
    priority VARCHAR(255),
    foreign key(location_id) references locations(location_id),
    foreign key(user_id) references users(user_id)
);

 create table tasks_teams (
	task_id INT  NOT NULL,
    team_id INT NOT NULL,
    foreign key(team_id) references teams(team_id),
    foreign key(task_id) references tasks(task_id),
    PRIMARY KEY(task_id, team_id)
);

create table tasks_users (
	task_id INT  NOT NULL,
    user_id INT NOT NULL,
    foreign key(user_id) references users(user_id),
    foreign key(task_id) references tasks(task_id),
    PRIMARY KEY(user_id, task_id)
);
create table documents_locations(
	document_id INT NOT NULL,
    location_id INT NOT NULL,
    foreign key(document_id) references documents(document_id),
    foreign key(location_id) references locations(location_id)
 );

 create table tasks_documents (
	task_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    document_id INT NOT NULL,
    foreign key(document_id) references documents(document_id),
    foreign key(task_id) references tasks(task_id)
);

  create table documents_organizations(
	document_id INT NOT NULL,
    organization_id INT NOT NULL,
    foreign key(document_id) references documents(document_id),
    foreign key(organization_id) references organizations(organization_id)
 );

 create table bills (
	bill_id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
	task_id INT NOT NULL,
    user_id INT NOT NULL,
	organization_id INT NOT NULL,
    foreign key(location_id) references locations(location_id),
    foreign key(task_id) references tasks(task_id),
    foreign key(organization_id) references organizations(organization_id),
    foreign key(user_id) references users(user_id)
);
 insert into rights (right_value) VALUES
 ('create'),
 ('update'),
 ('read'),
 ('delete');