Untitled

-- Create Architects table
CREATE TABLE Architects (
    ArchitectID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    PostalCode VARCHAR(20),
    Country VARCHAR(50)
);

-- Insert 10 sample values into Architects table
INSERT INTO Architects (ArchitectID, FirstName, LastName, Email, Phone, Address, City, State, PostalCode, Country)
VALUES
    (1, 'John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Main St', 'Anytown', 'CA', '12345', 'USA'),
    -- Add more sample records here...

-- Create Projects table
CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    Description TEXT,
    Location VARCHAR(100),
    StartDate DATE,
    EndDate DATE,
    Budget DECIMAL(18, 2),
    Status VARCHAR(50),
    ClientID INT,
    LeadArchitectID INT
);

-- Insert 10 sample values into Projects table
INSERT INTO Projects (ProjectID, ProjectName, Description, Location, StartDate, EndDate, Budget, Status, ClientID, LeadArchitectID)
VALUES
    (1, 'Project A', 'Description for Project A', 'City X', '2023-01-01', '2023-12-31', 1000000.00, 'In Progress', 1, 1),
    -- Add more sample records here...

-- Create webClients table
CREATE TABLE webClients (
    ClientID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Phone VARCHAR(20),
    CompanyName VARCHAR(100),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    PostalCode VARCHAR(20)
);

-- Insert 10 sample values into webClients table
INSERT INTO webClients (ClientID, FirstName, LastName, Email, Phone, CompanyName, Address, City, State, PostalCode)
VALUES
    (1, 'Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', 'ABC Corp', '456 Oak St', 'Somewhere', 'NY', '54321'),
    -- Add more sample records here...

-- Create ProjectTeamMembers table
CREATE TABLE ProjectTeamMembers (
    TeamMemberID INT PRIMARY KEY,
    ProjectID INT,
    ArchitectID INT,
    Role VARCHAR(50),
    JoinDate DATE,
    EndDate DATE,
    Responsibilities TEXT,
    ExperienceYears INT,
    Email VARCHAR(100),
    Phone VARCHAR(20)
);

-- Insert 10 sample values into ProjectTeamMembers table
INSERT INTO ProjectTeamMembers (TeamMemberID, ProjectID, ArchitectID, Role, JoinDate, EndDate, Responsibilities, ExperienceYears, Email, Phone)
VALUES
    (1, 1, 1, 'Lead Architect', '2023-01-01', '2023-12-31', 'Responsible for...', 10, 'architect1@example.com', '123-456-7890'),
    -- Add more sample records here...

-- Create Materials table
CREATE TABLE Materials (
    MaterialID INT PRIMARY KEY,
    MaterialName VARCHAR(100),
    Description TEXT,
    Supplier VARCHAR(100),
    UnitPrice DECIMAL(18, 2),
    StockQuantity INT,
    UsageNotes TEXT,
    DateAdded DATE,
    LastUpdated DATE,
    MaterialCategoryID INT
);

-- Insert 10 sample values into Materials table
INSERT INTO Materials (MaterialID, MaterialName, Description, Supplier, UnitPrice, StockQuantity, UsageNotes, DateAdded, LastUpdated, MaterialCategoryID)
VALUES
    (1, 'Brick', 'Red clay brick', 'Brick Supplier Inc.', 1.50, 5000, 'Use for construction', '2023-01-01', '2023-08-21', 1),
    -- Add more sample records here...

-- Create MaterialCategories table
CREATE TABLE MaterialCategories (
    CategoryID INT PRIMARY KEY,
    CategoryName VARCHAR(50),
    Description TEXT,
    ParentCategoryID INT
);

-- Insert 10 sample values into MaterialCategories table
INSERT INTO MaterialCategories (CategoryID, CategoryName, Description, ParentCategoryID)
VALUES
    (1, 'Bricks', 'Various types of bricks', NULL),
    -- Add more sample records here...

-- Create Drawings table
CREATE TABLE Drawings (
    DrawingID INT PRIMARY KEY,
    ProjectID INT,
    DrawingTitle VARCHAR(100),
    Description TEXT,
    Type VARCHAR(50),
    Version VARCHAR(20),
    CreationDate DATE,
    LastModifiedDate DATE,
    FileURL VARCHAR(255),
    Status VARCHAR(50)
);

-- Insert 10 sample values into Drawings table
INSERT INTO Drawings (DrawingID, ProjectID, DrawingTitle, Description, Type, Version, CreationDate, LastModifiedDate, FileURL, Status)
VALUES
    (1, 1, 'Floor Plan', 'Floor plan for Project A', 'Floor Plan', '1.0', '2023-01-15', '2023-08-10', 'http://example.com/floorplan.pdf', 'Approved'),
    -- Add more sample records here...

-- Create Suppliers table
CREATE TABLE Suppliers (
    SupplierID INT PRIMARY KEY,
    SupplierName VARCHAR(100),
    ContactPersonName VARCHAR(100),
    ContactEmail VARCHAR(100),
    ContactPhone VARCHAR(20),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    PostalCode VARCHAR(20),
    Country VARCHAR(50)
);

-- Insert 10 sample values into Suppliers table
INSERT INTO Suppliers (SupplierID, SupplierName, ContactPersonName, ContactEmail, ContactPhone, Address, City, State, PostalCode, Country)
VALUES
    (1, 'ABC Supplier', 'John Supplier', 'john.supplier@example.com', '555-123-4567', '789 Supplier Ave', 'Supplytown', 'CA', '78901', 'USA'),
    -- Add more sample records here...

-- Create Certifications table
CREATE TABLE Certifications (
    CertificationID INT PRIMARY KEY,
    ArchitectID INT,
    CertificationName VARCHAR(100),
    Organization VARCHAR(100),
    IssueDate DATE,
    ExpiryDate DATE,
    LicenseNumber VARCHAR(50),
    Description TEXT,
    AttachmentURL VARCHAR(255),
    Status VARCHAR(50)
);

-- Insert 10 sample values into Certifications table
INSERT INTO Certifications (CertificationID, ArchitectID, CertificationName, Organization, IssueDate, ExpiryDate, LicenseNumber, Description, AttachmentURL, Status)
VALUES
    (1, 1, 'LEED Certification', 'Green Building Council', '2022-01-15', '2025-01-15', 'LEED123', 'LEED Green Associate', 'http://example.com/leed.pdf', 'Active'),
    -- Add more sample records here...

-- Create Invoices table
CREATE TABLE Invoices (
    InvoiceID INT PRIMARY KEY,
    ProjectID INT,
    InvoiceDate DATE,
    Amount DECIMAL(18, 2),
    DueDate DATE,
    Status VARCHAR(50),
    BillingAddress VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(50),
    PostalCode VARCHAR(20),
    Country VARCHAR(50)
);

-- Insert 10 sample values into Invoices table
INSERT INTO Invoices (InvoiceID, ProjectID, InvoiceDate, Amount, DueDate, Status, BillingAddress, City, State, PostalCode, Country)
VALUES
    (1, 1, '2023-05-15', 5000.00, '2023-06-15', 'Pending', '123 Billing St', 'Billingville', 'NY', '12345', 'USA'),
    -- Add more sample records here...