1  -- Enable UUID extension
 2  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
 3  
 4  -- Trigger function to auto-update updated_at
 5  CREATE OR REPLACE FUNCTION set_updated_at_column()
 6  RETURNS TRIGGER AS $$
 7  BEGIN
 8      NEW.updated_at = NOW();
 9      RETURN NEW;
10  END;
11  $$ LANGUAGE plpgsql;
12  
13  -- Owners Table
14  CREATE TABLE owners (
15      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
16      email VARCHAR(255) NOT NULL UNIQUE,
17      username VARCHAR(50) NOT NULL UNIQUE,
18      password_hash VARCHAR(255) NOT NULL,
19      full_name VARCHAR(100),
20      phone_number VARCHAR(20),
21      address TEXT,
22      is_active BOOLEAN NOT NULL DEFAULT TRUE,
23      email_verified BOOLEAN NOT NULL DEFAULT FALSE,
24      metadata JSONB DEFAULT '{}'::JSONB,
25  
26      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
27      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
28      deleted_at TIMESTAMP WITH TIME ZONE
29  );
30  
31  -- Trigger for owners table
32  CREATE TRIGGER trigger_set_updated_at_owners
33  BEFORE UPDATE ON owners
34  FOR EACH ROW
35  EXECUTE PROCEDURE set_updated_at_column();
36  
37  -- Pets Table
38  CREATE TABLE pets (
39      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
40      owner_id UUID NOT NULL,
41      name VARCHAR(100) NOT NULL,
42      species VARCHAR(50) NOT NULL,
43      breed VARCHAR(100),
44      date_of_birth DATE,
45      gender VARCHAR(10),
46      weight DECIMAL(5,2),
47      microchip_id VARCHAR(100) UNIQUE,
48      medical_notes TEXT,
49      metadata JSONB DEFAULT '{}'::JSONB,
50  
51      created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
52      updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
53      deleted_at TIMESTAMP WITH TIME ZONE,
54  
55      FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
56  );
57  
58  -- Trigger for pets table
59  CREATE TRIGGER trigger_set_updated_at_pets
60  BEFORE UPDATE ON pets
61  FOR EACH ROW
62  EXECUTE PROCEDURE set_updated_at_column();
63  
64  -- Insert sample data into owners
65  INSERT INTO owners (id, email, username, password_hash, full_name, phone_number, address, email_verified)
66  VALUES
67      ('11111111-1111-1111-1111-111111111111', 'alice@example.com', 'alice123', 'hashedpassword1', 'Alice Johnson', '123-456-7890', '123 Apple St', TRUE),
68      ('22222222-2222-2222-2222-222222222222', 'bob@example.com', 'bobby88', 'hashedpassword2', 'Bob Smith', '987-654-3210', '456 Orange Ave', FALSE),
69      ('33333333-3333-3333-3333-333333333333', 'carol@example.com', 'carol_c', 'hashedpassword3', 'Carol Danvers', '555-555-5555', '789 Banana Blvd', TRUE);
70  
71  -- Insert sample data into pets
72  INSERT INTO pets (owner_id, name, species, breed, date_of_birth, gender, weight, microchip_id, medical_notes)
73  VALUES
74      ('11111111-1111-1111-1111-111111111111', 'Buddy', 'Dog', 'Golden Retriever', '2018-06-01', 'Male', 30.5, 'MC-1001', 'Allergic to peanuts.'),
75      ('22222222-2222-2222-2222-222222222222', 'Whiskers', 'Cat', 'Siamese', '2020-02-15', 'Female', 4.2, 'MC-1002', 'None'),
76      ('33333333-3333-3333-3333-333333333333', 'Hopper', 'Rabbit', 'Lop', '2021-11-20', 'Male', 2.1, 'MC-1003', 'Needs monthly nail trimming.');